How to save an Image into the database using Stored Procedure in ASP.NET?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In this article, we are going to learn how to save the Image into the database using Stored Procedure in ADO.NET.

To save the image into the database (using stored procedure), we can follow this approach. First let's see the database table structure I am using.

DATABASE STRUCTURE

Here AutoId is the Identity (Auto increment column) and the FileContent column where the actual image file content will be stored is of image type.

Below is my ASPX Page content.

ASPX PAGE

<h4>Save images into the database</h4>
       Select file: <asp:FileUpload ID="FileUpload1" runat="server" />
       <asp:Button ID="btnSubmit" runat="server" Text="Upload & Save" OnClick="UploadAndSaveIntoDatabase" />
        <p><asp:Label ID="lblMessage" runat="server" EnableViewState="false" 
ForeColor="Green" /></p>

In the above code snippet, we have a FileUpload control, asp:Label control and am asp:Button control. On click of the button, we are calling UploadAndSaveIntoDatabase server side method.

CODE BEHIND

    string _connStr =
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
 
   /// <summary>
   /// Uploads the and save into database.
   /// </summary>
   /// <param name="sender">The sender.</param>
   /// <param name="e">The <see cref="System.EventArgs"/> instance containing the
event data.</param>
    protected void UploadAndSaveIntoDatabase(object sender, EventArgs e)
    {
        string fileName = FileUpload1.PostedFile.FileName;
        int fileLength = FileUpload1.PostedFile.ContentLength;
 
        byte[] imageBytes = new byte[fileLength];
        FileUpload1.PostedFile.InputStream.Read(imageBytes, 0, fileLength);
 
        using (SqlConnection conn = new SqlConnection(_connStr))
        {
          using (SqlCommand cmd = new SqlCommand("InsertImage", conn))
          {
              cmd.CommandType = CommandType.StoredProcedure;
 
              SqlParameter[] prms = new SqlParameter[3];
              prms[0] = new SqlParameter("@fileName", SqlDbType.VarChar, 50);
              prms[0].Value = fileName;
              prms[1] = new SqlParameter("@fileContent", SqlDbType.Image);
              prms[1].Value = imageBytes;
              prms[2] = new SqlParameter("@active", SqlDbType.Bit);
              prms[2].Value = true; // hard coded value
 
              cmd.Parameters.AddRange(prms);
 
              conn.Open();
              cmd.ExecuteNonQuery();
              conn.Close();
        }
        lblMessage.Text = fileName + " saved to the database successsfully !";
    }
} 

In UploadAndSaveIntoDatabase method, we have got the file name and file length into fileName and fileLength variable respectively. We have created an imageBytes variable of bytes array based on the length of the file.

Rest codes are simple ADO.NET code that fires the InsertImage stored procedure to insert the data into the database. Notice the fileContent parameter; it is of SqlDbType.Image type as the FileContent field of the database is also of Image data type.

If you do not know how to work with Stored Procedure, read this article.

STORED PROCEDURE

CREATE PROCEDURE InsertImage
      -- Add the parameters for the stored procedure here
      @fileName varchar(250),
      @fileContent image,
      @active bit
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
     -- Insert statements for procedure here
       INSERT INTO Files ([FileName], FileContent, Active) VALUES (@fileName,
@fileContent, @active)
END

The stored procedure is pretty simple, here we have three input parameter and those are being used into the INSERT statement to insert image into the database.

OUTPUT

Hope this article was helpful. Stay tuned for more articles in ASP.NET and other technologies. You can help your friends by referring to this website that has a lot of tutorials, technical articles, career advice and much more.

Thanks for reading.