面向对象编程、模块内高内聚、模块间低耦合、数据库操作工具类

谁借莪１个温暖的怀抱￠ 2023-06-02 10:55 4阅读 0赞

## 一、web.config帮助类(ConfigHelper.cs) ##

1 using System;
      2 using System.Configuration;
      3 using System.Reflection;
      4 
      5 namespace Sam.OA.Common
      6 {
      7     /// <summary>
      8     /// web.config操作类
      9     /// author:陈彦斌
     10     /// 更新时间:2019年9月1日15:50:11
     11     /// 使用前需引用程序集：System.configuration
     12     /// </summary>
     13     public sealed class ConfigHelper
     14     {
     15         /// <summary>
     16         /// 获取系统配置信息
     17         /// </summary>
     18         /// <typeparam name="SystemConfig"></typeparam>
     19         /// <returns></returns>
     20         public static SystemConfig GetAppSettingsAllInfo()
     21         {
     22             try
     23             {
     24                 SystemConfig t = new SystemConfig();
     25                 string[] arrSysCfg = ConfigurationManager.AppSettings.AllKeys;
     26                 string value = string.Empty;
     27                 foreach (var key in arrSysCfg)
     28                 {
     29                     value = CacheHelper.GetAppSettings(key).ToString();
     30                     foreach (PropertyInfo pi in t.GetType().GetProperties())
     31                     {
     32                         if (key.Contains(pi.Name))
     33                         {
     34                             if (!StringUtil.isNullOrBlank(value))
     35                             {
     36                                 pi.SetValue(t, value, null);
     37                             }
     38                         }
     39                     }
     40                 }
     41                 return t;
     42             }
     43             catch(Exception ex)
     44             {
     45                 throw ex; 
     46             }
     47         }
     48         /// <summary>
     49         /// 获取链接字符串
     50         /// </summary>
     51         /// <param name="key"></param>
     52         /// <returns></returns>
     53         public static string GetConnectionString(string key)
     54         {
     55             return ConfigurationManager.ConnectionStrings[key].ConnectionString;
     56         }
     57         /// <summary>
     58         /// 获取AppSettings中配置String信息
     59         /// </summary>
     60         /// <param name="key">键</param>
     61         /// <returns></returns>
     62         public static string GetConfigString(string key)
     63         {
     64             object objValue = CacheHelper.GetCache(key);
     65             if (objValue == null) //缓冲区没有值
     66             {
     67                 objValue = CacheHelper.GetAppSettings(key);
     68                 if (objValue != null)
     69                 {
     70                     CacheHelper.SetCache(key, objValue, DateTime.Now.AddMinutes(180), TimeSpan.Zero);
     71                 }
     72             }
     73             return objValue.ToString();
     74         }
     75         /// <summary>
     76         /// 获取AppSettings中配置Bool信息
     77         /// </summary>
     78         /// <param name="key">键</param>
     79         /// <returns></returns>
     80         public static bool GetConfigBool(string key)
     81         {
     82             object objValue= CacheHelper.GetAppSettings(key);
     83             if (StringUtil.isNullOrBlank(objValue))
     84             {
     85                 try
     86                 {
     87                     bool.Parse(objValue.ToString());
     88                     return true;
     89                 }
     90                 catch
     91                 {
     92                     return false;
     93                 }
     94             }
     95             return false;
     96         }
     97         /// <summary>
     98         /// 获取AppSettings中配置decimal信息
     99         /// </summary>
    100         /// <param name="key"></param>
    101         /// <returns></returns>
    102         public static decimal GetConfigDecimal(string key)
    103         {
    104             object objValue = CacheHelper.GetAppSettings(key);
    105             if (StringUtil.isNullOrBlank(objValue))
    106             {
    107                 try
    108                 {                    
    109                     return decimal.Parse(objValue.ToString());
    110                 }
    111                 catch
    112                 {
    113                     return 0;
    114                 }
    115             }
    116             return 0;
    117         }
    118         /// <summary>
    119         /// 获取AppSettings中配置DateTime信息,可空
    120         /// </summary>
    121         /// <param name="key">键</param>
    122         /// <returns></returns>
    123         public static DateTime? GetConfigDateTime(string key)
    124         {
    125             DateTime? DateTimeNull = null;
    126             object objValue = CacheHelper.GetAppSettings(key);
    127             if (StringUtil.isNullOrBlank(objValue))
    128             {
    129                 try
    130                 {
    131                     return DateTime.Parse(objValue.ToString());
    132                 }
    133                 catch
    134                 {
    135                     return DateTimeNull;
    136                 }
    137             }
    138             return DateTimeNull;
    139         }
    140     }
    141     /// <summary>
    142     /// 系统配置类
    143     /// </summary>
    144     public sealed class SystemConfig
    145     {
    146         /// <summary>
    147         /// 数据库连接字符串
    148         /// </summary>
    149         public string ConnectionString { get; set; }
    150         /// <summary>
    151         /// 数据库类型
    152         /// </summary>
    153         public string dbType { get; set; }
    154         /// <summary>
    155         /// 打印报错SQL语句物理路径
    156         /// </summary>
    157         public string PrintErrorSqlPath { get; set; }
    158         /// <summary>
    159         /// 是否打印
    160         /// </summary>
    161         public string IsPrint { get; set; }
    162         /// <summary>
    163         /// Memcache服务器IP地址和端口
    164         /// </summary>
    165         public string MemcacheServiceList { get; set; }
    166     }
    167 }

## 二、缓存帮助类(CacheHelper.cs) ##

1 using System;
      2 using System.Collections;
      3 using System.Configuration;
      4 using System.Web;
      5 using System.Web.Caching;
      6 
      7 namespace Sam.OA.Common
      8 {
      9     /// <summary>
     10     /// 缓存帮助类
     11     /// author:陈彦斌
     12     /// 更新时间:2019年9月1日16:13:14
     13     /// HttpRuntime.Cache
     14     /// </summary>
     15     public sealed class CacheHelper
     16     {
     17         /// <summary>
     18         /// 获取configuratio节点下appSettings中add的值
     19         /// </summary>
     20         /// <param name="key">AppSettings的键</param>
     21         /// <returns></returns>
     22         public static object GetAppSettings(string key)
     23         {
     24             return ConfigurationManager.AppSettings[key];
     25         }
     26         /// <summary>
     27         /// 获取当前应用程序指定CacheKey的值
     28         /// </summary>
     29         /// <param name="CacheKey">appSettings节点下add中的键</param>
     30         /// <returns></returns>
     31         public static object GetCache(string CacheKey)
     32         {
     33             Cache objCache = HttpRuntime.Cache;
     34             return objCache[CacheKey];
     35         }
     36         /// <summary>
     37         /// 设置数据缓存(慎用)
     38         /// </summary>
     39         /// <param name="CacheKey">键</param>
     40         /// <param name="CacheValue">值</param>
     41         public static void SetCache(string CacheKey,object CacheValue)
     42         {
     43             Cache objCache = HttpRuntime.Cache;
     44             objCache.Insert(CacheKey, CacheValue);
     45         }
     46         /// <summary>
     47         /// 设置数据缓存
     48         /// </summary>
     49         /// <param name="CacheKey">键</param>
     50         /// <param name="CacheValue">值</param>
     51         /// <param name="TimeOut">时间间隔</param>
     52         public static void SetCache(string CacheKey, object CacheValue, TimeSpan TimeOut)
     53         {
     54             Cache objCache = HttpRuntime.Cache;
     55             objCache.Insert(CacheKey, CacheValue, null, DateTime.MaxValue, TimeOut, CacheItemPriority.NotRemovable, null);
     56         }
     57         /// <summary>
     58         /// 设置数据缓存
     59         /// </summary>
     60         /// <param name="CacheKey">键</param>
     61         /// <param name="CacheValue">值</param>
     62         /// <param name="absoluteExpiration">绝对过期时间</param>
     63         /// <param name="slidingExpiration">时间间隔</param>
     64         public static void SetCache(string CacheKey, object CacheValue, DateTime absoluteExpiration, TimeSpan slidingExpiration)
     65         {
     66             Cache objCache = HttpRuntime.Cache;
     67             objCache.Insert(CacheKey, CacheValue, null, absoluteExpiration, slidingExpiration);
     68         }
     69         /// <summary>
     70         /// 设置数据缓存
     71         /// </summary>
     72         /// <param name="CacheKey">键</param>
     73         /// <param name="CacheValue">值</param>
     74         /// <param name="expDate">过期时间</param>
     75         public static void SetCache(string CacheKey, object CacheValue,DateTime expDate)
     76         {
     77             Cache objCache = HttpRuntime.Cache;
     78             objCache.Insert(CacheKey, CacheValue, null, expDate, TimeSpan.Zero);
     79         }
     80         /// <summary>
     81         /// 移除全部缓存
     82         /// </summary>
     83         public static void RemovaAllCache()
     84         {
     85             Cache objCache = HttpRuntime.Cache;
     86             IDictionaryEnumerator CacheEnum = objCache.GetEnumerator();
     87             while (CacheEnum.MoveNext())
     88             {
     89                 objCache.Remove(CacheEnum.Key.ToString());
     90             }
     91         }
     92         /// <summary>
     93         /// 移除指定键的缓存
     94         /// </summary>
     95         /// <param name="CacheKey">键</param>
     96         public static void RemovaAllCache(string CacheKey)
     97         {
     98             Cache objCache = HttpRuntime.Cache;
     99             objCache.Remove(CacheKey);
    100         }
    101     }
    102 }

**Memcache缓存帮助类(MemcacheHelper.cs)**

1 using Memcached.ClientLibrary;
     2 using System;
     3 
     4 namespace Sam.OA.Common
     5 {
     6     /// <summary>
     7     /// Memcache缓存帮助类
     8     /// 作者：陈彦斌
     9     /// 更新时间：2019年9月1日15:48:12
    10     /// </summary>
    11     public sealed class MemcacheHelper
    12     {
    13         private static MemcachedClient memcachedClient;
    14         static MemcacheHelper()
    15         {
    16             //分布式Memcached服务器ip 端口
    17             string strAppMemcached = DbUtil.memcacheServiceList;
    18             if (strAppMemcached==""|| strAppMemcached==null)
    19             {
    20                 throw new Exception("Memcache远程服务器Ip和端口未配置");
    21             }
    22             string[] servers = strAppMemcached.Split(','); //Memcache机器IP
    23             //初始化池
    24             SockIOPool pool = SockIOPool.GetInstance();
    25             pool.SetServers(servers); //关联连接池
    26             pool.InitConnections = 3; //初始化链接
    27             pool.MinConnections = 3; //最小连接数
    28             pool.MaxConnections = 5; //最大连接数
    29             pool.SocketConnectTimeout = 1000; //Socket超时连接时间
    30             pool.SocketTimeout = 3000; //Socket超时时间
    31             pool.MaintenanceSleep = 30; //Socket休眠时间
    32             pool.Failover = true;
    33             pool.Nagle = false;
    34             pool.Initialize(); //初始化
    35             //客户端实例
    36             if (memcachedClient == null)
    37             {
    38                 memcachedClient = new MemcachedClient();
    39             }
    40             memcachedClient.EnableCompression = false; //启动压缩
    41         }
    42         /// <summary>
    43         /// 获取Memcache缓存数据
    44         /// </summary>
    45         /// <param name="CacheKey">键</param>
    46         /// <returns></returns>
    47         public static object GetCache(string CacheKey)
    48         {           
    49             return memcachedClient.Get(CacheKey);
    50         }
    51         /// <summary>
    52         /// 设置Memcache缓存数据
    53         /// </summary>
    54         /// <param name="CacheKey">键</param>
    55         /// <param name="CacheValue">值</param>
    56         public static void AddCache(string CacheKey, object CacheValue)
    57         {
    58             memcachedClient.Add(CacheKey, CacheValue);
    59         }
    60         /// <summary>
    61         /// 设置Memcache缓存数据
    62         /// </summary>
    63         /// <param name="CacheKey">键</param>
    64         /// <param name="CacheValue">值</param>
    65         /// <param name="expDate">过期时间</param>
    66         public static void AddCache(string CacheKey, object CacheValue, DateTime expDate)
    67         {
    68             memcachedClient.Add(CacheKey, CacheValue,expDate);
    69         }
    70         /// <summary>
    71         /// 设置Memcache缓存数据,key存在则更新，否则新增
    72         /// </summary>
    73         /// <param name="CacheKey">键</param>
    74         /// <param name="CacheValue">值</param>
    75         public static void SetCache(string CacheKey, object CacheValue)
    76         {
    77             memcachedClient.Set(CacheKey, CacheValue);
    78         }
    79         /// <summary>
    80         /// 设置Memcache缓存数据,key存在则更新，否则新增
    81         /// </summary>
    82         /// <param name="CacheKey">键</param>
    83         /// <param name="CacheValue">值</param>
    84         /// <param name="expDate">过期时间</param>
    85         public static void SetCache(string CacheKey, object CacheValue, DateTime expDate)
    86         {
    87             memcachedClient.Set(CacheKey, CacheValue, expDate);
    88         }
    89     }
    90 }

