Connecting to a Database

Question

This is a relatively simple question but I want to make sure I am doing this the right way.

What is the best practice for connecting to a database? This is how I am currently doing it and I want to make sure this is more or less following best practice.

private static SQLiteConnection conn;

    public static SQLiteConnection Conn
    {
        get 
        {
            try
            { 
                if (conn == null)
                    conn = new SQLiteConnection(fullName);

                if (conn.State != ConnectionState.Open)
                {
                    conn.ConnectionString = connectionString;
                    conn.Open();
                }
            }
            catch (Exception Excp)
            {
                DataErrorLogger.WriteError(Excp, "");
            }

            return conn;
        }

    }

When I actually use the connection I am doing this.

 using (SQLiteConnection conn = new SQLiteConnection(SQLiteConn.Conn))
        {}

Thanks!

Answer 1

Check out the SQLHelper class from Microsoft. It code you can use for best-practice database access: http://www.sharpdeveloper.net/source/SqlHelper-Source-Code-cs.html

It handles all the connections and has a number of methods to return different data objects. We've found it VERY useful.

Answer 2

You should re-throw your error. Otherwise you're going to get nullReference exceptions when you use Conn everywhere in your code. Otherwise looks ok to me.

Answer 3

SQLHelper can certainly give you some good ideas, but it is written specifically for MS Sql Server, not SqlLite which you appear to be using. Their are dozens of ways to do what you want, and I think it very much depends upon the situation.

1) Take into account multi-threading. If you aren't using it, then you can ignore this, but otherwise make certain that each thread is using its own connection. You might also want to make certain only one thread can call your SQLiteConnection at a time. Instead of using a class-level static conn, return a new conn each time.

2) Use connection pooling. This will help reduce the overhead of each call getting a new connection.

3) Consider caching information that does not change (or changed infrequently) but is used frequently.

4) Try catching some more specific exception types so that you can take corrective action immediately. Is the user/pass invalid? Is it a security exception? Do you not have enough resources?

5) You should rethrow your error, or throw a new one based on the error (innerexception) so that the calling function knows it failed.

6) Consider using some of the generic classes to help you write the above- it will make it easier to switch engines or reuse the code for an alternate engine (Db Classes or IDb interfaces)

7) You are using a singleton, generally I recommend people avoid them unless they have a good reason. I see a lot of misuse of them.

Hopefully this can move you onto the right path with what you want. If not, please come back and ask for clarification or further help/.

Answer 4

There are many ways to connect to databases in .NET. You've of course found one of them and it will work just fine.

There are three main schools of thought that I've seen 1st hand and really experienced regarding .NET data connections:

1. The first is a more "wizardy" approach to coding that involves adding a connection object to your form, and then using the table bindings and such to "wizard" your form into submission. This is generally efficient on the client machine since .NET pre-optimizes a lot of it if its done through its binding and wizard functions. However, depending on what you are doing it could introduce a bit more network traffic. Also, sometimes the wizards do not do exactly what you want, and relying on wizards can sometimes introduce hours of googling to figure out why the wizard isn't doing what you want it to.

2. The second (and my preferred way) is writing back-end code to pull down datasets and then display them in your software. This is I believe what your posted code is doing. I believe that it gives you the best flexibility with the data while still allowing you to do some binding and wizardy coding. This is usually less efficient on the client than option 1 since DataSets can be passed byVal on accident sometimes and cannot be quite as pre-optimized as the wizardy approach can. It generally means less network traffic though because your queries will be more subsinct and in theory should be called less often because you can call the data out, store it, and reuse it elsewhere. Also, there is something to be said about the speed at which you can code in this manner as well. I also find it easier if you don't know much about SQL string formatting and the like (which may be why we use this in the office for some of the other guys). Also, datasets require disposing so that they will be removed from memory.

3. The third method for data connection that I've seen was an entire class built around reading in datarows from a datareader object. Upon doing some research on datareaders vs dataConnections and datasets, I've discovered that the datareader approach can be up to 10 times faster than datarow connections and they use far less memory and resources. You can also choose to truncate your result set by just not reading in more rows if you've gotten enough data or found what you were after (which should be done with a "TOP" or "WHERE" clause anyway). However, the drawbacks are the following:

   • Harder to code since you will have arrays of datarows instead of neat datasets
   • Cannot use wizards for the most part (some still compatible with datarow arrays)
   • I think it would be safe to say most beginning coders would look at it and say "wtf happened here?"
   • More network traffic.
   • Sequential access only.

These are simply my observations. I would research any of the methods before choosing one of these. Also don't forget about things like cacheing to prevent round trips to the server as much as possible, but make sure not to cache anything that will change while the program is running (from an external source). Don't forget about multithreading and connection pools and the like. They are all very important features to keep in mind for any data access class.

Answer 5

1) use a good ORM. SQLite is very well supported by both NHibernate and EntityFramework (it had the first production non-microsoft entity framework implementation). There is also a project out there to create LinqToSql clones for lots of other dbs that supports SQLite.

2) If you dont want to use an ORM. Write yourself a good helper class. I'm personally partial to storing my sql in an embeded xml resource with a name and meta-data about its parameters and then having wrapper classes that make getting commands easy.

Note:

Sharing connections across threads is a bad idea. Connections are relatively cheap with sqlite and have some special rules as to how you can share them across threads.