Avatar of Olukayode Oluwole
Olukayode Oluwole
Flag 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
PostgreSQLDatabasesC#

Avatar of undefined
Last Comment
Olukayode Oluwole

8/22/2022 - Mon
pepr

You probably want to use something like

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

Open in new window

Olukayode Oluwole

ASKER
I have a syntax error  (see screen below)

Syntax error on Script Sent
What could i have done wrong

Olukay
pepr

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Olukayode Oluwole

ASKER
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
pepr

Add private static to the beginning of the line 39.
Olukayode Oluwole

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pepr

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 Oluwole

ASKER
While i try please confirm that your script is for postgreSQL

Olukay
Olukayode Oluwole

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
pepr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Olukayode Oluwole

ASKER
Yes it did

I actually  thought i had notified you

Sorry for the delay.

And thanks for the help

Olukay
pepr

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 Oluwole

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

Thanks once again
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.