Store and retrieve image from database MSSQL - asp.net
This is a example to upload image and store into database. The example also show how to retrieve image from database.
Note : Assume the web page have their own master page
By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
Note : Assume the web page have their own master page
SQL Create Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[imageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [image] NULL,
CONSTRAINT [PK_imageTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ASPX page Code
<asp:Label ID="Label1" runat="server" Text="Image Upload :"></asp:Label>
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />
<br />
<br />
<asp:Panel ID="Panel1" runat="server">
</asp:Panel>
Code Behind
protected void Page_Load(object sender, EventArgs e)
{
CheckIfHaveImage();
}
private void CheckIfHaveImage()
{
try
{
//Initialize SQL Server connection.
SqlConnection dbConn = new SqlConnection("Connection string");
//Initialize SQL adapter.
SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImageTest", dbConn);
//Initialize Dataset.
DataSet DS = new DataSet();
//Fill dataset with ImagesStore table.
ADAP.Fill(DS, "ImagesStore");
dbConn.Close();
string path = Server.MapPath("~");
foreach (DataRow row in DS.Tables[0].Rows)
{
string image1 = Convert.ToString(DateTime.Now.ToFileTime());
path += "/" + image1 + ".jpg";
FileStream fs = new FileStream(path, FileMode.CreateNew, FileAccess.Write);
byte[] bimage1 = (byte[])row["Image"];
fs.Write(bimage1, 0, bimage1.Length - 1);
fs.Flush();
Image I = new Image();
I.ImageUrl = "~/" + image1 + ".jpg";
Panel1.Controls.Add(I);
}
}
catch (Exception ex)
{
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
//get root path
string path = Server.MapPath("~");
//save image upload
path += "/" + FileUpload1.FileName;
FileUpload1.SaveAs(path);
//Initialize byte array with a null value initially.
byte[] data = null;
//Use FileInfo object to get file size.
FileInfo fInfo = new FileInfo(path);
long numBytes = fInfo.Length;
//Open FileStream to read file
FileStream fStream = new FileStream(path, FileMode.Open, FileAccess.Read);
//Use BinaryReader to read file stream into byte array.
BinaryReader br = new BinaryReader(fStream);
//When you use BinaryReader, you need to supply number of bytes to read from file.
//In this case we want to read entire file.
//So supplying total number of bytes.
data = br.ReadBytes((int)numBytes);
//create db connection
SqlConnection dbConn = new SqlConnection("Connection string");
SqlCommand dbComm;
string strsql = "insert into ImageTest (Image) values(@image)";
dbComm = new SqlCommand(strsql, dbConn);
SqlParameter param = new SqlParameter("@image", System.Data.SqlDbType.Image);
param.Value = data;
dbComm.Parameters.Add(param);
dbConn.Open();
dbComm.ExecuteNonQuery();
dbConn.Close();
}
}
Code Explanation
The example above have file upload controller.After user select on the image to upload and click save button, the code will save upload image into temporary file, and process it as a byre[]. After that store byte value into database. The retrieve process will load data from database and convert byte[] value from database into an image and then create Image controller dynamically and add the image controller into panel to show the image.By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
.bmp)
