DBHelper类.docx

1、DBHelper类1. usingSystem;2. usingSystem.Collections.Generic;3. usingSystem.Linq;4. usingSystem.Text;5. usingSystem.Data.SqlClient;6. usingSystem.Collections;7. usingSystem.Data;8. usingSystem.Configuration;9. usingSystem.Web;10. 11. 12. publicsealedclassSqlHelper13. 14. 15. publicstaticstringconnecti

2、onString=ConfigurationManager.ConnectionStringsdb_JXCconn.ConnectionString;16. 17. publicSqlHelper()18. 19. 20. 21. #region公用方法22. publicstaticintGetMaxID(stringFieldName,stringTableName)23. 24. stringstrsql=selectmax(+FieldName+)+1from+TableName;25. objectobj=SqlHelper.GetSingle(strsql);26. if(obj=

3、null)27. 28. return1;29. 30. else31. 32. returnint.Parse(obj.ToString();33. 34. 35. 36. publicstaticboolExists(stringstrSql)37. 38. objectobj=SqlHelper.GetSingle(strSql);39. intcmdresult;40. if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value)41. 42. cmdresult=0;43. 44. else45. 46. cmd

4、result=int.Parse(obj.ToString();47. 48. if(cmdresult=0)49. 50. returnfalse;51. 52. else53. 54. returntrue;55. 56. 57. 58. publicstaticboolExists(stringstrSql,paramsSqlParametercmdParms)59. 60. objectobj=SqlHelper.GetSingle(strSql,cmdParms);61. intcmdresult;62. if(Object.Equals(obj,null)|(Object.Equa

5、ls(obj,System.DBNull.Value)63. 64. cmdresult=0;65. 66. else67. 68. cmdresult=int.Parse(obj.ToString();69. 70. if(cmdresult=0)71. 72. returnfalse;73. 74. else75. 76. returntrue;77. 78. 79. #endregion80. 81. #region执行简单SQL语句82. /83. /执行SQL语句，返回影响的记录数84. /85. /SQL语句86. /影响的记录数87. publicstaticintExecute

6、Sql(stringSQLString)88. 89. using(SqlConnectionconnection=newSqlConnection(connectionString)90. 91. using(SqlCommandcmd=newSqlCommand(SQLString,connection)92. 93. try94. 95. connection.Open();96. introws=cmd.ExecuteNonQuery();97. returnrows;98. 99. catch(System.Data.SqlClient.SqlExceptionE)100. 101.

7、 connection.Close();102. thrownewException(E.Message);103. 104. 105. 106. 107. 108. /109. /执行SQL语句，返回影响的记录数适用于select语句110. /111. /SQL语句112. /影响的记录数113. publicstaticintExecuteSql2(stringSQLString)114. 115. using(SqlConnectionconnection=newSqlConnection(connectionString)116. 117. using(SqlCommandcmd=n

8、ewSqlCommand(SQLString,connection)118. 119. try120. 121. connection.Open();122. introws=Convert.ToInt32(cmd.ExecuteScalar();123. returnrows;124. 125. catch(System.Data.SqlClient.SqlExceptionE)126. 127. connection.Close();128. thrownewException(E.Message);129. 130. 131. 132. 133. 134. /135. /执行多条SQL语

9、句，实现数据库事务。136. /137. /多条SQL语句138. publicstaticvoidExecuteSqlTran(ArrayListSQLStringList)139. 140. using(SqlConnectionconn=newSqlConnection(connectionString)141. 142. conn.Open();143. SqlCommandcmd=newSqlCommand();144. cmd.Connection=conn;145. SqlTransactiontx=conn.BeginTransaction();146. cmd.Transac

10、tion=tx;147. try148. 149. for(intn=0;n1)153. 154. cmd.CommandText=strsql;155. cmd.ExecuteNonQuery();156. 157. 158. tx.Commit();159. 160. catch(System.Data.SqlClient.SqlExceptionE)161. 162. tx.Rollback();163. thrownewException(E.Message);164. 165. 166. 167. 168. /169. /执行带一个存储过程参数的的SQL语句。170. /171. /

11、SQL语句172. /参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加173. /影响的记录数174. publicstaticintExecuteSql(stringSQLString,stringcontent)175. 176. using(SqlConnectionconnection=newSqlConnection(connectionString)177. 178. SqlCommandcmd=newSqlCommand(SQLString,connection);179. System.Data.SqlClient.SqlParametermyParam

12、eter=newSystem.Data.SqlClient.SqlParameter(content,SqlDbType.VarChar);180. 181. myParameter.Value=content;182. cmd.Parameters.Add(myParameter);183. try184. 185. connection.Open();186. introws=cmd.ExecuteNonQuery();187. returnrows;188. 189. catch(System.Data.SqlClient.SqlExceptionE)190. 191. thrownew

13、Exception(E.Message);192. 193. finally194. 195. cmd.Dispose();196. connection.Close();197. 198. 199. 200. 201. /202. /向数据库里插入图像格式的字段(和上面情况类似的另一种实例)203. /204. /SQL语句205. /图像字节,数据库的字段类型为image的情况206. /影响的记录数207. publicstaticintExecuteSqlInsertImg(stringstrSQL,bytefs)208. 209. using(SqlConnectionconnect

14、ion=newSqlConnection(connectionString)210. 211. SqlCommandcmd=newSqlCommand(strSQL,connection);212. System.Data.SqlClient.SqlParametermyParameter=newSystem.Data.SqlClient.SqlParameter(fs,SqlDbType.Binary);213. myParameter.Value=fs;214. cmd.Parameters.Add(myParameter);215. try216. 217. connection.Ope

15、n();218. introws=cmd.ExecuteNonQuery();219. returnrows;220. 221. catch(System.Data.SqlClient.SqlExceptionE)222. 223. thrownewException(E.Message);224. 225. finally226. 227. cmd.Dispose();228. connection.Close();229. 230. 231. 232. 233. /234. /执行一条计算查询结果语句，返回查询结果（object）。235. /236. /计算查询结果语句237. /查询结

16、果（object）238. publicstaticobjectGetSingle(stringSQLString)239. 240. using(SqlConnectionconnection=newSqlConnection(connectionString)241. 242. using(SqlCommandcmd=newSqlCommand(SQLString,connection)243. 244. try245. 246. connection.Open();247. objectobj=cmd.ExecuteScalar();248. if(Object.Equals(obj,n

17、ull)|(Object.Equals(obj,System.DBNull.Value)249. 250. returnnull;251. 252. else253. 254. returnobj;255. 256. 257. catch(System.Data.SqlClient.SqlExceptione)258. 259. connection.Close();260. thrownewException(e.Message);261. 262. 263. 264. 265. 266. /267. /执行查询语句，返回SqlDataReader268. /269. /查询语句270. /SqlDataReader271. publicstaticSqlDataReaderExecuteReader(stringstrSQL)272. 273. SqlConnection