如何使用 c# sql server 向现有表添加新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1791042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a new row to an existing table using c# sql server
提问by menachem
I need to write a program. A part of the program is to write to an sql database (.mdf). I had a lot of trouble trying to add a new row to my table (called: "Data"). Here is the code:
我需要写一个程序。程序的一部分是写入 sql 数据库 (.mdf)。尝试向表中添加新行（称为：“数据”）时遇到了很多麻烦。这是代码：
...
DataSet ds = new DataSet();
System.Data.SqlClient.SqlDataAdapter da;
DataRow dRow;
string sql = "SELECT * From Data";
da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
...
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
dRow = ds.Tables["Data"].NewRow();
dRow[0] = "my_data1";
dRow[1] = "my_data2";
dRow[2] = "my_data3";
...
ds.Tables["Data"].Rows.Add(dRow);
da.Update(ds, "Data");
...
I execute this code, but the data didn't get saved to the table. Does anyone know how to enter a new row to the table and to save it?
我执行了这段代码，但数据没有保存到表中。有谁知道如何在表格中输入新行并保存它？
采纳答案by Aaron Daniels
You need an InsertCommandin your SqlDataAdapter.
您需要在 SqlDataAdapter 中插入一个InsertCommand。
EDIT:
编辑：
Here's a quick example I whipped up. There are many others out there, but this should get you going. It assumes that you have a table (dbo.Foos) with two columns (Foo int, Bar nvarchar(50)).
这是我提出的一个快速示例。还有很多其他的，但这应该会让你继续前进。它假设您有一个包含两列 (Foo int, Bar nvarchar(50)) 的表 (dbo.Foos)。
namespace DataAdapterSample
{
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
using (SqlConnection connection = new SqlConnection(@"Data Source=[your server];Initial Catalog=[your database];Integrated Security=true;"))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
dataAdapter.SelectCommand = new SqlCommand("select Foo, Bar from dbo.Foos", connection);
dataAdapter.InsertCommand = new SqlCommand("insert into dbo.Foos (Foo, Bar) values (@Foo, @Bar)", connection);
dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Foo", SqlDbType.Int, 4, "Foo"));
dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Bar", SqlDbType.NText, 50, "Bar"));
using (DataSet dataSet = new DataSet())
{
dataAdapter.Fill(dataSet);
Console.WriteLine("There are {0} rows in the table", dataSet.Tables[0].Rows.Count);
DataRow newRow = dataSet.Tables[0].NewRow();
newRow["Foo"] = 5;
newRow["Bar"] = "Hello World!";
dataSet.Tables[0].Rows.Add(newRow);
dataAdapter.Update(dataSet);
}
//Just to prove we inserted
using (DataSet newDataSet = new DataSet())
{
dataAdapter.Fill(newDataSet);
Console.WriteLine("There are {0} rows in the table", newDataSet.Tables[0].Rows.Count);
}
}
}
Console.ReadLine();
}
}
}
回答by wolfkabal
Two things I'm seeing, you're not initializing your Dataset (ds) or SqlDataAdapter (da) in anyway (unless you're simply leaving that out for post simplification). Part of the initialization of the da will be giving it an actual sql command.
我看到的两件事，无论如何你都没有初始化你的数据集 (ds) 或 SqlDataAdapter (da)（除非你只是为了后期简化而忽略它）。da 的部分初始化将给它一个实际的 sql 命令。
回答by Jeff
Try instead to set
尝试改为设置
dRow = new DataRow();
instead of
代替
dRow = ds.Tables["Data"].NewRow();
and change
和改变
da.Update(ds, "Data");
to
到
da.Update(ds);
回答by Kimani Victor
Force the dataset to Accept changes ie add ds.Acceptchangesto your code
强制数据集接受更改，即添加ds.Acceptchanges到您的代码中
