How to map all changes of a dataset to the original database?

[moonriver]

It seems that a C# program can not update the original database by mapping all the changes to the dataset, but have to explicitly assign the InsertCommand or UpdateCommand properties of a SqlDataAdapter instance. Look at the following codes about inserting a few records into the country table

SqlConnection myConn = new SqlConnection("server=(local)\\DANIEL;Trusted_Conn ection=yes;database=daniel1")
SqlDataAdapter myDataAdapter = new SqlDataAdapter()

// Create the SelectCommand
SqlCommand cmd = new SqlCommand("SELECT * FROM Country where name = 'Spain'", myConn)

myDataAdapter.SelectCommand = cmd

myConn.Open()
DataSet custDS = new DataSet()
myDataAdapter.Fill(custDS, "country")

// Create the InsertCommand
cmd = new SqlCommand("INSERT INTO Country " +
"VALUES(@Name, @Area);", myConn)
cmd.Parameters.Add("@Name", SqlDbType.Char, 20).Value = "India"
cmd.Parameters.Add("@Area", SqlDbType.Int).Value = 2970000
myDataAdapter.InsertCommand = cmd

DataRow myRow
custDS.Tables["country"].Clear()
myRow = custDS.Tables["country"].NewRow()
custDS.Tables["country"].Rows.Add(myRow)

myDataAdapter.Update(custDS, "country")

// Create the InsertCommand
cmd = new SqlCommand("INSERT INTO Country " +
"VALUES(@Name, @Area);", myConn)
cmd.Parameters.Add("@Name", SqlDbType.Char, 20).Value = "Sweden"
cmd.Parameters.Add("@Area", SqlDbType.Int).Value = 440000
myDataAdapter.InsertCommand = cmd

myRow = custDS.Tables["country"].NewRow()

myDataAdapter.Update(custDS, "country")
myConn.Close()

Pay attention to those assignment statements of InsertCommand. Could we try to discard them by just modifying the dataset and then updating the original database by mapping the dataset back

[Martin Marinov]

You can use commandbuilder and skip creating the insertstatement

Taken from MSDN
(ms-help://MS.MSDNQTR.2004APR.1033/cpref/html/frlrfSystemDataSqlClientSqlCom
mandBuilderClassTopic.htm):

string myConnection =
"server=(local)\\DANIEL;Trusted_Connection=yes;dat abase=daniel1";
string mySelectQuery = "SELECT * FROM Country where name = 'Spain'";
string myTableName = "country";
SqlConnection myConn = new SqlConnection(myConnection);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);

myConn.Open();

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, myTableName);

//code to modify data in DataSet here

//Without the SqlCommandBuilder this line would fail
myDataAdapter.Update(ds, myTableName);

myConn.Close();

this will update your database directly

Regards
Martin

"moonriver" <xi*******@yahoo.com> wrote in message
news:4E**********************************@microsof t.com...

It seems that a C# program can not update the original database by mapping all the changes to the dataset, but have to explicitly assign the
InsertCommand or UpdateCommand properties of a SqlDataAdapter instance. Look
at the following codes about inserting a few records into the country table:
SqlConnection myConn = new SqlConnection("server=(local)\\DANIEL;Trusted_Conn ection=yes;database=daniel
1"); SqlDataAdapter myDataAdapter = new SqlDataAdapter();

// Create the SelectCommand.
SqlCommand cmd = new SqlCommand("SELECT * FROM Country where name = 'Spain'", myConn);
myDataAdapter.SelectCommand = cmd;

myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS, "country");

// Create the InsertCommand.
cmd = new SqlCommand("INSERT INTO Country " +
"VALUES(@Name, @Area);", myConn);
cmd.Parameters.Add("@Name", SqlDbType.Char, 20).Value = "India";
cmd.Parameters.Add("@Area", SqlDbType.Int).Value = 2970000;
myDataAdapter.InsertCommand = cmd;

DataRow myRow;
custDS.Tables["country"].Clear();
myRow = custDS.Tables["country"].NewRow();
custDS.Tables["country"].Rows.Add(myRow);

myDataAdapter.Update(custDS, "country");

// Create the InsertCommand.
cmd = new SqlCommand("INSERT INTO Country " +
"VALUES(@Name, @Area);", myConn);
cmd.Parameters.Add("@Name", SqlDbType.Char, 20).Value = "Sweden";
cmd.Parameters.Add("@Area", SqlDbType.Int).Value = 440000;
myDataAdapter.InsertCommand = cmd;

myRow = custDS.Tables["country"].NewRow();

myDataAdapter.Update(custDS, "country");
myConn.Close();
Pay attention to those assignment statements of InsertCommand. Could we try to discard them by just modifying the dataset and then updating the
original database by mapping the dataset back?