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.

Nathan Baulch · Accepted Answer · 2017-02-06T03:14:32.070

47

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);

edited Feb 06 '17 at 03:14

answered Oct 27 '09 at 14:17

Nathan Baulch

20,233
5
52
56

1

That "WHERE 0 = 1" was a lifesaver. (Loading a media table of 2GB into memory...sigh..) – pkExec Jan 14 '13 at 09:50
5

I was interested in the line `new SqlCommandBuilder(dataAdapter);` - that makes some magic happen so I don't have to script the whole of the INSERT statements. Thanks! – Ian Grainger Mar 02 '15 at 10:34
dataSet.Tables["Customers"].Add(newRow); doesn't compile. "System.Data.DataTable does not contain a definition for 'Add'". Do you mean dataSet.Tables[tableName].Rows.Add(newRow);? – David Klempfner Aug 14 '15 at 04:56
1

WHERE 0=1? I think more elegant is 'SELECT TOP(0)...' – fcm Oct 11 '19 at 21:07
@fcm Both queries should have identical execution plans, but WHERE 1 = 0 has the advantage of being ANSI SQL compliant, while the TOP keyword is vendor-specific. Any RDBMS that understands any version of SQL will understand WHERE 1 = 0. It's a pattern used quite extensively in code-generated SQL queries, too. – Bacon Bits Oct 22 '19 at 13:19

score 7 · Answer 2 · edited Feb 11 '16 at 17:08

7

You can fill the dataSet with an empty set e.g.:

da = new SqlDataAdapter ("SELECT * FROM Customers WHERE id = -1", conn);
dataSet = new DataSet();
da.Fill(dataSet);

Then you add your rows and call update.

For this scenario though it would probably be better not to use SqlDataAdapter. Instead use the SqlCommand object directly for insertion. (Even better, use LINQ to SQL or any other ORM)

edited Feb 11 '16 at 17:08

Mykola

3,343
6
23
39

answered Oct 27 '09 at 14:11

Manu

28,753
28
75
83

I thought of it. But I've a form conatining more than 200 Text Columns. So, I opted this. – claws Oct 27 '09 at 19:40
@Manu this will not work if the Id uses negative values - you might get 1 row returned. Nathans answers assures 0 rows returned. However it still makes a db trip. – Ken Oct 22 '16 at 13:30

score -6 · Answer 3 · edited Nov 03 '12 at 07:53

-6

SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=test;Integrated Security=True");
SqlDataAdapter da=new SqlDataAdapter("Insert Into Employee values("+textBox1.Text+",'"+textBox2.Text+"','"+textBox3.Text+"',"+textBox4.Text+")",con);
DataSet ds = new DataSet();
da.Fill(ds);

I have to do first time. You can try it . It works well.

edited Nov 03 '12 at 07:53

Kjuly

34,476
22
104
118

answered Nov 03 '12 at 07:29

Rajesh Kumar Ranjan

11

this may work but at least I find databinding is clearer and less vulnerable to error for large number of input textboxes and more sturdy against table changes in the database – gg89 Apr 07 '18 at 03:22

Using SqlDataAdapter to insert a row

3 Answers3

Linked

Related