Skip to content

0007: Cycle summary

25/08/2010

Hi again,

I’d like to do a little review of works that I’ve already done. As I determined on the beginning, I’d like to use SQLite database that will be connected with OLGAtherer via Linq2SQLite provided by DbLinq team. Instead of excessive use of concrete classes, I’d like to use as many good, proved patterns as possible. First step is Repository Pattern that I described last week. Nuff about my plans. What has been done already? (“Done” means “planned and checked” in this cause).

I can proudly say that everything :). Of course there will be many, many problems during future works, but foundations have been established and I can go further. In this post I’d like to show code scema that I think will be used in concrete repository classes (e.g. BookRepository). As I’ve described earlier, these classes implements basic and extended CRUD operations. I’d like to show and explain 4 basic CRUD methods that will be implemented by Radosław or me. First I’d like you to look at these four methods and then I will explain them.

First, notice that all of below methods have the same structure – connection string followed by connection instance. These two lines won’t stay in the methods of course. They will be relocated – connection string to resources and connection to the class private fields (I think so now, this may change of course).

1) Create
To create items in the Books table we will use Add method. Let’s look at it:

public bool Add(Books item)
        {
            string connString = @"Data Source=c:\test.lite;Version=3;";
            IDbConnection connection = new System.Data.SQLite.SQLiteConnection(connString);

            using (Main mainDataContext = new Main(connection))
            {
                mainDataContext.GetTable<Books>().InsertOnSubmit(item);
                mainDataContext.SubmitChanges();
                return true;
            }
        }

As you certainly noticed, everything that could be interesting is contained inside using block. Using statement is great framework feature that “defines a scope outside of which an object or objects will be disposed”. Of course only objects that implement IDisposable could be used this way.
OK, let’s go inside using block. Here we can see how nice and simple in use Linq is. First line allows us to insert new item to the table, second submits changes and third returns true (of course try/catch statement will be required here). In fact, two lines make the deal – great for me :).

2) Read

public List<Books> GetAll()
        {
            string connString = @"Data Source=c:\test.lite;Version=3;";
            IDbConnection connection = new System.Data.SQLite.SQLiteConnection(connString);

            using (Main mainDataContext = new Main(connection))
            {
                Table<Books> books1 = mainDataContext.GetTable<Books>();
                return books1.ToList<Books>();
            }
        }

The same situation like above. One line to get all entities from the Books table, and one to change collection to the List of Books and return it. We can make it even shorter, and compress these two lines like this:

return mainDataContext.GetTable<Books>().ToList<Books>();

3) Update

public bool Update(Books item)
        {
            string connString = @"Data Source=c:\test.lite;Version=3;";
            IDbConnection connection = new System.Data.SQLite.SQLiteConnection(connString);

            using (Main mainDataContext = new Main(connection))
            {
                Books bookToUpdate = mainDataContext.GetTable<Books>().First(x => x.BookID == item.BookID);
                MethodThatCopiesProperties(bookToUpdate, item);
                mainDataContext.SubmitChanges();
                return true;
            }
        }

This method is slightly more complicated. We pass book entity with new, updated parameters, and we want book entity inside Books table to be changed accordingly. First we get bookToUpdate using First method that takes lambda expression comparing book IDs. Notice that if no book will be found, method will probably throw exception – in the future we will have to remember this fact and implement suitable try/catch blocks.

Next line invokes method that changes parameters in previously taken entity, and next one submits changes. True is returned only for compilation purposes – we need to figure out how to check whether entity is or isn’t updated and return proper output (probably we could do it catching some sort of exception).

4) Delete

public bool Delete(Books item)
        {
            string connString = @"Data Source=c:\test.lite;Version=3;";
            IDbConnection connection = new System.Data.SQLite.SQLiteConnection(connString);

            using (Main mainDataContext = new Main(connection))
            {
                Books b = mainDataContext.GetTable<Books>().First(x => x.BookID == item.BookID);
                mainDataContext.GetTable<Books>().DeleteOnSubmit(b);
                mainDataContext.SubmitChanges();
                return true;
            }
        }

Delete method works like Update. Finds proper book entity end then deletes it. Notice that Update/Delete commands must be confirmed using SubmitChanges() method. Without submitting, changes won’t be applied.

That’s it – just like I said – Linq makes whole thing very easy and straightforward – get collection, make changes, submit them and it’s done. I hope that this won’t make problems in the future, because for today I think this is very nice technology and I don’t want to change my opinion 🙂

At the end please click this link. (). I know that this isn’t about DbLinq, but regular .Net Linq, still you may find here MANY interesting facts about how all this works and how to use it properly.

OK, what now? We have DB communication foundations, now I have to figure out what features should OLGAtherer have. This and some other things in my life may delay publishing of the next post to the next week (maybe). However I can see in statistics that nobody read it anyway, so this shouldn’t be a big problem 🙂

Best regards, Paweł

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: