c#操作access数据库编程

1、c#操作 Access (上)看到CSDN上不断兄弟姐妹提问关于c#操作Access的问题，于是本人利用闲暇将c#操作 Access的方法加以总结，主要解决的问题有： 创建mdb 创建table 读取table内容 查询table中的内容 向table中插入数据 删除table中的记录 向table中插入照片 读取table中的照片等。另：本人水平有限，不当之处还请斧正。废话少说，开始正题。文介绍C#访问操作Access数据庠的基础知识，并提供一个相关的例程。C#的ADQ.NET 还不能通过编程方式创建全新的ACCESS(MDB)数据庠，所以还只能使用ADQX这个来自 CQM的链接库来操作。主

2、要知识点如下：using Systcm.r)ala.()lcDb;using System.r>ata;连接字符串：String connectionstring = HPr(mdcr=Micr()s<)ft.Jct.()LEr)B.4.0；r)ata Sourcc=product.mdbM;建立连接： OlcDbConncction connection = new () 1 cDbC()nnccti()n(c(ri()nStringj; 使用OkDbCommand类来执行Sql语句：OlcDbCommaiid emd = new ()kDbCommand(sql, connec

3、tion);coniiccrion.OpcnQ;cmd.ExucukNonQuuryO；1 .创建mdb库,例程如下:需要注意的是：参数mdbPath是mdb的完整跖径(不包含表的名称)。例如：D:icst.mdbview plaincopy to clipboardprint?创建mdbpublic static bool CreateMDBDataBasc(string mdbPath) tryADOX.CatalogClass cat = new ADOX.CatalogClassO； cat.Crcatc(MPnn4dcr=Microsoft.Jct.()LEr)B.4.0;Data

4、Sourcc=M + mdbPath + cat = null;return true;catch return false; 创建mdbpublic static bool CreateMDBDataBase(string mdbPath)tryADC )X.Catak)gClass cat = new ADOX.CatalogClassO；cat.Crcalc(HPr(ATidcr=Micr()soflJct.()LEr)B.4.0；r)ata Sourcc=M + mdbPath +cat = null;return true；catch return false; 2 .创建具体的表，

5、例程如下：通常一个mdb的可以包含n个表。下面的程序主要是创建一个tablcoview plaincopy to clipboardprint?/新建mdb的表/mdbHcad是一个ArrayList,存储的是table表中的具体列仁。public static bool CreateMDBTablc(string mdbPath,string tablcNamc, ArrayList mdbHcad) try .(ADC)X.CatalogClass cat = new ADOX.CatalogClassO；string sAcccssConncction="Pr()vidukMi

6、crosoft Jut.()LEDB.4.0;Data Sourcc=M + mdbPath;Ar)()r)B.C<)nncction cn = new ADODB.ConncctionO；cn.()pun(s Access Connection, null, null, -1);cat. Ac rivcC()iiiicc tic)n = cn;新建一个表ADOX.TablcClass tbl = new ADOX.TablcClassO；tbl.ParcntCatalog = cat;tbl.Namc = tablcNamc;int size = mdbHcad.Count;for (

7、int i = 0; i < size； i+)增加一个文本字段ADOX.ColumnClass col2 = new ADOX.ColumnClassO；c()12.ParciitCatalog = cat;col2.Name = mdbHuadQToStringO； 列 的名称c()12.Pn)pcrticsrjct()LEr)B:Allow Zero Length*1.Value = false;tbl.Columns.Appcnd(c()12, ADOX.DataTypcEnum.adXarWCha 500);cat.TablcS.Appcnd(tbl);这句把表加入数据庠(非常

8、重要)ibl = null;cat = null;cn. Close。；return true;catch return false; 新建mdb的表/mdbHcad是一个ArrayList,存储的是bible表中的具体列名。public static bool CrcatcMDBTablc(string mdbPath,string tablcNamc, Arra List mdbHcad) tryADC )X.CatalogClass cat = new ADOX.CatalogClassO；string sAcccssConncction=，Pn)Tidcr=Micn)soft.Jct.

9、OLEr)B.4.0；r)ata Sourcc=H + mdbpath;ADODB.Connection cn = new ADODB.ConnectionQ;cn.OpcsAcccssConncction, null, null, -1);cat.AcrivcCoiuicction = cn；新建一个表AD( )X.TabIcClass tbl = new AD()X.TablcClassO;tbl.ParcntCatalog = cat;tbl.Namc = tablcNamc;int size = mdbHcad.Count;for (int i = 0; i < size; i+

10、) /增加一个文本字段ADOX.ColumnClass c()12 = new ADOX.ColumnClassO；c()12.ParciitCatalog = cat;c()12.Namc = mdbHuadm.ToStringO；/歹 lj 的名称co.Propertiesf1 OLEDB:Allow Zero LengthM.Valuc = false;tbl.ColumnsAppund(col2, ADOX.r)ataTypcEnum.ad'7arWChar, 500);cat.Tablcs.Appcnd(tbl);/这句把表加入数据库徘常重要)tbl = null;cat =

11、 null;cn. Closer。；rctiirn true；3 .读取mdb内容(完全读取)，例程如下：本例程返回的是一个HalaTablc,如需其他格式可以自行转换。view plaincopy to clipboardprint?/读取mdb数据public static DataTablc RcadAllData(string tablcNamc, string mdbPathjcf bool success) DataTablc dt = new DataTablcQ;DataRow dr;/1'建立连接string strConn=HPn)ndcr=Micn)s()ft.J

12、ct.OLEr)B.4.0；r)ata Sourcc=H + mdbpath + "Jut QLEDB:Database Password=hacrun"OlcDbCoiiiiccrion odcComicction = ()lcr)bC()nncction(strC()nn);2、打开连接odcCoinicction.OpcnQ;建立SQL查询QkDbCcmmand odCommaiid = odcCoiiiiccrion.CrcatcCommaiidQ;3、输入查询语句odCommand.CommandTuxt = Hselect * from " + tab

13、lcNamc；建立读取OlcDbDaUiRcadcr odrReader = odComm<ind.ExccutcRcadcrQ;查询并显示数据int size = odrReader. FicldCount;for (int i = 0; i < size； i+)DataColumii de;de = new r>ataC<)lumn()drRcadcr.GctNamc(i);dt.Columns.Add(dc);while (xlrReader.ReadQ)dr = dt.NrwRowQ；for (int i = 0; i < size; i+)|drodr

