自用 笔记 一套偷懒用的 DAL层 可访问不同数据库

web.config 数据库链接字符串

  <connectionStrings>
    <add name="JYWeb"   connectionString="Server=.;Integrated Security=false;Pooling=False;user id=sa;password=123456;initial catalog=JYWebTest;"/>
    <add name="OldSys"  connectionString="Server=.;Integrated Security=false;Pooling=False;user id=sa;password=123456;initial catalog=JYDB20190903;"/>
  </connectionStrings>

DAL层 片段

using JYSH.DAL.DALHandler;
using System.Configuration;
using System.Data;

public class IndexDAL
{
        static string connStrJYWeb = ConfigurationManager.ConnectionStrings["JYWeb"].ConnectionString;//数据库1
        static string connStrOldSys = ConfigurationManager.ConnectionStrings["OldSys"].ConnectionString;//数据库2

        public static DataTable GetTeachers(int pageSize, int pageIndex, string Grade = "", string Subject = "")
        {
            string where = "[IsValid]=1 AND [BrandID]=1 ";
            if (!string.IsNullOrWhiteSpace(Grade)) where += " AND [StudySectionName] LIKE '%" + Grade + "%'";
            if (!string.IsNullOrWhiteSpace(Subject)) where += " AND [SubjectName] LIKE '%" + Subject + "%'";
            SqlStrFactory.ConnStr = connStrJYWeb;//数据库1
            DataTable dt = SqlStrFactory.GetPageData("[T_Teacher]", "[CreateDate] ASC", "", where, pageSize, pageIndex);
            return dt;
        }

        public static DataTable GetIsShowCompetition(int top)
        {
            SqlStrFactory.ConnStr = connStrOldSys;//数据库2
            DataTable dt = SqlStrFactory.GetData("[JYSH_CompetitionResults]", top,
                            "[CompetitionResultsID],[CompetitionResultsName],[ImageUrl],[ImagesUrlInfo],[CreateDate]",
                            "[IsValid]=1 AND [IsShow]=1", "[Sort] DESC,[CompetitionResultsID] DESC");
            return dt;
        }
}

SqlStrFactory ：制造SQL语句字符串的工厂

