C# 使用 SqlDataAdapter 插入一行

Using SqlDataAdapter to insert a row

提问by claws

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.

我想使用 SqlDataAdapter 在数据库中插入一行。我在 CustomerOrders 数据库中有 2 个表（Custormers & Orders）并且有超过一千条记录。我想创建一个 GUI (TextBoxes)，用于将新客户和订单添加到数据库中到各自的表中。

• 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.

但问题是为什么我应该首先使用 da.Fill(dataSet ) 将所有其他记录提取到 dataSet 中？我只想添加一个新记录。

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

为此，我正在做的是，在 DataSet 中创建数据库的架构。像这样：

  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.

我使用 DataBinding 将这些列绑定到相应的文本框。现在我很困惑！接下来我该怎么做？如何使用插入命令？因为我没有给任何 dataAdapter.SelectCommand 所以 dataAdapter.Update() 我猜不会工作。请提出正确的方法。

采纳答案by Nathan Baulch

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

使用“0 = 1”过滤器设置选择命令并使用SqlCommandBuilder，以便为您自动生成插入命令。

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

回答by Manu

You can fill the dataSetwith an empty set e.g.:

您可以dataSet用空集填充，例如：

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 SqlCommandobject directly for insertion. (Even better, use LINQ to SQL or any other ORM)

对于这种情况，虽然最好不要使用SqlDataAdapter. 而是SqlCommand直接使用对象进行插入。（更好的是，使用 LINQ to SQL 或任何其他 ORM）

回答by Rajesh Kumar Ranjan

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.

我必须做第一次。你可以试试看 。它运作良好。