风影ASP.NET基础教学 9 数据访问

在ASPNET里，我们需要和数据库进行交互。我们必须编写一个通用的类库。而且我们需要写一个和数据库无关的程序。

于是我们有了DBHelper。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Globalization;
using System.Web.UI.WebControls;

namespace ASPNETTeach5
{
    /// <summary>
    /// 数据库操作命令委托
    /// </summary>
    /// <param name="dbCommand"></param>
    /// <returns></returns>
    public delegate object CommandDelegate(DbCommand dbCommand);
    /// <summary>
    /// DbDataReader命令委托
    /// </summary>
    /// <param name="dbDataReader"></param>
    public delegate void DBDataReaderDelegate(DbDataReader dbDataReader);
    public class DbHelper
    {
        private static DbHelper instance;
        private ConnectionStringSettings connectionStringSettings = null;
        private DbProviderFactory dbProviderFactory = null;

        public static string DataConnenctionString = "con";

        public DbHelper() {
            connectionStringSettings = ConfigurationManager.ConnectionStrings[DataConnenctionString];

            if (connectionStringSettings != null) {
                if (!string.IsNullOrEmpty(connectionStringSettings.ConnectionString) && !string.IsNullOrEmpty(connectionStringSettings.ProviderName))
                {
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
                }
            }
        }

        public DbHelper(string dbConnectionSettingName) {
            if (string.IsNullOrEmpty(dbConnectionSettingName)) {
                dbConnectionSettingName = DataConnenctionString;
            }
            connectionStringSettings = ConfigurationManager.ConnectionStrings[DataConnenctionString];
            if (connectionStringSettings != null)
            {
                if (!string.IsNullOrEmpty(connectionStringSettings.ConnectionString) && !string.IsNullOrEmpty(connectionStringSettings.ProviderName))
                {
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
                }
            }
        }

        /// <summary>
        /// 获取Dbhelper对象
        /// </summary>
        public static DbHelper Instance {
            get {
                if (instance == null)
                    instance = new DbHelper();
                return instance;
            }
        }
        /// <summary>
        /// 获取链接串
        /// </summary>
        public string ConnectString {
            get {
                return connectionStringSettings.ConnectionString;
            }
        }

        /// <summary>
        /// 提供者字符串
        /// </summary>
        public string ProviderString {
            get { return connectionStringSettings.ProviderName; }
        }
        /// <summary>
        /// 获取工厂
        /// </summary>
        public DbProviderFactory ProviderFactory { get { return dbProviderFactory; } }



        /// <summary>
        /// 创建链接
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection() {
            if (dbProviderFactory == null)
                return null;
            else
            {
                DbConnection dbConnection = dbProviderFactory.CreateConnection();

                dbConnection.ConnectionString = connectionStringSettings.ConnectionString;
                return dbConnection;
            }
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType,string sql) {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch
                {
                    return -1;
                }
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd);
        }

        /// <summary>
        /// 带参数的非查询语句
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType,string sql,DbParameter[] pars){
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteNonQuery();
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd, pars);
        }

        /// <summary>
        /// 执行带参数与委托命令的查询语句，并返回相关的委托命令
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cd">Cmd委托</param>
        /// <param name="pars">参数集合</param>
        /// <returns></returns>
        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate cd, DbParameter[] pars) {
            using (DbConnection dbCnn = CreateConnection()) {
                using (DbCommand cmd = dbProviderFactory.CreateCommand()) {
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    cmd.Connection = dbCnn;
                    cmd.Parameters.AddRange(pars);
                    dbCnn.Open();
                    return cd(cmd);

                }
            }
        }
        /// <summary>
        /// 执行带参数与委托命令的查询语句，并返回相关的委托命令
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cd">Cmd委托</param>
        /// <returns></returns>
        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = dbCon;
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }
        private object ExecuteCmdCallback(CommandType commandType,   CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = dbCon;
                    cmd.CommandType = commandType;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }
        public bool GetReadData(string sql, DBDataReaderDelegate readdelegate)
        {
            bool result = false;
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader datareader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    readdelegate(datareader);
                    return true;
                }
            };
            result = (bool)ExecuteCmdCallback(CommandType.Text, sql, cd);
            return result;
        }


        public DataTable GetDataTable(CommandType commandType, string sql) {

            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Locale = CultureInfo.InvariantCulture;
                    dt.Load(dr);
                    return dt;
                }
            };
            return ExecuteCmdCallback(CommandType.Text, sql, cd) as DataTable;
        }

        /// <summary>
        /// 获取第一行第一列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetScalar(string sql) {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            object value = ExecuteCmdCallback(CommandType.Text, sql, cd);
            if (value == null)
                return "";
            return value.ToString();
        }


        /// <summary>
        /// 获取第一行第一列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetObject(string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            return ExecuteCmdCallback(CommandType.Text, sql, cd);

        }

        public DataSet GetDataSet(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataAdapter da = dbProviderFactory.CreateDataAdapter())
                {
                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.InvariantCulture;
                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    return ds;
                }
            };
            return (DataSet)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
    }
}

 

我们如何使用呢。首先我们需要在配置文件里，配置数据库连接

<configuration>
  <connectionStrings>
    <add name="con" connectionString="server=.;database=BookManagement;uid=sa;pwd=sa;pooling=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>
    <system.web>
      <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>

 

使用方式

DbHelper.Instance.GetDataTable(System.Data.CommandType.Text, "select * from books");

我们后面的内容需要这个dbhelper来进行。