(C#) ASP.NET Oracle BLOB Binary Data and Parameterized Query |
(C#) ASP.NET Oracle BLOB Binary Data and Parameterized Query เขียน ASP.NET เพื่อจัดการข้อมูลใน Oracle Database ผ่าน Binary Data ในรูปแบบของ BLOB โดยคำสั่ง Execute Add/Insert/Update และ Delete ใช้รูปแบบการส่งข้อมูล Parameter เพื่อไช้ในการ Query Data (Using Parameterized Queries)
Instance NameSpace
C#Using System.Data;
Using System.Data.OracleClient;
ASP.NET & System.Data.OracleClient
การสร้างตาราง
-- Create table files
create table files
(
FilesID number,
Name varchar2(100),
FilesName blob,
FilesType varchar2(20)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table files
add constraint PK_FilesID primary key (FILESID);
-- Create sequence
create sequence seq_next_filesid
minvalue 1
maxvalue 999999
start with 1
increment by 1;
ให้นำ Query นี้เพื่อไป Execute สร้างตารางในฐานข้อมูล Oracle
เริ่มต้นด้วยการสร้าง Project ใหม่บน Visual Studio เลือก Application เป็น ASP.NET Web Site
ตั้งชื่อ Project เป็น ASPNetBLOB เลือกภาษาที่ต้องการ สามารถใช้ได้กับ .NET Framework ทุกเวอร์ชั่น
สร้างไฟล์และ Code ทั้งหมดตามในตัวอย่าง
Language Code : VB.NET ||  C#
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ASPNetBLOB._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>ThaiCreate.Com Tutorials</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblStatus" runat="server"
Text="Please input Name and Chooes File. "></asp:Label>
<br />
<asp:Panel ID="pnlForm" Visible="true" runat="server">
<asp:Label ID="lblName" runat="server" Text="Name :" Width="50px"></asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="lblPicture" runat="server" Text="Picture :" Width="50px"></asp:Label>
<asp:FileUpload ID="fUpload" runat="server" />
<br />
<asp:Button ID="btnUpload" runat="server" Text="Upload"
onclick="btnUpload_Click" />
<input id="btnReset" type="reset" value="Reset" /></div>
</asp:Panel>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OracleClient;
namespace ASPNetBLOB
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
this.pnlForm.Visible = false;
if (this.fUpload.HasFile == false | string.IsNullOrEmpty(this.txtName.Text))
{
this.lblStatus.Text = "Please input Name and Chooes File.";
}
else
{
//*** Read Binary Data ***'
byte[] imbByte = new byte[fUpload.PostedFile.InputStream.Length + 1];
fUpload.PostedFile.InputStream.Read(imbByte, 0, imbByte.Length);
//*** MimeType ***'
string ExtType = System.IO.Path.GetExtension(fUpload.PostedFile.FileName).ToLower();
string strMIME = null;
switch (ExtType)
{
case ".gif":
strMIME = "image/gif";
break;
case ".jpg":
case ".jpeg":
case ".jpe":
strMIME = "image/jpeg";
break;
case ".png":
strMIME = "image/png";
break;
default:
this.lblStatus.Text = "Invalid file type.";
return;
}
//*** Insert to Database ***'
OracleConnection objConn = new OracleConnection();
string strConnString = null;
string strSQL = null;
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;";
strSQL = "INSERT INTO files (Name,FilesName,FilesType) VALUES (:sName,:sFilesName,:sFilesType)";
objConn.ConnectionString = strConnString;
objConn.Open();
OracleCommand objCmd = new OracleCommand(strSQL, objConn);
objCmd.Parameters.Add(":sName", OracleType.VarChar).Value = this.txtName.Text;
objCmd.Parameters.Add(":sFilesName", OracleType.Blob).Value = imbByte;
objCmd.Parameters.Add(":sFilesType", OracleType.VarChar).Value = strMIME;
objCmd.ExecuteNonQuery();
objConn.Close();
objConn = null;
this.lblStatus.Text = "File Upload Successfully. Click <a href='ListPicture.aspx'>here</a> to view.";
}
}
}
}
Edit.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Edit.aspx.cs" Inherits="ASPNetBLOB.Edit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>ThaiCreate.Com Tutorials</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblStatus" runat="server"
Text="Please input Name and Chooes File. "></asp:Label>
<br />
<asp:Panel ID="pnlForm" Visible="true" runat="server">
<asp:Image ID="ImgPic" runat="server" />
<br />
<asp:Label ID="lblName" runat="server" Text="Name :" Width="50px"></asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="lblPicture" runat="server" Text="Picture :" Width="50px"></asp:Label>
<asp:FileUpload ID="fUpload" runat="server" />
<br />
<asp:Button ID="btnUpload" runat="server" Text="Upload"
onclick="btnUpload_Click" />
<input id="btnReset" type="reset" value="Reset" />
<asp:Button ID="btnDel" runat="server" Text="Delete" onclick="btnDel_Click"
style="height: 26px" />
</asp:Panel>
</div>
</form>
</body>
</html>
Edit.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OracleClient;
namespace ASPNetBLOB
{
public partial class Edit : System.Web.UI.Page
{
OracleConnection objConn = new OracleConnection();
OracleCommand objCmd;
string strConnString;
string strSQL;
protected void Page_Load(object sender, EventArgs e)
{
this.btnDel.Attributes.Add("OnClick", "return confirm('Are you sure delete?');");
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;";
objConn.ConnectionString = strConnString;
objConn.Open();
if (!Page.IsPostBack)
{
ViewData();
}
}
protected void ViewData()
{
//*** DataTable ***'
OracleDataAdapter dtAdapter = default(OracleDataAdapter);
DataTable dt = new DataTable();
strSQL = "SELECT * FROM files WHERE FilesID = :sFilesID ";
dtAdapter = new OracleDataAdapter(strSQL, objConn);
objCmd = dtAdapter.SelectCommand;
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString["FilesID"];
dtAdapter.Fill(dt);
if (dt.Rows.Count > 0)
{
this.ImgPic.ImageUrl = "ViewImg.aspx?FilesID=" + dt.Rows[0]["FilesID"];
this.txtName.Text = dt.Rows[0]["Name"].ToString();
}
dt = null;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
this.pnlForm.Visible = false;
if (string.IsNullOrEmpty(this.txtName.Text))
{
this.lblStatus.Text = "Please input Name.";
}
else
{
//*** Update Name ***'
strSQL = "UPDATE files SET Name = :sName WHERE FilesID = :sFilesID ";
objCmd = new OracleCommand(strSQL, objConn);
objCmd.Parameters.Add(":sName", OracleType.VarChar).Value = this.txtName.Text;
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString["FilesID"];
objCmd.ExecuteNonQuery();
//*** Update Picture ***'
if (this.fUpload.HasFile == true)
{
//*** Read Binary Data ***'
byte[] imbByte = new byte[fUpload.PostedFile.InputStream.Length + 1];
fUpload.PostedFile.InputStream.Read(imbByte, 0, imbByte.Length);
//*** MimeType ***'
string ExtType = System.IO.Path.GetExtension(fUpload.PostedFile.FileName).ToLower();
string strMIME = null;
switch (ExtType)
{
case ".gif":
strMIME = "image/gif";
break;
case ".jpg":
case ".jpeg":
case ".jpe":
strMIME = "image/jpeg";
break;
case ".png":
strMIME = "image/png";
break;
default:
this.lblStatus.Text = "Invalid file type.";
return;
}
strSQL = "UPDATE files SET FilesName = :sFilesName , FilesType = :sFilesType " + " WHERE FilesID = :sFilesID ";
objCmd = new OracleCommand(strSQL, objConn);
objCmd.Parameters.Add(":sFilesName", OracleType.Blob).Value = imbByte;
objCmd.Parameters.Add(":sFilesType", OracleType.VarChar).Value = strMIME;
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString["FilesID"];
objCmd.ExecuteNonQuery();
}
this.lblStatus.Text = "File Upload Successfully. Click <a href='ListPicture.aspx'>here</a> to view.";
}
}
protected void btnDel_Click(object sender, EventArgs e)
{
this.pnlForm.Visible = false;
//*** Delete Rows ***'
strSQL = "DELETE FROM files WHERE FilesID = :sFilesID ";
objCmd = new OracleCommand(strSQL, objConn);
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString["FilesID"];
objCmd.ExecuteNonQuery();
this.lblStatus.Text = "Delete Successfully. Click <a href='ListPicture.aspx'>here</a> to view.";
}
protected void Page_Unload(object sender, System.EventArgs e)
{
objConn.Close();
objConn = null;
}
}
}
ListPicture.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListPicture.aspx.cs" Inherits="ASPNetBLOB.ListPicture" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>ThaiCreate.Com Tutorials</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView id="myGridView" runat="server" AutoGenerateColumns="False"
onrowdatabound="myGridView_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="ID">
<ItemTemplate>
<asp:Label id="lblFilesID" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label id="lblName" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Picture">
<ItemTemplate>
<asp:Image ID="ImgPic" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<asp:HyperLink ID="hplEdit" runat="server">Edit</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
ListPicture.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OracleClient;
namespace ASPNetBLOB
{
public partial class ListPicture : System.Web.UI.Page
{
OracleConnection objConn;
OracleCommand objCmd;
protected void Page_Load(object sender, EventArgs e)
{
string strConnString = null;
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;";
objConn = new OracleConnection(strConnString);
objConn.Open();
BindData();
}
protected void BindData()
{
string strSQL = null;
strSQL = "SELECT * FROM files ORDER BY FilesID ASC";
OracleDataReader dtReader = default(OracleDataReader);
objCmd = new OracleCommand(strSQL, objConn);
dtReader = objCmd.ExecuteReader();
//*** BindData to GridView ***'
myGridView.DataSource = dtReader;
myGridView.DataBind();
dtReader.Close();
dtReader = null;
}
protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
//*** FilesID ***'
Label lblFilesID = (Label)e.Row.FindControl("lblFilesID");
if ((lblFilesID != null))
{
lblFilesID.Text = DataBinder.Eval(e.Row.DataItem, "FilesID").ToString();
}
//*** Name ***'
Label lblName = (Label)e.Row.FindControl("lblName");
if ((lblName != null))
{
lblName.Text = DataBinder.Eval(e.Row.DataItem, "Name").ToString();
}
//*** Picture ***'
Image ImgPic = (Image)e.Row.FindControl("ImgPic");
if ((ImgPic != null))
{
ImgPic.ImageUrl = "ViewImg.aspx?FilesID= " + DataBinder.Eval(e.Row.DataItem, "FilesID").ToString();
}
//*** Hyperlink ***'
HyperLink hplEdit = (HyperLink)e.Row.FindControl("hplEdit");
if ((hplEdit != null))
{
hplEdit.Text = "Edit";
hplEdit.NavigateUrl = "Edit.aspx?FilesID=" + DataBinder.Eval(e.Row.DataItem, "FilesID").ToString();
}
}
protected void Page_Unload(object sender, EventArgs e)
{
objConn.Close();
objConn = null;
}
}
}
ViewImg.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ViewImg.aspx.cs" Inherits="ASPNetBLOB.ViewImg" %>
ViewImg.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OracleClient;
namespace ASPNetBLOB
{
public partial class ViewImg : System.Web.UI.Page
{
OracleConnection objConn = new OracleConnection();
OracleCommand objCmd;
string strConnString;
string strSQL;
protected void Page_Load(object sender, EventArgs e)
{
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;";
objConn.ConnectionString = strConnString;
objConn.Open();
//*** DataTable ***'
OracleDataAdapter dtAdapter = default(OracleDataAdapter);
DataTable dt = new DataTable();
strSQL = "SELECT * FROM files WHERE FilesID = :sFilesID ";
dtAdapter = new OracleDataAdapter(strSQL, objConn);
objCmd = dtAdapter.SelectCommand;
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString["FilesID"].ToString();
dtAdapter.Fill(dt);
if (dt.Rows.Count > 0)
{
Response.ContentType = dt.Rows[0]["FilesType"].ToString();
Response.BinaryWrite((byte[])dt.Rows[0]["FilesName"]);
}
dt = null;
}
protected void Page_Unload(object sender, System.EventArgs e)
{
objConn.Close();
objConn = null;
}
}
}
Screenshot
Screen หน้าจอสำหรับอัพโหลด (Upload Binary) ข้อมูลลงใน BLOB
Screen หน้าจอสำหรับแสดงข้อมูล (View BLOB)
Screen หน้าจอสำหรับแก้ไขข้อมูล (Edit BLOB)
Screen หน้าจอสำหรับการลบข้อมูล (Delete BLOB)
ASP.NET System.Data.OracleClient - Parameter Query
ASP.NET & System.Data.OracleClient
Note!! โปรดทราบ เนื่องจาก System.Data.OracleClient ได้ถูก Obsolete ไปแล้ว ฉะนั้นจึงจะต้องใช้ Oracle.DataAccess แทน โดยสามารถประกาศเรียกได้จาก
จากนั้นให้ใช้ using หรือ Import คอนเน็คเตอร์ใหม่คือ Oracle.DataAccess ส่วนคำสั่งอื่น ๆ ยังคงเหมือนเดิม
|
 ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท
|
|
| |
By : |
ThaiCreate.Com Team (บทความเป็นลิขสิทธิ์ของเว็บไทยครีเอทห้ามนำเผยแพร่ ณ เว็บไซต์อื่น ๆ) |
| |
Score Rating : |
 |
|
| |
Create/Update Date : |
2011-06-02 14:25:21 /
2017-03-29 10:55:20 |
| |
Download : |
|
|
|
Sponsored Links / Related |
|
|
|
|
|
|
|
|
Load balance : Server 01
