2015.1.5 用DBFactorySingleton写、读二进制字段与oralce CLOB类型字段读写[除非需要做内部解析，不要用CLOB，CLOB速度要比BLOB慢15倍以上]

//写

FileStream fstr = File.OpenRead(tb_filepath.Text); //openread函数用于打开只读文件，openwrite可写

BinaryReader br = new BinaryReader(fstr);

byte[] buffer = br.ReadBytes((int)fstr.Length);

string sql = string.Format("insert into NVDUSER_FLT.FILE_SOURCE values(1000,'  ','  ',:cont,'we')");

 

IDbConnection cnn = DBFactorySingleton.GetInstance().Factory.GetConnection();

cnn.Open();

IDbCommand dbcom = DBFactorySingleton.GetInstance().Factory.GetCommand();

dbcom.Connection = cnn;

dbcom.CommandText = sql;

 

System.Data.IDataParameter mypara= DBFactorySingleton.GetInstance().Factory.GetDataParameter("cont",DbType.Binary);

mypara.Value = buffer;

                   

dbcom.Parameters.Add(mypara);

dbcom.ExecuteNonQuery();                  

 

fstr.Close();

 

//读

string sql = string.Format("select content from {0}.file_source where file_id={1}",AisLogical.dbuser,fid);

            byte[] data = (byte[])DBFactorySingleton.GetInstance().Factory.ExecuteScalar(sql);

 

  if (data != null)

  {

    String ep = Application.ExecutablePath;

    ep = ep.Substring(0, ep.LastIndexOf("\\") + 1);

    try

     {

       string[] fls = Directory.GetFiles(ep, "tmpsourcefile.*");

       foreach (string fl in fls)

               File.Delete(fl);

      }

    catch { }

 

  try

  {

    string pf = ep + "tmpsourcefile."+hzm;

    FileStream stream = new FileStream(pf, FileMode.CreateNew, FileAccess.Write);

    BinaryWriter bw = new BinaryWriter(stream);

    bw.Write(data);

    bw.Close();

    stream.Close();

    Thread.Sleep(100);

 

    Process proc = new Process();

    proc.StartInfo.FileName = pf;

    proc.StartInfo.Arguments = "";

    proc.Start();

   }

   catch { }

}

CLOB、BLOB 是oracle两种特殊字段类型大小限制都是4G，CLOB与BLOB的区别是CLOB处理大字符串，而BLOB相当大二进制文件。在Oracle内部dbms_lob对CLOB进行类似于字符串比较等，这里不介绍。

插入：

OracleParameter para = new OracleParameter("cont", OracleType.Clob);

para.Value = xmlheader;

cmd.Parameters.Clear();

cmd.Parameters.Add(para);

cmd.CommandText = "insert into kmlview(name,content) values('机场',:cont) ";                               

cmd.ExecuteNonQuery();

 

读取：与读普通字符串没有区别

sql = string.Format("select content from {0}.kmlview where name='机场' ", AisLogical.dbuser);

string data = DBFactorySingleton.GetInstance().Factory.ExecuteScalar(sql).ToString().Trim();

 

除非需要做内部解析，不要用CLOB，CLOB速度要比BLOB速度慢15倍以上

补充：如果是存取大string对象，跟文件其实没实质区别，都是把插入内容先转化为byte[]

写大string的例子：

string sql = string.Format("update {0}.kmlblob set content = :cont where name= '{1}' ", v.LoginUser, type);

            byte[] bval = System.Text.Encoding.Default.GetBytes(kml);

            IDbConnection cnn = DBFactorySingleton.GetInstance().Factory.GetConnection();

            cnn.Open();

            IDbCommand dbcom = DBFactorySingleton.GetInstance().Factory.GetCommand();

            dbcom.Connection = cnn;

            dbcom.CommandText = sql;

            System.Data.IDataParameter mypara = DBFactorySingleton.GetInstance().Factory.GetDataParameter("cont", DbType.Binary);

            mypara.Value = bval;

            dbcom.Parameters.Add(mypara);

            if (dbcom.ExecuteNonQuery() == 0)

            {

                dbcom.CommandText = string.Format("insert into {0}.kmlblob(name,content) values('{1}',:cont) ", v.LoginUser, type);

                dbcom.ExecuteNonQuery();

            }