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
