Monday, December 7, 2015

Async Programming : Unit Testing Asynchronous Code

Unit testing is a cornerstone of modern development. The benefits of unit testing for a project are pretty well understood: Unit testing decreases the number of bugs, reduces time to market and discourages overly coupled design. Those are all nice benefits, but there are further advantages more directly relevant to developers. When I write unit tests, I can have much greater confidence in the code. It’s easier to add features or fix bugs in tested code, because the unit tests act as a safety net while the code is changing.
Writing unit tests for asynchronous code brings a few unique challenges. Furthermore, the current state of async support in unit test and mocking frameworks varies and is still evolving. This article will consider MSTest, NUnit and xUnit, but the general principles apply to any unit testing framework. Most of the examples in this article will use MSTest syntax, but I’ll point out any differences in behavior along the way. The code download contains examples for all three frameworks.
Before diving into the specifics, I’ll briefly review a conceptual model of how the async and await keywords work.

Async and Await in a Nutshell

The async keyword does two things: it enables the await keyword within that method, and it transforms the method into a state machine (similar to how the yield keyword transforms iterator blocks into state machines). Async methods should return Task or Task<T> when possible. It’s permissible for an async method to return void, but it’s not recommended because it’s very difficult to consume (or test) an async void method.
The task instance returned from an async method is managed by the state machine. The state machine will create the task instance to return, and will later complete that task.
An async method begins executing synchronously. It’s only when the async method reaches an await operator that the method may become asynchronous. The await operator takes a single argument, an “awaitable” such as a Task instance. First, the await operator will check the awaitable to see if it has already completed; if it has, the method continues (synchronously). If the awaitable isn’t yet complete, the await operator will “pause” the method and resume when the awaitable completes. The second thing the await operator does is retrieve any results from the awaitable, raising exceptions if the awaitable completed with an error.
The Task or Task<T> returned by the async method conceptually represents the execution of that method. The task will complete when the method completes. If the method returns a value, the task is completed with that value as its result. If the method throws an exception (and doesn’t catch it), then the task is completed with that exception.
There are two immediate lessons to draw from this brief overview. First, when testing the results of an asynchronous method, the important bit is the Task it returns. The async method uses its Task to report completion, results and exceptions. The second lesson is that the await operator has special behavior when its awaitable is already complete. I’ll discuss this later when considering asynchronous stubs.

The Incorrectly Passing Unit Test

In free-market economics, losses are just as important as profits; it’s the failures of companies that force them to produce what people will buy and encourage optimum resource allocation within the system as a whole. Similarly, the failures of unit tests are just as important as their successes. You must be sure the unit test will fail when it should, or its success won’t mean anything.
A unit test that’s supposed to fail will (incorrectly) succeed when it’s testing the wrong thing. This is why test-driven development (TDD) makes heavy use of the red/green/refactor loop: the “red” part of the loop ensures the unit test will fail when the code is incorrect. At first, testing code that you know to be wrong sounds ludicrous, but it’s actually quite important because you must be sure the tests will fail when they need to. The red part of the TDD loop is actually testing the tests.
With this in mind, consider the following asynchronous method to test:
public sealed class SystemUnderTest
{
  public static async Task SimpleAsync()
  {
    await Task.Delay(10);
  }
}
Newcomers to async unit testing will often make a test like this as a first attempt:
// Warning: bad code!
[TestMethod]
public void IncorrectlyPassingTest()
{
  SystemUnderTest.SimpleAsync();
}
Unfortunately, this unit test doesn’t actually test the asynchronous method correctly. If I modify the code under test to fail, the unit test will still pass:
public sealed class SystemUnderTest
{
  public static async Task SimpleAsync()
  {
    await Task.Delay(10);
    throw new Exception("Should fail.");
  }
}
This illustrates the first lesson from the async/await conceptual model: To test an asynchronous method’s behavior, you must observe the task it returns. The best way to do this is to await the task returned from the method under test. This example also illustrates the benefit of the red/green/refactor testing development cycle; you must ensure the tests will fail when the code under test fails.
Most modern unit test frameworks support Task-returning asynchronous unit tests. The IncorrectlyPassingTest method will cause compiler warning CS4014, which recommends using await to consume the task returned from SimpleAsync. When the unit test method is changed to await the task, the most natural approach is to change the test method to be an async Task method. This ensures the test method will (correctly) fail:
[TestMethod]
public async Task CorrectlyFailingTest()
{
  await SystemUnderTest.FailAsync();
}

