ASP.NET的SQLHelper最佳写法

来源：互联网发布：php zip 文件夹编辑：程序博客网时间：2024/05/24 23:14
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();                    }                }            }        }    }}
0 0