C# How to have an object available in another method?

Question

I have two methods, in the ConnecttoDB method I create an object called connection, but i need that object in the second method to specify the SQLiteCommand. How can I have it, that the object is available in the InsertField Method ?

public class Database
{
    private static string _datasource;
    private static string _table;
    private static string _field;
    private static string _values;

    public static void ConnecttoDB(string datasource)
    {
        _datasource = datasource;
        SQLiteConnection connection = new SQLiteConnection();
        connection.ConnectionString = "Data Source: " + _datasource;
        connection.Open();
    }
    public static void InsertField(string table, string field, string values)
    {
        SQLiteCommand command = new SQLiteCommand(/*connection*/);

        _table = table;
        _field = field;
        _values = values;
        String.Format("INSERT INTO {0} {1} VALUES{2}", _table, _field, _values);
    }
}

Answer 1

Your class design is very odd. Why do you declare _table, _field and _values as class fields for no apparent reason, but don't do it for connection, which would solve your problem? You can use the table, field and values parameters directly, without assigning them to fields beforehand.

Let me suggest another approach:

public class Database
{
    private string _connectionString;

    // Pass the datasource through the constructor.
    public Database(string datasource)
    {
        _connectionString = "Data Source: " + datasource;
    }

    public void InsertField(string table, string field, object value)
    {
        using (var conn = new SQLiteConnection(_connectionString)) {
            string sql = String.Format("INSERT INTO {0} ({1}) VALUES (@1)",
                                       table, field);
            var command = new SQLiteCommand(sql, conn);
            command.Parameters.AddWithValue("@1", value);
            conn.Open();
            command.ExecuteNonQuery();
        }
    }
}

(Not tested, calling conventions might be different for SQLite)

I am using the object type for value which is compatible with any type. Also I am passing this value to the command as command-parameter. Like this, DateTime values, for instance, are automatically converted to the right format for SQLite. It also prevents SQL injection. (I hope that the table and field names are defined by the application and not entered by the user so.)

Because of the connection pooling, the system maintains connections open a while and reuses open connections automatically. Therefore the connection.Open(); call does not really reopen the connection physically every time it is called. On the other hand, this approach prevents connections to be open a very long time and using system resources without being used.

The using statement takes automatically care of closing the connection and disposing the resources used by the connection - even when an exception should occur before the code block reaches its end.

You can use it like this:

var db = new Database("my data source");
db.InsertField("myTable", "myField", 100);

---

UPDATE

If you need a variable number of parameters, I would use arrays for the fields and values:

public void InsertField(string table, string[] fields, object[] values)
{
    if (fields.Length != values.Length) {
        throw new ArgumentException(
            "The fields and values arrays must have the same length");
    }

    string fieldNames = String.Join(", ", fields); // ==> "name1, name2, name3"

    // ==> "@p0, @p1, @p2"
    string paramNames = String.Join(", ",
        Enumerable.Range(0, fields.Length)
            .Select(i => "@p" + i)
            .ToArray()
    );
    using (var conn = new SQLiteConnection(_connectionString)) {
        string sql = String.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                                   table, fieldNames, paramNames);
        var command = new SQLiteCommand(sql, conn);
        for (int i = 0; i < values.Length; i++) {
            command.Parameters.AddWithValue("@p" + i, values[i]);
        }
        conn.Open();
        command.ExecuteNonQuery();
    }
}

Usage:

var db = new Database("my data source");
db.InsertField("myTable", 
               new string[]{ "Name", "Quantity", "Price", "Date" }, 
               new object[]{ "Apples", 12, 2.65m, DateTime.Now });

Answer 2

To address the issue you're currently facing:

In this case you shouldn't actually be doing that. Your two methods should be one method:

public static void InsertField(string table, string field, string values)
{
    using (SQLiteConnection connection = new SQLiteConnection())
    {
        using (SQLiteCommand command = new SQLiteCommand(connection))
        {
            _table = table;
            _field = field;
            _values = values;
            String.Format("INSERT INTO {0} {1} VALUES{2}", _table, _field, _values);
            // and the rest of the code, incomplete in the example
        }
    }
}

Building a SQL connection object isn't an expensive process. And it's only a single line of code. So you're not actually saving much by offloading it to another method. What you are doing, however, is introducing a number of other potential runtime problems (as opposed to the easily-visible compile-time problem you currently have), such as resource leaks and disposed object errors.

Encapsulate the connection and command objects as close to their usage as possible, and dispose of them as soon as possible when you're done with them. The underlying system is very well optimized to re-create them when you need them again in another method (or the same method called again).

To address your question on a more literal level:

Consider the scope of the variable being passed from one method to another, the scopes of the methods, etc. You have options.

Is the variable an attribute of the object in which the methods exist? For example, in a Person object you might have something called a Height. Since that describes the person, it can be class-level:

public int Height { get; private set; }

public void OneMethod(int someValue)
{
    // more code
    Height = someValue;
    // more code
}

public void AnotherMethod()
{
    // more code
    someOtherObject.DoSomethingWithHeight(Height);
    // more code
}

If the value doesn't actually describe the object but is instead in some smaller scope, consider passing it to the method. Maybe something like:

public void SomeMethod()
{
    // more code
    var someValue = 123;
    // more code
    AHelperMethod(someValue);
    // more code
}

private void AHelperMethod(int aValue)
{
    // more code
    someOtherObject.DoSomethingWithValue(aValue);
    // more code
}

In the first case both methods were enacting some action on the object itself, using an attribute which describes that object. In the second case the second method is just something internal to the object and is only there to support the first method's higher-level action. It could just be inline code in the first method, but it's been extracted into its own method for reuse, readability, maintaining levels of abstraction, or any number of refactoring reasons.

Answer 3

The easiest way is to declare the SQLiteConnection object as a class level variable. Then your InsertField method would then use the class level variable like so:

SQLiteCommand command = new SQLiteCommand(connection);

Answer 4

Have the connection as a private instance field, it can then be used in all methods. Also remove static keywords. Static is rarely needed and is usually bad.

public class Database
{
    private string _datasource;
    private string _table;
    private string _field;
    private string _values;
    private SQLiteConnection _connection;

    public void ConnecttoDB(string datasource)
    {
        _datasource = datasource;
        _connection = new SQLiteConnection();
        _connection.ConnectionString = "Data Source: " + _datasource;
        _connection.Open();
    }
    public void InsertField(string table, string field, string values)
    {
        SQLiteCommand command = new SQLiteCommand(_connection);

        _table = table;
        _field = field;
        _values = values;
        String.Format("INSERT INTO {0} {1} VALUES{2}", _table, _field, _values);
    }
}

Answer 5

Either you keep it global or pass it as an argument to InsertFields. In the latter case You must create it somewhere and then pass to Your method.