## 三、数据库基类(DbBaseTool.cs) ##

1 using System;
      2 using System.Collections.Generic;
      3 using System.Text;
      4 using System.IO;
      5 using System.Data.Common;
      6 using System.Data;
      7 using System.Data.SqlClient;
      8 using System.Data.OracleClient;
      9 using System.Threading;
     10 
     11 namespace Tool
     12 {
     13     /// <summary>
     14     /// 操作数据库基类
     15     /// author:陈彦斌
     16     /// 时间：2019年7月14日23:35:30
     17     /// </summary>
     18     public abstract class DbBaseTool
     19     {
     20         public const string c_where = " WHERE ";
     21         public const string c_where_one_equal_one = " WHERE 1=1 ";
     22         public const string c_where_one_equal_one_and = " WHERE 1=1 AND ";
     23         public const string c_like = " LIKE ";
     24         public const string c_and = " AND ";
     25         public const string c_or = " OR ";
     26         public const string c_equal = " = ";
     27         public const char c_comma_split = ',';
     28     }
     29     /// <summary>
     30     /// 错误信息打印类
     31     /// author:陈彦斌
     32     /// 时间：2019年7月14日23:36:10
     33     /// </summary>
     34     public class PrintSqlTool
     35     {
     36         public static string LogBasePath { get; set; }
     37         public static Queue<string> execSqlQueue = new Queue<string>();
     38         private const string printTxtSqlTemp = "打印时间:{0}\r\nSQL语句:\r\n      {1}\r\n";
     39         static PrintSqlTool()
     40         {
     41             ThreadPool.QueueUserWorkItem(o =>
     42             {
     43                 while (true)
     44                 {
     45                     lock (execSqlQueue)
     46                     {
     47                         if (execSqlQueue.Count > 0)
     48                         {
     49                             PrintSqlToText(LogBasePath,execSqlQueue.Dequeue());
     50                         }
     51                     }
     52                 }
     53             });
     54         }
     55         /// <summary>
     56         /// 打印报错SQL语句
     57         /// </summary>
     58         /// <param name="strPath">物理绝对路径</param>
     59         /// <param name="sql">报错SQL语句</param>
     60         public static void PrintSqlToText(string strPath, string sql)
     61         {
     62             appendStrToTxtFile(strPath, sql);
     63         }
     64         /// <summary>
     65         /// 打印报错SQL语句
     66         /// </summary>
     67         /// <param name="strPath">物理绝对路径</param>
     68         /// <param name="list">报错SQL语句集合</param>
     69         public static void PrintSqlToText(string strPath, List<string> list)
     70         {
     71             StringBuilder sb = new StringBuilder();
     72             foreach (var item in list)
     73             {
     74                 sb.Append(item).Append(";");
     75             }
     76             appendStrToTxtFile(strPath, sb.ToString().TrimEnd(','));
     77         }
     78         /// <summary>
     79         /// 向文本追加字符串
     80         /// </summary>
     81         /// <param name="fileFullPath">物理绝对路径</param>
     82         /// <param name="errStr">报错语句</param>
     83         private static void appendStrToTxtFile(string fileFullPath, string errStr)
     84         {
     85             FileStream fs = null; //文件流
     86             StreamWriter sw = null; //写入流
     87             try
     88             {
     89                 if (File.Exists(fileFullPath)) //判断文件是否存在
     90                 {
     91                     fs = new FileStream(fileFullPath, FileMode.Append); //打开文件搜寻到文件尾
     92                 }
     93                 else
     94                 {
     95                     fs = new FileStream(fileFullPath, FileMode.Create); //创建文件
     96                 }
     97                 sw = new StreamWriter(fs, Encoding.UTF8); //指定写入格式
     98                 sw.Write(string.Format(printTxtSqlTemp,DateTime.Now.ToString(), errStr)); //写入
     99             }
    100             catch (UnauthorizedAccessException err)
    101             {
    102                 throw err;
    103             }
    104             catch (IOException err)
    105             {
    106                 throw err;
    107             }
    108             finally
    109             {
    110                 if (sw != null)
    111                 {
    112                     sw.Close();
    113                 }
    114                 if (fs != null)
    115                 {
    116                     fs.Close();
    117                 }
    118             }
    119         }
    120     }
    121     /// <summary>
    122     /// 数据接口类
    123     /// author:陈彦斌
    124     /// 时间：2019年7月14日23:36:51
    125     /// </summary>
    126     public interface IDbProvider
    127     {
    128         /// <summary>
    129         /// 连接字符串
    130         /// </summary>
    131         string connStr { get; set; }
    132         /// <summary>
    133         /// 初始化 IDbConnection 类的新实例。
    134         /// </summary>
    135         /// <returns></returns>
    136         IDbConnection GetConnection();
    137         /// <summary>
    138         /// 如果给定包含连接字符串的字符串，则初始化 IDbConnection 类的新实例。
    139         /// </summary>
    140         /// <param name="connectionString">用于打开 SQL Server 数据库的连接。</param>
    141         /// <returns></returns>
    142         IDbConnection GetConnection(string connectionString);
    143         /// <summary>
    144         /// 初始化 IDbCommand 类的新实例。
    145         /// </summary>
    146         /// <returns></returns>
    147         IDbCommand GetCommand();
    148         /// <summary>
    149         /// 用查询文本初始化 IDbCommand 类的新实例。
    150         /// </summary>
    151         /// <param name="cmdText">查询的文本。</param>
    152         /// <returns></returns>
    153         IDbCommand GetCommand(string cmdText);
    154         /// <summary>
    155         /// 初始化 IDbCommand 类的新实例。
    156         /// </summary>
    157         /// <param name="connection">数据库链接字符串</param>
    158         /// <param name="transaction">将在其中执行 IDbCommand 的 IDbTransaction。</param>
    159         /// <returns></returns>
    160         IDbCommand GetCommand(IDbConnection connection, IDbTransaction transaction);
    161         /// <summary>
    162         /// 初始化具有查询文本和 IDbConnection 的 IDbCommand类的新实例。
    163         /// </summary>
    164         /// <param name="cmdText">查询的文本。</param>
    165         /// <param name="connection">数据库链接字符串</param>
    166         /// <returns></returns>
    167         IDbCommand GetCommand(string cmdText, IDbConnection connection);
    168         /// <summary>
    169         /// 初始化 IDbCommand 类的新实例。
    170         /// </summary>
    171         /// <param name="cmdText">查询的文本。</param>
    172         /// <param name="connection">数据库链接字符串</param>
    173         /// <param name="transaction">将在其中执行 IDbCommand 的 IDbTransaction。</param>
    174         /// <returns></returns>
    175         IDbCommand GetCommand(string cmdText, IDbConnection connection, IDbTransaction transaction);
    176         /// <summary>
    177         /// 初始化 DbDataAdapter 类的新实例。
    178         /// </summary>
    179         /// <returns></returns>
    180         DbDataAdapter GetDataAdapter();
    181         /// <summary>
    182         /// 初始化 DbDataAdapter 类的新实例，用指定的 DbDataAdapter 作为 DbDataAdapter.SelectCommand 的属性
    183         /// </summary>
    184         /// <param name="selectCommand">一个 IDbCommand（可以是 Transact-SQL SELECT 语句或存储过程），已设置为 DbDataAdapter 的 IDbCommand 属性。</param>
    185         /// <returns></returns>
    186         DbDataAdapter GetDataAdapter(IDbCommand selectCommand);
    187         /// <summary>
    188         /// 使用 IDbCommand 和 IDbConnection 对象初始化 DbDataAdapter 类的一个新实例。
    189         /// </summary>
    190         /// <param name="selectCommandText">一个 System.String，它是将要由 DbDataAdapter 的 IDbCommand 属性使用的 Transact-SQL SELECT 语句或存储过程。</param>
    191         /// <param name="selectConnection">数据库链接字符串</param>
    192         /// <returns></returns> 
    193         DbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection selectConnection);
    194         //添加参数
    195         IDataParameter GetParaneter(string param_name, DbType db_type);
    196         IDataParameter GetParaneter(string param_name, string param_text);
    197         IDataParameter GetParaneter(string param_name, DbType db_type, byte[] fs);
    198         IDataParameter GetParaneter(string param_name, DbType db_type, string param_text);
    199         IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, int para_size);
    200         IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, ParameterDirection ptype);
    201     }
    202     /// <summary>
    203     /// SqlServer数据库
    204     /// author:陈彦斌
    205     /// 时间：2019年7月14日23:37:25
    206     /// </summary>
    207     public sealed class SqlServerProvider : IDbProvider
    208     {
    209         private string _get_conn;
    210         private const string paramhead = "@";
    211         public SqlServerProvider(string strConn)
    212         {
    213             _get_conn = strConn;
    214         }
    215         public string connStr
    216         {
    217             get
    218             {
    219                 return _get_conn;
    220             }
    221 
    222             set
    223             {
    224                 _get_conn = value;
    225             }
    226         }
    227 
    228         public IDbCommand GetCommand()
    229         {
    230             return new SqlCommand();
    231         }
    232 
    233         public IDbCommand GetCommand(string cmdText)
    234         {
    235             return new SqlCommand(cmdText);
    236         }
    237 
    238         public IDbCommand GetCommand(string cmdText, IDbConnection connection)
    239         {
    240             return new SqlCommand(cmdText, (SqlConnection)connection);
    241         }
    242 
    243         public IDbCommand GetCommand(IDbConnection connection, IDbTransaction transaction)
    244         {
    245             return GetCommand(null, connection, transaction);
    246         }
    247 
    248         public IDbCommand GetCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)
    249         {
    250             return new SqlCommand(cmdText, (SqlConnection)connection, (SqlTransaction)transaction);
    251         }
    252 
    253         public IDbConnection GetConnection()
    254         {
    255             return new SqlConnection(this.connStr);
    256         }
    257 
    258         public IDbConnection GetConnection(string connectionString)
    259         {
    260             return new SqlConnection(connectionString);
    261         }
    262 
    263         public DbDataAdapter GetDataAdapter()
    264         {
    265             return new SqlDataAdapter();
    266         }
    267 
    268         public DbDataAdapter GetDataAdapter(IDbCommand selectCommand)
    269         {
    270             return new SqlDataAdapter((SqlCommand)selectCommand);
    271         }
    272 
    273         public DbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection selectConnection)
    274         {
    275             return new SqlDataAdapter(selectCommandText, (SqlConnection)selectConnection);
    276         }
    277 
    278         public IDataParameter GetParaneter(string param_name, string param_text)
    279         {
    280             return new SqlParameter(paramhead + param_name, param_text);
    281         }
    282 
    283         public IDataParameter GetParaneter(string param_name, DbType db_type)
    284         {
    285             return new SqlParameter(paramhead + param_name, db_type);
    286         }
    287 
    288         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text)
    289         {
    290             SqlParameter param = new SqlParameter(paramhead + param_name, db_type);
    291             param.Value = param_text;
    292             return param;
    293         }
    294 
    295         public IDataParameter GetParaneter(string param_name, DbType db_type, byte[] fs)
    296         {
    297             SqlParameter param = new SqlParameter(paramhead + param_name, db_type);
    298             param.Value = fs;
    299             return param;
    300         }
    301 
    302         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, ParameterDirection ptype)
    303         {
    304             SqlParameter param = new SqlParameter(paramhead + param_name, db_type);
    305             param.Value = param_text;
    306             param.Direction = ptype;
    307             return param;
    308         }
    309 
    310         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, int para_size)
    311         {
    312             SqlParameter param = new SqlParameter(paramhead + param_name, db_type);
    313             param.Value = param_text;
    314             param.Size = para_size;
    315             return param;
    316         }
    317     }
    318     /// <summary>
    319     /// Oracle数据库
    320     /// author:陈彦斌
    321     /// 时间：2019年7月14日23:37:50
    322     /// </summary>
    323     public sealed class OracleProvider : IDbProvider
    324     {
    325         private string _get_conn;
    326         public OracleProvider(string strConn)
    327         {
    328             _get_conn = strConn;
    329         }
    330         public string connStr
    331         {
    332             get
    333             {
    334                 return _get_conn;
    335             }
    336 
    337             set
    338             {
    339                 _get_conn = value;
    340             }
    341         }
    342 
    343         public IDbCommand GetCommand()
    344         {
    345             return new OracleCommand();
    346         }
    347 
    348         public IDbCommand GetCommand(string cmdText)
    349         {
    350             return new OracleCommand(cmdText);
    351         }
    352 
    353         public IDbCommand GetCommand(IDbConnection connection, IDbTransaction transaction)
    354         {
    355             return GetCommand(null, connection, transaction);
    356         }
    357 
    358         public IDbCommand GetCommand(string cmdText, IDbConnection connection)
    359         {
    360             return new OracleCommand(cmdText, (OracleConnection)connection);
    361         }
    362 
    363         public IDbCommand GetCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)
    364         {
    365             return new OracleCommand(cmdText, (OracleConnection)connection, (OracleTransaction)transaction);
    366         }
    367 
    368         public IDbConnection GetConnection()
    369         {
    370             return new OracleConnection(this.connStr);
    371         }
    372 
    373         public IDbConnection GetConnection(string connectionString)
    374         {
    375             return new OracleConnection(connectionString);
    376         }
    377 
    378         public DbDataAdapter GetDataAdapter()
    379         {
    380             return new OracleDataAdapter();
    381         }
    382 
    383         public DbDataAdapter GetDataAdapter(IDbCommand selectCommand)
    384         {
    385             return new OracleDataAdapter((OracleCommand)selectCommand);
    386         }
    387 
    388         public DbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection selectConnection)
    389         {
    390             return new OracleDataAdapter(selectCommandText, (OracleConnection)selectConnection);
    391         }
    392 
    393         public IDataParameter GetParaneter(string param_name, string param_text)
    394         {
    395             return new OracleParameter(param_name, param_text);
    396         }
    397 
    398         public IDataParameter GetParaneter(string param_name, DbType db_type)
    399         {
    400             return new OracleParameter(param_name, db_type);
    401         }
    402 
    403         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text)
    404         {
    405             OracleParameter param = new OracleParameter(param_name, db_type);
    406             param.Value = param_text;
    407             return param;
    408         }
    409 
    410         public IDataParameter GetParaneter(string param_name, DbType db_type, byte[] fs)
    411         {
    412             OracleParameter param = new OracleParameter(param_name, db_type);
    413             param.Value = fs;
    414             return param;
    415         }
    416 
    417         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, ParameterDirection ptype)
    418         {
    419             OracleParameter param = new OracleParameter();
    420             param.ParameterName = param_name;
    421             if (db_type == DbType.Int32)
    422             {
    423                 param.OracleType = OracleType.Number;
    424                 param.Value = Convert.ToDecimal(param_text);
    425             }
    426             else if (db_type == DbType.String)
    427             {
    428                 param.OracleType = OracleType.VarChar;
    429                 param.Value = param_text;
    430             }
    431             else
    432             {
    433                 param.DbType = db_type;
    434                 param.Value = param_text;
    435             }
    436 
    437             param.Direction = ptype;
    438             return param;
    439         }
    440 
    441         public IDataParameter GetParaneter(string param_name, DbType db_type, string param_text, int para_size)
    442         {
    443             OracleParameter param = new OracleParameter(param_name, db_type);
    444             param.Value = param_text;
    445             param.Size = para_size;
    446             return param;
    447         }
    448     }
    449 }

## 四、数据库帮助类(DbUtil.cs) ##

1 using System;
      2 using System.Collections.Generic;
      3 using System.Data;
      4 using System.Reflection;
      5 using System.Data.Common;
      6 
      7 namespace Sam.OA.Common
      8 {
      9     /// <summary>
     10     /// 数据库操作类
     11     /// author:陈彦斌
     12     /// 最后更新时间：2019年9月18日22:43:55
     13     /// </summary>
     14     public sealed class DbUtil : DbBaseTool
     15     {
     16         private static object lockHelper = new object();
     17         private static SystemConfig SysCfg;
     18         private static IDbProvider defaultPro;
     19         private static string connectionString { get { return SysCfg.ConnectionString; } }
     20         public static string memcacheServiceList { get { return SysCfg.MemcacheServiceList; } }
     21         public static void resetDbUtil()
     22         {
     23             lock (lockHelper)
     24             {
     25                 if (defaultPro == null)
     26                 {
     27                     try
     28                     {
     29                         SysCfg = ConfigHelper.GetAppSettingsAllInfo();
     30                         if (SysCfg.dbType.ToLower() == "sqlserver")
     31                         {
     32                             defaultPro = new SqlServerProvider(connectionString);
     33                         }
     34                         else if (SysCfg.dbType.ToLower() == "oracle")
     35                         {
     36                             defaultPro = new OracleProvider(connectionString);
     37                         }
     38                         else
     39                         {
     40                             throw new NotImplementedException();
     41                         }
     42                     }
     43                     catch (Exception ex)
     44                     {
     45                         throw ex;
     46                     }
     47                 }
     48             }
     49         }
     50         public static void resetDbUtil(string DbName)
     51         {
     52             lock (lockHelper)
     53             {
     54                 if (defaultPro == null)
     55                 {
     56                     try
     57                     {
     58                         SysCfg = ConfigHelper.GetAppSettingsAllInfo();
     59                         if (SysCfg.dbType.ToLower() == "sqlserver")
     60                         {
     61                             defaultPro = new SqlServerProvider(connectionString);
     62                         }
     63                         else if (SysCfg.dbType.ToLower() == "oracle")
     64                         {
     65                             defaultPro = new OracleProvider(connectionString);
     66                         }
     67                         else
     68                         {
     69                             throw new NotImplementedException();
     70                         }
     71                     }
     72                     catch (Exception ex)
     73                     {
     74                         throw ex;
     75                     }
     76                 }
     77             }
     78         }
     79         static DbUtil()
     80         {
     81             if (defaultPro == null)
     82             {
     83                 resetDbUtil();
     84             }
     85         }
     86         /// <summary>
     87         /// 执行查询SQL语句，返回DataTable
     88         /// </summary>
     89         /// <param name="strSql">查询SQL语句</param>
     90         /// <returns></returns>
     91         public static DataTable QueryDT(string strSql)
     92         {
     93             using (IDbConnection conn = defaultPro.GetConnection())
     94             {
     95                 if (conn.State!=ConnectionState.Open)
     96                 {
     97                     conn.Open();
     98                 }
     99                 try
    100                 {
    101                     PrintErrorStrSql(strSql);
    102                     IDbDataAdapter adap = defaultPro.GetDataAdapter(strSql, conn);
    103                     DataTable dt = new DataTable();
    104                     DataSet ds = new DataSet();
    105                     adap.Fill(ds);
    106                     dt = ds.Tables[0];
    107                     return dt;
    108                 }
    109                 catch (DbException ex)
    110                 {
    111                     throw new Exception(ex.Message);
    112                 }
    113                 finally
    114                 {
    115                     conn.Close();
    116                 }
    117             }
    118         }
    119         /// <summary>
    120         /// 分页嵌套查询
    121         /// </summary>
    122         /// <param name="strSql">查询SQL语句</param>
    123         /// <param name="orderBy">降序字段eg:order by id/order by id desc</param>
    124         /// <param name="start">开始</param>
    125         /// <param name="limit">一页多少条</param>
    126         /// <param name="total">总条数</param>
    127         /// <returns>返回DataTable</returns>
    128         public static DataTable QueryDT(string strSql,string orderBy,int start,int limit,ref int total)
    129         {
    130             using (IDbConnection conn = defaultPro.GetConnection())
    131             {
    132                 if (conn.State != ConnectionState.Open)
    133                 {
    134                     conn.Open();
    135                 }
    136                 try
    137                 {
    138                     string query_sql = string.Format(@"SELECT CSON4.* FROM ( SELECT CSON3.* FROM (
    139             Select CSON2.*, ROW_NUMBER() OVER({1}) as rownum from (
    140                  Select Count(*) over() total_count,CSON.* From ({0}) CSON
    141             ) CSON2  
    142 )  CSON3 where CSON3.rownum<{3}) CSON4 WHERE CSON4.rownum>{2}", strSql, orderBy, (start - 1) * limit, start * limit + 1);
    143                     PrintErrorStrSql(strSql);
    144                     IDbDataAdapter adap = defaultPro.GetDataAdapter(strSql, conn);
    145                     DataTable dt = new DataTable();
    146                     DataSet ds = new DataSet();
    147                     adap.Fill(ds);
    148                     dt = ds.Tables[0];
    149                     if (dt.Rows.Count>0)
    150                     {
    151                         total =Convert.ToInt32(dt.Rows[0]["total_count"]);
    152                     }                    
    153                     return dt;
    154                 }
    155                 catch (DbException ex)
    156                 {
    157                     throw new Exception(ex.Message);
    158                 }
    159                 finally
    160                 {
    161                     conn.Close();
    162                 }
    163             }
    164         }
    165         /// <summary>
    166         /// 分页嵌套查询
    167         /// </summary>
    168         /// <param name="strSql">查询SQL语句</param>
    169         /// <param name="orderBy">降序字段eg:order by id/order by id desc</param>
    170         /// <param name="start">开始</param>
    171         /// <param name="limit">一页多少条</param>
    172         /// <param name="total">总条数</param>
    173         /// <returns>List集合</returns>
    174         public static List<T> QueryDT<T>(string strSql, string orderBy, int start, int limit, ref int total) where T : new()
    175         {
    176             try
    177             {
    178                 List<T> ts = new List<T>();
    179                 string query_sql = string.Format(@"SELECT CSON4.* FROM ( SELECT CSON3.* FROM (
    180                     Select CSON2.*, ROW_NUMBER() OVER({1}) as rownum from (
    181                          Select Count(*) over() total_count,CSON.* From ({0}) CSON
    182                     ) CSON2  
    183         )  CSON3 where CSON3.rownum<{3} ) CSON4 WHERE CSON4.rownum>{2}", strSql, orderBy, (start-1)* limit, start*limit+1);
    184                 DataTable dt = QueryDT(query_sql);
    185                 if (dt.Rows.Count>0)
    186                 {
    187                     total = Convert.ToInt32(dt.Rows[0]["total_count"]);
    188                 }
    189                 ts = toEntity<T>(dt);
    190                 return ts;
    191             }
    192             catch (DbException ex)
    193             {
    194                 throw new Exception(ex.Message);
    195             }
    196         }
    197         /// <summary>
    198         /// 执行查询SQL语句，反射出实体类集合
    199         /// </summary>
    200         /// <typeparam name="T">数据类型</typeparam>
    201         /// <param name="strSql">查询SQL语句</param>
    202         /// <param name="total">总行数</param>
    203         /// <returns>List集合</returns>
    204         public static List<T> QueryDT<T>(string strSql, ref int total) where T : new()
    205         {
    206             try
    207             {
    208                 List<T> ts = new List<T>();
    209                 DataTable dt = QueryDT(strSql);
    210                 if (dt.Rows.Count > 0)
    211                 {
    212                     total = dt.Rows.Count;
    213                 }
    214                 ts = toEntity<T>(dt);
    215                 return ts;
    216             }
    217             catch (DbException ex)
    218             {
    219                 throw new Exception(ex.Message);
    220             }
    221         }
    222         /// <summary>
    223         /// 执行查询SQL语句，反射出实体类集合
    224         /// </summary>
    225         /// <typeparam name="T">数据类型</typeparam>
    226         /// <param name="strSql">查询SQL语句</param>
    227         /// <returns></returns>
    228         public static T QueryDT<T>(string strSql) where T : new()
    229         {
    230             try
    231             {
    232                 T t = new T();
    233                 DataTable dt = QueryDT(strSql);
    234                 foreach (DataRow dr in dt.Rows)
    235                 {
    236                     foreach (PropertyInfo pi in t.GetType().GetProperties())
    237                     {
    238                         if (dt.Columns.Contains(pi.Name))
    239                         {
    240                             if (!pi.CanWrite) continue;
    241                             var value = dr[pi.Name];
    242                             if (value != DBNull.Value)
    243                             {
    244                                 switch (pi.PropertyType.FullName)
    245                                 {
    246                                     case "System.Decimal":
    247                                         pi.SetValue(t, decimal.Parse(value.ToString()), null);
    248                                         break;
    249                                     case "System.String":
    250                                         pi.SetValue(t, value.ToString(), null);
    251                                         break;
    252                                     case "System.Int32":
    253                                         pi.SetValue(t, int.Parse(value.ToString()), null);
    254                                         break;
    255                                     default:
    256                                         pi.SetValue(t, value, null);
    257                                         break;
    258                                 }
    259                             }
    260                         }
    261                     }
    262                 }
    263                 if (dt.Rows.Count==0)
    264                 {
    265                     return default(T);
    266                 }
    267                 return t;
    268             }
    269             catch (DbException ex)
    270             {
    271                 throw new Exception(ex.Message);
    272             }
    273         }
    274         /// <summary>
    275         /// 执行查询SQL语句，反射出实体类集合
    276         /// </summary>
    277         /// <typeparam name="T">数据类型</typeparam>
    278         /// <param name="strSql">查询SQL语句</param>
    279         /// <returns></returns>
    280         public static List<T> QueryList<T>(string strSql) where T : new()
    281         {
    282             try
    283             {
    284                 List<T> ts = new List<T>();
    285                 ts=toEntity<T>(QueryDT(strSql));
    286                 return ts;
    287             }
    288             catch (DbException ex)
    289             {
    290                 throw new Exception(ex.Message);
    291             }
    292         }
    293         /// <summary>
    294         /// 将DataTable转换成实体类
    295         /// </summary>
    296         /// <typeparam name="T">实体类</typeparam>
    297         /// <param name="dt">DataTable</param>
    298         /// <returns></returns>
    299         public static List<T> toEntity<T>(DataTable dt) where T : new()
    300         {
    301             List<T> ts = new List<T>();
    302             foreach (DataRow dr in dt.Rows)
    303             {
    304                 T t = new T();
    305                 foreach (PropertyInfo pi in t.GetType().GetProperties())
    306                 {
    307                     if (dt.Columns.Contains(pi.Name))
    308                     {
    309                         if (!pi.CanWrite) continue;
    310                         var value = dr[pi.Name];
    311                         if (value != DBNull.Value)
    312                         {
    313                             switch (pi.PropertyType.FullName)
    314                             {
    315                                 case "System.Decimal":
    316                                     pi.SetValue(t, decimal.Parse(value.ToString()), null);
    317                                     break;
    318                                 case "System.String":
    319                                     pi.SetValue(t, value.ToString(), null);
    320                                     break;
    321                                 case "System.Int32":
    322                                     pi.SetValue(t, int.Parse(value.ToString()), null);
    323                                     break;
    324                                 default:
    325                                     pi.SetValue(t, value, null);
    326                                     break;
    327                             }
    328                         }
    329                     }
    330                 }
    331                 ts.Add(t);
    332             }
    333             if (dt.Rows.Count==0)
    334             {
    335                 return null;
    336             }
    337             return ts;
    338         }
    339         /// <summary>
    340         /// 对数据进行增、删、改
    341         /// </summary>
    342         /// <param name="strSql">SQL语句</param>
    343         /// <returns></returns>
    344         public static int ExecuteSql(string strSql)
    345         {
    346             using (IDbConnection conn = defaultPro.GetConnection())
    347             {
    348                 using (IDbCommand cmd = defaultPro.GetCommand(strSql, conn))
    349                 {
    350                     try
    351                     {
    352                         if (conn.State != ConnectionState.Open)
    353                         {
    354                             conn.Open();
    355                         }
    356                         return cmd.ExecuteNonQuery();
    357                     }
    358                     catch (Exception ex)
    359                     {
    360                         conn.Close();
    361                         throw ex;
    362                     }
    363                     finally
    364                     {
    365                         conn.Close();
    366                     }
    367                 }
    368             }
    369         }
    370         /// <summary>
    371         /// 对数据进行增、删、改
    372         /// </summary>
    373         /// <param name="strSql">SQL语句</param>
    374         /// <returns></returns>
    375         public static int ExecuteSqlTrans(string strSql)
    376         {
    377             using (IDbConnection conn = defaultPro.GetConnection())
    378             {
    379                 if (conn.State != ConnectionState.Open)
    380                 {
    381                     conn.Open();
    382                 }
    383                 IDbTransaction trans = conn.BeginTransaction();
    384                 IDbCommand cmd = defaultPro.GetCommand(conn, trans);
    385                 try
    386                 {
    387                     int resCount = 0;
    388                     PrintErrorStrSql(strSql);
    389                     cmd.CommandText = strSql;
    390                     resCount = cmd.ExecuteNonQuery();
    391                     trans.Commit();
    392                     return resCount;
    393                 }
    394                 catch (Exception ex)
    395                 {
    396                     trans.Rollback();
    397                     throw ex;
    398                 }
    399                 finally
    400                 {
    401                     cmd.Dispose();
    402                     conn.Close();
    403                 }
    404             }
    405         }
    406         /// <summary>
    407         /// 对数据进行增、删、改
    408         /// </summary>
    409         /// <param name="listSql">SQL集合</param>
    410         /// <returns></returns>
    411         public static int ExecuteSqlTrans(List<string> listSql)
    412         {
    413             using (IDbConnection conn = defaultPro.GetConnection())
    414             {
    415                 if (conn.State != ConnectionState.Open)
    416                 {
    417                     conn.Open();
    418                 }
    419                 IDbTransaction trans = conn.BeginTransaction();
    420                 IDbCommand cmd = defaultPro.GetCommand(conn, trans);
    421                 try
    422                 {
    423                     int resCount = 0;
    424                     string strSql = string.Empty;
    425                     for (int i = 0; i < listSql.Count; i++)
    426                     {
    427                         strSql = listSql[i];
    428                         PrintErrorStrSql(strSql);
    429                         cmd.CommandText = strSql;
    430                         resCount += cmd.ExecuteNonQuery();
    431                     }
    432                     trans.Commit();
    433                     return resCount;
    434                 }
    435                 catch (Exception ex)
    436                 {
    437                     trans.Rollback();
    438                     throw ex;
    439                 }
    440                 finally
    441                 {
    442                     cmd.Dispose();
    443                     conn.Close();
    444                 }
    445             }
    446         }
    447         /// <summary>
    448         /// 对数据进行增、删、改
    449         /// </summary>
    450         /// <param name="strSql">SQL语句</param>
    451         /// <param name="pms">可变参数</param>
    452         /// <returns></returns>
    453         public static int ExecuteSqlTrans(string strSql, IDataParameter[] pms)
    454         {
    455             using (IDbConnection conn = defaultPro.GetConnection())
    456             {
    457                 if (conn.State != ConnectionState.Open)
    458                 {
    459                     conn.Open();
    460                 }
    461                 IDbTransaction trans = conn.BeginTransaction();
    462                 using (IDbCommand cmd = defaultPro.GetCommand(conn, trans))
    463                 {
    464                     if (pms != null)
    465                     {
    466                         foreach (IDataParameter item in pms)
    467                         {
    468                             cmd.Parameters.Add(item);
    469                         }
    470                     }
    471                     try
    472                     {
    473                         int resCount = 0;
    474                         PrintErrorStrSql(strSql);
    475                         cmd.CommandText = strSql;
    476                         resCount = cmd.ExecuteNonQuery();
    477                         trans.Commit();
    478                         return resCount;
    479                     }
    480                     catch (Exception ex)
    481                     {
    482                         trans.Rollback();
    483                         throw ex;
    484                     }
    485                     finally
    486                     {
    487                         conn.Close();
    488                     }
    489                 }
    490             }
    491         }
    492         private static void PrintErrorStrSql(string strSql)
    493         {
    494             if (SysCfg.IsPrint == "true" && !StringUtil.isNullOrBlank(SysCfg.PrintErrorSqlPath))
    495             {
    496                 PrintSqlTool.LogBasePath = SysCfg.PrintErrorSqlPath;
    497                 PrintSqlTool.execSqlQueue.Enqueue(strSql);
    498                 //PrintSqlTool.PrintSqlToText(SysCfg.PrintErrorSqlPath, strSql);
    499             }
    500         }
    501     }
    502 }

