我可以将 DataTable 传递给我的 SQL 语句 (INSERT) 吗？

Question

假设我有以下结构：

SQL Table

[Person Table]
-----------------------------------
Name | Gender | Age
-----------------------------------
     |        |

ADO.Net

[DataTable]
--------------------------------
Name | Gender | Age
--------------------------------
John | M      | 18
Peter| M      | 20

给定我的代码：

SqlConnection conn = getConnection();
SqlCommand cmd = new SqlCommand("INSERT INTO Person VALUES(@Name, @Gender, @Age)", conn);
conn.Open();

...

有没有办法将整个 DataTable 作为程序中的参数传递？

谢谢。

Answer 1

string insertFields = "";
string insertValues = "";
        foreach (DataColumn col in dt.Columns)
        {
            insertFields += col.ColumnName + ",";
            insertValues += "@" + col.ColumnName + ",";
        }
//chop off ending comma here with substring.

string sql = "INSERT into T " + insertFields + " VALUES(" + insertValues + ")";


    foreach(DataRow dr in dt.Rows)
    {
        List<SqlParameter> sqlParams = new List<SqlParameter>;
        foreach (DataColumn col in dt.Columns)
        {
            sqlParams.Add(new SqlParameter(col.ColumnName, dr[col.ColumnName]));
        }
        //Execute cmd with sqlparams. May need to set property as sqlParams.ToArray()
    }