CODEWITHSUNDEEP
c#sql-serverado.net
claws
claws

Reputation: 54100

Using SqlDataAdapter to insert a row

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.

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.

Upvotes: 25

Views: 76365

Answers (3)

Nathan Baulch
Nathan Baulch

Reputation: 20683

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

Upvotes: 48

Manu
Manu

Reputation: 29143

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)

Upvotes: 7

Rajesh Kumar Ranjan
Rajesh Kumar Ranjan

Reputation: 11

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.

Upvotes: -6

Related Questions