What is the right pattern to close an OdbcConnection in an asp.net-mvc web page

Question

I have a website and I have a data access class that has an OdbcConnection. In the constructor I have this code:

    public MySybaseProvider()
    {
        _conn = GetConn();        
    }

    public OdbcConnection GetConn()
    {
        string connString = "DSN=SybaseIQ;Eng=SYSERVER;Links=tcpip(Host=" + _connectionInfo.Host + ";Port=" + _connectionInfo.Port + ");UID=" + _connectionInfo.User + ";PWD=" + _connectionInfo.Pwd + ";";

        return new OdbcConnection(connString);
    }

and through out the class i have the following code:

    private OdbcQuery GetQuery(string sql)
    {
        return new OdbcQuery(_conn, sql);
    }

I am trying to figure out the best way to make sure I close the connection properly. Should I implement IDisposable? should i open and close the connection on each query? Are there any other best practices here.

EDIT:

To clarify, my use case is that i am loading a single web page but it takes about 10 queries to get all of the data needed for the page.

Answer 1

Check if ODBC connection pooling is enabled, and create and open a connection object with the using statement for each of your accesses to the database:

using (var conn = new OdbcConnection(_connString)) {
  conn.Open();

  // do a database command ...
}

(Note that I changed the design to store your _connString in a field)

This is the most idiomatic way to do this, and you don't need to worry about creating your connection in the constructor, storing it in a field, or even implementing IDisposable.