Retrieve an image from database in VB.NET

This article shows how to insert and retrieve an image from database with VB.NET.
  • 28421
In this article you will learn how to retrieve an image from database SQL Server with VB.NET.

Inserting an image in database

Creating a table in SQL Server.

Table name student and database name master.

use master

go

create table student

(

name varchar(20),

rollno int,

photo image

);

go

select * from student;

go

Table has created in SQL Server.

Now come to the front end window forms application in VB.NET.

Taking two level, two textbox and three button and one picture box and opendialog control on the form. 
 

database1.gif 
 

Figure 1.

Now double click on the button named browse and add the following code in c#.

private void button1_Click(object sender, EventArgs e)

        {

            openFileDialog1.Filter = "Image Files|*.gif;*.jpg;*.png;*.bmp";

            openFileDialog1.ShowDialog();

            pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);

        }

 

code in visual Basic.

 

private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "Image Files|*.gif;*.jpg;*.png;*.bmp";
openFileDialog1.ShowDialog();
pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);
}

Now double click on the button save and add the following c# code.

private void button2_Click(object sender, EventArgs e)

        {

            string str = "Data Source=.;uid=sa;pwd=wintellect;database=master";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            string sql = "INSERT INTO student VALUES(@rollno,@name,@photo)";

            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.AddWithValue("@rollno", textBox1.Text);

            cmd.Parameters.AddWithValue("@name", textBox2.Text);

 

            MemoryStream ms = new MemoryStream();

            pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);

            byte[] data = ms.GetBuffer();

            SqlParameter p = new SqlParameter("@photo", SqlDbType.Image);

            p.Value = data;

            cmd.Parameters.Add(p);

            cmd.ExecuteNonQuery();

            MessageBox.Show("record has been saved","Save", MessageBoxButtons .OK);

            con.Close();

 

        }

 

code in visual Basic.

 

Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim str As String = "Data Source=.;uid=sa;pwd=wintellect;database=master"

        Dim con As New SqlConnection(str)

        con.Open()

        Dim sql As String = "INSERT INTO student VALUES(@rollno,@name,@photo)"

        Dim cmd As New SqlCommand(sql, con)

        cmd.Parameters.AddWithValue("@rollno", textBox1.Text)

        cmd.Parameters.AddWithValue("@name", textBox2.Text)

        Dim ms As New MemoryStream()

        pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat)

        Dim data As Byte() = ms.GetBuffer()

        Dim p As New SqlParameter("@photo", SqlDbType.Image)

        p.Value = data

        cmd.Parameters.Add(p)

        cmd.ExecuteNonQuery()

        MessageBox.Show("record has been saved", "Save", MessageBoxButtons.OK)

        con.Close()

    End Sub

  
 

Now execute the application press ctrl+F5.

 
 

  database2.gif

 

Figure 2.


 

Now click on the save button message will be display.


 

database3.gif
 

Figure 3.

Now open the sql server record has been saved with the image.

Retrieving an image in database

Now retrieve the image from database to form.

Taking two level, two textbox, one picture box, one button and combo box on the form. form look like this.

r1.gif
 

Figure 4.

Now double click on the form and add the following code in c#.
 


 

private void Form2_Load(object sender, EventArgs e)

        {

            string st = "Data Source=.;uid=sa;pwd=wintellect;database=master";

            SqlConnection con=new SqlConnection (st);

            con.Open ();

            string se= "select rollno from student";

            SqlCommand cmd=new SqlCommand (se,con);

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                comboBox1.Items.Add(dr["rollno"]);

               

            }

            con.Close();

           }

Code in VB.NET.

Private Sub Form2_Load(sender As Object, e As EventArgs)

        Dim st As String = "Data Source=.;uid=sa;pwd=wintellect;database=master"

        Dim con As New SqlConnection(st)

        con.Open()

        Dim se As String = "select rollno from student"

        Dim cmd As New SqlCommand(se, con)

        Dim dr As SqlDataReader = cmd.ExecuteReader()

        While dr.Read()

 

               comboBox1.Items.Add(dr("rollno"))

        End While

        con.Close()

End Sub

 

Now right click on combo box and select property->event->comboBox1_SelectionChangeCommitted.

r3.gif
 

Figure 5.

Now double click on event  comboBox1_SelectionChangeCommitted and add following c# code.

private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)

        {

            SqlConnection cn = new SqlConnection();

            cn.ConnectionString = "Data Source=.;uid=sa;pwd=wintellect;database=master";

            cn.Open();

            string sql = "Select * from student where rollno=" + comboBox1.SelectedItem;

            SqlCommand cmd = new SqlCommand(sql, cn);

            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.HasRows)

            {

                dr.Read();

                textBox2.Text = dr["name"].ToString();

                byte[] data = (byte[])dr["photo"];

                MemoryStream ms = new MemoryStream(data);

                pictureBox1.Image = Image.FromStream(ms);

            }

            cn.Close();

        }


 

 Code in VB.NET.

Private Sub comboBox1_SelectionChangeCommitted(sender As Object, e As EventArgs)

            Dim cn As New SqlConnection()

            cn.ConnectionString = "Data Source=.;uid=sa;pwd=wintellect;database=master"

            cn.Open()

            Dim sql As String = "Select * from student where rollno=" + comboBox1.SelectedItem

            Dim cmd As New SqlCommand(sql, cn)

            Dim dr As SqlDataReader = cmd.ExecuteReader()

            If dr.HasRows Then

                        dr.Read()

                        textBox2.Text = dr("name").ToString()

                        Dim data As Byte() = DirectCast(dr("photo"), Byte())

                        Dim ms As New MemoryStream(data)

                        pictureBox1.Image = Image.FromStream(ms)

            End If

            cn.Close()

End Sub

Now execute the application press ctrl+F5.

r2.gif
 

Figure 6.
 

Now we select 6 roll no from the combo box and name and image will be display according to roll no from database.

r4.gif
 

Figure 7.
 

The above form display name and image from database according to combo box values or roll no. 

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.