Thursday 6 June 2013

How to store and retrieve images in database with ASP.NET (Example)

How to store and retrieve images in database with ASP.NET (Example) 

http://www.dotnettwitter.com/2010/12/how-to-store-and-retrieve-images-in.html

 

This post is an example for how to store and retrieve image in database field. This is a continuation of another post, which concentrate only on what required to be done for achieving the goal. Here I am giving an example to get the full working implementation of the the page.

I have taken Northwind database to show the example. The implementation is:

  1. Add an additional field in Product table for storing the image.
  2. Design a page to add new Products which have the ability to select image for the product and store into database
  3. Show a grid which shows list of Products in the system
  4. On click of Product name hyperlink, show the same record into the entry fields to edit the values including product image
For experienced person, requesting to jump directly into required section and look the code.
  1. Adding image field into the Product table.
    Added a column ProductImage with data type image. The design of the table is as in the image.

  2. Design of aspx page
    Looking at the page design, it is normal as like other data entry screens. The first half of the screen has data entry and next half has grid view to show the records.
    The aspx script goes like this
    <table style="border:1px;">
        <tr>
            <td class="EntryHeading" colspan="2">Product Entry</td>
        </tr>
        <tr>
            <td style="width:120px;">
                Product Name:<strong class="red">*</strong><
            /td>
            <td style="width:280px;">
                <asp:TextBox ID="txtProductName" runat="server" 
                    CssClass="text" Width="245px" />
                <asp:HiddenField ID="hidProductID" runat="server" Value="0" />
            </td>
            <td rowspan="7" style="width:320px;">
                <img id="imgProduct" alt="" runat="server" 
                    src="~/Images/EmptyProduct.jpg" 
                    onclick="ShowImageWindow()" 
                    class="ImageContainer" />
            </td>
        </tr>
        <tr>
            <td>Supplier:<strong class="red">*</strong></td>
            <td>
                <asp:DropDownList ID="DDLSupplier" runat="server" 
                    DataTextField="CompanyName" 
                    DataValueField="SupplierId"
                    AppendDataBoundItems="true" Width="250px">
                    <asp:ListItem Text="Select" />
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td>Category:<strong class="red">*</strong></td>
            <td>
                <asp:DropDownList ID="DDLCategory" runat="server" 
                    DataTextField="CategoryName" 
                    DataValueField="CategoryId"
                    AppendDataBoundItems="true" Width="250px">
                    <asp:ListItem Text="Select" />
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td>Quantity Per Unit:</td>
            <td>
                <asp:TextBox ID="txtQtyPerUnit" CssClass="textR" 
                    runat="server" Width="120px" />
            </td>
        </tr>
        <tr>
            <td>Unit Price:</td>
            <td>
                <asp:TextBox ID="txtUnitPrice" CssClass="textR" 
                    runat="server" Width="120px" />
            </td>
        </tr>
        <tr>
            <td>Reorder Level:</td>
            <td>
                <asp:TextBox ID="txtReorderLevel" CssClass="textR" 
                    runat="server" Width="120px" />
            </td>
        </tr>
        <tr>
            <td>Discontinued:</td>
            <td>
                <asp:CheckBox ID="chkDiscontinued" runat="server" 
                    Checked="false" />
            </td>
        </tr>
        <tr>
            <td>Product Image:</td>
            <td colspan="2">
                <asp:FileUpload ID="fileName" runat="server" style="width:80%;" 
                    onchange="ChangeImage(this.value)" />
            </td>
        </tr>
        <tr style="text-align:center">
            <td colspan="3">
                <asp:Button ID="SaveButton" runat="server" Text="Save" 
                    Width="100px" onclick="SaveButton_Click" />
                <asp:Button ID="CancelButton" runat="server" Text="Cancel" 
                    Width="100px" onclick="CancelButton_Click" />
            </td>
        </tr>
    </table>
    <asp:GridView ID="grdViewProducts" runat="server" 
        AutoGenerateColumns="False" GridLines="None"
        AllowPaging="True" PageSize="8" DataKeyNames="ProductID" 
        Width="100%" CellPadding="4" ForeColor="#333333">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        
        <Columns>
            <asp:TemplateField  HeaderText="Product Name" >
                <ItemTemplate>
                    <asp:LinkButton Text='<%# Bind("ProductName") %>' 
                        ID="lnkProductName" runat="server" 
                        OnClick="lnkProductName_Click" 
                        CommandArgument='<%# Bind("ProductID") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
            <asp:BoundField DataField="CategoryName" HeaderText="Category" />
            <asp:BoundField DataField="QuantityPerUnit" 
                        HeaderText="Quantity Per Unit"/>
            <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
        </Columns>
        
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" 
                        HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" 
                        ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    
    The javascript for the aspx page is follows:
    function ChangeImage(url) {
        if (url.length == 0) {
        }
        else {
            if (document.getElementById("<%= imgProduct.ClientID %>")) {
                document.getElementById("<%= imgProduct.ClientID %>").src = url;
            }
        }
    }
    
    function ShowImageWindow() {
        popupWindow = window.open("", "popUpImage", 
                "location=0;status=1,scrollbars=1");
        popupWindow.document.write("<img src='" + 
                document.getElementById("imgProduct").src + "' />");
        popupWindow.document.close();
        popupWindow.focus();
    }
    
    As shown in the script, I have a file upload control to select the image from the local system. I also placed an image control next to the data entry controls, which is used for showing image of the product on selection of file upload control or selection of a product from the grid view.

    I have also implemented in this example, when I click the image on the image control in the web form, the system should open a popup to show the image in actual size.
    One more point to say here is – I have an empty image, which is nothing but to show an image inside “Select the Product Image”. All are to beautify my screen :) (skip if you don’t want to look all those codes)
    The style sheet of the page given below:
    .EntryHeading
    {
        font-weight:bold;
        font-size:14px;
        text-align:center;
    }
    .text
    {
        height: 16px;
        color: #2A2A2A;
        border: 1px solid #5A5A5A;
        font-size: 11px;
    }
    .textR
    {
        height: 16px;
        color: #2A2A2A;
        border: 1px solid #5A5A5A;
        font-size: 11px;
        text-align: right;
    }
    .red
    {
        color: #FF0000;
    }
    .ImageContainer
    {
        width:320px;
        height:175px;
        text-align:center;
        vertical-align:middle;
        background-color:gray;
        border:2px;
        cursor:hand;
    }
  3. Now let us see the code behind
    The page_load even to refresh the dropdown box and grid records.
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            FillDropDownList();
            BindGrid();
            hidProductID.Value = "0";
        }
    }
    private void FillDropDownList()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
        {
            SqlCommand command = new SqlCommand(
                    "select CompanyName, SupplierID from Suppliers", connection);
    
            connection.Open();
            SqlDataReader dr = command.ExecuteReader
                            (CommandBehavior.CloseConnection);
    
            DDLSupplier.DataSource = dr;
            DDLSupplier.DataBind();
    
            connection.Open();
            command = new SqlCommand("select CategoryName, CategoryID 
                            from Categories", connection);
            dr = command.ExecuteReader(CommandBehavior.CloseConnection);
    
            DDLCategory.DataSource = dr;
            DDLCategory.DataBind();
        }
    }
    private void BindGrid()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
        {
    
            SqlCommand command = new SqlCommand(
              "SELECT ProductID, ProductName, CompanyName, CategoryName, " +
              "QuantityPerUnit, UnitPrice FROM Products " +
              "JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
              "JOIN Categories ON Products.CategoryID = Categories.CategoryID " + 
              "Order by ProductID desc", 
                    connection);
    
            connection.Open();
            SqlDataReader dr = command.ExecuteReader
                                    (CommandBehavior.CloseConnection);
    
            IList productViewList = new List();
            while (dr.Read())
            {
                ProductView productView = new ProductView();
                productView.ProductID = dr["ProductID"].ToString();
                productView.ProductName = dr["ProductName"].ToString();
                productView.CompanyName = dr["CompanyName"].ToString();
                productView.CategoryName = dr["CategoryName"].ToString();
                productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
                productView.UnitPrice = 
                                    Convert.ToDouble(dr["UnitPrice"].ToString());
                productViewList.Add(productView);
            }
            grdViewProducts.DataSource = productViewList;
            grdViewProducts.DataBind();
        }
    }
    public class ProductView
    {
        public string ProductID { get; set; }
        public string ProductName { get; set; }
        public string CompanyName { get; set; }
        public string CategoryName { get; set; }
        public string QuantityPerUnit { get; set; }
        public double UnitPrice { get; set; }
    }
    Here I wish to say one point is – I have used an additional class ProductView for binding the data to the GridView. When you bind the SqlDataReader into Gridview directly, you will be getting an error message "The data source does not support server-side data paging". To resolve the issue only I have used an entity class to make IList collection (Even I can use DataTable in this case). For more information on this error, please look at this post (linking soon).
  4. Now let us look at the Save method, which is what the actual code comes to store the data into the database.
    protected void SaveButton_Click(object sender, EventArgs e)
    {
        if ((fileName.PostedFile.FileName.Trim().Length > 0) &&
           (fileName.PostedFile != null))
        {
            byte[] image = new byte[fileName.PostedFile.ContentLength];
    
            fileName.PostedFile.InputStream.
                    Read(image, 0, (int)fileName.PostedFile.ContentLength);
    
    
            SqlConnection connection = new SqlConnection(ConfigurationManager.
                  ConnectionStrings["SQLConnection"].ConnectionString);
    
            SqlCommand command = new SqlCommand();
            if (Convert.ToInt64(hidProductID.Value) == 0)
            {
                command.CommandText = 
                 "INSERT INTO Products (ProductName, SupplierID, CategoryID, " + 
                 "QuantityPerUnit, UnitPrice, ReorderLevel, Discontinued, " +
                 "ProductImage) VALUES (@ProductName, @SupplierID, @CategoryID," +
                 "@QuantityPerUnit, @UnitPrice, @ReorderLevel, @Discontinued, " + 
                 "@ProductImage)";
            }
            else
            {
                command.CommandText = 
                   "UPDATE Products SET ProductName = @ProductName, " +
                   "    SupplierID = @SupplierID, " +
                   "    CategoryID = @CategoryID, " +
                   "    QuantityPerUnit = @QuantityPerUnit, " +
                   "    UnitPrice = @UnitPrice, " +
                   "    ReorderLevel = @ReorderLevel, " +
                   "    Discontinued = @Discontinued, " +
                   "    ProductImage = @ProductImage " +
                   "WHERE ProductID = " + hidProductID.Value;
            }
            command.CommandType = CommandType.Text;
            command.Connection = connection;
    
            PrepareSQLParameter(command, "@ProductName", 
                            SqlDbType.VarChar, 40, txtProductName.Text);
            PrepareSQLParameter(command, "@SupplierID", 
                            SqlDbType.Int, 4, DDLSupplier.SelectedValue);
            PrepareSQLParameter(command, "@CategoryID", 
                            SqlDbType.Int, 4, DDLCategory.SelectedValue);
            PrepareSQLParameter(command, "@QuantityPerUnit", 
                            SqlDbType.NVarChar, 40, txtQtyPerUnit.Text);
            PrepareSQLParameter(command, "@UnitPrice", 
                            SqlDbType.Money, 8, txtUnitPrice.Text);
            PrepareSQLParameter(command, "@ReorderLevel", 
                            SqlDbType.Int, 2, txtReorderLevel.Text);
            PrepareSQLParameter(command, "@Discontinued", 
                            SqlDbType.Bit, 1, chkDiscontinued.Checked);
            PrepareSQLParameter(command, "@ProductImage", 
                            SqlDbType.Image, image.Length, image);
    
            connection.Open();
    
            int result = command.ExecuteNonQuery();
            connection.Close();
    
            BindGrid();
            ClearScreen();
        }
    }
    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;
    }
    private void ClearScreen()
    {
        txtProductName.Text = "";
        DDLSupplier.SelectedIndex = 0;
        DDLCategory.SelectedIndex = 0;
        txtQtyPerUnit.Text = "";
        txtUnitPrice.Text = "";
        txtReorderLevel.Text = "";
        chkDiscontinued.Text = "";
        imgProduct.Src = "~/Images/EmptyProduct.jpg";
        hidProductID.Value = "0";
    }
    
    Here I am declaring an array of byte type to get the image stream and assigning to the parameter. The code you are required to look at the line [06 to 09] and [55, 56]
  5. The PopulateValues method is used to retrieve the values from the database and show into entry fields with the image for a particular product. This method will be fired when clicking the hyperlink on the GridView.
    protected void lnkProductName_Click(object sender, EventArgs e)
    {
        LinkButton lnkProductName = (LinkButton)sender;
        if (Convert.ToInt32(lnkProductName.CommandArgument) > 0)
        {
            PopulateValues(Convert.ToInt32(lnkProductName.CommandArgument));
        }
    }
    private void PopulateValues(int ProductID)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
        {
            SqlCommand command = new SqlCommand("select * from Products where ProductID = '" + ProductID + "'", connection);
            
            connection.Open();
            SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            while (dr.Read())
            {
                txtProductName.Text = dr["ProductName"].ToString();
                DDLSupplier.SelectedValue = dr["SupplierID"].ToString();
                DDLCategory.SelectedValue = dr["CategoryID"].ToString();
                txtQtyPerUnit.Text = dr["QuantityPerUnit"].ToString();
                txtUnitPrice.Text = dr["UnitPrice"].ToString();
                txtReorderLevel.Text = dr["ReorderLevel"].ToString();
                chkDiscontinued.Checked = Convert.ToBoolean(dr["Discontinued"]);
                hidProductID.Value = dr["ProductID"].ToString();
                if (dr["ProductImage"].ToString().Length > 0)
                    imgProduct.Src = string.Format(
                    "GetImageHandler.ashx?ProductID={0}", ProductID.ToString());
                else
                    imgProduct.Src = "~/Images/EmptyProduct.jpg";
            }
        }
    }
    
    Here, to get the image from the database I have two open. 1. can have an Generic Hanlder, 2. Can have an aspx page. But In both the ways, we are writing the image stream as response to the calling place. We also required to pass necessary parameter to the aspx or ashx handlers to get the proper image from the database.
    If you are planning to use Generic Handler, add a Generic Handler in the project (.ashx extension) and place the following code in ProcessRequest() event
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand("select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"] + "'", connection);
    
        connection.Open();
        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        while (dr.Read())
        {
            if (dr["ProductImage"].ToString().Length > 0)
            {
                context.Response.BinaryWrite((byte[])dr["ProductImage"]);
            }
        }
    }
    
    If you are planning to use Web Page, add a Web Form in the project (.aspx extension) and place the following code in Page_Load() event
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand("select ProductImage from Products where ProductID = '" + Request.QueryString["ProductID"] + "'", connection);
    
        connection.Open();
        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        while (dr.Read())
        {
            if (dr["ProductImage"].ToString().Length > 0)
            {
                Response.BinaryWrite((byte[])dr["ProductImage"]);
            }
        }
    }
    
    As you seen here, the image as retrieve to the calling place as a response from this aspx or ashx. So we don’t have to write any other code other then for image as response.

    Also I am passing ProductID as query string to get particular product image.
  6. Event and Method to clear the entry screen
    protected void CancelButton_Click(object sender, EventArgs e)
    {
        ClearScreen();
    }
    private void ClearScreen()
    {
        txtProductName.Text = "";
        DDLSupplier.SelectedIndex = 0;
        DDLCategory.SelectedIndex = 0;
        txtQtyPerUnit.Text = "";
        txtUnitPrice.Text = "";
        txtReorderLevel.Text = "";
        chkDiscontinued.Text = "";
        imgProduct.Src = "~/Images/EmptyProduct.jpg";
        hidProductID.Value = "0";
    }
    
The following screen shows the output of the screen

Data entry screen at initial stage

Before Save button click

On click of Product Name column

Table Data with newly added Product (Printer) and image column

popup screen on click of image on the entry screen


    

 

No comments:

Post a Comment