Storing images in SQL Server 2005 and retrieving them from VB.NET

Tags:
SQL Server 2005
VB.NET
How can I store images in SQL Server 2005 and retrieve them from VB.NET?
ASKED: December 29, 2008  7:52 PM
UPDATED: October 21, 2013  6:25 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This sample code explains you how you can store images in SQL Server database. It uses ADO.Net System.Data.SqlClient namespace. Images can be stored in sql server using Sql parameters.

How to Store Image in SQL Server table

To store an image in to sql server, you need to read image file into a byte array. Once you have image data in byte array, you can easity store this image data in sql server using sql parameters. Following code explains you how to do this.
Collapse

private void cmdSave_Click(object sender, EventArgs e)
{
try
{
//Read Image Bytes into a byte array
byte[] imageData = ReadFile(txtImagePath.Text);

//Initialize SQL Server Connection
SqlConnection CN = new SqlConnection(txtConnectionString.Text);

//Set insert query
string qry = "insert into ImagesStore (OriginalPath,ImageData) values(@OriginalPath, @ImageData)";

//Initialize SqlCommand object for insert.
SqlCommand SqlCom = new SqlCommand(qry, CN);

//We are passing Original Image Path and Image byte data as sql parameters.
SqlCom.Parameters.Add(new SqlParameter("@OriginalPath", (object)txtImagePath.Text));
SqlCom.Parameters.Add(new SqlParameter("@ImageData", (object)imageData));

//Open connection and execute insert query.
CN.Open();
SqlCom.ExecuteNonQuery();
CN.Close();

//Close form and return to list or images.
this.Close();
}
Following code explains how to read image file in to a byte array.
Collapse
//Open file in to a filestream and read data in a byte array.
byte[] ReadFile(string sPath)
{
//Initialize byte array with a null value initially.
byte[] data = null;

//Use FileInfo object to get file size.
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;

//Open FileStream to read file
FileStream fStream = new FileStream(sPath, 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);

return data;
}

How to read image data from SQL Server table

To read images from SQL Server, prepare a dataset first which will hold data from SQL Server table. Bind this dataset with a gridview control on form.

Collapse

void GetImagesFromDatabase()
{
try
{
//Initialize SQL Server connection.
SqlConnection CN = new SqlConnection(txtConnectionString.Text);

//Initialize SQL adapter.
SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImagesStore", CN);

//Initialize Dataset.
DataSet DS = new DataSet();

//Fill dataset with ImagesStore table.
ADAP.Fill(DS, "ImagesStore");

//Fill Grid with dataset.
dataGridView1.DataSource = DS.Tables["ImagesStore">;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

Once you have image data in grid, get image data from grid cell. Alternatively you can also get image data from Dataset table cell.

Collapse

//When user changes row selection, display image of selected row in picture box.
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
try
{
//Get image data from gridview column.
byte[] imageData = (byte[])dataGridView1.Rows[e.RowIndex].Cells["ImageData">.Value;

//Initialize image variable
Image newImage;
//Read image data into a memory stream
using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
{
ms.Write(imageData, 0, imageData.Length);

//Set image variable value using memory stream.
newImage = Image.FromStream(ms, true);
}

//set picture
pictureBox1.Image = newImage;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

If you want you can extend this code to save image from Picture Box to a local image file.
Collapse

//Store image to a local file.
pictureBox1.Image.Save("c:\test_picture.jpg",System.Drawing.Imaging.ImageFormat.Jpeg);

Points of Interest

If you see frmImageStore in design mode, I have placed picturebox1 into a panel. This panel’s AutoScroll property is set to True and SizeMode property of PictureBox1 is set to True. This allows picturebox to resize itself to the size of original picture. When picturebox’s size is more than Panel1′s size, scrollbars becomes active for Panel.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chejane
    hi! can somebody help me about my problem? I want to retrieve the picture from sql server database and display it on a website using VB.net. coz' my boss want to display the student pictures, infos. and grades of the students. now he want me to retrieve the pictures of the students from sql database and display it on our website. pls help me do this project. i have a table in my database student id (nchar8) student name (nchar30) Student Photo (varbinary(max)) help me to encode the VB to business intelligence development studio pls?
    10 pointsBadges:
    report
  • Palanivelkavi
    i have following type of table r1:id,r2:name,r3:image following up 5 person detais in SQL I need asp Coding for when i entered name want to show that person Image.
    30 pointsBadges:
    report
  • Palanivelkavi
    ----------CODE FRO--------.ASHX----------------------- Imports System Imports System.Configuration Imports System.Web Imports System.IO Imports System.Data Imports System.Data.SqlClient Public Class ShowImage Implements IHttpHandler Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest Dim empno As Int32 If Not context.Request.QueryString("id") Is Nothing Then empno = Convert.ToInt32(context.Request.QueryString("id")) Else Throw New ArgumentException("No parameter specified") End If context.Response.ContentType = "image/jpeg" Dim strm As Stream = ShowEmpImage(empno) Dim buffer As Byte() = New Byte(4095) {} Dim byteSeq As Integer = strm.Read(buffer, 0, 4096) Do While byteSeq > 0 context.Response.OutputStream.Write(buffer, 0, byteSeq) byteSeq = strm.Read(buffer, 0, 4096) Loop 'context.Response.BinaryWrite(buffer); End Sub Public Function ShowEmpImage(ByVal empno As Integer) As Stream Dim conn As String = ConfigurationManager.ConnectionStrings("EmployeeConnString").ConnectionString Dim connection As SqlConnection = New SqlConnection(conn) Dim sql As String = "SELECT empimg FROM image WHERE empid = @ID" Dim cmd As SqlCommand = New SqlCommand(sql, connection) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@ID", empno) connection.Open() Dim img As Object = cmd.ExecuteScalar() Try Return New MemoryStream(CType(img, Byte())) Catch Return Nothing Finally connection.Close() End Try End Function Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property End Class
    30 pointsBadges:
    report
  • Palanivelkavi
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Image1.ImageUrl = "~/ShowImage.ashx?id=" & ID End Sub End Class
    30 pointsBadges:
    report
  • BFullen
    He said VB.NET. I am so tired of people asking for VB.NET solutions and people posting a C# solution. There is a reason we are asking for a VB.NET solution!!!
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following