Data binding through code

Hi.. Welcome to my first post. In this post I have chosen a problem solving subject. Many times I have been asked about some problems regarding database related work in ASP.NET web forms. They told me that they are facing problems to show data in a particular date range from database. For that today I am trying to show you a demo application. In this demo I am going to use Visual Web Developer 2008 and SQL Server 2008 Express Edition. Hope you have these software install on your computer. (If not please go to http://www.microsoft.com/express/download and follow the instructions). Also I am going to use Northwnd database (which you can freely download from).

Start VWD and create a new Website. Give a name of your choice to the website. I am giving ADO.NETDemo. Next I am going to add an existing database (Northwnd) to my website. Any data related files are stored in App_Data folder. So write click the App_Data folder and select ‘Add Existing Data’ from context menu. Then select the Northwnd database from your hard disk. Double click the Northwnd database and it will open the ‘Database Explorer’. Open the table tab and open the Orders table. It will show all the columns of the Orders table. I will show all the orders data in this table in my ASP.NET web pages filtered by OrderDate.

Database Explorar in VWD 2008
Database Explorar in VWD 2008

Open the Default.aspx and place Two TextBoxes for start date and end date and a button.

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Orders</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <p>
            Start date:<asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox></p>
        <p>
            End date:<asp:TextBox ID="txtEndDate" runat="server"></asp:TextBox></p>
        <p>
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /></p>
        <p>
            <asp:GridView ID="gviewOrder" runat="server">
            </asp:GridView></p>
    </div>
    </form>
</body>
</html>

Now go to the design view and double click on the button. It will create a button click event handler method in your code behind file. Now write some code to fill a DataTable with date filering and show data in to the GridView.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            // Get connection string from web.config file.
            string ConnectionString = WebConfigurationManager.ConnectionStrings["conNorthwnd"].ConnectionString;
            string quary = "SELECT OrderDate, ShipCity, ShipRegion, ShipCountry FROM Orders WHERE OrderDate = @StartDate AND OrderDate = @EndDate&quot;

            SqlConnection theConnection = new SqlConnection(ConnectionString);

            SqlCommand theCommand = new SqlCommand(quary, theConnection);
            theCommand.CommandType = CommandType.Text;

            // The input paramters.
            theCommand.Parameters.AddWithValue("StartDate&quot;, DateTime.Parse(txtStartDate.Text));
            theCommand.Parameters.AddWithValue("EndDate&quot;, DateTime.Parse(txtEndDate.Text));

            SqlDataAdapter theDataAdapter = new SqlDataAdapter(theCommand);
            DataTable theOrderTable = new DataTable();

            // Open the connection, fill data into data table and close the connection.
            theDataAdapter.Fill(theOrderTable);

            // Show in grid view.
            grvOrders.DataSource = theOrderTable;
            grvOrders.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
}

Then go to the web.config file and add some code in the connectionString area under system.web section

<connectionStrings>
    <add name="conNorthwnd" connectionString="Data Source=./SQLEXPRESS AttachDBName=|DataDirectory|/NORTHWND.mdf" providerName="System.Data.SqlClient"/>
</connectionStrings>

In our code we 1st take our connection string from web.config file. Connection string is the total information of database and how we interact with the database. We can store our connection string in our code, but it is good practise to store it in configaration file. It is good for maintenance perpose. Then we write SQL quary to get data from our database. There are some useful classes in ADO.NET like SqlConnection, SqlCommand DataSet etc in System.Data and System.Data.Sqlclient namepace. I am using Sql Server that is way I am using the classes from System.Data.Sqlclient namespaces. You can see that to pass parameter in sql quary I am using SqlParameter object in SqlCommand object’s Parameters collection. Fill the data in DataTable and show in GridView

Press ctrl + F5 to run your application. Give start date 1/1/1997 and end data 1/1/1998 and press the submit button. You can see all the data in this date range in your GridView.

There are many more wayout of this problem. This is just a single solution. You will like to open the other doors urself or may be I will also write some other ways in my next posts

Thank you.

Advertisements

3 Replies to “Data binding through code”

  1. thanks a lot sir. It was really helpful and informative blog. Looking forward to see more posts from you.

    thanking You…Pritha.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s