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

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 200 | Views : 28599 red flag

In this article, we shall learn how to save an Image into the SQL Server 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. 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Parcha on: 8/24/2011 | Points: 25
hi sheo narayan
I practiced each and every article of yours ..your latest articles on Ado.net concepts are really good
i learned a lot from your articles keep going...
can you please write articles on Ajax Controls and retreiving data from Xml....
using strings,Array,loops
your articles are awesome
Thanks
karthik


Login to post response

Comment using Facebook(Author doesn't get notification)