oracle存入文件blob,C#将文件保存到Oracle的BLOB字段

private void button1_Click(object sender, EventArgs e)

{

string cnnstr = "provider=OraOLEDB.Oracle;data source=HWQY;User Id=HWQY;Password=HWQY123;";

OleDbConnection con = new OleDbConnection(cnnstr);

try

{

con.Open();

}

catch

{ }

OleDbCommand cmd = new OleDbCommand(cnnstr, con);

cmd.CommandType = CommandType.Text;

cmd.CommandText = cnnstr;

string imgPath = @"c:/a.txt";//图片文件所在路径

FileStream file = new FileStream(imgPath, FileMode.Open, FileAccess.Read);

Byte[] imgByte = new Byte[file.Length];//把图片转成 Byte型 二进制流

file.Read(imgByte, 0, imgByte.Length);//把二进制流读入缓冲区

file.Close();

cmd.CommandText = " insert into test(id,zp ) values ('17',:zp) ";//正常sql语句插入数据库

cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, imgByte.Length);

cmd.Parameters[0].Value = imgByte;

try

{

cmd.ExecuteNonQuery();

MessageBox.Show("插入成功");

}

catch (System.Exception e1)

{

MessageBox.Show(e1.Message);

}

}

private void button2_Click(object sender, EventArgs e)

{

string cnnstr = "provider=OraOLEDB.Oracle;data source=HWQY;User Id=HWQY;Password=HWQY123;";

OleDbConnection con = new OleDbConnection(cnnstr);

try

{

con.Open();

}

catch

{ }

OleDbCommand cmd = new OleDbCommand(cnnstr, con);

cmd.CommandType = CommandType.Text;

cmd.CommandText = cnnstr;

string imgPath = @"c:/a.txt";//图片文件所在路径

FileStream file = new FileStream(imgPath, FileMode.Open, FileAccess.Read);

Byte[] imgByte = new Byte[file.Length];//把图片转成 Byte型 二进制流

file.Read(imgByte, 0, imgByte.Length);//把二进制流读入缓冲区

file.Close();

cmd.CommandText = " update test  set id='18',zp=:zp where id='17'";//正常sql语句插入数据库

cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, imgByte.Length);

cmd.Parameters[0].Value = imgByte;

try

{

cmd.ExecuteNonQuery();

MessageBox.Show("更新成功");

}

catch (System.Exception e1)

{

MessageBox.Show(e1.Message);

}

}

private void button3_Click(object sender, EventArgs e)

{

string strSQL = "select * from test where id = '18' ";

string cnnstr = "provider=OraOLEDB.Oracle;data source=hwqy;User Id=hwqy;Password=hwqy123;";

OleDbConnection con = new OleDbConnection(cnnstr);

try

{

con.Open();

}

catch

{ }

DataTable dt = new DataTable();

System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter("select * from test",con);

da.Fill(dt);

Byte[] fileByte = (byte[])(dt.Rows[0]["zp"]);

FileStream fsForWrite = new FileStream(@"c:/test.txt", FileMode.Create);

fsForWrite.Write(fileByte, 0, fileByte.GetLength(0));

fsForWrite.Close();

}

注意：

连接ORACLE可以有多种形式的字符串，

// HWQY_Task.HWQY_OraHelper.strConn = "Provider=MSDAORA;pools=true;User ID=" + LinkUser + ";Data Source=" + LinkName + ";Password=" + LinkPassWord + ";";

HWQY_Task.HWQY_OraHelper.strConn = "Provider=OraOLEDB.Oracle;pools=true;User ID=" + LinkUser + ";Data Source=" + LinkName + ";Password=" + LinkPassWord + ";";

但用Provider=MSDAORA的形式时，保存到BLOB将出错。只能采用Provider=OraOLEDB.Oracle的形式。