Get database values into HTML

Question

I am developing an app written in C# .Net 4.0 -- basically I have a stored proc that returns my orders from my database...I am binding those orders to a GridView --

CODEBEHIND

protected void Page_Load(object sender, EventArgs e)
{
    // connect to the database
    this.DbConnection();
    // open the connection
    sqlConn.Open();
    // call the stored procedure
    sqlCmd = new SqlCommand("usp_myorders", sqlConn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    // add parameter 
    sqlCmd.Parameters.Add("@startdate", SqlDbType.DateTime);
    sqlCmd.Parameters["@startdate"].Value = "06/01/2015 18:00:00"; 
    sqlRdr = sqlCmd.ExecuteReader();

    // get the count
    // @TODO: This is a very expensive method -- there must be a better way
    int count = 0;
    if (sqlRdr.HasRows)
    {
        while (sqlRdr.Read())
        {
            ++count;
        }
    }
    // close the connection
    sqlConn.Close();

    // reopen the connection, to create the grid
    sqlConn.Open();
    // bind data to the grid
    grid1.EmptyDataText = "No Record(s) Found...";
    grid1.DataSource = sqlCmd.ExecuteReader();
    grid1.DataBind();
    MakeGridViewPrinterFriendly(grid1);
    // close the connection
    sqlConn.Close();
    //Get the number of rows returned, and then assign it to the Label control
    lblRowCount.Text = count.ToString() + " Record(s) Found...";
    lblRowCount2.Text = count.ToString() + " Record(s) Found...";
}

On the HTML side I have the following code

HTML CODE

<asp:Label ID="lblRowCount" runat="server" Text="Label"></asp:Label>
        <!-- Table combined Styles -->
        <asp:GridView ID="grid1" runat="server" class="striped sortable lcpa_ord_nonprintable" AutoGenerateColumns="False" ShowHeaderWhenEmpty="true">
            <Columns>
                <asp:BoundField DataField="fname" HeaderStyle-HorizontalAlign="Left" HeaderText="First Name" SortExpression="fname" ReadOnly="true" />
                <asp:BoundField DataField="lname" HeaderStyle-HorizontalAlign="Left" HeaderText="Last Name" SortExpression="lname" ReadOnly="true" />
                <asp:BoundField DataField="item" HeaderStyle-HorizontalAlign="Center" HeaderText="Order" SortExpression="item" ReadOnly="true" />
                <asp:BoundField DataField="num_seats_pur" HeaderStyle-HorizontalAlign="Center" HeaderText="Quantity" SortExpression="num_seats_pur" ReadOnly="true" />

                <asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <a class="btn green small inline" href="#Order11">
                            <i class="fa fa-ticket"></i> View Order</a>
                        <a class="btn green small" href="#" onclick="printContent('Ticket11')">
                            <i class="fa fa-print"></i> Print</a>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView> 
        <asp:Label ID="lblRowCount2" runat="server" Text="Label"></asp:Label>

The piece that I am have difficulty with is here..

<a class="btn green small inline" href="#Order11">
    <i class="fa fa-ticket"></i> View Order</a>
        <a class="btn green small" href="#" onclick="printContent('Ticket11')">

I want to replace the href="#Order11" with the actual OrderID from the database and replace printContent('Ticket11') again with the actual OrderID from the database -- so it would read href="#Order12345" and printContent('Ticket12345')

Please keep in mind that there on average 30 - 50 orders in my table

Where I am stuck is I can't get the OrderIDs out of the database (in the CodeBehind) and into the HTML code block

Answer 1

There are a few ways you can do this. Let's say your OrderID field in your database is orderId column.

1) You can use template data binding like this:

<asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
                <ItemTemplate>
                    <a class="btn green small inline" href='#Order<%# Eval("orderId")%>'>
                        <i class="fa fa-ticket"></i> View Order</a>
                    <a class="btn green small" href="#" onclick="printContent('Ticket<%# Eval("orderId")%>')">
                        <i class="fa fa-print"></i> Print</a>
                </ItemTemplate>
            </asp:TemplateField>

2) You can use a server control and update it on the ItemDataBound event of the grid:

Template file:

<asp:GridView ID="grid1" runat="server" 
    class="striped sortable lcpa_ord_nonprintable" AutoGenerateColumns="False" 
    ShowHeaderWhenEmpty="true" onrowdatabound="grid1_RowDataBound">
    ...
    <asp:TemplateField HeaderText="Action" HeaderStyle-HorizontalAlign="Center">
        <ItemTemplate>
            <asp:HyperLink runat="server" ID="hlView" CssClass="btn green small inline">
                <i class="fa fa-ticket"></i> View Order
            </asp:HyperLink>
            <asp:LinkButton runat="server" ID="lbPrint" CssClass="btn green small">
                <i class="fa fa-print"></i> Print
            </asp:LinkButton>
        </ItemTemplate>
    </asp:TemplateField>
    ...
</asp:GridView>

Code behind:

    protected void grid1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRow row = (DataRow)e.Row.DataItem;
                HyperLink hlView = (HyperLink)e.Row.FindControl("hlView");
                LinkButton lbPrint = (LinkButton)e.Row.FindControl("lbPrint");
                if (row != null && lbPrint != null && hlView != null)
                {
                    hlView.NavigateUrl = "#Order" + row["orderId"];
                    lbPrint.OnClientClick = string.Format("printContent('Ticket{0}')", row["orderId"]);
                }
            }
        }

Your page_load should also be changed to improve performance and to put data inside a DataTable object instead so that it can be retrieved in the dataBound event as a DataRow:

        ...
        sqlCmd.Parameters.Add("@startdate", SqlDbType.DateTime);
        sqlCmd.Parameters["@startdate"].Value = "06/01/2015 18:00:00";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd, sqlConn);
        DataTable tblOrder = new DataTable();
        adapter.Fill(tblOrder);

        // get the count
        // @TODO: This is a very expensive method -- there must be a better way
        int count = tblOrder.Rows.Count;
        ...

        grid1.DataSource = tblOrder;
        grid1.DataBind();
        ...