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

问题描述

我想插入一行到使用SqlDataAdapter的数据库。我有2个表（Custormers和订单）的CustomerOrders数据库，拥有超过一千记录。我想创建一个图形用户界面（文本框）增加新客户和放大器;订单到数据库中，以它们各自的表。

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

现在从文本框取值（或使用数据绑定）来添加一个新行到DataSet，并呼吁

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

  da.Update(dataSet);

但问题是，我为什么要所有其他记录读取到数据集摆在首位使用da.Fill（数据集）？我只是想添加一个新的记录。

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.

为此我正在做的是，建立在DataSet中的数据库的模式。像这样的：

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

我使用数据绑定绑定这些列各自的文本框。 现在我很困惑！我应该怎么办？如何使用插入命令？因为我没有给任何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过滤器命令，并使用<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx">SqlCommandBuilder从而使INSERT命令会为您自动生成的。

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"].Add(newRow);

new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(dataSet);

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