c#调用存储过程

来源：互联网发布：夜奔什么意思网络内涵编辑：程序博客网时间：2024/06/03 23:05

一、下面讲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);
        }
    }

阅读全文

0 0