14、Rcadcr.GctNamc(i) =(>drRcadcr(>drRcadcr.GctNamc(i).T(>StringQ;d t.R(>ws.Add(d r);关闭连接 odrRcadcr.CloscQ; odcCoinicction.CloscO； success = true;return dt;catch(success = false;return dt;/读取mdb数据public static DataTablc RcadAllData(string tablcNamc, string mdbpathjcf b(x)l success) DataTablc

15、 di = new DataTablcQ;Dataflow dr;/1'建立连接string strConn=，Pn)Tidcr=Micn)softJct.()LEr)B.4.0；r)ata Sourcc=H + mdbpath + M;Jct OLEF)B：r)atabasc Passr<>rd=ha()rciiH;OlcDbComiccrion odeConncerion = new ()luDbConnuction(strConn);2、打开连接odcConncction.OpcnQ;建立SQL查询OlcDbCommand odCommand = odcCoiiiic

16、ction.CrcatcCommiUidQ;3、输入查询语句odCommand.CommandTuxl = Mselect " from " + tablcName;建立读取OlcDbDatiRcadcr odrReader = odCommand.ExccutcRcadcrO； 查询并显示数据int size = odrReader. FicldG)uiit;for (int i = 0; i < size; i+) DataColumn de;de = new r>ataC<)lumn()drRcadcr.GctNamc(i);dt.Columns.A

17、dd(dc);while (odrRcadcr.RcadQ)dr = dt.NcwRoxv。； f<)r (int i = 0; i < size; i+) drodrRcadcr.GctNamc(i)=od rReaderfod rRcadcr.CyctN am c (i).T<)S tringQ;dt.Rovs.Add（dr）;关闭连接odrRcadcr.CloscQ;odcC<>iiiiccti（）n.Cl（）sc0;success = true； return dt;catchsuccess = false； return dt;4 .读取mdb内容（按列

18、读取），例程如下：columns数组存储的是你要查询的列占称（必须确保mdb表中存在你要的列）view plaincopy to clipboardprint?/读取mdb数据public static DataTablc RcadDataByC（）lumiis（string mdbPaht,string tablcNamc, stringf columns, ref bool success） DataTablc dt = new DataTablcQ;trvDataRow dr;1、建立连接string strComi=:，Pr<Aridcr=Micr（）s<）ft.Jct（）

19、LEDB.4.0；r）ata Sourcc=H + mdbPath + H;Jct OLEDB：r）atabasc Pass7<>rd=ha（>rcno;OlcDbCoiiiiccrion odcComicction = new （）lcr）bC（）nncction（strC（）nn）;2、打开连接（）dcC<）iuicc ti（）n.（）pcnQ;建立SQL查询QluDbCommand odCommand =（>dcConnuction.CruatcCommand（）;3、输入查询语句string strColumn =for （int i = 0; i <

20、; columns.Length; i+）slrColumn += c（）lumns i .T（）S tringO + "J;strColumn = strColumn. Trim End。；odCommaiid.CommandTcxt = Mselect H+strC（）lumn+H from " + tablcNamc；建立读取OlcDbDataRcadcr odrRcadcr = odC<>mm:ind.ExccutcRcadcrQ;查询并显示数据int size =(>drRcadcr. FicldCount;for (int i = 0; i &

21、lt; size; i+) DataColumn de;de = new r>ataC()lumn(odrRcadcr.GctNamc(i);dt.Columns.Add(dc);while (xdrReader.ReadQ) dr = dt.NrwRowO；f()r (int i = 0; i < size; i+) drodrRcadcr.GctNamc(i) =(>drRcadcrodrRcadcr.GctNamc(i).T(>Slring();d t.Rows.Add (d r);关闭连接odrRcadcr.CloscQ;odcCoiuicction.Closc

22、O；success = true； return dt;catchsuccess = false;rcUirn dl;/读取mdb数据public static DataTablc ReadDauByC(>1 umns(string mdbPaht,string ublcNamc, stringQ columns, ref bool success)DataTablc dl = new DataTablcQ;try ,DataRow dr;/1'建立连接string strConn="ProvidukMicrosoft.Jut.OLEDB.4.0;Data Sourcc

23、=H + mdbpath + H;ct OLEr)B：r)atabasc Passw)rd=hacrun"OlcDbCoiiiiccrion odcConncction = new OkDbConncction(strConn);2、打开连接odcConncc tion.OpciiQ;建立SQL查询OlcDbCommand odCommaiid = (xlcCoiiiiccrion.CrcatcCommuidQ;3、输入查询语句string strColumn =for (int i = 0; i < columns.Length; i+) strColumn += colum

24、ns 0-ToStringO +strColumn = strColumn.TrimEndf,1);odCommand.CommandTuxt = Mselect H+strC<)lumn+H from " + tablcNamc； 建立读取OlcDbDaUiRcadcr odrReader = odCommand.ExccutcRcadcrO； 查询并显示数据int size = odrReader. FicldCount;for (int i = 0; i < size; i+) DataColumn de;de = new r)ataC()lumn(odrRcadc

25、r.(TctNamc(i);dt.Columns.Add(dc);while (odrRcadcr.RcadQ)dr = dt.NcwRovvO； for (int i = 0; i < size; i+) (drodrRcadcr.GctNamc(i)=od rRcadcrod rRcadcr.GctN am c (i).T()S tringQ;dt.Rows.Add(dr);关闭连接odrRcadcr.CloscQ;odcConiicction.CloscQ;success = true;return dt;catchsuccess = false;return dt;c#操作 Ac

26、cess (下)介绍之前先介绍一个结构体。因为以下函数都要用到这个结构体。view plaincopy to clipboardprint?普通的节点public struct Nodeprivate string nodeType;public string NoduTypu表的字段名set nodeType = value; get return nodeType； )private string nodcValuc;public string NeduValuu/具体的值set nodcValuc = value; get return nodcValuc； )照片节点public st

27、ruct PictiircNodcprivate string nodeType;public string NodeType/ 照片的列名set nodeType = value； get return nodeType； )private bytc node Value;public bylcQ NoduVakiu/照片的值，注意类型set node Value = value; get return nodcValuc； 普通的节点public struct Node private string nodeType;public string NoduTypu表的字段各 set node