## 五、实体类基类(EntityBase.cs) ##

1 using System;
      2 using System.Collections.Generic;
      3 using System.Reflection;
      4 using System.Web;
      5 using System.Text;
      6 using Sam.OA.Common;
      7 
      8 namespace Sam.OA.Model
      9 {
     10     /// <summary>
     11     /// 实体类基类
     12     /// author:陈彦斌
     13     /// 最后更新时间：2019年9月17日22:12:46
     14     /// </summary>
     15     [Serializable]
     16     public abstract class EntityBase
     17     {
     18         /// <summary>
     19         /// 获取主键
     20         /// </summary>
     21         /// <returns></returns>
     22         public abstract string GetPrimaryKey();
     23         /// <summary>
     24         /// 获取INSERT语句
     25         /// </summary>
     26         /// <returns></returns>
     27         public string GetInsertSql()
     28         {
     29             try
     30             {
     31                 Type t = this.GetType();
     32                 string tableName = t.Name,pKey=this.GetPrimaryKey(),fields=string.Empty,values=string.Empty,temp=null;
     33                 foreach (PropertyInfo pi in t.GetProperties())
     34                 {
     35                     if (!pi.CanWrite) continue;
     36                     if (pi.Name.Equals(pKey))
     37                     {
     38                         continue;
     39                     }
     40                     temp = GetByTypeStr(pi);
     41                     fields += pi.Name + ",";
     42                     values += temp + ",";
     43                 }
     44                 return string.Format("Insert into {0}({1}) Values({2})", tableName, fields.TrimEnd(','), values.TrimEnd(','));
     45             }
     46             catch
     47             {
     48                 throw;
     49             }
     50         }
     51         /// <summary>
     52         /// 通过POST/GET请求获取Insert语句
     53         /// </summary>
     54         /// <param name="arrPostOrGetAllKey">POST/GET请求的所有键</param>
     55         /// <param name="HttpRequest"></param>
     56         /// <returns></returns>
     57         public string GetInsertSql(string[] arrPostOrGetAllKey, HttpRequest Request)
     58         {
     59             try
     60             {
     61 
     62                 Dictionary<string, string> dd = new Dictionary<string, string>();
     63                 string key = string.Empty;
     64                 string value = string.Empty;
     65                 for (int i = 0; i < arrPostOrGetAllKey.Length; i++)
     66                 {
     67                     key = Request.Form.AllKeys[i];
     68                     value = Request.Form[key];
     69                     if (StringUtil.isNullOrBlank(key))
     70                     {
     71                         key = Request.QueryString.AllKeys[i];
     72                         value = Request.QueryString[key];
     73                     }
     74                     dd.Add(key, value);
     75                 }
     76                 Type t = this.GetType();
     77                 PropertyInfo[] pInfos = t.GetProperties();
     78                 string tableName = t.Name, pKey = this.GetPrimaryKey(), pValue = string.Empty, str_fields = string.Empty;
     79                 StringBuilder s_fields = new StringBuilder();
     80                 StringBuilder s_values = new StringBuilder();
     81                 int keyIndex = -1;
     82                 for (int i = 0; i < pInfos.Length; i++)
     83                 {
     84                     if (pInfos[i].Name.Equals(pKey))
     85                     {
     86                         keyIndex = i;
     87                         if (dd.ContainsKey(pKey))
     88                         {
     89                             pValue = dd[pKey];
     90                         }
     91                         continue;
     92                     }
     93                     if (dd.ContainsKey(pInfos[i].Name))
     94                     {
     95                         s_fields.Append(pInfos[i].Name).Append(",");
     96                         s_values.Append(GetByTypeStr(dd[pInfos[i].Name])).Append(",");
     97                     }
     98                 }
     99                 return string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, s_fields.ToString().TrimEnd(','), s_values.ToString().TrimEnd(','));
    100             }
    101             catch
    102             {
    103                 throw;
    104             }
    105         }
    106         /// <summary>
    107         /// 根据主键获取删除条件
    108         /// </summary>
    109         /// <typeparam name="T"></typeparam>
    110         /// <param name="pKey"></param>
    111         /// <returns></returns>
    112         public string GetDeleteSql<T>(int pKey)
    113         {
    114             try
    115             {
    116                 Type t = this.GetType();
    117                 string table = t.Name;
    118                 return string.Format("DELETE FROM {0} WHERE {1} = {2}", table, this.GetPrimaryKey(), pKey);
    119             }
    120             catch
    121             {
    122                 throw;
    123             }
    124         }
    125         /// <summary>
    126         /// 根据主键获取删除条件
    127         /// </summary>
    128         /// <param name="pKey"></param>
    129         /// <returns></returns>
    130         public string GetDeleteSql(int pKey)
    131         {
    132             try
    133             {
    134                 Type t = this.GetType();
    135                 string table = t.Name;
    136                 return string.Format("DELETE FROM {0} WHERE {1} = {2}", table, this.GetPrimaryKey(), pKey);
    137             }
    138             catch
    139             {
    140                 throw;
    141             }
    142         }
    143         /// <summary>
    144         /// 根据条件获取删除语句eg：name='alex' and age=8
    145         /// </summary>
    146         /// <typeparam name="T"></typeparam>
    147         /// <param name="deleteWhere">删除条件eg：name='alex' and age=8</param>
    148         /// <returns></returns>
    149         public string GetDeleteSql<T>(string deleteWhere)
    150         {
    151             try
    152             {
    153                 Type t = this.GetType();
    154                 string table = t.Name;
    155                 return string.Format("DELETE FROM {0} WHERE {1}", table, deleteWhere);
    156             }
    157             catch
    158             {
    159                 throw;
    160             }
    161         }
    162         /// <summary>
    163         /// 根据条件获取删除语句eg：name='alex' and age=8
    164         /// </summary>
    165         /// <typeparam name="T"></typeparam>
    166         /// <param name="deleteWhere">删除条件eg：name='alex' and age=8</param>
    167         /// <returns></returns>
    168         public string GetDeleteSql(string deleteWhere)
    169         {
    170             try
    171             {
    172                 Type t = this.GetType();
    173                 string table = t.Name;
    174                 return string.Format("DELETE FROM {0} WHERE {1}", table, deleteWhere);
    175             }
    176             catch
    177             {
    178                 throw;
    179             }
    180         }
    181         /// <summary>
    182         /// 获取UPDATE语句
    183         /// </summary>
    184         /// <returns></returns>
    185         public string GetUpdateSql()
    186         {
    187             try
    188             {
    189                 Type t = this.GetType();
    190                 PropertyInfo[] pInfos = t.GetProperties();
    191                 string tableName = t.Name, pKey = this.GetPrimaryKey(), str_fields=string.Empty;
    192                 int keyIndex = -1;
    193                 for (int i = 0; i < pInfos.Length; i++)
    194                 {
    195                     if (pInfos[i].Name.Equals(pKey))
    196                     {
    197                         keyIndex = i;
    198                         continue;
    199                     }
    200                     str_fields += pInfos[i].Name + " = " + GetByTypeStr(pInfos[i]) + ",";
    201                 }
    202                 return string.Format("UPDATE {0} SET {1} WHERE {2} = {3}", tableName, str_fields.TrimEnd(','), pKey, GetByTypeStr(pInfos[keyIndex]));
    203             }
    204             catch
    205             {
    206                 throw;
    207             }
    208         }
    209         /// <summary>
    210         /// 通过POST/GET请求获取UPDATE语句
    211         /// </summary>
    212         /// <param name="arrPostOrGetAllKey">POST/GET请求的所有键</param>
    213         /// <param name="HttpRequest"></param>
    214         /// <returns></returns>
    215         public string GetUpdateSql(string[] arrPostOrGetAllKey, HttpRequest Request)
    216         {
    217             try
    218             {
    219 
    220                 Dictionary<string, string> dd = new Dictionary<string, string>();
    221                 string key = string.Empty;
    222                 string value = string.Empty;
    223                 for (int i = 0; i < arrPostOrGetAllKey.Length; i++)
    224                 {
    225                     key = Request.Form.AllKeys[i];
    226                     value = Request.Form[key];
    227                     if (StringUtil.isNullOrBlank(key))
    228                     {
    229                         key = Request.QueryString.AllKeys[i];
    230                         value = Request.QueryString[key];
    231                     }
    232                     dd.Add(key, value);
    233                 }
    234                 Type t = this.GetType();
    235                 PropertyInfo[] pInfos = t.GetProperties();
    236                 string tableName = t.Name, pKey = this.GetPrimaryKey(), pValue = string.Empty, str_fields = string.Empty;
    237                 int keyIndex = -1;
    238                 for (int i = 0; i < pInfos.Length; i++)
    239                 {
    240                     if (pInfos[i].Name.Equals(pKey))
    241                     {
    242                         keyIndex = i;
    243                         if (dd.ContainsKey(pKey))
    244                         {
    245                             pValue = dd[pKey];
    246                         }
    247                         continue;
    248                     }
    249                     if (dd.ContainsKey(pInfos[i].Name))
    250                     {
    251                         str_fields += pInfos[i].Name + " = " + GetByTypeStr(dd[pInfos[i].Name]) + ",";
    252                     }
    253                 }
    254                 if (StringUtil.isNullOrBlank(pValue))
    255                 {
    256                     throw new Exception("更新条件不能为空!");
    257                 }
    258                 return string.Format("UPDATE {0} SET {1} WHERE {2} = {3}", tableName, str_fields.TrimEnd(','), pKey, GetByTypeStr(pValue));
    259             }
    260             catch
    261             {
    262                 throw;
    263             }
    264         }
    265         /// <summary>
    266         /// 根据实体类组装查询条件，并返回查询语句
    267         /// </summary>
    268         /// <typeparam name="T">数据类型</typeparam>
    269         /// <param name="t">实体类实例</param>
    270         /// <returns></returns>
    271         public string GetSelectSql<T>(T t) where T:class
    272         {
    273             try
    274             {
    275                 Type t1 = t.GetType();
    276                 string tableName = t1.Name, temp = null,p_key= this.GetPrimaryKey();
    277                 StringBuilder sb = new StringBuilder();
    278                 foreach (PropertyInfo pi in t1.GetProperties())
    279                 {
    280                     if (!pi.CanWrite) continue;
    281                     temp = GetByTypeStr(pi);
    282                     if (!StringUtil.isNullOrBlank(temp) && ((temp!="0"&& pi.Name != p_key)|| (temp != "0" && pi.Name == p_key)))
    283                     {
    284                         sb.Append(" AND ").Append(pi.Name).Append(" = ").Append(temp);
    285                     }
    286                 }
    287                 if (sb.ToString().Length > 0)
    288                 {
    289                     string query_sql = "SELECT * FROM {0} WHERE 1=1 {1}";
    290                     return string.Format(query_sql, tableName, sb.ToString());
    291                 }
    292                 else
    293                 {
    294                     string query_sql = "SELECT * FROM {0} WHERE 1=1 ";
    295                     return string.Format(query_sql, tableName);
    296                 }                
    297             }
    298             catch (Exception ex)
    299             {
    300                 throw ex;
    301             }
    302         }
    303         /// <summary>
    304         /// 根据实体类组装查询条件，并返回查询语句
    305         /// </summary>
    306         /// <typeparam name="T">数据类型</typeparam>
    307         /// <param name="t">实体类实例</param>
    308         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    309         /// <returns></returns>
    310         public string GetSelectSql<T>(T t, string strOrderBy) where T : class
    311         {
    312             try
    313             {
    314                 Type t1 = t.GetType();
    315                 string tableName = t1.Name, temp = null, p_key = this.GetPrimaryKey();
    316                 StringBuilder sb = new StringBuilder();
    317                 foreach (PropertyInfo pi in t1.GetProperties())
    318                 {
    319                     if (!pi.CanWrite) continue;
    320                     temp = GetByTypeStr(pi);
    321                     if (!StringUtil.isNullOrBlank(temp) && ((temp != "0" && pi.Name != p_key) || (temp != "0" && pi.Name == p_key)))
    322                     {
    323                         sb.Append(" AND ").Append(pi.Name).Append(" = ").Append(temp);
    324                     }
    325                 }
    326                 if (sb.ToString().Length > 0)
    327                 {
    328                     string query_sql = "SELECT * FROM {0} WHERE 1=1 {1} {2}";
    329                     return string.Format(query_sql, tableName, sb.ToString(), strOrderBy);
    330                 }
    331                 else
    332                 {
    333                     string query_sql = "SELECT * FROM {0} WHERE 1=1 {1}";
    334                     return string.Format(query_sql, tableName, strOrderBy);
    335                 }
    336             }
    337             catch (Exception ex)
    338             {
    339                 throw ex;
    340             }
    341         }
    342         /// <summary>
    343         /// 根据查询条件，返回查询语句eg:a='123' and b='456'
    344         /// </summary>
    345         /// <typeparam name="T">数据类型</typeparam>
    346         /// <param name="strWhere">WHERE条件：eg: a='123' and b='456' .....</param>
    347         /// <returns></returns>
    348         public string GetSelectSql<T>(string strWhere) where T : class
    349         {
    350             if (!StringUtil.isNullOrBlank(strWhere))
    351             {
    352                 Type t = this.GetType();
    353                 string tableName = t.Name;
    354                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1}",tableName, strWhere);
    355             }
    356             throw new Exception("无查询条件!");
    357         }
    358         /// <summary>
    359         /// 根据查询条件，返回查询语句eg:a='123' and b='456'
    360         /// </summary>
    361         /// <typeparam name="T">数据类型</typeparam>
    362         /// <param name="strWhere">WHERE条件：eg: a='123' and b='456' .....</param>
    363         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    364         /// <returns></returns>
    365         public string GetSelectSql<T>(string strWhere, string strOrderBy) where T : class
    366         {
    367             if (!StringUtil.isNullOrBlank(strWhere))
    368             {
    369                 Type t = this.GetType();
    370                 string tableName = t.Name;
    371                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} {2}", tableName, strWhere, strOrderBy);
    372             }
    373             throw new Exception("无查询条件!");
    374         }
    375         /// <summary>
    376         /// 根据查询条件，返回查询语句eg:a='123' and b='456'
    377         /// </summary>
    378         /// <param name="strWhere">WHERE条件：eg: a='123' and b='456' .....</param>
    379         /// <returns></returns>
    380         public string GetSelectSql(string strWhere)
    381         {
    382             Type t = this.GetType();
    383             string tableName = t.Name;
    384             if (!StringUtil.isNullOrBlank(strWhere))
    385             {
    386                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1}", tableName, strWhere);
    387             }
    388             throw new Exception("无查询条件!");
    389         }
    390         /// <summary>
    391         /// 根据查询条件，返回查询语句eg:a='123' and b='456'
    392         /// </summary>
    393         /// <param name="strWhere">WHERE条件：eg: a='123' and b='456' .....</param>
    394         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    395         /// <returns></returns>
    396         public string GetSelectSql(string strWhere, string strOrderBy)
    397         {
    398             Type t = this.GetType();
    399             string tableName = t.Name;
    400             if (!StringUtil.isNullOrBlank(strWhere))
    401             {
    402                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} {2}", tableName, strWhere, strOrderBy);
    403             }
    404             throw new Exception("无查询条件!");
    405         }
    406         /// <summary>
    407         /// 根据主键集合，返回查询结果集
    408         /// </summary>
    409         /// <param name="ids">主键集合</param>
    410         /// <returns></returns>
    411         public string GetSelectSql(List<int> ids)
    412         {
    413             Type t = this.GetType();
    414             string tableName = t.Name;
    415             if (ids.Count>0)
    416             {
    417                 StringBuilder stringBuider = new StringBuilder();
    418                 for (int i = 0; i < ids.Count; i++)
    419                 {
    420                     stringBuider.Append(ids[i]).Append(",");
    421                 }
    422                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} IN ({2})", tableName, this.GetPrimaryKey(), stringBuider.ToString().TrimEnd(','));
    423             }
    424             return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} = {2}", tableName, this.GetPrimaryKey(),"-1");
    425         }
    426         /// <summary>
    427         /// 根据主键集合，返回查询非结果集
    428         /// </summary>
    429         /// <param name="ids">主键集合</param>
    430         /// <returns></returns>
    431         public string GetReverseSelectSql(List<int> ids)
    432         {
    433             Type t = this.GetType();
    434             string tableName = t.Name;
    435             if (ids.Count > 0)
    436             {
    437                 StringBuilder stringBuider = new StringBuilder();
    438                 for (int i = 0; i < ids.Count; i++)
    439                 {
    440                     stringBuider.Append(ids[i]).Append(",");
    441                 }
    442                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} NOT IN ({2})", tableName, this.GetPrimaryKey(), stringBuider.ToString().TrimEnd(','));
    443             }
    444             return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} = {2}", tableName, this.GetPrimaryKey(), "-1");
    445         }
    446         /// <summary>
    447         /// 根据主键集合，返回查询结果集
    448         /// </summary>
    449         /// <param name="ids">主键集合</param>
    450         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    451         /// <returns></returns>
    452         public string GetSelectSql(List<int> ids, string strOrderBy)
    453         {
    454             Type t = this.GetType();
    455             string tableName = t.Name;
    456             if (ids.Count > 0)
    457             {
    458                 StringBuilder stringBuider = new StringBuilder();
    459                 for (int i = 0; i < ids.Count; i++)
    460                 {
    461                     stringBuider.Append(ids[i]).Append(",");
    462                 }
    463                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} IN ({2}) {3}", tableName, this.GetPrimaryKey(), stringBuider.ToString().TrimEnd(','), strOrderBy);
    464             }
    465             return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} = {2} {3}", tableName, this.GetPrimaryKey(), "-1", strOrderBy);
    466         }
    467         /// <summary>
    468         /// 根据主键集合，返回查询非结果集
    469         /// </summary>
    470         /// <param name="ids">主键集合</param>
    471         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    472         /// <returns></returns>
    473         public string GetReverseSelectSql(List<int> ids, string strOrderBy)
    474         {
    475             Type t = this.GetType();
    476             string tableName = t.Name;
    477             if (ids.Count > 0)
    478             {
    479                 StringBuilder stringBuider = new StringBuilder();
    480                 for (int i = 0; i < ids.Count; i++)
    481                 {
    482                     stringBuider.Append(ids[i]).Append(",");
    483                 }
    484                 return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} NOT IN ({2}) {3}", tableName, this.GetPrimaryKey(), stringBuider.ToString().TrimEnd(','), strOrderBy);
    485             }
    486             return string.Format("SELECT * FROM {0} WHERE 1=1 AND {1} = {2} {3}", tableName, this.GetPrimaryKey(), "-1", strOrderBy);
    487         }
    488         /// <summary>
    489         /// 获取表中所有数据
    490         /// </summary>
    491         /// <typeparam name="T">数据类型</typeparam>
    492         /// <returns></returns>
    493         public string GetSelectSql<T>() where T : class
    494         {
    495             try
    496             {
    497                 return string.Format("SELECT * FROM {0}", this.GetType().Name);
    498             }
    499             catch (Exception ex)
    500             {
    501                 throw ex;
    502             }            
    503         }
    504         /// <summary>
    505         /// 获取表中所有数据
    506         /// </summary>
    507         /// <returns></returns>
    508         public string GetSelectSql()
    509         {
    510             try
    511             {
    512                 return string.Format("SELECT * FROM {0}", this.GetType().Name);
    513             }
    514             catch (Exception ex)
    515             {
    516                 throw ex;
    517             }            
    518         }
    519         /// <summary>
    520         /// 根据主键查询表
    521         /// </summary>
    522         /// <typeparam name="T"></typeparam>
    523         /// <param name="pKey">主键</param>
    524         /// <returns></returns>
    525         public string SelectByPrimaryKey<T>(int pKey)
    526         {
    527             try
    528             {
    529                 Type t = this.GetType();
    530                 string table = t.Name;
    531                 return string.Format("SELECT * FROM {0} WHERE {1} = {2}", table, this.GetPrimaryKey(), pKey);
    532             }
    533             catch
    534             {
    535                 throw;
    536             }
    537         }
    538         /// <summary>
    539         /// 根据主键查询表
    540         /// </summary>
    541         /// <typeparam name="T"></typeparam>
    542         /// <param name="pKey">主键</param>
    543         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    544         /// <returns></returns>
    545         public string SelectByPrimaryKey<T>(int pKey,string strOrderBy)
    546         {
    547             try
    548             {
    549                 Type t = this.GetType();
    550                 string table = t.Name;
    551                 return string.Format("SELECT * FROM {0} WHERE {1} = {2} {3}", table, this.GetPrimaryKey(), pKey, strOrderBy);
    552             }
    553             catch
    554             {
    555                 throw;
    556             }
    557         }
    558         /// <summary>
    559         /// 根据主键查询表
    560         /// </summary>
    561         /// <typeparam name="T"></typeparam>
    562         /// <param name="pKey"></param>
    563         /// <returns></returns>
    564         public string SelectByPrimaryKey(int pKey)
    565         {
    566             try
    567             {
    568                 Type t = this.GetType();
    569                 string table = t.Name;
    570                 return string.Format("SELECT * FROM {0} WHERE {1} = {2}", table, this.GetPrimaryKey(), pKey);
    571             }
    572             catch
    573             {
    574                 throw;
    575             }
    576         }
    577         /// <summary>
    578         /// 根据主键查询表
    579         /// </summary>
    580         /// <param name="pKey">主键</param>
    581         /// <param name="strOrderBy">排序eg:order by id desc|order by id</param>
    582         /// <returns></returns>
    583         public string SelectByPrimaryKey(int pKey, string strOrderBy)
    584         {
    585             try
    586             {
    587                 Type t = this.GetType();
    588                 string table = t.Name;
    589                 return string.Format("SELECT * FROM {0} WHERE {1} = {2} {3}", table, this.GetPrimaryKey(), pKey, strOrderBy);
    590             }
    591             catch
    592             {
    593                 throw;
    594             }
    595         }
    596 
    597         /// <summary>
    598         /// 获取表中总行数
    599         /// </summary>
    600         /// <typeparam name="T">数据类型</typeparam>
    601         /// <returns></returns>
    602         public string GetAllRowsCount<T>() where T:class
    603         {
    604             return string.Format("SELECT COUNT(*) FROM {0}",this.GetType().Name);
    605         }
    606         /// <summary>
    607         /// 获取表中总行数
    608         /// </summary>
    609         /// <returns></returns>
    610         public string GetAllRowsCount()
    611         {
    612             try
    613             {
    614                 Type t = this.GetType();
    615                 string table = t.Name;
    616                 return string.Format("SELECT COUNT(*) FROM {0}", table);
    617             }
    618             catch
    619             {
    620                 throw;
    621             }
    622         }
    623         /// <summary>
    624         /// 获取分页数据
    625         /// </summary>
    626         /// <param name="pageSize">一页多少条数据</param>
    627         /// <param name="pageIndex">当前页的索引</param>
    628         /// <returns></returns>
    629         public virtual string GetPageData(int pageSize,int pageIndex)
    630         {
    631             try
    632             {
    633                 int first = pageSize * (pageIndex - 1)+1;
    634                 int last = pageSize * pageIndex;
    635                 Type t = this.GetType();
    636                 return string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() over (order by {0}) as row,TT.* from {1} TT) TTT
    637 WHERE TTT.row BETWEEN {2} AND {3}",this.GetPrimaryKey(), t.Name, first,last);
    638             }
    639             catch
    640             {
    641                 throw;
    642             }
    643         }
    644         /// <summary>
    645         /// 获取分页数据
    646         /// </summary>
    647         /// <param name="pageSize">一页多少条数据</param>
    648         /// <param name="pageIndex">当前页的索引</param>
    649         /// <param name="strWhere">查询条件，eg:and a=1 and b=2 ...</param>
    650         /// <returns></returns>
    651         public virtual string GetPageData(int pageSize, int pageIndex,string strWhere)
    652         {
    653             try
    654             {
    655                 int first = pageSize * (pageIndex - 1) + 1;
    656                 int last = pageSize * pageIndex;
    657                 Type t = this.GetType();
    658                 return string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() over (order by {0}) as row,TT.* from {1} TT) TTT
    659 WHERE TTT.row BETWEEN {2} AND {3} {4}", this.GetPrimaryKey(), t.Name, first, last, strWhere);
    660             }
    661             catch
    662             {
    663                 throw;
    664             }
    665         }
    666         /// <summary>
    667         /// 获取分页数据
    668         /// </summary>
    669         /// <param name="pageSize">一页多少条数据</param>
    670         /// <param name="pageIndex">当前页的索引</param>
    671         /// <param name="objOrderBy">排序规则</param>
    672         /// <returns></returns>
    673         public virtual string GetPageData(int pageSize, int pageIndex, object objOrderBy)
    674         {
    675             try
    676             {
    677                 int first = pageSize * (pageIndex - 1) + 1;
    678                 int last = pageSize * pageIndex;
    679                 Type t = this.GetType();
    680                 return string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() over (order by {0}) as row,TT.* from {1} TT) TTT
    681 WHERE TTT.row BETWEEN {2} AND {3}", objOrderBy.ToString(), t.Name, first, last);
    682             }
    683             catch
    684             {
    685                 throw;
    686             }
    687         }
    688         /// <summary>
    689         /// 获取分页数据
    690         /// </summary>
    691         /// <param name="pageSize">一页多少条数据</param>
    692         /// <param name="pageIndex">当前页的索引</param>
    693         /// <param name="strWhere">查询条件,eg:and a=1 and b=2 ...</param>
    694         /// <param name="objOrderBy">排序规则</param>
    695         /// <returns></returns>
    696         public virtual string GetPageData(int pageSize, int pageIndex,string strWhere, object objOrderBy)
    697         {
    698             try
    699             {
    700                 int first = pageSize * (pageIndex - 1) + 1;
    701                 int last = pageSize * pageIndex;
    702                 Type t = this.GetType();
    703                 return string.Format(@"SELECT * FROM (SELECT ROW_NUMBER() over (order by {0}) as row,TT.* from {1} TT) TTT
    704 WHERE TTT.row BETWEEN {2} AND {3} {4}", objOrderBy.ToString(), t.Name, first, last, strWhere);
    705             }
    706             catch
    707             {
    708                 throw;
    709             }
    710         }
    711         /// <summary>
    712         /// 根据数据类型反射字段值
    713         /// </summary>
    714         /// <param name="pInfo">公共属性</param>
    715         /// <returns></returns>
    716         private string GetByTypeStr(PropertyInfo pInfo)
    717         {
    718             try
    719             {
    720                 string result_str = string.Empty;
    721                 Type t = pInfo.PropertyType;
    722                 object obj = pInfo.GetValue(this, null);
    723                 bool valueNull = StringUtil.isNullOrBlank(obj);
    724                 if (t == typeof(string))
    725                 {
    726                     result_str = valueNull ? "null" : "'" + obj.ToString().Replace("--","") + "'";
    727                 }
    728                 else if (t == typeof(System.Decimal) || t == typeof(System.Int16) || t == typeof(System.Int32) || t == typeof(System.Int64))
    729                 {
    730                     result_str = t.Name == "Nullable`1"&& valueNull ? "null" : obj.ToString();                 
    731                 }
    732                 else if(t==typeof(DateTime)||t.Name== "Nullable`1")
    733                 {
    734                     if (valueNull||DateTime.MinValue.Equals(obj)|| t.Name == "Nullable`1")
    735                     {
    736                         result_str = "null";
    737                     }
    738                     else
    739                     {
    740                         result_str = "'"+obj.ToString().Replace("年", "-").Replace("月", "-").Replace("日", "")+"'";
    741                     }
    742                 }
    743                 return result_str;
    744             }
    745             catch
    746             {
    747                 throw;
    748             }
    749         }
    750         /// <summary>
    751         /// 获取字段值
    752         /// </summary>
    753         /// <param name="value">值</param>
    754         /// <returns></returns>
    755         private string GetByTypeStr(object obj)
    756         {
    757             try
    758             {
    759                 if (StringUtil.ObjectIsDate(obj))
    760                 {
    761                     return "'" + obj.ToString().Replace("年", "-").Replace("月", "-").Replace("日", "") + "'";
    762                 }
    763                 if (StringUtil.IsDecimal(obj))
    764                 {
    765                     return obj.ToString();
    766                 }
    767                 return "'" + obj.ToString().Replace("--", "") + "'";
    768             }
    769             catch
    770             {
    771                 throw;
    772             }
    773         }
    774     }
    775 }

