We help IT Professionals succeed at work.

How can i avoid repeating a connection string already defined in the appconfig file

Olukayode Oluwole
Olukayode Oluwole asked
on
I have a connection string in the appconfig file of my c# application  (see below) that writes to a postgresql database.

AppConfig

[]<connectionStrings>
    <add name="Tournaments" connectionString="Host = 138.197.165.89; Database = tournaments; Port = 5432; Username = postgres" />
  </connectionStrings>[/code]

In the method below that goes into my Database  I have had to repeat the string again  (see below)

[] public class PgrConnector : IDataConnection
    {
        private const string db = "tournaments";
        private const string pgrstring = "Host = 138.197.165.89; Database = tournaments; Port = 5432; Username = postgres";
     
        public void ClosePayrollTestRun(MonthPayModel model)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {

                using (var command = new NpgsqlCommand("public.sppayrollproccesor_closepayrolltestrun", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    //foreach (DataRow row in dataSource.)
                    //{
                    command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcompany });
                    command.Parameters.Add(new NpgsqlParameter("mnthyearx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticmmyyyy });
                    //  command.Parameters.Add(new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticstaffno });
                    // command.Parameters.Add(new NpgsqlParameter("entcodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticentcode });
                    string output = "";
                    output = Convert.ToString(command.ExecuteScalar());
                    LoginDetails.staticpayrollproccesorstatus = output;
                    if (output == "-1")
                    {
                        StandardMessages.PayrollScriptError();
                    }
                    return;
                    //}
                }
            }
        }[/code]

I want to avoid the repetition by changing

 using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))

so that it does not use pgrstring  but the connectionstring coming from my Appconfig file

What would the correct syntax  for the using statement

Thanks

Olukay
Comment
Watch Question

You probably want to use something like

    ConnectionStringSettingsCollection cs = System.Configuration.ConfigurationManager.ConnectionStrings;
    myConnectionString = cs["Tournaments"].ConnectionString;

Open in new window

Olukayode OluwoleSystems Analyst

Author

Commented:
I have a syntax error  (see screen below)

Syntax error on Script Sent
What could i have done wrong

Olukay
I do not know what syntax error it shows. Possibly, you should add string in front of myConnectionString (to declare the variable). Or (better) use pgrstring instead of the myConnectionString.
Olukayode OluwoleSystems Analyst

Author

Commented:
Sorry I did not display the Error. I actually thought i did.

I have tried the 2 new suggestions and they both have same error  (see below)

New Syntax error displayed
What do you suggest i try next

Olukay
Add private static to the beginning of the line 39.
Olukayode OluwoleSystems Analyst

Author

Commented:
Adding private static does not seem to fix the isssue

See the 2 errors displayed while moving mouse over Tournaments  and Connection respectfully

Error while mouse on Tournaments
Error While mouse on Connection
What do you suggest i try next

Regards


Olukay
I my code, I am using almost exactly that. The difference is that I am using the using System.Configuration; at the top of the file, and I have the name of the connection string (here "Tournaments") stored in the static string variable -- but that should not be a problem (see the image below that show my code with the original commented out, and with explicit string "Tournaments" -- no red waves).

using System.Configuration;

    //... snip ...

    private static ConnectionStringSettingsCollection cs = ConfigurationManager.ConnectionStrings;
    pgrstring = cs["Tournaments"].ConnectionString;

Open in new window


Original code with the test "Tournaments" literal.
Olukayode OluwoleSystems Analyst

Author

Commented:
While i try please confirm that your script is for postgreSQL

Olukay
Olukayode OluwoleSystems Analyst

Author

Commented:
I have tried to replicate what you have done but without success.

1.  I have using system.Configurations

2. I repeated your scrip  (see  screen below)

Error Displayed while duplicating your Script


The error displayed is

 The name 'appConnectionString' does not exist in the current context

Where am i supposed to define (or where did you define it) appConnectionString before using it

and is your script for postgreSQl

Thanks
On the lines 38 and 39 use just
    private static ConnectionStringSettingsCollection cs = ConfigurationManager.ConnectionStrings;
    pgrstring = cs["Tournaments"].ConnectionString;

Open in new window


Remove the static public string GetAppConnectionString() -- it was just illustration on how I use it.
Does it work now?
Olukayode OluwoleSystems Analyst

Author

Commented:
Yes it did

I actually  thought i had notified you

Sorry for the delay.

And thanks for the help

Olukay
You are welcome. I was just curious. :) When anything is not working, I am learning from searching for the solution.
Have a nice day,
Petr
Olukayode OluwoleSystems Analyst

Author

Commented:
I forgot to tell you though that string was missing in front of pgrstring

Thanks once again