public Boolean UpdateDataTable3(DataTable dt, string strTable)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = commSql.ReturnDbConn(); string a1 = "";
string a2 = "";
string a3 = ""; for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName.ToString().ToUpper() != "ID")
{
a1 = a1 + dt.Columns[i].ColumnName.ToString() + ",";
a2 = a2 + "@" + dt.Columns[i].ColumnName.ToString() + ",";
a3 = a3 + dt.Columns[i].ColumnName.ToString() + "=@" + dt.Columns[i].ColumnName.ToString() + ",";
}
} a1 = a1.Substring(0, a1.Length - 1);
a2 = a2.Substring(0, a2.Length - 1);
a3 = a3.Substring(0, a3.Length - 1); da.InsertCommand = new SqlCommand("Insert Into " + strTable + "(" + a1 + ") Values(" + a2 + ")", conn);
da.InsertCommand.CommandType = CommandType.Text; da.UpdateCommand = new SqlCommand("Update " + strTable + " Set " + a3 + " Where ID=@ID", conn);
da.UpdateCommand.CommandType = CommandType.Text; da.DeleteCommand = new SqlCommand("Delete From " + strTable + " Where ID=@ID", conn);
da.DeleteCommand.CommandType = CommandType.Text;
for (int i = 0; i < dt.Columns.Count; i++)
{ da.InsertCommand.Parameters.Add("@" + dt.Columns[i].ColumnName.ToString(), ReturnDbType(dt.Columns[i].DataType.ToString()), dt.Columns[i].MaxLength, dt.Columns[i].ColumnName); da.UpdateCommand.Parameters.Add("@" + dt.Columns[i].ColumnName.ToString(), ReturnDbType(dt.Columns[i].DataType.ToString()), dt.Columns[i].MaxLength, dt.Columns[i].ColumnName);
da.DeleteCommand.Parameters.Add("@" + dt.Columns[i].ColumnName.ToString(), ReturnDbType(dt.Columns[i].DataType.ToString()), dt.Columns[i].MaxLength, dt.Columns[i].ColumnName);
}
da.DeleteCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id"); try
{
da.Update(dt );
return true;
}
catch (Exception ex)
{
return false;
}
} SqlDbType ReturnDbType(string strTypeName)
{
switch (strTypeName)
{
case "System.String":
return SqlDbType.NVarChar;
break; case "System.Int32":
return SqlDbType.Int;
break; case "System.DateTime":
return SqlDbType.DateTime;
break; case "System.Decimal":
return SqlDbType.Decimal;
break; case "System.Byte":
return SqlDbType.Image;
break; case "System.Boolean":
return SqlDbType.Bit;
break; case "System.Double":
return SqlDbType.Float;
break; case "System.Single":
return SqlDbType.Real;
break; default:
return SqlDbType.NVarChar;
break;
}
}
我想要实现的效果是将一个datatable和表名称传到这个UpdateDataTable3里,就可以将这个datatble里的信息保存到数据库里,每次我只会对一个单表做操作,新增和修改操作都能实现了,现在的问题是对这个datatable的删行处理没有被保存到数据库里,请高人指点一下,谢谢!
da.Update(dt);
两句就搞定。
你循环里面这句去掉试试,删除只要@ID就够了。
你循环里面去掉这句,删除只要@ID就够了,试试吧
还有个问题 dt.Columns[i].MaxLength 怎么返回的都是0啊?