Day 8.2 – Dot NET On Linux – Connecting & Consuming SQLite with ADO.NET


SQLite is as light weight and simple yet remarkably powerful relational database. It requires no installation other than the existence of the executable and requires no running services SQLite does not support concurrent databases writes and in fact locks the whole database from other write transactions while writing. It also does not support network access;  it requires the executable, database file and implementing software to exist on the same machine.

SQLite has a good website for appropriate use here. Though this might not be suitable for a live website requiring concurrent usage across a network it still has several usages including strong integration within the Java based Android framework and is strongly promoted for storing application and user settings.

Mono has a SQLite ADO.NET data provider installed as default.

This post will run through installing, database and scheme creation and then connection and consumption through code.

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here


We are going to install sqllite3, the documentation and sqlitebrowserl  a simple yet convenient tool for managing and using SQLite databases. Version 3 is in the LMDE repositories along with sqlitebrowser and can be installed with the following command.

sudo apt-get install sqlite3 sqlite3-doc sqlitebrowser

Create a new Database

Open up sqllitebrowser via the launch icon which should be sitting inside the Programming menu. From the File menu select File –> New Database. You will be provided with a file dialog box. Find a suitable location to create the database file and select OK.

Create our Sample Table

To create a sample table go to the Edit menu and select; Edit File –> Create Table

Provide a sample name and then hit Add. To add a new field provide a name and a type from one of; TEXT, NUMERIC, BLOB and INTEGER PRIMARY KEY.

The screen shots below should be enough information to create the same SampleTable which mimics the same table we created in MySQL previously.

We could go into MySQL and generate a SQL script to create our SampleTable but this won’t actually work. I talk about this more in the next blog when I show how we can write code to interface with both MySQL and Sqlite with virtually no differences other than the connection string and a few tweaks.

SQLite DataTypes

You can read about the SQLite data types  here to help understand why they are very limited. In short the data type matches what you store and not what you define. This caused a few problems for me as you can imagine when I initially swapped out MySQL for SQLite in the previous code we wrote. This also explains why I have picked text to store a DateTime type.Connection.

The SQLite data provider comes installed and registered with mono. We don’t need to touch the machine.config to register the data provider like we did with MySQL. We still want to register the connection string in the machine.config. Search for and add in the following section:

    <add name="DotNetOnLinuxSQLLite" connectionString="Data Source=/PersonalData/DevProjects/SandBox/Git/DOT-NET-on-Linux/Day8ADOExample/ADOExample/SqliteDB/SampleSqlite.db;Version=3;" />

As you can see the connection string is pretty simple requiring just the location of the file and the version of SQLite.

The connection string can be easily accessed in code by:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinuxSQLLite"].ConnectionString;

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows the code behave agnostic to the database; see my next post where I talk about how to implement this.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download ( see at the top) where I have tried to implement a simple DAL layer with basic ORM capability.

Day 8.1 – Dot NET On Linux – Connecting & Consuming MySQL with ADO.NET


The ADO.NET framework provides a consistent way to connect and consume databases through the .NET languages. Microsoft provide ADO.NET data providers for a number of databases however support for MySQL come from Oracle.

The Source Code

This post is part of my Dot Net On Linux posts. All the source the source code is available on GitHub here


The Mono provider for MySQL can be used in both mono and .NET and can be downloaded here.

Download and extract the file taking note of the version you have downloaded.

The dll which was shipped as part of the downloaded was configured incorrectly. It has been named all in lower case and needs to be named in upper camel case just like its namespace; rename the dll to MySql.Data.dll which you can do with the following command.

cd path_to_your
cp MySql.Data.dll

GAC Registration

The MySQL ADO.Net data provider is required to be installed in the GAC. Mono provides a gacutil just like .Net on windows and it follows the same function arguments. You can register the dll with the following command;

sudo gacutil -i MySql.Data.dll

You need to register the dll in the GAC with root privileges; hence why we sudo. You can check the dll was successfully registered with the following commands.

Through the GAC utility application

gacutil -l | grep My

Or by searching the physical location of the GAC.