28、Type = value; get return nodcTypc； private string nodcVduc；public string NoduValuu/具体的值 set nodcValuc = value; get return nodcValuc; 照片节点public struct PictureNode private string nodcTypc；public string NoduTypu照片的列名 set nodcTypc = value; get return nodcTypc； private bytcQ nodcValuc；public bytcQ NoduV

29、akiu照片的值，注意类型set nodcValuc = value; get return nodcValuc; 具体就用不着多加描述了吧！继续看问题点。L向tablu中插入数据（按行插入，如果需要插入多条请自己组织这个函数就。k 了），其中的insert Array 存储的是一系列 Node, picturcX（xlc 是 PicliircN（）dc0 view plaincopy to clipboardprint?插入数据public static bool lnscrtR（）w（ string mdbPath, string tabluNamu, ArrayList insert A

30、rray, PictureNode pictureNode, ref string errinfo）try1、建立连接string strConn=<,Pn)vidcr=Nficros()flJcl.()LEr)B.4.0；r)ata Sourcc=M + mdbPalh + HJct OLEr)B：r)atabasc Passr<)rd=ha(>rcnM;()lcr)bC<)nnccti<)n odcComicction = new ( ) 1 cDbC()iinccri(>n(strC<)nn);2、打开连接<)dcC( >iiiicc

31、 tic)n.()pcnQ;string str_col =int sizc_col = insert Array.Count;for (int i = 0; i < size.col; i+)(Node vipNodu = new Node。；vipNodu = (Node) insc r t A rr ay 0 ；str_col += vipNodc.NodcTypc + "J;str_col = str_coLTrimEnd(',);int sizc_r<)w = inscrtArray.Count;string str_r<)w =for (int

32、i = 0; i < sizc_r<)w; i+)(Node vipNodu = new NodcQ;vipNodu = (N()d c) insc r t A r r ay 0 ；string v = xHpNodc.NodcValuc.ToStringQ;v = DcalS tring(v); if(v=) str_r()w += "null" + else str.row +=+ v + ,HH + *；;str_row = str_r()w.T rim Endf,1);if (pictureNode != null && picturcN

33、odc.NodcValuc != null) (str_col += '； + picturcNodc.NodcTypc;str_n)w += 齐”；string sql = "insert into " + tablcNamc + " (" + str_col + n) values1, + "(" + str-n)w +()lcr)bC<)mmand odCommand = new ()luDbCommand(sql, odcConiicction);if (pictureNode != null &&

34、; picturcNodc.NodcValuc != null)odC()mmaiid.Paramctcrs.Add(,Image, OlcDbTypc.VarBinar picturcNodc.N()dcValuc.Lcnglh).V aluc = pictureNode.NodcValuc；odCommriiid.ExccutcNoiiQucryO；<)dcC( >iiiicc ti<)n.Cl<)scQ;return true;catch (Exception err)errinfi) = crr.Mcssagr; return false；/插入数据public

35、 static bool lnscrtR()w( string mdbPath, string tablcNamc, A rray List insert A rray, PictureNode pictureNode, ref string errinfi)1、建立连接string strCoim=，Pn)ridcr=Micn)s(>ftJcl.()LEr)B.4.()；r)ata Sourcc=H + mdbpath + M;Jct OLEr)B：r>atabasc Password=harun''OlcDbComiccrion odcConnccrion =

36、()lcr)bC()nncction(stTC()nn);2、打开连接odcCoiuicction.OpcnQ;string str_colint sizc_col = inscrtArray.C<)unt; for (int i = 0; i < sizc_col; i+)Node vipNodu = new NodcQ; vipNodc = (N ode) insert A rray i; sir_c()l += npNodc.NodcTypc + H,H;str_col = str_c()l.TrimEndf/);int sizc_n)w = insert A rray.Co

