Asp.net Saving Image in binary into database table in Sql 2005/2008

In this article, I am going to explain how to store image file in database. for this I am using Northwind database and product table.

In this implementation, I have added a  column ProductImage of image datatype for storing the image of each product.




ALTER TABLE Products ADD ProductImage IMAGE




Html Code in aspx part

The code is very simple - the web page contains a text box for getting the Product Id of the record and a file upload control in the page and A button. 

<h2>

       Save Image in Database

    </h2>

    <div>

    <table>

        <tr>

            <td style="vertical-align:top">Product ID</td>

            <td><asp:TextBox ID="txtProductID" runat="server" Width="100px"></asp:TextBox></td>

        </tr>

        <tr>

            <td>Image</td>

            <td><asp:FileUpload runat="server" ID="fuImage" Width="400px" /> </td>

        </tr>

        <tr>

            <td colspan="2" style="text-align:center">

                <asp:Button ID="btnSave" runat="server" Width="80px" Text="Save"

                    onclick="btnSave_Click" />

            </td>

        </tr>

    </table>

</div>

On click of Save button on the page, below code executed

protected void btnSave_Click(object sender, EventArgs e)

        {

            if ((fuImage.PostedFile.FileName.Trim().Length > 0) &&

                (fuImage.PostedFile != null))

            {

                byte[] image = new byte[fuImage.PostedFile.ContentLength];

                fuImage.PostedFile.InputStream.Read(image, 0, (int)fuImage.PostedFile.ContentLength);

                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);

                SqlCommand command = new SqlCommand();

                command.CommandText = "Update Products set ProductImage = @Image where ProductID = '" + txtProductID.Text + "'";

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                PrepareSQLParameter(command, "@Image", SqlDbType.Image, image.Length, image);

                connection.Open();

                int result = command.ExecuteNonQuery();

                connection.Close();

                txtProductID.Text = "";

            }

        }

        private SqlParameter  PrepareSQLParameter(SqlCommand command, string parameterName, SqlDbType parameterType, int parameterLength, object parameterValue)

        {

            SqlParameter parameter = new SqlParameter(parameterName, parameterType, parameterLength);

            parameter.Value = parameterValue;

            command.Parameters.Add(parameter);

            return parameter;

        }