Select database from multiple Connection Strings using C# and VB.Net in ASP.Net

Hi democloud,

You need to create a class and write a method to get the connection string based on the client name.

Then use the class and call the method of your Master page or home page and set the connection in the Session to access in other page.

Refer below code.

Connection Class

C#

using System.Configuration;

public static class Connection
{
    public static string GetConnectionString(string clientName)
    {
        string connStr = "";
        if (clientName.ToLower() == "client1")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db1"].ConnectionString);
        }
        else if (clientName.ToLower() == "client1")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db2"].ConnectionString);
        }
        else if (clientName.ToLower() == "client3")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db3"].ConnectionString);
        }

        return connStr;
    }
}

VB.Net

using System.Configuration;

public static class Connection
{
    public static string GetConnectionString(string clientName)
    {
        string connStr = "";
        if (clientName.ToLower() == "client1")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db1"].ConnectionString);
        }
        else if (clientName.ToLower() == "client1")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db2"].ConnectionString);
        }
        else if (clientName.ToLower() == "client3")
        {
            connStr = (ConfigurationManager.ConnectionStrings["client_db3"].ConnectionString);
        }

        return connStr;
    }
}

VB.Net

Imports Microsoft.VisualBasic
Imports System.Configuration

Public Class ConnectionVB
    Function GetConnectionString(ByVal clientName As String) As String
        Dim connStr As String = ""
        If clientName.ToLower() = "client1" Then
            connStr = (ConfigurationManager.ConnectionStrings("client_db1").ConnectionString)
        ElseIf clientName.ToLower() = "client1" Then
            connStr = (ConfigurationManager.ConnectionStrings("client_db2").ConnectionString)
        ElseIf clientName.ToLower() = "client3" Then
            connStr = (ConfigurationManager.ConnectionStrings("client_db3").ConnectionString)
        End If

        Return connStr
    End Function
End Class

Page Load (Master or Home Page)

C#

protected void Page_Load(object sender, EventArgs e)
{
    //Your logic to identify the client name.
    string clientName = "client1";
    string connStr = Connection.GetConnectionString(clientName);
    Session["Connection"] = connStr;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    'Your logic to identify the client name.    
    Dim clientName As String = "client1"
    Dim connStr As String = Connection.GetConnectionString(clientName)
    Session("Connection") = connStr
End Sub

Use the connection from Session in any page.

C#

protected void btn_login_Click(object sender, EventArgs e)
{
    try
    {
        string constr = Session["Connection"].ToString();
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand("SELECT * FROM Admin WHERE UserName=@username AND Password=@password", con);
        cmd.Parameters.AddWithValue("@username", txtUsername.Text);
        cmd.Parameters.AddWithValue("@password", txtPassword.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {

        }
        else
        {
        }
    }
    catch (Exception ex)
    {
    }
}

VB.Net

Protected Sub btn_login_Click(ByVal sender As Object, ByVal e As EventArgs)
    Try
        Dim constr As String = Session("Connection").ToString()
        Dim con As SqlConnection = New SqlConnection(constr)
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Admin WHERE UserName=@username AND Password=@password", con)
        cmd.Parameters.AddWithValue("@username", txtUsername.Text)
        cmd.Parameters.AddWithValue("@password", txtPassword.Text)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)

        If dt.Rows.Count > 0 Then
        Else
        End If

    Catch ex As Exception
    End Try
End Sub