Use CommandBehaviour.CloseConnection to return multiple rows in VB.NET
You can use CommandBehavoir.CloseConnection parameter to skip copying record to generic list collection.
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
