Reading and Writing XML Documents

In this article, you will see how to read and write XML documents in Microsoft .NET using C# language. First, I will discuss XML .NET Framework Library namespace and classes. Then, you will see how to read and write XML documents. In the end of this article, I will show you how to take advantage of ADO.NET and XML .NET model to read and write XML documents from relational databases and vice versa.

Introduction to Microsoft .NET XML Namespaces and Classes

Before start working with XML document in .NET Framework, It is important to know about .NET namespace and classes provided by .NET Runtime Library. .NET provides five namespace - System.Xml, System.Xml.Schema, System.Xml.Serialization, System.Xml.XPath, and System.Xml.Xsl to support XML classes.

The System.Xml namespace contains major XML classes. This namespace contains many classes to read and write XML documents. In this article, we are going to concentrate on reader and write class. These reader and writer classes are used to read and write XMl documents. These classes are - XmlReader, XmlTextReader, XmlValidatingReader, XmlNodeReader, XmlWriter, and XmlTextWriter. As you can see there are four reader and two writer classes.

The XmlReader class is an abstract bases classes and contains methods and properties to read a document. The Read method reads a node in the stream. Besides reading functionality, this class also contains methods to navigate through a document nodes. Some of these methods are MoveToAttribute, MoveToFirstAttribute, MoveToContent, MoveToFirstContent, MoveToElement and MoveToNextAttribute. ReadString, ReadInnerXml, ReadOuterXml, and ReadStartElement are more read methods. This class also has a method Skip to skip current node and move to next one. We'll see these methods in our sample example.

The XmlTextReader, XmlNodeReader and XmlValidatingReader classes are derived from XmlReader class. As their name explains, they are used to read text, node, and schemas.

The XmlWrite class contains functionality to write data to XML documents. This class provides many write method to write XML document items. This class is base class for XmlTextWriter class, which we'll be using in our sample example.

The XmlNode class plays an important role. Although, this class represents a single node of XML but that could be the root node of an XML document and could represent the entire file. This class is an abstract base class for many useful classes for inserting, removing, and replacing nodes, navigating through the document. It also contains properties to get a parent or child, name, last child, node type and more. Three major classes derived from XmlNode are XmlDocument, XmlDataDocument and XmlDocumentFragment. XmlDocument class represents an XML document and provides methods and properties to load and save a document. It also provides functionality to add XML items such as attributes, comments, spaces, elements, and new nodes. The Load and LoadXml methods can be used to load XML documents and Save method to save a document respectively. XmlDocumentFragment class represents a document fragment, which can be used to add to a document. The XmlDataDocument class provides methods and properties to work with ADO.NET data set objects.

In spite of above discussed classes, System.Xml namespace contains more classes. Few of them are XmlConvert, XmlLinkedNode, and XmlNodeList.

Next namespace in Xml series is System.Xml.Schema. It classes to work with XML schemas such XmlSchema, XmlSchemaAll, XmlSchemaXPath, XmlSchemaType.

The System.Xml.Serialization namespace contains classes that are used to serialize objects into XML format documents or streams.

The System.Xml.XPath Namespce contains XPath related classes to use XPath specifications. This namespace has following classes -XPathDocument, XPathExression, XPathNavigator, and XPathNodeIterator. With the help of XpathDocument, XpathNavigator provides a fast navigation though XML documents. This class contains many Move methods to move through a document.

The System.Xml.Xsl namespace contains classes to work with XSL/T transformations.

Reading XML Documents

In my sample application, I'm using books.xml to read and display its data through XmlTextReader. This file comes with VS.NET samples. You can search this on your machine and change the path of the file in the following line:

XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

Or you can use any XML file.

The XmlTextReader, XmlNodeReader and XmlValidatingReader classes are derived from XmlReader class. Besides XmlReader methods and properties, these classes also contain members to read text, node, and schemas respectively. I am using XmlTextReader class to read an XML file. You read a file by passing file name as a parameter in constructor.

XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

After creating an instance of XmlTextReader, you call Read method to start reading the document. After read method is called, you can read all information and data stored in a document. XmlReader class has properties such as Name, BaseURI, Depth, LineNumber an so on.

List 1 reads a document and displays a node information using these properties.

About Sample Example 1

