1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Data.OleDb;
5 using System.Collections;
6 using System.Windows.Forms;
7 using System.Security.Cryptography;
8 using System.Text;
9 /// <summary>
10 /// AcceHelper 的摘要说明
11 /// </summary>
12 public static class AccessHelper
13 {
14 //数据库连接字符串
15 //public static readonly string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
16 /// <summary>
17 /// 打开数据库
18 /// </summary>
19 public static string GetConn()
20 {
21 //conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory+ConfigurationManager.AppSettings["myconn"].ToString() + ";Jet OLEDB:Database PassWord=sa";
22 try
23 {
24 string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
25 string filedata = Application.StartupPath + @"\lz_db.dat";
26 strConnection += @"Data Source=" + filedata;
27 return strConnection;
28 }
29 catch (Exception e)
30 {
31 throw new Exception(e.Message);
32 }
33 }
34 // 用于缓存参数的HASH表
35 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
36 /// <summary>
37 /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
38 /// </summary>
39 /// <param name="connectionString">一个有效的连接字符串</param>
40 /// <param name="commandText">存储过程名称或者sql命令语句</param>
41 /// <param name="commandParameters">执行命令所用参数的集合</param>
42 /// <returns>执行命令所影响的行数</returns>
43 public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
44 {
45 OleDbCommand cmd = new OleDbCommand();
46 using (OleDbConnection conn = new OleDbConnection(connectionString))
47 {
48 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
49 int val = cmd.ExecuteNonQuery();
50 cmd.Parameters.Clear();
51 return val;
52 }
53 }
54 /// <summary>
55 /// 用现有的数据库连接执行一个sql命令（不返回数据集）
56 /// </summary>
57 /// <remarks>
58 ///举例:
59 /// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
60 /// </remarks>
61 /// <param name="conn">一个现有的数据库连接</param>
62 /// <param name="commandText">存储过程名称或者sql命令语句</param>
63 /// <param name="commandParameters">执行命令所用参数的集合</param>
64 /// <returns>执行命令所影响的行数</returns>
65 public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
66 {
67 OleDbCommand cmd = new OleDbCommand();
68 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
69 int val = cmd.ExecuteNonQuery();
70 cmd.Parameters.Clear();
71 return val;
72 }
73 /// <summary>
74 ///使用现有的SQL事务执行一个sql命令（不返回数据集）
75 /// </summary>
76 /// <remarks>
77 ///举例:
78 /// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24));
79 /// </remarks>
80 /// <param name="trans">一个现有的事务</param>
81 /// <param name="commandText">存储过程名称或者sql命令语句</param>
82 /// <param name="commandParameters">执行命令所用参数的集合</param>
83 /// <returns>执行命令所影响的行数</returns>
84 public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
85 {
86 OleDbCommand cmd = new OleDbCommand();
87 PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters);
88 int val = cmd.ExecuteNonQuery();
89 cmd.Parameters.Clear();
90 return val;
91 }
92 /// <summary>
93 /// 用执行的数据库连接执行一个返回数据集的sql命令
94 /// </summary>
95 /// <remarks>
96 /// 举例:
97 /// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
98 /// </remarks>
99 /// <param name="connectionString">一个有效的连接字符串</param>
100 /// <param name="commandText">存储过程名称或者sql命令语句</param>
101 /// <param name="commandParameters">执行命令所用参数的集合</param>
102 /// <returns>包含结果的读取器</returns>
103 public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
104 {
105 //创建一个SqlCommand对象
106 OleDbCommand cmd = new OleDbCommand();
107 //创建一个SqlConnection对象
108 OleDbConnection conn = new OleDbConnection(connectionString);
109 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
110 //因此commandBehaviour.CloseConnection 就不会执行
111 try
112 {
113 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数
114 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
115 //调用 SqlCommand 的 ExecuteReader 方法
116 OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
117 //清除参数
118 cmd.Parameters.Clear();
119 return reader;
120 }
121 catch
122 {
123 //关闭连接，抛出异常
124 conn.Close();
125 throw;
126 }
127 }
128 /// <summary>
129 /// 返回一个DataSet数据集
130 /// </summary>
131 /// <param name="connectionString">一个有效的连接字符串</param>
132 /// <param name="cmdText">存储过程名称或者sql命令语句</param>
133 /// <param name="commandParameters">执行命令所用参数的集合</param>
134 /// <returns>包含结果的数据集</returns>
135 public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
136 {
137 //创建一个SqlCommand对象，并对其进行初始化
138 OleDbCommand cmd = new OleDbCommand();
139 using (OleDbConnection conn = new OleDbConnection(connectionString))
140 {
141 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
142 //创建SqlDataAdapter对象以及DataSet
143 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
144 DataSet ds = new DataSet();
145 try
146 {
147 //填充ds
148 da.Fill(ds);
149 // 清除cmd的参数集合
150 cmd.Parameters.Clear();
151 //返回ds
152 return ds;
153 }
154 catch
155 {
156 //关闭连接，抛出异常
157 conn.Close();
158 throw;
159 }
160 }
161 }
162 /// <summary>
163 /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
164 /// </summary>
165 /// <remarks>
166 ///例如:
167 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
168 /// </remarks>
169 ///<param name="connectionString">一个有效的连接字符串</param>
170 /// <param name="commandText">存储过程名称或者sql命令语句</param>
171 /// <param name="commandParameters">执行命令所用参数的集合</param>
172 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
173 public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
174 {
175 OleDbCommand cmd = new OleDbCommand();
176 using (OleDbConnection connection = new OleDbConnection(connectionString))
177 {
178 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
179 object val = cmd.ExecuteScalar();
180 cmd.Parameters.Clear();
181 return val;
182 }
183 }
184 /// <summary>
185 /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
186 /// </summary>
187 /// <remarks>
188 /// 例如:
189 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
190 /// </remarks>
191 /// <param name="conn">一个存在的数据库连接</param>
192 /// <param name="commandText">存储过程名称或者sql命令语句</param>
193 /// <param name="commandParameters">执行命令所用参数的集合</param>
194 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
195 public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
196 {
197 OleDbCommand cmd = new OleDbCommand();
198 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
199 object val = cmd.ExecuteScalar();
200 cmd.Parameters.Clear();
201 return val;
202 }
203 /// <summary>
204 /// 将参数集合添加到缓存
205 /// </summary>
206 /// <param name="cacheKey">添加到缓存的变量</param>
207 /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param>
208 public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
209 {
210 parmCache[cacheKey] = commandParameters;
211 }
212 /// <summary>
213 /// 找回缓存参数集合
214 /// </summary>
215 /// <param name="cacheKey">用于找回参数的关键字</param>
216 /// <returns>缓存的参数集合</returns>
217 public static OleDbParameter[] GetCachedParameters(string cacheKey)
218 {
219 OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
220 if (cachedParms == null)
221 return null;
222 OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
223 for (int i = 0, j = cachedParms.Length; i < j; i++)
224 clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone();
225 return clonedParms;
226 }
227 /// <summary>
228 /// 准备执行一个命令
229 /// </summary>
230 /// <param name="cmd">sql命令</param>
231 /// <param name="conn">Sql连接</param>
232 /// <param name="trans">Sql事务</param>
233 /// <param name="cmdText">命令文本,例如：Select * from Products</param>
234 /// <param name="cmdParms">执行命令的参数</param>
235 private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
236 {
237 //判断连接的状态。如果是关闭状态，则打开
238 if (conn.State != ConnectionState.Open)
239 conn.Open();
240 //cmd属性赋值
241 cmd.Connection = conn;
242 cmd.CommandText = cmdText;
243 //是否需要用到事务处理
244 if (trans != null)
245 cmd.Transaction = trans;
246 cmd.CommandType = CommandType.Text;
247 //添加cmd需要的存储过程参数
248 if (cmdParms != null)
249 {
250 foreach (OleDbParameter parm in cmdParms)
251 cmd.Parameters.Add(parm);
252 }
253 }
254 }
浙公网安备 33010602011771号