Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to save Image in SQL Server Database in ASP.NET

Author: Rachel Nicole
Download Source Code : 1317_SaveImagesInSQLDatabase.zip

The purpose of this article is to explain how we can save images in SQL Server Database in ASP.NET.

 

Every website contains images and some website provides file uploading facility including images.  We have two options to save these images. Images can be saved on disk or in SQL Server Database. Both options have their advantages and disadvantages.

If you want to save images in SQL Server Database then you need to deal with binary data. Images are stored in database in binary format. It is a simple task and it also has the benefit that you are not required to manage folders and remember paths for images.  A data type ‘image’ is available in SQL Server Database to save images.
 
For this example, I have created a table in SQL Server ‘master’ database. You can create table in SQL using Server Explorer in Visual Studio. The name of the table is ‘ImageSave’ and it has three columns: ImageName, ImageType and ImageData. ImageData is the field that has the ‘image’ data type and we will store our images in this field in binary format.
 
  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual Basic or Visual C#  > ASP.NET Empty Web Site
  4. Right click on web site > Add New Item > Web Form
  5. Right click on website > New Folder. Rename the folder as “Files”.
  6. Write code below in Default.aspx page or Drag and drop following controls
    A FileUpload Control
    A Button Control
    A Label Control

    Select Image:
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Save Image" onclick="Button1_Click" />
    <asp:Label ID="Label1" runat="server" ></asp:Label>
     
  7. Don’t forget to include following namespaces in code behind file

    C#

    using System.IO;
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.IO
    Imports System.Data
    Imports System.Data.SqlClient

     
  8. Write below function to save image in database

    C#
     
    private void SaveImage(string name, string type, byte[] image) {
        string insertText = "INSERT INTO ImageSave(ImageName, ImageType, ImageData) VALUES (@name, @type, @image)";
        string myConnection = "Data Source=Local;Initial Catalog=master;Integrated Security=True";
        SqlConnection connection = new SqlConnection(myConnection);
     
        SqlCommand command = new SqlCommand(insertText, connection);
        command.Parameters.Add("name", SqlDbType.VarChar, 50);
        command.Parameters["name"].Value = name;
        command.Parameters.Add("type", SqlDbType.VarChar, 50);
        command.Parameters["type"].Value = type;
        command.Parameters.Add("image", SqlDbType.Image);
        command.Parameters["image"].Value = image;
     
        try {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex) {
            Label1.Text = "Unable to insert image";
        }
        Finally {
            connection.Close();
        }
    }
     
    VB.NET
     
    Private Sub SaveImage(ByVal name As String, ByVal type As String, ByVal image As Byte())
        Dim insertText As String = "INSERT INTO ImageSave(ImageName, ImageType, ImageData) VALUES (@name, @type, @image)"
        Dim myConnection As String = "Data Source=Local;Initial Catalog=master;Integrated Security=True"
        Dim connection As New SqlConnection(myConnection)
     
        Dim command As New SqlCommand(insertText, connection)
        command.Parameters.Add("name", SqlDbType.VarChar, 50)
        command.Parameters("name").Value = name
        command.Parameters.Add("type", SqlDbType.VarChar, 50)
        command.Parameters("type").Value = type
        command.Parameters.Add("image", SqlDbType.Image)
        command.Parameters("image").Value = image
     
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Label1.Text = "Unable to insert image"
        Finally
            connection.Close()
        End Try
    End Sub
     
    Here is the explanation of the code.
     
    Above function insert image to database with image name and content type.
    I have written insert statement with column names and values.
    I have to connect to the Server to save images.
    Then I have created the SqlCommand object and add all three parameters to it with values.
    In try block, I have opened the connection to the server and called the ExecuteNonQuery().
    In “Catch” block I have displayed a message in case of any error.
     
  9. Write code below in button click event.


    C#
     
    protected void Button1_Click(object sender, EventArgs e) {
        bool imagefile = false;
     
        if (FileUpload1.HasFile) {
            string fileName = FileUpload1.PostedFile.FileName;
            string fileType = FileUpload1.PostedFile.ContentType;
            string fileExtension = Path.GetExtension(fileName).ToLower();
     
            string[] extensionsAllowed = { ".jpg", ".jpeg", ".gif", ".png" };
     
            for (int i = 0; i < extensionsAllowed.Length; i++) {
                if (fileExtension == extensionsAllowed[i]) {
                    imagefile = true;
                }
            }
     
            if (imagefile) {
                try {
                    BinaryReader binReader = new BinaryReader(FileUpload1.PostedFile.InputStream);
                    int length = FileUpload1.PostedFile.ContentLength;
                    byte[] image = binReader.ReadBytes(length);
                    SaveImage(fileName, fileType, image);
                    Label1.Text = "Image saved in database";
     
                }
                catch (Exception ex) {
                    Label1.Text = "Try again";
                }
     
     
            }
            else {
                Label1.Text = "Only images are allowed";
            }
        }
           
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim imagefile As Boolean = False
     
        If FileUpload1.HasFile Then
            Dim fileName As String = FileUpload1.PostedFile.FileName
            Dim fileType As String = FileUpload1.PostedFile.ContentType
            Dim fileExtension As String = Path.GetExtension(fileName).ToLower()
     
            Dim extensionsAllowed As String() = {".jpg", ".jpeg", ".gif", ".png"}
     
            For i As Integer = 0 To extensionsAllowed.Length - 1
                If fileExtension = extensionsAllowed(i) Then
                    imagefile = True
                End If
            Next
     
            If imagefile Then
                Try
                    Dim binReader As New BinaryReader(FileUpload1.PostedFile.InputStream)
                    Dim length As Integer = FileUpload1.PostedFile.ContentLength
                    Dim image As Byte() = binReader.ReadBytes(length)
                    SaveImage(fileName, fileType, image)
     
                    Label1.Text = "Image saved in database"
                Catch ex As Exception
                    Label1.Text = "Try again"
                End Try
            Else
                Label1.Text = "Only images are allowed"
            End If
        End If
    End Sub
     
    Here is the explanation of the code in button click event.
     
    At the start of the “Button1_Click” method, I have declared a variable “imagefile” and initializing it to “False”. It is used in the code to check the correct extension of the file which will be uploaded.
    In “If” statement expression, I have used the “HasFile” property of the “FileUpload” to check that “FileUpload” Control contains the file to upload.
    “FileName” property of “PostedFile” (Which is property of “FileUpload”) is used to get the name of the file and assigned this name to a “String” variable.
    “ContentType” property of “PostedFile” (Which is property of “FileUpload”) is used to get the content type of the file and assigned this type to a “String” variable.
    Then I have another “String” variable to store the extension of the file. “Path” class has the “GetExtension()” method which takes the file name and returns the extension of that file. I have used “ToLower()” method to convert the extension into lower case.
    Next I have declared an “Array” of “Strings” (named extensionsAllowed) to store extensions which I want for files to be uploaded and initialized it with four image file extensions.
    I have used “For” loop to loop through the “extensionsAllowed” array. It goes from index 0 to the index which is one less than the array length (“Length” property of the array is used to get the length).
    Inside the “For” loop, I have used an “If” statement to check that the extension of the file, that is selected for upload, is equal to one of my allowed extension to upload file. If it is so then I have set “imagefile” variable to true.
    Then there is another “If” statement to check “imagefile” variable. If it is “False” then I have displayed a message that only images are allowed.
    On the other hand, if “imagefile” variable is “True” then I have a “Try” block and a Catch” block.
    In “Try” block I have created a “BinaryReader” object. Then I have an “Integer” variable to store the Length of the image.
    Then I have called the “ReadBytes()” method of BinaryReader object and assigned result to byte array “image”. Then I have called the SaveImage() method by providing all parameters.
    At the end, I am displaying message that the image saved in database.
    In “Catch” block I have displayed a message in case of any error.

     
  10. Right Click on website > View web site in browser.
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< All, Any and Aggregate methods in LINQ

Disclaimer - Privacy
© 2002-2017 DevASP.net