Skip to content

0023: SQLite connection layer

10/12/2010

Hello!

Lately I created new helper class named OLGAtherer.Utilities.Helpers.DbBuilder. It is a simple construct that will be used to create most up-to-date database filled with different kind of data. For now, of course, there will be only books entries and collection data, but I hope that in the future I will be able to add new tables as the OLGAtherer will grow up.

What is new in the DbBuilder, that I don’t use connectionless layer to contact with database. As you probably know, in the OLGAtherer I use OLGAtherer.Model.Repositories.LiteDataContext class that inherits from DbLinq.Data.Linq.DataContext. This class is responsible for all operations on the tables (e.g. Books or CollectionData) and works in the same way as System.Data.Linq.DataContext from the framework’s Linq package. Generally it allows us not to worry about sql commands, connections, etc. and this simple construction:

virtual public List<T> GetAll()
        {
            using (LiteDataContext context = new LiteDataContext(new SQLiteConnection(this.connectionString)))
            {
                return context.GetTable<T>().ToList();
            }
        }

gives us whole table contain. It’s a simple and fast way of coding programs that use databases. Of course this method isn’t flawless – for example, what to do if database has millions of records? We don’t want all of them in the memory… OK, but I didn’t mean to talk about connectionless layer 🙂 I believe that I have done it in one of the previous posts. In short – I haven’t had almost any problems with that (except DbMetal, but I don’t use this now, instead I write whole code by myself).

OK, let’s go to the connection layer. To connect with database (of course I’m talking about SQLite) we need System.Data.SQLite package. It contains complete set of classes that we can use to work with SQLite in the same manner as standard libraries of .NET framework allow us to work with, for example, Microsoft SQL Server. Typical, widely known classes are: SQLiteConnection, SQLiteCommand, SQLiteParameter or SQLiteDataAdapter. As we want to work with the connection layer, we will mostly use first three of above-mentioned classes. At the moment we don’t use SQLiteTransaction for some reasons (I think that this isn’t necessary in so simple class that only creates and feeds small database).

So, we have necessary assembly, let’s write the skeleton of DbBuilder. For the sake of convenience, this class will implement IDisposable interface. In the constructor it creates new SQLiteConnection using provided path to the DB (not existing, but it’s all right, because instantiated SQLiteConnection creates DB if it doesn’t exist). Dispose method closes connection with DB and changes reference of the data member this.connection to the null.

So, we have class with private connection. We could try to make class as generic as possible to further use with databases other than SQLite, but isn’t necessary in this case. So all we need to show to the world is public method called simply CreateCompleteTestDb() 🙂

public void CreateCompleteTestDb()
        {
            CreateDbSkeleton();
            AddCollectionDataTable();
            AddBookTable();
            ShortlyFillBookTable(1);            
            UpdateCollectionDataTable(CollectionType.Books, "MyBooks");  
        }

As we can see, it creates db, adds book table, fills it with simple data (for now), updates CollectionData table and creates and fills Revision table. The latter table contains only one field – RevNo and will be probably used to synchronize version of OLGAtherer with DB version (I think that this is good idea, but I’m not sure yet – I’m still a newbie in the databases world).

Let’s take a look at first of the mentioned methods:

private void CreateDbSkeleton()
        {
            //Create revision table.
            SQLiteCommand command = new SQLiteCommand(CREATE_TABLE_REVISION, this.connection);
            command.ExecuteNonQuery();

            //Fill revision table.
            command.CommandText = INSERT_INTO_REVISION;
            command.Parameters.Add(new SQLiteParameter("RevNo", "1.0.0.0"));
            command.ExecuteNonQuery();            
        }

where

private const string CREATE_TABLE_REVISION = @"CREATE TABLE Revision (RevNo TEXT)";

As you can see, this method’s name isn’t exact. The database is created at the moment when connection is instantiated (as I wrote before). To execute any command we need to instantiate SQLiteConnection class, typically with two parameter – command text which is SQL command and opened connection. If the command isn’t use to read from database, to execute it we have to call method ExecuteNonQuery(). If it’s SELECT command, we need to call ExecuteReader() or ExecuteScalar(), it depends whether we need to iterate through many rows or maybe we need only one field. In the Revision table there is only one field, so to read it we need to do something like this:

SQLiteCommand command = new SQLiteCommand("SELECT * FROM Revision", this.connection);
string revision = command.ExecuteScalar().ToString();

