Close OleDbConnection if passed as parameter

Question

I am trying to parametrize SQL creation using OleDbCommand to avoid SQL injection. So, I want a common method that can do it and return an object that I can further use.

I want to use the code on page: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx

public void CreateMyOleDbCommand(OleDbConnection connection,
    string queryString, OleDbParameter[] parameters) 
{
    OleDbCommand command = new OleDbCommand(queryString, connection);
    command.CommandText = 
        "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";
    command.Parameters.Add(parameters);

    for (int j=0; j<parameters.Length; j++)
    {
        command.Parameters.Add(parameters[j]) ;
    }

    string message = "";
    for (int i = 0; i < command.Parameters.Count; i++) 
    {
        message += command.Parameters[i].ToString() + "\n";
    }
    Console.WriteLine(message);
}

Question 1. It passes parameter as value. So, do I have to call connection.Close at the end in this? connection.Close is not mentioned on the above link, so is it required? I do not want connections to my database left open during code execution.

Question 2. My original code was:

dbReader = new OleDbCommand("select * from Table1 where Table1.Company = '" + company + "'", dbConnection).ExecuteReader();
dbReader.Read();

if (dbReader.HasRows)
{
    //Do operations using dbReader["Company"]
}

And

new OleDbCommand("...insert sql query...", dbConnection).ExecuteNonQuery()

Should I return OleDbCommand command ? that I can execute as command.ExecuteReader() and command..ExecuteNonQuery()

Answer 1

The example you have showed is really bad. I think that Microsoft should be advised of this very bad code on their site.

I will try to make a better example and explain why

public OleDbCommand CreateMyOleDbCommand(OleDbConnection connection,
    string queryString, OleDbParameter[] parameters) 
{
    OleDbCommand command = new OleDbCommand(queryString, connection);
    command.Parameters.AddRange(parameters);
    return command;
}

First, the method is supposed to return an OleDbCommand with its parameter and its commandtext initialized correctly. So I have changed the return value of the method from void to OleDbCommand. In this way you could use the command in the calling code to execute your queries.

Second the OleDbCommand has a constructor that receives a command text and a connection. Building the OleDbCommand with this constructor will avoid to pass the command text afterward and also setting the connection to the parameter.

Third, to add a parameter array to the parameter collection of the OleDbCommand you need to use the AddRange method of the Parameters collection.

Now in your calling code, where you have defined the OleDbConnection you could call this method, open the connection and execute the command (ExecuteReader, ExecuteNonQuery or ExecuteScalar depending on the command text). Of course, where you open the connection you also close it