Avoiding Async Void Unit Tests

Experienced users of async know to avoid async void. I described the problems with async void in my March 2013 article, “Best Practices in Asynchronous Programming” . Async void unit test methods don’t provide an easy way for their unit test framework to retrieve the results of the test. In spite of this difficulty, some unit test frameworks do support async void unit tests by providing their own SynchronizationContext in which its unit tests are executed.
Providing a SynchronizationContext is somewhat controversial, because it does change the environment in which the tests run. In particular, when an async method awaits a task, by default it will resume that async method on the current SynchronizationContext. So the presence or absence of a SynchronizationContext will indirectly change the behavior of the system under test. If you’re curious about the details of SynchronizationContext, see my MSDN Magazine article on the subject at.
MSTest doesn’t provide a SynchronizationContext. In fact, when MSBuild is discovering tests in a project that uses async void unit tests, it will detect this and issue warning UTA007, notifying the user that the unit test method should return Task instead of void. MSBuild won’t run async void unit tests.
NUnit does support async void unit tests, as of version 2.6.2. The next major update of NUnit, version 2.9.6, supports async void unit tests, but the developers have already decided to remove support in version 2.9.7. NUnit provides a SynchronizationContext only for async void unit tests.
As of this writing, xUnit is planning to add support for async void unit tests with version 2.0.0. Unlike NUnit, xUnit provides a SynchronizationContext for all of its test methods, even synchronous ones. However, with MSTest not supporting async void unit tests, and with NUnit reversing its earlier decision and removing support, I wouldn’t be surprised if xUnit also chooses to drop async void unit test support before version 2 is released.
The bottom line is that async void unit tests are complicated for frameworks to support, require changes in the test execution environment, and bring no benefit over async Task unit tests. Moreover, support for async void unit tests varies across frameworks, and even framework versions. For these reasons, it’s best to avoid async void unit tests.

Async Task Unit Tests

Async unit tests that return Task have none of the problems of async unit tests that return void. Async unit tests that return Task enjoy wide support from almost all unit test frameworks. MSTest added support in Visual Studio 2012, NUnit in versions 2.6.2 and 2.9.6, and xUnit in version 1.9. So, as long as your unit testing framework is less than 3 years old, async task unit tests should just work.
Unfortunately, outdated unit test frameworks don’t understand async task unit tests. As of this writing, there’s one major platform that doesn’t support them: Xamarin. Xamarin uses a customized older version of NUnitLite, and it doesn’t currently support async task unit tests. I expect that support will be added in the near future. In the meantime, I use a workaround that’s inefficient but works: Execute the async test logic on a different thread pool thread, and then (synchronously) block the unit test method until the actual test completes. The workaround code uses GetAwaiter().GetResult() instead of Wait because Wait will wrap any exceptions inside an AggregateException:
[Test]
public void XamarinExampleTest()
{
  // This workaround is necessary on Xamarin,
  // which doesn't support async unit test methods.
  Task.Run(async () =>
  {
    // Actual test code here.
  }).GetAwaiter().GetResult();
}

Testing Exceptions

