ASP.NET的SQLHelper最佳写法

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace Common
{
    public sealed class SQLHelper
    {
        //数据库连接字符串
        private readonly static string ConnStr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
        
        /// <summary>
        /// 返回所影响的条数
        /// </summary> 
        public static int ExecNonquery(string sql,SqlParameter[] parameter) 
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        if (parameter != null)
                        {
                            cmd.Parameters.AddRange(parameter);
                        }
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                RecordLog.Record(e.Message, e.StackTrace.ToString());
                return -1;
            }
        }


        /// <summary>
        /// 返回首行首列
        /// </summary> 
        public static object ExecScalar(string sql, SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        if (parameter != null)
                        {
                            cmd.Parameters.AddRange(parameter);
                        }
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception e)
            {
                RecordLog.Record(e.Message, e.StackTrace.ToString());
                return null;
            }
        }


        /// <summary>
        /// 返回结果集
        /// </summary> 
        public static DataSet ExecDataSet(string sql, SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        if (parameter != null)
                        {
                            cmd.Parameters.AddRange(parameter);
                        }
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception e)
            {
                RecordLog.Record(e.Message, e.StackTrace.ToString());
                return null;
            }
        }


        /// <summary>
        /// 返回一张表
        /// </summary> 
        public static DataTable ExecDataTable(string sql, SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        if (parameter != null)
                        {
                            cmd.Parameters.AddRange(parameter);
                        }
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception e)
            {
                RecordLog.Record(e.Message, e.StackTrace.ToString());
                return null;
            }
        }


        /// <summary>
        /// 分页存储过程
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FieldName">字段名</param>
        /// <param name="wheres">where条件</param>
        /// <param name="order">只能是desc or asc</param>
        /// <param name="PageSize">每页条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="TotalCount">总页码</param>
        /// <param name="PageIdORField">指定字段来分页</param>
        /// <param name="OrderField">排序指定的字段</param>
        public static DataSet LinkProce(string TableName, string FieldName, string wheres, string order, string PageIdORField, string OrderField, ref int PageSize, ref int PageIndex)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "sp_PagingTabs";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@TableName", TableName);
                    cmd.Parameters.AddWithValue("@FieldName", FieldName);
                    cmd.Parameters.AddWithValue("@where", wheres);
                    cmd.Parameters.AddWithValue("@Order", order);
                    cmd.Parameters.AddWithValue("@OrderField", OrderField);
                    cmd.Parameters.AddWithValue("@PageIdORField", PageIdORField);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@PageIndex", PageIndex);


                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds);
                        return ds;
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        ds.Dispose();
                        conn.Close();
                    }
                }
            }
        }
    }
}