This is an attempted writeup of the methodology used with Mono / SQLite / ADO.NET in OpenSim. There is a real lack of information out there when trying to do something with these technologies. (this is very ongoing)

ADO.NET Overview

ADO.NET provides a level of abstraction from embedding SQL statements all through your code (which would be very bad). This is done by creating a top level set of objects that you can use to manipulate loaded data, as well as a way to connect these top level objects to your actual database.

• Top Level Types
  • DataSet - an in memory copy of your database
  • DataTable - a mapping of a database table
  • DataRow, DataColumn, etc. - you get the picture
• Vendor Specific DB Types
  • SqliteConnection - provides connection to an sqlite database
  • SqliteCommand - an SQL query against the database
  • SqliteParameter - an SQL parameter for parametrized queries

One of the early confusion points in reading any MicroSoft based documentation is that MS has claimed Sql as their vendor prefix for MS SQL Server. This can cause quite a bit of confusion if you think of Sql as something generic, and don't realize those are all examples specific to MS SQL Server.

Connecting Top Level Types to the Database

This example code will work, but it shouldn't be used, for reasons we'll go into later.

SqliteConnection con = new SqliteConnection("URI=file:opensim.db,version=3");
SqliteCommand fillcmd = new SqliteCommand("select * from prims", con);
SqliteDataAdapter da = new SqliteDataAdapter(fillcmd);
DataSet ds = new DataSet(); 
ds.Fill(da, "prims");

The database connection is vendor specific, obviously, as is the SQL statement (less obvious, but there are vendor differences that need to be handled). DataAdapter is an intermediary which manages pulling things from the database and getting them into the DataSet. Again, you can understand why this would be vendor specific.

The Dataset then uses that DataAdapter to fill itself from the dataconnection using the fillcmd we have specified.

XML for Free

One of the great things about using ADO.NET is that you get an intermediary XML format for free. Every DataSet has the following methods:

• GetXml(), GetXmlSchema()
• WriteXml(), WriteXmlSchema()
• ReadXml(), ReadXmlSchema()

Each of these provide a full dump/store/load mechanism for the entire DataSet as an XML document. This includes all tables stored in the DataSet. GetXml() can be very useful for debugging.

Sqlite Specific Commands

Creating Schema Definitions

Building SqliteCommands