ARTICLE

Flash Cards Program : How to read and save images in a SQL Server database

Posted by Mike Gold in Articles | ADO.NET in VB.NET on July 20, 2003

Tags: .NET, ADO.NET, DataRow Picture Item, DataTable, displaying flashcards from a database, Fill a DataSet, flashcard information, read and write images to the database, ReadFlashCard, Save Images in a SQL Server Database, VB.NET, Writing Image Data into the Database.Flash Cards Program

I thought it would be fun to write a simple program that displayed flashcards from a database and at the same time, show you how to read and write images to the database. This program is a simple flashcard program that talks to a single table in a Sql Server Database.

Tweet
14531
1

Download Files:

FlashCardsMG.zip

Reader Level: Beginner

Figure 1 - Flashcards read in from an Sql Database

I thought it would be fun to write a simple program that displayed flashcards from a database and at the same time, show you how to read and write images to the database. This program is a simple flashcard program that talks to a single table in a Sql Server Database. The design of the database table is shown below:

Figure 2 - Flashcard DB Design Reverse Engineered Using WithClass 2000

The Picture field is of type varbinary. As seen from the database design diagram, it holds 8000 bytes of data, enough for a square picture of about 89 x 89 pixels. When I created the table using the Server Explorer wizard, I needed to specify the maximum length in order to get the image storage to work correctly. Although the database is not provided with the download, you can easily recreate it using the design diagram above. Below is also a snapshot from server explorer of the data:

Figure 3 - Snapshot from the Server Explorer of the Data in this sample

A lot of the coding for this example is done visually. I created the sql server adapter and sql connection simply by dragging the table from the server explorer into the Design View of the Form. At this point, you have almost everything you need to read the flashcard information into the Form. The code for reading in the table in Figure 3 is shown in the listing below:

Public Function ReadFlashCard(ByVal x As Integer) As Boolean
Try
' dispose of the old image and set the picture box to null
If Not (pictureBox1.Image Is Nothing) Then
pictureBox1.Image.Dispose()
End If
pictureBox1.Image = Nothing
' form a query that will only select the flashcard we are interested in
sqlDataAdapter1.SelectCommand.CommandText = "Select * From Flashcards Where PrimaryKey = " + Convert.ToString(x)
' Fill the dataset using the query from the adapter
Dim ds As New DataSet
sqlDataAdapter1.Fill(ds, "Flashcards")
' Hide the labels that contain the answer to the flashcard
label1.Visible = False
label2.Visible = False
' if no row is returned it means we reached the end or something is wrong
If ds.Tables(0).Rows.Count = 0 Then
Return False
End If ' populate the hidden answers: the language word and the pronunciation
label1.Text = ds.Tables(0).Rows(0)("LearnLanguage").ToString()
label2.Text = ds.Tables(0).Rows(0)("Pronunciation").ToString()
' now we need to get the picture from the DataRow
' and assign it to a byte array
Dim MyData As Byte() = Nothing
MyData = CType(ds.Tables(0).Rows(0)("Picture"), Byte())
' After retrieving the byte array, we want to get the
' number of elements of the array for use in writing the array
Dim ArraySize As New Integer
ArraySize = MyData.GetUpperBound(0)
' Create a Filestream for writing the byte array to a gif file (the original format in this case)
Dim fs As New FileStream("tmp.gif", FileMode.OpenOrCreate, FileAccess.Write)
' Write the stream of data that we read in from the database to the filestream and close it.
' This will create the file tmp.gif, which we can use to display in the picturebox
fs.Write(MyData, 0, ArraySize + 1) ' don't ask me why, but I had to add 1 here for this to work
fs.Close()
' Assign the temporary gif file to the picture box
pictureBox1.Image = New Bitmap("tmp.gif")
Return True
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
Return False
End Try
End Function 'ReadFlashCard

Reading in the byte data is as simple as assigning the DataRow Picture Column to a byte array. Once we have the byte array, we can use it to create an image file (in this program we use gif format, although you are free to change it to use your own). The FileStream class serves our purpose well here, because it can be used to create a file from byte data. Once we've created the temporary gif file on our disk, we can assign it to the picturebox component.

Writing Image Data into the Database is almost as easy. In this application, we've created a second form used to populate the database. This form serves as almost a modeless dialog to allow us to put our flashcards easily into the Sql Server Database:

Figure 4- Second Form used to Populate the Database

The Code for writing to the Database is shown below:

Private Sub button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
' Fill a DataSet with existing Flashcards
Dim ds As New DataSet
sqlDataAdapter1.Fill(ds, "Flashcards")
Dim TheTable As DataTable = ds.Tables(0)
' Create a new row for the FlashCard Table in Memory
Dim aRow As DataRow = TheTable.NewRow()
' Insert the information from the dialog into the Flashcard Table
' Such as the Primary Key, Language Word, Translation, and Pronunciation
aRow("PrimaryKey") = TheTable.Rows.Count + 1
aRow("LearnLanguage") = textBox1.Text
aRow("FirstLanguage") = textBox2.Text
aRow("Pronunciation") = textBox3.Text
' Create a new FileStream for reading data from the image file in which the PictureBox populated
' Its data from (the FileName is maintained after the picture button is pressed and a picture is selected).
Dim fs As New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Read)
' Read the Data into the Byte Array
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, CInt(fs.Length))
fs.Close()
' Assign the DataRow Picture Column to the Byte Array to populate it in the DataRow
aRow("Picture") = MyData
' Add the DataRow to the DataTable
TheTable.Rows.Add(aRow)
' Write all the data (including the picture) to the Flashcards Sql Server database
sqlDataAdapter1.Update(ds, "Flashcards")
MessageBox.Show("Flashcard Added.")
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub 'button2_Click

The code above for writing a picture to the database is very similar to reading, only performed in the opposite direction. First you read the data into a byte array using the FileStream class. Then you assign the DataRow Picture Item to the byte array data that you just read. Finally, call Update on the Adapter to force it to write the picture data (along with the textual data) out to the database.

Improvements

One thing that would be nice to do in this program is to allow for font changes and store the font information in the database. This way you could adapt this program to work with any language and any language character set.