Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Revisions
 

Easy SQL-CE access utility in C#

, 8 Aug 2011
Rate this:
Please Sign up or sign in to vote.
Access data in any SQL server compact edition datatable using only one line of code in C#
This is an old version of the currently published article.

Introduction:

Writing and reading data to and from a database can be cumbersome. Different database objects need to be instantiated, databases opened and closed and parameters added. Quite some code is needed each time to read, write or update data from different tables. I often need to simply read, write, update or delete some data for tasks like finding addresses in tables or retrieving a not-too-large recordset and my tables are not always related to each other (especially not in SQL CE). For this reason, I created a number of static methods that can access any SQL CE-datatable as long as a helper-class is declared that mimics the looks of the datatable.

Background:

This solution provides abovementioned static methods that enable simple access to data in any table in any SQL CE 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 abovementioned 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 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 abovementioned 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-dll’s 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.

Points of interest:

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;
            }

            // 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.
            public int? TestTableID { get; set; }
            public string Name { get; set; }
            public DateTime Date { get; set; }
        }

Using abovementioned 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. You can choose to implement an overload that returns the List itself. However, remember that at present the methods return -1 when an exception occurs. You will loose that functionality if the method returns a List.

The use of the methods is fairly simple. Create a list of the object of interest, call the method and you're done! In this case one could also pass 'new TestTable()' as a parameter because no property was changed - and thus they are all null - and the WHERE-clause is omitted. Hence, all records are retrieved from the datatable:

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

In the method reflection is used to retrieve the properties from this 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 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
                long dateticks = 0; DateTime dt = new DateTime();
                Type type = propvalue != null ? propvalue.GetType() : null;
                if (propvalue != null && propvalue.GetType() == dt.GetType())
                {
                    dt = propvalue;
                    dateticks = dt.Ticks;
                }
                // DateTime 1800-01-01 equals null (hey, it's better than nothing...)
                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 1800-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.

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. Anyway, 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.

License

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

About the Author

Bart-Jan Verhoeff

Netherlands Netherlands
No Biography provided

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
GeneralMy vote of 5 Pinmembercsharpbd10-Nov-12 10:38 
GeneralRe: My vote of 5 PinmemberBart-Jan Verhoeff11-Nov-12 3:14 
Questionhi Pinmemberpcrun30-Oct-12 4:49 
AnswerRe: hi PinmemberBart-Jan Verhoeff30-Oct-12 11:11 
GeneralMy vote of 5 PinmemberVanzanz26-Oct-12 6:21 
GeneralRe: My vote of 5 PinmemberBart-Jan Verhoeff28-Oct-12 10:06 
QuestionMy vote of 5 PinmemberAlirezaDehqani25-Oct-12 4:38 
AnswerRe: My vote of 5 PinmemberBart-Jan Verhoeff28-Oct-12 10:02 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 5:58 
GeneralRe: My vote of 5 PinmemberB. Verhoeff24-Oct-12 11:47 
GeneralMy vote of 5 PinmemberSteve Maier12-Aug-11 6:37 
GeneralMy vote of 5 PinmemberTech Code Freak10-Aug-11 4:42 
Questiontry also LINQPad Pinmember10der10-Aug-11 2:36 
AnswerRe: try also LINQPad [modified] PinmemberVerhoeff10-Aug-11 8:59 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 8 Aug 2011
Article Copyright 2011 by Bart-Jan Verhoeff
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid