use two connection string in windows application

Question

0.00/5 (No votes)

See more:

Hello All.

I m working on a windows application.
it ll be used in two locations.
sql server ll b at one location.
so i hv given two connection strings in app-config file with two different names. (Local and Web)

now how to check which connection is available?

for connection i hv written following code in a class.

C#

public sealed class Connection : MyDBManager
   {
       private static string _dbUser, _dbPwd, _dataSource, _dataProvider;
       public string DBUser { get { return _dbUser; } set { _dbUser = value; } }
       public string DBPwd { get { return _dbPwd; } set { _dbPwd = value; } }
       public string DBSource { get { return _dataSource; } set { _dataSource = value; } }
       public string Provider { get { return _dataProvider; } set { _dataProvider = value; } }


       public Connection()
       {
           ConnectToDB();
       }

       public Connection(string ServerName)
       {
           ConnectToDB(ServerName);
       }

       private bool ConnectToDB()
       {
           getDBValues();
           string connStr = ConfigurationManager.ConnectionStrings["LocalServer"].ConnectionString;
           base.ConnectionString = connStr;
           try
           {
               base.Open();
               return true;
           }
           catch
           {
               return false;
           }
       }

       private bool ConnectToDB(string ServerName)
       {
           if (ServerName == "DelhiServer")
           {
               getDBValues();
               string connStr = ConfigurationManager.ConnectionStrings["Web"].ConnectionString;
               base.ConnectionString = connStr;
               try
               {
                   base.Open();
                   return true;
               }
               catch
               {
                   return false;
               }
           }
           else
           {
               return false;
           }
       }

       public bool DisposeConnection()
       {
           base.RemoveParameters();
           base.Close();
           base.Dispose();
           return true;
       }

       private void getDBValues()
       {
           //Cryptograph crypt= new Cryptograph();
           //string _dataProvider;
           //_dbUser = crypt.Decrypt(System.Configuration.ConfigurationSettings.AppSettings["dbUser"],PW_KEY);
           //_dbPwd = crypt.Decrypt(System.Configuration.ConfigurationSettings.AppSettings["dbPwd"], PW_KEY);
           //_dataSource = crypt.Decrypt(System.Configuration.ConfigurationSettings.AppSettings["dbSource"], PW_KEY);
           //_dataProvider=System.Configuration.ConfigurationSettings.AppSettings["dataProvider"];

           _dataProvider = "SQLSERVER";
           if (_dataProvider.ToUpper() == "OLEDB")
           {
               base.ProviderType = DataProvider.OleDb;
           }
           else if (_dataProvider.ToUpper() == "ORACLE")
               base.ProviderType = DataProvider.Oracle ;
           else if (_dataProvider.ToUpper() == "SQLSERVER")
               base.ProviderType = DataProvider.SqlServer;

           else if (_dataProvider.ToUpper() == "ODBC")
               base.ProviderType = DataProvider.Odbc;
           else
               base.ProviderType = DataProvider.OleDb;

       }
   }

please kindly help me to do this.

thanks in advance

Posted 23-Oct-13 23:25pm

dibyaaryan007

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BulletVictim · Accepted Answer · 2013-10-25T03:26:00

This might not be the most elegant or efficient way to do it, but it does work, This code first tries connecting with windows authentication and if that fails it will try SQL authentication. In essence it works on the same principal as your question.
All you will need to do is use the two different connection strings at different stages within the try catches.

C#

public void Connect()
       {
           try
           {
               string connectionString = "Data Source='" + ServerAdrress.Text + "';Initial Catalog='" + DatabaseSelection.SelectedItem.ToString() + "';Integrated Security=SSPI;";
               string SQLconnectionString = "Data Source='" + ServerAdrress.Text + "';Initial Catalog='" + DatabaseSelection.SelectedItem.ToString() + "';Persist Security Info=True;User ID='" + ConnectionUser.SelectedItem.ToString() + "';Password='" + lblPassword.Text + "'";
               try
               {
                   conn = new SqlConnection(connectionString);
                   conn.Open();
               }
               catch (Exception ex)
               {
                   try
                   {
                       conn = new SqlConnection(SQLconnectionString);
                       conn.Open();
                   }
                   catch (Exception)
                   {
                       MessageBox.Show(ex.Message);
                       conn.Close();
                   }
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }

       }

Hope this helps.

sjelen · Accepted Answer · 2013-10-25T04:12:00

Solution 2

You only need one connection string, simply distribute different config files to each location.

Posted 25-Oct-13 4:12am

sjelen