OK, basically that’s all. BUT here we are something that took me quite a long time to investigate. Look at the above method and the way we fill Revision table. We change command’s CommandText to the

private const string INSERT_INTO_REVISION = @"INSERT INTO Revision (RevNo) VALUES (?)";

Typically I would write another version of this command:

@"INSERT INTO Revision (RevNo) VALUES (@RevNo)";

And I did this. But when I tried to invoke ExecuteNonQuery, there was an exception thrown. Strange, and I don’t know why was that. What is more strange here, that when I debugged the application and the exception was thrown, I needed to place instruction pointer to the line where parameter is added, parameter was added again and next ExecutedNonQuery() didn’t throw. It’s magic for me, so if you know why is that, please let me know. In the other INSERT commands I can write values named like this: “@” + name, but not here…
OK, that was my first obstacle. Second one was when program went to the ShortlyFillBookTable. Firstly there was another method named just FillBookTable(). It’s body looks like this:

private void FillBookTable(string friendlyName)
        {
            SQLiteCommand command = new SQLiteCommand(INSERT_INTO_BOOKS, this.connection);
            command.Parameters.Add(new SQLiteParameter("@IdNo", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@CollectionIdNo", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Author", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Binding", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Comments", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Condition", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@CopyrightYear", SqlDbType.Int));                                                           
            command.Parameters.Add(new SQLiteParameter("@Edition", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Editor", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@FrontCover", SqlDbType.Image));                                                           
            command.Parameters.Add(new SQLiteParameter("@Genre", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Gift", SqlDbType.Int));                                                           
            command.Parameters.Add(new SQLiteParameter("@Isbn", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Keywords", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Language", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Lcnn", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@LendedOrBorrowed", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@Pages", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@PublicationYear", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@Publisher", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@PurchasePrice", SqlDbType.Real));
            command.Parameters.Add(new SQLiteParameter("@PurchaseTime", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@Rating", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@Read", SqlDbType.Int));                                    
            command.Parameters.Add(new SQLiteParameter("@Series", SqlDbType.Text));                                                           
            command.Parameters.Add(new SQLiteParameter("@SeriesNumber", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Signed", SqlDbType.Int));
            command.Parameters.Add(new SQLiteParameter("@Subtitle", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Title", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Translator", SqlDbType.Text));
            command.Parameters.Add(new SQLiteParameter("@Volume", SqlDbType.Int));

            foreach (var book in GetRandomBookData().Rows)
            {
                //get values from rows
                command.ExecuteNonQuery();
            }
        }

And here exception was thrown too 🙂 So I decided to write temporary method that fills Book table with records containing only Author, Title and CollectionIdNo:

private void ShortlyFillBookTable(int collectionId)
        {
            string INSERT_INTO_BOOKS_SHORT = @"INSERT INTO Books (CollectionIdNo, Author, Title) VALUES (@CollectionIdNo, @Author, @Title)";            
            
            SQLiteCommand command = new SQLiteCommand(INSERT_INTO_BOOKS_SHORT, this.connection);
            //command.Parameters.Add(new SQLiteParameter("@IdNo"));
            command.Parameters.Add(new SQLiteParameter("CollectionIdNo", collectionId));
            command.Parameters.Add(new SQLiteParameter("Author", string.Empty));           
            command.Parameters.Add(new SQLiteParameter("Title", string.Empty));

            int allAffected = 0;

            foreach (SimpleBook book in GetSimpleBookData())
            {
                command.Parameters[0].Value = collectionId;
                command.Parameters[1].Value = book.Author;
                command.Parameters[2].Value = book.Title;                
                
                allAffected += command.ExecuteNonQuery();
            }
        }

This method has been executed without problems. Where is a difference? Look closer at the SQLiteParameter constructor. In the first method we use this signature:

public SQLiteParameter(string parameterName, DbType dbType);

In the second method:

public SQLiteParameter(string parameterName, object value);

Yep. That caused troubles. The more I work with SQLite libraries for C# the more errors or other traps I see, but thankfully, until now I always could find solution. Maybe after this post someone who experienced problems described above will save some time that I unfortunately lost 🙂

In short – when you have problem with INSERT to the SQLite, try to change parameter name in the SQL command to the “?” or use SQLiteParameter constructor described above. If you will have any other similar problems, don’t hesitate to write to me – maybe I will be able to help.

Best regards,
Paweł

PS. I seriously think about adding Author table to the database…

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: