C# .NET - how can I update sdf database from dataset in .net C#?

Asked By Sean Salem on 12-May-11 01:03 PM
Hello you'll,

I have two .sdf databases and I need to merge them. So, I loaded the data from the two files into datasets and then  called the dataset.merge(); function. Now, I have the merge dataset ready but I am not sure how I am going to update the sdf file from the dataset. Please help I have been trying for a while .  The problem is how do I get the data in the dataset into an sdf file.

using VS 2010 and C# SqlCE 3.5, 

Thanks in advance, 
Sean
Peter Bromberg replied to Sean Salem on 12-May-11 01:16 PM
You have to issue the proper SQL commands for CREATE TABLE, and then you can pass the merged DataSet into a SqlCeDataAdapter with the correct INSERT command and call its Update Method. You have to create the new table first.
Sean Salem replied to Peter Bromberg on 12-May-11 02:04 PM
Thank you so much Mr. Bromberg for your reply,
Here is what I am doing, the sdf database has around 90 tables, so first I get all the tables' names and insert them into a datatable. Then, I do the following to create the dataset, 

for( int i = 0; i<dataset.tables["table_name"].Rows.count; i++)
{    
   //dataset.table has the names of the tables in one col ; getting the table name
     string tableName = dataset.tables[" table_name "].rows[i] [" table_name "].tostring();
     firstDataSet.Tables.Add(tableName);
     CeAdapter = new SqlCeDataAdapter("select * from "+tableName,Conn);
     CeAdapter.fill(firstDataSet.Tables[tableName]);
}
 //after the loop, firstDataSet has the 90 tables with their data. Now, after I do some adding and deleting to the data in the dataset,  How can I put the data back in the sdf database. The merge function is working on the dataset. I just need to update the sdf file from the dataset.  I did use the CeAdapter.update(firstDataset) but I am getting the following error
