Skip to content

0030: One year with OLGAtherer

Hello again,

This time in year 2011. This means that I work on OLGAtherer for a year, even though I started work, AFAIR, in September 2010. Since then I made about 60 commits on SVN, wrote about 030 posts on this blog (this is exactly 030) and drunk numerous cups of coffee.
It’s not bad score, because at start I didn’t want to write this on my own. This supposed to be training problem for RadosΕ‚aw. I have my own work, family, etc. But there was one thing that forced me to create and develop OLGAtherer – this was the first part of the name – OLGA. As you may know, it’s my daughter’s name and I must be stupid senthimental πŸ™‚
Uuuuh… stop. This post goes in the wrong direction. I wanted to state what has been done and what has to be done. First accomplished – let’s go further without unnecessary interludes.

What has to be done? Almost everything. Since now there is quite well implemented mechanism of interaction with database, test GUI that looks like nothing good, and a lot of unattached buttons πŸ™‚ My plan is to attach them one by one and periodically release new version of the application… blah, blah, blah

Oh crap, I have no idea what and how to write this post. I haven’t had interesting problems to overcome since last post and don’t know what to write instead of technical stuff. So maybe I will shut up. Gently.


PS. I can’t look at this thing above. You too, probably. So mercifully I will be quiet until I will come across something really interesting or there will be something really worth to write about (maybe new release of the OLGAtherer – new features will be available in not so distant future).

PS2. Happy new year.

0027: Design patterns in OLGAtherer – brief

Hello there!

I’ve changed OLGAtherer structutre recently. To be specific, I’ve introduced Command pattern and underscored (played up? – I don’t know correct word) MVC pattern that had been already implemented. First, I’d like to start with MVC. It isn’t design pattern, but rather architectural pattern, due to it’s purpose – it describes structure of the whole program, not only one piece of it. There are many kinds of MVC, many different implementations and “subdesigns”. I chose one that is depicted below:

I can only imagine how many mistakes I’ve made while drawing this, but I hope that you can see what I wanted to show. There are three parts that application has been splitted to.

Controller is the main (“core”) part of it and it is responsible for any action that is taken (for now it’s OLGAtherer.Controller executable). When program starts it instantiates windows, displays them, hides when necessary, and, what is most important, listens for messages from them (events). I think that is good, because when I will try to design new user interface (using WPF), I will need just to change View library, and rest of the application will be intact (I hope). For now Controller isn’t necessary, I think – now structure of the OLGAtherer would be described as MV only. It’s separated only for the future use. Controller would be good if application have a state (like drawing programs for example). I don’t plan that feature in OLGAtherer, but who knows? For sure Controller isn’t wrong here and I think that it will stay.

View is the part of the program that is contained in OLGAtherer.GUI.Forms. It is a set of the windows to be displayed to the user. Main goal that I aim to is that windows should contain no logic code. I mean – when user clicks a button, there is only event rising and rest of the action is executed elsewhere (controller or model) – Command pattern to the rescue πŸ™‚ (will be described later). View also listens from the Model. When model is changed, it rises OnChanged event and View takes an action as a reply (not implemented yet, but it probably will notice user that data model has been changed – somehow, I don’t know the way yet).

Model is contained in the OLGAtherer.Model, and has been described many times since now. Shortly, it is responsible for exchange data with database.

This short description above shows that separation of the application allows us to treat all three parts independently and changes in one of the part don’t affect rest of the parts. This is great idea and I very glad that I learned about it, implemented it and, hopefully, will take benefit from it.

