通用数据库操作方法（存储过程篇）

 #region 通用存储过程查询，返囬DataSet
    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="procName">存储过程名</param>
    /// <param name="parameters">存储过程的参数</param>
    /// <param name="tableName">表名</param>
    /// <returns>返回DataSet</returns>
    public DataSet SelectDS(string procName, SqlParameter[] parameters, string tableName)
    {
        DataSet ds = null;
        try
        {

            con = new SqlConnection(conStr);
            cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            cmd.Parameters.Clear();
            if (parameters != null)
            {
                foreach (SqlParameter par in parameters)
                {
                    cmd.Parameters.Add(par);
                }
            }
            ds = new DataSet();
            da.Fill(ds, tableName);
            con.Close();
        }
        catch (Exception ex)
        {
            throw new ApplicationException("取数据错误！", ex);
        }
        finally
        {

        }
        return ds;
    }
    #endregion

    #region 通用存储过程，进行增删改操作,返回int型
    /// <summary>
    /// 传入存储过程和参数，执行增删改
    /// </summary>
    /// <param name="procName">procName:存储过程名</param>
    /// <param name="paras">paras:参数数组</param>
    /// <returns>1：成功；-1：失败</returns>
    public int RunSql(string procName, params SqlParameter[] paras)
    {
        int dt = -1;
        try
        {
            con = new SqlConnection(conStr);
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = con;
            if (paras != null)
            {
                foreach (SqlParameter sp in paras)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            con.Open();
            cmd.ExecuteNonQuery();
            dt = 1;
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            con.Close();
        }
        return dt;
    }
    #endregion

    #region 通用存儲過程查詢，返囬DataReader類型
    public SqlDataReader SelectDR(string procName, SqlParameter[] parameters)
    {
        SqlDataReader dr = null;
        try
        {
            con = new SqlConnection(conStr);
            cmd = new SqlCommand();
            cmd.Connection = this.con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Parameters.Clear();
            if (parameters != null)
            {
                foreach (SqlParameter par in parameters)
                {
                    cmd.Parameters.Add(par);
                }
            }
            con.Open();
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            throw new ApplicationException("取数据错误！", ex);
        }
        return dr;
    }
    #endregion

    #region  通用存儲過程查詢，返囬DataTable類型
    /// <summary>
    ///函数输入存储过程，输出一个结果集（DATATABLE）
    /// </summary>
    /// <param name="procName">sql：传入的sql语句</param>
    /// <param name="paras">paras:存储过程的参数数组</param>
    /// <returns>返回DataTable</returns>
    public DataTable GetTable(string procName, params SqlParameter[] paras)
    {
        DataTable dt = null;
        try
        {
            con = new SqlConnection(conStr);
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = con;
            ds = new DataSet();
            if (paras != null)
            {
                foreach (SqlParameter sp in paras)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            da = new SqlDataAdapter(cmd);
            con.Open();
            da.Fill(ds, procName);
            dt = ds.Tables[procName];
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            con.Close();
        }
        return dt;
    }

    #endregion