Never underestimate the power of Passion!

Tuesday, 7 February 2017

On 02:01 by Vardan Kumar in    No comments
Dynamic Connection String C#

Dynamic connection String

In the course of development there may arise a situation when we require to ask the configuration details from the user in order to establish a database connection. This situation arises whenever the application is to be used by a group of users whose Username and Password are different or the users may want to access database through different service or server. Whatever the case may be we'll define a user-defined method to build the connection string for us. Text Box are the more likely used control which we use to retrieve information from the user.

Let us build the logic for the same

Well now lets see how it works.......

1.String sConnectionString collects the string returned by our user-defined method CreateConnectionString()

2. Now the method collects value entered by the users in the text box controls and stores each value in separate string variables. 

3. Method then checks if any of the variables is empty or null, if yes method would return null and display message box prompting user to enter all the configuration details.

4. If every text box is populated then our method completes the connection string and returns the same.

5. Now if the pocket variable(identifier collecting return from method) received null then it would return the control.

6. If pocket variable has something in it then it will try to establish a database connection if everything goes right that is connection string is correct then connection to database will be successful else an exception will be caught displaying relevant error message box.

Dynamic Connection String c#
Configuration details being fetched from text box

Source Code:

  string sConnectionString = CreateConnectionString();
                if (string.IsNullOrEmpty(sConnectionString))
                {
                    return;
                }
try
{
using (OracleConnection connection = new OracleConnection(sConnectionString))
                {
                     connection.Open();
                    using (OracleCommand command = new OracleCommand("SELECT count(posts) from website where website_name='cspassion'", connection))
                    {
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                pocket = Convert.ToInt32(reader[0]);
                            }
                       //     reader.Close();               Not required since we are in "using" block
                        }
                    }
                 }
                    connection.Close();
     }

catch(Exception ex)
{
      MessageBox.Show("ex.Message");
}

private string CreateConnectionString()
        {
            string sServerName = null, sPortNum = null, sServiceName = null, sUserName = null, sPassword = null;
            // Read the values from the TextBox and populate the above strings            
                sUserName = txt_UserName.Text;
                sPassword = txt_Password.Text;
                sServerName = txt_Host.Text;
                sPortNum = txt_PortNo.Text;
                sServiceName = txt_ServiceName.Text;
           
            if (String.IsNullOrEmpty(sUserName) || String.IsNullOrEmpty(sServiceName) || String.IsNullOrEmpty(sPassword) || String.IsNullOrEmpty(sPortNum) || String.IsNullOrEmpty(sServerName))
            {
                MessageBox.Show("Please Check if the configuration values are entered");
                return null;
            }
            s_oRcConnectionString =
                    "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = " + sServerName + " )(PORT = " +
                    sPortNum + " ))  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = " + sServiceName +
                    " ))); User Id = " + sUserName + "; Password = " + sPassword + ";";

            return s_oRcConnectionString;
        }


0 comments:

Post a comment