using JYSH.DAL.SqlHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace JYSH.DAL.DALHandler
{
    /// <summary>
    /// 制造SQL语句字符串的工厂
    /// </summary>
    public class SqlStrFactory
    {
        public static string ConnStr = string.Empty;//链接字符串，链接不同的数据库，配置于Web.Config中...

        #region 增
        /// <summary>
        /// 直接执行INSERT语句,返回添加条数
        /// </summary>
        /// <param name="sqlStr">INSERT语句</param>
        /// <returns>添加条数</returns>
        public static int INSERT(string sqlStr)
        {
            return ExecuteNonQuery(sqlStr);
        }
        public static int INSERT(string sqlStr, SqlParameter[] sqlparas)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        public static int ExecuteNonQuery(string sqlStr)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteNonQuery(sqlStr);
        }
        public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteNonQuery(sqlStr, sqlparas);
        }
        /// <summary>
        /// 直接执行INSERT语句，返回新ID
        /// insert一条数据，返回自增的id值：insert into table([NAME])values ('名字');Select @@Identity;
        /// </summary>
        /// <param name="sqlStr">INSERT语句</param>
        /// <returns>回新ID</returns>
        public static int INSERT_Return_NewId(string sqlStr)
        {
            return Convert.ToInt32(ExecuteScalar(sqlStr));
        }
        public static int INSERT_Return_NewId(string sqlStr, SqlParameter[] sqlparas)
        {
            return Convert.ToInt32(ExecuteScalar(sqlStr, sqlparas));
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlStr)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteScalar(sqlStr);
        }
        public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteScalar(sqlStr, sqlparas);
        }
        /// <summary>
        /// 增加一条数据,返回添加条数
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="TableName">表名</param>
        /// <returns>添加条数</returns>
        public static int Add<T>(string TableName, T model)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
            StringBuilder strSql = new StringBuilder();//SQL语句
            StringBuilder column = new StringBuilder();//列字段
            StringBuilder columnvalue = new StringBuilder();//列字段对应的值
            strSql.AppendFormat("INSERT INTO {0}(", TableName);
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                if (pi.GetValue(model, null) == null)
                    continue;

                column.AppendFormat(",{0}", info.Name);
                columnvalue.AppendFormat(",@{0}", info.Name);//添加字段

                SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
                paralist.Add(para);
            }
            strSql.AppendFormat("{0}) VALUES ({1});", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));

            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteNonQuery(strSql.ToString(), paralist.ToArray());
        }
        /// <summary>
        /// 增加一条数据,返回新的ID
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="TableName">表名</param>
        /// <returns>新的ID</returns>
        public static int Add_Return_NewId<T>(string TableName, T model)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
            StringBuilder strSql = new StringBuilder();//SQL语句
            StringBuilder column = new StringBuilder();//列字段
            StringBuilder columnvalue = new StringBuilder();//列字段对应的值
            strSql.AppendFormat("INSERT INTO {0}(", TableName);
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                if (pi.GetValue(model, null) == null)
                    continue;

                column.AppendFormat(",{0}", info.Name);
                columnvalue.AppendFormat(",@{0}", info.Name);//添加字段

                SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
                paralist.Add(para);
            }
            strSql.AppendFormat("{0}) VALUES ({1});SELECT @@Identity;", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));

            DbHelperSQL.ConnStr = ConnStr;
            var obj = DbHelperSQL.ExecuteScalar(strSql.ToString(), paralist.ToArray());
            return int.Parse(obj.ToString());
        }
        #endregion 增

        #region 删
        /// <summary>
        /// 直接执行DELETE语句,返回删除条数
        /// </summary>
        /// <param name="sqlStr">DELETE语句</param>
        /// <returns>删除条数</returns>
        public static int DELETE(string sqlStr)
        {
            return ExecuteNonQuery(sqlStr);
        }
        public static int DELETE(string sqlStr, SqlParameter[] sqlparas)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        /// <summary>
        /// 删除数据,返回删除条数
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="strWhere"></param>
        /// <returns>删除条数</returns>
        public static int Delete(string TableName, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("DELETE FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }

            DbHelperSQL.ConnStr = ConnStr;
            int rows = DbHelperSQL.ExecuteNonQuery(strSql.ToString());
            return rows;
        }
        #endregion 删

        #region 改
        /// <summary>
        /// 直接执行UPDATE语句，返回修改条数
        /// </summary>
        /// <param name="sqlStr">UPDATE语句</param>
        /// <returns>修改条数</returns>
        public static int UPDATE(string sqlStr)
        {
            return ExecuteNonQuery(sqlStr);
        }
        public static int UPDATE(string sqlStr, SqlParameter[] sqlparas)
        {
            return ExecuteNonQuery(sqlStr, sqlparas);
        }
        /// <summary>
        /// 更新一条数据，返回修改条数
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="strWhere">条件</param>
        /// <param name="TableName">表名</param>   
        /// <param name="TableName"></param>
        /// <returns>修改条数</returns>
        public static int Update<T>(string TableName, string strWhere, T model)
        {
            try
            {
                List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
                StringBuilder strSql = new StringBuilder();
                StringBuilder column = new StringBuilder();
                strSql.AppendFormat("UPDATE {0} SET ", TableName);
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    PropertyInfo pi = typeof(T).GetProperty(info.Name);
                    if (pi.GetValue(model, null) == null) continue;

                    column.AppendFormat(",{0}=@{0}", info.Name);//添加字段
                    SqlParameter para = new SqlParameter("@" + info.Name, pi.GetValue(model, null));
                    para.Value = pi.GetValue(model, null);//设置SqlParameter对应Value
                    paralist.Add(para);
                }
                strSql.Append(Regex.Replace(column.ToString(), "^,", ""));//去掉第一个,
                if (!string.IsNullOrEmpty(strWhere))
                    strSql.AppendFormat(" WHERE {0}", strWhere);

                DbHelperSQL.ConnStr = ConnStr;
                int rows = DbHelperSQL.ExecuteNonQuery(strSql.ToString(), paralist.ToArray());
                return rows;
            }
            catch
            {
                return 0;
            }
        }
        #endregion 改

        #region 查
        /// <summary>
        /// 直接执行SELECT语句
        /// </summary>
        /// <param name="strSql">SELECT语句</param>
        /// <returns>数据集</returns>
        public static DataTable SELECT(string strSql)
        {
            return ExecuteReader(strSql);
        }
        public static DataTable SELECT(string strSql, SqlParameter[] sqlparas)
        {
            return ExecuteReader(strSql, sqlparas);
        }
        public static DataTable ExecuteReader(string strSql)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteReader(strSql);
        }
        public static DataTable ExecuteReader(string strSql, SqlParameter[] sqlparas)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteReader(strSql, sqlparas);
        }
        /// <summary>
        /// 获得N行数据,带条件,带排序(单表查询)
        /// </summary>
        /// <param name="TableName">*表名</param>
        /// <param name="Top">N条</param>
        /// <param name="fldName">列</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="fldOrder">排序字符串</param>
        /// <returns>结果集合</returns>
        public static DataTable GetData(string TableName, int Top = 0, string fldName = "", string strWhere = "", string fldOrder = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            if (Top > 0)
            {
                strSql.AppendFormat(" TOP {0} ", Top);
            }
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.AppendFormat(fldName + " FROM {0}", TableName);
            }
            else
            {
                strSql.AppendFormat(" * FROM {0}", TableName);
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (!string.IsNullOrEmpty(fldOrder))
            {
                strSql.Append(" ORDER BY " + fldOrder);
            }

            DbHelperSQL.ConnStr = ConnStr;
            DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
            return dt;
        }
        /// <summary>
        /// 获得分页数据(单表查询)
        /// </summary>
        /// <param name="TableName">*表名</param>
        /// <param name="fldOrder">*排序字段</param>
        /// <param name="fldName">显示字段</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="PageSize">每页条数</param>
        /// <param name="PageIndex">页码</param>
        /// <returns></returns>
        public static DataTable GetPageData(string TableName, string fldOrder, string fldName = "", string strWhere = "", int PageSize = 10, int PageIndex = 1)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("DECLARE @PageSize INT;");
            strSql.AppendLine("DECLARE @PageIndex INT;");
            strSql.AppendLine("SET @PageSize=" + PageSize + ";");
            strSql.AppendLine("SET @PageIndex=" + PageIndex + ";");

            strSql.Append("SELECT ");
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.Append(fldName + " FROM (");
            }
            else
            {
                strSql.Append(" * FROM (");
            }
            strSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) rownum,", fldOrder);
            if (!string.IsNullOrEmpty(fldName))
            {
                strSql.AppendFormat(fldName + " FROM {0}", TableName);
            }
            else
            {
                strSql.AppendFormat(" * FROM {0}", TableName);
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.AppendLine(" WHERE " + strWhere);
            }
            strSql.AppendLine(")a");
            strSql.AppendLine("WHERE rownum > @PageSize * (@PageIndex - 1) AND rownum <= @PageSize * @PageIndex;");

            DbHelperSQL.ConnStr = ConnStr;
            DataTable dt = DbHelperSQL.ExecuteReader(strSql.ToString());
            return dt;
        }
        /// <summary>
        /// 根据条件获取数量
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static int GetCount(string TableName, string strWhere = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.AppendFormat(" WHERE {0}", strWhere);
            }
            object obj = GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 获取最大ID
        /// </summary>
        /// <param name="TableName">*表名</param>
        /// <param name="columnname">*列名</param>
        /// <param name="strWhere">条件</param>
        /// <returns>最大值</returns>
        public static int GetMaxID(string TableName, string columnname, string strWhere = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT MAX({0}) FROM {1}", columnname, TableName);
            if (!string.IsNullOrEmpty(strWhere)) strSql.AppendFormat(" WHERE {0}", strWhere);

            object obj = GetSingle(strSql.ToString());
            try
            {
                if (obj == null)
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
            catch
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取新的ID(最大ID+1)
        /// </summary>
        /// <param name="TableName">*表名</param>
        /// <param name="columnname">*列名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public static int GetNewID(string TableName, string columnname, string strWhere = "")
        {
            int MaxID = GetMaxID(TableName, columnname, strWhere);
            return MaxID + 1;
        }
        /// <summary>
        /// 是否存在该条件记录
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool IsExists(string TableName, string strWhere = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(1) FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return Exists(strSql.ToString());
        }
        #endregion 查

        #region 私有方法
        private static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        private static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static object GetSingle(string strSql)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteScalar(strSql);
        }
        public static object GetSingle(string strSql, params SqlParameter[] cmdParms)
        {
            DbHelperSQL.ConnStr = ConnStr;
            return DbHelperSQL.ExecuteScalar(strSql, cmdParms);
        }
        #endregion

        #region 公用方法
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="columnName">列名称</param>
        /// <param name="TableName">表名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string columnName, string tableName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string tableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + tableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion
    }
}

