使用 SqlDataAdapter 插入一行 [英] Using SqlDataAdapter to insert a row

问题描述

我想使用 SqlDataAdapter 在数据库中插入一行.我在 CustomerOrders 数据库中有 2 个表(Custormers & Orders)并且有超过一千条记录.我想创建一个 GUI (TextBoxes) 来添加新客户 &命令进入数据库到各自的表中.

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.

• 我该怎么做?

我猜通常遵循的方法是

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

现在从文本框中获取值(或使用 DataBinding)将新行添加到数据集中并调用

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

  da.Update(dataSet);

但问题是为什么我应该首先使用 da.Fill(dataSet) 将所有其他记录提取到 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"]);   

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

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.

推荐答案

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

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

这篇关于使用 SqlDataAdapter 插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！