Easy SQL-CE Access Utility in C#

Access data in any SQL server compact edition datatable using only one line of code in C#

New Version

NOTE THIS: For some reason most people continue to download this Sql CE access utility while a better and more versatile new version exists. See this article: EasySqlCe.

Improvements in abovementioned new version: e.g. with one line of code you can now create a whole new database, create a new table, create a new index or check if an index exists that you can use for the tabledirect-method, Moreover, you can choose whether you want to use wildcards ("%") when searching tables. Furthermore, under the hood all code is new and also works on .NET 3.5 while the old version needs .NET 4.0. Check it out!

Old Version

Introduction

I wondered why it is necessary to write so many lines of code to programmatically read, write, update or delete records in a datatable. I'm working with a lot with SQL server compact edition 3.5. Constructing a database helper class each time for every new datatable started to annoy me soon after writing the first one. For this reason, I created a number of methods that can access any SqlCe-datatable without any sql-code or SqlCe-objects. The only necessary investment is a small class that contains properties that have the same name as the fields in the datatable. I created a simple demo to show how (easily) it works.

Background

This solution provides the above mentioned methods that enable simple access to data in any table in any SqlCe-database using an instance of a simple derived class that contains properties that match all fields of the table of interest. Reflection is used to extract the properties of the class. An SQL-statement is then constructed using this information. The database is opened, the recordset is read (or written or updated or deleted) and closed within the scope of the method. All records are converted to objects of the above mentioned helper-class and added to a list (or a list is written or updated or deleted). While surfing on the net, I never came across a solution like this. If it already exists, please let me know. Furthermore, I’ve only started programming in C# last year so I expect to have used some awkward programming here and there. Maybe I've invented the wheel again. Please let me know, also if I need to change some things.

Using the Code

To read data, just declare a class that is named exactly as the data-table is and add properties that are named exactly as the fields in the data-table. Provide a connectionstring (using the provided method) and an emptyList of objects of the above mentioned class and a ‘search’-object of the same class that contains the property (as in field) to search for. The method fills the list with objects that match the resultset and returns an integer that counts the number of retrieved objects or -1 if an error occurred. The other functions are used more or less the same way. Obviously, SQL server compact edition 3.5 must be installed. The database must be added to the solution (simply drag it to the solution-explorer, no need for tableadapters or anything like that). Don’t forget to add a reference to System.Data.SqlServerCe and when you want to deploy the application, you need to copy the sqlce-DLLs to your application folder:

sqlceca35.dll

sqlcecompact35.dll

sqlceer35EN.dll

sqlceme35.dll

sqlceoledb35.dll

sqlceqp35.dll

sqlcese35.dll

You have to play around with the methods to get to know them. The demo-form is as simple as can be. I didn’t add examples for delete and update because those methods are used the same way. Instructions are also provided in the code.

How It Works

A simple helper-class would look like this:

///<summary>/// Declare a helper-class. Note: the name of the class must be exactly
/// the name of the DataTable.
///</summary>privateclass TestTable : SQLCEtools.BaseClass // this inheritance from
// baseclass is actually not really needed at present. I wanted to add
// extra functionality but didn't get that far up till now.
{
///<summary>/// Always add a constructor: DateTime needs to be set to
/// DateTimeNull
///</summary>public TestTable()
{
this.TestTableID = null;
this.Name = null;
this.Date = SQLCEtools.DateTimeNull;
}
///<summary>/// This overload is added to demonstrate searching
///</summary>///<paramname="name">String containing field 'name'</param>public TestTable(string name)
{
this.TestTableID = null;
if (!String.IsNullOrEmpty(name)) this.Name = name;
elsethis.Name = null;
this.Date = SQLCEtools.DateTimeNull;
}
// Other methods can be added to process data or validate.
// All properties need to be nullable because only if they are null
// they are not used as a search-term. Furthermore, the names and
// types of all properties must match the names and types of the
// fields in the datatable. Finally, the unique identifier must have
// the attribute [UniqueIdentifier]
[UniqueIdentifier]
publicint? TestTableID { get; set; }
publicstring Name { get; set; }
public DateTime Date { get; set; }
publicbool? Checked { get; set; }
}

