我可以将DataTable传递给我的SQL语句(INSERT)吗?
问题描述:
假设我有以下结构:我可以将DataTable传递给我的SQL语句(INSERT)吗?
SQL表
[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作为参数传递给我的程序?
谢谢。
答
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()
}
真的不需要在标题中包含诸如“C#”之类的东西。这就是标签的用途。 – 2011-01-28 02:57:14
原谅我...... – yonan2236 2011-01-28 03:23:19