Using Disconnected DataTable to Display Records in GridView in ASP.NET

Introduction

Download

In this article we are going to take a look at the way to use disconnected DataTable and will also display the records in GridView using this. In the later article, you will learn how to perform CRUD (Create Read Update Delete) operations using disconnected DataTable. At very first time a question will arise, what disconnected DataTable is? Let's define it in few words.

Prerequisite

I am assuming that article learner is aware about basics of ADO.NET and fundamental configuration, however I will try to use fundamental steps also so that beginner learner can also get this.

What is disconnected DataTable?

Disconnected DataTable can be used to communicate between distributed applications or web services, especially when someone wanted to revert back all operations (Create Update Delete) made on data. Disconnected DataTable supports multiple DataTable bindings and it also make possible to navigate forward and backward in DataTable, will talk about this is later articles.

Create Database

At very first, create the sample database for our demonstration. Here is the screenshot of my SQL Server Express database; you need to create it in your application.

In above database table definition window I will be using id column as primary key and identity specification that will be increased by 1 each time automatically.

Create ConectionString in Web.Config

In this demonstration we are going to refer the connectionString setting from web.config file. So, let's create the connectionString. Here is the one I am using.

<connectionStrings>

       <add name="DatabaseConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

</connectionStrings>

In above connectionstring setting I have attached its name to "DatabaseConnectionString1".

Default.aspx Page Desing

The scope of this article is to display the data in GridView using disconnected DataTable, so just need a GridView control here. Here is the one I am using.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

    Width="229px">

    <Columns>

        <asp:BoundField DataField="id" HeaderText="ID" />

        <asp:BoundField DataField="friends" HeaderText="Friends" />

    </Columns>

</asp:GridView>

In above code, I am using AutoGenerateColumns to false and also added two BoundField that will point the id and friends fields from DataTable.

Now let's move next to view main logic of this disconnected DataTable.

Default.aspx.cs Page Code

Here, we are going to talk on each method and even handler one by one.

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        InitializeDataAndGridView();

    }

}

Firstly, I am checking it's postback and if it is false then calling a method named InitializeDataAndGridView(), which will perform next task.

private void InitializeDataAndGridView()

{

    PopulateData();

    LoadGridView();

}

In above code, I am calling two more methods, one for populating the data and another for loading data in container that is GridView here for this demonstration.

Let's take a look at PopulateData() method:

protected void PopulateData()

{

    SqlDataAdapter sqlDA = CreateAdapter();

    sqlDA.FillSchema(FriendseDataTable, SchemaType.Source);

    sqlDA.Fill(FriendseDataTable);

}

protected SqlDataAdapter CreateAdapter()

{

    String connStr = ConfigurationManager.ConnectionStrings["DatabaseConnectionString1"].ConnectionString;

    SqlConnection conn = new SqlConnection(connStr);

    SqlCommand cmd = new SqlCommand("SELECT * FROM Table1", conn);

    cmd.CommandType = CommandType.Text;

    SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);

    return sqlDA;

}

public DataTable FriendseDataTable

{

    get

    {

        if (Cache["friendsDataTable"] == null)

            Cache["friendsDataTable"] = new DataTable("friendsDataTable");

        return (DataTable)Cache["friendsDataTable"];

    }

}

In above code, we are using SqlDataAdapter instance to sqlDA and this code is playing main role in disconnected model. Why, read on.

What are DataAdapter and all that stuff above?

A DataTable never has a live connection with a DataSource because DataAdapter connects with the DataSource and executes the query and places the result in a DataTable. We don't need an open connection to use the DataAdapter.

CreateAdapter method returns all resultant data back to DataTable and here our DataTable instance is FriendsDataTable.

That's all about the short description on PopulateData() method, now let's move to LoadGridView() method that will load our DataTable to GridView. Here is the code:

protected void LoadGridView()

{

    GridView1.DataSource = FriendseDataTable;

    GridView1.DataBind();

}

In above code, I am filling GridView1 by FriendDataTable that has all records.

Please download the attached file and demonstrate it yourself. I hope you like this post. Please post your comments and feedbacks here.