Download SQLCEtools_DEMO.zip - 70.34 KB

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

            // 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. I implemented an overload that returns a List of type T in stead 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 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 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. I updated the application on August 8, 2011 after adding a search-button in order to demonstrate searching the datatable.