ADO.NET DataSet Forum

LINQ - Full outter join on many subqueries

Sat, 28 Nov 2009 18:49:04 Z

I googled and saw alot of left outter joins and one big long function to outter join two existing tables, but I am trying to convert a huge complicated mess of a sql function into LINQ and it requires a full outter join of the results from 5 queries.

It's basically different accounting totals that were calculated. Any of them could be null and the user wants to see it as 0s for that total.

So, let's say I already have queries for "var total1, var total2, var total3, var total4, and var total 5"
Each has an ID and an amount.

How do I accomplish that in LINQ?

Compare two Tables data for each row...

Sat, 28 Nov 2009 07:49:32 Z

hi,

I have two tables (say ParentTable & ChildTable).
Both tables are having same columns.

Now i want to compare the ChildTable data with ParentTable. How i can compare the data for each row of child table and store the result in a list of bool... The result will be 'TRUE' for a row if the data matches and result will be 'FALSE' if there is any mismatch in the data.

Is there is any in build method to do it...? If not which is the fastest the way to do this comparison...?

Thanks in advancs,
IamHuM

How can I set a foreign key point to nothing?

Sat, 28 Nov 2009 16:19:39 Z

Environment: C#, Visual Studio 2008, .NET 3.5, SQL Server 2005 Express Ed.

If I want to set a table with a foreign key NULL, I raise an exception.
If I set the foreign key to 0, I raise another exception because the parent row doesn't exist.
So what can I do to achieve this result?

I don't want to create a parent row specific for a NOTHING item, please don't ask me that.

Thx for answers.

------- Life is what happens while doing other projects -------

Create Access 2003 tables from Dataset

Wed, 25 Nov 2009 17:18:59 Z

I have a bunch of datatables and I want to create and copy these tables into a new Access 2003 database. I think I know how to create and connect to the Access database, but is there a way to automatically create an Access table using the datatable schema?

I found this discussion here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/0b7fcec7-10db-4c9e-872e-f03ed745e2a9?prof=required where people talk about using "SELECT INTO", but how would you do that query with a datatable?

Help with XSD.EXE - xsd file containing a choice?

Thu, 26 Nov 2009 14:51:40 Z

Hello,

I'm trying to create a dataset object from an xsd.

The xsd contains this:
<xsd:choice>
<xsd:sequence>
<xsd:element name="StartRange" type="AONrangeStructure"/>
<xsd:element name="EndRange" type="AONrangeStructure" minOccurs="0"/>
<xsd:element name="Description" type="AONdescriptionType" minOccurs="0"/>
</xsd:sequence>
<xsd:element name="Description" type="AONdescriptionType"/>
</xsd:choice>

When executing xsd, the error thrown says that A column named 'Description'
already belongs to this DataTable. Does this mean that xsd.exe doesn't deal
with xsd:choices?

Any help appreciated.

TIA
Sam

Vb.net 2008 - System.data.enumerablerowcollection(of system.data.datarow) convert to string?

Thu, 26 Nov 2009 10:57:21 Z

Greetings,

For no shortage of trying I am unable to convert the contents of a datarow (containing xml data) to type string.

Using .tostring results in conversion syntax errors in the linq code and when adding the LINQ result as an xml element to an xmldocument the literal words systems.data.datarow (see code below for examples) appears.

Is it possible from the linq to extract the datatable rows containg xml and then write them to an xmldocument as xmlelements?

Apologies if this is the wrong section to post but the problem involves both LINQ selection from a datatable and XML.

Linq Code:-

Dim quotes = From quote In frmMainQuote.quoteData.Tables("quote").AsEnumerable _
                        Select quote _
                        Where (quote.Item("category").ToString = cboCategory.SelectedItem.ToString) _
                        And (quote.Item("author").ToString = txtAuthor.Text) _
                        And (quote.Item("text").ToString Like "*" & txtKeyword.Text & "*") _
                        And (quote.Item("date").ToString = dtpQuoteDate.Value.ToString("dd MMMM yyyy"))

Code to convert the content of the datarow(s) to xml (when examing the xmldocument it contains the word system.data.datarow or similar) :-

  Dim outQuoteDoc As New XDocument(New XElement("quotefile", quotes))

'Or

  Dim outQuoteDoc As New XDocument(New XElement("quotefile", quotes.toString))

Thanks
Rob

creating new ms access db with tables

Sat, 17 Oct 2009 16:36:47 Z

Hi all
I have a small app I am working on with c# and access. now I want to have the option to "save as" the db
1. to create copy of current db and to save it somewhere else(progrematicly)
2.to create completly new db , with the structure (tables, key...) of the existing db in the project. I find in google how to create new db, but how do I create the tables and everything?is there a way to do it from wxisting db , or do I need to run sql?
tnx

