基于公司级平台封装的SqlserverHelper
1 public class DBHelper 2 { 3 /// <summary> 4 /// 数据库帮助 5 /// </summary> 6 protected static DbHelper db = null; 7 8 9 /// <summary> 10 /// 增删改 11 /// </summary> 12 /// <param name="sql">sql语句</param> 13 /// <param name="param">参数</param> 14 /// <returns></returns> 15 public int ExecuteCommand(string sql, DbParameter[] param) 16 { 17 if (string.IsNullOrWhiteSpace(sql)) 18 { 19 throw new ArgumentNullException("参数异常"); 20 } 21 try 22 { 23 using (db = Config.CreateDbHelper()) 24 { 25 //循环添加参数; 26 if (param != null) 27 { 28 foreach (var pa in param) 29 { 30 db.AddParameter(pa.ParameterName, pa.Value); 31 } 32 } 33 return db.ExecuteNonQuerySQL(sql); 34 } 35 } 36 catch (Exception e) 37 { 38 throw e; 39 } 40 } 41 42 /// <summary> 43 /// 获取集合 44 /// </summary> 45 /// <param name="sql">sql语句</param> 46 /// <param name="param">参数</param> 47 /// <returns></returns> 48 public IHashObjectList ExecuteScalar(string sql, DbParameter[] param) 49 { 50 if (string.IsNullOrWhiteSpace(sql)) 51 { 52 throw new ArgumentNullException("参数异常"); 53 } 54 try 55 { 56 using (db = Config.CreateDbHelper()) 57 { 58 //循环添加参数; 59 if (param != null) 60 { 61 foreach (var pa in param) 62 { 63 db.AddParameter(pa.ParameterName, pa.Value); 64 } 65 } 66 return db.Select(sql); 67 } 68 } 69 catch (Exception e) 70 { 71 throw e; 72 } 73 } 74 75 /// <summary> 76 /// 获取第一行数据 77 /// </summary> 78 /// <param name="sql">sql语句</param> 79 /// <param name="param">参数</param> 80 /// <returns></returns> 81 public IHashObject SelectFirstRow(string sql, DbParameter[] param) 82 { 83 if (string.IsNullOrWhiteSpace(sql)) 84 { 85 throw new ArgumentNullException("参数异常"); 86 } 87 try 88 { 89 using (db = Config.CreateDbHelper()) 90 { 91 //循环添加参数; 92 if (param != null) 93 { 94 foreach (var pa in param) 95 { 96 db.AddParameter(pa.ParameterName, pa.Value); 97 } 98 } 99 return db.SelectFirstRow(sql) ?? new HashObject(); 100 } 101 } 102 catch (Exception e) 103 { 104 throw e; 105 } 106 } 107 108 /// <summary> 109 /// 获取第一行数据 110 /// </summary> 111 /// <param name="sql">sql语句</param> 112 /// <param name="param">参数</param> 113 /// <returns></returns> 114 public IHashObject SelectSingleRow(string sql, DbParameter[] param) 115 { 116 if (string.IsNullOrWhiteSpace(sql)) 117 { 118 throw new ArgumentNullException("参数异常"); 119 } 120 try 121 { 122 using (db = Config.CreateDbHelper()) 123 { 124 //循环添加参数; 125 if (param != null) 126 { 127 foreach (var pa in param) 128 { 129 db.AddParameter(pa.ParameterName, pa.Value); 130 } 131 } 132 return db.SelectSingleRow(sql) ?? new HashObject(); 133 } 134 } 135 catch (Exception e) 136 { 137 throw e; 138 } 139 } 140 141 /// <summary> 142 /// 分页获取 143 /// </summary> 144 /// <param name="pageIndex"></param> 145 /// <param name="pageCount"></param> 146 /// <param name="totalCount"></param> 147 /// <param name="tableName"></param> 148 /// <param name="order"></param> 149 /// <param name="whereData"></param> 150 /// <returns></returns> 151 public IHashObjectList GetByPage(int pageIndex, int pageCount, out int totalCount, string tableName, string order, bool isAsc, string[] fieldNames, IDictionary<string, object> whereData=null) 152 { 153 totalCount = 0; 154 if (string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(order)|| fieldNames.Length==0) 155 { 156 throw new ArgumentNullException("参数异常"); 157 } 158 try 159 { 160 using (db = Config.CreateDbHelper()) 161 { 162 string strWhere = BuildSelectWhereSql(whereData); 163 this.BuildParameters(whereData); 164 if (!string.IsNullOrWhiteSpace(strWhere)) 165 { 166 totalCount = (int)db.ExecuteScalerSQL(string.Format("select count(0) from {0} where ", tableName) + strWhere); 167 } 168 else 169 { 170 totalCount = (int)db.ExecuteScalerSQL(string.Format("select count(0) from {0}", tableName)); 171 } 172 StringBuilder strSql = new StringBuilder(); 173 strSql.Append("SELECT * FROM ( "); 174 strSql.Append(" SELECT ROW_NUMBER() OVER ("); 175 if (isAsc) 176 { 177 strSql.Append("order by T." + order); 178 } 179 else 180 { 181 strSql.Append("order by T." + order+" desc"); 182 } 183 StringBuilder strColumns = new StringBuilder(); 184 if (fieldNames.Length > 0) 185 { 186 foreach (var item in fieldNames) 187 { 188 if (strColumns.Length != 0) 189 { 190 strColumns.Append(" , "); 191 } 192 strColumns.Append("T." + item); 193 } 194 } 195 strSql.Append(")AS Row, " + strColumns + " from " + tableName + " T "); 196 if (!string.IsNullOrWhiteSpace(strWhere)) 197 { 198 strSql.Append(" WHERE " + strWhere); 199 } 200 strSql.Append(" ) TT"); 201 strSql.AppendFormat(" WHERE TT.Row between (({0}*{1})+1) and ((({0}+1)*{1}))", pageIndex, pageCount); 202 this.BuildParameters(whereData); 203 return db.Select(strSql.ToString()); 204 } 205 } 206 catch (Exception e) 207 { 208 throw e; 209 } 210 } 211 212 /// <summary> 213 /// 事务提交数据; 214 /// </summary> 215 /// <param name="sql">sql语句</param> 216 /// <param name="param">参数</param> 217 /// <returns></returns> 218 public bool ExecuteSQLByTransaction(string sql, DbParameter[] param) 219 { 220 if (string.IsNullOrWhiteSpace(sql)) 221 { 222 throw new ArgumentNullException("参数异常"); 223 } 224 try 225 { 226 using (db = Config.CreateDbHelper()) 227 { 228 int result = 0; 229 if (!db.HasBegunTransaction) 230 { 231 //循环添加参数; 232 if (param != null) 233 { 234 foreach (var pa in param) 235 { 236 db.AddParameter(pa.ParameterName, pa.Value); 237 } 238 } 239 try 240 { 241 db.BeginTransaction(); 242 result = db.ExecuteNonQuerySQL(sql); 243 db.CommitTransaction(); 244 } 245 catch (Exception ex) 246 { 247 db.RollbackTransaction(); 248 throw ex; 249 } 250 } 251 return result > 0 ? true : false; 252 } 253 } 254 catch (Exception e) 255 { 256 throw e; 257 } 258 } 259 260 /// <summary> 261 /// 执行sql语句得到返回结果 262 /// </summary> 263 /// <param name="sql">sql语句</param> 264 /// <returns></returns> 265 public object ExecuteScalerSQL(string sql) 266 { 267 if (string.IsNullOrWhiteSpace(sql)) 268 { 269 throw new ArgumentNullException("参数异常"); 270 } 271 try 272 { 273 using (db = Config.CreateDbHelper()) 274 { 275 return db.ExecuteScalerSQL(sql); 276 } 277 } 278 catch (Exception e) 279 { 280 throw e; 281 } 282 } 283 284 /// <summary> 285 /// 获取数据集数组 286 /// </summary> 287 /// <param name="sql">sql语句</param> 288 /// <returns></returns> 289 public DataTable[] ExecuteSQLEx(string sql, string[] tableNames) 290 { 291 if (string.IsNullOrWhiteSpace(sql)) 292 { 293 throw new ArgumentNullException("参数异常"); 294 } 295 try 296 { 297 using (db = Config.CreateDbHelper()) 298 { 299 return db.ExecuteSQLEx(sql, tableNames); 300 } 301 } 302 catch (Exception e) 303 { 304 305 throw e; 306 } 307 } 308 309 /// <summary> 310 /// 获取数据集数组 311 /// </summary> 312 /// <param name="sql">sql语句</param> 313 /// <param name="tableNames">表明</param> 314 /// <returns></returns> 315 public DataTable[] ExecuteSQLEx(string sql) 316 { 317 if (string.IsNullOrWhiteSpace(sql)) 318 { 319 throw new ArgumentNullException("参数异常"); 320 } 321 try 322 { 323 using (db = Config.CreateDbHelper()) 324 { 325 return db.ExecuteSQLEx(sql); 326 } 327 } 328 catch (Exception e) 329 { 330 throw e; 331 } 332 } 333 334 /// <summary> 335 /// 新增 336 /// </summary> 337 /// <param name="tableName"></param> 338 /// <param name="fieldNames"></param> 339 /// <param name="data"></param> 340 /// <returns></returns> 341 public int Insert(string tableName, string[] fieldNames, IDictionary<string, object> data) 342 { 343 if (string.IsNullOrWhiteSpace(tableName) || data==null) 344 { 345 throw new ArgumentNullException("参数异常"); 346 } 347 try 348 { 349 using (db = Config.CreateDbHelper()) 350 { 351 if (fieldNames.Length == 0) 352 { 353 return db.Insert(tableName, data); 354 } 355 else 356 { 357 return db.Insert(tableName, fieldNames,data); 358 } 359 } 360 } 361 catch (Exception e) 362 { 363 throw e; 364 } 365 } 366 367 /// <summary> 368 /// 修改 369 /// </summary> 370 /// <param name="tableName"></param> 371 /// <param name="fieldNames"></param> 372 /// <param name="data"></param> 373 /// <returns></returns> 374 public int Update(string tableName, string[] fieldNames, IDictionary<string, object> data) 375 { 376 if (string.IsNullOrWhiteSpace(tableName) || data == null || fieldNames.Length==0) 377 { 378 throw new ArgumentNullException("参数异常"); 379 } 380 try 381 { 382 using (db = Config.CreateDbHelper()) 383 { 384 return db.Update(tableName, fieldNames, data); 385 } 386 } 387 catch (Exception e) 388 { 389 throw e; 390 } 391 } 392 393 /// <summary> 394 /// 删除 395 /// </summary> 396 /// <param name="tableName"></param> 397 /// <param name="keyField"></param> 398 /// <param name="keyValue"></param> 399 /// <returns></returns> 400 public int Delete(string tableName, string keyField, object keyValue) 401 { 402 if (string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(keyField) || keyValue == null) 403 { 404 throw new ArgumentNullException("参数异常"); 405 } 406 try 407 { 408 using (db = Config.CreateDbHelper()) 409 { 410 return db.Delete(tableName, keyField, keyValue); 411 } 412 } 413 catch (Exception e) 414 { 415 throw e; 416 } 417 } 418 419 /// <summary> 420 /// 获取集合 421 /// </summary> 422 /// <param name="tableName"></param> 423 /// <param name="fieldNames"></param> 424 /// <param name="data"></param> 425 /// <returns></returns> 426 public IHashObjectList GetList(string tableName, string[] fieldNames, IDictionary<string, object> data) 427 { 428 if (string.IsNullOrWhiteSpace(tableName) || fieldNames==null) 429 { 430 throw new ArgumentNullException("参数异常"); 431 } 432 try 433 { 434 string sql = BuildSelectWhereSql(tableName, fieldNames, data); 435 this.BuildParameters(data); 436 return db.Select(sql); 437 } 438 catch (Exception e) 439 { 440 throw e; 441 } 442 } 443 444 #region 辅助方法 445 446 /// <summary> 447 /// 构建参数 448 /// </summary> 449 /// <param name="data"></param> 450 private void BuildParameters(IDictionary<string, object> data) 451 { 452 if (data != null && data.Count > 0) 453 { 454 foreach (var item in data) 455 { 456 this.AddParameter("@" + item.Key, "%" + item.Value + "%"); 457 } 458 } 459 } 460 461 /// <summary> 462 /// 添加参数 463 /// </summary> 464 /// <param name="parameterName"></param> 465 /// <param name="value"></param> 466 /// <returns></returns> 467 public DbParameter AddParameter(string parameterName, object value) 468 { 469 return db.AddParameter(parameterName, value); 470 } 471 472 /// <summary> 473 /// 构建查询条件语句 474 /// </summary> 475 /// <param name="tableName"></param> 476 /// <param name="fieldNames"></param> 477 /// <param name="data"></param> 478 /// <returns></returns> 479 public static string BuildSelectWhereSql(string tableName, string[] fieldNames, IDictionary<string, object> data) 480 { 481 StringBuilder builder = new StringBuilder(); 482 foreach (string str in fieldNames) 483 { 484 if (builder.Length != 0) 485 { 486 builder.Append(','); 487 } 488 builder.Append(str); 489 } 490 StringBuilder builderCon = new StringBuilder(); 491 if (data != null && data.Count > 0) 492 { 493 foreach (var item in data) 494 { 495 if (builderCon.Length != 0) 496 { 497 builderCon.Append(" and "); 498 } 499 builderCon.Append(item.Key + " like @" + item.Key); 500 } 501 return string.Format("select {0} from {1} where 1=1 and {2}", builder, tableName, builderCon); 502 } 503 else 504 { 505 return string.Format("select {0} from {1}", builder, tableName); 506 } 507 508 } 509 510 /// <summary> 511 /// 构建查询条件语句 512 /// </summary> 513 /// <param name="tableName"></param> 514 /// <param name="data"></param> 515 /// <returns></returns> 516 public static string BuildSelectWhereSql(IDictionary<string, object> data) 517 { 518 StringBuilder builderCon = new StringBuilder(); 519 if (data != null && data.Count > 0) 520 { 521 foreach (var item in data) 522 { 523 if (builderCon.Length != 0) 524 { 525 builderCon.Append(" and "); 526 } 527 builderCon.Append(item.Key + " like @" + item.Key); 528 } 529 return string.Format(" 1=1 and {0}", builderCon); 530 } 531 return ""; 532 } 533 534 #endregion 535 }
需要引用 carpa.dll 和carpa.web.dll。在哪里加入附件 ?
