本文以ADOX操作Access

1、在C#中新建AccessClass.cs文件，內容如下

1 using System;2 using System.Collections.Generic;3 using System.Linq;4 using System.Text;5 using System.Collections;6 using System.IO; //包含File7 using System.Data; //包含datatable8 using System.Data.OleDb; //包含OleDbConnection9 using System.Windows.Forms;10 11 namespace CsharpAccess090312 {13 public abstract class AccessClass14 {15 public static string myMdbPath;16 public static string[] myTableName=new string[2];17 public static ArrayList[] myMdbHead=new ArrayList[2];18 19 //普通的節點 20 public struct Node21 {22 private string nodeType;23 public string NodeType//表的字段名 24 {25 set { nodeType = value; }26 get { return nodeType; }27 }28 29 private string nodeValue;30 public string NodeValue//具體的值 31 {32 set { nodeValue = value; }33 get { return nodeValue; }34 }35 }36 37 //數據庫初始化38 public static bool MDBInit(string mdbPath, string[] tableName, ArrayList[] mdbHead, int tableNum)39 {40 //檢測數據庫是否存在，若不存在則創建數據庫并創建數據表41 if (!File.Exists(mdbPath))42 {43 CreateMDBDataBase(mdbPath);//創建mdb44 for (int i = 0; i < tableNum; i++)45 {46 CreateMDBTable(mdbPath, tableName[i], mdbHead[i]);//創建數據表47 }48 }49 myMdbPath = mdbPath;50 for (int i = 0; i < tableNum; i++)51 {52 myTableName[i] = tableName[i].Clone().ToString();53 }54 for (int i = 0; i < tableNum; i++)55 {56 myMdbHead[i] = (ArrayList)mdbHead[i].Clone();57 }58 return true;59 }60 61 //創建mdb 62 public static bool CreateMDBDataBase(string mdbPath)63 {64 try65 {66 ADOX.CatalogClass cat = new ADOX.CatalogClass();67 cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";");68 cat = null;69 return true;70 }71 catch { return false; }72 }73 74 //新建mdb的表 //mdbHead是一個ArrayList，存儲的是table表中的具體列名。 75 public static bool CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)76 {77 try78 {79 ADOX.CatalogClass cat = new ADOX.CatalogClass();//=========================================//=====================================//80 string sAccessConnection81 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath;82 ADODB.Connection cn = new ADODB.Connection();83 cn.Open(sAccessConnection, null, null, -1);84 cat.ActiveConnection = cn;85 86 //新建一個表 87 ADOX.TableClass tbl = new ADOX.TableClass();88 tbl.ParentCatalog = cat;89 tbl.Name = tableName;90 91 int size = mdbHead.Count;92 for (int i = 0; i < size; i++)93 {94 //增加一個文本字段 95 ADOX.ColumnClass col2 = new ADOX.ColumnClass();96 col2.ParentCatalog = cat;97 col2.Name = mdbHead[i].ToString();//列的名稱 98 col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;99 tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500); //把列加入table 100 } 101 cat.Tables.Append(tbl); //這句把表加入數據庫(非常重要) 102 tbl = null; 103 cat = null; 104 cn.Close(); 105 return true; 106 } 107 catch { return false; } 108 } 109 110 111 // 讀取mdb指定表的全部數據 112 public static DataTable ReadAllData(string tableName, string mdbPath, ref bool success) 113 { 114 DataTable dt = new DataTable(); 115 try 116 { 117 DataRow dr; 118 //1、建立連接 119 string strConn 120 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=";//創建數據庫時未設用戶及密碼，不用提交密碼即可操作數據庫 121 OleDbConnection odcConnection = new OleDbConnection(strConn); 122 //2、打開連接 123 odcConnection.Open(); 124 //建立SQL查詢 125 OleDbCommand odCommand = odcConnection.CreateCommand(); 126 //3、輸入查詢語句 127 odCommand.CommandText = "select * from " + tableName; 128 //建立讀取 129 OleDbDataReader odrReader = odCommand.ExecuteReader(); 130 //查詢并顯示數據 131 int size = odrReader.FieldCount; //獲取當前行中的列數 132 for (int i = 0; i < size; i++) 133 { 134 DataColumn dc; 135 dc = new DataColumn(odrReader.GetName(i)); //獲取每一列的名稱，放入列結構中 136 dt.Columns.Add(dc); //新建的table完成屬性列的構建 137 } 138 while (odrReader.Read()) //逐條記錄(也即逐行)地讀取數據 139 { 140 dr = dt.NewRow(); //一行數據 141 for (int i = 0; i < size; i++) 142 { 143 dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString(); //逐列讀取每單元格數據放到行結構中 144 } 145 dt.Rows.Add(dr); //新建的table完成每條記錄的存儲 146 } 147 //關閉連接 148 odrReader.Close(); 149 odcConnection.Close(); 150 success = true; 151 return dt; 152 } 153 catch 154 { 155 success = false; 156 return dt; 157 } 158 } 159 160 161 // 讀取指定表的若干列的數據 162 public static DataTable ReadDataByColumns(string mdbPath, string tableName, string[] columns, ref bool success) 163 { 164 DataTable dt = new DataTable(); 165 try 166 { 167 DataRow dr; 168 //1、建立連接 169 string strConn 170 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password="; 171 OleDbConnection odcConnection = new OleDbConnection(strConn); 172 //2、打開連接 173 odcConnection.Open(); 174 //建立SQL查詢 175 OleDbCommand odCommand = odcConnection.CreateCommand(); 176 //3、輸入查詢語句 177 string strColumn = ""; 178 //if (columns[0] == null) 179 //{ 180 // strColumn = " * "; 181 //} 182 //else 183 //{ 184 for (int i = 0; i < columns.Length; i++) 185 { 186 strColumn += columns[i].ToString() + ","; 187 } 188 strColumn = strColumn.TrimEnd(','); 189 //} 190 191 odCommand.CommandText = "select " + strColumn + " from " + tableName; 192 //建立讀取 193 OleDbDataReader odrReader = odCommand.ExecuteReader(); 194 //查詢并顯示數據 195 int size = odrReader.FieldCount; 196 for (int i = 0; i < size; i++) 197 { 198 DataColumn dc; 199 dc = new DataColumn(odrReader.GetName(i)); 200 dt.Columns.Add(dc); 201 } 202 203 while (odrReader.Read()) 204 { 205 dr = dt.NewRow(); 206 for (int i = 0; i < size; i++) 207 { 208 dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString(); 209 } 210 dt.Rows.Add(dr); 211 } 212 //關閉連接 213 odrReader.Close(); 214 odcConnection.Close(); 215 success = true; 216 return dt; 217 } 218 catch 219 { 220 success = false; 221 return dt; 222 } 223 } 224 225 //讀取指定列數據 226 public static DataTable ReadDataByColumnsBound(string mdbPath, string tableName, string[] columns, Node bound, ref bool success) 227 { 228 DataTable dt = new DataTable(); 229 try 230 { 231 DataRow dr; 232 //1、建立連接 233 string strConn 234 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 235 OleDbConnection odcConnection = new OleDbConnection(strConn); 236 //2、打開連接 237 odcConnection.Open(); 238 //建立SQL查詢 239 OleDbCommand odCommand = odcConnection.CreateCommand(); 240 //3、輸入查詢語句 241 string strColumn = ""; 242 if (columns[0] == null) 243 { 244 strColumn = " * "; 245 } 246 else 247 { 248 for (int i = 0; i < columns.Length; i++) 249 { 250 strColumn += columns[i].ToString() + ","; 251 } 252 strColumn = strColumn.TrimEnd(','); 253 } 254 255 odCommand.CommandText = "select " + strColumn + " from " + tableName + " where " + bound.NodeType + " = '" + bound.NodeValue + "' "; 256 //建立讀取 257 OleDbDataReader odrReader = odCommand.ExecuteReader(); 258 //查詢并顯示數據 259 int size = odrReader.FieldCount; 260 for (int i = 0; i < size; i++) 261 { 262 DataColumn dc; 263 dc = new DataColumn(odrReader.GetName(i)); 264 dt.Columns.Add(dc); 265 } 266 267 while (odrReader.Read()) 268 { 269 dr = dt.NewRow(); 270 for (int i = 0; i < size; i++) 271 { 272 dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString(); 273 } 274 dt.Rows.Add(dr); 275 } 276 //關閉連接 277 odrReader.Close(); 278 odcConnection.Close(); 279 success = true; 280 return dt; 281 } 282 catch 283 { 284 success = false; 285 return dt; 286 } 287 } 288 //================================================================== 289 //函數名： GetTableNameList 290 //作者： LiYang 291 //日期： 2015-6-26 292 //功能： 獲取ACCESS數據庫中的表名 293 //輸入參數： 數據庫路徑 294 //返回值： List<string> 295 //修改記錄： 296 //================================================================== 297 public static List<string> GetTableNameList(string myMdbPath) 298 { 299 List<string> list = new List<string>(); 300 try 301 { 302 OleDbConnection Conn = new OleDbConnection(); 303 Conn.ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=" + myMdbPath; 304 Conn.Open(); 305 DataTable dt = Conn.GetSchema("Tables"); 306 foreach (DataRow row in dt.Rows) 307 { 308 if (row[3].ToString() == "TABLE") 309 list.Add(row[2].ToString()); 310 } 311 Conn.Close(); 312 return list; 313 } 314 catch (Exception ex) 315 { 316 throw ex; 317 } 318 } 319 //================================================================== 320 //函數名： GetTableFieldNameList 321 //作者： LiYang 322 //日期： 2015-6-26 323 //功能： 獲取ACCESS數據庫指定表名中的字段 324 //輸入參數： 數據庫路徑，表名 325 //返回值： List<string> 屬性列的名稱 326 //修改記錄： 327 //================================================================== 328 public static List<string> GetTableFieldNameList(string myMdbPath, string TableName) 329 { 330 List<string> list = new List<string>(); 331 try 332 { 333 OleDbConnection Conn = new OleDbConnection(); 334 Conn.ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=" + myMdbPath; 335 Conn.Open(); 336 using (OleDbCommand cmd = new OleDbCommand()) 337 { 338 cmd.CommandText = "SELECT TOP 1 * FROM [" + TableName + "]";//讀取一個表中第一條記錄，如select top 5 * from tablename，讀取表中前5條記錄 339 cmd.Connection = Conn; 340 OleDbDataReader dr = cmd.ExecuteReader(); //得到列頭 341 for (int i = 0; i < dr.FieldCount; i++) 342 { 343 list.Add(dr.GetName(i));//得到屬性列名稱 344 } 345 } 346 Conn.Close(); 347 return list; 348 } 349 catch (Exception ex) 350 { 351 throw ex; 352 } 353 } 354 //================================================================== 355 //函數名： ReadDataByColumnsLike 356 //作者： LiYang 357 //日期： 2015-6-26 358 //功能： 獲取ACCESS數據庫指定表名指定列的相似數據到緩存DataTable 359 //輸入參數： 數據庫路徑，表名，列名，相似結點 360 //返回值： DataTable 361 //修改記錄： 362 //================================================================== 363 public static DataTable ReadDataByColumnsLike(string mdbPath, string tableName, string[] columns, Node Like) 364 { 365 DataTable dt = new DataTable(); 366 DataRow dr; 367 try 368 { 369 OleDbConnection Conn = new OleDbConnection(); 370 Conn.ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=" + myMdbPath; 371 Conn.Open(); 372 OleDbCommand odCommand = Conn.CreateCommand(); 373 string strColumn = ""; 374 if (columns[0] == null) 375 { 376 strColumn = " * "; 377 } 378 else 379 { 380 for (int i = 0; i < columns.Length; i++) 381 { 382 strColumn += columns[i].ToString() + ","; 383 } 384 strColumn = strColumn.TrimEnd(','); 385 } 386 387 odCommand.CommandText = "select " + strColumn + " from " + tableName + " where " + Like.NodeType + " LIKE '" + Like.NodeValue + "' "; 388 OleDbDataReader odrReader = odCommand.ExecuteReader(); 389 //查詢并顯示數據 390 int size = odrReader.FieldCount; 391 for (int i = 0; i < size; i++) 392 { 393 DataColumn dc; 394 dc = new DataColumn(odrReader.GetName(i)); 395 dt.Columns.Add(dc); 396 } 397 398 while (odrReader.Read()) 399 { 400 dr = dt.NewRow(); 401 for (int i = 0; i < size; i++) 402 { 403 dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString(); 404 } 405 dt.Rows.Add(dr); 406 } 407 //關閉連接 408 odrReader.Close(); 409 Conn.Close(); 410 return dt; 411 } 412 catch (Exception ex) 413 { 414 throw ex; 415 } 416 } 417 /// <summary> 418 /// 返回某一表的所有字段名 419 /// </summary> 420 public static string[] GetTableColumn(string database_path, string varTableName) 421 { 422 DataTable dt = new DataTable(); 423 try 424 { 425 OleDbConnection conn = new OleDbConnection(); 426 conn.ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=" + database_path; 427 conn.Open(); 428 dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, varTableName, null }); 429 int n = dt.Rows.Count; 430 string[] strTable = new string[n]; 431 int m = dt.Columns.IndexOf("COLUMN_NAME"); 432 for (int i = 0; i < n; i++) 433 { 434 DataRow m_DataRow = dt.Rows[i]; 435 strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString(); 436 } 437 conn.Close(); 438 return strTable; 439 } 440 catch (Exception ex) 441 { 442 throw ex; 443 } 444 } 445 446 //添加一個字段 447 public static void MakeNode(ref ArrayList arraylist, string nodetype, string nodevalue) 448 { 449 AccessClass.Node node = new AccessClass.Node(); 450 node.NodeType = nodetype; 451 node.NodeValue = nodevalue; 452 arraylist.Add(node); 453 } 454 455 //插入數據 456 public static bool InsertRow(string mdbPath, string tableName, ArrayList insertArray, 457 ref string errinfo) 458 { 459 try 460 { 461 //1、建立連接 462 string strConn 463 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 464 OleDbConnection odcConnection = new OleDbConnection(strConn); 465 //2、打開連接 466 odcConnection.Open(); 467 468 string str_col = ""; 469 int size_col = insertArray.Count; 470 for (int i = 0; i < size_col; i++) 471 { 472 Node vipNode = new Node(); 473 try 474 { 475 vipNode = (Node)insertArray[i]; 476 } 477 catch (System.Exception ex) 478 { 479 MessageBox.Show(ex.ToString()); 480 } 481 vipNode = (Node)insertArray[i]; 482 str_col += vipNode.NodeType + ","; 483 } 484 str_col = str_col.TrimEnd(','); 485 486 487 int size_row = insertArray.Count; 488 string str_row = ""; 489 for (int i = 0; i < size_row; i++) 490 { 491 Node vipNode = new Node(); 492 vipNode = (Node)insertArray[i]; 493 string v = vipNode.NodeValue.ToString(); 494 v = DealString(v); 495 if (v == "") 496 { 497 str_row += "null" + ','; 498 } 499 else 500 { 501 str_row += "'" + v + "'" + ','; 502 } 503 } 504 str_row = str_row.TrimEnd(','); ; 505 506 string sql = "insert into " + tableName + @" (" + str_col + ") values" + @"(" + str_row + ")"; 507 OleDbCommand odCommand = new OleDbCommand(sql, odcConnection); 508 odCommand.ExecuteNonQuery(); 509 odcConnection.Close(); 510 return true; 511 } 512 catch (Exception err) 513 { 514 errinfo = err.Message; 515 return false; 516 } 517 } 518 519 520 public static bool UpdateRow(string mdbPath, string tableName, 521 Node keyNode, ArrayList insertArray, ref string errinfo) 522 { 523 try 524 { 525 //1、建立連接 526 string strConn 527 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 528 OleDbConnection odcConnection = new OleDbConnection(strConn); 529 //2、打開連接 530 odcConnection.Open(); 531 532 int size = insertArray.Count; 533 string str = ""; 534 for (int i = 0; i < size; i++) 535 { 536 Node node = new Node(); 537 node = (Node)insertArray[i]; 538 string v = node.NodeValue.ToString(); 539 v = DealString(v); 540 str += node.NodeType + " = "; 541 if (v == "") 542 { 543 str += "null" + ','; 544 } 545 else 546 { 547 str += "'" + v + "'" + ','; 548 } 549 550 } 551 str = str.TrimEnd(','); 552 553 string sql = "update " + tableName + " set " + str + 554 " where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'"; 555 OleDbCommand odCommand = new OleDbCommand(sql, odcConnection); 556 odCommand.ExecuteNonQuery(); 557 odcConnection.Close(); 558 return true; 559 } 560 catch (Exception err) 561 { 562 errinfo = err.Message; 563 return false; 564 } 565 } 566 567 568 public static bool UpdateMDBNode(string mdbPath, string tableName, Node keyNode, 569 Node saveNode, ref string errinfo) 570 { 571 try 572 { 573 //1、建立連接 574 string strConn 575 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 576 OleDbConnection odcConnection = new OleDbConnection(strConn); 577 //2、打開連接 578 odcConnection.Open(); 579 580 string sql = @"update " + tableName + " set " + saveNode.NodeType + " = '" + saveNode.NodeValue + 581 "' where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'"; 582 OleDbCommand comm = new OleDbCommand(sql, odcConnection); 583 comm.ExecuteNonQuery(); 584 odcConnection.Close(); 585 return true; 586 } 587 catch (Exception err) 588 { 589 errinfo = err.Message; 590 return false; 591 } 592 } 593 594 595 596 //刪除一條記錄 597 public static bool DeleteRow(string mdbPath, string tableName, Node keyNode, ref string errinfo) 598 { 599 try 600 { 601 //1、建立連接 602 string strConn 603 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 604 OleDbConnection odcConnection = new OleDbConnection(strConn); 605 //2、打開連接 606 odcConnection.Open(); 607 string sql = ""; 608 if (keyNode.NodeType == null) 609 { 610 sql = @"delete from " + tableName; 611 } 612 else 613 { 614 sql = @"delete from " + tableName + " where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "' "; 615 } 616 617 OleDbCommand comm = new OleDbCommand(sql, odcConnection); 618 comm.ExecuteNonQuery(); 619 odcConnection.Close(); 620 return true; 621 } 622 catch (Exception err) 623 { 624 errinfo = err.Message; 625 return false; 626 } 627 } 628 629 public static bool ClearTable(string mdbPath, string tableName, ref string errinfo) 630 { 631 632 try 633 { 634 //1、建立連接 635 string strConn 636 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; 637 OleDbConnection odcConnection = new OleDbConnection(strConn); 638 //2、打開連接 639 odcConnection.Open(); 640 string sql = ""; 641 642 sql = @"delete * from " + tableName; 643 644 OleDbCommand comm = new OleDbCommand(sql, odcConnection); 645 comm.ExecuteNonQuery(); 646 odcConnection.Close(); 647 648 return true; 649 } 650 651 catch (Exception err) 652 { 653 errinfo = err.Message; 654 return false; 655 } 656 657 } 658 659 660 661 //處理數據，在把數據存到數據庫前，先屏蔽那些危險字符！ 662 public static string DealString(string str) 663 { 664 str = str.Replace("<", "<"); 665 str = str.Replace(">", ">"); 666 str = str.Replace("\r", "<br>"); 667 str = str.Replace("\'", "’"); 668 str = str.Replace("\x0020", " "); 669 670 return (str); 671 } 672 673 //恢復數據：把數據庫中的數據，還原成未處理前的樣子 674 public static string UnDealString(string str) 675 { 676 str = str.Replace("<", "<"); 677 str = str.Replace(">", ">"); 678 str = str.Replace("<br>", "\r"); 679 str = str.Replace("’", "\'"); 680 str = str.Replace(" ", "\x0020"); 681 682 return (str); 683 } 684 685 686 687 } 688 }

?

?

相關問題及解決方法記錄

P:ADOX缺少程序集引用?
S：引用C:\Program Files\Common Files\System\ado\msadox.dll,該DLL包含ADOX命名空間，

P:無法對ADOX嵌入式式互操作
S：ADOX屬性->嵌入互操作類型改為false

P:ADODB缺少程序集引用
S：引用C:\Program Files\Common Files\System\ado\msjro.dll，該dll包含ADOB命名空間，或在.net中添加adodb也可。

總結

以上是生活随笔為你收集整理的C#操作Access数据库 增删改查的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。