繁體中文当前位置: 首页 > 图文教程 > .Net技术 > ADO．NET > ado.net事务的使用
ADO．NET 中的 ado.net事务的使用
出处:互联网 整理: 软晨网（RuanChen.com） 发布: 2009-08-14 浏览: 172 ::
收藏到网摘: n/a
做的这个程序，可以确保所有数据的操作要么都成功，要么都失败！
注意，所的 sqlTrans.commit(); sqlTrans.rollBack();都应该放在try{}catch{}中进行错误处理。
#region 所有的事件信息将在一事务中完成
protected void transactionSave()
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["test5ConnectionString"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand();
SqlTransaction sqlTrans;
// 打开数据连接
if (sqlConnection.State == ConnectionState.Closed)
sqlConnection.Open();
sqlTrans = sqlConnection.BeginTransaction();
//sqlcommand对象获取事务的实例
sqlCommand.Transaction = sqlTrans;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "dbo.[sp_Event_Add]";
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@return", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventName", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventType", System.Data.SqlDbType.VarChar, 2));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventCreateTime", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventCreateDate", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventLength", System.Data.SqlDbType.Int, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCEventID", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCEmployID", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCEventName", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCTimeZone", System.Data.SqlDbType.VarChar, 10));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCServerTree", System.Data.SqlDbType.VarChar, 50));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCLearningObject", System.Data.SqlDbType.VarChar, 10));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCMaxNumberStudent", System.Data.SqlDbType.VarChar, 10));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCMaxNumberGuest", System.Data.SqlDbType.VarChar, 10));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCRecordInServer", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCRecordLocal", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCPublishToParticipant", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCVideoOpen", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCRequiresRegistration", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCOpenToParticipant", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCPublishInGuest", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCPhoneParticiption", System.Data.SqlDbType.VarChar, 1));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ICCDepartment", System.Data.SqlDbType.VarChar, 50));
// 返回值
int iRet = -1;
// 用户名
sqlCommand.Parameters["@EventName"].Value = this.TextBox1.Text;
sqlCommand.Parameters["@EventType"].Value = EventType;
sqlCommand.Parameters["@EventCreateTime"].Value = CreateTime;
sqlCommand.Parameters["@EventCreateDate"].Value = this.TextBox3.Text;
sqlCommand.Parameters["@EventLength"].Value = Convert.ToInt32(EventLength);
sqlCommand.Parameters["@ICCEventID"].Value = ICCEventID;
sqlCommand.Parameters["@ICCEmployID"].Value = ICCImployID;
sqlCommand.Parameters["@ICCEventName"].Value = ICCEventName;
sqlCommand.Parameters["@ICCTimeZone"].Value = "8";
sqlCommand.Parameters["@ICCServerTree"].Value = ICCServerTree;
sqlCommand.Parameters["@ICCLearningObject"].Value = "";
sqlCommand.Parameters["@ICCMaxNumberStudent"].Value = this.TextBox4.Text;
sqlCommand.Parameters["@ICCMaxNumberGuest"].Value = this.TextBox4.Text;
sqlCommand.Parameters["@ICCRecordInServer"].Value = ICCRecordInServer;
sqlCommand.Parameters["@ICCRecordLocal"].Value = ICCRecordLocal;
sqlCommand.Parameters["@ICCPublishToParticipant"].Value = ICCPublishToParticipant;
sqlCommand.Parameters["@ICCVideoOpen"].Value = ICCVideoOpen;
sqlCommand.Parameters["@ICCRequiresRegistration"].Value = ICCRequiresRegistration;
sqlCommand.Parameters["@ICCOpenToParticipant"].Value = ICCOpenToParticipant;
sqlCommand.Parameters["@ICCPublishInGuest"].Value = "0";
sqlCommand.Parameters["@ICCPhoneParticiption"].Value = "0";
sqlCommand.Parameters["@ICCDepartment"].Value = "0";
try
{
// 执行UserLogin存储过程
sqlCommand.ExecuteNonQuery();
// 返回值
iRet = Convert.ToInt32(sqlCommand.Parameters["@return"].Value);
if (iRet == 1)
{
//事件信息创建成功后保存用户与事件的相关信息
switch (this.DropDownList2.SelectedValue)
{
case "0":
//按用户添加信息
int right = -1;
int EventID = myEventID();
SqlCommand sqlCommand1 = new SqlCommand();
try
{
// 打开数据连接
if (sqlConnection.State == ConnectionState.Closed)
sqlConnection.Open();
sqlCommand1.Transaction = sqlTrans;
sqlCommand1.Connection = sqlConnection;
sqlCommand1.CommandType = CommandType.StoredProcedure;
sqlCommand1.CommandText = "dbo.[sp_UserEvent_Add]";
sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@return", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EventID", System.Data.SqlDbType.Int, 50));
sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int, 50));
for (int i = 0; i < this.ListBox2.Items.Count; i++)
{
sqlCommand1.Parameters["@EventID"].Value = EventID;
sqlCommand1.Parameters["@UserID"].Value = this.ListBox2.Items[i].Value;
sqlCommand1.ExecuteNonQuery();
}
// 执行UserLogin存储过程
// 返回值
right = Convert.ToInt32(sqlCommand1.Parameters["@return"].Value);
}
catch (SqlException ax)
{
Response.Write(ax.Message);
}
if (right == 1) // 登录成功
{
this.Label6.Text = "事件创建成功！";
}
else
{
//用户信息添加失败。事务回
try
{
sqlTrans.Rollback();
}
catch (Exception ae)
{
this.Label6.Text = ae.Message + "rollback信息" + ae.Message;
}
}
break;
case "1":
//按部门添加信息
if (giveEventToDepartment() == 1)
{
this.Label6.Text = "事件创建成功！";
}
else
{
//Response.Write("<script > alert('添加部门事件失败！请填写必要的信息')<script>");
this.Label6.Text = "添加部门事件失败！请填写必要的信息！";
}
break;
case "2":
//按公司添加信息
if (giveEventToCommpany() == 1)
{
this.Label6.Text = "事件创建成功！";
}
else
{
//Response.Write("<script > alert('添加公司事件失败！请填写必要的信息')<script>");
this.Label6.Text = "添加公司事件失败！请填写必要的信息！";
}
break;
}
}
}
catch (SqlException ax)
{
try
{
sqlTrans.Rollback();
}
catch (Exception ae)
{
this.Label6.Text =ax.Message+"rollback信息"+ ae.Message;
}
}
finally
{
sqlConnection.Close();
}
}
#endregion
评论 (0) All
