常用数据库操作类SQLhelp的写法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace RuPengSite.DataTier
{
    public static class SqlHelper
    {
        public static string ConnStr
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
            }
        }

        public static IDbDataParameter CreateParameter(string name, object value)
        {
            return new SqlParameter(name, value);
        }

        public static int ExecuteNonQuery(string sql, params IDbDataParameter[] parameters)
        {
            using (IDbConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static T ExecuteScalar<T>(string sql, params IDbDataParameter[] parameters)
        {
            using (IDbConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return (T)cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行一个返回标量的sql语句，如果有缓存则直接从缓存中读取
        /// </summary>
        /// <typeparam name="T">标量类型</typeparam>
        /// <param name="cacheKey">缓存唯一标识</param>
        /// <param name="absoluteExpiration">缓存过期时间</param>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="parameters">sql参数</param>
        /// <returns></returns>
        public static T CacheExecuteScalar<T>(string cacheKey, DateTime absoluteExpiration, string sql, params IDbDataParameter[] parameters)
        {
            object obj = HttpRuntime.Cache[cacheKey];
            if (obj != null)
            {
                return (T)obj;
            }
            T value = ExecuteScalar<T>(sql, parameters);
            HttpRuntime.Cache.Insert(cacheKey, value, null, absoluteExpiration, TimeSpan.Zero);
            return value;
        }

        /// <summary>
        /// 执行一个返回DataTable的sql语句，如果有缓存则直接从缓存中读取
        /// </summary>
        /// <param name="cacheKey">缓存唯一标识</param>
        /// <param name="absoluteExpiration">缓存过期时间</param>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="parameters">sql参数</param>
        /// <returns></returns>
        public static DataTable CacheExecuteDataSet(string cacheKey, DateTime absoluteExpiration, string sql, params IDbDataParameter[] parameters)
        {
            object obj = HttpRuntime.Cache[cacheKey];
            if (obj != null)
            {
                return (DataTable)obj;
            }
            DataTable value = ExecuteDataSet(sql, parameters);
            HttpRuntime.Cache.Insert(cacheKey, value, null, absoluteExpiration, TimeSpan.Zero);
            return value;
        }

        public static DataTable ExecuteDataSet(string sql, params IDbDataParameter[] parameters)
        {
            using (IDbConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter((SqlCommand)cmd);
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
    }
}