In this sample example, I read an XML file using XmlTextReader and call Read method to read its node one by one until end of file and display the contents to the console output.

Sample Example 1.

using System;

using System.Xml;

namespace ReadXml1

{

class Class1

{

static void Main(string[] args)

{

// Create an isntance of XmlTextReader and call Read method to read the file

XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

textReader.Read();

// If the node has value

while (textReader.Read())

{

// Move to fist element

textReader.MoveToElement();

Console.WriteLine("XmlTextReader Properties Test");

Console.WriteLine("===================");

// Read this element's properties and display them on console

Console.WriteLine("Name:" + textReader.Name);

Console.WriteLine("Base URI:" + textReader.BaseURI);

Console.WriteLine("Local Name:" + textReader.LocalName);

Console.WriteLine("Attribute Count:" + textReader.AttributeCount.ToString());

Console.WriteLine("Depth:" + textReader.Depth.ToString());

Console.WriteLine("Line Number:" + textReader.LineNumber.ToString());

Console.WriteLine("Node Type:" + textReader.NodeType.ToString());

Console.WriteLine("Attribute Count:" + textReader.Value.ToString());

}

}

}

}

The NodeType property of XmlTextReader is important when you want to know the content type of a document. The XmlNodeType enumeration has a member for each type of XML item such as Attribute, CDATA, Element, Comment, Document, DocumentType, Entity, ProcessInstruction, WhiteSpace and so on.

List 2 code sample reads an XML document, finds a node type and writes information at the end with how many node types a document has.

About Sample Example 2

In this sample example, I read an XML file using XmlTextReader and call Read method to read its node one by one until end of the file. After reading a node, I check its NodeType property to find the node and write node contents to the console and keep track of number of particular type of nodes. In the end, I display total number of different types of nodes in the document.

Sample Example 2.

using System;

using System.Xml;

namespace ReadingXML2

{

class Class1

{

static void Main(string[] args)

{

int ws = 0;

int pi = 0;

int dc = 0;

int cc = 0;

int ac = 0;

int et = 0;

int el = 0;

int xd = 0;

// Read a document

XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

// Read until end of file

while (textReader.Read())

{

XmlNodeType nType = textReader.NodeType;

// If node type us a declaration

if (nType == XmlNodeType.XmlDeclaration)

{

Console.WriteLine("Declaration:" + textReader.Name.ToString());

xd = xd + 1;

}

// if node type is a comment

if (nType == XmlNodeType.Comment)

{

Console.WriteLine("Comment:" + textReader.Name.ToString());

cc = cc + 1;

}

// if node type us an attribute

if (nType == XmlNodeType.Attribute)

{

Console.WriteLine("Attribute:" + textReader.Name.ToString());

ac = ac + 1;

}

// if node type is an element

if (nType == XmlNodeType.Element)

{

Console.WriteLine("Element:" + textReader.Name.ToString());

el = el + 1;

}

// if node type is an entity\

if (nType == XmlNodeType.Entity)

{

Console.WriteLine("Entity:" + textReader.Name.ToString());

et = et + 1;

}

// if node type is a Process Instruction

if (nType == XmlNodeType.Entity)

{

Console.WriteLine("Entity:" + textReader.Name.ToString());

pi = pi + 1;

}

// if node type a document

if (nType == XmlNodeType.DocumentType)

{

Console.WriteLine("Document:" + textReader.Name.ToString());

dc = dc + 1;

}

// if node type is white space

if (nType == XmlNodeType.Whitespace)

{

Console.WriteLine("WhiteSpace:" + textReader.Name.ToString());

ws = ws + 1;

}

}

// Write the summary

Console.WriteLine("Total Comments:" + cc.ToString());

Console.WriteLine("Total Attributes:" + ac.ToString());

Console.WriteLine("Total Elements:" + el.ToString());

Console.WriteLine("Total Entity:" + et.ToString());

Console.WriteLine("Total Process Instructions:" + pi.ToString());

Console.WriteLine("Total Declaration:" + xd.ToString());

Console.WriteLine("Total DocumentType:" + dc.ToString());

Console.WriteLine("Total WhiteSpaces:" + ws.ToString());

}

}

}

Writing XML Documents

