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          * providerName="System.Data.SqlClient"/> 19          * </connectionStrings> 20          */ 21         // private string connectionString = ConfigurationManager.ConnectionStrings["YcSqlCon"].ConnectionString; 22         private string connectionString = ""; 23         public YSqlHelper(string sqlPath) 24         { 25             //实例化对应的数据库链接 26             connectionString = ConfigurationManager.ConnectionStrings[sqlPath].ConnectionString; 27         } 28  29         /// <summary>执行不带参数的增删改SQL语句或存储过程 ，返回受影响的行数</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 sqlStr)226         {227             bool iss = false;228             DataSet ds = ExecuteQueryDataSet(sqlStr);229             for (int i = 0; i < ds.Tables.Count; i++)230             {231                 if (ds.Tables[i].Rows.Count > 0) iss = true;232             }233             return iss;234         }235 236         /// <summary>查询数据是否存在 </summary>237         public bool ExecuteDataIsExistByData(string sqlStr, SqlParameter[] paras)238         {239             bool iss = false;240             DataSet ds = ExecuteQueryDataSet(sqlStr, paras);241             for (int i = 0; i < ds.Tables.Count; i++)242             {243                 if (ds.Tables[i].Rows.Count > 0) iss = true;244             }245             return iss;246         }247 248         /// <summary>查询增删改数据操作是否成功 </summary>249         public bool ExecuteDataIsExistByInt(string sqlStr)250         {251             int ds = ExecuteNonQuery(sqlStr);252             bool iss = ds > 0 ? true : false;253             return iss;254         }255 256         /// <summary>查询增删改数据操作是否成功 </summary>257         public bool ExecuteDataIsExistByInt(string sqlStr, SqlParameter[] paras)258         {259             int ds = ExecuteNonQuery(sqlStr, paras);260             bool iss = ds > 0 ? true : false;261             return iss;262         }263     }264 }