开发者

Can I pass a DataTable to my SQL statement (INSERT)?

Suppose I have the following structures:

SQL Table

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

ADO.Net

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

Given my code:

SqlConnection conn = getConnection();
SqlCommand cmd = new SqlCommand("INSERT INTO P开发者_如何学Goerson VALUES(@Name, @Gender, @Age)", conn);
conn.Open();

...

Is there a way to pass my whole DataTable as a parameter in my program?

Thanks.


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