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()
}
精彩评论