## 六、数据访问层接口 ##

1 using Sam.OA.Common;
      2 using Sam.OA.Model;
      3 using System;
      4 using System.Collections.Generic;
      5 using System.Data;
      6 
      7 namespace Sam.OA.DAL
      8 {
      9     /// <summary>
     10     /// 数据访问层接口，职责：封装所有的数据访问层的公共的CRUD的方法
     11     /// </summary>
     12     public interface IBaseDal
     13     {
     14         /// <summary>
     15         /// 添加
     16         /// </summary>
     17         /// <param name="model"></param>
     18         /// <returns></returns>
     19         int Add<T>(T model) where T : EntityBase;
     20         /// <summary>
     21         /// 批量添加
     22         /// </summary>
     23         /// <param name="listSql"></param>
     24         /// <returns></returns>
     25         int Add<T>(List<string> listSql) where T : EntityBase;
     26         /// <summary>
     27         /// 删除
     28         /// </summary>
     29         /// <param name="deleteWhere">删除条件</param>
     30         /// <returns></returns>
     31         int Delete<T>(T model, string deleteWhere) where T : EntityBase;
     32         /// <summary>
     33         /// 修改
     34         /// </summary>
     35         /// <param name="model"></param>
     36         /// <returns></returns>
     37         int Update<T>(T model) where T : EntityBase;
     38         /// <summary>
     39         /// 批量修改
     40         /// </summary>
     41         /// <param name="listSql">sql语句集合</param>
     42         /// <returns></returns>
     43         int Update<T>(List<string> listSql) where T : EntityBase;
     44         /// <summary>
     45         /// 查询
     46         /// </summary>
     47         /// <param name="model">实体类</param>
     48         /// <returns></returns>
     49         List<T> Select<T>(T model) where T : EntityBase, new();
     50         /// <summary>
     51         /// 查询，返回实体类
     52         /// </summary>
     53         /// <param name="model">实体类</param>
     54         /// <returns></returns>
     55         T SelectEntity<T>(T model) where T : EntityBase, new();
     56         /// <summary>
     57         /// 查询，返回实体类
     58         /// </summary>
     59         /// <param name="model">实体类</param>
     60         /// <param name="strWhere">查询条件</param>
     61         /// <returns></returns>
     62         T SelectEntity<T>(T model, string strWhere) where T : EntityBase, new();
     63         /// <summary>
     64         /// 查询
     65         /// </summary>
     66         /// <param name="model">实体类</param>
     67         /// <param name="strWhere">查询条件</param>
     68         /// <returns></returns>
     69         List<T> Select<T>(T model, string strWhere) where T : EntityBase, new();
     70         /// <summary>
     71         /// 获取分页集合
     72         /// </summary>
     73         /// <param name="pageSize">页的大小</param>
     74         /// <param name="pageIndex">当前页的索引</param>
     75         /// <returns></returns>
     76         List<T> GetPageEntity<T>(T model, int pageSize, int pageIndex) where T : EntityBase, new();
     77     }
     78     /// <summary>
     79     /// 数据访问层基类
     80     /// </summary>
     81     /// <typeparam name="T"></typeparam>
     82     public class BaseDal: IBaseDal
     83     {
     84         /// <summary>
     85         /// 添加
     86         /// </summary>
     87         /// <param name="listSql">sql集合</param>
     88         /// <returns></returns>
     89         public int Add<T>(List<string> listSql) where T : EntityBase
     90         {
     91             try
     92             {
     93                 return DbUtil.ExecuteSqlTrans(listSql);
     94             }
     95             catch (Exception ex)
     96             {
     97                 throw ex;
     98             }
     99         }
    100         /// <summary>
    101         /// 添加
    102         /// </summary>
    103         /// <param name="model">实体类</param>
    104         /// <returns></returns>
    105         public int Add<T>(T model) where T:EntityBase
    106         {
    107             try
    108             {
    109                 return DbUtil.ExecuteSqlTrans(model.GetInsertSql());
    110             }
    111             catch (Exception ex)
    112             {
    113                 throw ex;
    114             }
    115         }
    116         /// <summary>
    117         /// 删除
    118         /// </summary>
    119         /// <param name="model">实体类</param>
    120         /// <param name="deleteWhere">删除条件</param>
    121         /// <returns></returns>
    122         public int Delete<T>(T model, string deleteWhere) where T : EntityBase
    123         {
    124             try
    125             {
    126                 return DbUtil.ExecuteSqlTrans(model.GetDeleteSql(deleteWhere));
    127             }
    128             catch (Exception ex)
    129             {
    130                 throw ex;
    131             }
    132         }
    133         /// <summary>
    134         /// 获取分页数据
    135         /// </summary>
    136         /// <param name="model">实体类</param>
    137         /// <param name="pageSize">一页多少条数据</param>
    138         /// <param name="pageIndex">当前页的索引号</param>
    139         /// <returns></returns>
    140         public List<T> GetPageEntity<T>(T model, int pageSize, int pageIndex) where T : EntityBase,new ()
    141         {
    142             try
    143             {
    144                 DataTable dt = DbUtil.QueryDT(model.GetPageData(pageSize, pageIndex));
    145                 return JsonHelper.DtConvertToModel<T>(dt);
    146             }
    147             catch (Exception ex)
    148             {
    149                 throw ex;
    150             }
    151         }
    152         /// <summary>
    153         /// 查询
    154         /// </summary>
    155         /// <param name="model">实体类</param>
    156         /// <returns></returns>
    157         public List<T> Select<T>(T model) where T : EntityBase,new ()
    158         {
    159             try
    160             {
    161                 DataTable dt = DbUtil.QueryDT(model.GetSelectSql<T>(model));
    162                 return JsonHelper.DtConvertToModel<T>(dt);
    163             }
    164             catch (Exception ex)
    165             {
    166                 throw ex;
    167             }
    168         }
    169         /// <summary>
    170         /// 查询
    171         /// </summary>
    172         /// <param name="model">实体类</param>
    173         /// <param name="strWhere">查询条件</param>
    174         /// <returns></returns>
    175         public List<T> Select<T>(T model, string strWhere) where T : EntityBase,new ()
    176         {
    177             try
    178             {
    179                 DataTable dt = DbUtil.QueryDT(model.GetSelectSql(strWhere));
    180                 return JsonHelper.DtConvertToModel<T>(dt);
    181             }
    182             catch (Exception ex)
    183             {
    184                 throw ex;
    185             }
    186         }
    187         /// <summary>
    188         /// 查询
    189         /// </summary>
    190         /// <param name="model">实体类</param>
    191         /// <returns></returns>
    192         public T SelectEntity<T>(T model) where T : EntityBase,new ()
    193         {
    194             try
    195             {
    196                 return DbUtil.QueryDT<T>(model.GetSelectSql<T>(model));
    197             }
    198             catch (Exception ex)
    199             {
    200                 throw ex;
    201             }
    202         }
    203         /// <summary>
    204         /// 查询
    205         /// </summary>
    206         /// <param name="model">实体类</param>
    207         /// <param name="strWhere">查询条件</param>
    208         /// <returns></returns>
    209         public T SelectEntity<T>(T model, string strWhere) where T : EntityBase,new ()
    210         {
    211             try
    212             {
    213                 return DbUtil.QueryDT<T>(model.GetSelectSql(strWhere));
    214             }
    215             catch (Exception ex)
    216             {
    217                 throw ex;
    218             }
    219         }
    220         /// <summary>
    221         /// 更新
    222         /// </summary>
    223         /// <param name="model">实体类</param>
    224         /// <returns></returns>
    225         public int Update<T>(T model) where T : EntityBase
    226         {
    227             try
    228             {
    229                 return DbUtil.ExecuteSqlTrans(model.GetUpdateSql());
    230             }
    231             catch (Exception ex)
    232             {
    233                 throw ex;
    234             }
    235         }
    236         /// <summary>
    237         /// 更新
    238         /// </summary>
    239         /// <param name="listSql">sql集合</param>
    240         /// <returns></returns>
    241         public int Update<T>(List<string> listSql) where T : EntityBase
    242         {
    243             try
    244             {
    245                 return DbUtil.ExecuteSqlTrans(listSql);
    246             }
    247             catch (Exception ex)
    248             {
    249                 throw ex;
    250             }
    251         }
    252     }
    253 }