When testing, it’s natural to test the successful scenario; for example, a user can update his own profile. However, testing exceptions is also very important; for example, a user shouldn’t be able to update someone else’s profile. Exceptions are part of an API surface just as much as method parameters are. Therefore, it’s important to have unit tests for code when it’s expected to fail.
Originally, the ExpectedExceptionAttribute was placed on a unit test method to indicate that the unit test was expected to fail. However, there were a few problems with ExpectedException­Attribute. The first was that it could only expect a unit test to fail as a whole; there was no way to indicate that only a particular part of the test was expected to fail. This isn’t a problem with very simple tests, but can have misleading results when the tests grow longer. The second problem with ExpectedExceptionAttribute is that it’s limited to checking the type of the exception; there’s no way to check other attributes, such as error codes or messages.
For these reasons, in recent years there has been a shift toward using something more like Assert.ThrowsException, which takes the important part of the code as a delegate and returns the exception that was thrown. This solves the shortcomings of ExpectedExceptionAttribute. The desktop MSTest framework supports only ExpectedExceptionAttribute, while the newer MSTest framework used for Windows Store unit test projects supports only Assert.ThrowsException. xUnit supports only Assert.Throws, and NUnit supports both approaches. Figure 1 is an example of both kinds of tests, using MSTest syntax.
Figure 1 Testing Exceptions with Synchronous Test Methods
// Old style; only works on desktop.
[TestMethod]
[ExpectedException(typeof(Exception))]
public void ExampleExpectedExceptionTest()
{
  SystemUnderTest.Fail();
}
// New style; only works on Windows Store.
[TestMethod]
public void ExampleThrowsExceptionTest()
{
  var ex = Assert.ThrowsException<Exception>(() 
    => { SystemUnderTest.Fail(); });
}
But what about asynchronous code? Async task unit tests work perfectly well with ExpectedExceptionAttribute on both MSTest and NUnit (xUnit doesn’t support ExpectedExceptionAttribute at all). However, the support for an async-ready ThrowsException is less uniform. MSTest does support an async ThrowsException, but only for Windows Store unit test projects. xUnit has introduced an async ThrowsAsync in the prerelease builds of xUnit 2.0.0.
NUnit is more complex. As of this writing, NUnit supports asynchronous code in its verification methods such as Assert.Throws. However, in order to get this to work, NUnit provides a SynchronizationContext, which introduces the same problems as async void unit tests. Also, the syntax is currently brittle, as the example in Figure 2 shows. NUnit is already planning to drop support for async void unit tests, and I wouldn’t be surprised if this support is dropped at the same time. In summary: I recommend you do not use this approach.
Figure 2 Brittle NUnit Exception Testing
[Test]
public void FailureTest_AssertThrows()
{
  // This works, though it actually implements a nested loop,
  // synchronously blocking the Assert.Throws call until the asynchronous
  // FailAsync call completes.
  Assert.Throws<Exception>(async () => await SystemUnderTest.FailAsync());
}

// Does NOT pass.
[Test]
public void BadFailureTest_AssertThrows()
{
  Assert.Throws<Exception>(() => SystemUnderTest.FailAsync());
}
So, the current support for an async-ready ThrowsException/Throws isn’t great. In my own unit testing code, I use a type very similar to the AssertEx in Figure 3. This type is rather simplistic in that it just throws bare Exception objects instead of doing assertions, but this same code works in all major unit testing frameworks.
Figure 3 The AssertEx Class for Testing Exceptions Asynchronously
using System;
using System.Threading.Tasks;
public static class AssertEx
{
  public static async Task<TException> 
    ThrowsAsync<TException>(Func<Task> action,
    bool allowDerivedTypes = true) where TException : Exception
  {
    try
    {
      await action();
    }
    catch (Exception ex)
    {
      if (allowDerivedTypes && !(ex is TException))
        throw new Exception("Delegate threw exception of type " +
          ex.GetType().Name + ", but " + typeof(TException).Name +
          " or a derived type was expected.", ex);
      if (!allowDerivedTypes && ex.GetType() != typeof(TException))
        throw new Exception("Delegate threw exception of type " +
          ex.GetType().Name + ", but " + typeof(TException).Name +
          " was expected.", ex);
      return (TException)ex;
    }
    throw new Exception("Delegate did not throw expected exception " +
      typeof(TException).Name + ".");
  }
  public static Task<Exception> ThrowsAsync(Func<Task> action)
  {
    return ThrowsAsync<Exception>(action, true);
  }
}
This allows async task unit tests to use a more modern ThrowsAsync instead of the ExpectedExceptionAttribute, like this:
[TestMethod]
public async Task FailureTest_AssertEx()
{
  var ex = await AssertEx.ThrowsAsync(() 
    => SystemUnderTest.FailAsync());
}

