.net下Oracle的Blob欄位存取(一)插入操作

--以對一篇文章的插入為例 操作Oracle的Blob欄位--

Oracle的Blob欄位是二進位存取,上傳前要把檔案轉成二進位,下載亦是把下載到的二進位轉換.最大可存取4GB的檔案,適合對大檔案或非文字檔的操作,以下就最近做的一個項目,把對Blob的存取寫出來供網友參考:

 

 1
 2        //新增文章------------
 3
 4        public void add_Papers(string username, string title, string author, string author2, string author3,string text)
 5        {
 6            
 7            try
 8            {
 9                byte[] b = System.Text.Encoding.Unicode.GetBytes(text);
10
11                System.DateTime time = System.DateTime.Now;
12                Users.Class1 u = new Users.Class1();
13                int uid = u.getUserID(username);
14                string sql = "insert into paper_table(state_ID,firstauthor_ID,PAPER_NAME,FIRSTWRITER_NAME,secondwriter_name,thirdwriter_name,submit_time,paper_text)";
15                sql = sql + "VALUES(:a,:b,:c,:d,:e,:f,:g,:h)";
16                OracleCommand cmd = new OracleCommand(sql, conn);
17                
18                conn.Open();
19                cmd.Parameters.Add("a", System.Data.OracleClient.OracleType.Number).Value =1;
20                cmd.Parameters.Add("b", System.Data.OracleClient.OracleType.Number).Value =uid;
21                cmd.Parameters.Add("c", System.Data.OracleClient.OracleType.VarChar).Value = title;
22                cmd.Parameters.Add("d", System.Data.OracleClient.OracleType.VarChar).Value = author;
23                cmd.Parameters.Add("e", System.Data.OracleClient.OracleType.VarChar).Value = author2;
24                cmd.Parameters.Add("f", System.Data.OracleClient.OracleType.VarChar).Value = author3;
25                cmd.Parameters.Add("g", System.Data.OracleClient.OracleType.DateTime).Value = time;// "to_date('" + time + "','yyyy-mm-dd HH24:MI:SS')";
26                cmd.Parameters.Add("h", System.Data.OracleClient.OracleType.Blob,b.Length).Value = b;
27                cmd.ExecuteNonQuery();
28                conn.Close();
29            Papers.papers p = new papers();
30             p.insert_modified_table(title,uid);
31             p.insert_into_submit_state_table(title, uid);
32            }
33            catch
34            { 
35           
36            }
37           
38        }

 

 

讀取文本

 

 1   //得到文章內容
 2        public string get_paper_text(string id,string table)
 3        {string sql;
 4            string str="";
 5            if (table == "paper_table")
 6            {
 7                sql = "select paper_text from paper_table where id='" + id + "'";
 8                OracleCommand cmd = new OracleCommand(sql, conn);
 9                conn.Open();
10                OracleDataReader dr = cmd.ExecuteReader();
11                while (dr.Read())
12                {
13                    if (dr["paper_text"].ToString() == "")//如果文章內容為空白 不能轉二進位
14                        str = "";
15                    else
16                    {
17                        byte[] b = (byte[])dr["paper_text"];
18                        str = System.Text.Encoding.Unicode.GetString(b);
19                    }
20                }
21                conn.Close();
22
23            }