## 数据访问层eg: ##

1 using Sam.OA.Model.Sam;
     2 using System;
     3 using System.Collections.Generic;
     4 using System.Data;
     5 using Sam.OA.Common;
     6 using Sam.OA.Model;
     7 using Sam.OA.DAL.ISam;
     8 
     9 namespace Sam.OA.DAL.Sam
    10 {
    11     /// <summary>
    12     /// 用户信息数据访问层
    13     /// </summary>
    14     public class UserInfoDAL : BaseDal, IUserInfoDAL
    15     {
    16         public void Test()
    17         {
    18             throw new NotImplementedException();
    19         }
    20     }
    21 }

## **数据访问层接口eg：** ##

1 using System;
     2 using System.Collections.Generic;
     3 using System.Linq;
     4 using System.Text;
     5 using System.Threading.Tasks;
     6 
     7 namespace Sam.OA.DAL.ISam
     8 {
     9     /// <summary>
    10     /// 用户信息数据访问层接口
    11     /// </summary>
    12     public interface IUserInfoDAL:IBaseDal
    13     {
    14         void Test();
    15     }
    16 }

## **简单工厂帮助类eg:** ##

1 using Sam.OA.DAL.ISam;
     2 using Sam.OA.DAL.Sam;
     3 
     4 namespace Sam.OA.DALFactory
     5 {
     6     /// <summary>
     7     /// 数据访问层静态工厂帮助类
     8     /// </summary>
     9     public class StaticFactory
    10     {
    11         /// <summary>
    12         /// 创建用户信息简单工厂
    13         /// </summary>
    14         /// <returns></returns>
    15         public static IUserInfoDAL CreateUserInfoFactory()
    16         {
    17             return new UserInfoDAL();
    18         }
    19     }
    20 }

