conn.Open();
OleDbCommand comm=conn.CreateCommand();
OleDbTransaction tran=conn.BeginTransaction();
comm.Connection=conn;
comm.Transaction=tran;
int count=ds.Tables[0].Rows.Count;
DataRow dr;
string id,name,sql;
byte[] photo;
for(int i=0;i<count;i++)
{
dr=ds.Tables[0].Rows[i];
id=dr[0].ToString().Trim();
name=dr[1].ToString().Trim();
photo=(byte[])dr[2];
sql="Insert Into Users (ID,Name,Photo) Values (@id,@name,@photo)"; comm.Parameters.Add("@id",id);
comm.Parameters.Add("@name",name);
comm.Parameters.Add("@photo",OleDbType.VarBinary,photo.Length).Value=photo; comm.CommandText=sql;//这句肯定不对,应该怎么写?
comm.ExecuteNonQuery();
}
tran.Commit();
OleDbCommand comm=conn.CreateCommand();
OleDbTransaction tran=conn.BeginTransaction();
comm.Connection=conn;
comm.Transaction=tran;
int count=ds.Tables[0].Rows.Count;
DataRow dr;
string id,name,sql;
byte[] photo;
for(int i=0;i<count;i++)
{
dr=ds.Tables[0].Rows[i];
id=dr[0].ToString().Trim();
name=dr[1].ToString().Trim();
photo=(byte[])dr[2];
sql="Insert Into Users (ID,Name,Photo) Values (@id,@name,@photo)"; comm.Parameters.Add("@id",id);
comm.Parameters.Add("@name",name);
comm.Parameters.Add("@photo",OleDbType.VarBinary,photo.Length).Value=photo; comm.CommandText=sql;//这句肯定不对,应该怎么写?
comm.ExecuteNonQuery();
}
tran.Commit();
conn.Open();
OleDbCommand comm=conn.CreateCommand();
OleDbTransaction tran=conn.BeginTransaction();
comm.Connection=conn;
comm.Transaction=tran;
int count=ds.Tables[0].Rows.Count;
DataRow dr;
string id,name,sql;
byte[] photo;
for(int i=0;i<count;i++)
{
dr=ds.Tables[0].Rows[i];
id=dr[0].ToString().Trim();
name=dr[1].ToString().Trim();
photo=(byte[])dr[2];
sql="Insert Into Users (ID,Name,Photo) Values (@id,@name,@photo)"; comm.CommandText=sql;//可能你会觉得这一句应该放这里。不过也是不对的。
//如果sql是不带参数的,那就没问题。问题是要带参数,该怎么处理? comm.Parameters.Add("@id",id);
comm.Parameters.Add("@name",name);
comm.Parameters.Add("@photo",OleDbType.VarBinary,photo.Length).Value=photo;
comm.ExecuteNonQuery();
}
tran.Commit();
try
{
sqlCommand.Transaction = sqlTransaction;
foreach (string sql in sqls)
{ sqlCommand.CommandText = sql;
sqlCommand.ExecuteNonQuery();
}
sqlTransaction.Commit();
}
catch (Exception)
{
sqlTransaction.Rollback();
throw;
}
finally
{
sqlTransaction.Dispose();
sqlCommand.Dispose();
}
问题的关键是字符串sql是带参数的,你这样写的结果跟我上面是一样的。。
批量更新可以这样
SqlDataAdapter dataAdapter = new SqlDataAdapter(SQL, CONNECTION);
string insertSQL = "INSERT INTO [Employee]([name], [age]) VALUES(@name, @age)";SqlCommand insertCmd = new SqlCommand(insertSQL, CONNECTION);
insertCmd.Parameters.Add(new SqlParameter("@name", SqlDbType.Char, 32, "name"));//name是DataTable中对应的列名
insertCmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Real, 8, "age"));dataAdapter.InsertCommand = insertCmd;
insertCmd.UpdatedRowSource = UpdateRowSource.None;dataAdapter.UpdateBatchSize = 0;
dataAdapter.Update(DataTable);
conn.Open();
OleDbCommand comm=conn.CreateCommand();
OleDbTransaction tran=conn.BeginTransaction();
comm.Connection=conn;
comm.Transaction=tran;
int count=ds.Tables[0].Rows.Count;
DataRow dr;
string id,name,sql;
byte[] photo;
for(int i=0;i<count;i++)
{
dr=ds.Tables[0].Rows[i];
id=dr[0].ToString().Trim();
name=dr[1].ToString().Trim();
photo=(byte[])dr[2];
sql="Insert Into Users (ID,Name) Values ('{0}''{1})";
sql=string.Format(sql,id,name);
comm.CommandText=sql; comm.ExecuteNonQuery();
}
tran.Commit();
只需加一句:
comm.Parameters.Clear();谢谢各位,散分了