下面是用DataSet一次插入多条数据,自动生成Sql语句,但比如总共插入10条,当插入五条时出现错误,怎么取消前五条的插入,然后报告哪一条出现错误??public bool InsertOrder(OrderData Order)
{
dsCommand.InsertCommand = GetInsertCommand();
dsCommand.Update(Order, "table_Orders");
return true; //or
return false;
}
private SqlCommand GetInsertCommand()
{
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = insertCommand.Parameters;
sqlParams.Add(new SqlParameter("@OrderId", SqlDbType.Int,4,"OrderId"));
sqlParams.Add(new SqlParameter("@Color", SqlDbType.NVarChar, 10,"Color"));
return insertCommand;
}
{
dsCommand.InsertCommand = GetInsertCommand();
dsCommand.Update(Order, "table_Orders");
return true; //or
return false;
}
private SqlCommand GetInsertCommand()
{
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = insertCommand.Parameters;
sqlParams.Add(new SqlParameter("@OrderId", SqlDbType.Int,4,"OrderId"));
sqlParams.Add(new SqlParameter("@Color", SqlDbType.NVarChar, 10,"Color"));
return insertCommand;
}
{
this.dbConnection.BeginTransaction();
1、子表删除
2、主表删除
this.dbConnection.Commit();}
catch
{
this.dbConnection.Rollback();
}
BEGIN TRAN
Ins_A
Del_B
Ins_c
if(@@ERROR>0)
ROLLBACK
ELSE
COMMIT
写在C#中:
try
{
//Ins_A
//Del_B
//Ins_c
myTrans.Commit();
}
catch
{
myTrans.Rollback();
}
/// 【方法02】:执行多个SQL语句
/// </summary>
/// <param name="sqlArray">动态数组:需要执行的多个SQL语句</param>
public static bool RunMultiSQL(System.Collections.ArrayList sqlArray)
{
if(sqlArray.Count < 1) return true; bool success = false; SqlConnection tmpConn = Common.MySettings.MyConn;
SqlCommand tmpCmm = new SqlCommand();
tmpCmm.Connection = tmpConn;
tmpConn.Open(); SqlTransaction tmpTrans = tmpConn.BeginTransaction();
tmpCmm.Transaction = tmpTrans;
try
{
for(int i=0;i<sqlArray.Count;i++)
{
tmpCmm.CommandText = sqlArray[i].ToString();
tmpCmm.ExecuteNonQuery();
} tmpTrans.Commit(); success = true;
Common.ShowMsg("保存成功!");
}
catch(System.Exception ex)
{
tmpTrans.Rollback();
Common.ShowMsg("保存失败!");
Common.ShowMsg(ex.ToString());
}
finally
{
tmpTrans.Dispose();
tmpConn.Close();
}
return success;
}
执行插入的. 这样怎么用事务,并可以知道是哪一条记录出错吗?
public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open(); SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans; // Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans; try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}