37、unt; string str_row =for (int i = 0; i < sizc_r(jw; i+)Node vipNedu = new NodcQ;vipNodc = (Node) insert A rray i;string v = vipNodc.NodcVduc.ToStringO；v = DcalString(v); if(v=MH) ( str_r()w += "null" + else ( str.row +=+ v + V;str_n)w = siT_n)w.Trim Endf/);if (pictureNode != null &&

38、amp; picturcNodc.NodcValuc != null) str_col +=+ picturcNodc.NodcTypc;str_n)w += string sql = "insert into " + tableName + str_col + M) values11 + ”(" +str_n)w +OlcDbCommand odCommaiid = new OkDbCommand(sql,()dcC<nuiccti()n);if (pictureNode != null && picturcNodc.NodcValuc !

39、= null) ()dC()mmJUid.ParamcLcrs.Add(HlmagcH, OlcDbTypc.VarBinary, picturcN()dc.NodcVjiluc.Lcngth).Valuc = piclurcNodc.NodcValuc;odCommand.ExucutuNonQuuryO； odcCoiuicction.CloscO； rctiirn true；catch (Exception err)|errinft) = crr.Mcssage;return false;2.更新一行的数据（与插入类似） view plaincopy to clipboardprint?

40、更新一行数据public static bool UpdatcR<)w(string mdbPath, string tablcNamc,Node keyNode,Array List insert A rray.Pic tnrcX()dc picturuNodu, ref string crriiif()try1、建立连接 string strConn=，Pn)vidcr=Micn)softJcl.()LEr)B.4.0;Data Sourcc=n + mdbpath + "Jul ()LEDB：r)atabasc Passr()rd=haorcnM;OlcDbCoinicc

41、rion odcConncction = new ()lcr)bC<Hiiiccti()n(strC<)nn);2、打开连接<)dcC()nncc ti()n. ()pcii();int size = insert Array.Count;string str =for (int i = 0; i < size； i+) Node node = new NodcQ;node = (N()dc) inscr t A rray 0 ；string v = nodc.NodcValuc.ToStringQ;v = r)calString(v);str += nodc.Nodc

42、Typc + "= if(v=与str += "null" +elsestr += ,MH + v+str = str.TrimEndf,1);if (pictureNode.NodcValue != null) str += + picturcNodc.NodcTypc;str += H = ImagcH;string sql = "update ” + tablcNamc + " set H + str +° where H + kcyNodc.NodcTypc + ” = “ + kcyNodc.NodcValuc + H,H;

43、OlcDbCommand odCommand = new ()kDbCommand(sql,(>dcC<nuiccti()n); if (pictureNode.NodcValue != null) odCommand.Parameters.Add(Hmagc, ()lcDbTypc.VarBinar picturcNodc.N()dcViiluc.Lcngth).Valuc = pictureNode.NodcValue； odCommand.ExccutcNoiiQucryO；odcConncction.CloscQ;return true；)catch (Exception

44、err)errinfo = crr.Mcssagc; return false;更新一行数据public static bool UpdatcR()w(string mdbpath, string tablcNamc,Node kcyNodc,ArrayList inscrtArraPicturcN()dc pictureNode, ref string errinfo)1、建立连接string strConn="Pr()vidukMicrosoft.Jut.()LEDB.4.0;Data Sourcc=H + mdbpath + M;Jct OLEr)B：r)atabasc Pas

45、sr<)rd=ha()rcnM;OlcDbCoiiiiccrion odeConncetion = ()lcDbC()nnccti()n(stTC()nn);2、打开连接odcCoinicction.OpcnQ;int size = inscrtArray.Count; string str =for (int i = 0; i < size; i+)Node node = new NodcQ;node = (Nodc)inscrtArrayO；string v = nodc.NodcValuc.ToStringQ;v = DcalString(v);str += nodc.Nod

46、cTypc + "="；str += "null" + elsestr +=+ v +str = str.TrimEnd();if (piclurcNodc.NodcValuc != null)str += + picturcNodc.NodcTypc; str += H = ImagcM;string sql = "update " + tablcNamc + “ set ” + sir +H where H + kcyNodc.NodcTypc + " = " + kcyNodc.NodcValuc +OlcD

47、bCommaiid odCommaiid = new OluDbCommand(sql, <)dcC<nuiccti()n); if (picturcNodc.NodcValuc != null) <)dC(>mmiUid.Paramclcrs.Add(Hlmagc, OlcDbTypc.VarBinary, pictureNodc.NodeValue.Lcngth).VaIuc = picturcNodc.NodcValuc;odCommuid.ExccutcNonQucryO；odcConncction.CloscQ;rctiini true；catch (Exce

48、ption err)errinfi) = crr.Mcssagc;return false;3.插入图片数据.view plaincopy to clipboardprint?插入图片数据public static bool lnscrtPiclurcT()Mr)B(string mdbPath, string tablcNamc,Node keyNode, PictureNode piclurcNodc,rcf string errinft)(try .1、建立连接string strConn=HPn)vidcr=Micros<)ft.Jcl.()LEr)B.4.0；r)ata Sou

