c#操作Access[下]-CSDN博客

本文链接：https://blog.csdn.net/gisfarmer/article/details/3701136

本文介绍了使用C#操作Access数据库的方法，包括插入、更新、删除数据，以及处理图片数据。通过定义Node和PictureNode结构体，实现了向数据库中按行插入数据、更新一行数据、插入图片数据和从数据库中获取照片等功能。代码已在.NET 2005环境下测试通过。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

话接上回。

介绍之前先介绍一个结构体。因为以下函数都要用到这个结构体。

//普通的节点 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。
//插入数据 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)insertArray[i]; 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)insertArray[i]; 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; } }

2.更新一行的数据（与插入类似）

//更新一行数据 public static bool UpdateRow(string mdbPath, string tableName, Node keyNode,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(); int size = insertArray.Count; string str = ""; for (int i = 0; i < size; i++) { Node node = new Node(); node = (Node)insertArray[i]; string v = node.NodeValue.ToString(); v = DealString(v); str += node.NodeType + " = "; if (v == "") { str += "null" + ','; } else { str += "'" + v + "'" + ','; } } str = str.TrimEnd(','); if (pictureNode.NodeValue != null) { str += ',' + pictureNode.NodeType; str += " = @Image"; } string sql = "update " + tableName + " set " + str + " where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'"; OleDbCommand odCommand = new OleDbCommand(sql, odcConnection); if (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; } }

3. 插入图片数据.

//插入图片数据 public static bool InsertPictureToMDB(string mdbPath, string tableName,Node keyNode, 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 sql = @"update " + tableName + " set " + pictureNode.NodeType + "=" + "@Image where " + keyNode.NodeType + "=" + "'"+keyNode.NodeValue+"'"; OleDbCommand comm = new OleDbCommand(sql, odcConnection); byte[] pic = pictureNode.NodeValue; comm.Parameters.Add("@Image", OleDbType.VarBinary, pic.Length).Value = pic; comm.ExecuteNonQuery(); odcConnection.Close(); return true; } catch (Exception err) { errinfo = err.Message; return false; } }

4.修改mdb的一条数据.

//修改mdb的一条数据 public static bool UpdateMDBNode( string tableName, Node keyNode, Node saveNode, 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 sql = @"update "+ tableName+" set " + saveNode.NodeType + " = '" + saveNode.NodeValue+ "' where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'"; OleDbCommand comm = new OleDbCommand(sql, odcConnection); comm.ExecuteNonQuery(); odcConnection.Close(); return true; } catch (Exception err) { errinfo = err.Message; return false; } }

5.从mdb中获得照片

//从mdb中获得照片 public static byte[] GetImageFromMDB( string tableName, Node keyNode) { try { string sql = "Select 照片 From " + tableName + " member Where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'"; string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren"; OleDbConnection odcConnection = new OleDbConnection(strConn); //2、打开连接 odcConnection.Open(); OleDbCommand comm = new OleDbCommand(sql, odcConnection); OleDbDataReader sdr = comm.ExecuteReader(); sdr.Read(); byte[] pic = (byte[])sdr[0]; sdr.Close(); odcConnection.Close(); return pic; } catch { return null; } }

到此以上描述问题，全部解决。这些程序代码都在net2005下测试通过。希望能给大家带来一些帮助。

补充一句：转载的朋友请一定注明出处谢谢！半支烟阿杰 http://blog.csdn.net/gisfarmer/