doubt

Thu, 26 Nov 2009 06:03:53 Z

one dropdownlist is of some state names and other one is of city of that state

so if user select any State then all the city should be come into 2nd dropdownlist (DropDownList2).

i have written the program below but the problem is when i first select ,first state it doesnt display and again i select another state, displays and again i select first state displays...why it happening

public void PopulateDropDownListStates(string state)
    {
        string sql = "stat";
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable ds = new DataTable();

        SqlParameter st = new SqlParameter("@state", SqlDbType.VarChar);
        cmd.Parameters.AddWithValue("@state",state);

        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = sql;

        conn.Open();
        da.Fill(ds);


        if (ds.Rows.Count > 0)
        {
            DropDownList2.DataSource = ds;
            DropDownList2.DataTextField = "city";
            DropDownList2.DataValueField = "city";
            DropDownList2.DataBind();
        }
        conn.Close();


    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
       PopulateDropDownListStates(DropDownList1.SelectedItem.Value.ToString());
    }

Bundled SQL Server

Fri, 20 Nov 2009 12:05:31 Z

Hello guys, not sure if this has been asked before, but I'll ask anyway. I am developing an application. Currently, I have added a .mdf database via the solution explorer. I have done the settings correctly and I am able to connect to the said database.
I have written code to write to the database using data adapter insert command. I have also included a data reader just to see if the data is correctly inserted.

Now, so far, I have not run into any errors while debugging + running. I can successfully read what has been inserted. I am curious, however, as to why the data no longer exists in the database after I terminate the program and run it again. Also, in the server explorer, I do not see any data recorded in the database. Why is this so? Is this normal? Does it mean that everything the application is closed, the database resets itself? I need help urgently. Thanks.

DataSet - when the view you want doesn't match tables in DB

Wed, 25 Nov 2009 19:18:00 Z

I am new to C# and ADO in general, so I am having trouble even forming my question...

Currently I have a form that allows the user to modify some data in the database. I created 2 classes that contain the data as I would present it to the user. There are several convoluted quieries and procedures I have to call in order to get that data in usable form for the user.

I have a feeling my code is messy because I am trying to manually sync my class instances with the grid contrlols on the form and handle events at the same time. So, I figure it would be easier to just use data sets that sync automatically with the controls.

My problem is all the examples show datasets with tables that are an exact match with what's in the database. They are examples, so I can see the simplicity. How do you go about handling data that needs to be "transformed" as it is put into the dataset?

Custom conversions for DataSet Merge (untyped to strongly typed)?

Mon, 23 Nov 2009 17:35:13 Z

Hello,

I am trying to merge an untyped DataSet into a strongly typed DataSet.

The two DataSets have the same table and column names, but the column data types are different in some cases -- so, the call to Merge() fails.

The untyped DataSet actually has the correct values for each column, only the data type is wrong (for example, string instead of double).

When I was converting this manually, I could simply call Convert.ToDouble(sourceTable["ColumnName"]), and get the right value. But obviously, Merge() doesn't do this automatically.

So, can I somehow create a custom converter to pass to Merge(), or is there another way to merge these data sets without going through each table and column manually?

Thanks

LINQ to SQL-pubs Console application: 3 errors in Lambda Expression - Why? How to solve them?

Tue, 24 Nov 2009 12:55:35 Z

Hi all,

In my Visual C# 2008 Express, I created a LINQ to SQL-pubs Console application with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;
using System.Data;

namespace LinqLambdaConsoleLee442
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

            //---loads the Authors table into dataset---
            conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;" +
                  "Initial Catalog=pubs;Integrated Security=True");
            comm = new SqlCommand("SELECT * FROM Authors", conn);
            adapter = new SqlDataAdapter(comm);
            adapter.Fill(ds);
            var query =
                ds.Tables[0].AsEnumerable()
                .Count (a => a.Field<string("State") == "CA");
                    Console.WriteLine(query);
            Console.ReadLine();
           
        }
    }
}

I got the following 3 errors immediately:

Error 1 'System.Data.EnumerableRowCollection<System.Data.DataRow>' does not contain a definition for 'Count' and the best extension method overload 'System.Linq.Enumerable.Count<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>)' has some invalid arguments C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\LinqLambdaConsoleLee442\LinqLambdaConsoleLee442\Program.cs 29 17 LinqLambdaConsoleLee442
Error 2 Argument '2': cannot convert from 'lambda expression' to 'System.Func<System.Data.DataRow,bool>' C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\LinqLambdaConsoleLee442\LinqLambdaConsoleLee442\Program.cs 29 25 LinqLambdaConsoleLee442
Error 3 Invalid expression term 'string' C:\Documents and Settings\e1enxshc\My Documents\Visual Studio 2008\Projects\LinqLambdaConsoleLee442\LinqLambdaConsoleLee442\Program.cs 29 44 LinqLambdaConsoleLee442

