Use CommandBehaviour.CloseConnection to return multiple rows in VB.NET

You can use CommandBehavoir.CloseConnection parameter to skip copying record to generic list collection.
  • 3718
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
 
display-table-data-in-vb.net.gif
 

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.