众所周知，在与.Net数据交互中，SqlServer是最好的数据库选择。而在单机中，Access也占据很大份额。今天奉上自己的AccessHelper类库，用作Access2003数据库的基本操作，这里没有加入什么接口、泛型、抽象类……等等超高深概念，这是基础中的基础，给各位当踏脚石的。
请看代码：
1 using System; 2 using System.Collections.Generic; 3 using System.Data.OleDb; 4 using System.Data; 5 using System.Collections; 6 using System.Reflection; 7 using System.Runtime.InteropServices; 8 9 /// <summary> 10 /// Access 数据库的操作类库。代码原创。 11 /// </summary> 12 public class AccessHelper 13 { 14 #region[字段] 15 private string accesspath = string.Empty; 16 private string accesspwd = string.Empty; 17 18 private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb"; 19 //"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\1.mdb;User ID='admin';Password='rr'; 20 OleDbConnection connect = null; 21 OleDbCommand command = null; 22 #endregion 23 #region[属性] 24 /// <summary> 25 /// Access数据库连接字符串 26 /// </summary> 27 public string ConnectionString 28 { 29 get { return connectionString; } 30 set { connectionString = value; } 31 } 32 33 /// <summary> 34 /// Access数据库路径 35 /// </summary> 36 public string AccessPath 37 { 38 get { return accesspath; } 39 set { accesspath = value; } 40 } 41 #endregion 42 #region[构造函数] 43 /// <summary> 44 /// 构造函数，初始化 45 /// </summary> 46 /// <param name="FilePath">Access文件路径</param> 47 public AccessHelper(string FilePath) 48 { 49 ToFullRootPath(FilePath); 50 ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessPath; 51 connect = new OleDbConnection(ConnectionString); 52 } 53 /// <summary> 54 /// 构造函数，初始化 55 /// </summary> 56 /// <param name="FilePath">Access文件路径</param> 57 /// <param name="PassWord">密码</param> 58 public AccessHelper(string FilePath, string PassWord) 59 { 60 accesspwd = PassWord; 61 ToFullRootPath(FilePath); 62 ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};User ID=Admin;Jet OLEDB:Database Password={1};", AccessPath, accesspwd); 63 connect = new OleDbConnection(ConnectionString); 64 } 65 #endregion 66 #region[私有函数] 67 private void Open() 68 { 69 try 70 { 71 if (connect.State != System.Data.ConnectionState.Open) 72 { 73 connect.Open(); 74 } 75 76 } 77 catch (Exception ex) 78 { 79 throw (new Exception(ex.Message)); 80 } 81 } 82 83 private void Close() 84 { 85 try 86 { 87 if (connect.State != System.Data.ConnectionState.Closed) 88 { 89 connect.Close(); 90 } 91 92 } 93 catch (Exception ex) 94 { 95 throw (new Exception(ex.Message)); 96 } 97 } 98 99 private void ToFullRootPath(string path) 100 { 101 if (System.IO.Path.IsPathRooted(path)) 102 { 103 AccessPath = path; 104 } 105 else 106 { 107 AccessPath = AppDomain.CurrentDomain.BaseDirectory + path; 108 } 109 } 110 #endregion 111 112 #region[数据库简单操作] 113 /// <summary> 114 /// 测试是否能够连通 115 /// </summary> 116 /// <returns>布尔值</returns> 117 public bool ConnectTest() 118 { 119 try 120 { 121 connect.Open(); 122 } 123 catch 124 { 125 connect.Close(); 126 return false; 127 } 128 return true; 129 } 130 131 /// <summary> 132 /// 执行无返回的Sql语句，如插入，删除，更新 133 /// </summary> 134 /// <param name="sqlstr">SQL语句</param> 135 /// <returns>受影响的条数,出错则产生异常</returns> 136 public int ExecuteNonQuery(string sqlstr) 137 { 138 try 139 { 140 Open(); 141 command = new OleDbCommand(sqlstr, connect); 142 int num = command.ExecuteNonQuery(); 143 command.Parameters.Clear(); 144 Close(); 145 return num; 146 } 147 catch 148 { throw; } 149 } 150 151 /// <summary> 152 /// 执行查询语句，返回DataSet 153 /// </summary> 154 /// <param name="sqlstr">Sql</param> 155 /// <returns>DataSet数据集,出错则产生异常</returns> 156 public DataSet ReturnDataSet(string sqlstr) 157 { 158 DataSet ds = new DataSet(); 159 try 160 { 161 OleDbDataAdapter adapter = new OleDbDataAdapter(sqlstr, connect);//adapter可自动打开数据库连接，不用Open() 162 adapter.Fill(ds, "Obj"); 163 adapter.Dispose(); 164 } 165 catch (Exception) 166 { 167 throw; 168 } 169 return ds; 170 171 } 172 173 /// <summary> 174 /// 执行查询语句，返回DataTable 175 /// </summary> 176 /// <param name="sqlstr">Sqk</param> 177 /// <returns>DataTable数据表,出错则产生异常</returns> 178 public DataTable ReturnDataTable(string sqlstr) 179 { 180 try 181 { 182 return ReturnDataSet(sqlstr).Tables[0]; 183 } 184 catch { throw; } 185 } 186 187 /// <summary> 188 /// 执行查询语句，返回DataReader 189 /// </summary> 190 /// <param name="sqlstr">Sql</param> 191 /// <returns>DataReader,出错则产生异常</returns> 192 public OleDbDataReader ReturnDataReader(string sqlstr) 193 { 194 try 195 { 196 Open(); 197 command = new OleDbCommand(sqlstr, connect); 198 OleDbDataReader myReader = command.ExecuteReader(); 199 command.Parameters.Clear(); 200 Close(); 201 return myReader; 202 } 203 catch (System.Data.SqlClient.SqlException e) 204 { 205 throw new Exception(e.Message,e); 206 } 207 208 } 209 210 /// <summary> 211 /// 执行事务,出错则产生异常 212 /// </summary> 213 /// <param name="SQLStringList">事务集合</param> 214 public void ExecuteSqlTran(ArrayList SQLStringList) 215 { 216 217 Open(); 218 command = new OleDbCommand(); 219 command.Connection = connect; 220 OleDbTransaction tx = connect.BeginTransaction(); 221 command.Transaction = tx; 222 try 223 { 224 for (int n = 0; n < SQLStringList.Count; n++) 225 { 226 string strsql = SQLStringList[n].ToString(); 227 if (strsql.Trim().Length > 1) 228 { 229 command.CommandText = strsql; 230 command.ExecuteNonQuery(); 231 } 232 } 233 tx.Commit(); 234 Close(); 235 } 236 catch (Exception) 237 { 238 tx.Rollback(); 239 Close(); 240 throw; 241 } 242 } 243 244 /// <summary> 245 /// Access获取数据库中的所有表 246 /// </summary> 247 /// <returns>表集合</returns> 248 public string[] GetTables() 249 { 250 List<string> Ls = new List<string>(); 251 252 Open(); 253 DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" }); //利用oledbconnection的getoledbschematable来获得数据库的结构 254 Close(); 255 foreach (DataRow dr in dt.Rows) 256 { 257 Ls.Add((string)dr["table_name"]); 258 } 259 260 return Ls.ToArray(); 261 } 262 263 /// <summary> 264 /// 获取指定表的所有列 265 /// </summary> 266 /// <param name="TableName">表名</param> 267 /// <returns>列集合</returns> 268 public string[] GetColumns(string TableName) 269 { 270 Open(); 271 DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null }); 272 Close(); 273 List<string> Ls = new List<string>(); 274 for (int i = 0; i < dt.Rows.Count; i++) 275 { 276 Ls.Add(dt.Rows[i]["COLUMN_NAME"].ToString());//["COLUMN_NAME"]); 277 } 278 return Ls.ToArray(); 279 280 } 281 #endregion 282 283 #region[数据库整体操作] 284 /// <summary> 285 /// 创建Access2003版本的数据库，可设密码 286 /// 287 /// 288 /// Exception 289 /// 通常创建数据库失败时会抛出基本类型的Exception，请注意在使用该方法时提供接收异常的容器 290 /// </summary> 291 /// <param name="mdbFilePath">Access数据库地址</param> 292 /// <param name="password">密码</param> 293 /// <returns></returns> 294 public static bool CreateMDB(string mdbFilePath, string password) 295 { 296 if (System.IO.File.Exists(mdbFilePath)) 297 { 298 throw (new Exception("当前目录下已存在该数据库，为保证安全性，请先删除后在新建！")); 299 } 300 try 301 { 302 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"; 303 if (password == null || password.Trim() == "") 304 { 305 connStr += "Data Source=" + mdbFilePath; 306 } 307 else 308 { 309 connStr += "Jet OLEDB:Database Password=" + password + ";Data Source=" + mdbFilePath; 310 } 311 object objCatalog = Activator.CreateInstance(Type.GetTypeFromProgID("ADOX.Catalog")); 312 object[] oParams = new object[] { connStr }; 313 objCatalog.GetType().InvokeMember("Create", BindingFlags.InvokeMethod, null, objCatalog, oParams); 314 Marshal.ReleaseComObject(objCatalog); 315 objCatalog = null; 316 return true; 317 } 318 catch 319 { 320 throw; 321 } 322 } 323 324 /// <summary> 325 /// 创建Access2003版本的数据库 326 /// 327 /// 328 /// Exception 329 /// 通常创建数据库失败时会抛出基本类型的Exception，请注意在使用该方法时提供接收异常的容器 330 /// </summary> 331 /// <param name="mdbFilePath">Access数据库地址</param> 332 /// <returns></returns> 333 public static bool CreateMDB(string mdbFilePath) 334 { 335 try 336 { 337 CreateMDB(mdbFilePath, null); 338 return true; 339 } 340 catch 341 { 342 throw; 343 } 344 } 345 346 /// <summary> 347 /// 设置或修改Access数据库的访问密码 348 /// 349 /// Exception 350 /// 设置失败时会弹出异常，请注意提供容器 351 /// </summary> 352 /// <param name="mdbFilePath">数据库文件路径</param> 353 /// <param name="oldPwd">旧密码</param> 354 /// <param name="newPwd">新密码</param> 355 /// <returns></returns> 356 public static bool SetMDBPassword(string mdbFilePath, string oldPwd, string newPwd) 357 { 358 string connStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;","Mode=Share Deny Read|Share Deny Write;", /*独占模式*/"Jet OLEDB:Database Password=" + oldPwd + ";Data Source=" + mdbFilePath); 359 using (OleDbConnection conn = new OleDbConnection(connStr)) 360 { 361 try 362 { 363 conn.Open(); 364 //如果密码为空时，请不要写方括号，只写一个null即可 365 string sqlOldPwd = (oldPwd == null || oldPwd.Trim() == "") ? "null" : "[" + oldPwd + "]"; 366 string sqlNewPwd = (newPwd == null || newPwd.Trim() == "") ? "null" : "[" + newPwd + "]"; 367 OleDbCommand cmd = new OleDbCommand(string.Concat("ALTER DATABASE PASSWORD ", sqlNewPwd, " ", sqlOldPwd),conn); 368 cmd.ExecuteNonQuery(); 369 conn.Close(); 370 return true; 371 } 372 catch 373 { 374 throw; 375 } 376 } 377 } 378 379 /// <summary> 380 /// 设置或修改Access数据库的访问密码 381 /// 382 /// Exception 383 /// 设置失败时会弹出异常，请注意提供容器 384 /// </summary> 385 /// <param name="newPwd">新密码</param> 386 /// <returns></returns> 387 public bool SetMDBPassword(string newPwd) 388 { 389 string connStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;", "Mode=Share Deny Read|Share Deny Write;", /*独占模式*/"Jet OLEDB:Database Password=" + accesspwd + ";Data Source=" + AccessPath); 390 using (OleDbConnection conn = new OleDbConnection(connStr)) 391 { 392 try 393 { 394 conn.Open(); 395 //如果密码为空时，请不要写方括号，只写一个null即可 396 string sqlOldPwd = (accesspwd == null || accesspwd.Trim() == "") ? "null" : "[" + accesspwd + "]"; 397 string sqlNewPwd = (newPwd == null || newPwd.Trim() == "") ? "null" : "[" + newPwd + "]"; 398 OleDbCommand cmd = new OleDbCommand(string.Concat("ALTER DATABASE PASSWORD ", sqlNewPwd, " ", sqlOldPwd), conn); 399 cmd.ExecuteNonQuery(); 400 conn.Close(); 401 return true; 402 } 403 catch 404 { 405 throw; 406 } 407 } 408 } 409 #endregion 410 411 }
代码不具备美观性，大家可以拍砖！！呵呵。也希望共同进步！