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.
- 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", T开发者_如何学编程ype.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.
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);
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)
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.
精彩评论