Using SqlDataAdapter to insert a row

Question

I want to insert a row into the Database using SqlDataAdapter. I've 2 tables (Custormers & Orders) in CustomerOrders database and has more than thousand records. I want to create a GUI (TextBoxes) for adding new customer & orders into the Database to their respective tables.

• How should I do it?

I guess the method that is usually followed is

dataAdapter = new SqlDataAdapter (sqlQuery, conn); dataSet = new DataSet(); da.Fill(dataSet); 

Now take the values from textboxes (or use DataBinding) to add a new row into the dataSet and call

  da.Update(dataSet); 

But the Question is Why should I fetch all other records into dataSet using da.Fill(dataSet ) in the first place? I just want to add a single new record.

For this purpose what I'm doing is, Creating the schema of the Database in the DataSet. like this:

  DataSet customerOrders = new DataSet("CustomerOrders");    DataTable customers = customerOrders.Tables.Add("Customers");   DataTable orders = customerOrders.Tables.Add("Orders");    customers.Columns.Add("CustomerID", Type.GetType("System.Int32"));   customers.Columns.Add("FirstName", Type.GetType("System.String"));   customers.Columns.Add("LastName", Type.GetType("System.String"));   customers.Columns.Add("Phone", Type.GetType("System.String"));   customers.Columns.Add("Email", Type.GetType("System.String"));    orders.Columns.Add("CustomerID", Type.GetType("System.Int32"));   orders.Columns.Add("OrderID", Type.GetType("System.Int32"));   orders.Columns.Add("OrderAmount", Type.GetType("System.Double"));   orders.Columns.Add("OrderDate", Type.GetType("System.DateTime"));    customerOrders.Relations.Add("Cust_Order_Rel", customerOrders.Tables["Customers"].Columns["CustomerID"], customerOrders.Tables["Orders"].Columns["CustomerID"]);    

I used DataBinding to bind these columns to respective text boxes. Now I'm confused! What should I do next? How to use Insert command? Because I didn't give any dataAdapter.SelectCommand so dataAdapter.Update() wont work I guess. Please suggest a correct approach.

Answer 1

Set the select command with a "0 = 1" filter and use an SqlCommandBuilder so that the insert command is automatically generated for you.

var sqlQuery = "select * from Customers where 0 = 1"; dataAdapter = new SqlDataAdapter(sqlQuery, conn); dataSet = new DataSet(); dataAdapter.Fill(dataSet);  var newRow = dataSet.Tables["Customers"].NewRow(); newRow["CustomerID"] = 55; dataSet.Tables["Customers"].Rows.Add(newRow);  new SqlCommandBuilder(dataAdapter); dataAdapter.Update(dataSet);