XmlWriter class contains the functionality to write to XML documents. It is an abstract base class used through XmlTextWriter and XmlNodeWriter classes. It contains methods and properties to write to XML documents. This class has several Writexxx method to write every type of item of an XML document. For example, WriteNode, WriteString, WriteAttributes, WriteStartElement, and WriteEndElement are some of them. Some of these methods are used in a start and end pair. For example, to write an element, you need to call WriteStartElement then write a string followed by WriteEndElement.

Besides many methods, this class has three properties. WriteState, XmlLang, and XmlSpace. The WriteState gets and sets the state of the XmlWriter class.

Although, it's not possible to describe all the Writexxx methods here, let's see some of them.

First thing we need to do is create an instance of XmlTextWriter using its constructor. XmlTextWriter has three overloaded constructors, which can take a string, stream, or a TextWriter as an argument. We'll pass a string (file name) as an argument, which we're going to create in C:\ root.

In my sample example, I create a file myXmlFile.xml in C:\\ root directory.

// Create a new file in C:\\ dir
XmlTextWriter textWriter = new XmlTextWriter("C:\\myXmFile.xml", null) ;

After creating an instance, first thing you call us WriterStartDocument. When you're done writing, you call WriteEndDocument and TextWriter's Close method.

textWriter.WriteStartDocument();
textWriter.WriteEndDocument();
textWriter.Close();

The WriteStartDocument and WriteEndDocument methods open and close a document for writing. You must have to open a document before start writing to it. WriteComment method writes comment to a document. It takes only one string type of argument. WriteString method writes a string to a document. With the help of WriteString, WriteStartElement and WriteEndElement methods pair can be used to write an element to a document. The WriteStartAttribute and WriteEndAttribute pair writes an attribute.

WriteNode is more write method, which writes an XmlReader to a document as a node of the document. For example, you can use WriteProcessingInstruction and WriteDocType methods to write a ProcessingInstruction and DocType items of a document.

//Write the ProcessingInstruction node
string PI= "type='text/xsl' href='book.xsl'"
textWriter.WriteProcessingInstruction("xml-stylesheet", PI);
//'Write the DocumentType node
textWriter.WriteDocType("book", Nothing, Nothing, "");

The below sample example summarizes all these methods and creates a new xml document with some items in it such as elements, attributes, strings, comments and so on. See Listing 5-14. In this sample example, we create a new xml file c:\xmlWriterText.xml. In this sample example, We create a new xml file c:\xmlWriterTest.xml using XmlTextWriter:

After that, we add comments and elements to the document using Writexxx methods. After that we read our books.xml xml file using XmlTextReader and add its elements to xmlWriterTest.xml using XmlTextWriter.

About Sample Example 3

In this sample example, I create a new file myxmlFile.xml using XmlTextWriter and use its various write methods to write XML items.

Sample Example 3.

using System;

using System.Xml;

namespace ReadingXML2

{

class Class1

{

static void Main(string[] args)

{

// Create a new file in C:\\ dir

XmlTextWriter textWriter = new XmlTextWriter("C:\\myXmFile.xml", null);

// Opens the document

textWriter.WriteStartDocument();

// Write comments

textWriter.WriteComment("First Comment XmlTextWriter Sample Example");

textWriter.WriteComment("myXmlFile.xml in root dir");

// Write first element

textWriter.WriteStartElement("Student");

textWriter.WriteStartElement("r", "RECORD", "urn:record");

// Write next element

textWriter.WriteStartElement("Name", "");

textWriter.WriteString("Student");

textWriter.WriteEndElement();

// Write one more element

textWriter.WriteStartElement("Address", ""); textWriter.WriteString("Colony");

textWriter.WriteEndElement();

// WriteChars

char[] ch = new char[3];

ch[0] = 'a';

ch[1] = 'r';

ch[2] = 'c';

textWriter.WriteStartElement("Char");

textWriter.WriteChars(ch, 0, ch.Length);

textWriter.WriteEndElement();

// Ends the document.

textWriter.WriteEndDocument();

// close writer

textWriter.Close();

}

}

}

Using XmlDocument

The XmlDocument class represents an XML document. This class provides similar methods and properties we've discussed earlier in this article.

