c#操作Access[下]

話接上回。

介紹之前先介紹一個結構體。因為以下函數都要用到這個結構體。

//普通的節點<br /> public struct Node<br /> {<br /> private string nodeType;<br /> public string NodeType//表的欄位名<br /> {<br /> set { nodeType = value; }<br /> get { return nodeType; }<br /> }</p><p> private string nodeValue;<br /> public string NodeValue//具體的值<br /> {<br /> set { nodeValue = value; }<br /> get { return nodeValue; }<br /> }<br /> }</p><p> //照片節點<br /> public struct PictureNode<br /> {<br /> private string nodeType;<br /> public string NodeType//照片的列名<br /> {<br /> set { nodeType = value; }<br /> get { return nodeType; }<br /> }</p><p> private byte[] nodeValue;<br /> public byte[] NodeValue//照片的值，注意類型<br /> {<br /> set { nodeValue = value; }<br /> get { return nodeValue; }<br /> }<br /> }

具體就用不著多加描述了吧！繼續看問題點。

1.向table中插入資料（按行插入，如果需要插入多條請自己組織這個函數就ok了），其中的 insertArray儲存的是一系列Node，pictureNode是PictureNode。
//插入資料<br /> public static bool InsertRow( string mdbPath, string tableName, ArrayList insertArray,<br /> PictureNode pictureNode, ref string errinfo)<br /> {<br /> try<br /> {<br /> //1、建立串連<br /> string strConn<br /> = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren";<br /> OleDbConnection odcConnection = new OleDbConnection(strConn);<br /> //2、開啟串連<br /> odcConnection.Open();</p><p> string str_col = "";<br /> int size_col = insertArray.Count;<br /> for (int i = 0; i < size_col; i++)<br /> {<br /> Node vipNode = new Node();<br /> vipNode = (Node)insertArray[i];<br /> str_col += vipNode.NodeType + ",";<br /> }<br /> str_col = str_col.TrimEnd(',');</p><p> int size_row = insertArray.Count;<br /> string str_row = "";<br /> for (int i = 0; i < size_row; i++)<br /> {<br /> Node vipNode = new Node();<br /> vipNode = (Node)insertArray[i];<br /> string v = vipNode.NodeValue.ToString();<br /> v = DealString(v);<br /> if (v == "")<br /> {<br /> str_row += "null" + ',';<br /> }<br /> else<br /> {<br /> str_row += "'" + v + "'" + ',';<br /> }<br /> }<br /> str_row = str_row.TrimEnd(',');<br /> if (pictureNode　!= null && pictureNode.NodeValue != null)<br /> {<br /> str_col += ',' + pictureNode.NodeType;<br /> str_row += ",@Image";<br /> }<br /> string sql = "insert into " + tableName + @" (" + str_col + ") values" + @"(" + str_row + ")";<br /> OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);<br /> if (pictureNode != null　&& pictureNode.NodeValue != null)<br /> {<br /> odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;<br /> }<br /> odCommand.ExecuteNonQuery();<br /> odcConnection.Close();<br /> return true;<br /> }<br /> catch (Exception err)<br /> {<br /> errinfo = err.Message;<br /> return false;<br /> }<br /> }

2.更新一行的資料（與插入類似）

//更新一行資料<br /> public static bool UpdateRow(string mdbPath, string tableName,<br /> Node keyNode,ArrayList insertArray,PictureNode pictureNode, ref string errinfo)<br /> {<br /> try<br /> {<br /> //1、建立串連<br /> string strConn<br /> = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren";<br /> OleDbConnection odcConnection = new OleDbConnection(strConn);<br /> //2、開啟串連<br /> odcConnection.Open();</p><p> int size = insertArray.Count;<br /> string str = "";<br /> for (int i = 0; i < size; i++)<br /> {<br /> Node node = new Node();<br /> node = (Node)insertArray[i];<br /> string v = node.NodeValue.ToString();<br /> v = DealString(v);<br /> str += node.NodeType + " = ";<br /> if (v == "")<br /> {<br /> str += "null" + ',';<br /> }<br /> else<br /> {<br /> str += "'" + v + "'" + ',';<br /> }</p><p> }<br /> str = str.TrimEnd(',');<br /> if (pictureNode.NodeValue != null)<br /> {<br /> str += ',' + pictureNode.NodeType;<br /> str += " = @Image";<br /> }<br /> string sql = "update " + tableName + " set " + str +<br /> " where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'";<br /> OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);<br /> if (pictureNode.NodeValue != null)<br /> {<br /> odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;<br /> }<br /> odCommand.ExecuteNonQuery();<br /> odcConnection.Close();<br /> return true;<br /> }<br /> catch (Exception err)<br /> {<br /> errinfo = err.Message;<br /> return false;<br /> }<br /> }

3.  插入圖片資料.

//插入圖片資料<br /> public static bool InsertPictureToMDB(string mdbPath, string tableName,Node keyNode,<br /> PictureNode pictureNode,ref string errinfo)<br /> {<br /> try<br /> {<br /> //1、建立串連<br /> string strConn<br /> = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren";<br /> OleDbConnection odcConnection = new OleDbConnection(strConn);<br /> //2、開啟串連<br /> odcConnection.Open();<br /> string sql = @"update " + tableName + " set " + pictureNode.NodeType + "="<br /> + "@Image where " + keyNode.NodeType + "=" + "'"+keyNode.NodeValue+"'";<br /> OleDbCommand comm = new OleDbCommand(sql, odcConnection);<br /> byte[] pic = pictureNode.NodeValue;<br /> comm.Parameters.Add("@Image", OleDbType.VarBinary, pic.Length).Value = pic;<br /> comm.ExecuteNonQuery();<br /> odcConnection.Close();<br /> return true;<br /> }<br /> catch (Exception err)<br /> {<br /> errinfo = err.Message;<br /> return false;<br /> }<br /> }

4.修改mdb的一條資料.

//修改mdb的一條資料<br /> public static bool UpdateMDBNode( string tableName, Node keyNode,<br /> Node saveNode, ref string errinfo)<br /> {<br /> try<br /> {<br /> //1、建立串連<br /> string strConn<br /> = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";Jet OLEDB:Database Password=haoren";<br /> OleDbConnection odcConnection = new OleDbConnection(strConn);<br /> //2、開啟串連<br /> odcConnection.Open();</p><p> string sql = @"update "+ tableName+" set " + saveNode.NodeType + " = '" + saveNode.NodeValue+<br /> "' where " + keyNode.NodeType + " = " + "'" + keyNode.NodeValue + "'";<br /> OleDbCommand comm = new OleDbCommand(sql, odcConnection);<br /> comm.ExecuteNonQuery();<br /> odcConnection.Close();<br /> return true;<br /> }<br /> catch (Exception err)<br /> {<br /> errinfo = err.Message;<br /> return false;<br /> }<br /> }

5.從mdb中獲得照片

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

到此以上描述問題，全部解決。這些程式碼都在net2005下測試通過。希望能給大家帶來一些協助。

補充一句：轉載的朋友請一定註明出處謝謝！半支煙阿傑 http://blog.csdn.net/gisfarmer/