cd /usr/lib/mono/gac
ls | grep MySql.Data

No matter how many .NET run-time environment versions you have installed you will onyl have one GAC by default. The dlls are version registered which is automatically resolved during run-time. When you reference a dll which has been installed in the GAC you  reference a copy of a correct versioned dll anywhere on your hard disk; this is resolved to the version in the GAC during run-time.

Machine.Config Registration

The MySQL data provider needs to be registered within the Machine.Config. Depending upon how many .Net run-time versions you have installed on your machine you could have more than one machine.config on your disk. I have .NET 2, 3 and 4 with a machine.config inside each of the installations.

I am coding in the latest available version of .Net 4 so I placed my config inside this file; /etc/mono/4.0/machine.config. You will need to edit this file as root. Search for the section and add in a section for our MySQL data provider.

        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL"
	    type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

Please note the version number of the download against the version you have downloaded. The PublicKeyToken should remain constant.

Connecting through ADO.NET

Firstly register our database as a connection string within the machine.config used previously. Search for and add in the connection details for our MySQL database named DotNetOnLinuxExample.

        <add name="DotNetOnLinux" connectionString="Server=localhost;Database=DotNetOnLinuxExample;User ID=ReadWriteUser;Password=MYPASSWORD;Pooling=false" providerName="MySql.Data." />

This registers our MySQL database as running on our local host. The Database connected to is Database={DataBaseName} which we created previously as DotNetOnLinuxExample. I will create a new user with ReadWrite access which will only have access to read and write data. Using our AdminUser user would allow our code more rights than is required. I have kept connection pooling turned off as we are running locally with no concurrent users. The final part is the name of the dll we downloaded and regsitered in the GAC for the MySQL data provider.

We can then simply refer to this string as DotNetOnLinux through our applications strings like so:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;

Next we need to add a reference to the MySql.Data.dll. As noted before we need to locate this dll as if it is a standalone dll and not registerd in the GAC

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows a database agnostic connection and consumption which I will prove later when we swap MySQL for Sqlite with minimal effort; cough*.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download where I have tried to implement a simple DAL layer with basic ORM capability.

Creating a MySQL User with Read and Write Access

We don’t want our run time environment to have access to any commands which we don’t need. We will create a user for our ADO.NET provider to use on our database DotNetOnLinuxExample which only has read and write access to this database.

The following creates a user called ReadWrite on the database named DotNetOnLinuxExample which can only connect to our database from the local host. Change the password NEWPD to something meaningful. We then grant permission to run the commands SELECT, INSERT, DELETE and UPDATE on any table within this database. This might not be as secure as required however for development purposes this is secure enough.

INSERT INTO mysql.user (User,Host,Password) VALUES('ReadWriteUser','localhost',PASSWORD('NEWPD'));

GRANT SELECT, INSERT, DELETE, UPDATE ON DotNetOnLinuxExample.* TO ReadWriteUser@'localhost';

show grants for 'ReadWriteUser'@'localhost';

Day 7 – Dot NET On Linux – Installing and a quick tour of Monodevelop monodevelop-database plugin


Monodevelop comes with a database plugin which can allow you to connect to various databases including MySQL. Once connected you can write and run SQL as well as access wizards to create and modify schema.

The Source Code

This post is part of my Dot Net On Linux posts. All the source is available on GitHub here


The monodevelop database plugin is available within the LMDE repositories; install by running the following command:

sudo apt-get install monodevelop-database

A quick tour

All menu items can be found under the Tools –> Database menu.

The database pad shows a tree view of connected databases and their schema. Access by View –> Pads –> Database Viewer menu.

Adding a Database Connection

Open the database connection dialog by opening Tool –> Databases –> Add Database Connection.

Enter a name for the connection along with the user name and password. Test the connection and select OK. The default port should be 3306.

Running Queries

Open a query window by selecting Tools –> Databases –> Query Database. Alternatively if you have the Database Viewer Pad open highlight the database and select Query Database from the context menu.

Schema Changes