Load and LoadXml are two useful methods of this class. A Load method loads XML data from a string, stream, TextReader or XmlReader. LoadXml method loads XML document from a specified string. Another useful method of this class is Save. Using Save method you can write XML data to a string, stream, TextWriter or XmlWriter.

About Sample Example 4

This tiny sample example pretty easy to understand. We call LoadXml method of XmlDocument to load an XML fragment and call Save to save the fragment as an XML file.

Sample Example 4.

//Create the XmlDocument.
XmlDocument doc = new XmlDocument();
doc.LoadXml(("Tommy
ex"));
//Save the document to a file.
doc.Save("C:\\std.xml");
You can also use Save method to display contents on console if you pass Console.Out as a
arameter. For example:
doc.Save(Console.Out);

About Sample Example 5

Here is one example of how to load an XML document using XmlTextReader. In this sample example, we read books.xml file using XmlTextReader and call its Read method. After that we call XmlDocumetn's Load method to load XmlTextReader contents to XmlDocument and call Save method to save the document. Passing Console.Out as a Save method argument displays data on the console

Sample Example 5.

XmlDocument doc = new XmlDocument();
//Load the the document with the last book node.
XmlTextReader reader = new XmlTextReader("c:\\books.xml");
reader.Read();
// load reader
doc.Load(reader);
// Display contents on the console
doc.Save(Console.Out);

Writing Data from a database to an XML Document

Using XML and ADO.NET mode, reading a database and writing to an XML document and vice versa is not a big deal. In this section of this article, you will see how to read a database table's data and write the contents to an XML document.

The DataSet class provides method to read a relational database table and write this table to an XML file. You use WriteXml method to write a dataset data to an XML file.

In this sample example, I have used commonly used Northwind database comes with Office 2000 and later versions. You can use any database you want. Only thing you need to do is just chapter the connection string and SELECT SQ L query.

About Sample Example 6

In this sample, I reate a data adapter object and selects all records of Customers table. After that I can fill method to fill a dataset from the data adapter.

In this sample example, I have used OldDb data provides. You need to add reference to the Syste.Data.OldDb namespace to use OldDb data adapters in your program. As you can see from Sample Example 6, first I create a connection with northwind database using OldDbConnection. After that I create a data adapter object by passing a SELECT SQL query and connection. Once you have a data adapter, you can fill a dataset object using Fill method of the data adapter. Then you can WriteXml method of DataSet, which creates an XML document and write its contents to the XML document. In our sample, we read Customers table records and write DataSet contents to OutputXml.Xml file in C:\ dir.

Sample Example 6.

using System;

using System.Xml;

using System.Data;

using System.Data.OleDb;

namespace ReadingXML2

{

class Class1

{

static void Main(string[] args)

{

// create a connection

OleDbConnection con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb";

// create a data adapter

OleDbDataAdapter da = new OleDbDataAdapter("Select * from Customers", con);

// create a new dataset

DataSet ds = new DataSet();

// fill dataset

da.Fill(ds, "Customers");

// write dataset contents to an xml file by calling WriteXml method

ds.WriteXml("C:\\OutputXML.xml");

}

}

}

Summary

.NET Framework Library provides a good support to work with XML documents. The XmlReader, XmlWriter and their derived classes contains methods and properties to read and write XML documents. With the help of the XmlDocument and XmlDataDocument classes, you can read entire document. The Load and Save method of XmlDocument loads a reader or a file and saves document respectively. ADO.NET provides functionality to read a database and write its contents to the XML document using data providers and a DataSet object.

.NET Serialization to SQLite Database

Most non-enterprise applications need the ability to store data locally. Some need only simple storage for things like settings, whereas others need a full SQL database capability for various needs. I have written many such applications over the years. Until I found SQLite, I had to use MS Access. On older computers, that meant installing MDAC (all of the ODBC/OLEDB drivers) - quite a large installer footprint. A happy day it was that I found out about SQLite. In case you don't know about SQLite, its a complete SQL database in a single file. And it is very fast and efficient. But - its all written in C and therefore is not suitable to use from .NET. We therefore need a COM wrapper for it so as to be able to use it from .NET languages. Fortunately there is SQLitePlus from EzTools Software (www.eztools-software.com/sqliteplus.htm), an excellent choice for a SQLite wrapper, as it has exceptional features for handling BLOB data and COM data types, and even encryption and compression.

This article explains 3 ways to serialize .NET objects to a SQLite database using SQLitePlus, which are: 1) Whole object serialization, 2) Custom object serialization, and 3) as normal database column/field level saving. The first two ways use a single binary database field (BLOB) to persist objects. The other way is using customary database columns (like FirstName, Age, etc.).

