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 }
