c#操作access数据库编程1.doc

精品文档c#操作Access（上）看到CSDN上不断兄弟姐妹提问关于c#操作Access的问题，于是本人利用闲暇将c#操作Access的方法加以总结，主要解决的问题有：创建mdb创建table读取table内容查询table中的内容向table中插入数据删除table中的记录向table中插入照片读取table中的照片等。另：本人水平有限，不当之处还请斧正。废话少说，开始正题。文介绍C#访问操作Access数据库的基础知识，并提供一个相关的例程。C#的ADO.NET还不能通过编程方式创建全新的ACCESS(MDB)数据库，所以还只能使用ADOX这个来自COM的链接库来操作。主要知识点如下：using System.Data.OleDb;using System.Data;连接字符串：String connectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb;建立连接：OleDbConnection connection = new OleDbConnection(connectionString);使用OleDbCommand类来执行Sql语句：OleDbCommand cmd = new OleDbCommand(sql, connection);connection.Open();cmd.ExecuteNonQuery();1.创建mdb库，例程如下：需要注意的是：参数mdbPath是mdb的完整路径（不包含表的名称）。例如：D:test.mdbview plaincopy to clipboardprint?/创建mdb public static bool CreateMDBDataBase(string mdbPath) try ADOX.CatalogClass cat = new ADOX.CatalogClass(); cat.Create(Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;); cat = null; return true; catch return false; /创建mdb public static bool CreateMDBDataBase(string mdbPath) try ADOX.CatalogClass cat = new ADOX.CatalogClass(); cat.Create(Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;); cat = null; return true; catch return false; 2.创建具体的表，例程如下：通常一个mdb的可以包含n个表。下面的程序主要是创建一个table。view plaincopy to clipboardprint?/新建mdb的表 /mdbHead是一个ArrayList，存储的是table表中的具体列名。 public static bool CreateMDBTable(string mdbPath,string tableName, ArrayList mdbHead) try ADOX.CatalogClass cat = new ADOX.CatalogClass(); string sAccessConnection = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath; ADODB.Connection cn = new ADODB.Connection(); cn.Open(sAccessConnection, null, null, -1); cat.ActiveConnection = cn; /新建一个表 ADOX.TableClass tbl = new ADOX.TableClass(); tbl.ParentCatalog = cat; tbl.Name = tableName; int size = mdbHead.Count; for (int i = 0; i size; i+) /增加一个文本字段 ADOX.ColumnClass col2 = new ADOX.ColumnClass(); col2.ParentCatalog = cat; col2.Name = mdbHeadi.ToString();/列的名称 col2.PropertiesJet OLEDB:Allow Zero Length.Value = false; tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500); cat.Tables.Append(tbl); /这句把表加入数据库(非常重要) tbl = null; cat = null; cn.Close(); return true; catch return false; /新建mdb的表 /mdbHead是一个ArrayList，存储的是table表中的具体列名。 public static bool CreateMDBTable(string mdbPath,string tableName, ArrayList mdbHead) try ADOX.CatalogClass cat = new ADOX.CatalogClass(); string sAccessConnection = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath; ADODB.Connection cn = new ADODB.Connection(); cn.Open(sAccessConnection, null, null, -1); cat.ActiveConnection = cn; /新建一个表 ADOX.TableClass tbl = new ADOX.TableClass(); tbl.ParentCatalog = cat; tbl.Name = tableName; int size = mdbHead.Count; for (int i = 0; i size; i+) /增加一个文本字段 ADOX.ColumnClass col2 = new ADOX.ColumnClass(); col2.ParentCatalog = cat; col2.Name = mdbHeadi.ToString();/列的名称 col2.PropertiesJet OLEDB:Allow Zero Length.Value = false; tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500); cat.Tables.Append(tbl); /这句把表加入数据库(非常重要) tbl = null; cat = null; cn.Close(); return true; catch return false; 3.读取mdb内容（完全读取），例程如下：本例程返回的是一个DataTable，如需其他格式可以自行转换。view plaincopy to clipboardprint?/ 读取mdb数据 public static DataTable ReadAllData(string tableName, string mdbPath,ref bool success) DataTable dt = new DataTable(); try DataRow dr; /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); /建立SQL查询 OleDbCommand odCommand = odcConnection.CreateCommand(); /3、输入查询语句 odCommand.CommandText = select * from + tableName; /建立读取 OleDbDataReader odrReader = odCommand.ExecuteReader(); /查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i size; i+) DataColumn dc; dc = new DataColumn(odrReader.GetName(i); dt.Columns.Add(dc); while (odrReader.Read() dr = dt.NewRow(); for (int i = 0; i size; i+) drodrReader.GetName(i) = odrReaderodrReader.GetName(i).ToString(); dt.Rows.Add(dr); /关闭连接 odrReader.Close(); odcConnection.Close(); success = true; return dt; catch success = false; return dt; / 读取mdb数据 public static DataTable ReadAllData(string tableName, string mdbPath,ref bool success) DataTable dt = new DataTable(); try DataRow dr; /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); /建立SQL查询 OleDbCommand odCommand = odcConnection.CreateCommand(); /3、输入查询语句 odCommand.CommandText = select * from + tableName; /建立读取 OleDbDataReader odrReader = odCommand.ExecuteReader(); /查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i size; i+) DataColumn dc; dc = new DataColumn(odrReader.GetName(i); dt.Columns.Add(dc); while (odrReader.Read() dr = dt.NewRow(); for (int i = 0; i size; i+) drodrReader.GetName(i) = odrReaderodrReader.GetName(i).ToString(); dt.Rows.Add(dr); /关闭连接 odrReader.Close(); odcConnection.Close(); success = true; return dt; catch success = false; return dt; 4.读取mdb内容（按列读取），例程如下：columns数组存储的是你要查询的列名称（必须确保mdb表中存在你要的列）view plaincopy to clipboardprint?/ 读取mdb数据 public static DataTable ReadDataByColumns(string mdbPaht,string tableName, string columns, ref bool success) DataTable dt = new DataTable(); try DataRow dr; /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); /建立SQL查询 OleDbCommand odCommand = odcConnection.CreateCommand(); /3、输入查询语句 string strColumn = ; for (int i = 0; i columns.Length; i+) strColumn += columnsi.ToString() + ,; strColumn = strColumn.TrimEnd(,); odCommand.CommandText = select +strColumn+ from + tableName; /建立读取 OleDbDataReader odrReader = odCommand.ExecuteReader(); /查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i size; i+) DataColumn dc; dc = new DataColumn(odrReader.GetName(i); dt.Columns.Add(dc); while (odrReader.Read() dr = dt.NewRow(); for (int i = 0; i size; i+) drodrReader.GetName(i) = odrReaderodrReader.GetName(i).ToString(); dt.Rows.Add(dr); /关闭连接 odrReader.Close(); odcConnection.Close(); success = true; return dt; catch success = false; return dt; / 读取mdb数据 public static DataTable ReadDataByColumns(string mdbPaht,string tableName, string columns, ref bool success) DataTable dt = new DataTable(); try DataRow dr; /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); /建立SQL查询 OleDbCommand odCommand = odcConnection.CreateCommand(); /3、输入查询语句 string strColumn = ; for (int i = 0; i columns.Length; i+) strColumn += columnsi.ToString() + ,; strColumn = strColumn.TrimEnd(,); odCommand.CommandText = select +strColumn+ from + tableName; /建立读取 OleDbDataReader odrReader = odCommand.ExecuteReader(); /查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i size; i+) DataColumn dc; dc = new DataColumn(odrReader.GetName(i); dt.Columns.Add(dc); while (odrReader.Read() dr = dt.NewRow(); for (int i = 0; i size; i+) drodrReader.GetName(i) = odrReaderodrReader.GetName(i).ToString(); dt.Rows.Add(dr); /关闭连接 odrReader.Close(); odcConnection.Close(); success = true; return dt; catch success = false; return dt; c#操作Access（下）介绍之前先介绍一个结构体。因为以下函数都要用到这个结构体。view plaincopy to clipboardprint?/普通的节点 public struct Node private string nodeType; public string NodeType/表的字段名 set nodeType = value; get return nodeType; private string nodeValue; public string NodeValue/具体的值 set nodeValue = value; get return nodeValue; /照片节点 public struct PictureNode private string nodeType; public string NodeType/照片的列名 set nodeType = value; get return nodeType; private byte nodeValue; public byte NodeValue/照片的值，注意类型 set nodeValue = value; get return nodeValue; /普通的节点 public struct Node private string nodeType; public string NodeType/表的字段名 set nodeType = value; get return nodeType; private string nodeValue; public string NodeValue/具体的值 set nodeValue = value; get return nodeValue; /照片节点 public struct PictureNode private string nodeType; public string NodeType/照片的列名 set nodeType = value; get return nodeType; private byte nodeValue; public byte NodeValue/照片的值，注意类型 set nodeValue = value; get return nodeValue; 具体就用不着多加描述了吧！继续看问题点。1.向table中插入数据（按行插入，如果需要插入多条请自己组织这个函数就ok了），其中的 insertArray存储的是一系列Node，pictureNode是PictureNode。view plaincopy to clipboardprint?/插入数据 public static bool InsertRow( string mdbPath, string tableName, ArrayList insertArray, PictureNode pictureNode, ref string errinfo) try /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); string str_col = ; int size_col = insertArray.Count; for (int i = 0; i size_col; i+) Node vipNode = new Node(); vipNode = (Node)insertArrayi; str_col += vipNode.NodeType + ,; str_col = str_col.TrimEnd(,); int size_row = insertArray.Count; string str_row = ; for (int i = 0; i size_row; i+) Node vipNode = new Node(); vipNode = (Node)insertArrayi; string v = vipNode.NodeValue.ToString(); v = DealString(v); if (v = ) str_row += null + ,; else str_row += + v + + ,; str_row = str_row.TrimEnd(,); if (pictureNode!= null & pictureNode.NodeValue != null) str_col += , + pictureNode.NodeType; str_row += ,Image; string sql = insert into + tableName + ( + str_col + ) values + ( + str_row + ); OleDbCommand odCommand = new OleDbCommand(sql, odcConnection); if (pictureNode != null& pictureNode.NodeValue != null) odCommand.Parameters.Add(Image, OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue; odCommand.ExecuteNonQuery(); odcConnection.Close(); return true; catch (Exception err) errinfo = err.Message; return false; /插入数据 public static bool InsertRow( string mdbPath, string tableName, ArrayList insertArray, PictureNode pictureNode, ref string errinfo) try /1、建立连接 string strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + mdbPath + ;Jet OLEDB:Database Password=haoren; OleDbConnection odcConnection = new OleDbConnection(strConn); /2、打开连接 odcConnection.Open(); string str_col = ; int size_col = insertArray.Count; for (int i = 0; i size_col; i+) Node vipNode = new Node(); vipNode = (Node)insertArrayi; str_col += vipNode.NodeType + ,; str_col = str_col.TrimEnd(,); int size_row = insertArray.Count; string str_row = ; for (int i = 0; i size_row; i+) Node vipNode = new Node(); vipNode = (Node)insertArrayi; string v = vipNode.NodeValue.ToString(); v = DealString(v); if (v = ) str_row += null + ,; else str_row += + v + + ,; str_row = str_row.TrimEnd(,); if (pictureNode!= null & pictureNode.NodeValue != null) str_col += , + pictureNode.NodeType; str_row += ,Image; string sql = insert into + tableName + ( + str_col + ) values + ( + str_row + ); OleDbCommand odCommand = new OleDbCommand(sql, odcConnection); if (pictureNode