使用 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屋!