用事务处理方式提交roles(角色)

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;

public partial class admin_Massage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            getroles();
        }
    }
    public void getroles()
    {
        DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringMain, CommandType.Text, "select *from Roles").Tables[0];
        CheckBoxList1.DataSource = dt;
        CheckBoxList1.DataTextField = "roleName";
        CheckBoxList1.DataValueField = "roleId";
        CheckBoxList1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //第一步把用户插入到user表中，并返回userid
        SqlParameter[] par = new SqlParameter[]
        {
            new SqlParameter("@userName",txtuserName.Text),
            new SqlParameter("@userPwd",txtUserpwd.Text)
        };
        string sql = "INSERT INTO Users ([userName],[userpwd]) VALUES (@userName,@userPwd) select @@identity";

        int i = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringMain,CommandType.Text, sql, par));
        //Response.Write(i);
        //第二步把userid和rolesid插入到userInRoles表中
        foreach (ListItem lt in CheckBoxList1.Items)
        {
            if (lt.Selected)
            {
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@userid",i),
                    new SqlParameter("@roleid",lt.Value)
                };
                string Sql = @"INSERT INTO [UserInRole]
                               ([userid]
                               ,[roleid])
                         VALUES
                               (@userid
                               ,@roleid)";
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringMain,CommandType.Text, Sql, para);
            }
        }
           
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(SqlHelper.ConnectionStringMain);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "INSERT INTO Users ([userName],[userpwd]) VALUES (@userName,@userPwd) select @@identity";
        cmd.Connection = con;
        SqlParameter[] par = new SqlParameter[]
        {
            new SqlParameter("@userName",txtuserName.Text),
            new SqlParameter("@userPwd",txtUserpwd.Text)
        };
        cmd.Parameters.Add(par[0]);
        cmd.Parameters.Add(par[1]);

        SqlTransaction tran=null ;
        try
        {
            con.Open();//打开数据库
            tran = con.BeginTransaction();//创建事务对象 并实现和连接对象的关联
            cmd.Transaction = tran;//把事务对象和command对象关联
            int i=Convert.ToInt32(cmd.ExecuteScalar());
            foreach (ListItem lt in CheckBoxList1.Items)
            {
                if (lt.Selected)
                {
                   
                    cmd.CommandText = null;
                    cmd.Parameters.Clear();
                    cmd.CommandText = @"INSERT INTO [UserInRole]
                               ([userid]
                               ,[roleid])
                         VALUES
                               (@userid
                               ,@roleid)";
                    cmd.Connection = con;

                    SqlParameter[] para = new SqlParameter[]
                    {
                        new SqlParameter("@userid",i),
                        new SqlParameter("@roleid",lt.Value)
                    };                   
                    cmd.Parameters.Add(para[0]);
                    cmd.Parameters.Add(para[1]);
                    cmd.ExecuteNonQuery();
                   
                }
            }
            tran.Commit();//提交事务
        }
        catch
        {
            tran.Rollback();
        }
        finally
        {
            con.Close();
        }

    }
}