ASP.NET Access BLOB Binary Data and Parameterized Query |
ASP.NET Access BLOB Binary Data and Parameterized Query เขียน ASP.NET เพื่อจัดการข้อมูลใน Database Access ผ่าน Binary Data ในรูปแบบของ BLOB โดยคำสั่ง Execute Add/Insert/Update และ Delete ใช้รูปแบบการส่งข้อมูล Parameter เพื่อไช้ในการ Query Data (Using Parameterized Queries)
Instance NameSpace
VB.NETImports System.Data
Imports System.Data.OleDb
ASP.NET & System.Data.OleDb
โครงสร้างของฐานข้อมูล Microsoft Access
หรือสามารถดาวน์โหลดได้จากตัวข้างล่าง
เริ่มต้นด้วยการสร้าง 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.OleDb
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 OleDbConnection
Dim strConnString, strSQL As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("App_Data/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
strSQL = "INSERT INTO files (Name,FilesName,FilesType) " & _
" VALUES " & _
" (@sName,@sFilesName,@sFilesType)"
objConn.ConnectionString = strConnString
objConn.Open()
Dim objCmd As New OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@sName", OleDbType.VarChar).Value = Me.txtName.Text
objCmd.Parameters.Add("@sFilesName", OleDbType.Binary).Value = imbByte
objCmd.Parameters.Add("@sFilesType", OleDbType.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
![](/images/adv.jpg)
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.OleDb
Partial Class Edit
Inherits System.Web.UI.Page
Dim objConn As New OleDbConnection
Dim objCmd As OleDbCommand
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 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("App_Data/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn.ConnectionString = strConnString
objConn.Open()
If Not Page.IsPostBack() Then
ViewData()
End If
End Sub
Protected Sub ViewData()
'*** DataTable ***'
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM files WHERE FilesID = @sFilesID "
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
objCmd = dtAdapter.SelectCommand
objCmd.Parameters.Add("@sFilesID", OleDbType.Integer).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 OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@sName", OleDbType.VarChar).Value = Me.txtName.Text
objCmd.Parameters.Add("@sFilesID", OleDbType.Integer).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 OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@sFilesName", OleDbType.Binary).Value = imbByte
objCmd.Parameters.Add("@sFilesType", OleDbType.VarChar).Value = strMIME
objCmd.Parameters.Add("@sFilesID", OleDbType.Integer).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 OleDbCommand(strSQL, objConn)
objCmd.Parameters.Add("@sFilesID", OleDbType.Integer).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.OleDb
Partial Class ListPicture
Inherits System.Web.UI.Page
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("App_Data/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn = New OleDbConnection(strConnString)
objConn.Open()
BindData()
End Sub
Protected Sub BindData()
Dim strSQL As String
strSQL = "SELECT * FROM files ORDER BY FilesID ASC"
Dim dtReader As OleDbDataReader
objCmd = New OleDbCommand(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.OleDb
Partial Class ViewImg
Inherits System.Web.UI.Page
Dim objConn As New OleDbConnection
Dim objCmd As OleDbCommand
Dim strConnString, strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("App_Data/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn.ConnectionString = strConnString
objConn.Open()
'*** DataTable ***'
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM files WHERE FilesID = @sFilesID "
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
objCmd = dtAdapter.SelectCommand
objCmd.Parameters.Add("@sFilesID", OleDbType.Integer).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
![](/images/adv.jpg)
Screenshot
หน้าจอสำหรับอัพโหลด (Upload Binary) ข้อมูลลงใน BLOB
หน้าจอสำหรับแสดงข้อมูล (View BLOB)
หน้าจอสำหรับแก้ไขข้อมูล (Edit BLOB)
หน้าจอสำหรับการลบข้อมูล (Delete BLOB)
ASP.NET System.Data.OleDb - Parameter Query
ASP.NET & System.Data.OleDb
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท
|
|
|
By : |
ThaiCreate.Com Team (บทความเป็นลิขสิทธิ์ของเว็บไทยครีเอทห้ามนำเผยแพร่ ณ เว็บไซต์อื่น ๆ) |
|
Score Rating : |
![](/images/resource/startrue.gif) ![](/images/resource/startrue.gif) |
|
|
Create/Update Date : |
2011-06-02 13:23:16 /
2017-03-29 09:52:48 |
|
Download : |
|
|
Sponsored Links / Related |
|
|
|
|
|
|
|