If you are new to database programming and ADO.NET, you may want to look at the ADO.NET section of C# Corner. Plenty of source code samples and tutorials are available for free. You might want to check out my book for ADO.NET beginners: A Programmer's Guide to ADO.NET in C# (published by APress).
First we need to create a database. We start by creating a new Access database called AppliedAdoNet.mdb and adding a table to the database called "Users." The database table schema should look like Figure 15.7 Microsoft Access stores binary large objects (BLOBs) using the OLE object data type.
To make our application a little more interactive and user-friendly, let's create a Windows application and add a text box, three button controls, and a PictureBox control. The final form looks like Figure 15.8. As you can probably guess, the Browse Image button allows users to browse for bitmap files; the Save Image button saves the image to the database; and the Read Image button reads the first row of the database table, saves binary data as a bitmap, and displays the image in the picture box.
Before we write code on button clicks, we need to define the following variables:
//User-defined variable
private Image curImage = null;
private string curFileName = null;
private string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; "+
"Data Source =F:\\AppliedAdoNet.mdb";
private string savedImageName =
"F:\\ImageFromDb.BMP";
Do not forget to add references to the System.IO and System.Data.OleDb namespaces:
using System.IO;
using System.Data.OleDb;
![Figure 15.7.gif]()
Figure 15.7: Users table schema
![Figure 15.8.gif]()
Figure 15.8: Reading and writing images in a database form
The stream-related classes are defined in the System.IO namespace. We will use the OLE DB data provider, which is defined in the System.Data.OleDb namespace, to work with our Access database.
The Browse Image button click code is given in Listing 15.9, which simply browses bitmap files and saves the file name in CurFileName. We can set filter to access the file formats we want.
Listing 15.9: the Browse button click event handler
private void BrowseBtn_Click (object sender, system.EventArgs e)
{
OpenFileDialog openDlg = new OpenFileDialog();
openDlg.Filter = "All Bitmap files | *.bmp";
string filter = openDlg.Filter;
openDlg.Title ="Open a Bitmap File";
if (openDlg.ShowDialog() == DialogResult.OK)
{
curFileName = openDlg.FileName;
textBox1.Text = curFileName;
}
}
The Save Image button code given in Listing 15.10 creates a FileStream object from the bitmap file, opens a connection with the database, adds a new data row, set its values, and saves the row back to the database.
Listing 15.10: The Save Image button click event handler
private void SaveImageBtn_Click (object sender, System.EventArgs e)
{
//Read a bitmap's contents in a stream
FileStream fs = new FileStream (curFileName,
FileMode.OpenOrCreate, FileAccess.Read);
byte[] rawData = new byte [fs.Length];
fs.Read (rawData, 0, System.Convert.ToInt32 (fs.Length));
fs.Close();
//Construct a SQL string and a connection object
string sql = "SELECT * FROM Users";
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = connectionString;
//Open the connection
if (conn.State != ConnvetionState.Open)
conn.Open();
//Create a data adapter and data set
OleDbDataAdapter adapter =
new PleDbDataAdapter (sql, conn);
OleDbCommandBuilder cmdBuilder =
new OleDbCommandBuilder (adapter);
DataSet ds = new DataSet ("Users");
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
//Fill the data adapter
adapter.Fill (ds, "Users");
string userDes =
"Mahesh Chand is a founder of C# Corner";
userDes += "Author: 1. A Programmer's Guide to ADO.NET;";
userDea += ", 2. Applied ADO.NET.";
//Create a new row
DataRow row = ds.Tables ["Users"].NewRow();
row["UserName] ="Mahesh Chand";
row["UserEmail"]= [email protected];
row["UserDescription"] = userDes;
row["UserPhoto"] = rawData;
//Add the row to the collection
ds.Tables ["Users"].Rows.Add (row);
//Save changes to the database
adapter.Update (ds, "Users");
//Clean up connection
if(conn!=null)
{
if (con.State == ConnectionState.Open)
conn.Close();
//Dispose of connection
conn.Dispose();
}
MessageBox.Show ("Image Saved");
}
Once the data has been saved, the next step is to read data from the database table, save it as a bitmap again, and view the bitmap on the form. We can view an image using the Graphics.DrawImage method or using a picture box. Our example uses a picture box.
The code for reading binary data is shown in Listing 15.11. We open a connection, create a data adapter, fill a data set, and get the first row of the Users table. If you want to read all the images, you may want to modify your application or loop through all the rows. Once a row has been read, we retrieve the data stored in the UserPhoto column in a stream and save it as a bitmap file. Later we view that bitmap file in a picture box by setting its Image property to the file name.
Listing 15.11: Reading images from a database
private void ReadImageBtn_Click(object sender, System.EventArgs e)
{
//Construct a SQL string and a connection object
string sql = "SELECT * FROM Users";
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = connectionString;
//Open the connection
if (conn.State != ConnectionState.Open)
conn.Open();
//Create a data adapter and data set
OleDbDataAdapter adapter =
new OleDbDataAdapter(sql, conn);
OleDbCommandBuilder cmdBuilder =
new OleDbCommandBuilder(adapter);
DataSet ds = new DataSet("Users");
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
//Fill the data adapter
adapter.Fill(ds, "Users");
//Get the first row of the table
DataRow row = ds.Tables["Users"].Rows[0];
//Read data in a stream
byte[] rawData = new byte[0];
rawData = (byte[])row["UserPhoto"];
int len = new int();
len = rawData.GetUpperBound(0);
//Save rawData as a bitmap
FileStream fs = new FileStream
(savedImageName, FileMode.OpenOrCreate,
FileAccess.Write);
fs.Write(rawData, 0, len);
//Close the stream
fs.Close();
//View the image in a pciture box
curImage = Image.FromFile(savedImageName);
pictureBox1.Image = curImage;
//Clean up connection
if (conn != null)
{
if (conn.State == ConnectionState.Open)
conn.Close();
//Dispose of connection
conn.Dispose();
}
}
To see the program in action, we select the MyPhoto.bmp file by using the Browse Image button, and we click the Save Image button. When we open the database, we see that a new record has been added to the Users table. When we click on the Read Image button, a new ImageFromDb.bmp file is added to the current folder. The output is shown in figure 15.9.
![Figure 15.9.jpg]()
Figure 15.9: Displaying a bitmap after reading data from a database