DbHelperSQL：SqlHelper精简版

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

namespace JYSH.DAL.SqlHelper
{
    /// <summary>
    /// SqlHelper精简版
    /// </summary>
    public class DbHelperSQL
    {
        public static string ConnStr = string.Empty;//链接字符串，链接不同的数据库，配置于Web.Config中...
        private static SqlConnection conn = null;//数据库链接对象 
        private static SqlCommand cmd = null;//数据库操作
        private static SqlDataReader sdr = null;//数据集

        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <returns></returns>
        private static SqlConnection GetConn()
        {
            conn = new SqlConnection(ConnStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 增删改 INSERT|DELETE|UPDATE
        /// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),}; 
        /// </summary>
        public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas = null)
        {
            cmd = new SqlCommand(sqlStr, GetConn());
            if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }

        /// <summary>
        /// 查 SELECT
        /// SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@Name",DBNull.Value),}; 
        /// </summary>
        public static DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas = null)
        {
            DataTable dt = new DataTable();
            try
            {
                cmd = new SqlCommand(sqlStr, GetConn());
                if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch { }
            return dt;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。如：
        /// insert一条数据，返回自增的id值：insert into table([NAME])values ('名字');Select @@Identity;
        /// </summary>
        public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas = null)
        {
            cmd = new SqlCommand(sqlStr, GetConn());
            if (sqlparas != null) cmd.Parameters.AddRange(sqlparas);
            object res = cmd.ExecuteScalar();
            conn.Close();
            return res;
        }
    }
}