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 do开发者_运维知识库ing that?

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;

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;

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

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

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;

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>

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();