在 ADO.NET 中向 SQL Server 表添加行而不需要硬编码 SQL 的最直接方法是什么？

发布于 2024-09-13 20:42:21 字数 763 浏览 3 评论 0原文

我想知道最好/最有效/常见的方法是使用 C# 和 ADO.NET 向 SQL Server 表添加行。我当然知道我可以为此创建一条 SQL 语句，但首先，目标表架构可能会有所不同，因此我想保持灵活性，其次，有太多列我不想编码和维护手动执行此操作。因此，我目前使用 SqlCommandBuilder 与 SQLDataAdapter 一起自动为我创建正确的插入语句，如下所示：

var dataAdapter = new SqlDataAdapter("select * from sometable", _databaseConnection);
new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataTable);

// ... add row to dataTable, fill fields from some external file that 
// ... includes column names as well, 
//.... add some more field values not from the file, etc. ...

dataAdapter.Update(dataTable);

尽管我不需要它们做任何事情（尤其是首先从表中获取所有记录），但这似乎效率很低考虑到那里甚至可能已经有一百万条记录）。使用一些选择语句，例如 select * from sometable where 1=2 可以工作，但这似乎不是一个非常干净的方法。我想对此有一些我不知道的不同解决方案。

谢谢，
蒂莫

原文

I am wondering what the best / most efficient / common way is to add a row to an SQL Server table using C# and ADO.NET. I know of course that I can just create an SQL statement for that, but first, the destination table schema might vary, so I want to keep this flexible, and second, there are so much columns that I do not want to code and maintain this manually. So I currently use a SqlCommandBuilder that is automatically creating the proper insert statement for me, together with an SQLDataAdapter, like this:

var dataAdapter = new SqlDataAdapter("select * from sometable", _databaseConnection);
new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataTable);

// ... add row to dataTable, fill fields from some external file that 
// ... includes column names as well, 
//.... add some more field values not from the file, etc. ...

dataAdapter.Update(dataTable);

This seems pretty inefficient though to first grab all the records from the table even though I do not need them for anything (especially considering that there might even already be a million records in there). Using some select statement like select * from sometable where 1=2 would work, but it does not seem like a very clean approach. I imagine there is some different solution for this that I am just not aware of.

Thanks,

Timo

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荒岛晴空 2024-09-20 20:42:21

我认为插入行的最佳方法是通过 ADO.NET 命令对象使用存储过程。

如果您要插入大量数据并使用 SQL Server 2008，则可以使用用户定义的表类型将 DataTable 对象传递给存储过程。

在 SQL 中：

CREATE TYPE SAMPLE_TABLE_TYPE --
AS
field1 VARCHAR(255)
field2 VARCHAR(255)

CREATE STORED PROCEDURE insert_data
AS
@data Sample_TABLE_TYPE
BEGIN
INSERT INTO table1 (field1, field1) 
SELECT username, password FROM @data;

在 .NET 中：

DataTable myTable = new DataTable();
myTable.Columns.Add(new DataColumn("field1", typeof(string));
myTable.Columns.Add(new DataColumn("field1", typeof(string));
SqlCommand command = new SqlCommand(conn, CommandType.StoredProcedure);
command.Parameters.Add("@data", myTable);
command.ExecuteNonQuery();

如果数据还包含更新，则可以使用 SQL Server 2008 中使用的新 MERGE 函数在同一过程中高效地执行插入和更新。

但是，如果创建用户定义的表类型和创建存储过程的工作量太大，并且您需要一个完整的动态解决方案，我会坚持使用您现有的解决方案，并建议使用

Where 1 = 0

附加到 SQL 文本的内容。

I think the best way to insert rows is by using Stored Procedures through the ADO.NET command object.

If you are inserting massive amounts of data and are using SQL Server 2008 you can pass DataTable objects to a stored procedure by using a User-Defined Table Types.

In SQL:

CREATE TYPE SAMPLE_TABLE_TYPE --
AS
field1 VARCHAR(255)
field2 VARCHAR(255)

CREATE STORED PROCEDURE insert_data
AS
@data Sample_TABLE_TYPE
BEGIN
INSERT INTO table1 (field1, field1) 
SELECT username, password FROM @data;

In .NET:

DataTable myTable = new DataTable();
myTable.Columns.Add(new DataColumn("field1", typeof(string));
myTable.Columns.Add(new DataColumn("field1", typeof(string));
SqlCommand command = new SqlCommand(conn, CommandType.StoredProcedure);
command.Parameters.Add("@data", myTable);
command.ExecuteNonQuery();

If you data also contains updates you can use the new MERGE function used in SQL Server 2008 to efficiently perform both inserts and updates in the same procedure.

However, if creating User-Defined Table Types and creating stored procedures is too much work, and you need a complete dynamic solution I would stick with what you have, with the recommendation of using the