【转】通用数据访问层--SQLServer版

1. using?System;
2. using?System.Data;
3. using?System.Configuration;
4. using?System.Web;
5. using?System.Web.Security;
6. using?System.Web.UI;
7. using?System.Web.UI.WebControls;
8. using?System.Web.UI.WebControls.WebParts;
9. using?System.Web.UI.HtmlControls;
10. using?System.Data.SqlClient;
11. 
    
12. 
    
13. 
    
14. ///?<summary>
15. ///?DBmanager的摘要说明
16. ///?</summary>
17. public?class?DBmanager
18. {
19. ????private?static?readonly?string?sqlCon?=?ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
20. 
    
21. ????protected?static?SqlConnection?GetSqlconnection()//返回SqlConnection对象；
22. ????{
23. ????????SqlConnection?sqlcon?=?new?SqlConnection(sqlCon);
24. ????????return?sqlcon;
25. ????}
26. ????//
27. ????//?TODO:
28. ????///?<summary>
29. ????///?检测记录是否存在
30. ????///?</summary>
31. ????///?<param?name="CheckedField">被检测的字段</param>
32. ????///?<param?name="CheckedContent">字段的内容</param>
33. ????///?<param?name="CheckedTable">被检测的字段所在的表</param>
34. ????///?<returns>True:存在,False:不存在</returns>
35. ????public?static?bool?IsExistRecord(string?CheckedField,?string?CheckedContent,?string?CheckedTable)
36. ????{
37. ????????string?IsExistConditon?=?"where?"?+?CheckedField?+?"="?+?CheckedContent?+?"";
38. ????????string?IsExistSQL?=?"select?"?+?CheckedField?+?"?from?"?+?CheckedTable?+?"?"?+?IsExistConditon?+?"";
39. ????????SqlDataReader?myReader?=?Static_getDataReadBySql(IsExistSQL);
40. ????????if?(myReader.Read())
41. ????????{
42. ????????????if?(myReader?!=?null)?{?myReader.Close();?}
43. ????????????return?true;//存在
44. ????????}
45. ????????else
46. ????????{
47. ????????????if?(myReader?!=?null)?{?myReader.Close();?}
48. ????????????return?false;//不存在
49. ????????}
50. 
    
51. ????}
52. 
    
53. ????///?<summary>
54. ????///?根据SQL语句返回一个数据集对象
55. ????///?</summary>
56. ????///?<param?name="Sqlcmd">要生成数据集对象的SqlCommand</param>
57. ????///?<returns>返回值：DataSet</returns>
58. ????public?static?DataSet?Static_getDataSet(SqlCommand?Sqlcmd)
59. ????{
60. ????????//连接对象
61. ????????SqlConnection?sqlCon?=?GetSqlconnection();
62. ????????//sql命令
63. ????????Sqlcmd.Connection?=?sqlCon;
64. ????????//适配器
65. ????????SqlDataAdapter?sqlData?=?new?SqlDataAdapter();
66. ????????sqlData.SelectCommand?=?Sqlcmd;
67. ????????DataSet?ds?=?new?DataSet();
68. ????????sqlData.Fill(ds);
69. ????????return?ds;
70. ????}
71. ????///?<summary>
72. ????///?根据SQL语句返回一个数据集对象
73. ????///?</summary>
74. ????///?<param?name="sqlstr">要生成数据集对象的string</param>
75. ????///?<returns>返回值：DataSet</returns>
76. ????public?static?DataSet?Static_getDataSet(string?sqlstr)
77. ????{
78. ????????//连接对象
79. ????????SqlConnection?sqlCon?=?GetSqlconnection();
80. ????????SqlCommand?cmd?=?new?SqlCommand();
81. ????????cmd.CommandText?=?sqlstr;
82. ????????//sql命令
83. ????????cmd.Connection?=?sqlCon;
84. ????????//适配器
85. ????????SqlDataAdapter?sqlData?=?new?SqlDataAdapter();
86. ????????sqlData.SelectCommand?=?cmd;
87. ????????DataSet?ds?=?new?DataSet();
88. ????????sqlData.Fill(ds);
89. ????????return?ds;
90. ????}
91. ????///?<summary>
92. ????///?根据SQL语句返回一个数据集对象
93. ????///?</summary>
94. ????///?<param?name="sqlstr">要生成数据集对象的string</param>
95. ????///?<param?name="tbname">指定返回数据集的表名称</param>
96. ????///?<returns>返回值：DataSet</returns>
97. ????public?static?DataSet?Static_getDataSet(string?sqlstr,?string?tbname)
98. ????{
99. ????????//连接对象
100. ????????SqlConnection?sqlCon?=?GetSqlconnection();
101. ????????SqlCommand?cmd?=?new?SqlCommand();
102. ????????cmd.CommandText?=?sqlstr;
103. ????????//sql命令
104. ????????cmd.Connection?=?sqlCon;
105. ????????//适配器
106. ????????SqlDataAdapter?sqlData?=?new?SqlDataAdapter();
107. ????????sqlData.SelectCommand?=?cmd;
108. ????????DataSet?ds?=?new?DataSet();
109. ????????sqlData.Fill(ds,?tbname);
110. ????????return?ds;
111. ????}
112. 
     
113. ????///?<summary>
114. ????///?根据SQL语句返回一个数据视图对象
115. ????///?</summary>
116. ????///?<param?name="Sqlcmd">要生成数据集对象的SqlCommand</param>
117. ????///?<returns>返回值：DataView</returns>
118. ????public?static?DataView?Static_getDataView(SqlCommand?Sqlcmd)
119. ????{
120. ????????//连接对象
121. ????????SqlConnection?sqlCon?=?GetSqlconnection();
122. ????????//sql命令
123. ????????Sqlcmd.Connection?=?sqlCon;
124. ????????//适配器
125. ????????SqlDataAdapter?sqlData?=?new?SqlDataAdapter();
126. ????????sqlData.SelectCommand?=?Sqlcmd;
127. ????????DataSet?ds?=?new?DataSet();
128. ????????sqlData.Fill(ds);
129. ????????return?ds.Tables[0].DefaultView;
130. ????}
131. 
     
132. ????///?<summary>
133. ????///?根据SqlCommand对象的实例返回SqlDataAdapter对象
134. ????///?</summary>
135. ????///?<param?name="Sqlcmd">SqlCommand对象</param>
136. ????///?<returns>SqlDataAdapter?SqlDataAdapter对象的实例</returns>
137. ????public?static?SqlDataAdapter?Static_getDataAdapter(SqlCommand?Sqlcmd)
138. ????{
139. ????????//连接对象
140. ????????SqlConnection?sqlCon?=?GetSqlconnection();
141. ????????//sql命令
142. ????????Sqlcmd.Connection?=?sqlCon;
143. ????????//适配器
144. ????????SqlDataAdapter?sqlData?=?new?SqlDataAdapter();
145. ????????sqlData.SelectCommand?=?Sqlcmd;
146. ????????return?sqlData;
147. ????}
148. ????///?<summary>
149. ????///?根据传递过来的参数返回一个数据读对象
150. ????///?</summary>
151. ????///?<param?name="strSql">String?SQL语句</param>
152. ????///?<returns>SqlDataReader?SqlDataReader对象的实例</returns>
153. ????public?static?SqlDataReader?Static_getDataReadBySql(string?strSql)
154. ????{
155. ????????//连接对象
156. ????????SqlConnection?sqlCon?=?GetSqlconnection();
157. ????????//sql命令
158. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
159. ????????sqlCmd.CommandType?=?CommandType.Text;
160. ????????sqlCmd.CommandText?=?strSql;
161. ????????sqlCmd.Connection?=?sqlCon;
162. ????????//
163. ????????sqlCon.Open();
164. ????????SqlDataReader?sqlRead?=?sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
165. ????????//sqlCon.Close();
166. ????????return?sqlRead;
167. ????}
168. 
     
169. ????///?<summary>
170. ????///?根据传递过来的参数返回一个数据读对象
171. ????///?</summary>
172. ????///?<param?name="Conn">SqlConnection　数据库连接对象</param>
173. ????///?<param?name="SQL">String?SQL语句</param>
174. ????///?<returns>SqlDataReader?SqlDataReader对象的实例</returns>
175. ????public?static?SqlDataReader?Static_GetSqlDataReader(SqlConnection?Conn,?String?Str_SQL)
176. ????{
177. ????????//连接对象
178. ????????SqlConnection?sqlCon?=?Conn;
179. ????????//sql命令
180. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
181. ????????sqlCmd.CommandType?=?CommandType.Text;
182. ????????sqlCmd.CommandText?=?Str_SQL;
183. ????????sqlCmd.Connection?=?sqlCon;
184. ????????//
185. ????????sqlCon.Open();
186. ????????SqlDataReader?sqlRead?=?sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
187. ????????//sqlCon.Close();
188. ????????return?sqlRead;
189. ????}
190. 
     
191. ????///?<summary>
192. ????///?执行传递过来的SQL语句
193. ????///?</summary>
194. ????///?<param?name="strSql">string?要执行的SQL语句</param>
195. ????public?static?void?Static_excuteSql(string?strSql)
196. ????{
197. 
     
198. ????????//连接对象
199. ????????SqlConnection?sqlCon?=?GetSqlconnection();
200. ????????//sql命令
201. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
202. ????????sqlCmd.CommandType?=?CommandType.Text;
203. ????????sqlCmd.CommandText?=?strSql;
204. ????????sqlCmd.Connection?=?sqlCon;
205. ????????sqlCon.Open();
206. ????????sqlCmd.ExecuteNonQuery();
207. ????????sqlCon.Close();
208. ????}
209. ????///?<summary>
210. ????///?执行传递过来的SQL语句返回受影响的行数
211. ????///?</summary>
212. ????///?<param?name="strSql">string?要执行的SQL语句</param>
213. ????///?<returns>int?受影响的行数</returns>
214. ????public?static?int?Static_excuteNonQuerySqlCount(string?strSql)
215. ????{
216. 
     
217. ????????//连接对象
218. ????????SqlConnection?sqlCon?=?GetSqlconnection();
219. ????????//sql命令
220. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
221. ????????sqlCmd.CommandType?=?CommandType.Text;
222. ????????sqlCmd.CommandText?=?strSql;
223. ????????sqlCmd.Connection?=?sqlCon;
224. ????????sqlCon.Open();
225. ????????int?i;
226. ????????i?=?sqlCmd.ExecuteNonQuery();
227. ????????sqlCon.Close();
228. ????????return?i;
229. ????}
230. ????///?<summary>
231. ????///?执行传递过来的SQL语句
232. ????///?</summary>
233. ????///?<param?name="strSql">string?要执行的SQL语句</param>
234. ????///?<returns>int?受影响的行数</returns>
235. ????public?static?object?Static_excuteSqlCount(string?strSql)
236. ????{
237. 
     
238. ????????//连接对象
239. ????????SqlConnection?sqlCon?=?GetSqlconnection();
240. ????????//sql命令
241. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
242. ????????sqlCmd.CommandType?=?CommandType.Text;
243. ????????sqlCmd.CommandText?=?strSql;
244. ????????sqlCmd.Connection?=?sqlCon;
245. ????????sqlCon.Open();
246. ????????object?obj;
247. ????????obj?=?sqlCmd.ExecuteScalar();
248. ????????sqlCon.Close();
249. ????????return?obj;
250. ????}
251. ????///?<summary>
252. ????///?执行存储过程返回数据集
253. ????///?</summary>
254. ????///?<param?name="p_strProcName">存储过程名称</param>
255. ????///?<param?name="cmdParams">存储过程参数</param>
256. ????///?<returns>Dataset</returns>??????????
257. ????public?static?DataSet?Static_ExcuteQueryDataByProc(string?p_strProcName,?SqlParameter[]?cmdParams)
258. ????{
259. ????????//连接对象
260. ????????SqlConnection?sqlCon?=?GetSqlconnection();
261. ????????DataSet?dsTemp?=?new?DataSet();
262. ????????SqlDataAdapter?sqlAdapter?=?new?SqlDataAdapter();
263. 
     
264. ????????SqlCommand?sqlCmd?=?new?SqlCommand();
265. ????????sqlCmd.CommandType?=?CommandType.StoredProcedure;
266. ????????sqlCmd.CommandText?=?p_strProcName;
267. ????????sqlCmd.Connection?=?sqlCon;
268. ????????//添加参数
269. ????????for?(int?i?=?0;?i?<?cmdParams.Length;?i++)
270. ????????????sqlCmd.Parameters.Add(cmdParams[i]);
271. 
     
272. ????????sqlAdapter.SelectCommand?=?sqlCmd;
273. ????????sqlAdapter.Fill(dsTemp);
274. ????????return?dsTemp;
275. ????}
276. ????//
277. }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!