Insert/Read image from SQL Database

Here is a few useful links showing the different ways of storing and retrieving an image from MS SQL database.

Forum discussion : sgDotNet forum msdn forum
Blog post from the same forum : Wen Ching

Very good code snippet from Akadia :


// **** Read Image from Filesystem and add it to the Database.

public void AddEmployee(
string plastName,
string pfirstName,
string ptitle,
DateTime phireDate,
int preportsTo,
string photoFilePath)
{

// Read Image into Byte Array from Filesystem
byte[] photo = GetPhoto(photoFilePath);

// Construct INSERT Command
SqlCommand addEmp = new SqlCommand(
“INSERT INTO Employees (“+
“LastName,FirstName,Title,HireDate,ReportsTo,Photo) “+
“VALUES(@LastName,@FirstName,@Title,@HireDate,@ReportsTo,@Photo)”,_conn);

addEmp.Parameters.Add(“@LastName”, SqlDbType.NVarChar, 20).Value = plastName;
addEmp.Parameters.Add(“@FirstName”, SqlDbType.NVarChar, 10).Value = pfirstName;
addEmp.Parameters.Add(“@Title”, SqlDbType.NVarChar, 30).Value = ptitle;
addEmp.Parameters.Add(“@HireDate”, SqlDbType.DateTime).Value = phireDate;
addEmp.Parameters.Add(“@ReportsTo”, SqlDbType.Int).Value = preportsTo;
addEmp.Parameters.Add(“@Photo”, SqlDbType.Image, photo.Length).Value = photo;

// Open the Connection and INSERT the BLOB into the Database
_conn.Open();
addEmp.ExecuteNonQuery();
_conn.Close();
}

// **** Read Image into Byte Array from Filesystem
public static byte[] GetPhoto(string filePath)
{
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

br.Close();
fs.Close();

return photo;
}

// **** Read BLOB from the Database and save it on the Filesystem
public void GetEmployee(string plastName,string pfirstName)
{
SqlCommand getEmp = new SqlCommand(
“SELECT EmployeeID, Photo “+
“FROM Employees “+
“WHERE LastName = @lastName “+
“AND FirstName = @firstName”, _conn);

getEmp.Parameters.Add(“@LastName”, SqlDbType.NVarChar, 20).Value = plastName;
getEmp.Parameters.Add(“@FirstName”, SqlDbType.NVarChar, 10).Value = pfirstName;

FileStream fs; // Writes the BLOB to a file (*.bmp).
BinaryWriter bw; // Streams the BLOB to the FileStream object.
int bufferSize = 100; // Size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.
long retval; // The bytes returned from GetBytes.
long startIndex = 0; // The starting position in the BLOB output.
string emp_id = “”; // The employee id to use in the file name.

// Open the connection and read data into the DataReader.
_conn.Open();
SqlDataReader myReader = getEmp.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
// Get the employee id, which must occur before getting the employee.
emp_id = myReader.GetInt32(0).ToString();

// Create a file to hold the output.
fs = new FileStream(“employee” + emp_id + “.bmp”,
FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.
startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
bw.Write(outbyte);
bw.Flush();

// Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
}

// Write the remaining buffer.
bw.Write(outbyte, 0, (int)retval);
bw.Flush();

// Close the output file.
bw.Close();
fs.Close();
}

// Close the reader and the connection.
myReader.Close();
_conn.Close();
}

9 responses to this post.

  1. Posted by Ali on 2007/12/03 at 2:38 pm

    i want to know the type of column in sql database that i can insert image in it,

    Reply

  2. I use Image sql datatype as you can see from the code above(SqlDbType.Image),but there are also other possibilities like VARBINARY(MAX) NOT NULL.

    Reply

  3. Posted by rakesh on 2008/08/22 at 9:24 am

    i wan’t to copy the image saved in a table to a specific location i.e., vice versa of image reading. plz help.

    Reply

  4. Posted by Igor on 2008/09/13 at 4:12 pm

    How output Image from Sql database to dataGridView

    Reply

  5. Posted by Sarkie on 2009/06/16 at 8:36 am

    Thank you, just what I was looking for!

    Reply

  6. Posted by Prethiviraj on 2010/06/24 at 8:15 am

    i stored a image named(r) in sql server 2005
    using this command

    Insert into Persons(images)
    select * from openrowset (bulk N’D:\r.jpeg’,single_blob) as r

    i dont knw how to retrive it
    any 1 can help me

    Reply

  7. more discussions please

    Reply

  8. Hello, always i used to check blog posts here early in the break of day, since i enjoy to find out more and
    more.

    Reply

  9. Posted by Johna640 on 2014/08/12 at 8:46 am

    Amoxicillin And Clavulanate 250mg With No Prescription in Indianapolis ddgkdcccgead

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: