(C#) ASP.NET MySQL BLOB Binary Data and Parameterized Query |
(C#) ASP.NET MySQL BLOB Binary Data and Parameterized Query เขียน ASP.NET เพื่อจัดการข้อมูลใน MySQL Database ผ่าน Binary Data ในรูปแบบของ BLOB โดยคำสั่ง Execute Add/Insert/Update และ Delete ใช้รูปแบบการส่งข้อมูล Parameter เพื่อไช้ในการ Query Data (Using Parameterized Queries)
Instance NameSpace
C#Using System.Data;
Using MySql.Data.MySqlClient;
ASP.NET & MySql.Data.MySqlClient
การสร้างตาราง
CREATE TABLE `files` (
`FilesID` int(4) NOT NULL auto_increment,
`Name` varchar(100) NOT NULL,
`FilesName` blob NOT NULL,
`FilesType` varchar(50) NOT NULL,
PRIMARY KEY (`FilesID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
นำ SQL นี้ไป Query บน phpMyAdmin หรือ Tools อื่น ๆ เพื่อสร้างตาราง
เริ่มต้นด้วยการสร้าง 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 MySql.Data.MySqlClient;
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 ***'
MySqlConnection objConn = new MySqlConnection();
string strConnString = null;
string strSQL = null;
strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false;";
strSQL = "INSERT INTO files (Name,FilesName,FilesType) VALUES (?sName,?sFilesName,?sFilesType)";
objConn.ConnectionString = strConnString;
objConn.Open();
MySqlCommand objCmd = new MySqlCommand(strSQL, objConn);
objCmd.Parameters.Add("?sName", MySqlDbType.VarChar).Value = this.txtName.Text;
objCmd.Parameters.Add("?sFilesName", MySqlDbType.Binary).Value = imbByte;
objCmd.Parameters.Add("?sFilesType", MySqlDbType.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 MySql.Data.MySqlClient;
namespace ASPNetBLOB
{
public partial class Edit : System.Web.UI.Page
{
MySqlConnection objConn = new MySqlConnection();
MySqlCommand 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 = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false;";
objConn.ConnectionString = strConnString;
objConn.Open();
if (!Page.IsPostBack)
{
ViewData();
}
}
protected void ViewData()
{
//*** DataTable ***'
MySqlDataAdapter dtAdapter = default(MySqlDataAdapter);
DataTable dt = new DataTable();
strSQL = "SELECT * FROM files WHERE FilesID = ?sFilesID ";
dtAdapter = new MySqlDataAdapter(strSQL, objConn);
objCmd = dtAdapter.SelectCommand;
objCmd.Parameters.Add("?sFilesID", MySqlDbType.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 MySqlCommand(strSQL, objConn);
objCmd.Parameters.Add("?sName", MySqlDbType.VarChar).Value = this.txtName.Text;
objCmd.Parameters.Add("?sFilesID", MySqlDbType.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 MySqlCommand(strSQL, objConn);
objCmd.Parameters.Add("?sFilesName", MySqlDbType.Binary).Value = imbByte;
objCmd.Parameters.Add("?sFilesType", MySqlDbType.VarChar).Value = strMIME;
objCmd.Parameters.Add("?sFilesID", MySqlDbType.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 MySqlCommand(strSQL, objConn);
objCmd.Parameters.Add("?sFilesID", MySqlDbType.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 MySql.Data.MySqlClient;
namespace ASPNetBLOB
{
public partial class ListPicture : System.Web.UI.Page
{
MySqlConnection objConn;
MySqlCommand objCmd;
protected void Page_Load(object sender, EventArgs e)
{
string strConnString = null;
strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false;";
objConn = new MySqlConnection(strConnString);
objConn.Open();
BindData();
}
protected void BindData()
{
string strSQL = null;
strSQL = "SELECT * FROM files ORDER BY FilesID ASC";
MySqlDataReader dtReader = default(MySqlDataReader);
objCmd = new MySqlCommand(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 MySql.Data.MySqlClient;
namespace ASPNetBLOB
{
public partial class ViewImg : System.Web.UI.Page
{
MySqlConnection objConn = new MySqlConnection();
MySqlCommand objCmd;
string strConnString;
string strSQL;
protected void Page_Load(object sender, EventArgs e)
{
strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false;";
objConn.ConnectionString = strConnString;
objConn.Open();
//*** DataTable ***'
MySqlDataAdapter dtAdapter = default(MySqlDataAdapter);
DataTable dt = new DataTable();
strSQL = "SELECT * FROM files WHERE FilesID = ?sFilesID ";
dtAdapter = new MySqlDataAdapter(strSQL, objConn);
objCmd = dtAdapter.SelectCommand;
objCmd.Parameters.Add("?sFilesID", MySqlDbType.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
หน้าจอสำหรับอัพโหลด (Upload Binary) ข้อมูลลงใน BLOB
หน้าจอสำหรับแสดงข้อมูล (View BLOB)
หน้าจอสำหรับแก้ไขข้อมูล (Edit BLOB)
หน้าจอสำหรับการลบข้อมูล (Delete BLOB)
ASP.NET MySql.Data.MySqlClient - Parameter Query
ASP.NET & MySql.Data.MySqlClient
|
 ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท
|
|
| |
By : |
ThaiCreate.Com Team (บทความเป็นลิขสิทธิ์ของเว็บไทยครีเอทห้ามนำเผยแพร่ ณ เว็บไซต์อื่น ๆ) |
| |
Score Rating : |
 |
|
| |
Create/Update Date : |
2011-06-02 14:22:46 /
2017-03-29 10:34:18 |
| |
Download : |
|
|
|
Sponsored Links / Related |
|
|
|
|
|
|
|
|
Load balance : Server 01
