学着写的一个DBHelper类
学习了下工厂模式，简单三层，还有单类这三种结构，写了一个自己的数据库处理类，呵呵，想不到叫什么名字也就姑且叫DBHelper吧，当然，肯定不能跟李天平老师那个类相比啦，我写的这个比较简单啦，贴上代码，希望大家帮我看看哪里不到位的，谢谢啦！
DBHelper
1 public class DBHelper
2 {
3 public static string connectionstring = ConfigurationManager.AppSettings["ConnectionString"].ToString();
4 public enum Type { StrSql, StoredProcuder }
5 #region 构造SqlCommand
6 /// <summary>
7 /// 构带参数的SqlCommand
8 /// </summary>
9 /// <param name="conn"></param>
10 /// <param name="type"></param>
11 /// <param name="paras"></param>
12 /// <returns></returns>
13 public static SqlCommand PrepareSqlCommand(SqlConnection conn, String commandtext, Type type, SqlParameter[] paras)
14 {
15 SqlCommand cmd = new SqlCommand();
16 if (paras == null)
17 {
18 throw new Exception("参数不为空");
19 }
20 else
21 {
22 switch (type)
23 {
24 case Type.StrSql:
25 cmd.Connection = conn;
26 cmd.Parameters.AddRange(paras);
27 cmd.CommandType = CommandType.Text;
28 cmd.CommandText = commandtext;
29 break;
30 case Type.StoredProcuder:
31 cmd.Connection = conn;
32 cmd.Parameters.AddRange(paras);
33 cmd.CommandType = CommandType.StoredProcedure;
34 cmd.CommandText = commandtext;
35 break;
36 }
37 }
38 return cmd;
39 }
40 /// <summary>
41 /// 构造不带参数的SqlCommand
42 /// </summary>
43 /// <param name="conn"></param>
44 /// <param name="type"></param>
45 /// <returns></returns>
46 public static SqlCommand PrepareSqlCommand(SqlConnection conn, String commandtext, Type type)
47 {
48 SqlCommand cmd = new SqlCommand();
49 switch (type)
50 {
51 case Type.StrSql:
52 cmd.Connection = conn;
53 cmd.CommandType = CommandType.Text;
54 cmd.CommandText = commandtext;
55 break;
56 case Type.StoredProcuder:
57 cmd.Connection = conn;
58 cmd.CommandType = CommandType.StoredProcedure;
59 cmd.CommandText = commandtext;
60 break;
61 }
62 return cmd;
63 }
64 #endregion
65 #region 执行sql语句或者存储过程
66 /// <summary>
67 /// 执行一条sql语句
68 /// </summary>
69 /// <param name="sql"></param>
70 /// <returns></returns>
71 public static int ExcuteSql(string sql)
72 {
73 using (SqlConnection conn = new SqlConnection(connectionstring))
74 {
75 if (conn.State == ConnectionState.Closed)
76 {
77 conn.Open();
78 }
79 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, Type.StrSql))
80 {
81 int res = cmd.ExecuteNonQuery();
82 return res;
83 }
84 }
85 }
86 /// <summary>
87 /// 执行带参数的sql语句
88 /// </summary>
89 /// <param name="sql"></param>
90 /// <param name="para"></param>
91 /// <returns></returns>
92 public static int ExcuteSql(string sql, SqlParameter[] paras)
93 {
94 using (SqlConnection conn = new SqlConnection(connectionstring))
95 {
96 if (conn.State == ConnectionState.Closed)
97 {
98 conn.Open();
99 }
100 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, Type.StrSql, paras))
101 {
102 int res = cmd.ExecuteNonQuery();
103 return res;
104 }
105 }
106 }
107 /// <summary>
108 /// 执行不带参数的存储过程
109 /// </summary>
110 /// <param name="procname"></param>
111 /// <returns></returns>
112 public static int ExcuteStoredProc(string procname)
113 {
114 using (SqlConnection conn = new SqlConnection(connectionstring))
115 {
116 if (conn.State == ConnectionState.Closed)
117 {
118 conn.Open();
119 }
120 using (SqlCommand cmd = PrepareSqlCommand(conn, procname, Type.StoredProcuder))
121 {
122 int res = cmd.ExecuteNonQuery();
123 return res;
124 }
125 }
126
127 }
128 public static int ExcuteStoredProc(string procname, SqlParameter[] paras)
129 {
130 using (SqlConnection conn = new SqlConnection(connectionstring))
131 {
132 if (conn.State == ConnectionState.Closed)
133 {
134 conn.Open();
135 }
136 using (SqlCommand cmd = PrepareSqlCommand(conn, procname, Type.StoredProcuder))
137 {
138 int res = cmd.ExecuteNonQuery();
139 return res;
140 }
141 }
142
143 }
144 #endregion
145 #region 执行查询，得到DataSet
146 public static DataSet GetDataList(string sql, Type type)
147 {
148 DataSet ds = new DataSet();
149 using (SqlConnection conn = new SqlConnection(connectionstring))
150 {
151 if (conn.State == ConnectionState.Closed)
152 {
153 conn.Open();
154 }
155 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, type))
156 {
157 using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
158 {
159 sda.Fill(ds);
160 return ds;
161 }
162 }
163 }
164 }
165 public static DataSet GetDataList(string sql, Type type, SqlParameter[] paras)
166 {
167 DataSet ds = new DataSet();
168 using (SqlConnection conn = new SqlConnection(connectionstring))
169 {
170 if (conn.State == ConnectionState.Closed)
171 {
172 conn.Open();
173 }
174 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, type, paras))
175 {
176 using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
177 {
178 sda.Fill(ds);
179 return ds;
180 }
181 }
182 }
183 }
184 #endregion
185 #region 得到首行首列
186 public static object GetSingle(string sql, Type type)
187 {
188 object obj;
189 using (SqlConnection conn = new SqlConnection(connectionstring))
190 {
191 if (conn.State == ConnectionState.Closed)
192 {
193 conn.Open();
194 }
195 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, type))
196 {
197 obj = cmd.ExecuteScalar();
198 if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value))
199 {
200 return null;
201 }
202 else return obj;
203 }
204 }
205 }
206 public static object GetSingle(string sql, Type type, SqlParameter[] paras)
207 {
208 object obj;
209 using (SqlConnection conn = new SqlConnection(connectionstring))
210 {
211 if (conn.State == ConnectionState.Closed)
212 {
213 conn.Open();
214 }
215 using (SqlCommand cmd = PrepareSqlCommand(conn, sql, type,paras))
216 {
217 obj = cmd.ExecuteScalar();
218 if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value))
219 {
220 return null;
221 }
222 else return obj;
223 }
224 }
225 }
226 #endregion
227 #region 判断是否存在
228 public static bool IsExit(string sql,Type type)
229 {
230
231 return Convert.ToInt32(GetSingle(sql,type))>0?true:false;//Convert.ToInt32(null)返回0而int.parse(null)引发异常
232 }
233 public static bool IsExit(string sql, Type type, SqlParameter[] paras)
234 {
235 return Convert.ToInt32(GetSingle(sql, type,paras)) >0 ? true :false;
236 }
237 #endregion
238 }
浙公网安备 33010602011771号