Using the above mentioned helper-class, the information about the datatable is passed to the method by a generic parameter as follows. First, the definition of the base-method:

From this base-method, the methods ReadData(...) and ReadLikeData(...) are derived. ReadData(...) uses a SELECT-statement with a WHERE-clause that compares fields for equality ('=') whereas ReadLikeData(...) uses a WHERE-clause that compares the fields with the 'LIKE' keyword and appends '%' to both sides of the value. The latter happens later in the method. I implemented an overload that returns a List of type T instead of int. In case of an exception, null is returned.

The use of the methods is fairly simple. Just call the method with 'new TestTable()' as a searchparameter and the WHERE-clause is omitted. Hence, all records are retrieved from the datatable:

In the ReadData-method, reflection is used to retrieve the properties from the generic type T:

PropertyInfo[] propinfs = typeof(T).GetProperties();

Furthermore, a SELECT-statement is constructed using the names of the properties. Concurrently a WHERE-clause is constructed using only those properties that are not null (hence the need for nullable properties in the helper-class). Note the use of the dynamic-type. Extra code was needed to perform this action for the DateTime type (null was defined as 1800-01-01):

In the database-phase, reflection is used again to add parameters with value to the SQL-statement. Note that in this part, the difference is made between ReadData(...) and ReadLikeData(...). Also note another use of the dynamic-type:

In the method that inserts the records into the table, an extra SQL-command was performed before moving to the next object in the list. This command ('SELECT @@IDENTITY') yields the identity column of the last record. When this command is executed using ExecuteScalar() the value can be used, in this case to return the new ID of the record to the user.

Declaration Rules at-a-glance

The unique identifier in the datatable must have 'autoincrement' and 'seed' set to '1' in order for the WriteData-method to work properly.

The names of the properties must match the names of the fields in the datatable. The types must match the types of the fields.

The property that represents the unique identifier must have the attribute [UniqueIdentifier] to enable the write- and update-methods to recognize the identifier of the datatable.

The methods will use all properties except those that are null. Hence, the types of all properties must be nullable.

DateTime is an exception to the previous rule. If DateTime is used, a constructor must be declared that equals DateTime to SQLCEtools.DateTimeNull, a static property of the SQLCEtools-class that represents 1753-1-1 (which is the lowest possible date in SQL CE. May be replaced by another date if necessary).

Points of Interest

As expected, the penalty for using reflection every time the recordset is iterated increases with increasing record count. It starts to become significant when the recordset gets larger than about 1000 records. In that case, an optimized tabledirect method is preferred and may yield a performance gain of more than 25%. In my case, this does not happen often. Anyways, always create indexes for the fields you’re searching.

I tested the methods on SQL server compact edition 3.5 only. Obviously, you can use the utility at your own risk.

Finally, it should not be too difficult to port this solution to SQL server or any other database system, although I imagine that when those systems are needed, more sophisticated SQL-statements are needed as well and those are not provided here.

History

The first attempt of the ‘readdata’-method was released by me on ‘stackoverflow.com’ as a question on March 2, 2011. At present, this utility is part of an application that I wrote for use in a professional setting on a daily basis. The first release on this site was on August 6, 2011. The following updates were performed:

August 8, 2011: Adding a search-button to the form in order to demonstrate searching the datatable.

August 12, 2011 bug-fix: Null-values resulted in an SQL-error when either the WriteData- or UpdateData-method was used. I actually never tried to write a null-value before.

September 15, 2011: When an object (or a list of objects) is inserted into the datatable, the method now retrieves the unique identifier and writes it to the respective property of the object that was passed as a parameter. Furthermore, a missing column in the demo-database was added.

September 19, 2011: Before this update, the WriteData- and UpdateData-methods assumed that the first property that was retrieved using reflection was the unique identifier of the datatable. This worked fine. However, the MSDN-documentations states that the order by which the properties are retrieved using reflection is unpredictable. Therefore, to avoid SQL-errors or unpredictable behavior in the future, an attribute was declared ([UniqueIdentifier]). This attribute must now precede the property in the helper class that represents the unique identifier of the datatable (Remember that the datatable must autoincrement and seed). Furthermore, DateTimeNull is now defined as 1753-1-1 (which is the lowest possible date in SQL-CE).