介绍之前先介绍一个结构体。因为以下函数都要用到这个结构体。
- //普通的节点
- 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下测试通过。希望能给大家带来一些帮助。