c#调用存储过程

http://www.cnblogs.com/zxtceq/p/5956808.html

存储过程（Stored Procedure）即用来存储数据表操作的一个过程，是把对数据表操作的方法存储到一起的一个对象，是存储在数据库中。

优点：1.降低网络传输数据量：通过存储过程的名称和参数传递即可调用执行，不用传输sql。

2.执行效率高：sqlserver会事先将存储过程编译成可执行的二进制代码，运行存储过程时，无需再次编译。

3.封装性：也可叫模块式编程，将实现某种功能的多条sql封装到一个对象中，可多次重复调用，可移植性强。

4.安全：可针对不同的权限的用户使用不同的存储过程。

下面讲C#如何调用写好的存储过程，下面是对数据操作封装几个函数

//执行对表的增删改操作的sql语句
public static int ExecuteCommand(string text)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    int i = cmd.ExecuteNonQuery();
                    return i;
                }               
            }            
        }

public static int ExecStoredProcedure(string procName, params SqlParameter[] parameters)
        {
            int rtn = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {                   
                    SqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = procName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters);
                        rtn= cmd.ExecuteNonQuery();
                        st.Commit();
                        return rtn;
                    }
                    catch(SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }                                       
                }
            }
        }
        public static int ExecuteStoredProcedure(string procName,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = procName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
            
        }

推荐用第三种方式，用到了事务处理，若只要有一条不通过全部回滚，避免脏数据的产生。也可以在存储过程中使用事务，后续会继续更新...

protected void Button1_Click(object sender, EventArgs e)
    {
        string mainName = "圆梦组";
        string detailName="刘能|赵本山|沈燕|欧阳锋";
        string detailAge="23|32|18|19";
        int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string mainName = "起航组2";
        string detailName="段毅2|乔峰2|杨过2|李莫愁2";
        string detailAge="18|28|jj|35";
        SqlParameter[] sps = new SqlParameter[] { 
        new SqlParameter("@mainName",mainName),
            new SqlParameter("@detailNameStr",detailName),
             new SqlParameter("@detailAgeStr",detailAge)
        };
        try
        {           
            int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        string mainName = "起航组2";
        string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
        string detailAge = "18|28|jj|35";
        SqlParameter[] sps = new SqlParameter[] { 
        new SqlParameter("@mainName",mainName),
            new SqlParameter("@detailNameStr",detailName),
             new SqlParameter("@detailAgeStr",detailAge)
        };
        try
        {
            int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }

protected void Button1_Click(object sender, EventArgs e)
    {
        string mainName = "圆梦组";
        string detailName="刘能|赵本山|沈燕|欧阳锋";
        string detailAge="23|32|18|19";
        int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string mainName = "起航组2";
        string detailName="段毅2|乔峰2|杨过2|李莫愁2";
        string detailAge="18|28|jj|35";
        SqlParameter[] sps = new SqlParameter[] { 
        new SqlParameter("@mainName",mainName),
            new SqlParameter("@detailNameStr",detailName),
             new SqlParameter("@detailAgeStr",detailAge)
        };
        try
        {           
            int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        string mainName = "起航组2";
        string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
        string detailAge = "18|28|jj|35";
        SqlParameter[] sps = new SqlParameter[] { 
        new SqlParameter("@mainName",mainName),
            new SqlParameter("@detailNameStr",detailName),
             new SqlParameter("@detailAgeStr",detailAge)
        };
        try
        {
            int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }