做的这个程序，可以确保所有数据的操作要么都成功，要么都失败！
注意，所的 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

关于ADO.Net连接池（Connection Pool）的一些个人见解 ADO.NET非连接类（一）关于DataTable、DataColumn和DataRow对象的创建

评论 (0) All