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
|
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 Email
|
|||
2 comments :
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.
Got a Clear Understanding now. Thanks a lot.
Great work, Keep it up...................................
Note: Only a member of this blog may post a comment.