Preface
This article actually is an update of SQLCEtools. The solution was designed to enable easy use of the portable database SQL Compact Edition. A number of issues in the old version needed attention: first, the old version would not work on systems with .NET 3.5 installed, which is the case in my new working environment. Furthermore, the code of SQLCEtools was rather complex and contained a lot of repetition. Therefore I rewrote and refactored the code. I renamed it EasySqlCe because it is supposed to render an easy way to use SQL-CE. Because it is very different from the static SQLCEtools-methods I wrote the present new article for it. The main differences from SQLCEtools:
- Written for .NET 3.5 and higher (instead of .NET 4.0 and higher).
- You need to reference EasySqlCe:
using EasySqlCe. - You need to instantiate an '
AccessPoint', see Demo-project. - The filename, filepath and password are to be set in de respective properties in de instance of
AccessPoint. You have to think about security and/or passwordprotection. - Whether or not the Where-Clause is used with 'LIKE' instead of '=' needs to be set in the property '
UseLikeStatement'. Also specify on which sides the wildcards should be used ('%'). - DateTime needs to be nullable as well: '
DateTime?', this obviates the need for 'DateTimeNull' and a lot of extra code. I don't understand why I used it in the first place, probably because I started programming in C# at that time. - When the template-class is used, a unique identifier property is provided named
UID.
Background
Everybody who has used SQL-CE must have experienced the cumbersome paths that must be followed in order to perform ordinary database tasks. Linq-to-SQL has helped for SQL-databases but is not easy to initiate for SQL-CE. Moreover, for simple tasks that need a database, a simple solution should be available. For this reason, I created this project. Simply declare a class according to a set of rules and you can access your SQL-CE-database without the need to write methods for SQL-statements like INSERT, SELECT, DELETE, etc. You can play around using the DEMO-form.
Using the code
To read data, just create a class that has the same name as the data-table and add properties matching the names and types of the fields in the data-table. Furthermore, instantiate a database object that contains the name, path and password of the database in your form or class that contains the methods that will access the database. 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 SQL-CE-DLLs to your application folder:
- sqlceca35.dll
- sqlcecompact35.dll
- sqlceer35EN.dll
- sqlceme35.dll
- sqlceoledb35.dll
- sqlceqp35.dll
- sqlcese35.dll
That’s it. Don't forget that some sort of security should be included in order to protect the password. You have to play around with the methods to get to know them. The following methods are included:
CreateDataBase: Creates a whole new database if a database with that name does not existCheckTable: Checks if a table exists with the name of the referenced objectCreateTable: Creates a table that matches the class that is referencedInsertSelectCheckIndex: Checks if an index exists for the referenced objectCreateIndex: Creates a new index for a specific property/fieldTableDirectReader: A faster function for bulk-readingUpdateDeleteResetIdentifier: resets the identifier, only if the table is empty
The demo-form demonstrates some of the functions. I didn’t add examples for delete and update because those methods are used the same way. Instructions are also provided in the code. In the demo it is demonstrated how a ‘helper’-class should look like. When the class is derived from EasySqlCe.Template a unique identifier is provided named "UID". (The use of this template is not required. Note that if you don't derive from the template-class remember to declare one property preceded by the attribute "[UniqueIdentifier]"):
private class TestTable : EasySqlCe.Template
{
public TestTable() { }
public TestTable(string name)
{
if (!String.IsNullOrEmpty(name)) this.Name = name;
else this.Name = null;
}
public string Name { get; set; }
public DateTime? Date { get; set; }
public bool? Checked { get; set; }
}
An EasySqlCe.AccessPoint-object must be instantiated. I placed it in the partial class so that every Method can use it. However, one can choose to declare it in every method as well:
private EasySqlCe.AccessPoint DataBase;
In the eventhandlers the methods are demonstrated. This is how the select-statement is performed:
var founddata = DataBase.Select(search);
If you want to read the whole table it would look like this:
var founddata = DataBase.Select(new TestTable());
If you want to search for a specific record, you can use the overloaded contructor:
var founddata = DataBase.Select(new TestTable("mr Bean"));
This is how the insert-statement looks like:
DataBase.Insert(searchItem);
It couldn’t be easier, ins’t it?
How it works
This is how the core of the ‘Select’-method looks like:
if (!AccessPointReady()) return null;
SetPropertyInfosAndUniqueIdentifier(SearchItem);
List<T> dataList = new List<T>();
string selectStatement = ConstructSQLStatementSelect(SearchItem);
SqlCeConnection connection = new SqlCeConnection(ConnectionString());
try
{
if (connection.State == ConnectionState.Closed) connection.Open();
SqlCeCommand command = GetSqlCeCommand(connection, selectStatement);
AddParametersWithValuesFromProperties(SearchItem, command, Suffix.Where);
SqlCeResultSet ResultSet =
command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (ResultSet.HasRows) while (ResultSet.Read())
dataList.Add(FillObjectWithResultSet(new T(), ResultSet));
return dataList;
}
From line to line:
if (!AccessPointReady()) return null;
checks if a filename is provided.
SetPropertyInfosAndUniqueIdentifier(SearchItem);
performs the following:
void SetPropertyInfosAndUniqueIdentifier<T>(T SearchItem) where T : class, new()
{
PropertiesOfT = typeof(T).GetProperties();
this.CurrentUniqueIdentifier = PropertiesOfT.First(p =>
(p.GetCustomAttributes(typeof(UniqueIdentifier), true)).Length > 0);
}
The private property PropertiesOfT is filled with properties of T for later use and the unique identifier is recognized due to its preceding attribute. The unique identifier is stored in a private property.
Then:
string selectStatement = ConstructSQLStatementSelect(SearchItem);
performs the following:
string ConstructSQLStatementSelect<T>(T ObjectOfInterest) where T : class, new()
{
string sqlStatement = "SELECT " +
GetFieldNamesFromProperties(ObjectOfInterest, false) + " FROM " +
typeof(T).Name;
string whereClause = GetWhereClauseFromProperties(ObjectOfInterest);
sqlStatement += whereClause.Length == 0 ? "" : " WHERE " + whereClause;
return sqlStatement;
}
GetFieldNamesFromProperties is an important method that finally results in the following action:
string GetNamesFromProperties
(bool ExcludeUniqueIdentifier,
string Prefix,
Suffix AddToParameterName)
{
string betweenProperties = ", " + Prefix;
StringBuilder nameString = new StringBuilder();
foreach (PropertyInfo propertyOfT in PropertiesOfT)
{
if (!ExcludeUniqueIdentifier ||
(propertyOfT.GetCustomAttributes(typeof(UniqueIdentifier),
true)).Length == 0)
{
if (nameString.Length > 0)
nameString.Append(betweenProperties);
else nameString.Append(Prefix);
nameString.Append(propertyOfT.Name);
if (AddToParameterName != Suffix.None)
nameString.Append(AddToParameterName.ToString());
}
}
return nameString.ToString();
}
What happens is that the name of each property of the object that is referenced is added to a string that is included in the ‘SELECT’-statement. Depending on the arguments, the unique identifier is excluded or not. A prefix is added if necessary (e.g. 1, 2, etc in case field names are used more than once in the SQL-statement).
Another important method creates a ‘WHERE’-clause:
string GetEquationsFromProperties<T>
(T ObjectOfInterest,
Suffix AddToParameterName,
Separator SeparateBy) where T : class, new()
{
string betweenProperties = " " + this.WhereClauseComparer + " @";
StringBuilder equations = new StringBuilder(4096);
foreach (PropertyInfo propertyOfT in PropertiesOfT)
{
object valueOfProperty = propertyOfT.GetValue(ObjectOfInterest, null);
if (valueOfProperty != null &&
(!ExcludeUniqueIdentifier(AddToParameterName) ||
(propertyOfT.GetCustomAttributes(typeof(UniqueIdentifier),
true)).Length == 0))
{
if (equations.Length > 0) equations.Append(Separate(SeparateBy));
equations.Append(propertyOfT.Name);
equations.Append(betweenProperties);
equations.Append(propertyOfT.Name.ToLower());
equations.Append(AddToParameterName.ToString());
}
}
return equations.ToString();
}
The equations that are created are separated either by ‘,’ or by ‘AND’ depending on the arguments
When we continue analysis of the Select-method we enter the try-catch-block and where following method is used:
void AddParametersWithValuesFromProperties<t>
(T ObjectOfInterest,
SqlCeCommand Command,
Suffix AddToParameterName) where T : class, new()
{
bool excludeUniqueIdentifier = ExcludeUniqueIdentifier(AddToParameterName);
foreach (PropertyInfo propertyOfT in PropertiesOfT)
{
if (!excludeUniqueIdentifier || propertyOfT != CurrentUniqueIdentifier)
{
object propertyValue = propertyOfT.GetValue(ObjectOfInterest, null);
string parameterName = "@" + propertyOfT.Name.ToLower()
+ AddToParameterName.ToString().ToLower();
if (propertyValue != null && _UseLikeStatement)
propertyValue = AddWildCard(propertyValue);
if (propertyValue == null) propertyValue = DBNull.Value;
if (!Command.Parameters.Contains(parameterName))
Command.Parameters.Add(parameterName,
GetSqlDbTypeFromDotNetType(propertyOfT.PropertyType));
Command.Parameters[parameterName].Value = propertyValue;
}
}
}
In this method, the SqlCeCommand is further prepared by adding parameters with values – hence the name. Note that wildcards can be added before or after the data when the UseLikeStatement-flag is set to true.
Finally, after execution of the SqlCeCommand a new object is instantiated and filled with data from the SqlCeResultSet:
T FillObjectWithResultSet<T>(T dataItem,
SqlCeResultSet ResultSet) where T : class, new()
{
foreach (PropertyInfo propertyOfT in PropertiesOfT)
{
PropertyInfo singlepropinf = typeof(T).GetProperty(propertyOfT.Name);
int ordinal = ResultSet.GetOrdinal(propertyOfT.Name);
object result = ResultSet.GetValue(ordinal);
if (result is DBNull) singlepropinf.SetValue(dataItem, null, null);
else singlepropinf.SetValue(dataItem, result, null);
}
return dataItem;
}
I’ve tried to use names for methods and properties, etc. that explain their function as much as possible and I’ve also tried to refactor as much as possible so I hope this code is more easy to understand than that of SQLCEtools.
History
The first attempt of the ‘readdata’-method was released by me on ‘stackoverflow.com’ as a question on March 2, 2011. The first release of SQLCEtools on this site was on August 6, 2011. The first version of EasySqlCe was submitted October 24, 2012.
In this update a bug in the 'Update'-function was fixed among other details.