In this post I’d like also to describe Command pattern. Here ( is a nice UML diagram of the pattern and brief description of it. I think that it’s pointless to repeat it. Instead, I will show code that I put into OLGAtherer to implement this pattern. Let’s start from the ICommand interface:

public interface ICommand
        void Execute();

Simple, isn’t it? πŸ™‚ This interface will be implemented by any command that user can choose. For example:

class CommandAddNewItem : ICommand 
	#region Initialization of the command
        private Book book;
        private BookCollection bookCollection;

        public CommandAddNewItem(BookCollection books, Book book)
            this.bookCollection = books;
   = book;

        public void Execute()
            this.bookCollection.Add(new Book[] { book });

Take a look at the command above. It will be used when user click Add New Item button on the main window. Command will be instantiated then with the current book collection and book item to be add. Execute method only adds new book to the collection. At the first glance it looks like more complicated way to do something. Advantages of this approach will be shown later.

In the main window’s code I added new lines:

#region Command pattern

        public delegate void CommandSelectedHook(ICommand command);
        public event CommandSelectedHook CommandSelected;

        private void tsbNewItem_Click(object sender, EventArgs e)
            RaiseCommandSelected(new CommandAddNewItem(this.collection, new Book()));

        private void RaiseCommandSelected(ICommand command)
            if (this.CommandSelected != null)

As you can see there is a delegate and event that are responsible for notifying clients that new command has been picked by user. Method called tsbNewItem_Click() invokes common method that raises the event. For now there is new Book() as a parameter, but of course there will be a book selected at the moment. This CommandSelected event is subscribed by a controller right after MainWindow is instantiated. This code do this:

form.CommandSelected += new MainWindow.CommandSelectedHook(form_CommandSelected);

void form_CommandSelected(ICommand command)

Hah – that’s the clue! Sending parameter isn’t of the class CommandAddNewItem, but only must implement ICommand interface. Handler in the Controller invokes Execute() method of any command has been sent. In the “standard” approach we could do here switch like this:

if(command is CommandAddNewItem)
	then ...
else if(command is CommandDeleteNewItem)
	then ...
else if...

For every of the circa 50 commands we would have to add new “else if” – horror. Command pattern makes the code nice and clean. But we can go further. Let’s add in the Model new interface:

   public interface ITransaction
        void Execute();
        void Undo();

πŸ™‚ I know that you know what is going on πŸ™‚ We try to add Undo/Redo πŸ™‚ But wait a moment and you will see how simple it is with the design patterns πŸ™‚

Next, we have to add new class:

public abstract class Transaction : ITransaction
        protected BookCollection collection;

        public Transaction(BookCollection collection)
            this.collection = collection;

        public abstract void Execute();

        public abstract void Undo();        

For now, it’s valid for BookCollection only, but it should be easy to make this code generic (this is marked as TODO). This abstract class adds only constructor and protected field, that will be used by the descendants:

   public class TransactionAddEntity : Transaction
        private Book book;
        public TransactionAddEntity(BookCollection collection, Book book) : base(collection)
   = book;            

        public override void Execute()
            this.collection.Add(new Book[]{});

        public override void Undo()
            this.collection.Delete(new Book[] { });

It’s a transaction to AddEntity (book). There are other – TransactionDeleteEntity and TransactionUpdateEntity, but their construction is similar, so I explain only this one. So we have new transaction class that contains method responsible for action Execute and another one – oppositive to it – Undo, responsible for withdrawing the Execute action.

Next step is to create quite meaningful class called DbUndoManager

public class DbUndoManager
        private Stack<ITransaction> history;
        private Stack<ITransaction> undoHistory;

        public DbUndoManager()
            history = new Stack<ITransaction>();
            undoHistory = new Stack<ITransaction>();

	public void Register(ITransaction transaction)

        public void Undo()
            if (history.Count > 0)
                ITransaction tr = history.Pop();

        public void Redo()
            if (undoHistory.Count > 0)
                ITransaction tr = undoHistory.Pop();

Whooa, what’s this πŸ™‚ Two stacks that record history of executed (or withdrawed) commands, two methods responsible for Undo/Redo features (it’s a simple stack handling) that’s it πŸ™‚ How to use it? In the EntityCollection class we add new region:

#region Commanding

        private DbUndoManager undoManager = new DbUndoManager();

        public DbUndoManager UndoManager
            get { return this.undoManager; }

        public void DoTransaction(ITransaction transaction)


And we call DoTransaction any time when we want to do something with this collection (database). That’s it. Let’s say we want to handle click on the button “Delete Item From The Collection”:

class CommandDeleteItem : ICommand 
        private Book book;
        private BookCollection bookCollection;

        public CommandDeleteItem(BookCollection books, Book book)
            this.bookCollection = books;
   = book;

        public void Execute()
            this.bookCollection.DoTransaction(new TransactionAddEntity(this.bookCollection,;

Nothing more to say about it. Of course, there will be some trouble with another commands, but this pattern is quite clear and easy once implemented and I think that small adjustments won’t take too much effort. In case of any questions, please write.

Best regards,

0026: Dynamic WebBrowser control

Hello again,

Recently, during develop process I came across strange “bug” in WebBrowser control. Please, take a look at the text that I post at

Good day

I have question about displaying html documents in a windows forms applications. App that I’m working on should display information from the database in the html format. I will try to describe actions that I have taken (and which failed):

1) I tried to load “virtual” html page that exists only in memory and dynamically change it’s parameters (webbMain is a WebBrowser control):

public static string CreateBookHtml()
            StringBuilder sb = new StringBuilder();


            sb.AppendLine(@"<title>Exemplary document</title>");


            return sb.ToString();

void LoadBrowser()
this.webbMain.DocumentText = CreateBookHtml();
HtmlDocument doc = this.webbMain.Document;

This failed, because doc.Body is null, and doc.getElementById(“paragraph”) returns null too. So I cannot change paragraph InnerText property. Furthermore, this.webbMain.DocumentText is “”…

2) I tried to create html file in specified folder, load it to the WebBrowser and then change its parameters. Html is the same as created by CreateBookHtml() method:

private void LoadBrowser()
            HtmlDocument doc = this.webbMain.Document;

This time this.webbMain.DocumentText contains Html data read from the file, but doc.Body returns null again, and I still cannot take element using getByElementId() method. Of course, when I have text, I would try regex to get specified fields, or maybe do other tricks to achieve a goal, but I wonder – is there simply way to mainipulate html? For me, ideal way would be to create HTML text in memory, load it into the WebBrowser control, and then dynamically change its parameters using IDs. Is it possible? Thanks for the answers in advance, best regards,


This is the answer that I received after few hours from Andrei Pana:

I’ve worked some time ago with the WebControl and like you wanted to load a html from memory but have the same problem, body being null. After some investigation, I noticed that the Navigate and NavigateToString methods work asynchronously, so it needs a little time for the control to load the document, the document is not available right after the call to Navigate. So i did something like (wbChat is the WebBrowser control):

wbChat.NavigateToString("<html><body><div>first line</div></body><html>");


where DoEvents() is implemeted as:

[SecurityPermissionAttribute(SecurityAction.Demand, Flags = SecurityPermissionFlag.UnmanagedCode)]
public void DoEvents()
    DispatcherFrame frame = new DispatcherFrame();
                new DispatcherOperationCallback(ExitFrame), frame);

