How to make my sql connection available to other parts in the same class

Question

I am new to .Net and C# and I have been struggling to get my head round on how to utilise a sql connection created in one section of the code and use it in another. I got 2 buttons on my form. One connects to the database and the other inserts to a table. How do I use the connection variable when inserting to the table?

I hope this makes sense. Thanks

namespace SQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

    private void btnConnect_Click(object sender, EventArgs e)
    {

        SqlOperations connect = new SqlOperations();
        connect.connectToSQL("server=localhost\\SQLExpress;", "database=Cromwell; ");
    }

    private void btnAddToDatabase_Click(object sender, EventArgs e)
    {
        SqlOperations addToTable = new SqlOperations();
        addToTable.InsertToTable("InputDir", "C:\\");

    }

}


public class SqlOperations
{

    public bool connectToSQL(string sqlHost, string database)
    {

        SqlConnection SqlConnect = new SqlConnection("user id=userid;" +
                                   "password=validpassword;" + sqlHost +
                                   "Trusted_Connection=yes;" +
                                   database + "connection timeout=30");

        try
        {
            SqlConnect.Open();
            MessageBox.Show("Connected to SQL Express");

            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return false;
        }

    }

    public bool InsertToTable(string columnName, string value)
    {

        SqlCommand myCommand = new SqlCommand();

        myCommand.Connection = **SqlConnect**; // THIS BIT COMPLAINS

        myCommand.CommandText = "INSERT INTO Config (" + columnName + ") " + 
                    "Values ('" + value + "')";

    }
}

}

You could declare the connection as a global variable, but I'd advance against it. Don't keep connection open longer than it is needed for actual operation. Open it before INSERT and close right after. — Yuriy Galanter, Mar 06 '14 at 15:37
The pattern it looks like you're trying to achieve is described pretty well here: http://stackoverflow.com/a/7911659/328193 — David, Mar 06 '14 at 15:44

Sudhakar Tillapudi · Answer 1 · 2014-03-06T15:43:06.317

Solution 1: You can create your connection string as constant string variable and access it using class name from whereever you need it as constant variables are implicitly static(you can access them as global variables)

Try This:

Class MyConnectionString
{
const string strCon="user id=userid;password=validpassword;
                     server=localhost\\SQLExpress;database=Cromwell;
                     Trusted_Connection=yes;connection timeout=30";
}

while accessing :

SqlConnection scon=new SqlConnection(MyConnectionString.strCon);

Solution 2:

Create your connection string in Configuration file and access it.

<connectionStrings>
<add name="myConString" 
connectionString="user id=userid;password=validpassword;
                     server=localhost\\SQLExpress;database=Cromwell;
                     Trusted_Connection=yes;connection timeout=30" />
</connectionStrings>

use it whenever you need it:

string ConnectionString = 
ConnfigurationManager.ConnectionStrings["myConString"].ConnectionString;

Thanks for your suggestion, I was not aware of config file :-) — gogi, Mar 07 '14 at 08:27

Mez · Accepted Answer · 2014-03-07T08:56:11.183

0

Check this link, http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C . It is a beginners guide to access SQL Databases using c#.NET.

You can also add the connection string in your web.config, or app.config and then access it from c# code.

C#

// Add a reference at the top of your code file

using System.Configuration;

// Within the code body set your variable

string cs = ConfigurationManager.ConnectionStrings["connectionStringName"].ConnectionString;

VB

' Add a reference at the top of your code file

Imports System.Configuration

' Within the code body set your variable

Dim cs as String = ConfigurationManager.ConnectionStrings("connectionStringName").ConnectionString

Obviously remember to add this (With your own connection string) in your web.config

<connectionStrings>
<add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />

edited Mar 07 '14 at 08:56

answered Mar 06 '14 at 15:42

Mez

4,666
4
29
57

Thanks for this. If you use the config files does it mean that you can change these and the program will use the new values without the need for recompiling or would you have to re-publish your project for the changes to take effect? – gogi Mar 07 '14 at 08:53
If you change the web.config, the project will automatically re-compile. When ready, yes it will take the new changes. – Mez Mar 07 '14 at 08:54
Remember to mark as answered if the answer led you to fix your problem. – Mez Mar 07 '14 at 09:08

score 0 · Answer 3 · answered Mar 06 '14 at 15:42

I like that you've defined a class to connect to SQL. You can use that class to manage the lifecycle of your SQL connections, which is a good thing. It would also be good if it handled the connection credentials too so your callers don't have to know what it is. How about this:

public class SqlOperations
{
    private SqlConnection Connect()
    {
        ... Get SQL credentials here
        ... Open and return connection here
    }

    public bool InsertToTable(string columnName, string value)
    {
        using (var conn = Connect())
        {
            using (SqlCommand myCommand = new SqlCommand())
            {
                myCommand.Connection = conn;
                ... do your myCommand stuff here
            }
        }
    }
}

Then in your form ditch the connect-to-db button - it's managed for you! All you need is your insert button with this event:

private void btnAddToDatabase_Click(object sender, EventArgs e)
{
    SqlOperations addToTable = new SqlOperations();
    addToTable.InsertToTable("InputDir", "C:\\");
}

That makes perfect sense now, I got some good suggestions here and I appreciate all the input given. Thanks — gogi, Mar 07 '14 at 08:29

How to make my sql connection available to other parts in the same class

3 Answers3