Easy SQL-CE Access Utility 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

• Download demo - 59.89 KB

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 empty List 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>        
private class 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>
    /// <param name="name">String containing field 'name'</param>
    public TestTable(string name)
    {
        this.TestTableID = null;
        if (!String.IsNullOrEmpty(name)) this.Name = name;
        else this.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]
    public int? TestTableID { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public bool? 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:

private static int BaseRead<T>(List<T> data, string table, 
	T search, string connect, string comparer) where T : BaseClass, new()

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:

List<TestTable> alldata = SQLCEtools.ReadData(new TestTable(), Connection());

One could also use the overloaded constructor to search for a name directly. Furthermore, List<TestTable> can be replaced by var:

var alldata = SQLCEtools.ReadData(new TestTable("some_name"), Connection());

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):

foreach (PropertyInfo p in propinfs)
{
    fields += fields == "" ? p.Name : ", " + p.Name;
    dynamic propvalue = p.GetValue(search, null);
    // Solutions for properties of type DateTime
    DateTime dt = new DateTime();
    Type type = propvalue != null ? propvalue.GetType() : null;
    if (propvalue != null && propvalue.GetType() == dt.GetType()) dt = propvalue;
    // DateTime 1753-01-01 equals null
    if (propvalue != null && dt != DateTimeNull)
        wherestr += wherestr == "" ? p.Name + " " + comparer + " @" + p.Name.ToLower() 
            : " AND " + p.Name + " " + comparer + " @" + p.Name.ToLower();
}
// Create SQL SELECT statement with properties and search
string sql = "SELECT " + fields + " FROM " + table;
sql += wherestr == "" ? "" : " WHERE " + wherestr;

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:

SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// Add propertyvalues to WHERE-statement using reflection
foreach (PropertyInfo p in propinfs)
{
    dynamic propvalue = p.GetValue(search, null);
    // Except for DateTime values 1753-01-01 (defined as null)
    if (propvalue != null && !(propvalue.GetType() is DateTime && 
				propvalue != DateTimeNull))
    {
        if (comparer == "LIKE") propvalue = "%" + propvalue + "%";
        cmd.Parameters.AddWithValue("@" + p.Name.ToLower(), propvalue);
    }
}
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

Finally, the records in the resultset are converted to the generic objects using reflection again, and added to the list that was passed as a parameter. Again, note the use of the dynamic-type:

var dataitem = new T();  // Object to put the field-values in
foreach (PropertyInfo p in propinfs)
{
    // Read database fields using reflection
    PropertyInfo singlepropinf = typeof(T).GetProperty(p.Name);
    int ordinal = rs.GetOrdinal(p.Name);
    dynamic result = rs.GetValue(ordinal);
    // Conversion to null in case field is DBNull
    if (result is DBNull)
    {
        if (singlepropinf.PropertyType.Equals(typeof(DateTime)))
        {
            // Fill data item with datetimenull
            singlepropinf.SetValue(dataitem, DateTimeNull, null); 
        }
        else
        {
            // Fill data item with null
            singlepropinf.SetValue(dataitem, null, null); 
        }
    }
    else
    {
        // Or fill data item with value
        singlepropinf.SetValue(dataitem, result, null); 
    }
}
data.Add(dataitem);  // And add the record to List<T> data.

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.

// now create new command to get identity
cmd.CommandText = "SELECT @@IDENTITY";
// Get new identifier value, convert to int32
int propID = Convert.ToInt32((decimal)cmd.ExecuteScalar());
// And put identifier value in ID property for later reference
ID.SetValue(dat, propID, null); 
// change commandtext back to original text
cmd.CommandText = sql;

Declaration Rules at-a-glance

Declare a class in your application according to the following rules:

• The name of the class must match the name of the datatable.
• 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).