Save Image In SQL Database Through Vb.net form BY SH3H@N



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

0 comments:

Post a Comment

Powered by Blogger.