面向对象编程、模块内高内聚、模块间低耦合、数据库操作工具类
一、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"?> <!-- 有关如何配置 ASP.NET 应用程序的详细信息，请访问 http://go.microsoft.com/fwlink/?LinkId=169433 --> <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" /> <!--数据库配置--> <!--分布式缓存,服务器IP和端口请用冒号隔开,若有多台服务器请用“,”隔开，例如: <add key="MemcacheServiceList" value="172.20.10.7:11211,172.20.10.8:11211"/>--> <add key="MemcacheServiceList" value="172.20.10.7:11211"/> <!--分布式缓存--> </appSettings> 。。。。。。。。 </configuration>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？