Retain DataTable value across multiple functions using C# and VB.Net

Last Reply on May 04, 2018 12:36 AM By kalpesh

Posted on May 04, 2018 12:00 AM

Is it possible to use a DataTable across multiple functions in VB.net 

Public Sub Foo_Click.....
Dim dt As New DataTable()
Sql query is created
Using dat AS New SqlDataAdapter(cmd)
dat.Fill(dt)
Stuff done...
End Sub

Public Sub Foo2_Click.....
reuse DataTable
End Sub

I've tried, within the same Sub

Select Case 
and 
If sender Is Foo Then
End If
If sender Is Foo2 Then
End If

but I can't retrieve the query results into the Second situation.

You are viewing reply posted by: kalpesh on May 04, 2018 12:36 AM.
Posted on May 04, 2018 12:36 AM

Refer the below sample code for your reference.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnFirst" runat="server" Text="First" OnClick="OnclickFirst" />
        <br />
        <asp:GridView ID="gvCustomerDetails1" runat="server">
        </asp:GridView>
        <br />
        <br />
        <asp:Button ID="btnSecond" runat="server" Text="Second" OnClick="OnclickSecond" />
        <br />
        <asp:GridView ID="gvCustomerDetails2" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

 VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

private DataTable CustomerDetails
{
    get
    {
        return ViewState["CustomerDetails"] != null ? (DataTable)ViewState["CustomerDetails"] : null;
    }
    set
    {
        ViewState["CustomerDetails"] = value;
    }
}

protected void OnclickFirst(object sender, EventArgs e)
{
    // Check the  the Viestate property 
    if (CustomerDetails == null)
    {
        CustomerDetails = GetData("SELECT TOP 5 [CustomerID]  ,[CompanyName] ,[ContactName]  ,[City] FROM [Customers]");
    }
    // Rest code
    gvCustomerDetails1.DataSource = CustomerDetails;
    gvCustomerDetails1.DataBind();
}

protected void OnclickSecond(object sender, EventArgs e)
{
    // Check the  the Viestate property 
    if (CustomerDetails == null)
    {
        CustomerDetails = GetData("SELECT TOP 5 [CustomerID]  ,[CompanyName] ,[ContactName]  ,[City] FROM [Customers]");
    }
    // Rest code
    gvCustomerDetails2.DataSource = CustomerDetails;
    gvCustomerDetails2.DataBind();
}

private DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {

        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

VB.Net

Private Property CustomerDetails As DataTable
    Get
        Return If(ViewState("CustomerDetails") IsNot Nothing, CType(ViewState("CustomerDetails"), DataTable), Nothing)
    End Get

    Set(ByVal value As DataTable)
        ViewState("CustomerDetails") = value
    End Set
End Property

Protected Sub OnclickFirst(ByVal sender As Object, ByVal e As EventArgs)
    'Check the  the Viestate property 
    If CustomerDetails Is Nothing Then
        CustomerDetails = GetData("SELECT TOP 5 [CustomerID]  ,[CompanyName] ,[ContactName]  ,[City] FROM [Customers]")
    End If
    'Rest code
    gvCustomerDetails1.DataSource = CustomerDetails
    gvCustomerDetails1.DataBind()
End Sub

Protected Sub OnclickSecond(ByVal sender As Object, ByVal e As EventArgs)
    'Check the  the Viestate property 
    If CustomerDetails Is Nothing Then
        CustomerDetails = GetData("SELECT TOP 5 [CustomerID]  ,[CompanyName] ,[ContactName]  ,[City] FROM [Customers]")
    End If
    'Rest code
    gvCustomerDetails2.DataSource = CustomerDetails
    gvCustomerDetails2.DataBind()
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
            End Using
        End Using
        Return dt
    End Using
End Function

Screenshot