Connect to database in sepaerate method of my SqlCommand

Question

I have a form that checks whether values are in a database before adding them. Each field is in a different table, and to keep everything clean, I have a checkExists method for each field. Is there a way to have a separate method that connects to the database, so that I don't have to connect in every field method?

I'd like to do something like this so that my code is less messy:

public void SetConnection()
{
    SqlConnection myConnection = 
        new SqlConnection("user id=[username];" +
        "password=[password];" +
        "server=[server];" +
        "database=[db_name];");

    try
    {
        myConnection.Open();
    }
    catch(Exception e)
    {
        Console.WriteLine("Unable to Connect");
    } 
}

public Boolean CheckData_Company(string[] items)
{
    Class_DB set_conn = new Class_DB();

    try
    {
        set_conn.SetConnection();
    }
    catch(Exception e)
    {
         Console.WriteLine(e.ToString());
    }

    //check that item does not already exist
    string query_string = "SELECT * FROM CR_Company WHERE ([CompanyName] = @companyName";

    SqlCommand check_Company = new SqlCommand(query_string, set_conn);
    check_Company.Parameters.AddWithValue("@CompanyName", items[0]);
    int CompanyExist = (int)check_Company.ExecuteScalar();

    if(CompanyExist > 0)
    {
        return true;
    }
    else
    {
        return false;
    }
}

But I get a

local variable set_conn
Argument 2: Cannot Convert from Class_DB to System.Data.SqlClient.SqlConnection

I understand the error, so what can I do to return the correct value, or do I have to establish a connection within my CheckData_Comany() method?

Answer 1

Your method SetConnection should be returning SqlConnection back like:

public SqlConnection SetConnection()
{

    SqlConnection myConnection = new SqlConnection("user id=[username];" +
                                            "password=[password];" +
                                            "server=[server];" +
                                            "database=[db_name];");
    try
    {
        myConnection.Open();
    }
    catch(Exception e)
    {
        Console.WriteLine("Unable to Connect");
    }

    return myConnection;

}

and then you can have something like:

SqlConnection connection = set_conn.SetConnection();

and then pass it in SqlCommand constructor as parameter :

SqlCommand check_Company = new SqlCommand(query_string, connection);

Your complete method implementation would become :

public Boolean CheckData_Company(string[] items)
{
    bool Exists = false;
    Class_DB set_conn = new Class_DB();
    SqlConnection connection = null;
    try
    {
        connection = set_conn.SetConnection();

        //check that item does not already exist
        string query_string = "SELECT * FROM CR_Company WHERE ([CompanyName] = @companyName";
        SqlCommand check_Company = new SqlCommand(query_string, set_conn);
        check_Company.Parameters.AddWithValue("@CompanyName", items[0]);
        int CompanyExist = (int)check_Company.ExecuteScalar();

        if(CompanyExist > 0)
            Exists =  true;


     }
     catch(Exception e)
     {
        Console.WriteLine(e.ToString());
     }
     finally
     {
        connection.Close();
     }

    return Exists;
 }

and important thing to note is do not forget the close the connection finally by calling connection.Close(), otherwise it might cause eating up the resources that shouldn't happen when we are done with querying the database and we should release the resources occupied.