CODEWITHSUNDEEP
c#asp.netconnection-stringswitch-statement
LostLord
LostLord

Reputation: 2329

How Can We Have two Connection Strings In Web.Config And Switch Betweeen Them In Code Behind?

When I add two connection strings in the web.config, an error appears that tells me I can't add two connection strings in the web.config.

I want the upper job because I have 2 databases and I want transfer data from another to the other one.

Would you please show me a way for doing that?

Upvotes: 10

Views: 31675

Answers (7)

Soft Dev Ahmad yar khan
Soft Dev Ahmad yar khan

Reputation: 494

Step:1 In "Web.Config" File: Add a new connectionstring with New Name

 <connectionStrings>
<add name="str_1" connectionString="Data Source=Server Name;Min Pool Size=0;Max Pool Size=5000;Pooling=true; Initial Catalog= First DataBase Name;User ID=sa;Password=; TrustServerCertificate=True;" providerName="System.Data.SqlClient" /> 
<add name="str_2" connectionString="Data Source=Server Name;;Min Pool Size=0;Max Pool Size=5000;Pooling=true; Initial Catalog=Second DataBase Name; User ID=sa;Password=;TrustServerCertificate=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Step2: MaKe a New Class "MyConnection_2" :For connectionString "str_2"

public class MyConnection_2
{

    public DataSet ds = new DataSet();
    public SqlDataAdapter da = new SqlDataAdapter();
    public SqlCommandBuilder cmdb = new SqlCommandBuilder();
    public SqlConnection con_2;
    public SqlCommand cmd;

    #region Constructor
    public MyConnection_2()
    {
        con_2 = new SqlConnection(ConfigurationManager.ConnectionStrings["str_2"].ToString());
        cmd = new SqlCommand("", con_2);
        da.SelectCommand = cmd;
        con_2.Open();
    }
    #endregion
    public void Open()
    {
        if (con_2.State == ConnectionState.Closed)
        {
            con_2.Open();
        }
    }
    public void Close()
    {
        if (con_2.State == ConnectionState.Open)
        {
            con_2.Close();
        }
    }
}

Step:3 Now, Use this class "MyConnection_2" Where you Uses Second DataBase

MyConnection_2 con_2 = new MyConnection_2();

Upvotes: 0

anil shrestha
anil shrestha

Reputation: 3190

Here are the steps:

 public class KisanDbContext : DbContext
    {
        public KisanDbContext() : base(nameOrConnectionString: "DbContext") { }
        public KisanDbContext(string conn) : base(nameOrConnectionString: conn) { }
    }

Access methods:

 private readonly KisanDbContext db;
 private readonly KisanDbContext db_old;

Inside your constructor:

public className()
{
    db = new KisanDbContext();
    db_old = new KisanDbContext("DbContextBackup");
}

This one is for your default connection.

public KisanDbContext() : base(nameOrConnectionString: "DbContext") { }

And if you want your multiple database connection please used this convention, where you can pass multiple database connection strings using same dbcontext.

public KisanDbContext(string conn) : base(nameOrConnectionString: conn) { }

Remember to have your connection string with different name as:

<connectionStrings>
     <add name="DbContext" connectionString="Server=A;Database=ABC;User 
     Id=ccd;Password=***"  providerName="System.Data.SqlClient" />
    <add name="DbContextBackup" connectionString="B;Database=DEF;User Id=axx;Password=***;   
    providerName="System.Data.SqlClient" />  
</connectionStrings>

Upvotes: 0

Deepnath Kundu
Deepnath Kundu

Reputation: 41

We can declare multiple connection strings under Web.Config or App.Config:

<connectionStrings>
    <add name="SourceDB" connectionString="..." />
    <add name="DestinationDB" connectionString="..." />
</connectionStrings>

In DAL you can access connection strings according to your requirements:

string SounceConnection = ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString;
string DestinationConnection = ConfigurationManager.ConnectionStrings["DestinationDB"].ConnectionString;

Upvotes: 1

Oded
Oded

Reputation: 499002

When you add a connection string, you name it.

You can access each such connection string and assign it to a different variable, passing that connection string to your data access layer.

In the config file:

<connectionStrings>

  <add name="Sales" 
       providerName="System.Data.SqlClient"
       connectionString= "server=myserver;database=Products;uid=<user name>;pwd=<secure password>" />

  <add name="NorthWind" 
       providerName="System.Data.SqlClient" 
       connectionString="server=.;database=NorthWind;Integrated Security=SSPI" />

</connectionStrings>

In your code:

 var conn1 = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;
 var conn2 = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;

Upvotes: 20

Darin Dimitrov
Darin Dimitrov

Reputation: 1038780

Simply put those strings in your web.config:

<connectionStrings>
    <add name="CS1"
         connectionString="SOME CONNECTION STRING"
         providerName="System.Data.SqlClient" />
    <add name="CS2"
         connectionString="SOME OTHER STRING"
         providerName="System.Data.SqlClient" />
</connectionStrings>

And then pick the one you wish in your code:

string cs = ConfigurationManager.ConnectionStrings["CS2"].ConnectionString;

Upvotes: 5

Etienne de Martel
Etienne de Martel

Reputation: 36852

Strange, because you can specify multiple connection strings. They simply must have different names.

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

You can add all the connectionstrings that you want to web.config. But they must have different names.

Upvotes: 1

Related Questions