c#操作Access[下]

来源：互联网发布：linux 内核编译安装编辑：程序博客网时间：2024/06/14 21:38

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

    //普通的节点 
    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; 
            } 
        }

http://blog.csdn.net/gisfarmer/