First, a bit of background on SQLitePlus. It consists of 2 DLLs - the COM DLL, which provides the COM classes you use in your programs. They are the SqliteDb, Dataset, Columns and Column classes. Using these COM class objects we can create new database files, execute SQL commands and get back result datasets. The other DLL is a "version DLL" which is a plain DLL (not COM) that encapsulates the SQLite database engine C code. The reason it is split into 2 DLLs is so that newer versions of the version DLL can be swapped-in without changing the COM DLL. For example, a new version of the SQLite engine is released, so you only need to update the version DLL rather than both. Also, you can make custom builds of the version DLL, for example to add your own User Defined Functions. Its quite a nifty system.

These DLLs are included in the source code Zip file, but you must register the COM DLL to run the projects (using RegSvr32.exe). A .cmd file is included that does this for you (click on runme.cmd).

Note: You can also download the SQLitePlus Database Manager from the EzTools website. It is very useful for visualizing your data as you experiment with the SQLitePlus COM DLL in your code. Here is the download link:

www.eztools-software.com/downloads/sqliteplus.exe

After registering the SQLitePlus COM DLL, we are ready to start by adding a reference to the SQLitePlus COM DLL in your project. In the "Add Reference" dialog, scroll down and find "EzTools SqlitePus 3.5 COM DLL", as shown here:

Now in MainFrm.cs, add this "using" statement near the top of the file:

using SQLITEPLUS35;

OK, now we're ready to add a SqliteDb member variable which we can use for our serialization functions. Add this line near the top of the class definition:

SqliteDb mDB = new SqliteDb();

Now scroll down to the form's Load event handler. Here we find the SqliteDb initialization and database opening:

String sErr;
ErrorCodeEnum eErr;

mDB.Init( "sqlt3317.dll", "demo", null );
mDB.Open( Application.StartupPath + @"\sqlite.db", -1, false );

Notice I have specified the version DLL name in the Init method call. If the version DLL is not in the same folder as the COM DLL, you must also specify its path. The second parameter is for the license key. Just use "demo" (you can use SQLitePlus with no license, but with a 10 row limit for dataset results).

The next line is the Open method call. This actually creates or opens the database file. The first time you run the project, the database file (sqlite.db) will be created. Thereafter it will be opened.

Next we create our database tables. I have created a helper function, TableExists, which checks the sqlite_master table for the table name. If its not there, we create the table.

Table Products1 is for BLOB serialization so it has only the ID and BLOB Data column. Here is the table definition for Products1:

CREATE TABLE Products1(ID INTEGER PRIMARY KEY NOT NULL, Data BLOB)

Products2 is for field serialization, so it has individual fields for each data member. Here is the table definition:

CREATE TABLE Products2(ID INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL, QtyPerUnit TEXT NOT NULL, Price FLOAT NOT NULL, LastOrderDate DATE NOT NULL)

This sort of table definition will look familiar to most programmers. Its a one-to-one mapping of the Product object fields to the table columns (see below).

OK, now that we have our database connection and the tables created, let's create our example class named Product. We must give it the Serializable Attribute so that we can use .NET Serialization. We then declare the data members. We'll add the serialization methods as we go. Here is the class definition with only the data members declared:

[System.Serializable]
public class Product
{
public int mnID;
public string msName,
msQtyPerUnit;
public float mPrice;
public DateTime mLastOrderDate;
}

I have deliberately declared members that use a few different data types so as to demonstrate how to use them with SQLitePlus. The members are public so as to simplify access, and consequently, code readability.

I create two Product objects in the code for our serialization purposes in CreateProducts.

void CreateProducts()
{
mProduct1 = new Product();
mProduct1.mnID = PROD_ID_1;
mProduct1.mPrice = 97F;
mProduct1.msName = "Mishi Kobe Niku";
mProduct1.msQtyPerUnit = "18 - 500 g pkgs.";
mProduct1.mLastOrderDate = DateTime.Today.AddDays( 90 );

mProduct2 = new Product();
mProduct2.mnID = PROD_ID_2;
mProduct2.mPrice = 18F;
mProduct2.msName = "Queso Cabrales";
mProduct2.msQtyPerUnit = "1 kg pkg.";
mProduct1.mLastOrderDate = DateTime.Today.AddDays( 120 );
}

