ORACLE数据库中主要字段类型的读写例子(包括：Long、Raw、Blob)
这里我用的是ORACLE9I, 建立了表C_EMP1_T,结构如下：
create table C_EMP1_T
(
EMP_ID NUMBER(20) not null, //用户ID
EMP_NO VARCHAR2(20), //用户编号
EMP_DESC LONG, //用户简历
USED_DATE DATE, //注册日期
EMP_IC_MAC RAW(50), //用户IC卡的MAC号
EMP_ADMIN_FLAG CHAR(1), //管理员标志
EMP_PICTURE BLOB //用户图像
)
其中INSERT/UPDATE/SELECT的代码分别如下（DELETE比较简单就省略了，其中SELECT和UPDATE的条件都是记录的rowid）：
首先BLOB字段是存图片的，所以有一个过程是把图片传到服务器上：
先在aspx上加：
<form id="Form1" enctype="multipart/form-data" method="post" runat="server">
<input id="IoFile" runat="server" type="file"> <FONT face="宋体">下一步→</FONT>
<asp:Button id="btunload" runat="server" Text="上传"></asp:Button>
</form>
和代码：
private void btunload_Click(object sender, System.EventArgs e)
{
btunload.Enabled = false; //获得文件名称 string tempfilename = IoFile.PostedFile.FileName; //注： loFile.PostedFile.FileName 返回的是 通过文件对话框选择的文件名，这之中包含了文件的目录信息 tempfilename = Path.GetFileName ( tempfilename); if (tempfilename.Substring(tempfilename.Length-4,4) == ".bmp")
{
//去掉目录信息，返回文件名称 //判断上传目录是否存在，不存在就建立 string tempDirectory = "D:/WWWROOT/MYWEB/dbtest/Image/"; if ( ! Directory.Exists ( tempDirectory ) ) Directory.CreateDirectory ( tempDirectory ) ; //上传文件到服务器 string tempPath = tempDirectory+tempfilename;//得到上传目录及文件名称 IoFile.PostedFile.SaveAs ( tempPath ); Label1.Text = "<img src='image/"+tempfilename+"'>"; Session["filename"] = tempfilename;
} else { Label1.Text = "错误的文件类型"; } btunload.Enabled = true; /* ' 获得并显示上传文件的属性 FileName.Text = lstrFileName ' 获得文件名称 FileType.Text = loFile.PostedFile.ContentType ' 获得文件类型 FileLength.Text = cStr ( loFile.PostedFile.ContentLength ) ' 获得文件长度 FileUploadForm.visible = false AnswerMsg.visible = true ' 显示上传文件属性 End sub */
}
INSERT:
private void BtInsert_Click(object sender, System.EventArgs e) { OracleConnection Ocon = new OracleConnection("user id=cmes;data source=mes;password=cmes"); Ocon.Open(); //OracleCommand Ocom = new OracleCommand("insert into c_emp1_t (EMP_ID,EMP_NO,EMP_DESC,USED_DATE,EMP_IC_MAC,EMP_ADMIN_FLAG,EMP_PICTURE)values(:emp_id,:emp_no,:emp_desc,:emp_date,:emp_ic_mac,:flag,:picture)",Ocon); OracleCommand Ocom = new OracleCommand("insert into c_emp1_t (EMP_ID,EMP_NO,EMP_DESC,USED_DATE,EMP_IC_MAC,EMP_ADMIN_FLAG)values(:emp_id,:emp_no,:emp_desc,:emp_date,:emp_ic_mac,:flag)",Ocon); Ocom.Parameters.Add(new OracleParameter("emp_id",OracleType.Number)); Ocom.Parameters.Add(new OracleParameter("emp_no",OracleType.VarChar)); Ocom.Parameters.Add(new OracleParameter("emp_desc",OracleType.LongVarChar)); Ocom.Parameters.Add(new OracleParameter("emp_date",OracleType.DateTime)); Ocom.Parameters.Add(new OracleParameter("flag",OracleType.Char)); Ocom.Parameters.Add(new OracleParameter("emp_ic_mac",OracleType.Raw)); //Ocom.Parameters.Add(new OracleParameter("picture",OracleType.Blob)); Ocom.Parameters["emp_id"].Value = Convert.ToDecimal(tbempid.Text); Ocom.Parameters["emp_no"].Value = tbempno.Text; Ocom.Parameters["emp_desc"].Value = tbempdesc.Text; string empdate = tbempdate.Text;// yyyymmdd Ocom.Parameters["emp_date"].Value = new DateTime(Convert.ToInt32(empdate.Substring(0,4)),Convert.ToInt32(empdate.Substring(4,2)),Convert.ToInt32(empdate.Substring(6,2)) ); if (rb1.Checked) Ocom.Parameters["flag"].Value = '1'; if (rb2.Checked) Ocom.Parameters["flag"].Value = '2'; if (rb3.Checked) Ocom.Parameters["flag"].Value = '3'; Ocom.Parameters["emp_ic_mac"].Value = setraw(tbicmac.Text); Ocom.ExecuteNonQuery(); /*由与有字段long和blob一起，所以不能在一个insert语句中进行插入，单独insert是成功的 OracleConnection Ocon = new OracleConnection("user id=cmes;data source=mes;password=cmes"); Ocon.Open(); OracleCommand Ocom = new OracleCommand("insert into c_emp1_t (EMP_ID,EMP_PICTURE)values(:emp_id,:picture)",Ocon); Ocom.Parameters.Add(new OracleParameter("emp_id",OracleType.Number)); Ocom.Parameters.Add(new OracleParameter("picture",OracleType.Blob)); Ocom.Parameters["emp_id"].Value = Convert.ToDecimal(tbempid.Text); FileStream reader = new FileStream("D:/WWWROOT/MYWEB/dbtest/Image/002.bmp",FileMode.Open);//在iis中不用用“\”目录结构应该是“/” byte[] tempimage = new byte[reader.Length]; reader.Read(tempimage,0,tempimage.Length); //OracleLob tempLob=OracleLob.Null; //tempLob.BeginBatch(OracleLobOpenMode.ReadWrite); //tempLob.Write(tempimage,0,tempimage.Length); //tempLob.EndBatch(); reader.Close(); Ocom.Parameters["picture"].Size = tempimage.Length; //tempimage.CopyTo(Ocom.Parameters["picture"].Value,0); Ocom.Parameters["picture"].Value = tempimage; Ocom.ExecuteNonQuery();*/ string myfilename = Convert.ToString(Session["filename"]); if (myfilename != "") { Ocom.Parameters.Clear(); Ocom.CommandText = "update c_emp1_t SET EMP_PICTURE = :PICTURE WHERE EMP_ID= :EMP_ID "; Ocom.Parameters.Add(new OracleParameter("EMP_ID",OracleType.Number)); Ocom.Parameters.Add(new OracleParameter("PICTURE",OracleType.Blob)); Ocom.Parameters["EMP_ID"].Value = Convert.ToDecimal(tbempid.Text); FileStream reader = new FileStream("D:/WWWROOT/MYWEB/dbtest/Image/"+myfilename,FileMode.Open);//在iis中不用用“\”目录结构应该是“/” byte[] tempimage = new byte[reader.Length]; reader.Read(tempimage,0,tempimage.Length); reader.Close(); Ocom.Parameters["PICTURE"].Size = tempimage.Length; Ocom.Parameters["PICTURE"].Value = tempimage; Ocom.ExecuteNonQuery(); Session["filename"] = ""; } Ocon.Close(); }UPDATE:
private void btUpdate_Click(object sender, System.EventArgs e) { OracleConnection Ocon = new OracleConnection("user id=cmes;data source=mes;password=cmes"); Ocon.Open(); OracleCommand Ocom = new OracleCommand("UPDATE c_emp1_t SET EMP_ID= :emp_id,EMP_NO= :emp_no,EMP_DESC= :emp_desc,USED_DATE= :emp_date,EMP_IC_MAC= :emp_ic_mac,EMP_ADMIN_FLAG= :flag WHERE ROWID = :MYROWID",Ocon); Ocom.Parameters.Add(new OracleParameter("MYROWID",OracleType.RowId)); Ocom.Parameters.Add(new OracleParameter("emp_id",OracleType.Number)); Ocom.Parameters.Add(new OracleParameter("emp_no",OracleType.VarChar)); Ocom.Parameters.Add(new OracleParameter("emp_desc",OracleType.LongVarChar)); Ocom.Parameters.Add(new OracleParameter("emp_date",OracleType.DateTime)); Ocom.Parameters.Add(new OracleParameter("flag",OracleType.Char)); Ocom.Parameters.Add(new OracleParameter("emp_ic_mac",OracleType.Raw)); Ocom.Parameters["MYROWID"].Value = TBROWID.Text; Ocom.Parameters["emp_id"].Value = Convert.ToDecimal(tbempid.Text); Ocom.Parameters["emp_no"].Value = tbempno.Text; Ocom.Parameters["emp_desc"].Value = tbempdesc.Text; string empdate = tbempdate.Text;// yyyymmdd Ocom.Parameters["emp_date"].Value = new DateTime(Convert.ToInt32(empdate.Substring(0,4)),Convert.ToInt32(empdate.Substring(4,2)),Convert.ToInt32(empdate.Substring(6,2)) ); if (rb1.Checked) Ocom.Parameters["flag"].Value = '1'; if (rb2.Checked) Ocom.Parameters["flag"].Value = '2'; if (rb3.Checked) Ocom.Parameters["flag"].Value = '3'; Ocom.Parameters["emp_ic_mac"].Value = setraw(tbicmac.Text); Ocom.ExecuteNonQuery(); string myfilename = Convert.ToString(Session["filename"]); if (myfilename != "") { Ocom.Parameters.Clear(); Ocom.CommandText = "update c_emp1_t SET EMP_PICTURE = :PICTURE WHERE EMP_ID= :EMP_ID "; Ocom.Parameters.Add(new OracleParameter("EMP_ID",OracleType.Number)); Ocom.Parameters.Add(new OracleParameter("PICTURE",OracleType.Blob)); Ocom.Parameters["EMP_ID"].Value = Convert.ToDecimal(tbempid.Text); FileStream reader = new FileStream("D:/WWWROOT/MYWEB/dbtest/Image/"+myfilename,FileMode.Open);//在iis中不用用“\”目录结构应该是“/” byte[] tempimage = new byte[reader.Length]; reader.Read(tempimage,0,tempimage.Length); reader.Close(); Ocom.Parameters["PICTURE"].Size = tempimage.Length; Ocom.Parameters["PICTURE"].Value = tempimage; Ocom.ExecuteNonQuery(); Session["filename"] = ""; } Ocon.Close(); }private void btselect_Click(object sender, System.EventArgs e) { if (TBROWID.Text.Length < 18) { TBROWID.Text = "请输入正确的ROWID"; return; } OracleConnection Ocon = new OracleConnection("user id=cmes;data source=mes;password=cmes"); Ocon.Open(); OracleCommand Ocom = new OracleCommand("select EMP_ID,EMP_NO,EMP_DESC,USED_DATE,EMP_IC_MAC,EMP_ADMIN_FLAG,EMP_PICTURE from c_emp1_t where rowid = :emprowid",Ocon); Ocom.Parameters.Add(new OracleParameter("emprowid",OracleType.RowId)); Ocom.Parameters["emprowid"].Value = TBROWID.Text; OracleDataReader reader = Ocom.ExecuteReader(); while (reader.Read()) { if (!(reader.IsDBNull(0))) tbempid.Text = Convert.ToString( reader.GetDecimal(0)); if (!(reader.IsDBNull(1))) tbempno.Text = reader.GetString(1); if (!(reader.IsDBNull(2))) tbempdesc.Text = reader.GetString(2); if (!(reader.IsDBNull(3))) { DateTime DT = reader.GetDateTime(3); tbempdate.Text = DT.ToString("yyyyMMdd"); } if (!(reader.IsDBNull(4))) { byte[] temp = new byte[4000]; reader.GetBytes(4,0,temp,0,4000); tbicmac.Text = getraw(temp); } else tbicmac.Text = ""; if (!(reader.IsDBNull(5))) { /*char tempflag = reader.GetChar(5); //不支持此方法 switch (tempflag) { case '1': rb1.Checked = true;break; case '2': rb2.Checked = true;break; default : rb3.Checked = true;break; }*/ char tempflag = Convert.ToChar(reader.GetValue(5)); switch (tempflag) { case '1': rb1.Checked = true;break; case '2': rb2.Checked = true;break; default : rb3.Checked = true;break; } } if (!(reader.IsDBNull(6))) { Label1.Text = "<img src='webform3.aspx?ROWID="+TBROWID.Text+"'>"; } } reader.Close(); Ocon.Close(); }webform3.aspx的程序：
private void Page_Load(object sender, System.EventArgs e) { OracleConnection Ocon = new OracleConnection("user id=cmes;data source=mes;password=cmes"); Ocon.Open(); OracleCommand Ocom = new OracleCommand("select EMP_PICTURE from c_emp1_t where rowid = :emprowid",Ocon); Ocom.Parameters.Add(new OracleParameter("emprowid",OracleType.RowId)); Ocom.Parameters["emprowid"].Value = Request.Params["ROWID"]; OracleDataReader reader = Ocom.ExecuteReader(); while (reader.Read()) { if (!(reader.IsDBNull(0))) { OracleLob tempBlob = reader.GetOracleLob(0); byte[] tempbuffer = new byte[tempBlob.Length]; //tempBlob.BeginBatch(OracleLobOpenMode.ReadWrite);会有错误ORA-22292: 无法在没有事务处理的情况下以读写模式打开 LOB tempBlob.BeginBatch(); tempBlob.Read(tempbuffer,0,tempbuffer.Length); tempBlob.EndBatch(); //下面是把具体的文件保存出来 BinaryWriter writer = new BinaryWriter(new FileStream("D:/WWWROOT/MYWEB/dbtest/Image/temp.bmp",FileMode.Create)); for(int i=0;i<tempbuffer.Length;i++) { writer.Write(tempbuffer[i]); } writer.Close(); //下面是直接把图片用流向客户端输出 Response.BinaryWrite(tempbuffer); } } reader.Close(); Ocon.Close(); }
