1、创建事务的结构
SqlConnection sqlConnection = new SqlConnection();
...初始化连接
// 开启事务
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
// 将事务应用于Command
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.Transaction = sqlTransaction;
try
{
// 利用sqlcommand进行数据操作
...
// 成功提交
sqlTransaction.Commit();
}
catch(Exception ex)
{
// 出错回滚
sqlTransaction.Rollback();
}
2、简单例子
{
DataTable dt = new DataTable();
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection("连接字符串");
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = cnn;
cnn.Open();
System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction();
try
{
foreach(DataRow dr in dt.Rows)
{
cm.CommandText = "update [表] set [数量] = @amount where productID = @productID";
cm.Parameters.Add("@amount",SqlDbType.Int);
cm.Parameters["@amount"].Value = Convert.ToInt32(dr["amount"]);
cm.Parameters.Add("@productID",SqlDbType.VarChar);
cm.Parameters["@productID"].Value = dr["productID"].ToString();
cm.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
cnn.Close();
trans.Dispose();
cnn.Dispose();
}
}
另外一个例子,稍微复杂一点,注意每次执行的时候要清除上一次的参数
/// <summary>
/// 添加,编辑问卷
/// </summary>
/// <param name="questionnaire"></param>
/// <returns></returns>
public int AddQuestionnaire(Entity.Questionnaire.Questionnaire questionnaire)
{
int identity = 0;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnectionStringTrainingOA"].ConnectionString);
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
try
{
//1写入问卷
command.CommandText = "Questionnaire_ADD";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@ID",SqlDbType.Int),
new SqlParameter("@QuestionnaireTitle",SqlDbType.NVarChar)
};
parms[0].Value = questionnaire.ID;
parms[1].Value = questionnaire.QuestionnaireTitle;
command.Parameters.AddRange(parms);
int questionnaireID = Convert.ToInt32(command.ExecuteScalar());
identity = questionnaireID;
//2写入题目
for(int i = 0; i < questionnaire.Questions.Count; i++)
{
command.CommandText = "Question_ADD";
command.Parameters.Clear();
parms = new SqlParameter[] {
new SqlParameter("@ID",questionnaire.ID),
new SqlParameter("@QuestionTitle",questionnaire.Questions[i].QuestionTitle),
new SqlParameter("@Sequence",questionnaire.Questions[i].Sequence),
new SqlParameter("@QuestionTypeID",questionnaire.Questions[i].QuestionTypeID),
new SqlParameter("@QuestionnaireID",questionnaireID)};
command.Parameters.AddRange(parms);
int questionID = Convert.ToInt32(command.ExecuteScalar());
identity = questionID;
if((questionnaire.Questions[i].QuestionType == QuestionType.singlechoice) || (questionnaire.Questions[i].QuestionType == QuestionType.multiplechoice))
{
for(int j = 0; j < questionnaire.Questions[i].ChoiceItems.Count; j++)
{
command.CommandText = "ChoiceItem_ADD";
command.Parameters.Clear();
parms = new SqlParameter[]{
new SqlParameter("@ID",questionnaire.Questions[i].ChoiceItems[j].ID),
new SqlParameter("@ItemContent",questionnaire.Questions[i].ChoiceItems[j].ItemContent),
new SqlParameter("@Options",questionnaire.Questions[i].ChoiceItems[j].Option),
new SqlParameter("@QuestionID",questionID)};
command.Parameters.AddRange(parms);
identity = Convert.ToInt32(command.ExecuteScalar());
}
}
}
transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
transaction.Dispose();
connection.Dispose();
}
return identity;
}
3、SQl server中的事务例子
begin transaction
save transaction A
insert into demo values('BB','B term')
rollback TRANSACTION A
create table demo2(name varchar(10),age int)
insert into demo2(name,age) values('lis',1)
rollback transaction
insert into demo values('BB','B term')
commit TRANSACTION A
commit TRANSACTION
4、注意
1。事务必须在连接打开后BeginTransaction();
2.事务添加到SqlCommand(sqlCommand.Transaction = sqlTransaction; )
3、其他数据库对应做相应调整
4、可以用微软提供的一个dll,很方便.