Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Asp.net- Convert DataReader to DataTable in C#,VB.NET

Oct 2, 2012
Introduction:
 
In this article I will explain how to convert datareader to datatable in asp.net using C#.net and VB.NET

Description:

In previous post I explained ExecuteReader, ExecuteScalar, ExecuteNonQuery and differences between ExecuteNonQuery, ExecuteReader and ExecuteScalar in asp.net. Now I will explain how to convert datareader to datatable in asp.net using C#.net, VB.NET

DataReader

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture during fetch the data from database. DataReader will fetch the data very fast when compared with dataset. Generally we will use ExecuteReader object to bind data to datareader for that check this post ExecuteReader Example in asp.net.

To convert datareader to datatable we need to use Load() Method of datatable like as shown below

C# Code

// This method is used to bind gridview from database
protected void BindGridview()
{
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt=new DataTable();
dt.Load(dr);
gvUserInfo.DataSource = dt;
gvUserInfo.DataBind();
con.Close();
}
}
VB.NET Code


' This method is used to bind gridview from database
Protected Sub BindGridview()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("Select UserName,LastName,Location FROM UserInformation", con)
Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim dt As New DataTable()
dt.Load(dr)
gvUserInfo.DataSource = dt
gvUserInfo.DataBind()
con.Close()
End Using
End Sub
 If you want to see complete example first design one table UserInformation in your database as shown below

Column Name
Data Type
Allow Nulls
UserName
varchar(50)
Yes
LastName
varchar(50)
Yes
Location
Varchar(50)
Yes
Once table designed in database enter some dummy data to test after that write the following code in your aspx page 



<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Convert DataReader to DataTable in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Bind Gridview with datareader object</b><br /><br />
<asp:GridView ID="gvUserInfo" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White"/>
</asp:GridView>
</div>
</form>
</body>
</html>
Now add the following namespaces in code behind

C# Code


using System;
using System.Data.SqlClient;
using System.Data;
After add namespaces write the following code in code behind


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}

// This method is used to bind gridview from database
protected void BindGridview()
{
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt=new DataTable();
dt.Load(dr);
gvUserInfo.DataSource = dt;
gvUserInfo.DataBind();
con.Close();
}
}
VB.NET Code


Imports System.Data
Imports System.Data.SqlClient

Partial Public Class ExecuteReader
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridview()
End If
End Sub

' This method is used to bind gridview from database
Protected Sub BindGridview()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("Select UserName,LastName,Location FROM UserInformation", con)
Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim dt As New DataTable()
dt.Load(dr)
gvUserInfo.DataSource = dt
gvUserInfo.DataBind()
con.Close()
End Using
End Sub
End Class
Demo


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

Dustin Lester said...

Your blog is a perfect guidance to me through which I am learning ASP.NET. On each and every post there is something to learn which is new for me.

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.