CEData.cs

CEData.cs

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

namespace 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).
    //  This class does not currently support nesting.  (Ie only one table can be worked with at a time.)
    public static class CEData
    {
        // The command used to affect the data
        private static SqlCeCommand _command;
        // Used to read through the data and find what we are looking for.
        private static SqlCeResultSet _resultSet;
        // 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;

        /// <summary>
        /// Call this before anything else.  Used to setup the connection and get the list of indexes.
        /// </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 void 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.
            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 void 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)
                    {
                        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)
                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 void SetupTable(string tableName, string indexName, bool allowUpdates, params object[] whereValues)
        {
            // Clean up the past command
            if (_resultSet != null)
                _resultSet.Close();

            _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)
                _resultSet = _command.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
            else
                _resultSet = _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="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(object indexedKey, params object[] rowValues)
        {
            MergeRow(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="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(object[] indexedKeys, params object[] rowValues)
        {
            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): " + _command.CommandText, 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): " + _command.CommandText, 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="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(params object[] indexValue)
        {
            if (_resultSet.Seek(DbSeekOptions.FirstEqual, indexValue))
            {
                _resultSet.Read();
                _resultSet.Delete();
            }
        }

        /// <summary>
        /// Used to iterate through the rows selected by the SetupTable Method.
        /// </summary>
        /// <returns>An enumerable that can be iterated.</returns>
        public static IEnumerable<Dictionary<string, object>> GetRowsIter()
        {
            if (_resultSet.HasRows)
            {
                _resultSet.Read();

                do
                {
                    var resultList = new Dictionary<string, object>();
                    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)
        {
            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.
            SqlCeCommand 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;
            }
        }
    }


}