C#从Access数据库中读取文件

  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