数据库访问工具类

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Threading.Tasks;
6. using System.Configuration;
7. using System.Data;
8. using System.Data.SqlClient;
9. using System.Reflection;
11. namespace StudentDLL
12. {
13. public class Datebase
14. {
15. private static string _sqlserver = ConfigurationManager.ConnectionStrings["sqlserver"].ToString();
16. private SqlConnection conn;
17. /// <summary>
18. /// 初始化conn并且打开sqlconnection
19. /// </summary>
20. public SqlConnection Conn
21. {
22. get
23. {
24. if (conn == null)
25. {
26. conn = new SqlConnection(_sqlserver);
27. }
28. if (conn.State == ConnectionState.Closed)
29. {
30. conn.Open();
31. }
32. else if (conn.State == ConnectionState.Broken)
33. {
34. conn.Close();
35. conn.Open();
36. }
37. return conn;
38. }
39. }
41. #region 关闭数据库连接
42. public void CloseSqlconnection()
43. {
44. if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
45. {
46. conn.Close();
47. }
48. }
49. #endregion
50. #region 查询
51. /// <summary>
52. /// 使用T-SQL语句查询
53. /// </summary>
54. /// <param name="sql"></param>
55. /// <returns></returns>
56. public DataSet executeReader(string sql)
57. {
58. DataSet ds = new DataSet();
59. using(SqlDataAdapter da=new SqlDataAdapter(sql,conn)){da.Fill(ds);}
60. CloseSqlconnection();
61. return ds;
62. }
63. /// <summary>
64. /// T-Sql参数化查询
65. /// </summary>
66. /// <param name="sql"></param>
67. /// <param name="par"></param>
68. /// <returns></returns>
69. public DataSet executeReader(string sql, params SqlParameter[] par)
70. {
71. DataSet ds = new DataSet();
72. using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
73. {
74. da.SelectCommand.Parameters.AddRange(par);
75. da.Fill(ds);
76. }
77. CloseSqlconnection();
78. return ds;
79. }
81. /// <summary>
82. /// 不带参数存储过程查询
83. /// </summary>
84. /// <param name="procName">存储过程名称</param>
85. /// <returns>DataSet表</returns>
86. public DataSet procExecuteReader(string procName)
87. {
88. DataSet ds = new DataSet();
89. using (SqlDataAdapter dar = new SqlDataAdapter(procName, Conn))
90. {
91. dar.SelectCommand.CommandType = CommandType.StoredProcedure;
92. dar.Fill(ds);
93. }
94. CloseSqlconnection();
95. return ds;
96. }
97. /// <summary>
98. /// 参数化存储过程查询
99. /// </summary>
100. /// <param name="procName">存储过程名称</param>
101. /// <param name="par"></param>
102. /// <returns>DataSet</returns>
103. public DataSet procExecuteReader(string procName,params SqlParameter [] par)
104. {
105. DataSet ds = new DataSet();
106. using (SqlDataAdapter dar = new SqlDataAdapter(procName,Conn))
107. {
108. dar.SelectCommand.CommandType = CommandType.StoredProcedure;
109. dar.SelectCommand.Parameters.AddRange(par);
110. dar.Fill(ds);
111. }
112. CloseSqlconnection();
113. return ds;
114. }
115. #endregion
117. #region 增删改
118. /// <summary>
119. /// 使用sql语句增删改
120. /// </summary>
121. /// <param name="sql"></param>
122. /// <returns></returns>
123. public int executeNoQuery(string sql)
124. {
125. int dr = 0;
126. using (SqlCommand comm = new SqlCommand(sql, Conn))
127. {
128. dr = comm.ExecuteNonQuery();
129. }
130. CloseSqlconnection();
131. return dr;
132. }
134. /// <summary>
135. /// 使用参数化增删改
136. /// </summary>
137. /// <param name="sql"></param>
138. /// <param name="obj"></param>
139. /// <returns></returns>
140. public int executeNoQuery(string sql,params SqlParameter [] par)
141. {
142. int dr = 0;
143. using (SqlCommand comm = new SqlCommand(sql, Conn))
144. {
145. comm.Parameters.AddRange(par);
146. dr = comm.ExecuteNonQuery();
147. }
148. CloseSqlconnection();
149. return dr;
150. }
152. /// <summary>
153. /// 使用存储过程增删改
154. /// </summary>
155. /// <returns></returns>
156. public int procExecuteNoQuery(string procName,params SqlParameter[] par)
157. {
158. int result = 0;
159. using (SqlCommand comm = new SqlCommand(procName,Conn))
160. {
161. comm.CommandType = CommandType.StoredProcedure;
162. comm.Parameters.AddRange(par);
163. result = comm.ExecuteNonQuery();
164. }
165. CloseSqlconnection();
166. return result;
167. }
168. #endregion
169. }
170. }

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/BookCode/p/ec47195a9d16abd4798d7677ea3d72ea.html