Access/Store images (binary data) in database using ADO.NET

by Jagadish Pulakhandam on 9/1/2011 3:09:56 PM
Rated 0 from 0 votes
Brief: Demonstrates on retrieving / saving images in database using ADO.NET
Posted to: Manipulating (insert/update/delete) Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates on the following:
  • How to retrieve an image from the database and show it on Windows Form
  • How to store an image in to database from a Windows Form
NOTE: You need to create a table with a binary column to test this sample.

Screen shot:



Source Code:

001.using System;
002.using System.Collections.Generic;
003.using System.ComponentModel;
004.using System.Data;
005.using System.Drawing;
006.using System.Linq;
007.using System.Text;
008.using System.Windows.Forms;
009.using System.Data.SqlClient;
010.using System.IO;
011. 
012.namespace CSWorkingWithBinaryData
013.{
014.    public partial class Form1 : Form
015.    {
016.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=sql2008";
017. 
018.        public Form1()
019.        {
020.            InitializeComponent();
021.        }
022. 
023.        private void btnBrowse_Click(object sender, EventArgs e)
024.        {
025.            this.OpenFileDialog1.ShowDialog();
026.        }
027. 
028.        private void OpenFileDialog1_FileOk(object sender, CancelEventArgs e)
029.        {
030.            this.txtFilePath.Text = this.OpenFileDialog1.FileName;
031.        }
032. 
033.        private void btnSave_Click(object sender, EventArgs e)
034.        {
035.            if (string.IsNullOrEmpty(this.txtEmpno.Text))
036.            {
037.                MessageBox.Show("Provide Empno");
038.                return;
039.            }
040.            if (string.IsNullOrEmpty(this.txtFilePath.Text))
041.            {
042.                MessageBox.Show("Provide File path to upload...");
043.                return;
044.            }
045. 
046.            try
047.            {
048.                byte[] b = File.ReadAllBytes(this.txtFilePath.Text);
049.                if (IsRowExists(this.txtEmpno.Text))
050.                {
051.                    using (SqlCommand cmd = new SqlCommand("UPDATE empImages SET empImage=@empImage WHERE empno = @empno", new SqlConnection(CONNSTR)))
052.                    {
053.                        cmd.Parameters.AddWithValue("@empno", this.txtEmpno.Text);
054.                        cmd.Parameters.AddWithValue("@empImage", b);
055.                        cmd.Connection.Open();
056.                        MessageBox.Show("Updated rows: " + cmd.ExecuteNonQuery());
057.                    }
058.                }
059.                else
060.                {
061.                    using (SqlCommand cmd = new SqlCommand("INSERT INTO empImages VALUES (@empno, @empImage)", new SqlConnection(CONNSTR)))
062.                    {
063.                        cmd.Parameters.AddWithValue("@empno", this.txtEmpno.Text);
064.                        cmd.Parameters.AddWithValue("@empImage", b);
065.                        cmd.Connection.Open();
066.                        MessageBox.Show("Added rows: " + cmd.ExecuteNonQuery());
067.                    }
068.                }
069.            }
070.            catch (Exception ex)
071.            {
072.                MessageBox.Show(ex.Message);
073.            }
074. 
075.        }
076. 
077.        private void btnShow_Click(object sender, EventArgs e)
078.        {
079.            if (string.IsNullOrEmpty(this.txtEmpno.Text))
080.            {
081.                MessageBox.Show("Provide Empno");
082.                return;
083.            }
084. 
085.            try
086.            {
087.                using (SqlCommand cmd = new SqlCommand("SELECT empImage FROM empImages WHERE empno = " + this.txtEmpno.Text, new SqlConnection(CONNSTR)))
088.                {
089.                    cmd.Connection.Open();
090.                    object r = cmd.ExecuteScalar();
091.                    if (r != null)
092.                    {
093.                        byte[] b = (byte[]) r;
094.                        using (MemoryStream ms = new MemoryStream(b, 0, b.Length))
095.                        {
096.                            ms.Write(b, 0, b.Length);
097.                            this.PictureBox1.Image = Image.FromStream(ms, true);
098.                        }
099.                    }
100.                }
101.            }
102.            catch (Exception ex)
103.            {
104.                MessageBox.Show(ex.Message);
105.            }
106. 
107.        }
108. 
109. 
110.        private bool IsRowExists(string empno)
111.        {
112.            using (SqlCommand cmd = new SqlCommand("SELECT 1 FROM empImages WHERE empno=" + empno, new SqlConnection(CONNSTR)))
113.            {
114.                cmd.Connection.Open();
115.                object r = cmd.ExecuteScalar();
116.                if (r==null)
117.                {
118.                    return false;
119.                }
120.                else
121.                {
122.                    return true;
123.                }
124.            }
125.        }
126.    }
127.}

Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following