c#操作access数据库编程1

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

2、usingSystem.Data.OleDb;usingSystem.Data;连接字符串：StringconnectionString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=product.mdb"建立连接:OleDbConnectionconnection=newOleDbConnection(connectionString);使用OleDbComman畿来执行Sql语句:1. OleDbCommandcmd=newOleDbCommand(sql,connection);connection.Open();cmd.

3、ExecuteNonQuery();创建mdb库，例程如下：需要注意的是：参数mdbPath是mdb的完整路径(不包含表的名称)。例如：D:Vest.mdbviewplaincopytoclipboardprint?/创建mdbpublicstaticboolCreateMDBDataBase(stringmdbPath)tryADOX.CatalogClasscat=newADOX.CatalogClass();cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"");c

4、at=null;returntrue;catch(returnfalse;/创建mdbpublicstaticboolCreateMDBDataBase(stringmdbPath)(try(ADOX.CatalogClasscat=newADOX.CatalogClass();cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"");cat=null;returntrue;catch(returnfalse;2. 创建具体的表，例程如下：通常一个mdb的可以包含n个表。下

5、面的程序主要是创建一个table。viewplaincopytoclipboardprint?/新建mdb的表/mdbHead是一个ArrayList，存储的是table表中的具体列名。publicstaticboolCreateMDBTable(stringmdbPath,stringtableName,ArrayListmdbHead)(try(ADOX.CatalogClasscat=newADOX.CatalogClass();stringsAccessConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="

6、+mdbPath;ADODB.Connectioncn=newADODB.Connection();cn.Open(sAccessConnection,null,null,-1);cat.ActiveConnection=cn;/新建一个表ADOX.TableClasstbl=newADOX.TableClass();tbl.ParentCatalog=cat;tbl.Name=tableName;intsize=mdbHead.Count;for(inti=0;i<size;i+)(/增加一个文本字段ADOX.ColumnClasscol2=newADOX.ColumnClass();

7、col2.ParentCatalog=cat;col2.Name=mdbHeadi.ToString();/列的名称col2.Properties"JetOLEDB:AllowZeroLength".Value=false;tbl.Columns.Append(col2,ADOX.DataTypeEnum.adVarWChar,500);cat.Tables.Append(tbl);/这句把表加入数据库(非常重要)tbl=null;cat=null;cn.Close();returntrue;catch(returnfalse;/新建mdb的表/mdbHead是一个Arra

8、yList，存储的是table表中的具体列名。publicstaticboolCreateMDBTable(stringmdbPath,stringtableName,ArrayListmdbHead)(try(ADOX.CatalogClasscat=newADOX.CatalogClass();stringsAccessConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath;ADODB.Connectioncn=newADODB.Connection();cn.Open(sAccessConne

9、ction,null,null,-1);cat.ActiveConnection=cn;/新建一个表ADOX.TableClasstbl=newADOX.TableClass();tbl.ParentCatalog=cat;tbl.Name=tableName;intsize=mdbHead.Count;for(inti=0;i<size;i+)(/增加一个文本字段ADOX.ColumnClasscol2=newADOX.ColumnClass();col2.ParentCatalog=cat;col2.Name=mdbHeadi.ToString();/col2.Properties&

10、quot;Jettbl.Columns.Append(col2,cat.Tables.Append(tbl);/tbl=null;列的名称OLEDB:AllowZeroLength".Value=false;ADOX.DataTypeEnum.adVarWChar,500);这句把表加入数据库（非常重要）cat=null;cn.Close();returntrue;catch(returnfalse;3. 读取mdb内容(完全读取)，例程如下：本例程返回的是一个DataTable,如需其他格式可以自行转换。viewplaincopytoclipboardprint?/读取mdb数据p

11、ublicstaticDataTableReadAllData(stringtableName,stringmdbPath,refboolsuccess)(DataTabledt=newDataTable();try(DataRowdr;/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(str

12、Conn);/2、打开连接odcConnection.Open();/建立SQ站询OleDbCommandodCommand=odcConnection.CreateCommand();/3、输入查询语句odCommand.CommandText="select*from"+tableName;/建立读取OleDbDataReaderodrReader=odCommand.ExecuteReader();/查询并显示数据intsize=odrReader.FieldCount;for(inti=0;i<size;i+)(DataColumndc;dc=newDataC

13、olumn(odrReader.GetName(i);dt.Columns.Add(dc);while(odrReader.Read()(dr=dt.NewRow();for(inti=0;i<size;i+)(drodrReader.GetName(i)=odrReaderodrReader.GetName(i).ToString();dt.Rows.Add(dr);/关闭连接odrReader.Close();odcConnection.Close();success=true;returndt;catch(success=false;returndt;/读取mdb数据publics

14、taticDataTableReadAllData(stringtableName,stringmdbPath,refboolsuccess)(DataTabledt=newDataTable();try(DataRowdr;/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);

15、/2、打开连接odcConnection.Open();/建立SQ站询OleDbCommandodCommand=odcConnection.CreateCommand();/3、输入查询语句odCommand.CommandText="select*from"+tableName;/建立读取OleDbDataReaderodrReader=odCommand.ExecuteReader();/查询并显示数据intsize=odrReader.FieldCount;for(inti=0;i<size;i+)(DataColumndc;dc=newDataColumn(

16、odrReader.GetName(i);dt.Columns.Add(dc);while(odrReader.Read()dr=dt.NewRow();for(inti=0;i<size;i+)(drodrReader.GetName(i)=odrReaderodrReader.GetName(i).ToString();dt.Rows.Add(dr);/关闭连接odrReader.Close();odcConnection.Close();success=true;returndt;catch(success=false;returndt;4. 读取mdb内容(按列读取)，例程如下：

17、columns数组存储的是你要查询的列名称(必须确保mdb表中存在你要的列)viewplaincopytoclipboardprint?/读取mdb数据publicstaticDataTableReadDataByColumns(stringmdbPaht,stringtableName,stringcolumns,refboolsuccess)(DataTabledt=newDataTable();try(DataRowdr;/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPat

18、h+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();/建立SQ站询OleDbCommandodCommand=odcConnection.CreateCommand();/3、输入查询语句stringstrColumn=""for(inti=0;i<columns.Length;i+)(strColumn+=columnsi.ToString()+”,”;str

19、Column=strColumn.TrimEnd(',');odCommand.CommandText="select"+strColumn+"from"+tableName;/建立读取OleDbDataReaderodrReader=odCommand.ExecuteReader();/查询并显示数据intsize=odrReader.FieldCount;for(inti=0;i<size;i+)(DataColumndc;dc=newDataColumn(odrReader.GetName(i);dt.Columns.Add(

20、dc);while(odrReader.Read()(dr=dt.NewRow();for(inti=0;i<size;i+)(drodrReader.GetName(i)=odrReaderodrReader.GetName(i).ToString();dt.Rows.Add(dr);/关闭连接odrReader.Close();odcConnection.Close();success=true;returndt;catch(success=false;returndt;/读取mdb数据publicstaticDataTableReadDataByColumns(stringmdbP

21、aht,stringtableName,stringcolumns,refboolsuccess)(DataTabledt=newDataTable();try(DataRowdr;/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.O

22、pen();/建立SQ站询OleDbCommandodCommand=odcConnection.CreateCommand();/3、输入查询语句stringstrColumn=""for(inti=0;i<columns.Length;i+)(strColumn+=columnsi.ToString()+","strColumn=strColumn.TrimEnd(',');odCommand.CommandText="select"+strColumn+"from"+tableName;

23、/建立读取OleDbDataReaderodrReader=odCommand.ExecuteReader();/查询并显示数据intsize=odrReader.FieldCount;for(inti=0;i<size;i+)(DataColumndc;dc=newDataColumn(odrReader.GetName(i);dt.Columns.Add(dc);while(odrReader.Read()(dr=dt.NewRow();for(inti=0;i<size;i+)(drodrReader.GetName(i)=odrReaderodrReader.GetName

24、(i).ToString();dt.Rows.Add(dr);/关闭连接odrReader.Close();odcConnection.Close();success=true;returndt;catch(success=false;returndt;c#操作Access(下)介绍之前先介绍一个结构体。因为以下函数都要用到这个结构体。viewplaincopytoclipboardprint?/普通的节点publicstructNode(privatestringnodeType;publicstringNodeType/表的字段名(set(nodeType=value;get(return

25、nodeType;privatestringnodeValue;publicstringNodeValue/具体的值(set(nodeValue=value;get(returnnodeValue;/照片节点publicstructPictureNode(privatestringnodeType;publicstringNodeType/照片的列名(set(nodeType=value;get(returnnodeType;privatebytenodeValue;publicbyteNodeValue/(set(nodeValue=value;get(returnnodeValue;/普通

26、的节点publicstructNode(privatestringnodeType;publicstringNodeType/(set(nodeType=value;get(returnnodeType;privatestringnodeValue;publicstringNodeValue/(set(nodeValue=value;get(returnnodeValue;/照片节点publicstructPictureNode(privatestringnodeType;publicstringNodeType/(set(nodeType=value;get(returnnodeType;p

27、rivatebytenodeValue;publicbyteNodeValue/(set(nodeValue=value;get(returnnodeValue;照片的值，注意类型表的字段名具体的值照片的列名照片的值，注意类型具体就用不着多加描述了吧！继续看问题点。1.向table中插入数据（按行插入，如果需要插入多条请自己组织这个函数就的insertArray存储的是一系列Node,pictureNode是PictureNode。viewplaincopytoclipboardprint?ok了），其中/插入数据publicstaticboolInsertRow(stringmdbPath,

28、stringtableName,ArrayListinsertArray,PictureNodepictureNode,refstringerrinfo)try/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();strin

29、gstr_col=""intsize_col=insertArray.Count;for(inti=0;i<size_col;i+)NodevipNode=newNode();vipNode=(Node)insertArrayi;str_col+=vipNode.NodeType+","str_col=str_col.TrimEnd(',');intsize_row=insertArray.Count;stringstr_row=""for(inti=0;i<size_row;i+)NodevipNode=

30、newNode();vipNode=(Node)insertArrayi;stringv=vipNode.NodeValue.ToString();v=DealString(v);if(v="")str_row+="null"+','elsestr_row+="'"+v+"'"+','str_row=str_row.TrimEnd(',');if(pictureNode!=null&&pictureNode.NodeValue!=nul

31、l)(str_col+=','+pictureNode.NodeType;str_row+=",Image”;stringsql="insertinto"+tableName+"("+str_col+")values"+"("+str_row+")"OleDbCommandodCommand=newOleDbCommand(sql,odcConnection);if(pictureNode!=null&&pictureNode.NodeValue!=nu

32、ll)(odCommand.Parameters.Add("Image",OleDbType.VarBinary,pictureNode.NodeValue.Length).Value=pictureNode.NodeValue;odCommand.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)(errinfo=err.Message;returnfalse;/插入数据publicstaticboolInsertRow(stringmdbPath,stringtableName,A

33、rrayListinsertArray,PictureNodepictureNode,refstringerrinfo)(try(/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();stringstr_col="

34、"intsize_col=insertArray.Count;for(inti=0;i<size_col;i+)(NodevipNode=newNode();vipNode=(Node)insertArrayi;str_col+=vipNode.NodeType+","str_col=str_col.TrimEnd(',');intsize_row=insertArray.Count;stringstr_row=""for(inti=0;i<size_row;i+)NodevipNode=newNode();vipN

35、ode=(Node)insertArrayi;stringv=vipNode.NodeValue.ToString();v=DealString(v);if(v="")str_row+="null"+','elsestr_row+=""'+v+""'+','str_row=str_row.TrimEnd(',');if(pictureNode!=null&&pictureNode.NodeValue!=null)str_col+=

36、9;,'+pictureNode.NodeType;str_row+=",Image”;stringsql="insertinto"+tableName+"("+str_col+")values+"("+str_row+")"OleDbCommandodCommand=newOleDbCommand(sql,odcConnection);if(pictureNode!=null&&pictureNode.NodeValue!=null)odCommand.Paramete

37、rs.Add("Image",OleDbType.VarBinary,pictureNode.NodeValue.Length).Value=pictureNode.NodeValue;odCommand.ExecuteNonQuery();odcConnection.Close();returntrue;errinfo=err.Message;returnfalse;catch(Exceptionerr)更新一行的数据(与插入类似)viewplaincopytoclipboardprint?/更新一行数据publicstaticboolUpdateRow(stringmd

38、bPath,stringtableName,NodekeyNode,ArrayListinsertArray,PictureNodepictureNode,refstringerrinfo)try/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren”;OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnectio

39、n.Open();intsize=insertArray.Count;stringstr=""for(inti=0;i<size;i+)Nodenode=newNode();node=(Node)insertArrayi;stringv=node.NodeValue.ToString();v=DealString(v);str+=node.NodeType+"="if(v="")str+="null"+','elsestr+="”'+v+"”'+',

40、'str=str.TrimEnd(',');if(pictureNode.NodeValue!=null)str+=','+pictureNode.NodeType;str+="=Image"stringsql="update"+tableName+"set"+str+nin.where"+keyNode.NodeType+"="+"'"+keyNode.NodeValue+OleDbCommandodCommand=newOleDbCo

41、mmand(sql,odcConnection);if(pictureNode.NodeValue!=null)(odCommand.Parameters.Add("Image”,OleDbType.VarBinary,pictureNode.NodeValue.Length).Value=pictureNode.NodeValue;odCommand.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)(errinfo=err.Message;returnfalse;/更新一行数据publics

42、taticboolUpdateRow(stringmdbPath,stringtableName,NodekeyNode,ArrayListinsertArray,PictureNodepictureNode,refstringerrinfo)(try(/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnec

43、tion(strConn);/2、打开连接odcConnection.Open();intsize=insertArray.Count;stringstr=""for(inti=0;i<size;i+)(Nodenode=newNode();node=(Node)insertArrayi;stringv=node.NodeValue.ToString();v=DealString(v);str+=node.NodeType+"="if(v="")(str+="null"+','elsestr+

44、="”'+v+"”'+','；str=str.TrimEnd(',');if(pictureNode.NodeValue!=null)(str+=','+pictureNode.NodeType;str+="=Image”;stringsql="update"+tableName+"set"+str+"where"+keyNode.NodeType+"="+""'+keyNode.NodeVal

45、ue+;OleDbCommandodCommand=newOleDbCommand(sql,odcConnection);if(pictureNode.NodeValue!=null)(odCommand.Parameters.Add("Image",OleDbType.VarBinary,pictureNode.NodeValue.Length).Value=pictureNode.NodeValue;odCommand.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)(errin

46、fo=err.Message;returnfalse;2. 插入图片数据.viewplaincopytoclipboardprint?/插入图片数据publicstaticboolInsertPictureToMDB(stringmdbPath,stringtableName,NodekeyNode,PictureNodepictureNode,refstringerrinfo)(try(/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:Dat

47、abasePassword=haoren”;OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();stringsql="update"+tableName+"set"+pictureNode.NodeType+"="+"Imagewhere"+keyNode.NodeType+"="+"'"+keyNode.NodeValue+"'&q

48、uot;OleDbCommandcomm=newOleDbCommand(sql,odcConnection);bytepic=pictureNode.NodeValue;comm.Parameters.Add("Image”,OleDbType.VarBinary,pic.Length).Value=pic;comm.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)errinfo=err.Message;returnfalse;/插入图片数据publicstaticboolInsertPic

49、tureToMDB(stringmdbPath,stringtableName,NodekeyNode,PictureNodepictureNode,refstringerrinfo)try/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren”;OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.O

50、pen();stringsql="update"+tableName+"set"+pictureNode.NodeType+"="+"Imagewhere"+keyNode.NodeType+"="+"”'+keyNode.NodeValue+"”'OleDbCommandcomm=newOleDbCommand(sql,odcConnection);bytepic=pictureNode.NodeValue;comm.Parameters.Add("

51、;Image",OleDbType.VarBinary,pic.Length).Value=pic;comm.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)(errinfo=err.Message;returnfalse;3. 修改mdb的一条数据.viewplaincopytoclipboardprint?/修改mdb的一条数据publicstaticboolUpdateMDBNode(stringtableName,NodekeyNode,NodesaveNode,refstringer

52、rinfo)(try(/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren”;OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();stringsql="update"+tableName+"set"+saveNode.NodeType+"

53、;='"+saveNode.NodeValue+"'where"+keyNode.NodeType+"="+""'+keyNode.NodeValue+""'OleDbCommandcomm=newOleDbCommand(sql,odcConnection);comm.ExecuteNonQuery();odcConnection.Close();returntrue;catch(Exceptionerr)(errinfo=err.Message;returnfalse;

54、/修改mdb的一条数据publicstaticboolUpdateMDBNode(stringtableName,NodekeyNode,NodesaveNode,refstringerrinfo)(try/1、建立连接stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odc

55、Connection.Open();stringsql="update"+tableName+"set"+saveNode.NodeType+"='"+saveNode.NodeValue+"'where"+keyNode.NodeType+"="+""'+keyNode.NodeValue+;OleDbCommandcomm=newOleDbCommand(sql,odcConnection);comm.ExecuteNonQuery();odcCo

56、nnection.Close();returntrue;catch(Exceptionerr)(errinfo=err.Message;returnfalse;从mdb中获得照片viewplaincopytoclipboardprint?/从mdb中获得照片publicstaticbyteGetImageFromMDB(stringtableName,NodekeyNode)(try(stringsql="Select照片From"+tableName+"memberWhere"+keyNode.NodeType+"="+"

57、”'+keyNode.NodeValue+"”'stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren"OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();OleDbCommandcomm=newOleDbCommand(sql,odcConnection);Ole

58、DbDataReadersdr=comm.ExecuteReader();sdr.Read();bytepic=(byte)sdr0;sdr.Close();odcConnection.Close();returnpic;catchreturnnull;/从mdb中获得照片publicstaticbyteGetImageFromMDB(stringtableName,NodekeyNode)trystringsql="Select照片From"+tableName+"memberWhere"+keyNode.NodeType+"="+

59、""'+keyNode.NodeValue+""'stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+mdbPath+"JetOLEDB:DatabasePassword=haoren”;OleDbConnectionodcConnection=newOleDbConnection(strConn);/2、打开连接odcConnection.Open();OleDbCommandcomm=newOleDbCommand(sql,odcCon

60、nection);OleDbDataReadersdr=comm.ExecuteReader();sdr.Read();bytepic=(byte)sdr0;sdr.Close();odcConnection.Close();returnpic;catchreturnnull;到此以上描述问题，全部解决。这些程序代码都在net2005下测试通过。datagridview的增删改查usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;u

61、singSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;namespacedataGridViewUsepublicpartialclassForm1:FormprivateOleDbConnectioncon=null;privateOleDbCommandcom=null;privateOleDbDataAdapteradapter=null;privateOleDbDataReaderdr=null;privatestrings;privatestringsqlstring="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=E:/MyDocuments/VisualStudio2005/Projects/dataGridViewUse/dataGridViewUse/表.mdb"privateSy