Upload images to SQL Server Database using asp.net

There are several ways to upload images from an asp.net application. Uploading images or pictures to the directory is quite simple and easy in asp.net but another approach is to use SQL server database. To save an image file in the database, first create a table with columns to hold the ImageName, ImageSize, Picture. The datatype for the Picture column should be image and for other columns  use nvarchar. The image is saved in the database in binary format. In order to save an image file in the database it should be first converted to byte.

Create a Pictures table in the database Album.

CREATE TABLE [dbo].[Pictures](
[ImageId] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) NOT NULL,
[ImageSize] [int] NULL,
[Picture] [image] NULL,
 CONSTRAINT [PK_Pictures] PRIMARY KEY CLUSTERED
(
[ImageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Stored Procedure to save image to the database table Pictures.

CREATE PROCEDURE sprocSaveImage
@ImageName nvarchar(100),
@ImageSize int,
@Picture image
AS
BEGIN
INSERT INTO [Album].[dbo].[Pictures]
           (ImageName
           ,ImageSize
           ,Picture)
     VALUES
           (@ImageName
           ,@ImageSize
           ,@Picture)
END
GO

Default.aspx

<div>
    Image:&nbsp;
    <asp:FileUpload ID="fileImage" runat="server" />
    &nbsp;
    <asp:Button ID="btnUpload" runat="server" Text="Upload"
        onclick="btnUpload_Click" />
</div>

Default.aspx.cs

using System.Data;
using System.Data.SqlClient;

SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Album.MDF;Integrated Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
protected void btnUpload_Click(object sender, EventArgs e)
    {
        HttpPostedFile _pfImage = fileImage.PostedFile;
        string imgName = _pfImage.FileName;
        int imgSize = _pfImage.ContentLength;
        byte[] imageOriginal = new byte[imgSize];
        _pfImage.InputStream.Read(imageOriginal, 0, imgSize);

        SaveImageToDatabase(imgName, imgSize, imageOriginal);
    }
    private void SaveImageToDatabase(string imgName, int imgSize, byte[] imageOriginal)
    {
        SqlCommand sqlcmd = new SqlCommand("sprocSaveImage", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddWithValue("ImageName", imgName);
        sqlcmd.Parameters.AddWithValue("ImageSize", imgSize);
        sqlcmd.Parameters.AddWithValue("Picture", imageOriginal);
        if (sqlcon.State == ConnectionState.Closed)
        {
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
        }
        if (sqlcon.State == ConnectionState.Open)
        {
            sqlcon.Close();
        }
    }

In the next post I will explain how to retrieve images from SQL server database and bind to gridview.