自己封装的SQLHelper
1 /*
2 * 创建人：Devil
3 * 创建时间：2010-11-1 14：41
4 * 说明：数据库助手类
5 */
6 using System;
7 using System.Collections.Generic;
8 using System.Linq;
9 using System.Text;
10 using System.Data;
11 using System.Data.SqlClient;
12 using System.Configuration;
13 using System.Windows.Forms;
14
15 namespace DataAccessLayer
16 {
17 public class SQLHelper
18 {
19 private SqlConnection conn = null;
20 private SqlCommand cmd = null;
21 private SqlDataReader sdr = null;
22 private SqlDataAdapter sda = null;
23
24 public SQLHelper()
25 {
26 string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
27 conn = new SqlConnection(connStr);
28 }
29
30 /// <summary>
31 /// 获得数据库的链接
32 /// </summary>
33 /// <returns></returns>
34 public SqlConnection GetConn()
35 {
36 if (conn.State == ConnectionState.Closed)
37 {
38 conn.Open();
39 }
40 return conn;
41 }
42
43 /// <summary>
44 /// 执行不带参数的增删改SQL语句或存储过程
45 /// </summary>
46 /// <param name="cmdText">增删改SQL语句或存储过程</param>
47 /// <param name="ct">命令类型</param>
48 /// <returns></returns>
49 public int ExecuteNonQuery(string cmdText, CommandType ct)
50 {
51 int res;
52 using (cmd = new SqlCommand(cmdText, GetConn()))
53 {
54 cmd.CommandType = ct;
55 res = cmd.ExecuteNonQuery();
56 }
57 return res;
58 }
59
60 /// <summary>
61 /// 执行带参数的增删改SQL语句或存储过程
62 /// </summary>
63 /// <param name="cmdText">增删改SQL语句或存储过程</param>
64 /// <param name="ct">命令类型</param>
65 /// <returns></returns>
66 public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
67 {
68 int res;
69 using (cmd = new SqlCommand(cmdText, GetConn()))
70 {
71 cmd.CommandType = ct;
72 cmd.Parameters.AddRange(paras);
73 res = cmd.ExecuteNonQuery();
74 }
75 return res;
76 }
77
78 /// <summary>
79 /// 执行不带参数的查询SQL语句或存储过程
80 /// </summary>
81 /// <param name="cmdText">查询SQL语句或存储过程</param>
82 /// <param name="ct">命令类型</param>
83 /// <returns></returns>
84 public DataTable ExecuteQuery(string cmdText, CommandType ct)
85 {
86 DataTable dt = new DataTable();
87 cmd = new SqlCommand(cmdText, GetConn());
88 cmd.CommandType = ct;
89 using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
90 {
91 dt.Load(sdr);
92 }
93 return dt;
94 }
95
96 /// <summary>
97 /// 执行带参数的查询SQL语句或存储过程
98 /// </summary>
99 /// <param name="cmdText">查询SQL语句或存储过程</param>
100 /// <param name="paras">参数集合</param>
101 /// <param name="ct">命令类型</param>
102 /// <returns></returns>
103 public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
104 {
105 DataTable dt = new DataTable();
106 cmd = new SqlCommand(cmdText, GetConn());
107 cmd.CommandType = ct;
108 cmd.Parameters.AddRange(paras);
109 using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
110 {
111 dt.Load(sdr);
112 }
113 return dt;
114 }
115
116 /// <summary>
117 /// 从数据库获取数据存放在dataset中
118 /// </summary>
119 /// <param name="sql"></param>
120 /// <returns></returns>
121 public DataSet GetDS(string sql)
122 {
123 DataSet ds = new DataSet();
124 sda = new SqlDataAdapter(sql, GetConn());
125 try
126 {
127 sda.Fill(ds);
128 }
129 catch (Exception ex)
130 {
131 MessageBox.Show(ex.Message);
132 return null;
133 }
134 return ds;
135 }
136
137 ///// <summary>
138 ///// 执行不带参数的SQL语句，结果存于SqlDataAdapter
139 ///// </summary>
140 ///// <param name="sql"></param>
141 ///// <returns></returns>
142 //public SqlDataAdapter ExecuteAdapter(string sql)
143 //{
144 // sda = new SqlDataAdapter(sql, GetConn());
145 // return sda;
146 //}
147 }
148 }
149
posted on 2011-02-11 10:58 Devil.Waltz 阅读(1413) 评论(3) 编辑 收藏 举报