Schema changes can be made via the query windows. Alternatively there is a create table dialog box which has some functionality.  It appears to be missing identity index though I did not investigate for much time as I personally prefer Workbench. Some screen shots below of the create table dialog box.

Day 6 – Dot NET On Linux – Installing and a quick tour of Workbench


Work bench is a GUI for managing and interacting with MySQL; just like SQL Management Studio for SQL Server. It allows you to perform tasks such as creating, querying and maintaining data, creating and modifying schema as well as database administration tasks. This post gives instructions on how to install, connect and interact with our new MySQL database which we created previously in the last blog post here.. We also have a quick  tour of the more common features we will require. You can read more here.

The Source Code

This post is part of my Dot Net On Linux posts. All the source is available on GitHub here


You can install the latest version of Workbench from the website above however installing everything from your supported Linux distro’s repositories is a recommend practice. If you installed the latest version of MySQL Server then you should follow suit here; however assuming you have come from my previous blog then you are using the version as installed in your repositories.

sudo apt-get install mysql-workbench


An icon should have been placed within your start menu (Programming menu tree for me). Alternatively you can run the following command


Connect to our database

Configure A Connection

We are going to configure a database connection which will persist; we will then be able to connect to the database by selecting our configured connection in our connection list. Go to Database –> Manage Connections and the Manage DB Connections dialog will open. Enter the information as shown in the screen below replacing any details for your installation.

