//获取连接字符串
private static readonly string ConnectionString=ConfigurationManager.["connectionString"].ConnectionString;

//执行增删改的存储过程

public static int ExecuteNonQueryByProcedure(string procedureName, params SqlParameter[] parameters)

{

    using (SqlConnection con = new SqlConnection(DbConnectionString))//创建连接

    using (SqlCommand cmd = new SqlCommand(procedureName, con))//创建命令

    {

        if (parameters != null && parameters.Length > 0)

        {

            cmd.Parameters.AddRange(parameters);//批量添加参数

        }

        //【重要】 设置当前命令为存储过程

        cmd.CommandType = CommandType.StoredProcedure;

 

        con.Open();//打开连接

        return cmd.ExecuteNonQuery();//执行增删改

}

}

//返回首行首列的存储过程

public static object ExecuteScalarByProcedure(string procedureName, params SqlParameter[] parameters)

{

    using (SqlConnection con = new SqlConnection(DbConnectionString))//创建连接

    using (SqlCommand cmd = new SqlCommand(procedureName, con))//创建命令

    {

        if (parameters != null && parameters.Length > 0)

        {

            cmd.Parameters.AddRange(parameters);//批量添加参数

        }

    //【重要】 设置当前命令为存储过程

        cmd.CommandType = CommandType.StoredProcedure;

 

        con.Open();//打开连接

        return cmd.ExecuteScalar();//返回首行首列

    }

}

//逐行读取数据的存储过程

public static SqlDataReader ExecuteReaderByProcedure(string procedureName, params SqlParameter[] parameters)

{

    SqlConnection con = new SqlConnection(DbConnectionString);//创建连接

    using (SqlCommand cmd = new SqlCommand(procedureName, con))//创建命令

    {

        if (parameters != null && parameters.Length > 0)

        {

            cmd.Parameters.AddRange(parameters);//批量添加参数

        }

    //【重要】 设置当前命令为存储过程

        cmd.CommandType = CommandType.StoredProcedure;

        con.Open();//打开连接

        return cmd.ExecuteReader(CommandBehavior.CloseConnection);//返回SqlDataReader  逐行读取  SqlConnection不能在这里释放

    }

}

//返回DataSet的存储过程

public static DataSet ExecuteDataSetByProcedure(string procedureName, params SqlParameter[] parameters)

{

    DataSet ds = new DataSet();

 

    using (SqlConnection con = new SqlConnection(DbConnectionString))//创建连接

    using (SqlDataAdapter ada = new SqlDataAdapter(procedureName, con))//创建适配器

    {

        if (parameters != null && parameters.Length > 0)

        {

            ada.SelectCommand.Parameters.AddRange(parameters);//批量添加参数

        }

        ada.SelectCommand.CommandType = CommandType.StoredProcedure;

 

        ada.Fill(ds);//将查询出的数据填充到DataSet中       

    }

    return ds;

}