(" Update unable to find TableMapping['Table'] or DataTable 'Table'

Thanks a lot 

Jitendra Faye replied to Sean Salem on 13-May-11 02:23 AM
I have taken two con. object for connecting with 2 sdf database .

and also i have taken two dataset ds1 and ds2, which will hold value from different database.

here I have  read data from ds2 and then appent to ds1.

finally using update() method of dataadapter, i have inserted data into database. 

try this code-


string stConnection1 = "constring1";
string stConnection2 = "constring2";
SqlConnection cn1 = new SqlConnection(stConnection1 ); SqlCommand cmd1= new SqlCommand("select * from employees",cn1); SqlCeDataAdapter da1= new SqlCeDataAdapter (cmd1); SqlCommandBuilder cb = new SqlCommandBuilder(da1);

     SqlConnection cn2 = new SqlConnection(stConnection2 );
     SqlCommand cmd2= new SqlCommand("select * from employees",cn2);
     SqlCeDataAdapter da2= new SqlCeDataAdapter (cmd2);





DataSet ds1= new DataSet(); DataSet ds2 = new DataSet(); da1.Fill(ds1,"Employees");
     da2.Fill(ds2,"Employees");


  




int limit = ds2.Tables["Employees"].Rows.Count;

for (int i = 0; i < limit; i++)

{

DataRow NewR = ds1.Tables["Employees"].NewRow();

NewR.ItemArray = ds2.Tables["Employees"].Rows[i].ItemArray;

ds1.Tables["Employees"].Rows.Add(NewR);

}

da1.Update(ds, "Employees");


try this code and let me know.

Sean Salem replied to Jitendra Faye on 13-May-11 12:51 PM
Good morning Vickey,

Thank you so much for your replay,
I did  us the code but not exactly because I have around 90 tables in sdf database file. However, I got the following exception
(" Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information").
I am going to post my code here including your code. 

Please take a look at it and let me know. I am really stuck. I tried to explain the code. 
Thanks a lot
protected void Merge_Datasets()
       {
           //connection string to the first sdf file  selected by the user
           
string dbConString = (connString)      
            DataSet dataset = new DataSet("dataset");
      //Data table to hold the names of the tables in the sdf database
           dataset.Tables.Add("Names");
           SqlCeConnection connection = new SqlCeConnection(dbConString);
           SqlCeDataAdapter CeAdapter = new SqlCeDataAdapter("selecttable_name from information_schema.tables",                  connection);

           //loading the names of the tables
           CeAdapter.Fill(dataset.Tables["Names"]);

         //Dataset to hold the data from first sdf database
           DataSet firstDataset = new DataSet("firstDataset");
        //Loop to build sql query and populate every table in the dataset from te sdf file  
         for (int i = 0; i < dataset.Tables["Names"].Rows.Count; i++)
           {
               string tableName =dataset.Tables["Names"].Rows[i]["table_name"].ToString();
               firstDataset.Tables.Add(tableName);
               CeAdapter = new SqlCeDataAdapter("select * from " +tableName, connection);
               SqlCeCommandBuilder cb = new SqlCeCommandBuilder(CeAdapter);
               CeAdapter.Fill(firstDataset.Tables[tableName]);
           }

           firstDataset.AcceptChanges();
          //Connection string to the second sdf database
          string  sourceConn =( connString)
        //Repeat the same steps to populate the second dataset
          DataSet sourceDataset = new DataSet("sourceDataset");
          sourceDataset.Tables.Add("Table_Name");
          connection = new SqlCeConnection(sourceConn);
          CeAdapter = new SqlCeDataAdapter("select table_name from information_schema.tables", sourceConn);
          CeAdapter.Fill(sourceDataset.Tables["Table_name"]);


          //Dataset to hold the data from the second sdf database
          DataSet secondDataset = new DataSet("secondDataset");
          for (int i = 0; i <sourceDataset.Tables["Table_name"].Rows.Count; i++)
          {
              string tableName =sourceDataset.Tables["Table_name"].Rows[i]["table_name"].ToString();//get
the table name to be used in the select statement

              secondDataset.Tables.Add(tableName);
              CeAdapter = new SqlCeDataAdapter("select * from " +tableName, connection);
              CeAdapter.Fill(secondDataset.Tables[tableName]);

          }
 //**I added this loop from your post, but I think the merge function does the same thing
           //for(int j = 0; j<secondDataset.Tables[80].Rows.Count;j++)
           //{
           //    DataRow NewR = firstDataset.Tables[80].NewRow();
           //    NewR.ItemArray =  secondDataset.Tables[80].Rows[j].ItemArray;
           //    firstDataset.Tables[80].Rows.Add(NewR);
           //}

          secondDataset.AcceptChanges();
       //calling the merge function and just doing it on one table for now
          firstDataset.Tables[80].Merge(secondDataset.Tables[80]);
          firstDataset.AcceptChanges();
     //diplaying the data in a datagrid to make sure the dataset has the merged data; and it is working until here
          RadGrid1.DataSource = firstDataset.Tables[80];
          RadGrid1.DataBind();
//trying to update the sdf file so i am reassigning the connection to the adapter; x to see how many records got affected 
          connection = new SqlCeConnection(dbConString);
          CeAdapter = new SqlCeDataAdapter(null, connection);
          SqlCeCommandBuilder cb3 = new SqlCeCommandBuilder(CeAdapter);
           int x =    CeAdapter.Update(firstDataset, "visit");// function returning zero 
       
      }

Jitendra Faye replied to Sean Salem on 16-May-11 06:18 AM
I have given solution for one table only, if you have more table then first you have to get all tables and using loop
you can insert data into table of seconf database.

Use this query to get all table names of database.

 USE YourDBName
GO

SELECT
*
FROM sys.Tables
GO

This will return all the tables in the database which user have created.

Sean Salem replied to Sean Salem on 16-May-11 10:26 AM
Good Morning Vickey,

Thanks again for the help, my code is working and please disregard the other replay. I appreciate your help a lot.

Have a great day,
Sean
Jitendra Faye replied to Sean Salem on 17-May-11 05:46 AM
you welcom ,if u hv any more issue then let me know,.