Confusion in data adapter fill method process

[ashish101]

I'm doing connectivity of my sql server database with C sharp code.

In sql server 2005 i made two tables person(empid,citycode,name,salary) and city(name,citycode)and entered few records. i haven't set any constraint on any column.

Person
11 Ashish 101 99000
12 XYZ111 110 993939
13 yuuuuu 101 88484
14 hdhfhh 101 89999
15 hdhdhj 111 88999

City
LosAngels 101
london 110
newyork 111

When i try to fetch these two tables in data set's data tables after setting parent child relationship using data relation i get an error on dataadapter's fill method call "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints". Program works fine on setting enforce constraints to false but i want to know the reason. My records in tables satisfy the primary and foreign key relationship please help me out.

My code is below. Please guide me

class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection(@"Data source=ASHOK-268960F0F\ASHISH;initial catalog=db1;integrated security=SSPi");
            SqlDataAdapter da = new SqlDataAdapter("select empid,citycode,name,salary from person select name,citycode from city", con);
            DataSet ds = new DataSet("Person_City_Detaset");
            DataTable dt1 = new DataTable("Person_Table");
            DataTable dt2 = new DataTable("City_Table");
            ds.Tables.Add(dt1);
            dt1.Columns.Add("empid", typeof(int));
            dt1.Columns.Add("citycode", typeof(int));
            dt1.Columns.Add("name", typeof(string));
            dt1.Columns.Add("salary", typeof(double));
            ds.Tables.Add(dt2);
            dt2.Columns.Add("name", typeof(string));
            dt2.Columns.Add("citycode", typeof(int));
            //da.Fill (ds);
            da.TableMappings.Add("Table", "person_table");
            da.TableMappings.Add("Table1", "city_table");
            da.Fill(ds);
            ds.Relations.Add("Person_City_Relation", dt2.Columns[1], dt1.Columns[1]);
            da.Fill(ds); //Error

            Console.WriteLine(ds.Tables.Count);

            for (int i = 0; i < 2; i++)
            {
                Console.WriteLine("Table Name-------"+ds.Tables[i].TableName);
                foreach (DataColumn col in ds.Tables[i].Columns)
                {
                    Console.WriteLine(col.ColumnName);
                }
                Console.WriteLine();
            }
            Console.ReadKey();
        }
    }

[ashish101]

Moreover if i go ahead with

ds.EnforceConstraints = false;

I'm getting 6 records in city_detail data table rather than 3.

I'm unable to get the logic behind fill method.
How does the fill method actually operate.please guide me

[Mitja Bonca]

Try this code:

SqlDataAdapter da1 = new SqlDataAdapter("select empid,citycode,name,salary from person", con);
            SqlDataAdapter da2 = new SqlDataAdapter("select name,citycode from city", con);
            DataSet ds = new DataSet("Person_City_Detaset");
            DataTable dt1 = new DataTable("Person_Table");
            DataTable dt2 = new DataTable("City_Table");
            da1.Fill(dt1);
            da2.Fill(dt2);
            ds.Tables.AddRange(new DataTable[] { dt1, dt2 });
            ds.Relations.Add("Person_City_Relation", dt2.Columns[1], dt1.Columns[1]); //use thisis needed!

[ashish101]

Sorry ,it's not city_detail datatable. It's city_table

[ashish101]

Thank u so much mitja.

This will work fine for sure.
But i want to know why the code written by me fails and is generating error.

i know there is some logic behind fill method and that's vat m looking forward

Please execute using my code. I'm a beginne. I'm not getting the hint

[Mitja Bonca]

1st of all, you cannot have 2 select statements in the sqlDataApapter.
Next, you filled dataSet or 3 times with the same s(wrong) dataAdapter, without specifying which dataTable you want to fill.
See my code, and compare to yours.

[ashish101]

Sir, your code is absolutely correct.

But i have read and executed also that i can have more than one select statement in data adapter.

Two default tables one with name Table and another Table1 will be created.
Table will hold the result of 1st select query and table1 will hold the result of 2nd select query.

Doing da.fill(ds) thrice also is not an error.

If i add two data tables,add them in the data set and then do fill then the no. of tables in the data set will be 4 namely (datatable1tablename),(datatable2tablename),table(defaulttablenamefromdataadapter),table1(defaulttablenamefromdataadapter).

It i go tablemapping then the no. of tables will remain 2.

Please run in the manner i wrote.......I did little bit of R&D.....and found these interesting.

Trust me sir, My question is not silly
Please run the code.

Please, It will be a great help for me

[abelLazm]

dear ashish yes you are right that one DataSet can hold more than one table at a time and you can use them by using their index number or name but the code you have pasted contains a number of useless code line like you don't need to set Datatables or datacolumns when you fill your dataset DbAdapter.Fill(dataSet1, tblName); it will automatically create table names try this code

private static SqlConnection Conn1 = new SqlConnection();
private static SqlDataAdapter DbA = new SqlDataAdapter();
Conn1.ConnectionString = "connextion String";
Conn1.Open(); 
DbA = new SqlDataAdapter("select empid,citycode,name,salary from person",Conn1);
DbA.Fill(dataSet1, "person");
DbA = new SqlDataAdapter("select name,citycode from city",Conn1);
DbA.Fill(dataSet1, "City");
dataSet1.Relations.Add("Person_City_Relation",dataSet1.Tables["person"].Columns[1], dataSet1.Tables["City"].Columns[1]);
//rest of the code

[ashish101]

Thank u so much for ur support.
I asked one of my sir. He told me that fill method is generating such an error bcoz i haven't marked necessary constraints in sql server database table i.e. Person and city.

this code is better than mine.....I know i have done some useless coding but it's part of my R&D....My approach is reach the solution with efforts.

Thank u so much......People.....you all have been of tremendous help..

Ashish

[abelLazm]

:D You are always welcome .... If you got your solution please mark it solved

[ashish101]

Finally, I achieve my objective......

Final Corrected Code(Obviously bcos of u ppl help)..

SqlDataAdapter  da = new SqlDataAdapter("select empid,citycode,name,salary from person",con);
        DataSet dataSet1 = new DataSet();
        da.Fill(dataSet1, "person");

        da = new SqlDataAdapter("select name,citycode from city",con );

        da.Fill(dataSet1, "City");

        dataSet1.Relations.Add("Person_City_Relation",dataSet1.Tables["city"].Columns[1], dataSet1.Tables["person"].Columns[1]);

        da.Fill(dataSet1);

        foreach (DataRow r in dataSet1.Tables[1].Rows)
        {
            Console.WriteLine(r[0] + " "+r[1]);

            foreach (DataRow rr in r.GetChildRows("Person_city_relation"))
            {
                Console.WriteLine(rr[0] + " "+rr[1]+" "+rr[2]);
            }
        }

        Console.ReadKey();

But still if u don't mind.....Please tell me vere my logic went wrong in d original previous pasted code.