1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Data.OleDb;
6
7 namespace DNCWinService
8 {
9 /**/
10 /// <summary>
11 /// DataAccess 的摘要说明
12 /// </summary>
13 public class AccessDBHelper
14 {
15 protected static OleDbConnection conn =
new OleDbConnection();
16 protected static OleDbCommand comm =
new OleDbCommand();
17 public AccessDBHelper()
18 {
19 //init
20 }
21 /**/
22 /// <summary>
23 /// 打开数据库
24 /// </summary>
25 private static void openConnection(
string path)
26 {
27 if (conn.State ==
ConnectionState.Closed)
28 {
29 conn.ConnectionString =
@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + path;
//web.config文件里设定。
30 comm.Connection =
conn;
31 try
32 {
33 conn.Open();
34 }
35 catch (Exception e)
36 {
throw new Exception(e.Message); }
37 }
38 }
39 /**/
40 /// <summary>
41 /// 关闭数据库
42 /// </summary>
43 private static void closeConnection()
44 {
45 if (conn.State ==
ConnectionState.Open)
46 {
47 conn.Close();
48 conn.Dispose();
49 comm.Dispose();
50 }
51 }
52 /**/
53 /// <summary>
54 /// 执行sql语句
55 /// </summary>
56 /// <param name="sqlstr"></param>
57 public static void excuteSql(
string sqlstr,
string path)
58 {
59 try
60 {
61 openConnection(path);
62 comm.CommandType =
CommandType.Text;
63 comm.CommandText =
sqlstr;
64 comm.ExecuteNonQuery();
65 }
66 catch (Exception e)
67 {
68 throw new Exception(e.Message);
69 }
70 finally
71 { closeConnection(); }
72 }
73 /**/
74 /// <summary>
75 /// 返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。
76 /// </summary>
77 /// <param name="sqlstr"></param>
78 /// <returns></returns>
79 public static OleDbDataReader dataReader(
string sqlstr,
string path)
80 {
81 OleDbDataReader dr =
null;
82 try
83 {
84 openConnection(path);
85 comm.CommandText =
sqlstr;
86 comm.CommandType =
CommandType.Text;
87 dr =
comm.ExecuteReader(CommandBehavior.CloseConnection);
88 }
89 catch
90 {
91 try
92 {
93 dr.Close();
94 closeConnection();
95 }
96 catch { }
97 }
98 return dr;
99 }
100 /**/
101 /// <summary>
102 /// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭
103 /// </summary>
104 /// <param name="sqlstr"></param>
105 /// <param name="dr"></param>
106 public static void dataReader(
string sqlstr,
ref OleDbDataReader dr,
string path)
107 {
108 try
109 {
110 openConnection(path);
111 comm.CommandText =
sqlstr;
112 comm.CommandType =
CommandType.Text;
113 dr =
comm.ExecuteReader(CommandBehavior.CloseConnection);
114 }
115 catch
116 {
117 try
118 {
119 if (dr !=
null && !
dr.IsClosed)
120 dr.Close();
121 }
122 catch
123 {
124 }
125 finally
126 {
127 closeConnection();
128 }
129 }
130 }
131 /**/
132 /// <summary>
133 /// 返回指定sql语句的dataset
134 /// </summary>
135 /// <param name="sqlstr"></param>
136 /// <returns></returns>
137 public static DataSet dataSet(
string sqlstr,
string path)
138 {
139 DataSet ds =
new DataSet();
140 OleDbDataAdapter da =
new OleDbDataAdapter();
141 try
142 {
143 openConnection(path);
144 comm.CommandType =
CommandType.Text;
145 comm.CommandText =
sqlstr;
146 da.SelectCommand =
comm;
147 da.Fill(ds);
148 }
149 catch (Exception e)
150 {
151 throw new Exception(e.Message);
152 }
153 finally
154 {
155 closeConnection();
156 }
157 return ds;
158 }
159 /**/
160 /// <summary>
161 /// 返回指定sql语句的dataset
162 /// </summary>
163 /// <param name="sqlstr"></param>
164 /// <param name="ds"></param>
165 public static void dataSet(
string sqlstr,
ref DataSet ds,
string path)
166 {
167 OleDbDataAdapter da =
new OleDbDataAdapter();
168 try
169 {
170 openConnection(path);
171 comm.CommandType =
CommandType.Text;
172 comm.CommandText =
sqlstr;
173 da.SelectCommand =
comm;
174 da.Fill(ds);
175 }
176 catch (Exception e)
177 {
178 throw new Exception(e.Message);
179 }
180 finally
181 {
182 closeConnection();
183 }
184 }
185 /**/
186 /// <summary>
187 /// 返回指定sql语句的datatable
188 /// </summary>
189 /// <param name="sqlstr"></param>
190 /// <returns></returns>
191 public static DataTable dataTable(
string sqlstr,
string path)
192 {
193 DataTable dt =
new DataTable();
194 OleDbDataAdapter da =
new OleDbDataAdapter();
195 try
196 {
197 openConnection(path);
198 comm.CommandType =
CommandType.Text;
199 comm.CommandText =
sqlstr;
200 da.SelectCommand =
comm;
201 da.Fill(dt);
202 }
203 catch (Exception e)
204 {
205 throw new Exception(e.Message);
206 }
207 finally
208 {
209 closeConnection();
210 }
211 return dt;
212 }
213 /**/
214 /// <summary>
215 /// 返回指定sql语句的datatable
216 /// </summary>
217 /// <param name="sqlstr"></param>
218 /// <param name="dt"></param>
219 public static void dataTable(
string sqlstr,
ref DataTable dt,
string path)
220 {
221 OleDbDataAdapter da =
new OleDbDataAdapter();
222 try
223 {
224 openConnection(path);
225 comm.CommandType =
CommandType.Text;
226 comm.CommandText =
sqlstr;
227 da.SelectCommand =
comm;
228 da.Fill(dt);
229 }
230 catch (Exception e)
231 {
232 throw new Exception(e.Message);
233 }
234 finally
235 {
236 closeConnection();
237 }
238 }
239 /**/
240 /// <summary>
241 /// 返回指定sql语句的dataview
242 /// </summary>
243 /// <param name="sqlstr"></param>
244 /// <returns></returns>
245 public static DataView dataView(
string sqlstr,
string path)
246 {
247 OleDbDataAdapter da =
new OleDbDataAdapter();
248 DataView dv =
new DataView();
249 DataSet ds =
new DataSet();
250 try
251 {
252 openConnection(path);
253 comm.CommandType =
CommandType.Text;
254 comm.CommandText =
sqlstr;
255 da.SelectCommand =
comm;
256 da.Fill(ds);
257 dv = ds.Tables[
0].DefaultView;
258 }
259 catch (Exception e)
260 {
261 throw new Exception(e.Message);
262 }
263 finally
264 {
265 closeConnection();
266 }
267 return dv;
268 }
269
270 }
271 }
老版的Access文件(文件类型以mde结尾的等)，conn.ConnectionString使用代码中的字符串
新版的Access文件(文件类型以accdb结尾的等)
conn.ConnectionString = @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + path;//web.config文件里设定。
如果程序报错：未在本地计算机上注册"microsoft.ACE.oledb.12.0"，去这里下载
http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
转载于:https://www.cnblogs.com/zhangfeng1993/p/6163526.html
相关资源：C#打开ACCESS基本操作
