c# - Connecting to connectionString in app.config

Question

I have my connection string stored in App.Config

<connectionStrings>
 <clear />
 <add name="CTaC_Information_System.Properties.Settings.CIS_beConn"
     connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot;\serverfileCIS DataDatabaseCIS_be.accdb&quote;;Jet OLEDB:Database Password=123" 
     providerName="System.Data.OleDb" />

Then when I go to my main.xaml.cs I type in the following:

string cisconn = ConfigurationManager.ConnectionStrings["CTaC_Information_System.Properties.Settings.CIS_beConn"].ConnectionString;`

I found that answer on Stack Overflow when searching, but some were say to put var but when I typed var it wouldn't recognize it so I went with the string method.

When I go to type cisconn.Open(); the option isn't there. I am referencing System.Configuartion;,System.Data.Sql; System.Data.SqlClient; and System.Data.OleDb;.

Can someone show / tell me how I can connect to the database from c#? I'm trying to test the connection when my application runs but I can't figure it out.

Answer 1

The connection string is just a string, its meant to be used in your connection, so you should do:

public void DoSomeDatabaseOp()
{
    string cisconn = ConfigurationManager.ConnectionStrings["CTaC_Information_System.Properties.Settings.CIS_beConn"].ConnectionString;

    using (OleDbConnection conn = new OleDbConnection(cisconn))
    {
        conn.Open();
        //Create your commands or do your SQL here.
    }
}

You should create/destroy the connection inside the method you are using it in. Don't keep a reference to it in the root of the class object. This keeps the connections clean and open if you aren't doing database operations.

If you really wanted to though, you could do this:

class MyClass
{
    OleDbConnection _rootConn;
    string _connStr;

    public MyClass()
    {
        _connStr = string cisconn = ConfigurationManager.ConnectionStrings["CTaC_Information_System.Properties.Settings.CIS_beConn"].ConnectionString;
        _rootConn = new OleDbConnection(_connStr);
    }

    public void DoSomeDatabaseOp()
    {
        //Use _rootConn here
    }
}

BUT the class should implement IDisposable so that it can dispose of the connection properly! How to implement IDisposable is beyond the scope of the answer, but look up how to implement it properly.