.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);

                System.DateTime time = System.DateTime.Now;
                Users.Class1 u = new Users.Class1();
                int uid = u.getUserID(username);
                string sql = "insert into paper_table(state_ID,firstauthor_ID,PAPER_NAME,FIRSTWRITER_NAME,secondwriter_name,thirdwriter_name,submit_time,paper_text)";
                sql = sql + "VALUES(:a,:b,:c,:d,:e,:f,:g,:h)";
                OracleCommand cmd = new OracleCommand(sql, conn);
                
                conn.Open();
                cmd.Parameters.Add("a", System.Data.OracleClient.OracleType.Number).Value =1;
                cmd.Parameters.Add("b", System.Data.OracleClient.OracleType.Number).Value =uid;
                cmd.Parameters.Add("c", System.Data.OracleClient.OracleType.VarChar).Value = title;
                cmd.Parameters.Add("d", System.Data.OracleClient.OracleType.VarChar).Value = author;
                cmd.Parameters.Add("e", System.Data.OracleClient.OracleType.VarChar).Value = author2;
                cmd.Parameters.Add("f", System.Data.OracleClient.OracleType.VarChar).Value = author3;
                cmd.Parameters.Add("g", System.Data.OracleClient.OracleType.DateTime).Value = time;// "to_date('" + time + "','yyyy-mm-dd HH24:MI:SS')";
                cmd.Parameters.Add("h", System.Data.OracleClient.OracleType.Blob,b.Length).Value = b;
                cmd.ExecuteNonQuery();
                conn.Close();
            Papers.papers p = new papers();
             p.insert_modified_table(title,uid);
             p.insert_into_submit_state_table(title, uid);
            }
            catch
            { 
           
            }
           
        }

 

 

读取文本

 

 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();
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    if (dr["paper_text"].ToString() == "")//如果文章内容为空 不能转二进制
                        str = "";
                    else
                    {
                        byte[] b = (byte[])dr["paper_text"];
                        str = System.Text.Encoding.Unicode.GetString(b);
                    }
                }
                conn.Close();

            }