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

时间：2011-04-05 来源：吴永富

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, true, 0, 0, "", 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;
/