如何在C#中动态构建从Datatable插入命令

Question

我在C#中使用dataTable对象动态生成SQL插入语句时遇到了一些问题。我想知道最佳实践是什么。以下是迄今为止我尝试的代码片段。

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

欢迎提出任何建议 :) 谢谢。

- SM Farhad Ali

---

最佳实践是根本不要这样做，而是使用正确的参数集构建一个准确无误的命令。（顺便说一下，应该是VALUES） - Steve

Answer 1

使用VALUES而不是VALUE。除此之外，您应该始终使用 SQL 参数：

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

- Tim Schmelter

---

注意，对于那些想要复制此代码以根据 DataTable 自动生成插入语句的人： "RAW_DATA" 似乎是 OP 的表名。因此，您不能只是复制并粘贴此代码。应该用 dataTable.TableName 替换。 - Wolfsblvt

变量sqlCommandInsert中的string.Format参数{0}和{1}是否容易受到SQL注入攻击？ - Kunal Mukherjee

@KunalMukherjee：不，该值并未直接用于SQL中，而是将作为SQL参数传递给数据库。SQL语句只是INSERT INTO Table(col1,col2) VALUES(@col1,@col2)。列名col1、col2来自于DataTable的列。因此，如果是由您的代码创建的，则应该是安全的。 - Tim Schmelter

@KunalMukherjee：这取决于您对“动态”的定义。如果用户可以提供列名，并且您不会将其与现有列进行验证，则不安全。如果您自己构建DataTable，则是安全的。 - Tim Schmelter

@KunalMukherjee：客户端传递表格、列和值？那么只有一种安全的方法，就是在执行插入/更新之前对每个表格和列进行数据库验证。您可以在Stackoverflow上创建一个问题。 - Tim Schmelter

Answer 2

   ## Dynamic Update Query from Datatable with Npgsql##
  public string UpdateExecute(DataTable dataTable, string TableName)
{

    NpgsqlCommand cmd = null;
    string Result = String.Empty;

    try
    {            

        if (dataTable.Columns.Contains("skinData")) dataTable.Columns.Remove("skinData");
        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)));

        StringBuilder sqlCommandInsert = new StringBuilder();
        sqlCommandInsert.Append("Update " + TableName + " Set ");

        string[] TabCol = columns.Split(',');
        string[] TabVal = values.Split(',');

        for (int i = 0; i < TabCol.Length; i++)
        {
            for (int j = 0; j < TabVal.Length; j++)
            {
                sqlCommandInsert.Append(TabCol[i] +" = "+ TabVal[i] + ",");
                break;
            }
        }
      string  NpgsqlCommandUpdate= sqlCommandInsert.ToString().TrimEnd(',');
      NpgsqlCommandUpdate += (" where " + TabCol[0] + "=" + TabVal[0]);


        using (var con = new NpgsqlConnection("Server=localhost;Port=5432;uid=uapp;pwd=Password;database=Test;"))
        {
            con.Open();
            foreach (DataRow row in dataTable.Rows)
            {
                cmd = new NpgsqlCommand(NpgsqlCommandUpdate.ToString(), con);
                cmd.Parameters.Clear();
                foreach (DataColumn col in dataTable.Columns)
                    cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);

                Result = cmd.ExecuteNonQuery().ToString();
            }
        }
    }
    catch (Exception)
    {
        Result = "-1";
    }
    return Result;
}   

- vsharma10286

---

你好，欢迎来到 Stack Overflow。请解释一下你的答案如何改进其他给出的答案，因为这并不显而易见。 - bernie

我的回答不是立即的，因为它是一个动态更新语句。之前的回答是插入语句，所以我想如果有人在这里搜索动态插入语句，那么他/她可能也需要动态更新语句。 - vsharma10286