Link to home
Start Free TrialLog in
Avatar of Olukayode Oluwole
Olukayode OluwoleFlag for Canada

asked on

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

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
Avatar of pepr
pepr

You probably want to use something like

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

Open in new window

Avatar of Olukayode Oluwole

ASKER

I have a syntax error  (see screen below)

User generated image
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.
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)

User generated image
What do you suggest i try next

Olukay
Add private static to the beginning of the line 39.
Adding private static does not seem to fix the isssue

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

User generated image
User generated image
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


User generated image
While i try please confirm that your script is for postgreSQL

Olukay
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)

User generated image


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
ASKER CERTIFIED SOLUTION
Avatar of pepr
pepr

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I forgot to tell you though that string was missing in front of pgrstring

Thanks once again