Please help and tell me why I got these three errors and how I can solve them.

Thanks,
Scott Chang

Help: Millions of Rows no T-SQL only ADO.NET (and C#) !

Sat, 21 Nov 2009 21:18:23 Z

Hi all,

I am working in financial reporting for an insurance company and i have to generate a complex report. I will try to describe the problem as clearly as i can. Please ask for details if any further information needed. Your comments and replies are appreciated.

The General Frame:

We currently hold our data in an Oracle DB.
As a finance user i do not have any rights on this DB other than select, not on the production system; we do have a nightly copy for reporting.
In our current organization, business intelligence is nearly taking no attraction so requesting any improvements from IT stuff does not work.
Unfortunately our parent company demands really detailed reports which can not be handled in our current structure in DB.

The Task:

We need to report the development in our written premium (or sales shortly and more comfortably).
This report intends to control the rate of lapsed business' from the last year and structure of the business for the current year.
To generate the report i need to start with the sales of the previous year,
Eliminate the contracts which can not be renewed (the ones covering more/less than one year),
Eliminate the contracts which were in charge last year but canceled at some point up to day,
Determine the remaining business from the last year,
Determine which contracts are renewed,
Determine the status of the current year's business in two categories: renewed or new business
This report also requires 5 dimensions like customer category, product group, region etc.

Technical Details:

I have no option to utilize the DB like creating temp tables and running queries in SPs and filling those tables due to access limitations.
I have to inspect each contract to categorize it as described above.
More over, i have to compare the set of contracts for the last and current year to determine if they are renewed or not.
Roughly there are more than 1 million rows for each year.

Current Program:

I used C# express to create a WinForm application to collect input from user such as for which date the report is going to be prepared and where to be saved.
Project is targeting .net 3.5
I used Oracle client to access data.
I created 2 queries to get the the contracts into a typed dataset both for last year and current year.
The queries run rather long but keeping the data taken it is acceptable.
Data is returned as sorted .
From now on, i do not use the DB. Everything is executed in the local machine.
Once i download the data to local dataset in the memory, i start to loop (foreach loop) over the rows for the last year testing the conditions i described above and assign a category.
I use a function testing some fields of decimal and string with connected if (if/else if/else) statements.
This function also checks the last years contract in the current years rows using multiple fields like product code, arrangement date and customer code.(I can not just join the queries, i have already considered that)
This part involves LINQ . I am not very experienced with LINQ, i have created the queries before i get into the loop but not sure if it is caching the results.
I also collect the IDs of contracts which are marked as renewed.
Using the collected IDs, i again loop over the rows but for the current year this time and i mark them accordingly whether new business or not.
After i group the data and save the aggregated figures to an excel file.

The Problem:

the first loop processing the last year's data is taking too much time to get out of the loop.
The second loop relatively exits faster than the first but is not in an acceptable situation either.

My Lack of Knowledge/Points to Consider:

I accept in advance that the resources in my local machine (a simple work station) may not be suitable for a task like this.
I have never worked with a dataset like this before, it is relatively large for me.
I have never had a performance consideration due to small datasets.
I may have overloaded the program with the typed dataset, i used it just to save time. I do not need most of it except a clear row definition. I just did not want go into another loops for a custom object and a datareader.
I do not have to much experience with LINQ, it is just easier to code, therefore i just picked it up to run crosschecks between the two years' data.

To sum up, i need to process lots of rows out of the DB and i am not so experienced in this sort of performance-minded/data-intensive tasks and my solution is useless at this point.
I want to thank in advance for your time, help, comment and suggestions.

akr

How to refresh XSD file when datasource (access db) is changed

Tue, 24 Nov 2009 02:13:58 Z

Hi,

I've generated an XSD file using access database as its datasource.
However, I need to change the db schema.
I need to add a few more columns and modify some data types.
Is there a way of updating my XSD file without creating it again?

Regards,

Jean Grey

How to insert dataset into Oracle database directly

Thu, 12 Nov 2009 11:35:38 Z

I have one Excel sheet.
I am loading data from excel sheet to a dataset.
Now i want to insert this dataset to oracle database table directly.

Can you suggest me?

My code goes here-----

Excel sheet name:Book1
Oracle Table Name:Import1

OracleConnection destination=

new OracleConnection();
string lsConnectionString = ConfigurationSettings.AppSettings["CCMdsn"];
destination.ConnectionString=lsConnectionString;
if(destination.State != ConnectionState.Open)
{
destination.Open();
}
OracleTransaction trans=null;
OracleCommand command1 =new OracleCommand("Select * from import1", destination);
OracleDataAdapter adapter =new OracleDataAdapter(command1);
DataSet dataSet =new DataSet();
int b =adapter.Fill(dataSet, "import1");
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\loader\Book1.xls; Extended Properties=""Excel 8.0;HDR=YES""";
OleDbConnection source =new OleDbConnection(excelConnectionString);
source.Open();
OleDbCommand command =new OleDbCommand("SELECT * FROM [Book1$]", source);
oleDbDataAdapter dataAdapter =new OleDbDataAdapter(command);
int a =dataAdapter.Fill(dataSet, "import1");
OracleCommandBuilder builder =new OracleCommandBuilder(adapter);
trans = destination.BeginTransaction();
command1.Transaction = trans;
adapter.InsertCommand = builder.GetInsertCommand();
int check = adapter.Update(dataSet, "import1");
trans.Commit();
if(destination.State == ConnectionState.Open)
destination.Close();
source.Close();

It is executing with out any error.But database table is not updating with any changes.

Adding column to dataset via designer causes "cannot find the parent relation" exception

Mon, 16 Nov 2009 23:19:45 Z

I have added two columns to a rather complex dataset via the designer. When I execute an add record against this dataset the app throws an exception with the message "Cannot find the parent relation". I have been all thru the code and the relation is defined. I have deleted the relation and readded it with the same exception being thrown. I have done many searches and found that this is an existing issue that was supposedly resolved. However, I have also found additional posting on connect.microsoft... that people are still seeing this issue in current VS releases including VS 2008 SP1, which is what I am running.

% in query

Fri, 20 Nov 2009 17:42:05 Z

I create a table adapter to fill a datatable in dataset.

The query defined in the table adapter is:
select * from orders where orderno like @orderno

When running the fill method, I passed "%3%" as the parameter value. However, no row returned. I couldn't get anything even I used "%".

daorders.fill("%")

Is there any one knows why?

Identifying every field type in a table programmably

Wed, 18 Nov 2009 07:05:38 Z

I want to retrieve every field type in a table in a program.

We tried this by select top 0 * from Table with ADO recordset and found that many types can be identified by reading Fields[].Type value.
However, some field types show incorrect results, for example, Date type in SQL Server is shown as VarWChar in ADO Fields[].Type value.
How can I retrieve correct field type list of a table?

Thanks in advance.

How to do aggregates with a groupby against a datatable

Fri, 20 Nov 2009 22:58:46 Z

How can I accomplish the equivalent of this SQL query in C# against an ado.net datatable

insert resulttable select a, sum(a) as b, count(*) as n from c group by a

Thanks in advance...

Handling large tables with datarelations

Fri, 20 Nov 2009 18:33:45 Z

Suppose I have multiple tables for which I created DataRelation objects in a DataSet which describe a hierarchical relationship. The tables are all very large. I have a query which limits the number of records for the root table and it fills very quickly. How do I fill the dependent tables without reading all of their data? This fill needs to be driven off the records selected by the original query. (Doing this basically requires doing all the joins to determine the appropriate records in the dependent tables.) Everything I've found on google basically assumes that you load all the data from the dependent tables but that is not feasible.

Am I trying to do something that just isn't appropriate with a DataSet? It seems like the only real alternative to reading all the data is to use a DataReader but it seems like I'll lose the power of the DataRelations and the hierarchical DataSet.

vb cod convert in C#

Thu, 19 Nov 2009 06:02:45 Z

i have vb6 function how to convert in c# , can u help me

i have filled combox

cmbItemCategory.DataSource = ds.Tables[0];

cmbItemCategory.DisplayMember = "Description";

cmbItemCategory.ValueMember = "CategoryID";

now i want to display description in combobox but i have saved in database value member

Function FindNameInCombo(code As String, ComboName As ComboBox) As Integer

Dim i As Integer

i = 0

For i = 0 To ComboName.ListCount - 1

If ComboName.ItemData(i) = Val(code) Then

ComboName.ListIndex = i

FindNameInCombo = ComboName.ListIndex

Exit Function

End If

End Function

CRUD Example with Dataset, DataRow and Access Mdb

Fri, 13 Nov 2009 11:51:45 Z

I've created (using a wizzard, don't remember which) a dataset that points to a mdb file (Access 2007)
The generated DataSet is named "EstacionarDataSet"

Here's some code I have:

EstacionarDataSet ed = new EstacionarDataSet();
DataRow newRow = ed.Tables[0].NewRow();
newRow["patente"] = tb_Patente.Text;
newRow["numerobox"] = tb_Box.Text;
newRow["fechahora_ingreso"] = DateTime.Now;
ed.Tables[0].Rows.Add(newRow);

* The above code is executed in SecondForm (not mainform). I'm trying to insert a new record into the only table mdb file has (this code doesn't work, but doesn't give any exception).

* The mainform contains a System.Windows.Forms.DataGridView component, attached to this mdb file, which works fine, it shows the records correctly, etc. (PS: I added some sample records using Access)

* I guess the wizzard has created all the necessary code, connectionstring, etc. Project source code is available here
http://www.mediafire.com/?zwotmgwvzii

* I'm needing some code samples for adding a new row, updating, and deleting... Thanks

Beza - Custom Joomla Templates

LINQ to ADO.NET-LINQ to DataSet in Windows Forms: Accessing Data of Typed DataSet and UnTyped DataSet & Presenting the Data in DataGridView

Tue, 17 Nov 2009 15:21:56 Z

Hi all,

In my Visual C# 2008 Express, the followimg code of LINQ to DataSet Console application works nicely:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;
using System.Data;

namespace LINQ_ConsoleLee439
{
    class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

            //---loads the Authors table into dataset---
            conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;" +
                  "Initial Catalog=pubs;Integrated Security=True");
            comm = new SqlCommand("SELECT * FROM Authors", conn);
            adapter = new SqlDataAdapter(comm);
            adapter.Fill(ds);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine("{0} <=> {1} {2}",
                   row["au_id"], row["au_fname"], row["au_lname"]);
            }
            //---Using LINQ to DataSet---
            //---query for authors living in CA---
            var authors =
                (from author in ds.Tables[0].AsEnumerable()
                 where author.Field<string>("State") == "CA"
                 select new
                 {
                     ID = author.Field<string>("au_id"),
                     FirstName = author.Field<string>("au_fname"),
                     LastName = author.Field<string>("au_lname")
                 }).ToList();

            Console.ReadLine();
        }
    }
}

I got the desired output of 23 items of the Authors living in CA (of pubs database) in the Console screen without any problem.

Now I try to do the same LINQ to DataSet, but print the output in the Windows Forms DataGridView in 2 ways:

#1-Accessing the "Authors" data of pubs database as Typed DataSet (i.e. creating the .xsd file in the Solution Explorer) and presenting the "Author_ID", "Author_firstName", and "Author_lastName" living in CA in the DataGridView.
#2-Accessing the "Authors" data of pubs database as UnTyped DataSet (i. e. without creating the .xsd file in the Soultion Explorer) and presenting the "Author_ID", "Author_firstName", and "Author_lastName" living in CA in the DataGridView.

I have difficulties of LINQ query in the both ways:
For #1: How can I create a data view? How can I set the DataSource property of the binding source for the DataGridView to the data view?
For #2: How can I create the UnTyped DataSet, Data Adapter, and Binding Source in code?

I read the following articles:
Article #1: Creating a DataView Object (LINQ to DataSet) in http://msdn.microsoft.com/en-us/library/bb669080.aspx
Article #2: LINQ to ADO.NET-LINQ to DataSet (Filling a Typed DataTable with the CopyToDataTable Method) in http://en.csharp-online.net/LINQ_toADO.NET%E2%80%94LINQ_to_DataSet
Article #3: An useful sample of LINQ to DataSet (to perform filtering od data against an untyped daraset) in http://www.dmm888.com/Blog/linq2.html
Article #4: Adding UnTyped DataSets to a Form or Component in http://msdn.microsoft.com/en-us/cwefee50.aspx

I am very confused with the above-mentioned LINQ techniques and coding. Please help and advise.

Thanks,
Scott Chang

P. S. The following code is my first attemp to do LINQ to UnTyped DataSet that gives me the Windows Forms output with an umpty DataGridView:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Data;
//using AuthorDisplay.DataSetLee439TableAdapters;
//using System.Data.Linq;
//using System.Data.Linq.Mapping;

namespace LINQ_Lee439
//namespace LINQtoDataSet (Lee's Book: Page 439)
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

       // [Table(Name = "PubsAuthorNames")]
        public class PubsAuthorNames
        {
            public string au_id { get; set; }
            public string au_fname { get; set; }
            public string au_lname { get; set; }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

        //---loads the Authors table into dataset---
        conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;"  +
              "Initial Catalog=pubs;Integrated Security=True");
        comm = new SqlCommand("SELECT * FROM Authors", conn);
        adapter = new SqlDataAdapter(comm);
        adapter.Fill(ds);
         MessageBox.Show(ds.Tables[0].Rows.Count.ToString());  //per Yichun Feng
        
    //---Using LINQ to DataSet---
    //---query for authors living in CA---
        var authors =
            (from author in ds.Tables[0].AsEnumerable()
             where author.Field<string>("State") == "CA"
             select new
             {
                 ID = author.Field<string>("au_id"),
                 FirstName = author.Field<string>("au_fname"),
                 LastName = author.Field<string>("au_lname")
             }).ToList();

        MessageBox.Show(authors.Count.ToString()); //per Yichun Feng

        //---bind to a datagridview control---
        this.dataGridView1.AutoGenerateColumns = true;
        this.dataGridView1.DataSource = authors;
   
     }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

            //---loads the Authors table into dataset---
            conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;" +
                  "Initial Catalog=pubs;Integrated Security=True");
            comm = new SqlCommand("SELECT * FROM Authors", conn);
            adapter = new SqlDataAdapter(comm);
            adapter.Fill(ds);
            MessageBox.Show(ds.Tables[0].Rows.Count.ToString());  //per Yichun Feng
        
            //---Using LINQ to DataSet---
            //---query for authors living in CA---
            var authors =
                (from author in ds.Tables[0].AsEnumerable()
                 where author.Field<string>("State") == "CA"
                 select new
                 {
                     ID = author.Field<string>("au_id"),
                     FirstName = author.Field<string>("au_fname"),
                     LastName = author.Field<string>("au_lname")
                 }).ToList();

            MessageBox.Show(authors.Count.ToString()); //per Yichun Feng

            //---bind to a datagridview control---
            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.DataSource = authors;
   
        }
    }
}

LINQ to ADO.NET-LINQ to DataSet (pubs) in Console application: Why " where author.Field("State") == "CA" " fails?

Tue, 17 Nov 2009 19:54:35 Z

Hi all,

In my Visual C# 2008 Express, I ran the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;
using System.Data;

namespace LINQ_ConsoleLee439
{
    class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

            //---loads the Authors table into dataset---
            conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;" +
                  "Initial Catalog=pubs;Integrated Security=True");
            comm = new SqlCommand("SELECT * FROM Authors", conn);
            adapter = new SqlDataAdapter(comm);
            adapter.Fill(ds);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine("{0} <=> {1} {2}",
                   row["au_id"], row["au_fname"], row["au_lname"]);
            }
            //---Using LINQ to DataSet---
            //---query for authors living in CA---
            var authors =
                (from author in ds.Tables[0].AsEnumerable()
                 where author.Field<string>("State") == "CA"
                 select new
                 {
                     ID = author.Field<string>("au_id"),
                     FirstName = author.Field<string>("au_fname"),
                     LastName = author.Field<string>("au_lname")
                 }).ToList();

            Console.ReadLine();
        }
    }
}

It gave me all the 23 Authors living in CA, IN, MD, TN, OR, KS, UT, MI that is not the code statement

where

author.Field<string>("State") == "CA"

orders!!??

Please help and tell me what is wrong in the code of my LINQ query.

Thanks,
Scott Chang

LINQ to SQL-pub Windows Application in Visual C# 2008 Express: No error and no output of 3 columns in dataGridView!! Why?

Tue, 20 Oct 2009 14:49:55 Z

Hi all,
In my Visual C# 2008 Express, I created a Windows Forms application "LINQ-Lee439" with the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;

namespace LINQ_Lee439
//namespace LINQtoDataSet (Lee's Book: Page 439)
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection conn;
            SqlCommand comm;
            SqlDataAdapter adapter;
            DataSet ds = new DataSet();

        //---loads the Authors table into dataset---
        conn = new SqlConnection(@"Data Source=NAB-WK-02554356\SQLSCOTT;"  +
              "Initial Catalog=pubs;Integrated Security=True");
        comm = new SqlCommand("SELECT * FROM Authors", conn);
        adapter = new SqlDataAdapter(comm);
        adapter.Fill(ds);
        
    //---Using LINQ to DataSet---
    //---query for authors living in CA---
        var authors =
            (from author in ds.Tables[0].AsEnumerable()
             where author.Field<string>("State") == "CA"
             select new
             {
                 ID = author.Field<string>("au_id"),
                 FirstName = author.Field<string>("au_fname"),
                 LastName = author.Field<string>("au_lname")
             }).ToList();

        //---bind to a datagridview control---
        this.dataGridView1.AutoGenerateColumns = true;
        this.dataGridView1.DataSource = authors;
   
     }
    }
}

From the ToolBox, I dragged a DataGridView to the surface of Form1. It did not have any compilation errors. I executed this project. It ran and the Form1 appeared, but the dataGridView1 was empty!!! I do not know what is wrong with this project. Please help and advise.

Thanks,

Scott Chang

insert csv file into the sqltable

Fri, 09 Jan 2009 12:47:02 Z

I am writing a program by which it reads the data from the csv file and insert data into the sql table. and the program is running successfully. but i am getting one bug that it not reads the first row of the file.

program:

        OdbcConnection cnx;
        System.Data.DataTable dt = new System.Data.DataTable();
        OdbcDataAdapter da = new OdbcDataAdapter();
        OdbcCommand cmd;
        string cnxStr;
        string CSVFolder;
        CSVFolder = "c:\\CSVFolder";
        cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder + ";";
        cnx = new OdbcConnection(cnxStr);
        cmd = new OdbcCommand("Select * from [junk.txt] ", cnx);
        da.SelectCommand = cmd;
        cnx.Open();
        da.Fill(dt);
        foreach (System.Data.DataRow dr in dt.Rows)
        {
            if (Convert.ToString(dr[0]).Length > 0 && Convert.ToString(dr[1]).Length > 0)
            {
                //sql insertion command here
            }
        }

note: suppose if the file contains the three rows

1, sudhir
2, bharti
3, raj

it is counting the total row as 2, it leaves the first row (1, sudhir)

and ideas?

LINQ with DataTable

Wed, 18 Nov 2009 10:10:37 Z

I am using LINQ on datatable and select distinct Column from data table
but when i use following code then i got incomplete string in str. i think its due to ASEnumerable().
if a character is repeating in value then its skipped!
i dont understand, why its happened?
Can any one tell me how can i select distinct data values from a data table using LINQ?

  var query = (from rec in this.Objects.Tables[0].AsEnumerable()
               select rec.Field<String>("TblName").Distinct().ToList());

                foreach (var row in query)
              {
                  String str = new String(row.ToArray());
              }

Zubair Khalid

DataTable Select

Wed, 18 Nov 2009 18:43:58 Z

I am working in vb2008. I need to query a datatable.

Dim FoundRows as datarow()=maindatatable.select()

I need to extract all the rows where StructureID = 2000 where RecordType = Record in ascending order. Please help me with the correct format for a datatable.select ?

Thanks,
Fred

DataTable.Copy() throws exception for tables with relations

Wed, 28 Oct 2009 12:14:42 Z

Hi there,

there is a problem in the Copy method in the DataTable if the table has relations.

ie: if we make table A and table B and relate them by a relation, then we add to table A column based on expression like "Parent(TableBRelation).Name", when we copy table A to new table C it throws an exception in the Copy method says that he can't find the "TableBRelation" relation.

any one help please?

Akram MellIce

Failed to enable constrains problem

Wed, 11 Nov 2009 05:37:10 Z

hi
am making an inventory program with vb.net 2008 and sql server 2008, the database contains several tables , two of them are the Items and the Companies , each linked in a different forum but they have a foreign key relationship between comapnyID at the companies and the one at the items which appears at the forum as a combobox to display the name instead of the ID, the problem is when i add a new item and save it , it is saved but when i come back to load the items fourm the FAMOUS error appears ,(failed to enable constrains ) so i throught a try catch at the item fourm load sub, so after the error occur , i get into the items forum , i get to see the item i just added but without the company name(companyID)
what should i do ??

SqlDbType - and the Sql Server numeric(X,X) type

Fri, 13 Nov 2009 19:55:46 Z

In the VB and C# ADO.Net interaction with SQL Server, there is the SqlDbType which basically gives a reference of the actual Sql Server type as opposed the the converted (or interpreted) .Net System.Type. Thus SqlDbType.NvarChar becomes a string with a length limiter, while SqlDbType.Money becomes a Decimal type, and so forth.

However, when dealing with certain elements of SQL Server, one can define their own numerical type, such as:
numeric(3,0)
which basically allows a whole number from 0 to 999. How does .Net interpret this? How does it handle the implications and data verification when going to and from the server. is there some backside handler that is instigated to make sure the number 3000 isn't updated to a column of only 3 digit magnitude? Does Sql Server just truncate these values on its own?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

"Never Trust a computer. Your brain is smarter than any micro-chip."

Copy few column data from one table to another...

Tue, 17 Nov 2009 05:34:49 Z

Hi,

I have two tables in my application(Table1 and Table2).
Table1 - Coulm1, Column2, Column3 Column4, Column5
Table1 is having some records(say 50 rows).

I want to copy few records from first table to second table.
But i want to copy only 3 column data for all rows from first table(Table1) to second table(Table2).

What is the quickest and easiest way to do this...?

Thanks,
IamHuM

Why does Dataset.GetChanges() return all the tables when one row was deleted?

Tue, 10 Nov 2009 07:05:21 Z

I am pulling my hair out on this one.

I have 18 SQL Server 2008 tables loaded into a 3.5 DataSet via Fill calls by the SqlDataAdapter. I have removed all the relations, got rid of the FillSchema calls, called AcceptChanges, verified that GetChanges returns null and still when the Infragistics data grid deletes one row from one table, when I call GetChanges again, I get all 18 of my tables returned in the DataSet copy albeit most of the tables return a 0 count for the Tables.Rows.Count property. Still I need to know exaclty what tables have changed. Many of these tables are related in the actual database but this table has only a couple relations with other tables, and none of those relations are defined in this case. I have debugged from "before row deleted" to "after row deleted" in Infragistics grid and there are no more calls into my data layer unless somehow the DataSet object is grabbed from the DataTable I gave the grid.

Would appreciate any help with this.

Thanks, Dave

join 2 datatables and get a datatable from the join (linq to dataset)

Tue, 17 Nov 2009 05:56:02 Z

hi all.
i cant find an example of how to create a join between 2 datatables, select some fields from that join and then return a datatable from this.
in an msdn example i can get from this join a list, but i need a datatable because i need to use dataview on it, filter it and set it as the datasource of my datagridview.

shachar

DataView.RowFilter cannot find rows.

Mon, 09 Nov 2009 08:32:39 Z

Hi!

Why in sample below DataView is empty ? In table is row " a a " and filter is also the same but DataView is empty.

			DataSet1 ds = new DataSet1();
			ds.DataTable1.AddDataTable1Row(" a a ");
			ds.AcceptChanges();

			DataView view = new DataView(ds.DataTable1);
			view.RowFilter = string.Format("Name LIKE '%{0}%'", " a a ");
			Console.WriteLine(view.Count.ToString());

			Console.ReadKey();

Regards!

New dataviews property change event not raised. bug fixed for 4.0 but how about 3.5? what can we do now?

Tue, 17 Nov 2009 02:26:28 Z

understand that the bug fix on the propertychangeevent not raised on new Dataviews has been fixed for .net 4.0.

But we are still utilizing 3.5 and dataviews and datarowviews are quite important specially when working with wpf. Could you provide us with a patch or a bug fix or a good workaround?

Utilizing the defaultview everywhere works but it is simply a not feasible solution when binding the same dataset to multiple wpf controls everywhere in the system.

Hopefully we can get a fix on this important issue for 3.5

Luis

Please, approve the bug!

Fri, 26 Dec 2008 07:46:02 Z

Please, approve the bug!
The link:
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=389689

Description:
When you're binding to a more than one DataView on the same table directly, event notifcations stops working.

Strangelly, this issue doesn't occurs when binding via WinForm's BindingSource and specifiyng DataSet as source and table name as member.

Some links:
other problems related to dataView:
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=314279
(my report, no reaction, just closed):
-and-
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=361248

My previous report (no reaction, just closed):
https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=339740&SiteID=212

Threads on MSDN Forums: // I'll create new one soon, see the comments
http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/33442f0d-96a2-402c-91c4-d4c7b991c93a
-and-
http://forums.msdn.microsoft.com/en-US/wpf/thread/737889e6-6315-4d72-9563-8104b5e0429f

P.S.
This post will be duplicated on another threads, because the bug touches implementation of binding at WPF and WinForms.

Please, help me contact directly with ADO.NET or WPF Testing teams. I've feedbacked this bug first time 8 month ago and there is no reaction but "there is no workaround".

my mail: sinix DOT 2005 AT gmail DOT com.

Thanks!

TableAdapterManager.Connection.ConnectionString

Tue, 13 Oct 2009 20:00:52 Z

Hi!

I'm currently trying to use a typed TableAdapterManager, but I need to change the connection string that is originally supplied (the one I set in the designer) at runtime. I tried setting the TableAdapterManager.Connection.ConnectionString property, thinking that it would propagate that value to each TableAdapter. This did not happen, only 1 of the 3 TA's connectionstrings was changed, the other 2 still had the value I supplied in the designer.

What am I doing wrong? Should I manually set each connection string for each TA? If so, what is the purpose of TableAdapterManager.Connection?

Thank you!

Handle SqlInjection in DataView.RowFilter

Mon, 16 Nov 2009 11:49:16 Z

Hi,

How to handle sql injection in row filter property.

table.DefaultView.RowFilter =

"NAME LIKE '" + filterString + "%'";

is filterString can be injected??
and if yes how can this handled?

Akram MellIce

Make transactiones between tables

Tue, 10 Nov 2009 21:49:57 Z

hi
am making an inventory application with vb.net 2008 and SQL SERVER 2008 and i have this table of ITEMS and Orders , what i want is when i order a certain quantity , it will automatically be subtracted from the quantity of the item in the items table , ho is that done ?
and how to allow null for certain columns ?