1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Reflection;//反射命名空间
7
8 namespace LeaveWordBorad.DAL
9 {
10 /// <summary>
11 /// 数据层 - 数据库 操作类
12 /// </summary>
13 internal class DbHelperSQL
14 {
15 //获得配置文件的连接字符串
16 public static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
17
18 #region 01.查询数据表 +static DataTable GetTabel(string strSql, params SqlParameter[] paras)
19 /// <summary>
20 /// 查询数据表
21 /// </summary>
22 /// <param name="strSql">sql语句</param>
23 /// <param name="paras">参数数组</param>
24 /// <returns></returns>
25 public static DataTable GetDataTable(string strSql, params SqlParameter[] paras)
26 {
27 DataTable dt = null;
28 using (SqlConnection conn = new SqlConnection(strConn))
29 {
30 //创建 适配器对象（sql命令，连接通道）
31 SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
32 //添加参数
33 da.SelectCommand.Parameters.AddRange(paras);
34 //创建数据表对象
35 dt = new DataTable();
36 //适配器 读取数据库，并将查询的结果 装入程序的 dt里
37 da.Fill(dt);
38 }
39 return dt;
40 }
41 #endregion
42
43 #region 02.执行 增删改 (非查询语句) +int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
44 /// <summary>
45 /// 执行 增删改 (非查询语句)
46 /// </summary>
47 /// <param name="strSql"></param>
48 /// <param name="paras"></param>
49 /// <returns></returns>
50 public static int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
51 {
52 int res = -1;
53 //创建连接通道
54 using (SqlConnection conn = new SqlConnection(strConn))
55 {
56 //创建命令对象（sql语句，连接通道）
57 SqlCommand cmd = new SqlCommand(strSql, conn);
58 //添加参数
59 cmd.Parameters.AddRange(paras);
60 conn.Open();
61 res = cmd.ExecuteNonQuery();
62 }
63 return res;
64 }
65 #endregion
66
67 #region 02a.执行 多条增删改 (非查询语句) +int ExcuteNonQuery(string strSql, params SqlParameter[] paras)
68 /// <summary>
69 /// 执行 多条增删改 (非查询语句)
70 /// </summary>
71 /// <param name="strSql"></param>
72 /// <param name="paras"></param>
73 /// <returns></returns>
74 public static int ExcuteNonQuerys(string[] strSqls, SqlParameter[][] paras2Arr)
75 {
76 int res = 0;
77 //创建连接通道
78 using (SqlConnection conn = new SqlConnection(strConn))
79 {
80 conn.Open();
81 //创建 事务
82 SqlTransaction tran = conn.BeginTransaction();
83 //创建命令对象
84 SqlCommand cmd = new SqlCommand();
85 //为命令对象指定连接通道
86 cmd.Connection = conn;
87 //为命令对象指定事务
88 cmd.Transaction = tran;
89 try
90 {
91 //循环执行sql语句
92 for (int i = 0; i < strSqls.Length; i++)
93 {
94 //获得要执行的sql语句
95 string strSql = strSqls[i];
96 //为命令对象指定 此次执行的 sql语句
97 cmd.CommandText = strSql;
98 //添加参数
99 if (paras2Arr.Length > i)//如果 参数2维数组的长度大于当前循环的下标
100 {
101 cmd.Parameters.AddRange(paras2Arr[i]);//将 交错数组 的第一个元素（其实也是一个数组，添加到参数集合中）
102 }
103 res += cmd.ExecuteNonQuery();
104 cmd.Parameters.Clear();
105 }
106 tran.Commit();//提交事务
107 }
108 catch (Exception ex)
109 {
110 res = 0;
111 tran.Rollback();//回滚事务
112 throw ex;
113 }
114 }
115 return res;
116 }
117 #endregion
118
119 #region 02.执行 查询单个值 +int ExcuteScalar(string strSql, params SqlParameter[] paras)
120 /// <summary>
121 /// 执行 增删改 (非查询语句)
122 /// </summary>
123 /// <param name="strSql"></param>
124 /// <param name="paras"></param>
125 /// <returns></returns>
126 public static int ExcuteScalar(string strSql, params SqlParameter[] paras)
127 {
128 int res = -1;
129 //创建连接通道
130 using (SqlConnection conn = new SqlConnection(strConn))
131 {
132 //创建命令对象（sql语句，连接通道）
133 SqlCommand cmd = new SqlCommand(strSql, conn);
134 //添加参数
135 cmd.Parameters.AddRange(paras);
136 conn.Open();
137 res = Convert.ToInt32(cmd.ExecuteScalar());
138 }
139 return res;
140 }
141 #endregion
142
143 #region 04.执行 特殊的 分页存储过程 +DataTable GetPageListByProc(int pageIndex, int pageSize,out int pageCount,out int rowCount)
144 /// <summary>
145 ///04.执行 特殊的 分页存储过程
146 /// </summary>
147 /// <param name="proName">存储过程名称</param>
148 /// <param name="pageIndex">页码</param>
149 /// <param name="pageSize">页容量</param>
150 /// <param name="pageCount">总页数--输出</param>
151 /// <param name="rowCount">总行数--输出</param>
152 /// <returns></returns>
153 public static DataTable GetPageListByProc(string proName,int pageIndex, int pageSize,bool isDel, out int pageCount, out int rowCount)
154 {
155 DataTable dt = new DataTable();
156 //创建连接通道
157 using (SqlConnection conn = new SqlConnection(strConn))
158 {
159 //创建适配器对象
160 SqlDataAdapter da = new SqlDataAdapter(proName, conn);
161 //设置 命令类型 为存储过程
162 da.SelectCommand.CommandType = CommandType.StoredProcedure;
163 //设置 参数
164 da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex);//当前页码
165 da.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize);//页容量
166 da.SelectCommand.Parameters.AddWithValue("@isDel", isDel);//页容量
167
168 //在存储过程中 输出参数
169 da.SelectCommand.Parameters.Add(new SqlParameter("@pageCount", SqlDbType.Int));//总页数
170 da.SelectCommand.Parameters.Add(new SqlParameter("@rowCount", SqlDbType.Int));//总行数
171 //将后面两个参数 设置为 输出类型
172 da.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;
173 da.SelectCommand.Parameters[4].Direction = ParameterDirection.Output;
174
175 //执行 并将查询到的 结果 赋给 数据表对象
176 da.Fill(dt);
177 //获得 存储过程 返回的 输出参数
178 pageCount = Convert.ToInt32(da.SelectCommand.Parameters[3].Value);
179 rowCount = Convert.ToInt32(da.SelectCommand.Parameters[4].Value);
180 }
181 //返回数据表
182 return dt;
183 }
184 #endregion
185
186 #region 04.执行 特殊的 分页存储过程 +DataTable GetPageListByProc(int pageIndex, int pageSize,out int pageCount,out int rowCount)
187 /// <summary>
188 ///04.执行 特殊的 分页存储过程
189 /// </summary>
190 /// <param name="proName">存储过程名称</param>
191 /// <param name="pageIndex">页码</param>
192 /// <param name="pageSize">页容量</param>
193 /// <param name="userId">用户id</param>
194 /// <param name="pageCount">总页数--输出</param>
195 /// <param name="rowCount">总行数--输出</param>
196 /// <returns></returns>
197 public static int ExcuteNonQueryWithProc(string proName, params SqlParameter[] paras)
198 {
199 DataTable dt = new DataTable();
200 //创建连接通道
201 using (SqlConnection conn = new SqlConnection(strConn))
202 {
203 SqlCommand cmd = new SqlCommand(proName,conn);
204 cmd.CommandType = CommandType.StoredProcedure;
205 cmd.Parameters.AddRange(paras);
206 conn.Open();
207 return cmd.ExecuteNonQuery();
208 }
209 }
210 #endregion
211
212 #region 01.3为 对象数据源控件 提供 分页数据
213 /// <summary>
214 /// 01.3为 对象数据源控件 提供 分页数据
215 /// </summary>
216 /// <param name="pageSize">页容量</param>
217 /// <param name="startRowIndex">起始行下标</param>
218 /// <returns></returns>
219 public static DataTable GetPagedListForObjectDataSource(int pageSize, int startRowIndex)
220 {
221 string strSql = "select * from(select Row_Number() over(order by cid) as num, * from Classes)as temp where num>@startRowIndex and num<=@size";
222 SqlParameter[] paras = {
223 new SqlParameter("@startRowIndex",startRowIndex),
224 new SqlParameter("@size",pageSize+startRowIndex)
225 };
226 return GetDataTable(strSql, paras);
227 }
228 #endregion
229
230 #region 3.执行查询多行语句 - 返回数据读取器 +static SqlDataReader ExcuteDataReader(string strSelectCmd, params SqlParameter[] paras)
231 /// <summary>
232 /// 执行查询多行语句 - 返回数据读取器
233 /// </summary>
234 /// <param name="strSelectCmd"></param>
235 /// <param name="paras"></param>
236 /// <returns></returns>
237 public static SqlDataReader ExcuteDataReader(string strSelectCmd, params SqlParameter[] paras)
238 {
239 SqlConnection conn = null;
240 try
241 {
242 //1.创建连接通道
243 conn = new SqlConnection(strConn);
244 //2.创建命令对象
245 SqlCommand cmd = new SqlCommand(strSelectCmd, conn);
246 //3.添加命令参数
247 cmd.Parameters.AddRange(paras);
248 //4.打开连接
249 conn.Open();
250 //5.创建读取器（当关闭此读取器时，会自动关闭连接通道）
251 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//当关闭此读取器时，会自动关闭连接通道
252 //6.返回读取器
253 return dr;
254 }
255 catch (Exception ex)
256 {
257 conn.Dispose();
258 throw ex;
259 }
260 }
261 #endregion
262
263 #region 2.0升级泛型版 ------ 执行查询多行语句 - 返回数据表
264 /// <summary>
265 /// 2.0升级泛型版 ------ 执行查询多行语句 - 返回数据表
266 /// </summary>
267 /// <typeparam name="T2">泛型类型</typeparam>
268 /// <param name="strSelectCmd">查询sql语句</param>
269 /// <param name="paras">查询参数</param>
270 /// <returns>泛型集合</returns>
271 public static List<T2> ExcuteList<T2>(string strSelectCmd, params SqlParameter[] paras)
272 {
273 //1.创建连接通道
274 using (SqlConnection conn = new SqlConnection(strConn))
275 {
276 //2.创建适配器
277 SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);
278 //2.1设置查询命令的参数
279 da.SelectCommand.Parameters.AddRange(paras);
280 //3.数据表
281 DataTable dt = new DataTable();
282 //4.将数据查询并填充到数据表中
283 da.Fill(dt);
284 //5.将DataTable转成泛型集合List<T2>
285 if (dt.Rows.Count > 0)
286 {
287 //6.创建泛型集合对象
288 List<T2> list = new List<T2>();
289 //7.遍历数据行，将行数据存入 实体对象中，并添加到 泛型集合中list
290 foreach (DataRow row in dt.Rows)
291 {
292 //留言：等学完反射后再讲~~~~！
293 //7.1先获得泛型的类型(里面包含该类的所有信息---有什么属性啊，有什么方法啊，什么字段啊....................)
294 Type t = typeof(T2);
295 //7.2根据类型创建该类型的对象
296 T2 model = (T2)Activator.CreateInstance(t);// new MODEL.Classes()
297 //7.3根据类型 获得 该类型的 所有属性定义
298 PropertyInfo[] properties = t.GetProperties();
299 //7.4遍历属性数组
300 foreach (PropertyInfo p in properties)
301 {
302 //7.4.1获得属性名，作为列名
303 string colName = p.Name;
304 //7.4.2根据列名 获得当前循环行对应列的值
305 object colValue = row[colName];
306 //7.4.3将 列值 赋给 model对象的p属性
307 //model.ID=colValue;
308 p.SetValue(model, colValue, null);
309 }
310 //7.5将装好 了行数据的 实体对象 添加到 泛型集合中 O了！！！
311 list.Add(model);
312 }
313 return list;
314 }
315 }
316 return null;
317 }
318 #endregion
319
320 #region 6.查询结果集里的第一个单元格的值（单个值）-- 泛型版本 + static T ExcuteScalar<T>(string strSelectCmd, params SqlParameter[] paras)
321 /// <summary>
322 /// 查询结果集里的第一个单元格的值（单个值）-- 泛型版本
323 /// </summary>
324 /// <typeparam name="T">类型参数</typeparam>
325 /// <param name="strSelectCmd"></param>
326 /// <param name="paras"></param>
327 /// <returns></returns>
328 public static T ExcuteScalar<T>(string strSelectCmd, params SqlParameter[] paras)
329 {
330 using (SqlConnection conn = new SqlConnection(strConn))
331 {
332 SqlCommand cmd = new SqlCommand(strSelectCmd, conn);
333 cmd.Parameters.AddRange(paras);
334 conn.Open();
335 object o = cmd.ExecuteScalar();
336 return (T)Convert.ChangeType(o, typeof(T));
337 }
338 }
339 #endregion
340
341 #region 将数据表 转成对应 T2 类型的泛型集合对象
342 /// <summary>
343 /// 将数据表 转成对应 T2 类型的泛型集合对象
344 /// </summary>
345 /// <typeparam name="T2">泛型类型</typeparam>
346 /// <returns>泛型集合</returns>
347 public static List<T2> Table2List<T2>(DataTable dt)
348 {
349 //5.将DataTable转成泛型集合List<T2>
350 if (dt.Rows.Count > 0)
351 {
352 //6.创建泛型集合对象
353 List<T2> list = new List<T2>();
354 //7.遍历数据行，将行数据存入 实体对象中，并添加到 泛型集合中list
355 foreach (DataRow row in dt.Rows)
356 {
357 //留言：等学完反射后再讲~~~~！
358 //7.1先获得泛型的类型(里面包含该类的所有信息---有什么属性啊，有什么方法啊，什么字段啊....................)
359 Type t = typeof(T2);
360 //7.2根据类型创建该类型的对象
361 T2 model = (T2)Activator.CreateInstance(t);// new MODEL.Classes()
362 //7.3根据类型 获得 该类型的 所有属性定义
363 PropertyInfo[] properties = t.GetProperties();
364 //7.4遍历属性数组
365 foreach (PropertyInfo p in properties)
366 {
367 //7.4.1获得属性名，作为列名
368 string colName = p.Name;
369 //7.4.2根据列名 获得当前循环行对应列的值
370 object colValue = row[colName];
371 //7.4.3将 列值 赋给 model对象的p属性
372 //model.ID=colValue;
373 p.SetValue(model, colValue, null);
374 }
375 //7.5将装好 了行数据的 实体对象 添加到 泛型集合中 O了！！！
376 list.Add(model);
377 }
378 return list;
379 }
380 return null;
381 }
382 #endregion
383
384 public static void PrepareCommand(SqlCommand cmd, SqlConnection conn,SqlTransaction trans, CommandType type, string cmdText, SqlParameter[] cmdParms)
385 {
386 if (conn.State != ConnectionState.Open) conn.Open();
387 cmd.Connection = conn;
388 cmd.CommandText = cmdText;
389 cmd.CommandType = type;
390 if (trans != null) cmd.Transaction = trans;
391 if (cmdParms != null)
392 {
393 foreach (SqlParameter parameter in cmdParms)
394 {
395 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
396 (parameter.Value == null))
397 {
398 parameter.Value = DBNull.Value;
399 }
400 cmd.Parameters.Add(parameter);
401 }
402 }
403 }
404 }
405 }