Async Stubs and Mocks

In my opinion, only the simplest of code can be tested without some kind of stub, mock, fake or other such device. In this introductory article, I’ll just refer to all of these testing assistants as mocks. When using mocks, it’s helpful to program to interfaces rather than implementations. Asynchronous methods work perfectly well with interfaces; the code in Figure 4 shows how code can consume an interface with an asynchronous method.
Figure 4 Using an Asynchronous Method from an Interface
public interface IMyService
{
  Task<int> GetAsync();
}
public sealed class SystemUnderTest
{
  private readonly IMyService _service;
  public SystemUnderTest(IMyService service)
  {
    _service = service;
  }
  public async Task<int> RetrieveValueAsync()
  {
    return 42 + await _service.GetAsync();
  }
}
With this code, it’s easy enough to create a test implementation of the interface and pass it to the system under test. Figure 5 shows how to test the three major stub cases: asynchronous success, asynchronous failure and synchronous success. Asynchronous success and failure are the primary two scenarios for testing asynchronous code, but it’s also important to test the synchronous case. This is because the await operator behaves differently if its awaitable is already completed. The code in Figure 5 uses the Moq mocking framework to generate the stub implementations.
Figure 5 Stub Implementations for Asynchronous Code
[TestMethod]
public async Task RetrieveValue_SynchronousSuccess_Adds42()
{
  var service = new Mock<IMyService>();
  service.Setup(x => x.GetAsync()).Returns(() => Task.FromResult(5));
  // Or: service.Setup(x => x.GetAsync()).ReturnsAsync(5);
  var system = new SystemUnderTest(service.Object);
  var result = await system.RetrieveValueAsync();
  Assert.AreEqual(47, result);
}
[TestMethod]
public async Task RetrieveValue_AsynchronousSuccess_Adds42()
{
  var service = new Mock<IMyService>();
  service.Setup(x => x.GetAsync()).Returns(async () =>
  {
    await Task.Yield();
    return 5;
  });
  var system = new SystemUnderTest(service.Object);
  var result = await system.RetrieveValueAsync();
  Assert.AreEqual(47, result);
}
[TestMethod]
public async Task RetrieveValue_AsynchronousFailure_Throws()
{
  var service = new Mock<IMyService>();
  service.Setup(x => x.GetAsync()).Returns(async () =>
  {
    await Task.Yield();
    throw new Exception();
  });
  var system = new SystemUnderTest(service.Object);
  await AssertEx.ThrowsAsync(system.RetrieveValueAsync);
}
Speaking of mocking frameworks, there’s a bit of support they can give to asynchronous unit testing, as well. Consider for a moment what the default behavior of a method should be, if no behavior was specified. Some mocking frameworks (such as Microsoft Stubs) will default to throwing an exception; others (such as Moq) will return a default value. When an asynchronous method returns a Task<T>, a naïve default behavior would be to return default(Task<T>), in other words, a null task, which will cause a NullReferenceException.
This behavior is undesirable. A more reasonable default behavior for asynchronous methods would be to return Task.FromResult­(default(T))—that is, a task that’s completed with the default value of T. This enables the system under test to use the returned task. Moq implemented this style of default behavior for asynchronous methods in Moq version 4.2. To my knowledge, as of this writing, it’s the only mocking library that uses async-friendly defaults like that.

Wrapping Up

Async and await have been around since the introduction of Visual Studio 2012, long enough for some best practices to emerge. Unit test frameworks and helper components such as mocking libraries are converging toward consistent async-friendly support. Asynchronous unit testing today is already a reality, and it will get even better in the future. If you haven’t done so recently, now is a good time to update your unit test frameworks and mocking libraries to ensure you have the best async support.
Unit test frameworks are converging away from async void unit tests and toward async task unit tests. If you have any async void unit tests, I recommend you change them today to async task unit tests.
I expect over the next couple years you’ll see much better support for testing failure cases in async unit tests. Until your unit test framework has good support, I suggest you use the AssertEx type mentioned in this article, or something similar that’s more tailored to your particular unit test framework.
Proper asynchronous unit testing is an important part of the async story, and I’m excited to see these frameworks and libraries adopt async. One of my first lightning talks was about async unit testing a few years ago when async was still in community technology preview, and it’s so much easier to do these days!

