G
Guest
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_Connection=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
SqlConnection myConn = new SqlConnection("server=(local)\\DANIEL;Trusted_Connection=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