## **抽象工厂eg:** ##

1 using Sam.OA.DAL.ISam;
     2 
     3 namespace Sam.OA.DALFactory
     4 {
     5     /// <summary>
     6     /// 数据库访问接口
     7     /// </summary>
     8     public interface IDbSession
     9     {
    10         IUserInfoDAL UserInfoDal { get; }
    11     }
    12     /// <summary>
    13     /// 数据库访问会话层
    14     /// </summary>
    15     public class DbSession: IDbSession
    16     {
    17         /// <summary>
    18         /// 用户信息表数据访问层
    19         /// </summary>
    20         public IUserInfoDAL UserInfoDal {
    21             get { return StaticFactory.CreateUserInfoFactory(); }
    22         } 
    23     }
    24 }

## **数据库访问工厂eg:** ##

1 using System.Runtime.Remoting.Messaging;
     2 
     3 namespace Sam.OA.DALFactory
     4 {
     5     /// <summary>
     6     /// 数据库访问工厂
     7     /// </summary>
     8     public class DbSessionFactory
     9     {
    10         public static IDbSession GetCurrentDbSession()
    11         {
    12             //一次请求共用一个实例
    13             IDbSession db = CallContext.GetData("DbSession") as IDbSession;
    14             if (db==null)
    15             {
    16                 db = new DbSession();
    17                 CallContext.SetData("DbSession", db);
    18             }
    19             return db;
    20         }
    21     }
    22 }

