CEData.cs

CEData.cs

using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using System.Windows.Forms;
using NLog;

namespace ARUP.ScanTrack.Mobile
{

    // How to use this class:
    //  First call SetupConnection.  Then call SetupTable (on the table you want to work with)
    //  Then call MergeRow or any other data like function.  To move on to a new table, you can just
    //  SetUpTable again (don't call SetupConnection again unless you are switching connections).
    public static class CEData
    {
        // This is used to know the index to search on (the primary key)
        private static List<Index> _indexes;
        // The connection to the database.
        private static SqlCeConnection _connection;
        // Lock to prevent concurrent writes
        private static readonly object Database = new object();


        /// <summary>
        /// Call this before anything else.  Used to setup the connection and get the list of indexes.
        /// More than one connection is NOT Supported.  Do not make a new connection unless you are sure that all
        /// threads are done using the previous connections.
        /// </summary>
        /// <param name="connection">Valid connection to the database</param>
        public static void SetupConnection(SqlCeConnection connection)
        {
            _connection = connection;
            _indexes = GetIndexes(connection);
        }


        /// <summary>
        /// Call this second.  It is used to focus on the table you want to affect.  This version uses the primary key of the
        /// Table as the index.
        /// </summary>
        /// <param name="tableName">Name of the table to perform data actions on</param>
        /// <param name="whereValues">Optional: Used to limit the scope of values returned when iterating (selecting).</param>
        public static SqlCeResultSet SetupTable(string tableName, params object[] whereValues)
        {
            string pkIndex;
            try
            {
                // Find the primary we are looking for
                pkIndex = _indexes.Find(index => (index.IsPrimaryKey) && (index.TableName == tableName)).IndexName;
            }
            catch (Exception)
            {
                Logger.Log.Error("Cannot find table " + tableName + " with a valid index.");
                throw;
            }

            // Pass it as the index to use.
            return SetupTable(tableName, pkIndex, true, whereValues);
        }

        /// <summary>
        /// Call this second.  It is used to focus on the table you want to affect.
        /// This version will find an index based on the column name passed in.
        /// </summary>
        /// <param name="tableName">Table to get ready to work with</param>
        /// <param name="columnNames">Column(s) to key off of.  Column(s) must have an index.</param>
        /// <param name="whereValues">Values to restrict the result set by.  Can be null or empty</param>
        public static SqlCeResultSet SetupTable(string tableName, string[] columnNames, params object[] whereValues)
        {
            Index resultIndex = null;
            try
            {
                foreach (Index index in _indexes.FindAll(index => index.TableName == tableName))
                {
                    bool columnsMatch = true;
                    foreach (string columnName in columnNames)
                    {
                        // Once we go to false then we are done (we want to make
                        // sure that all our columns are in the index).
                        if (columnsMatch)
                        {
                            columnsMatch = index.Columns.Contains(columnName);
                        }
                    }

                    if (columnsMatch)
                    {
                        int indexColumnCount = index.Columns.Count;
                        int passedInColumnCount = columnNames.Length;

                        // We need the columns to match up exactly to use the index (or else it does not work)
                        if (indexColumnCount == passedInColumnCount)
                        {
                            resultIndex = index;
                            // We prefer a primary key. If this is the primary key then we are done.
                            // otherwise we will use the last one.
                            if (resultIndex.IsPrimaryKey)
                                break; 
                        }
                    }
                }
            }
            catch (Exception)
            {
                Logger.Log.Error("Cannot find index on column(s) " + columnNames + " and table " + tableName + ".");
                throw;
            }

            if (resultIndex != null)
                return SetupTable(tableName, resultIndex.IndexName, true, whereValues);
            else
            {
                Logger.Log.Error("Cannot find index on column(s) " + columnNames + " and table " + tableName + ".");
                throw new IndexOutOfRangeException("Cannot find index on column(s) " + columnNames + " and table " + tableName + ".");
            }
        }


    /// <summary>
    /// Call this second.  It is used to focus on the table you want to affect.
    /// </summary>
    /// <param name="tableName">Name of the table you want to work with</param>
    /// <param name="indexName">Name of the index to use when performing seeks and searches</param>
    /// <param name="allowUpdates">Controls if the result set is updateable.  Usually should be true.</param>
    /// <param name="whereValues">Value used (with the index) to perform a restriction of possible rows.</param>
    public static SqlCeResultSet SetupTable(string tableName, string indexName, bool allowUpdates, params object[] whereValues)
    {
        // The command used to affect the data
        var command = new SqlCeCommand
                          {

                              CommandType = CommandType.TableDirect,
                              Connection = _connection,
                              // Set the table that we are going to be working with.
                              CommandText = tableName,
                              // Indicate what index we are going to be using.  
                              IndexName = indexName
                          };

        if ((whereValues != null) && (whereValues.Length > 0))
            command.SetRange(DbRangeOptions.Match, whereValues, null);

        // Get the table ready to work with.
        if (allowUpdates)
            return command.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
        else
            return command.ExecuteResultSet(ResultSetOptions.Scrollable);
    }

        /// <summary>
        /// Used to merge a row with the database.  If the row is not there it is inserted.
        /// If it is there then it is updated.
        /// </summary>
        /// <param name="resultSet">The SqlCeResultSet returned by SetupTable</param>
        /// <param name="indexedKey">Key to search on.  If found then the row is updated.</param>
        /// <param name="rowValues">Values to insert/update to the database</param>
        public static void MergeRow(this SqlCeResultSet resultSet, object indexedKey, params object[] rowValues)
        {
            MergeRow(resultSet, new[] { indexedKey }, rowValues);
        }

