Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server

EasySqlCe: easy access to SQL CE in C#

Rate me:
Please Sign up or sign in to vote.
4.81/5 (9 votes)
23 Jan 2014GPL36 min read 41.6K   1.5K   31   11
An easy way to access your SQL compact edition database in C#

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.

Image 1 

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 exist
  • CheckTable: Checks if a table exists with the name of the referenced object
  • CreateTable: Creates a table that matches the class that is referenced
  • Insert
  • Select
  • CheckIndex: Checks if an index exists for the referenced object
  • CreateIndex: Creates a new index for a specific property/field
  • TableDirectReader: A faster function for bulk-reading
  • Update
  • Delete
  • ResetIdentifier: 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]"):    

C#
/// <summary>
/// Declare a helper-class. Note: the name of the class must be exactly 
/// the name of the DataTable.
/// </summary>
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: 

C#
private EasySqlCe.AccessPoint DataBase; 

In the eventhandlers the methods are demonstrated. This is how the select-statement is performed: 

C#
var founddata = DataBase.Select(search); 

If you want to read the whole table it would look like this: 

C#
var founddata = DataBase.Select(new TestTable()); 

If you want to search for a specific record, you can use the overloaded contructor: 

C#
var founddata = DataBase.Select(new TestTable("mr Bean")); 

This is how the insert-statement looks like: 

C#
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: 

C#
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: 

C#
if (!AccessPointReady()) return null; 

checks if a filename is provided. 

C#
SetPropertyInfosAndUniqueIdentifier(SearchItem); 

performs the following: 

C#
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: 

C#
string selectStatement = ConstructSQLStatementSelect(SearchItem); 

performs the following: 

C#
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: 

C#
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: 

C#
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: 

C#
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

C#
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.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Netherlands Netherlands
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to select using a numeric range? Pin
Member 991964826-Dec-15 20:42
Member 991964826-Dec-15 20:42 
AnswerRe: How to select using a numeric range? Pin
Bart-Jan Verhoeff28-Jan-16 11:47
Bart-Jan Verhoeff28-Jan-16 11:47 
QuestionThis does not work with Compact Framework Pin
Jaime_Des2-Aug-14 19:11
Jaime_Des2-Aug-14 19:11 
AnswerRe: This does not work with Compact Framework Pin
Bart-Jan Verhoeff27-Aug-14 9:39
Bart-Jan Verhoeff27-Aug-14 9:39 
QuestionCould you cut and paste as sample of update and delete? Pin
David Fenstemaker31-Jan-14 9:15
David Fenstemaker31-Jan-14 9:15 
AnswerRe: Could you cut and paste as sample of update and delete? Pin
Bart-Jan Verhoeff2-Feb-14 10:52
Bart-Jan Verhoeff2-Feb-14 10:52 
GeneralVery good Pin
Paulo Augusto Kunzel21-Oct-13 2:09
professionalPaulo Augusto Kunzel21-Oct-13 2:09 
GeneralMy vote of 5 Pin
Kanasz Robert27-Oct-12 4:10
professionalKanasz Robert27-Oct-12 4:10 
GeneralRe: My vote of 5 Pin
Bart-Jan Verhoeff27-Oct-12 4:14
Bart-Jan Verhoeff27-Oct-12 4:14 
QuestionImage and download are missing Pin
wvd_vegt25-Oct-12 0:55
professionalwvd_vegt25-Oct-12 0:55 
Hi,

fyi, the first image and the download is missing.
wvd_vegt

AnswerRe: Image and download are missing Pin
Bart-Jan Verhoeff25-Oct-12 1:15
Bart-Jan Verhoeff25-Oct-12 1:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.