By Unknown with No comments

Csharp Unit Test In Sharp Develop

Why do unit testing?

I can't say it better than Martin Aspeli does in [2] so I'll just quote him: "[Unit testing is] the only way of even remotely convincing your customers and friends your code doesn't completely suck."
Also unit testing is:
  • The only way of making sure ... you don't break things without realising it.
  • The only way of making sure ... you don't re-introduce bugs you thought you'd fixed.
  • Usually a way of saving time in the long run...

So what is unit testing?

Test driven development is about first writing tests. And then writing code. It is like when you start a course you get a pack of old exams and you only study stuff to pass the old exams. When you then have your real exam you should be able to pass it if you were able to pass the old exams.

Read More

  • Wikipedia on test driven development: [3]
  • Wikipedia on NUnit: [4]
  • Nice three rulse: [5]
  • The c2-wiki [6]

Example of Unit testing with NUnit in Sharp Develop

NUnit is an open source unit testing framework written in C#, download and install from[7] .
In this simple example we will implement rövarspråket ([8]) in a library. To get started we just:
  1. Start Sharp Develop (see Sharp Develop to find a download link).
  2. Create a new solution.
  3. Add a reference to nunit.framework that you should find in the GAC: http://www.pererikstrandberg.se/blog/dot-net-unit-test/gac-nunit-framework.png
  4. Right click the project and add a new item: and add a unit test: http://www.pererikstrandberg.se/blog/dot-net-unit-test/nunit-add-item.png
  5. This will create a blank test file with one blank test. To test the test change the comment into something like this: Assert.IsTrue(false, "This will always fail :)");
  6. You should now get a little window to the right with the unittest: http://www.pererikstrandberg.se/blog/dot-net-unit-test/nunit-test-view.png
  7. Running all tests should now give an error similar to this: This will always fail :), Expected: True, But was: False ... Also notice that there are red things to the right - read means bad.

Back to Rövarspråket

I make a skeleton rorovovaror.cs with an intentional missing f in the consonants.
using System;

namespace rorovovaror
{
    static class rovar
    {
        static string lower_consonants = "bcdghjklmnpqrstvwxz";
        static string upper_consonants = "BCDGHJKLMNPQRSTVWXZ";

        public static string enrov(string normal)
        {
            return "";
        }

        public static string derov(string rov)
        {
            return "";
        }
    }
}

I make a little list of known test cases and make test the conversion to and from rovar:
using System;
using NUnit.Framework;
using NUnit.Framework.SyntaxHelpers;

namespace rorovovaror
{
    [TestFixture]
    public class rorovovaror_test
    {  
        string[][] testPairs =  new string[][] {
            new string[] {"rov", "rorovov"},
            new string[] {"Test",      "TOTesostot"},
            new string[] {"IBM",       "IBOBMOM"},
            new string[] {"fooBAR",    "fofooBOBAROR"},
            new string[] {"XYZZYX",    "XOXYZOZZOZYXOX"},
            new string[] {"emacs",     "emomacocsos"},
            new string[] {"5",         "5"},
            };

        [Test]
        public void TestEnrov()
        {
            string i, o;
            for (int j = 0; j < testPairs.Length; j++)
            {
                i = testPairs[j][0];
                o = testPairs[j][1];
                Assert.IsTrue(rovar.enrov(i) == o, "{0} -> {1} == {2}?", i, o, rovar.enrov(i));
            }
        }
  
        [Test]
        public void TestDerov()
        {
            string i, o;
            for (int j = 0; j < testPairs.Length; j++)
            {
                i = testPairs[j][1];
                o = testPairs[j][0];
                Assert.IsTrue(rovar.derov(i) == o, "{0} -> {1} == {2}?", i, o, rovar.derov(i));
            }
        }
    }
}

