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                 catch
109                 {
110 
111                 }
112                 finally
113                 {
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                 try
130                 {
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                 catch
143                 {
144 
145                 }
146                 finally
147                 {
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                 try
164                 {
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                 catch
176                 {
177 
178                 }
179                 finally
180                 {
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                 try
197                 {
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                 catch
210                 {
211 
212                 }
213                 finally
214                 {
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 }