DBHelper

　Content: 把李天平老師的代碼拿過來學習

　　原文地址:http://www.49028c.com/aivdesign/articles/1263247.html

　　1 using System;   2 using System.Collections;   3 using System.Collections.Specialized;   4 using System.Data;   5 using System.Data.SqlClient;   6 using System.Configuration;   7 using System.Data.Common;   8 using System.Collections.Generic;   9 namespace Maticsoft.DBUtility  10 {  11     ///   12     /// 數據訪問抽象基礎類  13     /// Copyright (C) 2004-2008 By LiTianPing   14     ///   15     public abstract class DbHelperSQL  16     {  17         //數據庫連接字符串(web.config來配置)，可以動態更改connectionString支持多數據庫.    18         public static string connectionString = PubConstant.ConnectionString;         19         public DbHelperSQL()  20         {              21         }  22   23         #region 公用方法  24         ///   25         /// 判斷是否存在某表的某個字段  26         ///   27         /// 表名稱  28         /// 列名稱  29         /// 是否存在  30         public static bool ColumnExists(string tableName, string columnName)  31         {  32             string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";  33             object res = GetSingle(sql);  34             if (res == null)  35             {  36                 return false;  37             }  38             return Convert.ToInt32(res) > 0;  39         }  40         public static int GetMaxID(string FieldName, string TableName)  41         {  42             string strsql = "select max(" + FieldName + ")+1 from " + TableName;  43             object obj = DbHelperSQL.GetSingle(strsql);  44             if (obj == null)  45             {  46                 return 1;  47             }  48             else  49             {  50                 return int.Parse(obj.ToString());  51             }  52         }  53         public static bool Exists(string strSql)  54         {  55             object obj = DbHelperSQL.GetSingle(strSql);  56             int cmdresult;  57             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  58             {  59                 cmdresult = 0;  60             }  61             else  62             {  63                 cmdresult = int.Parse(obj.ToString());  64             }  65             if (cmdresult == 0)  66             {  67                 return false;  68             }  69             else  70             {  71                 return true;  72             }  73         }  74         ///   75         /// 表是否存在  76         ///   77         ///   78         ///   79         public static bool TabExists(string TableName)  80         {  81             string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";  82             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";  83             object obj = DbHelperSQL.GetSingle(strsql);  84             int cmdresult;  85             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  86             {  87                 cmdresult = 0;  88             }  89             else  90             {  91                 cmdresult = int.Parse(obj.ToString());  92             }  93             if (cmdresult == 0)  94             {  95                 return false;  96             }  97             else  98             {  99                 return true; 100             } 101         } 102         public static bool Exists(string strSql, params SqlParameter[] cmdParms) 103         { 104             object obj = DbHelperSQL.GetSingle(strSql, cmdParms); 105             int cmdresult; 106             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 107             { 108                 cmdresult = 0; 109             } 110             else 111             { 112                 cmdresult = int.Parse(obj.ToString()); 113             } 114             if (cmdresult == 0) 115             { 116                 return false; 117             } 118             else 119             { 120                 return true; 121             } 122         } 123         #endregion 124  125         #region  執行簡單SQL語句 126  127         ///  128         /// 執行SQL語句，返回影響的記錄數 129         ///  130         /// SQL語句 131         /// 影響的記錄數 132         public static int ExecuteSql(string SQLString) 133         { 134             using (SqlConnection connection = new SqlConnection(connectionString)) 135             { 136                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 137                 { 138                     try 139                     { 140                         connection.Open(); 141                         int rows = cmd.ExecuteNonQuery(); 142                         return rows; 143                     } 144                     catch (System.Data.SqlClient.SqlException e) 145                     { 146                         connection.Close(); 147                         throw e; 148                     } 149                 } 150             } 151         } 152  153         public static int ExecuteSqlByTime(string SQLString, int Times) 154         { 155             using (SqlConnection connection = new SqlConnection(connectionString)) 156             { 157                 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 158                 { 159                     try 160                     { 161                         connection.Open(); 162                         cmd.CommandTimeout = Times; 163                         int rows = cmd.ExecuteNonQuery(); 164                         return rows; 165                     } 166                     catch (System.Data.SqlClient.SqlException e) 167                     { 168                         connection.Close(); 169                         throw e; 170                     } 171                 } 172             } 173         } 174        175         ///  176         /// 執行Sql和Oracle滴混合事務 177         ///  178         /// SQL命令行列表 179         /// Oracle命令行列表 180         /// 執行結果 0-由于SQL造成事務失敗 -1 由于Oracle造成事務失敗 1-整體事務執行成功 181         public static int ExecuteSqlTran(List list, List oracleCmdSqlList) 182         { 183             using (SqlConnection conn = new SqlConnection(connectionString)) 184             { 185                 conn.Open(); 186                 SqlCommand cmd = new SqlCommand(); 187                 cmd.Connection = conn; 188                 SqlTransaction tx = conn.BeginTransaction(); 189                 cmd.Transaction = tx; 190                 try 191                 { 192                     foreach (CommandInfo myDE in list) 193                     { 194                         string cmdText = myDE.CommandText; 195                         SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 196                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms); 197                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent) 198                         { 199                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 200                             { 201                                 tx.Rollback(); 202                                 throw new Exception("違背要求"+myDE.CommandText+"必須符合select count(..的格式"); 203                                 //return 0; 204                             } 205  206                             object obj = cmd.ExecuteScalar(); 207                             bool isHave = false; 208                             if (obj == null && obj == DBNull.Value) 209                             { 210                                 isHave = false; 211                             } 212                             isHave = Convert.ToInt32(obj) > 0; 213                             if (isHave) 214                             { 215                                 //引發事件 216                                 myDE.OnSolicitationEvent(); 217                             } 218                         } 219