Running the tests now till result in broken test (as expected) and something like this:
  • rov -> rorovov == ? Expected: True But was: False...
  • rorovov -> rov == ? Expected: True But was: False...

Implementing enrov and derov

I implement a pretty straight forward enrov and derov, I am sure there are more compact ways of doing it - but since this will later pass the tests I do not have to improve them :)
        public static string enrov(string normal)
        {
            System.Text.StringBuilder builder = new System.Text.StringBuilder();
            
            foreach(char c in normal)
                if (lower_consonants.Contains(c.ToString()))
                    builder.Append(c + "o" + c);
                else if (upper_consonants.Contains(c.ToString()))
                    builder.Append(c + "O" + c);
                else 
                    builder.Append(c);
                            
            return builder.ToString();
        }
        
        public static string derov(string rov)
        {
            foreach(char c in lower_consonants)
                rov = rov.Replace(c + "o" + c, c.ToString());

            foreach(char c in upper_consonants)
                rov = rov.Replace(c + "O" + c, c.ToString());

            return rov;
        }

Analyzing the test results

The Assert.IsTrue(bool b) method has a sibling: Assert.IsTrue(bool b, string s) and we used the latter (there is also a third one with other arguments). Since we used the message it will be a lot easier to find any problems in the tests.
Our error messages was:
  • fooBAR -> fofooBOBAROR == fooBOBAROR?, Expected: True, But was: False...
  • fofooBOBAROR -> fooBAR == fofooBAR?, Expected: True, But was: False...
  • kalasfint -> kokalolasosfofinontot == kokalolasosfinontot ?, Expected: True, But was: False...
By looking at this (even for a few minutes) you should easily notice the missing f in the consonants bug.
If we add the f and rerun the tests it seems to work (at least the tests did not break).

What if I find something the tests do not cover?

If you find something that the tests do not cover - what if we want to do enrov(null) or an empty string? Will the tests cover that?
Whatever you do - make sure you implement the tests first and then implement the functionality you want to test.

Download

Download this solution here: [9]

What about Visual Studio?

From the menu: select Test > New Test > Unit Test. The rest is more or less the same.
http://www.pererikstrandberg.se/blog/dot-net-unit-test/visual-studio-test-driven.png

By Unknown with No comments

Exporting Objects from the Visual Studio Debugger


I had been searching for a way to export objects from my debugging windows (ex. locals, autos, watch) to their C# object initialization code for creating unit tests. There have been a few cases where there are some complex objects that I load through a database which would make great unit tests. Unfortunately i was not able to find anything like that, so I decided to create it myself. Enter Object Exporter.


Object Exporter is accessed through the tools menu and is only visible when you are debugging.
Export Objects (Tool Menu)
Once selected a dialog is shown with settings for the Object Export.
Export Objects (Select Objects)
The Output Format details which format you want your exported objects to be in. Maximum Object Depth refers to the maximum depth the Serializer will generate text for, this is important as some objects can have an infinite depth. Once you select a debugging window from the drop down, it’s objects will be loaded onto the screen and you may check the objects you wish to export.
The objects which can be selected correspond to the objects that exist within the various windows:
Export Objects (Locals)
Once the objects are selected and the “Export” button is selected, another dialog with the generated texts appear:
Xml:
Export Objects (Xml Generated)
Json:
Export Objects (Json Generated)
CSharp:
Export Objects (CSharp Generated)
You can copy the text in the currently selected tab by selecting “Copy To Clipboard” or you can save all the objects to a folder by selecting the “Save All to Folder” button, a file per tab will be generated.
That’s pretty much it.
Enjoy

By Unknown with No comments

Getting started with SQLite in C#

