ASP.NET Image to Database
A data type is an attribute that defines the type of data that can be stored in an object. In SQL Server, one of the available data types is the Image data type. The Image data type is used to store variable-length binary data ranging from 0 to 2^31-1 (2,147,483,647) bytes in size.
Image data type
The Image data type is commonly used to store large amounts of binary data, such as images, documents, or other multimedia files. It provides a means to store binary data as a single entity within a SQL Server table. The actual binary data is stored outside the table, and the Image data type column stores a reference or pointer to that data.
The Image data type can hold a wide range of binary information, including image files, audio files, video files, or any other form of binary data. It is a versatile data type that allows for the storage and retrieval of large binary objects within a SQL Server database.
The following sql script help you to create a table with Image Datatype column.
CREATE TABLE [dbo].[imagestore](
[id] [int] NULL,
[img] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The above script will create a table named as imagestore and define two columns , first column is id ,an integer datatype and second column is image, an image(img) datatype.
It is important to note that the Image data type has been deprecated in recent versions of SQL Server. Microsoft recommends using the VARBINARY(MAX) data type instead, which offers similar functionality but with improved performance and features. VARBINARY(MAX) can store up to 2^31-1 bytes of binary data and is the preferred data type for storing large binary objects in modern SQL Server databases.
The following ASP.NET program upload an image file and saved it to local system and later that image will insert into the database.
Default.aspx
<!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 id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Upload"
Width="123px" />
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Full Source | C#
using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
try
{
string imgPath;
FileUpload1.SaveAs("C:\\files\\" + FileUpload1.FileName);
imgPath = "C:\\files\\" + FileUpload1.FileName;
if (File.Exists(imgPath))
{
int id = 1;
byte[] content = imgStream(imgPath);
string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand("insert into imagestore (id,img) values ( @id,@img)", connection);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@img", content);
cmd.ExecuteNonQuery();
connection.Close();
Label1.Text = "Insert Success !! ";
}
else
{
Label1.Text = imgPath + " not found ";
}
}
catch (Exception ex)
{
Label1.Text = "File Upload Failed !! " + ex.Message.ToString();
}
else
{
Label1.Text = "Please select a file ";
}
}
private byte[] imgStream(string fileName)
{
MemoryStream stream = new MemoryStream();
tryagain:
try
{
Bitmap bmp = new Bitmap(fileName);
bmp.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
}
catch (Exception ex)
{
goto tryagain;
}
return stream.ToArray();
}
}
Full Source | VB.NET
Imports System
Imports System.IO
Imports System.Data
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If FileUpload1.HasFile Then
Dim imgPath As String
FileUpload1.SaveAs("C:\files\" + FileUpload1.FileName)
imgPath = "C:\files\" + FileUpload1.FileName.ToString
If File.Exists(imgPath) Then
Dim id As Integer = 2
Dim content As Byte() = imgStream(imgPath)
Dim connectionString As String = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString()
Dim connection As New SqlConnection(connectionString)
connection.Open()
Dim cmd As New SqlCommand("insert into imagestore (id,img) values ( @id,@img)", connection)
cmd.Parameters.AddWithValue("@id", id)
cmd.Parameters.AddWithValue("@img", content)
cmd.ExecuteNonQuery()
connection.Close()
Label1.Text = "Insert Success !! "
Else
Label1.Text = imgPath + " not found "
End If
Else
Label1.Text = "Please select a file "
End If
End Sub
Private Function imgStream(ByVal fileName As String) As Byte()
Dim stream As New MemoryStream()
tryagain:
Try
Dim bmp As New Bitmap(fileName)
bmp.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
Catch ex As Exception
GoTo tryagain
End Try
Return stream.ToArray()
End Function
End Class
Conclusion
By understanding the Image data type and its capabilities, database developers can effectively manage and store binary data in SQL Server databases, enabling efficient retrieval and manipulation of large binary objects.