Save Images in SQL
database through vb.net form
Ever Wondered How to save images in Sql server with vb.net
form? Well here is the solution. After reading this tutorial you will be able
to.
- Save
Images in Sql Server database with vb.net form.
- Create
Stored Procedure In SQL
- Execute
Stored Procedure through vb.net form.
So Lets Start!
The task is easy if you know how. So to make thing even easier
I have decided divide this tutorial into section.
- Setting
up Sql server database
- Designing
vb.net form
- Final
Coding
Setting Up Sql Server Database
If you have already created a database create a new table in
it or create a new database (Right-click on database and select “new database”)
and create a new table and create following columns.
Column Name Data
type Allow Nulls
- Name varchar(50) yes
- image image yes
And save the table by the name of “save_image”.
Like this
If you open the table you’ve just created (right-click on
it, select “open table”) you will see two columns by the name you have
provided.
When that is done click on “New Query” on top left of the
screen
But before doing anything make sure that is our database we
are making changes in, select correct database
After selecting correct database, we have to create a stored
procedure which will make our process easier.
So write the following your query area.
create procedure spx_images(
@Name varchar(50),
@image image)
as
begin
insert into save_image
(name,image)
values(@name,@image)
end
And press F5
Message
will appear at bottom “Command(s) completed successfully.”
So basically that’s
it. Your Setup of SQL server has been set.
Designing VB.Net Form
Open Vb.Net
Create a new project and give it any name, I am going name
it “Save image in database through vb.net”.
Set your form size property to: 632, 475
And import the following controls on your form in correct
order.
No. Control Type Name
1 Label label1
2 Textbox name_to_save
3 Button bttn_save
4 Label label2
5 Textbox name_to_search
6 Button bttn_search
7 Picture box Picturebox1
8 Button Exti
9 Open
File Dialog box openfiledialog1
10 Button browse
11 Label label1
12 Textbox txtimg
Note: control no 8’s
name is EXTI not EXIT.
Control no
7’s name is misspelled in image
And with this your Designing section is complete.
Final Coding
Switch to code view and write the following on top of “Public Class Form1”
Imports System.IO
Imports
System.Data.SqlClient
Imports System.Runtime.InteropServices
These are the classes required for our project and to
communicate with SQL server database.
It’s time to declare some important variables which will be used
in our project. So declare these variables after “Public Class Form1”.
Dim cn As New SqlConnection("Your Connection String Here.")
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Note: Write your connection string where Your Connection String Here is written.
Don’t know how to get connection string? Get a quick and short tutorial here
EXPLANATION:
“cn” is our connection string
by which our form will communicate with SQL server.
“cmd” is our sql command
variable by which we can execute and command or query in SQl.
“dr” is our data reader which
will read data from sql so we can display it on our form.
Once the variable is declared
lets start to work on saving image,
Goto designer view and double-click on “browse” button (control no 10)
You will be switched to it’s Code
handler
Write the following code.
OpenFileDialog1.ShowDialog()
txtimg.Text =
OpenFileDialog1.FileName
PictureBox1.Image = Image.FromFile(txtimg.Text)
Explaination:
When you click on browse
button, Open file dialog box will show up and you can select any picture with
it. As you close the openfiledialogbox, txtimg(which is a text box) will be
filled with the path of file(here: Image) you have selected, and picturebox1
will display image from txtimg which already have the file path you have selected.
If you want to check how are
things going you can press F5 to run you program But add Connection string (string
which we have declared on top with “cn” variable) before you do otherwise you
will get error.
So now you are able to
display any picture to picture box.
Now once that is done lets
save it in database
Again goto designer view and
double-click on “save” button (control no: 3)
and write the following codes
in it.
'closing connection for safety
cn.Close()
'checking if
we have a image path to save or not
If
txtimg.Text = Nothing Then
MsgBox("Please
Select a Image")
Exit
Sub
End If
'But the
image is in use by our own programm, we have to shut the connection of image
and our form
'only the we
will be able to add image in database
PictureBox1.Image.Dispose()
PictureBox1.Image = Nothing
'We can only
save images in database which is in binary format(ones and zeros)
'but the
regular images we see are in pixel format
'Luckily our
memory Stream have the capability of converting
'and binary
data to pixel and pixel to binary
'so now we
only need to put our image on memory stream and it will automatically
'be converted
to pixels which we want.
Dim fs As New
FileStream(txtimg.Text, FileMode.Open)
Dim
data() As Byte
= New [Byte](fs.Length) {}
fs.Read(data, 0, fs.Length)
'Saving the
image in database by stored procedure we created before
cn.Open()
Dim cmd
As New
System.Data.SqlClient.SqlCommand("spx_images")
cmd.Connection = cn
cmd.CommandType =
CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Name", name_to_save.Text)
cmd.Parameters.AddWithValue("@image", data)
cmd.ExecuteNonQuery()
cn.Close()
fs.Close()
MsgBox("saved")
getimage()
And with this code our image
is converted to binary and saved into our database. Just select the image by
clicking on “browse” button and enter the name by which you want it to save in
database (important: otherwise the image will be saved without any name and you
will have problem getting it back) and click on save.
But now you must be thinking
how to get it back? You can see the last line of the above code that I already
have a command for that.
So to read the image along
with proper name we will use the search option we created before. To get image
write or paste this query anywhere in your code section but not between any
other sub. The best bet will be to write it in the end before “End Class”
Public Sub getimage()
cn.Close()
cn.Open()
Dim cmd
As SqlCommand = New
SqlCommand("select name,image from save_image
where name = '" + name_to_save.Text + "'",
cn)
dr = cmd.ExecuteReader
dr.Read()
If
dr.HasRows = True Then
Dim
arrayimage As Byte()
= DirectCast(dr("image"),
Byte())
Dim
ms As New
MemoryStream(arrayimage)
PictureBox1.Image = Image.FromStream(ms)
name_to_save.Text = dr(0)
End If
End Sub
And again goto designer view,
Double-click on “Search” button (control no: 6)
And write this in its code
handler.
cn.Close()
cn.Open()
Dim cmd
As SqlCommand = New
SqlCommand("select name,image from save_image
where name = '" + name_to_search.Text + "'",
cn)
dr = cmd.ExecuteReader
dr.Read()
If
dr.HasRows = True Then
Dim
arrayimage As Byte()
= DirectCast(dr("image"),
Byte())
Dim
ms As New
MemoryStream(arrayimage)
PictureBox1.Image =
Image.FromStream(ms)
name_to_save.Text = dr(0)
End If
That’s it. Just enter the
name of image you have saved in the database in “name_to_search” textbox
(control no: 5) and click on search button. Your image will be displayed.
And yes one more thing. Goto
designer view double-click on “Exit” button (control no: 9) and write the
following code in it.
Application.Exit()
And with the code above your
project is complete. Press F5 and start saving images in database.
Tutorial Written By:
Shehan Abid (SH3H@N)
For:
SH3HANWARES.blogspot.com
+Control+Names.jpg)