Now we are ready to serialize our data.

Serialize Whole Objects Using .NET Serialization

Our first serialization method is to use .NET serialization to persist whole objects to the database. Our table has only the ID and Data columns, the latter of which is defined as BLOB. We will now see how to save entire objects to this single column. Here is the code.

private void lnkSerializeWholeObject_LinkClicked( object sender, LinkLabelLinkClickedEventArgs e )
{
// ensure we have an object
CreateProducts();

byte[] arData;

using(MemoryStream stream = new MemoryStream())
{
BinaryFormatter formatter = new BinaryFormatter();
formatter.Serialize( stream, mProduct1 );
arData = stream.ToArray();
stream.Close();
}

String sErr, sSql;
ErrorCodeEnum eErr;
SQLITEPLUS35.Dataset ds;
object oKey = null;

SQLITEPLUS35.VariantCollection paramsCol = new SQLITEPLUS35.VariantCollection();
paramsCol.Add( arData, oKey );

ds = mDB.Exec( "SELECT ID FROM Products1 WHERE ID=" + PROD_ID_1, null, out eErr, out sErr );

if(ds.eof)
sSql = String.Format( "INSERT INTO Products1 (ID, Data) VALUES({0}, ?)", PROD_ID_1);
else
sSql = String.Format( "UPDATE Products1 SET Data=? WHERE ID={0}", PROD_ID_1);

mDB.Exec( sSql, paramsCol, out eErr, out sErr );
}

The first thing we need are a MemoryStream and BinaryFormatter object. The latter is used to serialize our Product object into the former using built-in .NET serialization. Then we get the stream's contents as a byte array with the call arData = stream.ToArray().

So far so good. We are now ready to save the byte array as a BLOB into the SQLite Products1 table. To do this we need a SQLitePlus VariantCollection object, which is used to hold BLOB parameters. We call the Add method, giving a null key since we don't care about ordering the collection in any way. Now its not obvious, but what happens here is the byte array is converted to a COM SAFEARRAY by the COM InterOp runtime, which is exactly what we need to happen to insert or update a BLOB field.

Then we must determine if this is a new product or if it already exists in the database, so that we can build the appropriate SQL statement. To do this, we attempt to retrieve just the ID column for the Product ID of interest. We then check the Dataset to see if its empty (eof). If it is, we build an INSERT statement. If not, we build an UPDATE statement.

Notice here the ? in the SQL statements. This is the parameter substitution mechanism for SQLite. The BLOB data will be substituted deep within the SQLite engine as the Data column value. SQLitePlus provides us the high-level objects that give us the ability to store and retrieve binary data.

And of course, the last thing to do is execute the SQL statement with the SqliteDb.Exec method. We will note that the size of the byte array is 255 bytes.

OK. We have successfully created a SQLite database and table, and serialized a .NET class object into a BLOB field. Now lets look at how to recreate the same class object using .NET deserialization.

Deserialize Whole Objects using .NET Deserialization

Right, so now we want to re-create our class objects from the saved BLOB data. Its going to be very easy, as we see in the code below.

private void lnkDeserializeWholeObject_LinkClicked( object sender, LinkLabelLinkClickedEventArgs e )
{
String sErr;
ErrorCodeEnum eErr;
SQLITEPLUS35.Dataset ds;

ds = mDB.Exec( "SELECT ID, Data FROM Products1 WHERE ID=" + PROD_ID_1, null, out eErr, out sErr );

if(ds.eof)
{
MessageBox.Show( "Product not found!" );
return;
}

object val = ds.Columns["Data"].Value;

// check for NULL database value
if(Convert.IsDBNull( val ))
return;

byte[] arData = (byte[]) val;

using(MemoryStream stream = new MemoryStream())
{
stream.Write( arData, 0, arData.Length );
stream.Seek( 0, SeekOrigin.Begin );

BinaryFormatter formatter = new BinaryFormatter();
mProduct1 = (Product) formatter.Deserialize( stream );
// we could just assign PROD_ID_1, but I'm showing how to get column data
// from a Dataset
mProduct1.mnID = (int) ds.Columns[0].Value;
}
}

