C# SQL connection String in class connection issue

Question

So I noticed in my code I had a lot of repetitive connection strings and decided to clean it up a little bit.

My issue is, now that I've put the connection string into a seperate class I can no longer open the connection when using using (InfoTableConnection = new SqlConnection(infoTableConnString))

However if I don't use using it works fine.

I'm not quite understanding how it works I guess. Here is my code if someone could explain what exactly is happening once it's introduced to a class and/or how to fix it.

Connection Class: Connection.cs

class Connection
    {
        public static SqlConnection InfoTableConnection = null;
        public void InfoConnection()
        {
            string infoTableConnString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MTRInfoTables;Integrated Security=True";

            using (InfoTableConnection = new SqlConnection(infoTableConnString))

            InfoTableConnection.Open();

        }
    }

Sample Code from: MainForm.cs

private void zGradeCombo()
        {
            try
            {

                //Connection string from class.
                Connection connInfoTable = new Connection();
                connInfoTable.InfoConnection();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection.InfoTableConnection;
                cmd.CommandText = "SELECT * FROM [dbo].[Item] ORDER by [Type] ASC";

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    cmbType.Items.Add(reader["Type"].ToString());
                }

                //Close connection from "Connection" class
                Connection.InfoTableConnection.Close();
            }

            //Catch Exception
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, "SQL ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

The using keyword disposes of the object in question once it has left the using block, so your connection only exists until InfoTableConnection.Open(); is called, and then it is disposed of — Robert Woods
– Robert Woods, Commented Jan 23, 2017 at 15:32
So since it disposes the connection after it opens there's no way to use it in a class and call it from a separate form? — Mokey
– Mokey, Commented Jan 23, 2017 at 15:36
It can if you don't use using but then you will have to call InfoTableConnection.Dispose() when you're done — Robert Woods
– Robert Woods, Commented Jan 23, 2017 at 15:37

SirBirne · Accepted Answer · 2017-01-23 15:45:08Z

The using keyword makes sure that your object will be disposed when you reached the end of the scope so that all the resources will be cleaned up afterwards

using (InfoTableConnection = new SqlConnection(infoTableConnString))
{
        InfoTableConnection.Open();
} // <- At this point InfoTableConnection will be disposed (and closed)

Since you care about disposing in the code around you do not need the using block in the constructor of the class. But it would be a good idea to implement IDisposable on your Connection class and use it like this:

using(var con = new Connection())
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con.InfoTableConnection;
    cmd.CommandText = "SELECT * FROM [dbo].[Item] ORDER by [Type] ASC";

    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        cmbType.Items.Add(reader["Type"].ToString());
    }
}

In the Dispose() method of your connection you should dispose the SQL Connection.

DanHarrigan · Accepted Answer · 2017-01-23 16:00:37Z

1

If your goal is to have your connection string is one location, why not just place your connection string in your app.config (settings) file and reference it in the code from there?

app.config

<connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MTRInfoTables;Integrated Security=True" />
</connectionStrings>

code.cs

string myConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

You must include a reference to the System.Configuration.dll in your references to use ConfigurationManager.

This way, you can continue using your using statements they way that they were designed to be used.

answered Jan 23, 2017 at 16:00

DanHarrigan

404 bronze badges

1 Comment

Mokey Over a year ago

Thanks... This makes it a lot cleaner too :)

Collectives™ on Stack Overflow

C# SQL connection String in class connection issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related