        // 
        /// <summary>
        /// Used to merge a row with the database.  If the row is not there it is inserted.
        /// If it is there then it is updated.
        /// </summary>
        /// <param name="resultSet">The SqlCeResultSet returned by SetupTable</param>
        /// <param name="indexedKeys">Keys to search on.  If found then the row is updated.</param>
        /// <param name="rowValues">Values to insert/update to the database</param>
        public static void MergeRow(this SqlCeResultSet resultSet, object[] indexedKeys, params object[] rowValues)
        {
            // Make sure we don't multi thread the database.
            lock (Database)
            {
                try
                {

                    // Find the row we are looking for.
                    if (resultSet.Seek(DbSeekOptions.FirstEqual, indexedKeys))
                    {
                        // row exists, need to update it.  Load the row in with a Read()
                        resultSet.Read();

                        try
                        {
                            // set values for or table.  They must match exactly.
                            for (int i = 0; i < rowValues.Length; i++)
                            {
                                resultSet.SetValue(i, rowValues[i] ?? DBNull.Value);
                            }
                        }
                        catch (Exception e)
                        {
                            Logger.Log.LogException(LogLevel.Error, "Error writing to DB.  " +
                                                                    "Params do not match columns for the table.  " +
                                                                    "If param count is correct try casting to get a better match " +
                                                                    "(ie cast to an Int16 rather than just using an int)",
                                                    e);
                            throw;
                        }

                        // Update the row that has been changed.
                        resultSet.Update();
                    }
                    else
                    {
                        // row doesn't exist, insert
                        var record = resultSet.CreateRecord();

                        try
                        {
                            // set values for the table.  They must match exactly.
                            for (int i = 0; i < rowValues.Length; i++)
                            {
                                record.SetValue(i, rowValues[i] ?? DBNull.Value);
                            }

                        }
                        catch (Exception e)
                        {
                            Logger.Log.LogException(LogLevel.Error, "Error writing to DB.  " +
                                                                    "Params do not match columns for the table.  " +
                                                                    "If param count is correct try casting to get a better match " +
                                                                    "(ie cast to an Int16 rather than just using an int)",
                                                    e);
                            throw;
                        }

                        // Insert the new row
                        resultSet.Insert(record);
                    }

                }
                catch (Exception e)
                {
                    Logger.Log.ErrorException("Error Updating Database", e);
                    MessageBox.Show("Cannot Update Database");
                    throw;
                }
            }

        }

        /// <summary>
        /// Delete a row based on the passed in index value.
        /// </summary>
        /// <param name="resultSet">The SqlCeResultSet returned by SetupTable</param>
        /// <param name="indexValue">Value to key off of.  Should be a value based on the 
        /// index selected by the Setup Table routine. That index is the Primary Key by default.</param>
        public static void DeleteRow(this SqlCeResultSet resultSet, params object[] indexValue)
        {
            // Make sure we don't multi thread the database.
            lock (Database)
            {
                if (resultSet.Seek(DbSeekOptions.FirstEqual, indexValue))
                {
                    resultSet.Read();
                    resultSet.Delete();
                }
            }
        }

        /// <summary>
        /// Used to iterate through the rows selected by the SetupTable Method.
        /// </summary>
        /// <param name="resultSet">The SqlCeResultSet returned by SetupTable</param>
        /// <returns>An enumerable that can be iterated.</returns>
        public static IEnumerable<Dictionary<string, object>> GetRowsIter(this SqlCeResultSet resultSet)
        {
            if (resultSet.HasRows)
            {
                resultSet.Read();

                do
                {
                    var resultList = new Dictionary<string, object>();
                    // Make sure we don't multi thread the database.
                    lock (Database)
                    {
                        for (int i = 0; i < resultSet.FieldCount; i++)
                        {
                            var value = resultSet.GetValue(i);
                            resultList.Add(resultSet.GetName(i), value == DBNull.Value ? null : value);
                        }
                    }
                    yield return resultList;
                } while (resultSet.Read());
            }
            yield break;
        }


        // Get a list of all the indexes for this database.
        private static List<Index> GetIndexes(SqlCeConnection connection)
        {
            // Make sure we don't multi thread the database.
            lock (Database)
            {
                const string query = "SELECT [TABLE_NAME], [COLUMN_NAME], [INDEX_NAME], " +
                                     "       [PRIMARY_KEY], [UNIQUE], [ORDINAL_POSITION] " +
                                     "FROM   INFORMATION_SCHEMA.INDEXES " +
                                     "ORDER BY INDEX_NAME, ORDINAL_POSITION";

                // Command to call the query.
                var command = new SqlCeCommand
                                  {
                                      CommandType = CommandType.Text,
                                      Connection = connection,
                                      CommandText = query
                                  };

                // Mapping of tables to indexes
                var indexMap = new List<Index>();
                var result = command.ExecuteResultSet(ResultSetOptions.Scrollable);

                // Iterate through the indexes and save them off.
                Index index = null;
                while (result.Read())
                {
                    if (result.GetSqlInt32(5) == 1)
                        index = new Index();

                    if (index != null)
                    {
                        index.TableName = result.GetString(0);
                        index.Columns.Add(result.GetString(1));
                        index.IndexName = result.GetString(2);
                        index.IsPrimaryKey = result.GetBoolean(3);
                        index.IsUnique = result.GetBoolean(4);

                        indexMap.Add(index);
                    }
                }
                return indexMap;
            }
        }

        private class Index
        {
            public string IndexName { get; set; }
            public string TableName { get; set; }
            public List<string> Columns { get; set; }
            public bool IsPrimaryKey { get; set; }
            public bool IsUnique { get; set; }
            public Index()
            {
                Columns = new List<string>();
            }

            public override string ToString()
            {
                return TableName + " " + IndexName;
            }
        }
    }


}