First we must select our Product row from the database into a Dataset object. We check for EOF, and if no row was found we display a message and return. If we have a row, we access the Data column using the Dataset's Columns collection. We assign the value to an object first, so we can use the Convert.IsDBNull method to check for a NULL database value. If its not null, we then cast the value to a byte array and write it to a MemoryStream. Then we create a BinaryFormatter object and call its Deserialize method, casting the return value to a Product type, thus giving us a newly deserialized Product object. Lastly, we assign the ID from the Dataset to show how to access other column data types. Notice I indexed by ordinal position rather than by column name (Columns[0] vs. Columns["ID"]). You can use either method.

Now we have seen how to save whole objects directly into a SQLite database file with very little programming effort. Now lets see how to use a custom BLOB serialization method, apart from any .NET mechanism.

Custom BLOB Serialization

This is our second method of serializing object data to SQLite database. We will show how to serialize the same object data, but in a more space efficient way and without having to derive from and implement the ISerializable interface. Here is the code:

private void lnkCustomSerialization_LinkClicked( object sender, LinkLabelLinkClickedEventArgs e )
{
// ensure we have an object
CreateProducts();

byte[] arData;

using( MemoryStream stream = new MemoryStream() )
{
using( BinaryWriter bWriter = new BinaryWriter( stream ) )
{
mProduct2.Serialize( bWriter );
arData = stream.ToArray();
}
}

String sErr, sSql;
ErrorCodeEnum eErr;
SQLITEPLUS35.Dataset ds;
object oKey = null;

SQLITEPLUS35.VariantCollection paramsCol = new SQLITEPLUS35.VariantCollection();
paramsCol.Add( arData, oKey );

ds = mDB.Exec( "SELECT ID FROM Products1 WHERE ID=" + PROD_ID_2, null, out eErr, out sErr );

if(ds.eof)
sSql = String.Format( "INSERT INTO Products1 (ID, Data) VALUES({0},?)", PROD_ID_2 );
else
sSql = String.Format( "UPDATE Products1 SET Data=? WHERE ID={0}", PROD_ID_2 );

mDB.Exec( sSql, paramsCol, out eErr, out sErr );

arData = null;
}

The code is almost identical to the whole object serialization example. So what have we done different? Notice we have called Product.Serialize instead of BinaryFormatter.Serialize. Let's now define Product.Serialize:

public void Serialize( BinaryWriter bWriter )
{
short nVer = 1; // each object should have its own version

bWriter.Write( nVer );
bWriter.Write( msName );
bWriter.Write( msQtyPerUnit );
bWriter.Write( (double) mPrice );
bWriter.Write( mLastOrderDate.ToOADate() );
}

Here we see that we explicitly write each member value that we want to serialize. We can also write a version number first, which makes it possible to add new fields later. We simply check the version number when deserializing to see if we should deserialize the new fields or not. Notice we use DateTime.ToAODate to convert mLastOrderDate to OLE date type.

Custom Deserialization

Again, the code is very similar to the .NET deserialization method. The only difference is the Product.Deserialize method call. Let's look at the code:

private void lnkCustomDeserialization_LinkClicked( object sender, LinkLabelLinkClickedEventArgs e )
{
if(mProduct2 == null)
mProduct2 = new Product();
else
mProduct2.Reset();

String sErr;
ErrorCodeEnum eErr;
Dataset ds = mDB.Exec( "SELECT ID, Data FROM Products1 WHERE ID=" + PROD_ID_2, null, out eErr, out sErr );
if(ds.eof)
{
MessageBox.Show( "Product not found!" );
return;
}
object val = ds.Columns[1].Value;

if(Convert.IsDBNull( val ))
return;

byte[] arData = (byte[]) val;

using(MemoryStream stream = new MemoryStream())
{
stream.Write( arData, 0, arData.Length );
stream.Seek( 0, SeekOrigin.Begin );

using(BinaryReader bReader = new BinaryReader( stream ))
{
mProduct2.Deserialize( bReader );
mProduct2.mnID = (int) ds.Columns[0].Value;
}
}
}