This will create a configured entry call DotNetOnLinux which connects to our service running on local host ( on the default port (which we did not change this during installation) of 3306. The user we configured was called AdminUser.

For password hit the Store in Keychain.

Hit test connection and make sure it succeeds

Open a connection

To start working on our database we need to open the Connect to Database dialog box. This can be found under the Database menu; Database –> Query Database.

Select the configured connection from the previous step, select OK and when prompted enter your password. This is the MySQL user’s password and not your desktop user’s password.

Lets check the connection; we can see our database in the sachems tree view on the far left. You can open the tree view to see any tables and views.

Lets test the connection further by running a database query. Run the following to see which databases we are connected to:

show databases

You should see DotNetOnLinuxExample (or the name of you database you have created) and information_schema

A quick tour

Far left the schema tree view which will show any databases, tables, stored procedures, views etc. Highlighting a table allows pre-configured scripts to insert, update, delete and select statement into a query pane.

Far right is a snippets pane with pre-configured SQL which can be used as a template.

To create a new query tab there is a menu item under the File menu.

Results are displayed bottom middle.

Create a table

Highlight tables under the DotNetOnLinuxExample database schema entry.

Enter the table name. This one is called SampleTable. Leave the Schema to be our database. Leave the collation and engine as the defaults.

The following screen shot shows what to put in. Double click the last row under column name to enter a new name. Enter the names and data types as shown below.

The check boxes represent:

PX: Primary Key
NN: Non Null (mandatory)
AI: Identity Insert
Default: Default value when not provided.

Being a simple example we can leave everything else. Hit the apply button and.

Our table is now created and we can validate this by running the following query in a Query tab.

<pre>select * from DotNetOnLinuxExample.SampleTable

Lets insert some data,

INSERT INTO DotNetOnLinuxExample.SampleTable
(Name, Height, DateOfBirth)
VALUES ( 'Test', 180, '1978-10-25' )

Lets check the data was created correctly.

select * from DotNetOnLinuxExample.SampleTable

Also note the ID column was automatically populated. We can insert many entries and this will automatically be incremented.

Additional Worthy Notes

I am loving the beautify script option. My only gripe is it does not put key words into upper-case.

Toggle Auto Commit option along with Commit / Roll-back is pretty sweet as well.

Day 5 – Dot NET On Linux – Installing and Securing MySQL


MySQL is an open source relational database which requires little or no introduction.  MySQL also provides an ADO.NET data provider for .NET and more importantly Mono.

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Installing MySQL

The current latest version is 6 however LMDE repositories server are referencing 5.5 for both the client and server. Install the server with the following command. If you are using a machine with the server installed you don’t need to install the client.

sudo apt-get install mysql-server

During installation you should be prompted to set a root password; if not we can fill this in later when we secure the the installation.

Starting & Stopping

To manually start and stop MySQL service you can run the following commands. It should be started after installation and automatically after reboot.

sudo  service mysql start
sudo service mysql stop

Automatically start upon booting

To start MySQL during boot-up use the following commands:

sudo insserv mysql

To start a MySQL shell use the following command

To start a MySQL prompt you need to log in. If you were not prompted for a root password earlier this will be blank.

/usr/bin/mysql -u root -p Password

Securing MySQL Server

Seeing as we are developing locally we can tie down the access to usage only on localhost.

We  will disable startup on boot, password protect all user accounts, remove guest user access, remove test access and then finally create a new database with a new user who has administrative rights to this database so we don’t have to log in with root.

Disable MySQL Server at BootUp

I don’t like to have this sort of service started automatically on my personal laptop. The service will more than likely be configured to automatically start irrelevant of our instruction to do so above. To remove the auto start use the following command.

sudo insserv -r mysql

You can use the following commands to start and stop the MySQL service when required.

sudo service mysql start
sudo service mysql stop

Securing Root

You should have been promoted to set a root password during installation. If you were not then use the following command to set a root password replacing ‘NEWPD’ in PASSWORD with your required password. This needs to be done inside the MySQL terminal.

UPDATE mysql.user SET Password = PASSWORD('NEWPD') WHERE User = 'root';


SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEWPD');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('NEWPD');

Notice how we are simply setting a field in the mysql database table user. The PASSWORD function encrypts your password. If you omit this your password will be in plain text. You can read about MySQL hashing algorithms here.

Securing Anonymous users

Within a MySQL command prompt search the users table for users without password or usernames

select user, host, password from mysql.user;

In my installation I have anonymous users for localhost and my hostname

You can set passwords for these accounts in the same was as above however I would recommend removing the users. You need to replace MyHostName with your host name if you have an entry displayed from the above query; this is displayed in the host column.

DROP USER ''@'localhost';
DROP USER ''@'MyHostName';

Securing Test databases

MySQL comes with a test database already set up. I am going to create my own so I want to remove this.

DELETE FROM mysql.db WHERE Db LIKE 'test%';

Create a new Database

We will now create a database. We will do this before creating our user so that we can add the permissions for the new database to our user upon creation.

create database DotNetOnLinuxExample;
show databases;

Creating a user.

It is not a good idea to use root in any environment so we are going to create our own user. This will be a user with full privileges to the database schema we just created. When we come to run .Net with MySQL access we will create a new user account with read and write access only.

We are going to create a user which is only accessible on the local host. Replace NEWPD in PASSWORD with your required password.

<pre>INSERT INTO mysql.user (User,Host,Password) VALUES('AdminUser','localhost',PASSWORD('NEWPD'));

GRANT ALL PRIVILEGES ON DotNetOnLinuxExample.* to AdminUser@localhost;
SHOW GRANTS FOR 'AdminUser'@'localhost';

You can now log on with the following command.

mysql -u AdminUser -p

Day 4 – Dot NET On Linux – Mocking with Rhino Mocks


“Rhino Mocks is a dynamic mock object framework for the .Net platform. Its purpose is to ease testing by allowing the developer to create mock implementations of custom objects and verify the interactions using unit testing.”

The Source Code

This post is part of my Dot Net On Linux posts. All the source the source code is available on GitHub here

What is a ‘Mocking’ framework?

Mocking allows us to fake the components in our system which can not be unit tested. This might be because the resource is expensive to unit test such as a database or is outside of our domain such as a web service. By mocking these objects and faking any actions required by them including returning fake responses or data, we can increase the coverage of our unit tests.

How To Mock

Though strictly not necessary it is recommend for code to adhere to the Interface Segregation Principle. We can then mock our interface and use DI to inject our mock into our interface. See my previous blog for using the Ninject dependency injection framework.

The mock framework will absorb any calls to the class or interface that it is mocking. If we need to return data, respond to requests or throw exceptions we can stub the mock to simulate this.

We are going to be using the mocking framework Rhino Mocks. It’s usage has simplified since version 3.5. The traditional record and playback syntax has been replaced by a simpler syntax like Moq though still allows us to Stub and Moq. There is a difference between mocking and stubbing and I would recommend reading the following post.


I won’t lie here. I am a little bit confused. 3.5 is stable though I can only find the download for 3.6 which is a nightly build. Seeing as we are only investigating I don’t see any problem using a development build.

Downloads can be found here or here.


Unzip and place somewhere useful. Installation into our solution is as simple as adding a reference to the Rhino.Mocks.dll which should be extracted.

Simply add a reference to the Rhino.Mocks.dll in your unit test project. Project –> Edit References to activate the Edit References dialog. Navigate to the unzipped directory and select Rhino.Mocks.dll.

Lets Code

We are going to mimic a Domain Driven Design strategy which might be similar to some code we would like to unit test but can’t without a database with read and write access.  Please note this is only a crude mimic to show the principle only and not a true DDD design strategy. We want to save a Model class via a Model Repository. Though we only have one model in our domain we will use an aggregate called Model Creator.. Again not strictly DDD but a close enough representation to show how we can effectively mock our DAL for unit testing. In this case our DAL is our Model Repository which implements IModelRepository.

Below is the code required to initiate our mock including creating a mock, stubbing any functions or properties we would like to fake and binding our mock to our interface within our DI kernel.

using Rhino.Mocks;
using Rhino;

public void PreTestInitialize ()

	// instance variable which stores or fake database data.
	mockedModels = new List () {
		new Model() { FieldA = 1, FieldB = "Mock 1"},
    	new Model() { FieldA = 2, FieldB = "Mock 2"},
    	new Model() { FieldA = 3, FieldB = "Mock 3"},
    	new Model() { FieldA = 4, FieldB = "Mock 4"}

	// we mock our IModelRepository with RhinoMocks
	IModelRepository mock = MockRepository.GenerateStub ();

	// We stub the database read function GetModels on IModelReposiotry

	mock.Stub (m => m.GetModels ()).Return (mockedModels);

	// We stub a properry to prove that our reale ModelRepository (set to false) is not being used.
	mock.Stub (m => m.IsMock).Return (true);

	// we use out Ninject DI framework to inject our mock repository
	ninjectKernel.Bind ().ToConstant (mock);

We can then fake the GetModels function by creating a mock object to fake a response and inject a concrete class to our abstraction via our DI kernel. The models are retrieved as expected and without touching our database.

public void CanMockMethodTest ()
	var modelCreator = ninjectKernel.Get ();

	var models = modelCreator.GetModels ();

	Assert.AreEqual (models.FirstOrDefault ().FieldA, mockedModels.First ().FieldA);
	Assert.AreEqual (models.FirstOrDefault ().FieldB, mockedModels.First ().FieldB);

The mock absorbs all public entities upon the object we are mocking. Our aggregate calls add upon our repository when we call CreateModel even thought it does not return any data. We did not mock this call but we still would like to assure that the Add function was called. We can test this by asking it!.

<pre>public void CanMockCreate ()
	var modelCreator = ninjectKernel.Get ();
	var modelRepository = ninjectKernel.Get ();

	var aModel = new Model () { FieldA = 1, FieldB = "Test"};
	modelCreator.CreateModel (aModel);

	modelRepository.AssertWasCalled (p => p.Add (aModel));

Our ModelRepository has a property is IsMock which is hard coded to false. We stubbed the property to be true within our mock repository. Some added prof that out concrete ModelRepository is not called upon for our mock testing.

public void CanMockProperty ()
	var modelCreator = ninjectKernel.Get ();

	Assert.IsTrue( modelCreator.IsMock);

Day 3 – Dot NET on Linux – Dependency Injection with Ninject


Dependency injection along with Moc Frameworks are becoming  increasingly popular. They allow decoupling of parts of the system to allow them to be substituted easily during runtime. This is especially powerful when the item being substituted is an expensive entity which is hard to consume during unit testing.

The Source Code

This post is part of my Dot Net On Linux posts. All the source the source code from the series of posts is available on GitHub here

What is Dependency Injection?

DI for short is a control mechanism which allows us to satisfy the Dependency Inversion Principle of the SOLID Design Principles.

By dictating to lower level components what other lower level components are to be used we can uncouple parts of a system to allow them to be independently replaced. This is done by injecting concrete class implementations into classes which are expecting abstractions.

DI is the principle which also allows us to inject mock implementation of components which are expensive or hard to consume while unit testing. This can allow us to unit test without a database or 3rd party web services. This can allow a greater coverage of unit testing and also allowing unit testing to be run quicker meaning there is more chance they will be run.


Ninject is a DI framework which is commonly used. What it lacks comparing to other DI frameworks it makes up in its simplicity.

The Source Code

All the source code from the series of posts is available on GitHub here.


mkdir Ninject
mv Ninject
cd Ninject

Adding Ninject References

To add Ninject into a  project; highlight the project within the solution tree then from the Project menu “Project –> Edit References”.

From the “Edit References” dialog box select the .Net Assembly tab. Navigate to the unzipped Ninject directory and highlight Ninject.dll. Select the add button and then ok.

Ninject can then be imported into a class with “using Ninject”

The code

The most common occurrence of DI injection are

  • Resolve concrete class from abstract interface
  • Resolve concrete class with dependence of constructor DI
  • Resolve concrete class from abstract interface with configured constructor arguments
  • Conditionally resolve concrete class from abstract interface when injected into a child class

Resolve concrete class from abstract interface

The most common form of injection requires resolving a concrete implementation of a class when referencing its interface abstraction.
At an initial point in our application run time we create an instance of the DI kernel and register our required concrete class against our interface. Later on when we want to use the class via its abstraction we ask the DI kernel to resolve this from its abstract interface and provide us with an instance.

// The DI Kernel
var kernelInstance = new StandardKernel ();

// Bind our concre
kernelInstance.Bind().To (typeof(SimpleClass));

// Retrieval of a concrete implementation of our interface
var myClass = kernel.Get();

Resolve concrete class with dependence of constructor DI

If any constructor or class being resolved requires arguments these can be automatically passed in upon creation. As long as we have a parameterless constructor for the argument all this can happen automatically.

// Constructor of class being resolved by the DI kernel
public ClassWithDependancy (ISimpleClass simpleClass)
    this.SimpleClass = simpleClass;

// The DI Kernel
var kernelInstance = new StandardKernel ();

// Bind our concrete classes to abstractions
kernelInstance.Bind().To (typeof(SimpleClass));
kernelInstance.Bind().To (typeof(ClassWithDependancy));

// Retrieval of a concrete implementation of our interface
var myClass = kernel.Get();

Resolve concrete class from abstract interface with configured constructor arguments

An injection with parameters in the constructor is also catered for.

kernel.Bind().To (typeof(ClassWithConstructorParameters)).
WithConstructorArgument ("messageOne", "Hello").
WithConstructorArgument ("messageTwo", "There");

Conditionally resolve concrete class from abstract interface when injected into a child class

If two classes implement the same interface we can conditional config which is injected into an implementing classes constructor.


Dot Net on Linux

As described here I have started a series of posts about developing .Net on Linux.

All source code can be found on GitHub here.

The posts so far:

Day 0: Setting up the .Net Development Environment
Day 1: Hello World in ASP.NET MVC 2
Day 2: Unit testing with NUnit
Day 3: Dependency Injection with Ninject
Day 4: Mocking with Rhino Mocks
Day 5: Installing and Securing MySQL
Day 6: Installing and a quick tour of Workbench
Day 7: Installing and a quick tour of MonoDevelop Database Plugin
Day 8.1:  Connecting & Consuming MySQL with ADO.NET
Day 8.2: Connecting & Consuming SQLite with ADO.NET
Day 8.3: Agnostic database connection with ADO.NET
Day 9: Generics
Day 10: Linq
Day 11: NInject
Day 12: NUnit
Day 13: Delegates and Lambdas
Day 14: Rhino Mocks