C# Application and SQL Server Image Data Type
SQL Varbinary(Max) Data Type and C# Windows Application
This article demonstrates how to create a SQL Server table with the data type Varbinary(Max), add JPG photos to the table and then retrieve the photos in a C# Windows application.
To begin, we will create a simple Windows Form Project in C# using a ListBox and corresponding pictures in a Picture Box.
If this were done manually (including the jpg files in the project), we would have to add new images and new listbox items manually and rebuild the application everytime. If a SQL database is used to provide the pictures and the listbox details, the information can be added to a table using a string and the photos can be added using varbinary(max) in the table. SQL Server 2008R2 can still use the Image datatype (vs. Varbinary(max)), however it is not recommended as the Image datatype will be deprecated in a future release of SQL Server.
Adding the JPG files to SQL Server
CREATE TABLE picCatalog
( Id int,
picName varchar(50) not null,
Photo varbinary(max) not null)
Some sample inserts to put the data into the table using BulkColumn Openrowset syntax
insert into picCatalog (Id, picName, Photo)
SELECT 1,'Captain Cook Monument', BulkColumn
FROM Openrowset
( Bulk 'C:\Data2\Pics\spinnerdolphinscaptcook.jpg', Single_Blob) as picture
insert into picCatalog (Id, picName, Photo)
SELECT 2,'Diamond Head', BulkColumn
FROM Openrowset
( Bulk 'C:\Data2\Pics\DiamondHeadViewFromTop1.jpg', Single_Blob) as picture
insert into picCatalog (Id, picName, Photo)
SELECT 3,'Makapuu Pt Islands', BulkColumn
FROM Openrowset
( Bulk 'C:\Data2\Pics\MakapuuPtRabbitandTurtleIslands.jpg', Single_Blob) as picture
insert into picCatalog (Id, picName, Photo)
SELECT 4,'Manoa Falls', BulkColumn
FROM Openrowset
( Bulk 'C:\Data2\Pics\ManoaFallsTheWaterfall.jpg', Single_Blob) as picture
insert into picCatalog (Id, picName, Photo)
SELECT 5,'Koko Crater', BulkColumn
FROM Openrowset
( Bulk 'C:\Data2\Pics\Kokocrater.jpg', Single_Blob) as picture
A SELECT statement on the table picCatalog verifies the data has been inserted
Create the C# Windows Application to Display the SQL Server Images
Create a Windows Project and add a ListBox and PictureBox to the Form.
Create a separate Project as a Class to deliver the list data and a selected photo to the listbox and pictureBox.
Add a few References and Using statements to the top of the class code.
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System.IO;
using System.Collections;
First, a static method to return a List to the ListBox object in the form. A SQLConnection and Command Object to the retrieve the Id and PicName from the table into a SqlDataReader. Iterate through the Reader and use the Add method of a List object to get all of the items from the table to be returned to the ListBox.
public static List<string>
sqlGetData()
{
string path = @"Data Source=YourSQLServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(path);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT Id, PicName FROM dbo.picCatalog", conn);
SqlDataReader sql = cmd.ExecuteReader();
List
while(sql.Read())
{
il.Add(sql["Id"].ToString() + "-" + sql["picName"].ToString());
}
conn.Close();
return il;
}
Another static method to retrieve each Photo using the Id number as criteria from the table into the pictureBox. The command object will need to be cast into a byte array and from a byte array into a Stream object. This article uses a Memory Stream object. Finally an Image object is created from a new Bitmap that uses the specified MemoryStream.
public static System.Drawing.Image getPhoto (int id)
{
string path = @"Data Source=SHELLIWIN7\SQL_SHELLI;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(path);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT Photo FROM dbo.picCatalog WHERE Id = " + id, conn);
byte [] bArray = (byte[])cmd.ExecuteScalar();
MemoryStream fs = new MemoryStream(bArray);
System.Drawing.Image img = new System.Drawing.Bitmap(fs);
fs.Flush();
fs.Close();
conn.Close();
return img;
}
Add a reference to the Class (DLL) file created
Add a using statement for the picData reference at the top. The Form Load event will use the sqlGetData method to populate the ListBox with the names of all of the photos in the table and the getPhoto method to display the 1st photo in the table.
using picData;
private void Form1_Load(object sender, EventArgs e)
{
listBox1.DataSource = picData.picData.sqlGetData();
pictureBox1.Image = picData.picData.getPhoto(1);
}
The listBox1 SelectedIndexChanged event will use the substring and parse methods of the string value in the listbox to extract the Id value of the photo in the database. The getPhoto method is again used to get the photo matching the identifier in the ListBox.
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
int i = 0;
string s = listBox1.SelectedValue.ToString();
s = s.Substring(0,s.IndexOf("-"));
i = Int32.Parse(s);
pictureBox1.Image = picData.picData.getPhoto(i);
}
Run the Program and click on the List Box to check that the Images change in the PictureBox.
