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,.