[原创]数据访问类
2007-10-31 15:43 水随风 阅读(295) 评论(0) 编辑 收藏 举报 1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Collections;
7 using System.Configuration;
8
9 namespace Yzx.SqlHelper
10 {
11 public class DBUltility : IDisposable
12 {
13
14 // private SqlConnection globalConnection;
15 private string connectionString;
16 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
17 public DBUltility()
18 {
19 connectionString =ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
20 }
21 public DBUltility(string newConnectionString)
22 {
23 connectionString = newConnectionString;
24 //globalConnection = new SqlConnection (newConnectionString);
25
26 }
27 protected SqlCommand GetSqlCommand(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
28 {
29 SqlCommand comd = new SqlCommand(cmdText, conn);
30 comd.CommandType = cmdType;
31 if (cmdParameters != null)
32 {
33 foreach (SqlParameter param in cmdParameters)
34 {
35 comd.Parameters.Add(param);
36 }
37 }
38 return comd;
39 }
40 protected SqlCommand GetSqlCommandByreturn(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
41 {
42 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
43 comd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
44 return comd;
45 }
46 //protected void ConnectionOpen()
47 //{
48 // if(conn.State != ConnectionState.Open)
49 // {
50 // conn.Open();
51 // }
52 //}
53 //protected void ConnectionClose()
54 //{
55 // if(conn.State != ConnectionState.Closed)
56 // {
57 // conn.Close();
58 // }
59 //}
60 /// <summary>
61 /// 执行一条语句，返回一个DataSet结果集
62 /// </summary>
63 /// <param name="cmdText">查询语句或者是存储过程名</param>
64 /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
65 /// <param name="cmdParameters">附代的参数列表</param>
66 /// <returns>返回一个DataSet结果集</returns>
67 public DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
68 {
69 using (SqlConnection conn = new SqlConnection(connectionString))
70 {
71 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
72 DataSet ds = new DataSet();
73 conn.Open();
74 SqlDataAdapter da = new SqlDataAdapter(comd);
75 da.Fill(ds);
76 conn.Close();
77 comd.Parameters.Clear();
78 return ds;
79 }
80
81 }
82 /// <summary>
83 /// 执行一条语句，返回一个DataSet结果集
84 /// </summary>
85 /// <param name="cmdText">查询语句或者是存储过程名</param>
86 /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
87 /// <param name="cmdParameters">附代的参数列表</param>
88 /// <returns>返回一个DataTable结果集</returns>
89 public DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
90 {
91 using (SqlConnection conn = new SqlConnection(connectionString))
92 {
93 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
94 DataTable dt = new DataTable();
95 conn.Open();
96 SqlDataAdapter da = new SqlDataAdapter(comd);
97 da.Fill(dt);
98 conn.Close();
99 comd.Parameters.Clear();
100 return dt;
101 }
102 }
103 /// <summary>
104 /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
105 /// </summary>
106 /// <param name="cmdText">查询语句</param>
107 /// <param name="cmdType">查询的方式</param>
108 /// <param name="cmdParameters">可选参数列表</param>
109 /// <returns>SqlDataReader(注意手工关闭SqlDataReader)</returns>
110 public SqlDataReader ExecuteDataReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
111 {
112 SqlConnection conn = new SqlConnection(connectionString);
113 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
114 try
115 {
116
117 conn.Open();
118 SqlDataReader dtr = comd.ExecuteReader(CommandBehavior.CloseConnection);
119 comd.Parameters.Clear();
120 return dtr;
121 }
122 catch
123 {
124 conn.Close();
125 throw;
126 }
127
128 }
129 /// <summary>
130 /// 执行SQL语句，返回影响的记录数
131 /// </summary>
132 /// <param name="cmdText">查询语句</param>
133 /// <param name="cmdType">查询的方式</param>
134 /// <param name="cmdParameters">可选参数列表</param>
135 /// <param name="ifreturn">是否为返回参数值（一般为存储过程）</param>
136 /// <returns>返回影响的记录数一般为整数</returns>
137 public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters, bool ifreturn)
138 {
139 using (SqlConnection conn = new SqlConnection(connectionString))
140 {
141 SqlCommand comd;
142 int result;
143
144 if (ifreturn)
145 {
146 comd = GetSqlCommandByreturn(conn, cmdText, cmdType, cmdParameters);
147 conn.Open();
148 comd.ExecuteScalar();
149 result = (int)comd.Parameters["ReturnValue"].Value;
150
151 }
152 else
153 {
154 comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
155 conn.Open();
156 result = comd.ExecuteNonQuery();
157 }
158 comd.Parameters.Clear();
159 conn.Close();
160 return result;
161
162 }
163 }
164 /// <summary>
165 /// 执行一条计算查询结果语句，返回查询结果（object）
166 /// </summary>
167 /// <param name="cmdText">查询语句</param>
168 /// <param name="cmdType">查询的方式</param>
169 /// <param name="cmdParameters">可选参数列表</param>
170 /// <returns>查询结果（object）</returns>
171 public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
172 {
173 using (SqlConnection conn = new SqlConnection(connectionString))
174 {
175 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
176 conn.Open();
177 object result = comd.ExecuteScalar();
178 comd.Parameters.Clear();
179 conn.Close();
180 return result;
181 }
182 }
183 /// <summary>
184 /// 缓存SQL 参数列表
185 /// </summary>
186 /// <param name="cacheKey">缓存对象名</param>
187 /// <param name="cheparams">要缓存的参数列表 SqlParameter [] 类型</param>
188 public static void CacheParameter(string cacheKey, SqlParameter[] cheparams)
189 {
190 parmCache[cacheKey] = cheparams;
191 }
192 /// <summary>
193 /// 获取在HashTable之是的缓存参数列表
194 /// </summary>
195 /// <param name="cacheKey">要获取的缓存对象名</param>
196 /// <returns>根据缓存对象名，取得缓存的参数列表</returns>
197 public static SqlParameter[] GetCachedParameters(string cacheKey)
198 {
199 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
200
201 if (cachedParms == null)
202 return null;
203
204 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
205
206 for (int i = 0, j = cachedParms.Length; i < j; i++)
207 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
208
209 return clonedParms;
210 }
211 #region IDisposable 成员
212
213 public void Dispose()
214 {
215 //throw new Exception ("The method or operation is not implemented.");
216 //Dispose (true);
217 //GC.SuppressFinalize (this);
218 //GC.g
219 //globalConnection.Dispose ();
220 connectionString = null;
221 GC.ReRegisterForFinalize(this);
222 }
223 //public void Dispose(bool iftrue)
224 //{
225 // if (iftrue)
226 // {
227
228 // }
229 //}
230 ~DBUltility()
231 {
232 //globalConnection.Dispose ();
233 connectionString = null;
234
235 }
236 #endregion
237 }
238 }
239
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Collections;
7 using System.Configuration;
8
9 namespace Yzx.SqlHelper
10 {
11 public class DBUltility : IDisposable
12 {
13
14 // private SqlConnection globalConnection;
15 private string connectionString;
16 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
17 public DBUltility()
18 {
19 connectionString =ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
20 }
21 public DBUltility(string newConnectionString)
22 {
23 connectionString = newConnectionString;
24 //globalConnection = new SqlConnection (newConnectionString);
25
26 }
27 protected SqlCommand GetSqlCommand(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
28 {
29 SqlCommand comd = new SqlCommand(cmdText, conn);
30 comd.CommandType = cmdType;
31 if (cmdParameters != null)
32 {
33 foreach (SqlParameter param in cmdParameters)
34 {
35 comd.Parameters.Add(param);
36 }
37 }
38 return comd;
39 }
40 protected SqlCommand GetSqlCommandByreturn(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
41 {
42 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
43 comd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
44 return comd;
45 }
46 //protected void ConnectionOpen()
47 //{
48 // if(conn.State != ConnectionState.Open)
49 // {
50 // conn.Open();
51 // }
52 //}
53 //protected void ConnectionClose()
54 //{
55 // if(conn.State != ConnectionState.Closed)
56 // {
57 // conn.Close();
58 // }
59 //}
60 /// <summary>
61 /// 执行一条语句，返回一个DataSet结果集
62 /// </summary>
63 /// <param name="cmdText">查询语句或者是存储过程名</param>
64 /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
65 /// <param name="cmdParameters">附代的参数列表</param>
66 /// <returns>返回一个DataSet结果集</returns>
67 public DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
68 {
69 using (SqlConnection conn = new SqlConnection(connectionString))
70 {
71 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
72 DataSet ds = new DataSet();
73 conn.Open();
74 SqlDataAdapter da = new SqlDataAdapter(comd);
75 da.Fill(ds);
76 conn.Close();
77 comd.Parameters.Clear();
78 return ds;
79 }
80
81 }
82 /// <summary>
83 /// 执行一条语句，返回一个DataSet结果集
84 /// </summary>
85 /// <param name="cmdText">查询语句或者是存储过程名</param>
86 /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
87 /// <param name="cmdParameters">附代的参数列表</param>
88 /// <returns>返回一个DataTable结果集</returns>
89 public DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
90 {
91 using (SqlConnection conn = new SqlConnection(connectionString))
92 {
93 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
94 DataTable dt = new DataTable();
95 conn.Open();
96 SqlDataAdapter da = new SqlDataAdapter(comd);
97 da.Fill(dt);
98 conn.Close();
99 comd.Parameters.Clear();
100 return dt;
101 }
102 }
103 /// <summary>
104 /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
105 /// </summary>
106 /// <param name="cmdText">查询语句</param>
107 /// <param name="cmdType">查询的方式</param>
108 /// <param name="cmdParameters">可选参数列表</param>
109 /// <returns>SqlDataReader(注意手工关闭SqlDataReader)</returns>
110 public SqlDataReader ExecuteDataReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
111 {
112 SqlConnection conn = new SqlConnection(connectionString);
113 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
114 try
115 {
116
117 conn.Open();
118 SqlDataReader dtr = comd.ExecuteReader(CommandBehavior.CloseConnection);
119 comd.Parameters.Clear();
120 return dtr;
121 }
122 catch
123 {
124 conn.Close();
125 throw;
126 }
127
128 }
129 /// <summary>
130 /// 执行SQL语句，返回影响的记录数
131 /// </summary>
132 /// <param name="cmdText">查询语句</param>
133 /// <param name="cmdType">查询的方式</param>
134 /// <param name="cmdParameters">可选参数列表</param>
135 /// <param name="ifreturn">是否为返回参数值（一般为存储过程）</param>
136 /// <returns>返回影响的记录数一般为整数</returns>
137 public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters, bool ifreturn)
138 {
139 using (SqlConnection conn = new SqlConnection(connectionString))
140 {
141 SqlCommand comd;
142 int result;
143
144 if (ifreturn)
145 {
146 comd = GetSqlCommandByreturn(conn, cmdText, cmdType, cmdParameters);
147 conn.Open();
148 comd.ExecuteScalar();
149 result = (int)comd.Parameters["ReturnValue"].Value;
150
151 }
152 else
153 {
154 comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
155 conn.Open();
156 result = comd.ExecuteNonQuery();
157 }
158 comd.Parameters.Clear();
159 conn.Close();
160 return result;
161
162 }
163 }
164 /// <summary>
165 /// 执行一条计算查询结果语句，返回查询结果（object）
166 /// </summary>
167 /// <param name="cmdText">查询语句</param>
168 /// <param name="cmdType">查询的方式</param>
169 /// <param name="cmdParameters">可选参数列表</param>
170 /// <returns>查询结果（object）</returns>
171 public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
172 {
173 using (SqlConnection conn = new SqlConnection(connectionString))
174 {
175 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
176 conn.Open();
177 object result = comd.ExecuteScalar();
178 comd.Parameters.Clear();
179 conn.Close();
180 return result;
181 }
182 }
183 /// <summary>
184 /// 缓存SQL 参数列表
185 /// </summary>
186 /// <param name="cacheKey">缓存对象名</param>
187 /// <param name="cheparams">要缓存的参数列表 SqlParameter [] 类型</param>
188 public static void CacheParameter(string cacheKey, SqlParameter[] cheparams)
189 {
190 parmCache[cacheKey] = cheparams;
191 }
192 /// <summary>
193 /// 获取在HashTable之是的缓存参数列表
194 /// </summary>
195 /// <param name="cacheKey">要获取的缓存对象名</param>
196 /// <returns>根据缓存对象名，取得缓存的参数列表</returns>
197 public static SqlParameter[] GetCachedParameters(string cacheKey)
198 {
199 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
200
201 if (cachedParms == null)
202 return null;
203
204 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
205
206 for (int i = 0, j = cachedParms.Length; i < j; i++)
207 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
208
209 return clonedParms;
210 }
211 #region IDisposable 成员
212
213 public void Dispose()
214 {
215 //throw new Exception ("The method or operation is not implemented.");
216 //Dispose (true);
217 //GC.SuppressFinalize (this);
218 //GC.g
219 //globalConnection.Dispose ();
220 connectionString = null;
221 GC.ReRegisterForFinalize(this);
222 }
223 //public void Dispose(bool iftrue)
224 //{
225 // if (iftrue)
226 // {
227
228 // }
229 //}
230 ~DBUltility()
231 {
232 //globalConnection.Dispose ();
233 connectionString = null;
234
235 }
236 #endregion
237 }
238 }
239
