如何在 C# 中从 Datatable 动态构建插入命令 [英] How to dynamically build an insert command from Datatable in c#

问题描述

我在从 C# 中的 dataTable 对象动态创建 SQL 插入语句时遇到了一些问题.我想知道实现它的最佳实践.这是我的代码片段，到目前为止我已经尝试过了.

I am facing some problem with making a SQL insert statement dynamically from a dataTable object in c#. I want to know the best practices to make it.Here is my code snippet , I have tried so far.

 String sqlCommandInsert = "INSERT INTO dbo.RAW_DATA(";
 String sqlCommandValue = "";
 foreach (DataColumn dataColumn in dataTable.Columns)
 {
     sqlCommandInsert += dataColumn + ",";
 }
 sqlCommandInsert += sqlCommandInsert.TrimEnd(',');

 sqlCommandInsert += ") VALUE(";

 for (int i = 0; i < dataTable.Rows.Count; i++)
 {
     sqlCommandValue += "'" + dataTable.Rows[i].ItemArray[i] + "',";
 }

 var insertCommand = sqlCommandInsert;
 sqlCommandValue = sqlCommandValue.TrimEnd(',');

 var command = insertCommand + sqlCommandValue + ")";
 dataContext.Database.ExecuteSqlCommand(command); 

任何建议将不胜感激:)问候.

Any suggestion would be appreciated :) Regards.

推荐答案

使用 VALUES 而不是 VALUE.除此之外，您应该始终使用 sql 参数:

Use VALUES instead of VALUE. Apart from that you should always use sql-parameters:

string columns = string.Join("," 
    , dataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName));
string values = string.Join("," 
    , dataTable.Columns.Cast<DataColumn>().Select(c => string.Format("@{0}", c.ColumnName)));
String sqlCommandInsert = string.Format("INSERT INTO dbo.RAW_DATA({0}) VALUES ({1})" , columns, values);

using(var con = new SqlConnection("ConnectionString"))
using (var cmd = new SqlCommand(sqlCommandInsert, con))
{
    con.Open();
    foreach (DataRow row in dataTable.Rows)
    {
        cmd.Parameters.Clear();
        foreach (DataColumn col in dataTable.Columns)
            cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);
        int inserted = cmd.ExecuteNonQuery();
    }
}

这篇关于如何在 C# 中从 Datatable 动态构建插入命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！