C# 长度超过40万的文本,保存到oracle clob字段中去

 protected void Page_Load(object sender, EventArgs e)
        {
            
if (this.Request.Files.Count == 0)
            {
                
return;
            }
            HttpPostedFile postedFile 
= this.Request.Files[0];
            
int ContentLength = postedFile.ContentLength;
            
if (ContentLength == 0)
                
return;

            
byte[] bytes = new byte[ContentLength];
            
            
//把上传的文件中的XML文本读出到string
            postedFile.InputStream.Read(bytes, 0, ContentLength);
            
string tempStr = System.Text.Encoding.Default.GetString(bytes);
            
            
//获取使用 Little-Endian 字节顺序的 UTF-16 格式的编码。 
            
//因为OracleLob Clob存储是以UTF-16的合格,如果不转换就会保存成乱码
            bytes = System.Text.Encoding.Unicode.GetBytes(tempStr);
            ContentLength 
= bytes.Length;


            
string connString = "Data Source=owen;User Id=userid;Password=password;Integrated Security=no;";
            System.Data.OracleClient.OracleConnection conn 
= new OracleConnection(connString);
            
try
            {
                conn.Open();
                OracleTransaction trans 
= conn.BeginTransaction();

                OracleCommand command 
= new OracleCommand();
                command.Transaction 
= trans;
                command.Connection 
= conn;
                command.CommandType 
= CommandType.Text;

                
//获取临时clob
                command.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :templob := xx; end;";
                command.Parameters.Add(
new OracleParameter("templob", OracleType.Clob)).Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();
                
                OracleLob tmpclob 
= (OracleLob)command.Parameters[0].Value;
                
//end 获取临时clob
                tmpclob.Write(bytes, 0, ContentLength);
                tmpclob.Position 
= 0;
                trans.Commit();


                command.Parameters.Clear();
                command.CommandText 
= "pro_test1";
                command.CommandType 
= CommandType.StoredProcedure;

                command.Parameters.Add(
new OracleParameter("name", Guid.NewGuid().ToString()));
                command.Parameters.Add(
new OracleParameter("clob1", OracleType.Clob, ContentLength,
                ParameterDirection.Input, 
true00"", DataRowVersion.Current, tmpclob));

                command.ExecuteNonQuery();

                
this.Response.Write("insert OK");


            }
            
catch (Exception ex)
            {
                
this.Response.Write(ex.Message);
            }
            
finally
            {
                
if (conn != null)
                    conn.Close();
            }
        }

数据库内容

 

create table TEST1
(
  NAME         
VARCHAR2(100),
  UPDATED_TIME DATE 
default sysdate,
  ROWS_COUNT   
NUMBER default 0,
  CLOB1        CLOB
)
/
create or replace procedure pro_test1(name in varchar2,clob1 in clob)
as
  theXmlParser            dbms_xmlparser.Parser;
  theXmlDoc               dbms_xmldom.DOMDocument;
  theXmlNodeList          dbms_xmldom.DOMNodeList;
  rowsCount 
number(10);
begin
     theXmlParser :
= dbms_xmlparser.newParser;
     dbms_xmlparser.parseclob(theXmlParser, clob1);
     theXmlDoc :
= dbms_xmlparser.getDocument(theXmlParser);

     theXmlNodeList :
= dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(theXmlDoc),'/table/row');
     rowsCount :
= dbms_xmldom.getLength(theXmlNodeList);
     
     
insert into test1 (name,clob1,rows_count) values (name,clob1,rowsCount);
     
commit;
end pro_test1;
/

 

posted @ 2011-04-05 01:00  吴永富  阅读(3219)  评论(1编辑  收藏  举报