49、rcc=H + mdbPath + "Jul C)LEDB：r)atabasc Passw)rd=haorun"()lcDbC<)iiiiccri()n odcConncction = new ()lcr)bC(HBiccti()n(strC()nn);2、打开连接odcConiicction.OpcnQ;string sql = Hupdatc " + tablcNamc + H set” + picturcNodc.NodcTypc + nImagt where H + kcyNodc.NodcTypc + "=" + HH,+kcy

50、N()dc.N()dcValuc+HM,;OlcDbCommand comm = new Olcr)bC()mmaiid(sql,()dcC()nncctic>n);bytcQ pic = picturcXodc.NodcValuc;c()mm.Paramctcrs.Add(HlmagtM, OlcDbTypc.VarBinar pic.Lcngth).Valuc = pic;comm. Execu teNonQ uu ry 0;odcConncction.CloscQ;return true；catch (Exception err) (errinfo = crr.Mcssagc;re

51、turn false; 插入图片数据public static bool lnscrtPicturcT()Mr)B(string mdbPath, string tablcNamc,Nodc keyNode,PictureNode picturcNodc,rcf string errinfo) try 1、建立连接 string strConn=，Pnnridcr=Micn)s()ftJct.()LEr)B.4.0;Data Sourcc=H + mdbpath + M;ct ()LEr)B:Databasc Passw()rd=haorun"OlcDbCoiiiicction od

52、cConncction = ()lcDbC()nnccti()n(strC()nn);2、打开连接odcCoiuicction.OpcnQ;string sql = update " + tablcNamc + M set" + picturcNodc.NodcTypc + + HImagc where ° + kcyNodc.NodcTypc +MHt+kcyN()dc.NodcValuc+”小;OlcDbCommand comm = new ()kDbCommand(sql, odcConnucticn);bytcQ pic = picturcNodc.Nod

53、cValuc;comm.Parameters.Add(1,Jmagcl OlcDbTypc.VarBinar pic.Length).Value = pic;comm.ExccutcXonQucnQ;odcC<)nnccti()n.Closc0;return true； catch (Exception err) (errinfi) = crr.Mcssagc;return false; 4.修改mdb的一条数据.view plaincopy to clipboardprint? 修改mdb的一条数据public static bool UpdatuMDBNodu( string tab

54、lcNamc, Node keyNode,Node saveNode, ref string errinft)try ( /l'建立连接 string strComi="Pr()vidur=MicrosoflJut.()LEDB.4.0;Data Sourcc=M + mdbpath + HJct OLEr)B：r)atabasc Passr<)rd=haorcnM;OlcDbCoiiiiccrion odcCoinicction = new ()lcr)bC()nnccti()n(strC()nn);2、打开连接odcC<)iiiiccti()n.()pcn()

55、;string sql = update "+ tablcNamc+n set H + saveNode.NodeType + " = "' + savcNodc.NodcValuc+where '' + kcyNodc.NodcTypc + " = '' + MH, 4- kcyNodc.NodcValuc + ,HH; OluDbCommand comm = new()luDbCommand(sql, odcConncctioii);c()mm.ExccutcN()nQucrrQ;odcCoiuicction.

56、CloscO； return true;catch (Exception err) errinfi) = crr.Mcssage; return false; 修改mdb的一条数据public static bool UpdatcMDBNodc( string lablcNamc, Node keyNode, Node savcNcxic, ref string errinfo) try | 1、建立连接 string strConn=HPnnridcr=Micr<)soft.Jct.()LEr)B.4.0；r)ata Sourcc=H + mdbpath + M;Jct OLEr)B：

57、r>atabasc Password=hacrun"OlcDbCoiiiiccrion odcConncction = ()lcDbC()nnccti<)n(strC()nn);2、打开连接 odcConncc tion.OpciiQ;strings. = update "+ tablcName+n set" + saveNode.NodeTypc + " = + savcNodc.NodcValuc+m where " + kcyNodc.NodcTypc + ” = “ + kcyNodc.NodcValuc +，”，OlcDbCommaiid comm = new()kDbC