and it worked for me, after the DoEvents call, I could obtain a non-null body:

mshtml.IHTMLDocument2 doc2 = (mshtml.IHTMLDocument2)wbChat.Document;

mshtml.HTMLDivElement div = (mshtml.HTMLDivElement)doc2.createElement("div");
div.innerHTML = "some text";
mshtml.HTMLBodyClass body = (mshtml.HTMLBodyClass)doc2.body;

if (body != null)

    body.scrollTop = body.scrollHeight;
    Console.WriteLine("body is still null");

I don’t know if this is the right way of doing this, but it fixed the problem for me, maybe it helps you too.

Later Edit:

public object ExitFrame(object f)
    ((DispatcherFrame)f).Continue = false;
    return null;

The DoEvents method is necessary on WPF. For System.Windows.Forms one can use Application.DoEvents().

I tried to implement this and it works! After short investigation, I posted comment back:

Hello! Thank you for the answer. I’d like to add something to it. First, there should be ExitFrame method body. It looks like: ExitFrame(object f){((DispatcherFrame)f).Continue = false;return null;}. Second, we have to add two assemblies to the solution: WindowsBase.dll and Microsoft.mshtml.dll. Third: I couldn’t find NavigateToString method in WebBrowser. Most important is, that your solution works (looks awful, but works :)), and I’m writing above only for other readers. Thank you very much!

And again received answer from Andrei:

I’ve forgot to add the ExitFrame method and to mention about the assemblies, I will edit the answer so that the code will be more visible. Regarding the NavigateToString – I was actually using the WPF version of the WebBrowser control which actually has this method (… ) though this is strange, because I suspect it is a wrapper around the native control, just like the WebBrowser from System.Windows.Forms. I’m not happy either with how this looks, but sometimes you just don’t have what to do…

As you can see, this is the next time that I’ve found the answer to my question on stackoverflow, so I definitly can recommend it: in case of any coding trouble you will find the answer there (with good probability). Best regards,