Here is the Product.Deserialize method definition:

public void Deserialize( BinaryReader bReader )
{
short nVer;

nVer = bReader.ReadInt16();
msName = bReader.ReadString();
msQtyPerUnit = bReader.ReadString();
mPrice = (float) bReader.ReadDouble();
double date = bReader.ReadDouble();
mLastOrderDate = DateTime.FromOADate( date );
}

Notice how straight-forward the code is. And see how we must convert our LastOrderDate field using DateTime.FromOADate. The version number can be used later when you must add new fields and know if you should deserialize them or not.

Custom deserialization is not quite as simple as .NET object serialization, but it is much more flexible. You have to define your own Serialize and Deserialize methods, but you have fine control over what goes in and comes out. And there are some instances where it is quite preferable. For example, when using Reflection to deserialize objects from a dynamically loaded assembly, you will get a security exception, thus preventing you from deserializing your objects (you would need to write a special handler to overcome this issue). Additionally, the size of the binary data is only 35 bytes vs. the 255 bytes for the .NET serialization method (no doubt the extra 220 bytes are .NET header info).

Now its on to our final serialization method.

Save to Table Columns

We have seen how to save entire objects into BLOB fields. Now we will show how to use traditional table columns using SQLitePlus. Here is the code for saving an object:

// ensure we have an object
CreateProducts();

String sErr, sSql;
ErrorCodeEnum eErr;
SQLITEPLUS35.Dataset ds;

sSql = String.Format( "SELECT ID FROM Products2 WHERE ID={0}", PROD_ID_1 );

ds = mDB.Exec( sSql, null, out eErr, out sErr );

if(ds.eof)
sSql = String.Format( "INSERT INTO Products2 (ID, Name, QtyPerUnit, Price, LastOrderDate) VALUES({0}, '{1}', '{2}', {3}, {4})", PROD_ID_1, mProduct1.msName, mProduct1.msQtyPerUnit, mProduct1.mPrice, mProduct1.mLastOrderDate.ToOADate() );
else
sSql = String.Format( "UPDATE Products2 SET Name='{0}', QtyPerUnit='{1}', Price={2}, LastOrderDate={3} WHERE ID={4}", mProduct1.msName, mProduct1.msQtyPerUnit, mProduct1.mPrice, mProduct1.mLastOrderDate.ToOADate(), PROD_ID_1 );

mDB.Exec( sSql, null, out eErr, out sErr );

You can see that we are simply formatting a SQL string by using the object data members directly. Notice that for the date field mLastOrderDate, we use the DateTime.ToOADate method to convert it to OLE date format, which is the double data type.

Load Columns From Table

OK, so lets see how to load our object fields from the database. Here is the last code:

private void lnkLoadFields_LinkClicked( object sender, LinkLabelLinkClickedEventArgs e )
{
if(mProduct1 == null)
mProduct1 = new Product();
else
mProduct1.Reset();

String sErr, sSql;
ErrorCodeEnum eErr;

sSql = "SELECT * FROM Products2 WHERE ID=" + PROD_ID_1;

Dataset ds = mDB.Exec( sSql, null, out eErr, out sErr );
if(ds.eof)
{
MessageBox.Show( "Product not found!" );
return;
}
mProduct1.mnID = (int) ds.Columns["ID"].Value;
mProduct1.msName = (string) ds.Columns["Name"].Value;
mProduct1.msQtyPerUnit = (string) ds.Columns["QtyPerUnit"].Value;
mProduct1.mPrice = (float) ds.Columns["Price"].Value;
mProduct1.mLastOrderDate = (DateTime) ds.Columns["LastOrderDate"].Value;
}

Notice how its just a simple matter of casting the column values to their field data types.

Conclusion

In this article, I have demonstrated how easy and straight-forward it is to use SQLitePlus for .NET object serialization to SQLite database files. We have learned 3 different ways to go about it, taking advantage of COM InterOp to easily and elegantly use the SQLitePlus COM DLL in a C# program and to convert between data types seamlessly. I can't tell you how much I love using this system to get my coding work done super-fast with such cool database tools as SQLite and SQLitePlus. In my next article, "Encrypted .NET Serialization to SQLite Database", I will show how to use the same methods described here but with encryption added.