ARTICLE
Use CommandBehaviour.CloseConnection to return multiple rows
You can use CommandBehavoir.CloseConnection parameter to skip copying record to generic list collection.
Download
Files:
In my previous article. I have used List collection to copies all the
records from the SqlDataReader to a collection. Now, in this article i will not
use to add the records into generic list collection, I will skip copying step.
For this, I am using CommandBehavior.CloseConnection parameter that you can pass
to ExecuteReader() method. It will close connection automatically when all
records have been fetched from the SQLDataReader.
return
cmd.ExecuteReader(CommandBehavior.CloseConnection);
Default.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1">
</asp:GridView>
<asp:ObjectDataSource TypeName="class1" SelectMethod="All" ID="ObjectDataSource1"
runat="server"></asp:ObjectDataSource>
</div>
</form>
</body>
</html>
Web.Config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Employee" connectionString="Data
Source=GURJEET-PC\GURJEET;Initial Catalog=master;User ID=sa;Password=gurjeetsingh" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true" targetFramework="4.0"/>
</system.web>
</configuration>
App_Code\class1.vb
Imports
Microsoft.VisualBasic
Imports
System.Data
Imports
System.Data.SqlClient
Imports
System.Web.Configuration
Public Class Class1
Private Shared ReadOnly
Connection_string As
String
Shared
Sub New()
Connection_string =
WebConfigurationManager.ConnectionStrings("Employee").ConnectionString
End Sub
Public
Function All() As SqlDataReader
Dim con
As New SqlConnection(Connection_string)
Dim cmd
As New
SqlCommand("SELECT
EMP_fname,Emp_lname,Emp_country,Emp_phone_no,Emp_salary from employee",
con)
con.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function
End Class
Output
