C# SQL數據庫助手類2.0（自用）

  1 using System;  2 using System.Collections.Generic;  3 using System.Configuration;  4 using System.Data;  5 using System.Data.SqlClient;  6 using System.Text;  7   8 namespace YcTools  9 { 10     /// <summary>C# SQL數據庫助手類2.0</summary> 11     public class YSqlHelper 12     { 13         //Sql連接語句 14         /*注意引用System.Configuration 15          * <connectionStrings> 16          * <add name="YcSqlCon"  17          * connectionString="Data Source=服務器;Initial Catalog=數據庫;User ID=登錄名;PassWord=密碼"  18          * 存儲過程 ，返回受影響的行數</summary> 30         public int ExecuteNonQuery(string cmdText) 31         { 32             int res = 0;//受影響的行數 33             using (SqlConnection conn = new SqlConnection(connectionString)) 34             { 35                 try 36                 { 37                     conn.Open();//打開數據庫鏈接 38                     using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 39                     { 40                         cmd.CommandType = CommandType.Text; 41                         res = cmd.ExecuteNonQuery();//執行Sql語句并受影響的行數 42                     } 43                 } 44                 catch 45                 { 46  47                 } 48                 finally 49                 { 50                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態 51                     { 52                         conn.Close();//關閉與數據庫的鏈接 53                     } 54                 } 55             } 56             return res; 57         } 58  59         /// <summary>  執行帶參數的增刪改SQL語句或存儲過程，返回受影響的行數</summary> 60         public int ExecuteNonQuery(string cmdText, SqlParameter[] paras) 61         { 62             int res = 0;//受影響的行數 63             using (SqlConnection conn = new SqlConnection(connectionString)) 64             { 65                 try 66                 { 67                     conn.Open();//打開數據庫鏈接 68                     using (SqlCommand cmd = new SqlCommand(cmdText, conn)) 69                     { 70                         cmd.CommandType = CommandType.Text; 71                         cmd.Parameters.AddRange(paras); 72                         res = cmd.ExecuteNonQuery();//執行Sql語句并受影響的行數 73                     } 74                 } 75                 catch 76                 { 77  78                 } 79                 finally 80                 { 81                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態 82                     { 83                         conn.Close();//關閉與數據庫的鏈接 84                     } 85                 } 86             } 87             return res; 88         } 89  90         /// <summary> 執行不帶參數的查詢SQL語句或存儲過程，返回DataTable對象</summary> 91         public DataTable ExecuteQueryDataTable(string cmdText) 92         { 93             DataTable dt = new DataTable(); 94             using (SqlConnection conn = new SqlConnection(connectionString)) 95             { 96                 try 97                 { 98                     conn.Open();//打開數據庫鏈接 99                     using (SqlCommand cmd = new SqlCommand(cmdText, conn))100                     {101                         cmd.CommandType = CommandType.Text;102                         using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))103                         {104                             dt.Load(sdr);105                         }106                     }107                 }108                 catch109                 {110 111                 }112                 finally113                 {114                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態115                     {116                         conn.Close();//關閉與數據庫的鏈接117                     }118                 }119             }120             return dt;121         }122 123         /// <summary> 執行帶參數的查詢SQL語句或存儲過程，返回DataTable對象</summary>124         public DataTable ExecuteQueryDataTable(string cmdText, SqlParameter[] paras)125         {126             DataTable dt = new DataTable();127             using (SqlConnection conn = new SqlConnection(connectionString))128             {129                 try130                 {131                     conn.Open();//打開數據庫鏈接132                     using (SqlCommand cmd = new SqlCommand(cmdText, conn))133                     {134                         cmd.CommandType = CommandType.Text;135                         cmd.Parameters.AddRange(paras);136                         using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))137                         {138                             dt.Load(sdr);139                         }140                     }141                 }142                 catch143                 {144 145                 }146                 finally147                 {148                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態149                     {150                         conn.Close();//關閉與數據庫的鏈接151                     }152                 }153             }154             return dt;155         }156 157         /// <summary> 執行不帶參數的查詢SQL語句或存儲過程，返回DataSet對象</summary>158         public DataSet ExecuteQueryDataSet(string cmdText)159         {160             DataSet ds = new DataSet();161             using (SqlConnection conn = new SqlConnection(connectionString))162             {163                 try164                 {165                     conn.Open();//打開數據庫鏈接166                     using (SqlCommand cmd = new SqlCommand(cmdText, conn))167                     {168                         cmd.CommandType = CommandType.Text;169                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))170                         {171                             da.Fill(ds, "ds");172                         }173                     }174                 }175                 catch176                 {177 178                 }179                 finally180                 {181                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態182                     {183                         conn.Close();//關閉與數據庫的鏈接184                     }185                 }186             }187             return ds;188         }189 190         /// <summary> 執行帶參數的查詢SQL語句或存儲過程，返回DataSet對象</summary>191         public DataSet ExecuteQueryDataSet(string cmdText, SqlParameter[] paras)192         {193             DataSet ds = new DataSet();194             using (SqlConnection conn = new SqlConnection(connectionString))195             {196                 try197                 {198                     conn.Open();//打開數據庫鏈接199                     using (SqlCommand cmd = new SqlCommand(cmdText, conn))200                     {201                         cmd.CommandType = CommandType.Text;202                         cmd.Parameters.AddRange(paras);203                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))204                         {205                             da.Fill(ds, "ds");206                         }207                     }208                 }209                 catch210                 {211 212                 }213                 finally214                 {215                     if (conn.State == ConnectionState.Open)//判斷連接是否處于打開狀態216                     {217                         conn.Close();//關閉與數據庫的鏈接218                     }219                 }220             }221             return ds;222         }223 224         /// <summary>查詢數據是否存在</summary>225         public bool ExecuteDataIsExistByData(string sqlSt