ASP.NET Oracle BLOB Binary Data and Parameterized Query |
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
VB.NETImports System.Data
Imports 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="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_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" />
<input id="btnReset" type="reset" value="Reset" /></div>
</asp:Panel>
</form>
</body>
</html>
Default.aspx.vb
Imports System.Data
Imports System.Data.OracleClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Me.pnlForm.Visible = False
If Me.fUpload.HasFile = False Or Me.txtName.Text = "" Then
Me.lblStatus.Text = "Please input Name and Chooes File."
Else
'*** Read Binary Data ***'
Dim imbByte(fUpload.PostedFile.InputStream.Length) As Byte
fUpload.PostedFile.InputStream.Read(imbByte, 0, imbByte.Length)
'*** MimeType ***'
Dim ExtType As String = System.IO.Path.GetExtension(fUpload.PostedFile.FileName).ToLower()
Dim strMIME As String = Nothing
Select Case ExtType
Case ".gif"
strMIME = "image/gif"
Case ".jpg", ".jpeg", ".jpe"
strMIME = "image/jpeg"
Case ".png"
strMIME = "image/png"
Case Else
Me.lblStatus.Text = "Invalid file type."
Exit Sub
End Select
'*** Insert to Database ***'
Dim objConn As New OracleConnection
Dim strConnString, strSQL As String
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()
Dim objCmd As New OracleCommand(strSQL, objConn)
objCmd.Parameters.Add(":sName", OracleType.VarChar).Value = Me.txtName.Text
objCmd.Parameters.Add(":sFilesName", OracleType.Blob).Value = imbByte
objCmd.Parameters.Add(":sFilesType", OracleType.VarChar).Value = strMIME
objCmd.ExecuteNonQuery()
objConn.Close()
objConn = Nothing
Me.lblStatus.Text = "File Upload Successfully. Click <a href='ListPicture.aspx'>here</a> to view."
End If
End Sub
End Class
Edit.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Edit.aspx.vb" Inherits="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" />
<input id="btnReset" type="reset" value="Reset" />
<asp:Button ID="btnDel" runat="server" Text="Delete" />
</asp:Panel>
</div>
</form>
</body>
</html>
Edit.aspx.vb
Imports System.Data
Imports System.Data.OracleClient
Partial Class Edit
Inherits System.Web.UI.Page
Dim objConn As New OracleConnection
Dim objCmd As OracleCommand
Dim strConnString, strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.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 Not Page.IsPostBack() Then
ViewData()
End If
End Sub
Protected Sub ViewData()
'*** DataTable ***'
Dim dtAdapter As OracleDataAdapter
Dim dt As 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 Then
Me.ImgPic.ImageUrl = "ViewImg.aspx?FilesID=" & dt.Rows(0)("FilesID")
Me.txtName.Text = dt.Rows(0)("Name")
End If
dt = Nothing
End Sub
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Me.pnlForm.Visible = False
If Me.txtName.Text = "" Then
Me.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 = Me.txtName.Text
objCmd.Parameters.Add(":sFilesID", OracleType.Int32).Value = Request.QueryString("FilesID")
objCmd.ExecuteNonQuery()
'*** Update Picture ***'
If Me.fUpload.HasFile = True Then
'*** Read Binary Data ***'
Dim imbByte(fUpload.PostedFile.InputStream.Length) As Byte
fUpload.PostedFile.InputStream.Read(imbByte, 0, imbByte.Length)
'*** MimeType ***'
Dim ExtType As String = System.IO.Path.GetExtension(fUpload.PostedFile.FileName).ToLower()
Dim strMIME As String = Nothing
Select Case ExtType
Case ".gif"
strMIME = "image/gif"
Case ".jpg", ".jpeg", ".jpe"
strMIME = "image/jpeg"
Case ".png"
strMIME = "image/png"
Case Else
Me.lblStatus.Text = "Invalid file type."
Exit Sub
End Select
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()
End If
Me.lblStatus.Text = "File Upload Successfully. Click <a href='ListPicture.aspx'>here</a> to view."
End If
End Sub
Protected Sub btnDel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDel.Click
Me.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()
Me.lblStatus.Text = "Delete Successfully. Click <a href='ListPicture.aspx'>here</a> to view."
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
objConn.Close()
objConn = Nothing
End Sub
End Class
ListPicture.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ListPicture.aspx.vb" Inherits="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">
<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.vb
Imports System.Data
Imports System.Data.OracleClient
Partial Class ListPicture
Inherits System.Web.UI.Page
Dim objConn As OracleConnection
Dim objCmd As OracleCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
objConn = New OracleConnection(strConnString)
objConn.Open()
BindData()
End Sub
Protected Sub BindData()
Dim strSQL As String
strSQL = "SELECT * FROM files ORDER BY FilesID ASC"
Dim dtReader As OracleDataReader
objCmd = New OracleCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to GridView ***'
myGridView.DataSource = dtReader
myGridView.DataBind()
dtReader.Close()
dtReader = Nothing
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
objConn.Close()
objConn = Nothing
End Sub
Protected Sub myGridView_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles myGridView.RowDataBound
'*** FilesID ***'
Dim lblFilesID As Label = DirectCast(e.Row.FindControl("lblFilesID"), Label)
If Not IsNothing(lblFilesID) Then
lblFilesID.Text = e.Row.DataItem("FilesID")
End If
'*** Name ***'
Dim lblName As Label = DirectCast(e.Row.FindControl("lblName"), Label)
If Not IsNothing(lblName) Then
lblName.Text = e.Row.DataItem("Name")
End If
'*** Picture ***'
Dim ImgPic As Image = DirectCast(e.Row.FindControl("ImgPic"), Image)
If Not IsNothing(ImgPic) Then
ImgPic.ImageUrl = "ViewImg.aspx?FilesID= " & e.Row.DataItem("FilesID")
End If
'*** Hyperlink ***'
Dim hplEdit As HyperLink = DirectCast(e.Row.FindControl("hplEdit"), HyperLink)
If Not IsNothing(hplEdit) Then
hplEdit.Text = "Edit"
hplEdit.NavigateUrl = "Edit.aspx?FilesID=" & e.Row.DataItem("FilesID")
End If
End Sub
End Class
ViewImg.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ViewImg.aspx.vb" Inherits="ViewImg" %>
ViewImg.aspx.vb
Imports System.Data
Imports System.Data.OracleClient
Partial Class ViewImg
Inherits System.Web.UI.Page
Dim objConn As New OracleConnection
Dim objCmd As OracleCommand
Dim strConnString, strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
objConn.ConnectionString = strConnString
objConn.Open()
'*** DataTable ***'
Dim dtAdapter As OracleDataAdapter
Dim dt As 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 Then
Response.ContentType = dt.Rows(0)("FilesType").ToString()
Response.BinaryWrite(dt.Rows(0)("FilesName"))
End If
dt = Nothing
End Sub
Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
objConn.Close()
objConn = Nothing
End Sub
End Class
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 04