I’ll assume that you’re already familiar with SQL and at least have some knowledge of how it works (for example: what to expect as a result from “select * from table1″ )
There will probably be two parts, with the first one discussing the basics needed to do pretty much anything, and in the second part I’ll discuss some miscellaneous subjects like how to parameterize your queries to make them much faster and safer.
Let’s get started.
Create a standard C# console project.
Since we’re working in C# we’ll be using the System.Data.SQLite library. This library is not a standard library (packaged with .NET for example) so we’ll need to download it. It is being developed by the people who’re also working on the (original) SQLite.
All you’ll need are two files, a .dll and a .xml file for some documentation. These files are available for download at the end of this article, you can also download these from their website, but you’ll also get some files that you don’t need.
Put these files in the folder of your project and add an assembly reference to the .dll. Just browse to System.Data.SQLite.dll and select it.
Now add using System.Data.SQLite; to the usings and you’re done. You’ve successfully added the SQLite library to you project!
Creating a database file:
You usually don’t need to create a new database file, you work with an existing one, but for those cases where you do need to create a brand new one, here’s the code:
1
SQLiteConnection.CreateFile("MyDatabase.sqlite");
Connecting to a database:
Before you can use the database, you’ll need to connect to it. This connection is stored inside a connection object. Every time you interact with the database, you’ll need to provide the connection object. Therefore, we’ll declare the connection object as a member variable.
1
SQLiteConnection m_dbConnection;
When creating a connection, we’ll need to provide a “connection string” this string can contain information about the… connection. Things like the filename of the database, the version, but can also contain things like a password, if it’s required.
You can find a few of these at: http://www.connectionstrings.com/sqlite
The first one is good enough to get our connection up and running, so we get:
1
2
3
m_dbConnection =
new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
After we create the connection object, we’ll have to open it. And with every Open() there comes a Close(), so don’t forget to call that after you’re done with your connection.
Creating a table:
Let’s write some SQL now. We’ll create a table with two columns, the first one contains names and the second one contains scores. See it as a high scores table.
1
string sql = "create table highscores (name varchar(20), score int)";
You could also spam caps if you like and get something like this:
1
string sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";
Now we’ll need to create an SQL command in order to execute it. Luckily, we’ve got the SQLiteCommand class for this. We create a command by entering the sql query along with the connection object.
1
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
Afterwards, we execute the command. But before we execute our command, i’d like to mention that not all commands are the same, some commands return results (like SELECT etc.) and others don’t (like the one we just wrote) That’s why there are two execute methods (actually, there are three) One returns the actual results (the rows of the table) the other returns an integer indicating the number of rows that have been modified. We’ll use the last one now.
1
command.ExecuteNonQuery();
At this time, we’re not interested in the number of rows that have been modified (it’s 0) But you could imagine that it might be interesting to know this information in UPDATE queries.
Filling our table:
Let’s fill our table with some values, so we can do some SELECT queries. Let’s create a new command. We’ll see later that this process can be made a bit easier and faster with command parameters.
1
string sql = "insert into highscores (name, score) values ('Me', 9001)";
We create and execute the command the same way as we created the table. I added two more rows (or records) to the table. Here’s the code:
1
2
3
4
5
6
7
8
9
string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
As you can see, this is three times pretty much the same piece code. But it works!
Getting the high scores out of our database:
Let’s query the database for the high scores sorted on score in descending order. Our SQL query becomes: “select * from highscores order by score desc”
We create a command in the regular fashion:
1
2
string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
However, we execute this command using a different method, we’ll use the ExecuteReader() method which returns an SQLiteDataReader object. We’ll use this object to read the results of the query.
1
SQLiteDataReader reader = command.ExecuteReader();
With this reader you can read the result row by row. Here’s some code that iterates trough all the rows and writes them to the console:
1
2
3
4
5
string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
       Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
The Read() method of the reader moves the reader to the next row. With the [] operators, you can read the value of a certain column. The value returned is of the type object. So you’ll usually need to cast it before you can use it. Fortunately, you usually know what this type is.
Well, that’s about it for this tutorial. You should now be able to do pretty much anything you want with your database.
The above link also contains the libraries. If you’d like to have only the library, click the link below: The version of the library provided here is the .NET 4.0 x86 version. This should work fine for anyone working in Visual Studio 2010. If it doesn’t work, download the correct version from their website (mentiond at the beginning of this article)

By Unknown with No comments