PS. I noticed that Application.DoEvents() works from time to time. Custom DoEvents proposed by Andrei works every time.

0025: First release unleashed :)

Good day,

I’d like to announce that very fist version of OLGAtherer release is available on the Sourceforge. To start the application you simply need to download the archive, extract it anywhere, and double-click OLGAtherer.Controller.exe. It will start with default database and configuration. I’m aware that it contains numerous bugs and isn’t functional even in 30%, but I think that in 2014 it would be nice to go back and look at the beginning of the app πŸ™‚ (that’s why I placed that version now).

Since now I’ve made circa 50 updates on the SVN, and I think that at least 60-70 will be needed since program reaches 90% of its assumed functionality. Long way ahead, but I will do it.

Best regards.

EDITED: I’ve just noticed that there have been 7 downloads. Unfrtunately, during preparation of the archive I forgot to include System.Data.SQLite.dll library into it. Now I fixed this mistake and archive on the sourceforge contains all necessary files and can be tested. Sorry for my mistake.

0024: Documentation – most important thing. Most boring thing. I hate it. Don’t you?


Today’s note will be quite short. During last meeting with Andrzej, he pointed out that I don’t have documentation for the OLGAtherer. Of course, I said – because it’s so boring!

Yep. It is. But true is, that it’s also probably most important thing in the project. Without it we don’t even know what we want to do. If we don’t know what to do, we can’t know how to do it. That makes program lousy. I can cheat myself that my program will be good anyway, but without simple document that states what do I want OLGAtherer to do I can forget about it. Forgerabotyt. (Donnie Brasco rulez :)).

I’ve checked semi-professional documentation file and here is the table of contents from it:

Table of Contents

      1.1 Background & Purpose
      1.2 Requirements
      2 DESIGN
      2.1 Use cases
      2.1.1 Create a new project
      2.1.2 Delete a project
      2.2 Functional Design
      2.2.1 Main window
      2.2.2 Project Creation Wizard
      2.2.3 Project replication
      2.2.4 Deleting projects
      3 TESTING
      3.1 Projects list
      3.2 Project creation and replication
      3.3 Project deletion
      4.1 Limitations
      4.2 Dependencies
      5 ISSUES

As you can imagine, I need to reflect at least second paragraph of the first chapter (Requirements) – I have to describe what I need OLGAtherer to do. Background isn’t vital, but would be nice, I think. Problem is that I have no willingness to write this whole thing, so probably I do only most important points.

OK, so one paragraph since now. Design is, I think, critical here. First paragraph contains use cases as a tables where user actions are described. I think that better would be here sequential diagram (something like in PLC programming – you know, I’m Industrial Process Engineer by profession :)), but anything will be OK – content is vital here, not form that it’s depicted.

Paragraph 2.2.1 contain windows design and describes purposes of all controls – it’s nice to have, too.

Chapter 3 describes tests that should be done. In my case I think that I won’t create it now, because all tests could be created using use cases provided in the chapter 2. Maybe I’m wrong now, but time will show – if so, I will just try to describe tests too.

Chapter 4 – restrictions, limitations and dependencies, chapter 5: issues – I don’t know nothing about it at the moment.
Appendix – Communication – my email address will do the trick πŸ™‚

That’s it. Why I wrote this post? Because probably in a few next days I will try to write skeleton of the documentation, and this may absorb me completely, so code and blog won’t be updated for a while. I know also that this is only my private diary (no one probably reads it), so I treat this blog like it, and note things to do. So don’t be surprise if you see posts like this πŸ™‚

Best regards, me.

0023: SQLite connection layer


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()
            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);

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


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

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,

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

0022: My Visual Studio add-ins: quick review


