1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Configuration;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Collections;
8
9 namespace Common
10 {
11 /// <summary>
12 /// SqlHelper类提供很高的数据访问性能,
13 /// 使用SqlClient类的通用定义.
14 /// </summary>
15 public abstract class SqlHelper
16 {
17 //定义数据库连接串
18 public static readonly string CONN_STRING =
19 ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
20
21 public static readonly string CONN = "server=.;uid=sa;pwd=sa;database=MyOffice";
22 // 存贮Cache缓存的Hashtable集合
23 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
24
25 /// <summary>
26 /// 使用连接字符串，执行一个SqlCommand命令（没有记录返回）
27 /// 使用提供的参数集.
28 /// </summary>
29 /// <param name="connectionString">一个有效的SqlConnection连接串</param>
30 /// <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>
31 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
32 /// <param name="commandParameters">执行命令的参数集</param>
33 /// <returns>受此命令影响的行数</returns>
34 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
35 {
36
37 SqlCommand cmd = new SqlCommand();
38
39 using (SqlConnection conn = new SqlConnection(connectionString))
40 {
41 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
42
43 int val = cmd.ExecuteNonQuery();
44
45 cmd.Parameters.Clear();
46
47 return val;
48 }
49 }
50
51 /// <summary>
52 /// 在一个存在的连接上执行数据库的命令操作
53 /// 使用提供的参数集.
54 /// </summary>
55 /// <param name="conn">一个存在的数据库连接对象</param>
56 /// <param name="commandType">命令类型CommandType (stored procedure, text, etc.)</param>
57 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
58 /// <param name="commandParameters">执行命令的参数集</param>
59 /// <returns>受此命令影响的行数</returns>
60 public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
61 {
62
63 SqlCommand cmd = new SqlCommand();
64
65 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
66
67 int val = cmd.ExecuteNonQuery();
68
69 cmd.Parameters.Clear();
70
71 return val;
72
73 }
74
75
76 /// <summary>
77 /// 在一个事务的连接上执行数据库的命令操作
78 /// 使用提供的参数集.
79 /// </summary>
80 /// <param name="trans">一个存在的事务</param>
81 /// <param name="commandType">命令类型CommandType (stored procedure, text, etc.)</param>
82 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
83 /// <param name="commandParameters">执行命令的参数集</param>
84 /// <returns>受此命令影响的行数</returns>
85 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
86 {
87 SqlCommand cmd = new SqlCommand();
88 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
89
90 int val = cmd.ExecuteNonQuery();
91
92 cmd.Parameters.Clear();
93
94 return val;
95 }
96
97 /// <summary>
98 /// 在一个连接串上执行一个命令，返回一个SqlDataReader对象
99 /// 使用提供的参数.
100 /// </summary>
101 /// <param name="connectionString">一个有效的SqlConnection连接串</param>
102 /// <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>
103 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
104 /// <param name="commandParameters">执行命令的参数集</param>
105 /// <returns>一个结果集对象SqlDataReader</returns>
106 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
107 {
108 SqlCommand cmd = new SqlCommand();
109
110 SqlConnection conn = new SqlConnection(connectionString);
111
112
113 // 如果不存在要查询的对象，则发生异常
114 // 连接要关闭
115 // CommandBehavior.CloseConnection在异常时不发生作用
116 try
117 {
118 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
119
120 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
121
122 cmd.Parameters.Clear();
123
124 return rdr;
125 }
126 catch
127 {
128 conn.Close();
129 throw;
130 }
131 }
132
133
134
135 /// <summary>
136 /// 在一个连接串上执行一个命令，返回表中第一行，第一列的值
137 /// 使用提供的参数.
138 /// </summary>
139 /// <param name="connectionString">一个有效的SqlConnection连接串</param>
140 /// <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>
141 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
142 /// <param name="commandParameters">执行命令的参数集</param>
143 /// <returns>返回的对象，在使用时记得类型转换</returns>
144 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
145 {
146 SqlCommand cmd = new SqlCommand();
147
148 using (SqlConnection connection = new SqlConnection(connectionString))
149 {
150 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
151
152 object val = cmd.ExecuteScalar();
153
154 cmd.Parameters.Clear();
155
156 return val;
157 }
158 }
159
160
161
162 /// <summary>
163 /// 在一个连接上执行一个命令，返回表中第一行，第一列的值
164 /// 使用提供的参数.
165 /// </summary>
166 /// <param name="connectionString">一个有效的SqlConnection连接</param>
167 /// <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>
168 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
169 /// <param name="commandParameters">执行命令的参数集</param>
170 /// <returns>返回的对象，在使用时记得类型转换</returns>
171 public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
172 {
173 SqlCommand cmd = new SqlCommand();
174
175 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
176
177 object val = cmd.ExecuteScalar();
178
179 cmd.Parameters.Clear();
180
181 return val;
182 }
183
184 /// <summary>
185 /// 在一个连接串上执行一个命令，返回数据集
186 /// 使用提供的参数.
187 /// </summary>
188 /// <param name="connectionString">一个有效的SqlConnection连接串</param>
189 /// <param name="cmdType">命令类型CommandType(stored procedure, text, etc.)</param>
190 /// <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>
191 /// <param name="commandParameters">执行命令的参数集</param>
192 /// <returns>返回数据集</returns>
193 public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
194 {
195
196 SqlCommand cmd = new SqlCommand();
197
198 using (SqlConnection conn = new SqlConnection(connectionString))
199 {
200 PrepareCommand(cmd, conn, null, commandType, commandText, commandParameters);
201
202 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
203 DataSet ds = new DataSet();
204 adapter.Fill(ds);
205
206 cmd.Parameters.Clear();
207 return ds;
208 }
209 }
210
211 /// <summary>
212 /// 在缓存中添加参数数组
213 /// </summary>
214 /// <param name="cacheKey">参数的Key</param>
215 /// <param name="cmdParms">参数数组</param>
216 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
217 {
218 parmCache = commandParameters;
219 }
220
221
222
223 /// <summary>
224 /// 提取缓存的参数数组
225 /// </summary>
226 /// <param name="cacheKey">查找缓存的key</param>
227 /// <returns>返回被缓存的参数数组</returns>
228 public static SqlParameter[] GetCachedParameters(string cacheKey)
229 {
230 SqlParameter[] cachedParms = (SqlParameter[])parmCache;
231
232 if (cachedParms == null)
233 return null;
234
235 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
236
237 for (int i = 0, j = cachedParms.Length; i < j; i++)
238 clonedParms = (SqlParameter)((ICloneable)cachedParms).Clone();
239
240 return clonedParms;
241 }
242
243
244
245 /// <summary>
246 /// 提供一个SqlCommand对象的设置
247 /// </summary>
248 /// <param name="cmd">SqlCommand对象</param>
249 /// <param name="conn">SqlConnection 对象</param>
250 /// <param name="trans">SqlTransaction 对象</param>
251 /// <param name="cmdType">CommandType 如存贮过程，T-SQL</param>
252 /// <param name="cmdText">存贮过程名或查询串</param>
253 /// <param name="cmdParms">命令中用到的参数集</param>
254 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
255 {
256 if (conn.State != ConnectionState.Open)
257 conn.Open();
258
259 cmd.Connection = conn;
260 cmd.CommandText = cmdText;
261
262 if (trans != null)
263 cmd.Transaction = trans;
264
265 cmd.CommandType = cmdType;
266
267 if (cmdParms != null)
268 {
269 foreach (SqlParameter parm in cmdParms)
270 cmd.Parameters.Add(parm);
271 }
272 }
273 }
274 }
275
276 ----访问----
277 using System;
278 using System.Collections.Generic;
279 using System.Text;
280 using System.Data;
281 using System.Data.SqlClient;
282 using Common;
283 using team1.Model;
284
285 namespace team1.SQLServerDAL
286 {
287
288
289 /// <summary>
290 /// 员工信息表的数据访问操作
291 /// </summary>
292 public partial class UserInfoService
293 {
294
295
296 /// <summary>
297 /// 获得所有员工信息表
298 /// </summary>
299 /// <returns>员工信息表集合</returns>
300 public IList<UserInfo> GetAllUserInfos2()
301 {
302 //string sql = "SELECT UserId,UserName,Password,DepartId,Gender,UserStateId " +
303 // "FROM UserInfo";
304
305 IList<UserInfo> allUserInfos = new List<UserInfo>();
306
307 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
308 SqlHelper.CONN_STRING, CommandType.StoredProcedure, ""))
309 {
310 while (rdr.Read())
311 {
312 UserInfo userInfo = new UserInfo();
313 userInfo.UserId = (string)rdr["UserId"];
314 userInfo.UserName = (string)rdr["UserName"];
315 userInfo.Password = (string)rdr["Password"];
316 userInfo.DepartId = (int)rdr["DepartId"];
317 userInfo.Gender = (int)rdr["Gender"];
318 userInfo.UserStateId = (int)rdr["UserStateId"];
319 userInfo.UserStateName = (string)rdr["UserStateName"];
320 userInfo.DepartName = (string)rdr["DepartName"];
321 userInfo.RoleName = (string)rdr["RoleName"];
322
323 allUserInfos.Add(userInfo);
324 }
325 }
326
327 return allUserInfos;
328 }
329
330
331 #region 验证用户登陆信息
332
333 public UserInfo CheckLogin(string loginID, string loginPwd)
334 {
335 SqlParameter[] pas = new SqlParameter[]
336 {
337 new SqlParameter("@userId",SqlDbType.VarChar,50),
338 new SqlParameter("@password",SqlDbType.VarChar,50)
339 };
340
341 pas[0].Value = loginID;
342 pas[1].Value = loginPwd;
343
344 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
345 SqlHelper.CONN_STRING, CommandType.StoredProcedure, "", pas))
346 {
347 if (rdr.Read())
348 {
349 UserInfo userInfo = new UserInfo();
350 userInfo.UserId = (string)rdr["UserId"];
351 userInfo.UserName = (string)rdr["UserName"];
352 userInfo.Password = (string)rdr["Password"];
353 userInfo.DepartId = (int)rdr["DepartId"];
354 userInfo.Gender = (int)rdr["Gender"];
355 userInfo.UserStateId = (int)rdr["UserStateId"];
356 userInfo.DepartName = (string)rdr["DepartName"];
357 userInfo.RoleName = (string)rdr["RoleName"];
358 userInfo.RoleId = (int)rdr["RoleId"];
359 return userInfo;
360 }
361 }
362 return null;
363 }
364
365 /// <summary>
366 /// 判断用户名称是否存在
367 /// </summary>
368 /// <param name="loginID">用户登陆ID</param>
369 /// <returns></returns>
370 public bool CheckLoginByLoginId(string loginID)
371 {
372 string sql = "Select * from userInfo where UserId='" + loginID + "'";
373 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
374 SqlHelper.CONN_STRING, CommandType.Text, sql))
375 {
376 if (rdr.Read())
377 {
378 return true;
379 }
380 return false;
381 }
382 }
383
384 //根据用户名称获得密码
385 public string CheckLoginByLoginPwd(string loginID)
386 {
387 string sql = "Select password from userInfo where UserId='" + loginID + "'";
388 return (string)SqlHelper.ExecuteScalar(SqlHelper.CONN_STRING, CommandType.Text, sql);
389 }
390 #endregion
391
392 /// <summary>
393 /// 根据输入用户名前缀返回相关用户名
394 /// </summary>
395 /// <param name="foreName">用户输入姓名前缀</param>
396 /// <param name="count">数量</param>
397 /// <returns></returns>
398 public IList<string> GetUsersByPreName(string foreName, int count)
399 {
400 IList<string> user = new List<string>();
401
402 string sql = "select top " + count + " * from UserInfo where UserName like '" + foreName.Trim() + "%'";
403
404 using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.CONN_STRING, CommandType.Text, sql, null))
405 {
406 int i = 0;
407 while (rdr.Read())
408 {
409
410 UserInfo userInfo = new UserInfo();
411
412 userInfo.UserId = (string)rdr["UserId"];
413 userInfo.UserName = (string)rdr["UserName"];
414
415 try
416 {
417 user.Add(userInfo.UserName);
418 }
419 catch (Exception ex)
420 {
421 Console.WriteLine(ex.Message);
422 }
423
424
425 }
426 }
427 return user;
428 }
429
430 /// <summary>
431 /// 点击搜索范围时根据条件进行查询所有用户姓名
432 /// </summary>
433 /// <param name="branchId">机构ID</param>
434 /// <param name="departId">部门ID</param>
435 /// <param name="loginId">工号</param>
436 /// <param name="userName">用户姓名</param>
437 /// <returns></returns>
438 public IList<UserInfo> GetUserInfoSearch(int branchId, int departId, string loginId, string userName)
439 {
440 string sql = "select u.userName,u.userId from userInfo u join departInfo " +
441 "d on(u.departId=d.departId) where 1=1";
442 if (branchId != 0)
443 {
444 sql += " and d.branchId=" + branchId;
445 }
446 if (departId != 0)
447 {
448 sql += " and u.departId =" + departId;
449 }
450 if (userName != null && !userName.Trim().Equals(""))
451 {
452 sql += " and u.userName = '" + userName + "'";
453 }
454
455 if (loginId != null && !"".Equals(loginId.Trim()))
456 {
457 sql += " and u.loginId='" + loginId + "'";
458 }
459
460 if (branchId == 0 && departId == 0 && loginId.Trim() == "" && userName.Trim() == "")
461 {
462 return null;
463 }
464
465 IList<UserInfo> allUserInfos = new List<UserInfo>();
466
467 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
468 SqlHelper.CONN_STRING, CommandType.Text, sql))
469 {
470 while (rdr.Read())
471 {
472 UserInfo userInfo = new UserInfo();
473 userInfo.UserId = (string)rdr["UserId"];
474 userInfo.UserName = (string)rdr["UserName"];
475 allUserInfos.Add(userInfo);
476 }
477
478 return allUserInfos;
479 }
480 }
481
482
483 public IList<UserInfo> GetUserInfoByDepartId(int departId)
484 {
485 string sql = "SELECT UserId,UserName,Password,DepartId,Gender,UserStateId FROM UserInfo where departId = " + departId;
486
487 IList<UserInfo> userInfoList = new List<UserInfo>();
488
489 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
490 SqlHelper.CONN_STRING, CommandType.Text, sql))
491 {
492 while (rdr.Read())
493 {
494 UserInfo userInfo = new UserInfo();
495 userInfo.UserId = (string)rdr["UserId"];
496 userInfo.UserName = (string)rdr["UserName"];
497 userInfo.Password = (string)rdr["Password"];
498 userInfo.DepartId = (int)rdr["DepartId"];
499 userInfo.Gender = (int)rdr["Gender"];
500 userInfo.UserStateId = (int)rdr["UserStateId"];
501 userInfoList.Add(userInfo);
502 }
503 }
504
505 return userInfoList;
506
507 }
508
509 /// <summary>
510 /// 根据ID查询员工信息表2
511 /// </summary>
512 /// <param name="userId">员工信息表ID</param>
513 /// <returns>员工信息表对象</returns>
514 public UserInfo GetUserInfoByUserId2(string userId)
515 {
516 SqlParameter para = new SqlParameter("@UserId", SqlDbType.VarChar, 30);
517 para.Value = userId;
518 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
519 SqlHelper.CONN_STRING, CommandType.StoredProcedure, "", para))
520 {
521 if (rdr.Read())
522 {
523 UserInfo userInfo = new UserInfo();
524 userInfo.UserId = (string)rdr["UserId"];
525 userInfo.UserName = (string)rdr["UserName"];
526 userInfo.Password = (string)rdr["Password"];
527 userInfo.DepartId = (int)rdr["DepartId"];
528 userInfo.Gender = (int)rdr["Gender"];
529 userInfo.UserStateId = (int)rdr["UserStateId"];
530
531 userInfo.UserStateName = (string)rdr["UserStateName"];
532 userInfo.DepartName = (string)rdr["DepartName"];
533 userInfo.RoleName = (string)rdr["RoleName"];
534
535 return userInfo;
536 }
537 }
538
539 return null;
540 }
541
542 /// <summary>
543 /// 根据部门编号查询用户
544 /// </summary>
545 /// <param name="departId"></param>
546 /// <returns></returns>
547 public UserInfo GetUserByDepartId(int DepartId)
548 {
549 string sql = "SELECT UserId,UserName,Password,DepartId,Gender,UserStateId " +
550 "FROM UserInfo where departId = @DepartId";
551
552 SqlParameter pa = new SqlParameter("@DepartId", SqlDbType.Int, 4);
553 pa.Value = DepartId;
554
555 using (SqlDataReader rdr = SqlHelper.ExecuteReader(
556 SqlHelper.CONN_STRING, CommandType.Text, sql, pa))
557 {
558 while (rdr.Read())
559 {
560 UserInfo userInfo = new UserInfo();
561 userInfo.UserId = (string)rdr["UserId"];
562 userInfo.UserName = (string)rdr["UserName"];
563 userInfo.Password = (string)rdr["Password"];
564 userInfo.DepartId = (int)rdr["DepartId"];
565 userInfo.Gender = (int)rdr["Gender"];
566 userInfo.UserStateId = (int)rdr["UserStateId"];
567 return userInfo;
568 }
569 }
570
571 return null;
572
573 }
574 }
575 }