## **数据访问层静态工厂eg:** ##

1 using Sam.OA.DAL.ISam;
     2 using Sam.OA.DAL.Sam;
     3 using System.Reflection;
     4 
     5 namespace Sam.OA.DALFactory
     6 {
     7     /// <summary>
     8     /// 数据访问层静态工厂
     9     /// </summary>
    10     public class StaticFactory
    11     {
    12         /// <summary>
    13         /// 创建用户信息工厂
    14         /// </summary>
    15         /// <returns></returns>
    16         public static IUserInfoDAL CreateUserInfoFactory()
    17         {
    18             return new UserInfoDAL();
    19         }
    20     }
    21 }

## **业务逻辑层eg:** ##

1 using System;
     2 using System.Collections.Generic;
     3 using Sam.OA.BLL.ISam;
     4 using Sam.OA.DALFactory;
     5 using Sam.OA.Model;
     6 using Sam.OA.Model.Sam;
     7 
     8 namespace Sam.OA.BLL.Sam
     9 {
    10     /// <summary>
    11     /// 用户信息业务逻辑层
    12     /// </summary>
    13     public class UserInfoBll: IUserInfoBll
    14     {
    15         IDbSession dbSession = DbSessionFactory.GetCurrentDbSession();
    16 
    17         public int Add(List<string> listSql)
    18         {
    19             return dbSession.UserInfoDal.Add<UserInfo>(listSql);
    20         }
    21 
    22         public int Add(UserInfo model)
    23         {
    24             return dbSession.UserInfoDal.Add<UserInfo>(model);
    25         }
    26 
    27         public int Delete(UserInfo model, string deleteWhere)
    28         {
    29             return dbSession.UserInfoDal.Delete<UserInfo>(model, deleteWhere);
    30         }
    31 
    32         public List<UserInfo> GetPageEntity(UserInfo model, int pageSize, int pageIndex)
    33         {
    34             throw new NotImplementedException();
    35         }
    36 
    37         public List<UserInfo> Select(UserInfo model)
    38         {
    39             throw new NotImplementedException();
    40         }
    41 
    42         public List<UserInfo> Select(UserInfo model, string strWhere)
    43         {
    44             throw new NotImplementedException();
    45         }
    46 
    47         public UserInfo SelectEntity(UserInfo model)
    48         {
    49             throw new NotImplementedException();
    50         }
    51 
    52         public UserInfo SelectEntity(UserInfo model, string strWhere)
    53         {
    54             throw new NotImplementedException();
    55         }
    56 
    57         public int Update(List<string> listSql)
    58         {
    59             throw new NotImplementedException();
    60         }
    61 
    62         public int Update(UserInfo model)
    63         {
    64             throw new NotImplementedException();
    65         }
    66     }
    67 }

## **业务逻辑层接口基类eg:** ##

1 using Sam.OA.Model;
     2 using System;
     3 using System.Collections.Generic;
     4 using System.Linq;
     5 using System.Text;
     6 using System.Threading.Tasks;
     7 
     8 namespace Sam.OA.BLL.ISam
     9 {
    10     /// <summary>
    11     /// 业务逻辑层接口基类
    12     /// 作者：陈彦斌
    13     /// 时间：2019年8月9日22:20:41
    14     /// </summary>
    15     public interface IBaseBll<T>
    16     {
    17         /// <summary>
    18         /// 添加
    19         /// </summary>
    20         /// <param name="model"></param>
    21         /// <returns></returns>
    22         int Add(T model);
    23         /// <summary>
    24         /// 批量添加
    25         /// </summary>
    26         /// <param name="listSql"></param>
    27         /// <returns></returns>
    28         int Add(List<string> listSql);
    29         /// <summary>
    30         /// 删除
    31         /// </summary>
    32         /// <param name="deleteWhere">删除条件</param>
    33         /// <returns></returns>
    34         int Delete(T model, string deleteWhere);
    35         /// <summary>
    36         /// 修改
    37         /// </summary>
    38         /// <param name="model"></param>
    39         /// <returns></returns>
    40         int Update(T model);
    41         /// <summary>
    42         /// 批量修改
    43         /// </summary>
    44         /// <param name="listSql">sql语句集合</param>
    45         /// <returns></returns>
    46         int Update(List<string> listSql);
    47         /// <summary>
    48         /// 查询
    49         /// </summary>
    50         /// <param name="model">实体类</param>
    51         /// <returns></returns>
    52         List<T> Select(T model);
    53         /// <summary>
    54         /// 查询，返回实体类
    55         /// </summary>
    56         /// <param name="model">实体类</param>
    57         /// <returns></returns>
    58         T SelectEntity(T model);
    59         /// <summary>
    60         /// 查询，返回实体类
    61         /// </summary>
    62         /// <param name="model">实体类</param>
    63         /// <param name="strWhere">查询条件</param>
    64         /// <returns></returns>
    65         T SelectEntity(T model, string strWhere);
    66         /// <summary>
    67         /// 查询
    68         /// </summary>
    69         /// <param name="model">实体类</param>
    70         /// <param name="strWhere">查询条件</param>
    71         /// <returns></returns>
    72         List<T> Select(T model, string strWhere);
    73         /// <summary>
    74         /// 获取分页集合
    75         /// </summary>
    76         /// <param name="pageSize">页的大小</param>
    77         /// <param name="pageIndex">当前页的索引</param>
    78         /// <returns></returns>
    79         List<T> GetPageEntity(T model, int pageSize, int pageIndex);
    80     }
    81 }

## **业务逻辑层用户信息接口eg：** ##

1 using Sam.OA.Model.Sam;
     2 using System;
     3 using System.Collections.Generic;
     4 using System.Linq;
     5 using System.Text;
     6 using System.Threading.Tasks;
     7 
     8 namespace Sam.OA.BLL.ISam
     9 {
    10     public interface IUserInfoBll: IBaseBll<UserInfo>
    11     {
    12 
    13     }
    14 }

## **业务逻辑层工厂eg：** ##

1 using System;
     2 using System.Collections.Generic;
     3 using System.Linq;
     4 using System.Runtime.Remoting.Messaging;
     5 using System.Text;
     6 using System.Threading.Tasks;
     7 
     8 namespace Sam.OA.BLLFactory
     9 {
    10     /// <summary>
    11     /// 业务逻辑层工厂
    12     /// </summary>
    13     public class BllSessionFactory
    14     {
    15         public static IBllSession GetCurrentBllSession()
    16         {
    17             IBllSession bll = CallContext.GetData("BllSession") as IBllSession;
    18             if (bll==null)
    19             {
    20                 bll = new BllSession();
    21                 CallContext.SetData("BllSession", bll);
    22             }
    23             return bll;
    24         }
    25     }
    26 }

## **业务逻辑层静态工厂eg：** ##

1 using Sam.OA.BLL.ISam;
     2 using Sam.OA.BLL.Sam;
     3 
     4 namespace Sam.OA.BLLFactory
     5 {
     6     public class StaticFactory
     7     {
     8         /// <summary>
     9         /// 创建用户信息实例
    10         /// </summary>
    11         /// <returns></returns>
    12         public static IUserInfoBll CreateUserInfoBll()
    13         {
    14             return new UserInfoBll();
    15         }
    16     }
    17 }

## **业务逻辑层接口基类eg：** ##

1 using Sam.OA.BLL.ISam;
     2 using System;
     3 using System.Collections.Generic;
     4 using System.Linq;
     5 using System.Text;
     6 using System.Threading.Tasks;
     7 
     8 namespace Sam.OA.BLLFactory
     9 {
    10     /// <summary>
    11     /// 业务逻辑层接口基类
    12     /// </summary>
    13     public interface IBllSession
    14     {
    15         IUserInfoBll UserInfo { get; }
    16     }
    17     /// <summary>
    18     /// 业务逻辑层基类
    19     /// </summary>
    20     public class BllSession : IBllSession
    21     {
    22         public IUserInfoBll UserInfo
    23         {
    24             get
    25             {
    26                 return StaticFactory.CreateUserInfoBll();
    27             }
    28         }
    29     }
    30 }

## 七、web.config部分 ##

<?xml version="1.0" encoding="utf-8"?>
    
    <configuration>
      <appSettings>
        
        <add key="ConnectionString" value="server=.;uid=sa;pwd=0;database=Sam.OA.DB" />
        <add key="dbType" value="sqlserver" />
        <add key="PrintErrorSqlPath" value="D://NewError.txt" />
        <add key="IsPrint" value="false" />
        
        
        <add key="MemcacheServiceList" value="172.20.10.7:11211"/>
        
      </appSettings>
        。。。。。。。。
    </configuration>

转载于:https://www.cnblogs.com/chenyanbin/p/11186495.html