This time there is almost no gap between this post and last one πŸ˜‰ In fact, I’ve just finished writing last one and started this. I think that I will post them separated, but I want to exploit my willingness to write and as long as I want to post, I will do this πŸ™‚
“Today” I’d like to introduce two nice gadgets that help programmers to code much easier.

  • SlickEdit Gadgets for Microsoft Visual Studio
  • First of them, smaller one is little add-in that consists of only few options. It is a free stuff that you can download from this site. When you will check that address you will get all necessary information about this gadget, and here I only describe which function are interesting (in my opinion).

    On the screen below (No.1) you can see my Visual Studio 2008 with SlickEdit installed. Red rectangles indicate points of interest – features provided by this tool. Rectangle No.1 borders SlickEdit menu. As you can see it’s pretty simple and consists only of 4 features, one shortcut to configuration panel and about window. To be honest I use only SLOC Report here (depicted on the second screen). This feature counts lines of code, comment and whitespace in selected project. To do the count you only need to right-click on the desired project/file and pick the last option from the context menu (Count Lines of Code), as you can see on the screen No.2.

    Second option, Command Spy shows what keys are binded to the action that you do at the moment. For example, if I choose from the context menu option “Build Solution, Command Spy shows me something similar to the screen No.3:

    As you can see, Command Spy window is a simple table with four columns. What is interested here, that you can see that Build Solution action may be called by pressing Ctrl+Shift+B. This may be nice feature for the beginner, but after quite short time you remember shortcuts to the most frequent actions, so Command Spy is rather useless hereafter.

    Data Object Analyzer is rather useless for me in 99% of my worktime. It shows clipboard contain and do a simple analyze of it. Honestly, I have never used it.

    Last, but not least – File Explorer which is quite useful – it’s a replacement for internal Visual Studio file explorer used to, e.g. opening solution, project, file, etc. Since it has Windows Explorer style GUI it’s a lot better than simple OpenFileDialog built into VS.

    Features above are “Active”. It means that you have to open it in order to use. “Passive” features like:

    • Line ruler (screen No.1, rectangle No.3)
    • Indentation guide (screen No.1, rectangle No.4)
    • Editor Graphic (screen No.1, rectangle No.2)

    Are the best tools in this package. Especially EditorGraphic that allows programmer to place desired graphic on the code panel (I have Lion II battleship from the Navy Field game). Try this at work and enjoy colleagues’ gazes πŸ™‚

    To resume, SlickEdit is small, light, free and nice tool. Even if not all of its features are equally useful, it’s worth to get it.

  • CodeRush Xpress for C# Developers
  • This is huge one. I use only XPress version which is free, but even with this I can imagine how powerful full version is. I could describe SlickEdit gadgets feature by feature, but in this case it could take a week to do this. I don’t have a whole week to spend, so please watch these videos (especially this one) and install Coderush Xpress – after few weeks you won’t be able to work without it πŸ™‚
    I can only add that after installing this tool the best thing is to print this document:
    and keep it next to the monitor for the first few days/weeks.

    That’s all for now, best regards

    0021: OLGAtherer.Model structure explained

    Hi again,

    As usual I write after quite long pause. During this absence I made a really nice progress. Unfortunately, if I write a code or do something else that is connected with program, I have no time to describe it on this blog. So I can write code or write posts – not both at the same time. This is very unfortunate, because during writing OLGAtherer I learn so much, come across so many interesting obstacles that I would be very happy to have time to bring it to you. Now I have two (or maybe more, I think that I recall this later, during writing this post) interesting things and I think that I will split it into two (three, four?) separated posts, because they have rather different nature.

    First one is an architecture feature that we worked out with Andrzej. Second one is technical problem with SQLite connection layer (I’ve just recalled another two or three :)).

    So, shall we?

    I opened Visual Studio a second ago, and I try to figure out what to describe as first – there is so many changes…
    To date, I have written about 2721 lines of code (including braces) and 1312 lines of comments. When I wrote last post, code was much much shorter πŸ™‚ I have added some new projects, split old ones and now there are (alphabetically):

    • OLGAtherer.Controller: I think that application may have a structure similar to this described by MVC pattern. Controller runs whole application (this will be probably executable file) – shows forms, computes something, etc. Controller knows about model (old OLGAtherer.Core) and view (all forms), and manage them.
    • OLGAtherer.Core.Tests: same like always. It was created by me to test only OLGAtherer.Core feautres, but now I don’t use this. Maybe in future I will write pack of tests to check application, but now I don’t have time (and later I probably won’t do this due to lack of time). In short: this is rather useless right now.
    • OLGAtherer.GUI.Forms: this project contains View part of MVC. That includes any form that will be shown to the user. For now there are 5 forms, including MainWindow (main application window), StartWindow (shown short after start of application, user can pick collection here), AddCollection and AddUser forms (pretty self-explanatory) and SplashScreen (for now it’s just a form with jpeg background).
      In the future I will add OLGAtherer.GUI.Wpf project with WPF forms and user will be able to pick which GUI suits him best, but for now there is only WinForms set of windows, for test purposes mainly. I’m thinking about changing name of this project to OLGAtherer.View.Forms.
    • OLGAtherer.Model: previously OLGAtherer.Core – new name suits better to MVC pattern. Now any can see structure of the program at first glance. This project contains program engine actually – here are placed structures responsible for exchanging data with database.
    • OLGAtherer.Utilities: any tool that will be used in the other projects will be placed here. Now this project contains configuration utils, enumerators, logger, text utilities and my newest tool – DbBuilder class that creates complete SQLite database and provides it with data.

    Most of these projects are quite simple – View contains only forms and almost no program logic. Controller shows forms, listen events from them and does actions in response. However, I’m very proud of OLGAtherer.Model – this is project that I spent most of the time on, and I think that this is visible right now. I cannot recall how many times I changed whole structure of this project – probably 5 or 6 times at least, but now it’s so clear and nice that I think maybe 2 or 3 changes will be needed to mark it as completed (it’s a joke :)). In this post I’d like to describe structure of it.

    This project contains 1700 lines of code and about dozen of class.
    Main class is DBManager. It can create database and return instance of DB class.
    DB class has methods to read data from CollectionData table (table that contains information about all collections placed in database) and any other tables (Books, Stamps, etc.).
    Above methods return CollectionDataCollection and BookCollection classes that inherit from generic EntityCollection class. The EntityCollection contains four CRUD methods. It’s quite smart implemented – you have EntityCollection (Book or something else) that contains entities from the table, and you can do anything you want with it. Let’s say you want to add few books to BookCollection. You can call then Add method:

    public virtual void Add(IEnumerable<T> elems)

    This is beautiful πŸ™‚ The method simply adds books to the list of books in memory, adds books to database and raise event that informs listeners that collection has been changed. What is nice here that base EntityCollection contains repository and items members. Any new collection will inherit from this base class and in 99% no changes will be need. If there would be non-standard collection, it could just override one or more CRUD methods πŸ™‚ Simple, clean, nice – I just hope that there is no mistake in my thinking and this structure REALLY IS nice πŸ™‚ Anyway it works quite nice right now and this is most important.

    Database structure is rather simple too. There is one main table – CollectionData that consists of four columns:

    • IdNo that is autoincremented integer field
    • CollectionType – text field indicating type of the collection
    • FriendlyCollectionName – it’s unique field – two collections couldn’t have the same name
    • CreationDate – date that collection has been created

    For any new collection supported by OLGAtherer new table will be created. For example, Book collections entities will be placed in Books table. This one has about 20 columns and one of the column is CollectionIdNo. This field indicates collection that entity belongs to. For now, with my knowledge about databases, I think that this is rather good structure, if not, please tell me why.

    OK, that’s it for now. I have no idea what to add. I encourage to get source code from SourceForge. All you need to do this is SVN client and this link:
    svn co olgatherer

    Best regards,

    0020: There is nothing but the problems

    Hello all,

    Another not so short period without writing here. Not a surprise, huh? πŸ™‚ Instead I have done many other things, like investigation on architecture, code reorganization (another one), fixing code bugs (so I hah had to check the code too) and many other things. I hope that I will describe it today (I’m not so sure, because I’m very tired and I would love to get some rest ASAP). So, get to it.

    First of all I have changed code structure (again). I’ve got rid of interfaces (IRepository, IBookRepository, etc.). So BookRepository class had to be removed too. Instead of this I have done one generic class,

    Repository where T : Entity

    Entity is a base class that any class representing database entity will inherit from. It implements INotifyPropertyChanged interface and contains one field that represents IdNo column. As you can imagine, this column will contain unique number that will identify any entity.

    So, Repository contains for CRUD operations that have structure similar to this one:

    virtual public void Add(List<T> items)
     using (LiteDataContext context = new LiteDataContext(new SQLiteConnection(this.connectionString)))

    These methods are:

    virtual public void Add(List<T> items)
    virtual public void Delete(List<T> items)
    virtual public List<T> GetAll()
    virtual public List<T> GetByPredicate(Predicate<T> match)
    virtual public void Update(List<T> items)

    As you can see, methods are marked as virtual. It means that if I would like to create repository of very non-standard entities I would inherit from Repository and override desired methods. I think that this possibility will never occur, but it’s a nice feature that could help just in case.

    There is one interesting method – GetByPredicate. Thanks to it I could remove any entity-specific method that was used to list entities (eg. GetByAuthor). Now I can just pass custom predicate and desired collection will be returned. I think that this increases level of code genericness.

    That’s all about new class Repository. But after removing interfaces I had to remove partial classes DB.Books and DB.TableData. Instead of them I implemented new methods in DB class:

    public void Add<T>(List<T> items, string tableName) where T : Entity
    public void Delete<T>(List<T> items, string tableName) where T : Entity
    public List<T> GetAll<T>(string tableName) where T : Entity
    public List<T> GetByPredicate<T>(Predicate<T> match, string tableName) where T : Entity
    public void Update<T>(List<T> items, string tableName) where T : Entity
    public EntityCollection<T> GetEntityCollection<T>(string repoName) where T : Entity

    Methods above are generic ones. Last one returns new generic class – EntityCollection that represents whole table. It means that it contains list of entities and few information about the table (creationDate and tableName). Andrzej suggested it and I think that it’s nice feature that would be helpful in the future.
    Generally speaking this DB class is a part of the Bridge design pattern (not Facade that I stated previously).
    Notice that I remove problem with calling generic interfaces that I described month ago, but I remove it temporarily – it will show up level above, when I implement next layer (maybe user interface, maybe controller or something that I don’t know yet), but it’s nothing that troubles me right now. Besides Andrzej told me that he will discuss OLGAtherer architecture with me this Friday. We think that it may be smart to reconsider all considerations πŸ™‚ and plan whole architecture from the scratches. I believe that there won’t be many changes, because what’s right now seems to be quite reasonable. But we will see.

    OK, what’s next. Aaa, I remember. I tried to check the code yesterday. I had a problem with ResourceManager – I used wrong data to create it and this piece of code thrown an error. Nothing that I couldn’t override. What was much more strange, that this code doesn’t work for me:

    public partial class LiteDataContext : DataContext
      public LiteDataContext(IDbConnection connection)
               : base(connection)

    This thrown exception: Unable to load the `DbLinq.SqlServer’ DbLinq vendor within assembly ‘SqlServer.dll’. Parameter name: connectionString. Strange, isn’t it? It took me quite a long time to figure out that I have to invoke another base constructor :

    public LiteDataContext(IDbConnection connection)
                : base(connection, new DbLinq.Sqlite.SqliteVendor())

    Strange. But it’s getting stranger when I’m recalling that I had this problem before but I forgot it. Silly me.

    And last thing – I figured out that my concept of database structure was wrong. I assumed that any user would have got its own database and in this database there would be number of tables. For example, if user would like to create two repositories of books, because one physical repository is placed in the room, and second one is placed in the bedroom, there would be two Books table created with different names. This table structure would be reflected by Book class in OLGAtherer. THIS IS WRONG. There will be just one table of Books that will contain books from these two repositories, but there will be another table that will contain information of two separated physical repositories (libraries), and books entities from Book table will be linked with this table contains. I can’t describe it better now because we will discuss it with Andrzej this Friday (hopefully). I figured out that current architecture is wrong when I learned about TableAttribute. MSDN states that name of the table to be reflected must be known before code will be compiled. I planned to do otherwise. Of course I could “hack” it with reflection, or something else, but this would be stupid. There is something like “good practice” that I would like to learn about, and if I would do “hacks” like this program would work, but this isn’t my point – my program have to work well πŸ™‚ As you can see I’m not a good “architect”, but I try πŸ™‚ With help from good people I will overcome all the problems and learn a lot πŸ™‚

    Best regards

    0017: Bitter thoughts – do not read this.


    Today’s note will be very short. I still have no time to work on OLGAtherer. I’ve created UML class diagram, but today Andrzej told me that current architecture isn’t well enough, so I don’t place diagram here – it’s rather inadequate right now.
    I’m also working on UML use case diagram, but I have no experience with this, and I don’t know whether I would finish doing this in reasonable time.

    Whatever – I’m in bad mood today, so I don’t care about anything. I hope that maybe this weekend I will change OLGAtherer core and I will write something on Monday. But this, of course, may be only a wish. Either way, no one read this, so screw it.