sqldatastore.cs

微软的行业应用解决方案示例

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.IO;
using System.Text;
using System.Reflection;
using System.Resources;


namespace Microsoft.Mobile.Data
{
    /// <summary>
    /// A base implmenetation for an RDBMS of
    /// <see cref="Microsoft.Mobile.Data.DataStore"/>. It uses ADO.Net and adds 
    /// triggers and stored procedure like facilities to light weight SQL databases.
    /// </summary>
    public abstract class SqlDataStore : DataStore
    {
        #region Constants
        /// <summary>
        /// Constant for the name of the parameter to set the maximum number of 
        /// commands to cache.
        /// </summary>
        public const string MaxCommands = "MaxCommands";
        #endregion


        #region Fields
        private DbConnection _connection;
        private int _maxCommands = 12;
        private Commands _mostUsedCommands = new Commands();
        private DbTransaction _transaction = null;
        #endregion


        #region Constructor(s) & Dispose
        /// <summary>
        /// Open a RDMS type data store
        /// </summary>
        /// <param name="connectionString">
        /// string containing connection information
        /// </param>
        protected SqlDataStore(string connectionString)
            : this(connectionString, null)
        {
        }


        /// <summary>
        /// Optional DataStore construction signature which allows for 
        /// implementation specific parameters to be passed to a specific
        /// implementation.
        /// </summary>
        /// <param name="connectionString">
        /// string containing connection information
        /// </param>
        /// <param name="implementationParameters">
        /// implementation specific additional parameters
        /// </param>
        [SuppressMessage("Microsoft.Usage", 
                         "CA2214:DoNotCallOverridableMethodsInConstructors",
                         Justification="The virtual call to CreateConnecion is deliberate. It makes building Sql providers very easy by only having to implement two methods, CreateConnection and CreateParameter.")]
        protected SqlDataStore(string connectionString, Parameters implementationParameters)
            : base(connectionString, implementationParameters)
        {
            // Extract any implementation parameters
            // Check for MaxCommands which controls how many commands to cache
            if ((ImplementationParameters != null) &&
                (ImplementationParameters.ContainsKey(MaxCommands)))
            {
                _maxCommands = Convert.ToInt32(ImplementationParameters[MaxCommands],
                                               CultureInfo.InvariantCulture);
            }
            
            _connection = CreateConnection(connectionString);
            _connection.Open();
            _mostUsedCommands.MaxItems = _maxCommands;
        }


        /// <summary>
        /// Close the Sql Server CE Database connection and dispose of it.
        /// </summary>
        protected override void  Dispose(bool disposing)
        {
            foreach (CommandItem currentCommand in _mostUsedCommands.Values)
            {
                currentCommand.Command.Dispose();
            }
            _connection.Close();
            _connection.Dispose();
        }
        #endregion


        #region Methods
        /// <summary>
        /// Start a transaction
        /// </summary>
        public override void BeginTransaction()
        {
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            if (_transaction == null)
            {
                _transaction = _connection.BeginTransaction();
            }
        }

        /// <summary>
        /// Create a command from a string. The string can be SQL, StoredProc 
        /// or TableName.
        /// </summary>
        /// <param name="sql">
        /// string containing SQL, name of a stored procedure file or table 
        /// name for a table direct access
        /// </param>
        /// <param name="parameters">
        /// Parameters for the SQL string
        /// </param>
        /// <returns>Command to built from the string</returns>
        protected DbCommand BuildCommand(string sql, Parameters parameters)
        {
            return BuildCommand(sql, parameters, false);
        }


        /// <summary>
        /// Create a command from a string. The string can be SQL, StoredProc 
        /// or TableName.
        /// </summary>
        /// <param name="sql">
        /// string containing SQL, name of a stored procedure file or table 
        /// name for a table direct access
        /// </param>
        /// <param name="parameters">paraemters for the sql</param>
        /// <param name="storedProc">
        /// if stored procedure building a command then true. This is so a 
        /// command item is not created.
        /// </param>
        /// <returns>Command to built from the string</returns>
        protected DbCommand BuildCommand(string sql, 
                                         Parameters parameters, 
                                         bool storedProc)
        {
            DbCommand result;

            //ensure that we are working with an open connection
            if (_connection.State == ConnectionState.Closed)
                _connection.Open();

            // Test that something was past into the method
            if (string.IsNullOrEmpty(sql) ||
                string.IsNullOrEmpty(sql.Trim()))
            {
                throw new ArgumentException("String is empty so a command cannot be created.",
                                            "sql");
            }


            if (_mostUsedCommands.ContainsKey(sql))
            {
                result = _mostUsedCommands[sql].Command;
                if ((parameters != null) && (parameters.Count > 0))
                {
                    foreach (KeyValuePair<string, object> currentParameter in parameters)
                    {
                        result.Parameters[currentParameter.Key].Value = currentParameter.Value;
                    }
                }
            }
            else
            {
                // Check for if command type
                if (sql.StartsWith("Table:", StringComparison.OrdinalIgnoreCase))
                {


                    // build table direct command
                    result = _connection.CreateCommand();
                    result.CommandText = sql.Replace("Table:", string.Empty);
                    result.CommandType = CommandType.TableDirect;
                }
                else
                {
                    // Assume the string is SQL
                    result = _connection.CreateCommand();
                    result.CommandText = sql;
                }
                // Add parameters if any
                if ((parameters != null) && (parameters.Count > 0))
                {
                    foreach (KeyValuePair<string, object> currentParameter in parameters)
                    {
                        result.Parameters.Add(CreateParameter(currentParameter.Key, 
                                                              currentParameter.Value));
                    }
                }

                // Used to prevent SQL from the stored procedure from being put 
                // into the command stack.
                if (!storedProc)
                {
                    result.Prepare();
                    CommandItem commandItem = new CommandItem();
                    commandItem.Command = result;
                    commandItem.Count = 0;
                    _mostUsedCommands.Add(sql, commandItem);
                }
            }

            // update command item count
            if (!storedProc)
            {
                _mostUsedCommands[sql].UsedCommand();
            }

            return result;
        }


        /// <summary>
        /// Close connection. When used the connection will reopen.
        /// </summary>
        public override void Close()
        {
            _connection.Close();
        }


        /// <summary>
        /// Commit transaction.
        /// </summary>
        public override void Commit()
        {
            if (_transaction == null)
            {
                throw new Exception("No transaction is open to commit.");
            }
            else
            {
                _transaction.Commit();
                _transaction.Dispose();
                _transaction = null;
            }
        }

        /// <summary>
        /// Used to create a specific connection implementation
        /// </summary>
        /// <param name="connectionString">
        /// string containing connection information
        /// </param>
        /// <returns>database connection</returns>
        protected abstract DbConnection CreateConnection(string connectionString);


        /// <summary>
        /// Used to create a specific parameter implementation
        /// </summary>
        /// <param name="name">name of the parameter</param>
        /// <param name="value">value of the parameter</param>
        /// <returns>database parameter</returns>
        protected abstract DbParameter CreateParameter(string name, object value);


        /// <summary>
        /// Method used to fire the trigger event.
        /// </summary>
        /// <param name="command">command that is triggering the event</param>
        protected void OnTrigger(DbCommand command)
        {
            TriggerType action;
            string tableName;
            string sqlString;
            string sqlCommand;
            string[] sqlTokens;

            // Only do the parsing if subscribers exisst
            if (HasTriggerSubscribers)
            {
                // create event argument
                sqlString = command.CommandText.Trim();
                sqlTokens = sqlString.Split(' ');

                // find type of command and table
                sqlCommand = sqlTokens[0].Trim().ToUpper(CultureInfo.InvariantCulture);
                switch (sqlCommand)
                {
                    case "DELETE":
                        action = TriggerType.Delete;
                        tableName = sqlTokens[2];
                        break;
                    case "INSERT":
                        action = TriggerType.Insert;
                        tableName = sqlTokens[2];
                        break;
                    default: // update
                        action = TriggerType.Update;
                        tableName = sqlTokens[1];
                        break;
                }
                base.OnTrigger(action, tableName);
            }
        }


        /// <summary>
        /// Use SQL to return an enumerator of rows. This is then used to loop 
        /// through the SqlCeResultSet.
        /// </summary>
        /// <param name="selector">SQL Select</param>
        /// <returns>
        /// An implementation of 
        /// <see cref="Microsoft.Mobile.Data.RowEnumerator"/>
        /// </returns>
        public override RowEnumerator Read(string selector)
        {
            return Read(selector, null);
        }


        /// <summary>
        /// Get an enumerator to return a collection of named values.
        /// </summary>
        /// <param name="selector">
        /// string to direct the method on what to retrieve
        /// </param>
        /// <param name="parameters">
        /// collections of parameters for the selector
        /// </param>
        /// <returns>enumerator of data in a row column heirarchy</returns>
        public override RowEnumerator Read(string selector, 
                                           Parameters parameters)
        {
            return new EnumerateDataReader(BuildCommand(selector, parameters).ExecuteReader());
        }


        /// <summary>
        /// Get a single value from the data store.
        /// </summary>
        /// <param name="selector">
        /// string to direct the method on what to retrieve
        /// </param>
        /// <returns>
        /// The first value that matches the selection criteria
        /// </returns>
        public override object GetValue(string selector)
        {
            return GetValue(selector, null);
        }


        /// <summary>
        /// Get a single value from the data store.
        /// </summary>
        /// <param name="selector">
        /// string to direct the method on what to retrieve
        /// </param>
        /// <param name="parameters">
        /// collections of parameters for the selector
        /// </param>
        /// <returns>
        /// The first value that matches the selection criteria
        /// </returns>
        public override object GetValue(string selector, Parameters parameters)
        {
            DbCommand command;
            object result;

            // Build the command
            command = BuildCommand(selector, parameters);

            // Execute command
            result = command.ExecuteScalar();

            return result;
        }


        /// <summary>
        /// Rollback transaction.
        /// </summary>
        public override void  Rollback()
        {
            if (_transaction == null)
            {
                throw new Exception("No transaction is open to rollback.");
            }
            else
            {
                _transaction.Rollback();
                _transaction.Dispose();
                _transaction = null;
            }
        }


        /// <summary>
        /// Method for insert, updating and deleting from a data store.
        /// </summary>
        /// <param name="update">
        /// string to direct the method on to update the date store.
        /// </param>
        public override void Save(string update)
        {
            Save(update, null);
        }


        /// <summary>
        /// Method for insert, updating and deleting from a data store.
        /// </summary>
        /// <param name="update">
        /// string to direct the method on to update the date store.
        /// </param>
        /// <param name="parameters">
        /// collections of parameters for the selector
        /// </param>
        public override void Save(string update, Parameters parameters)
        {
            DbCommand command;

            // Execute Command
            command = BuildCommand(update, parameters);
            command.ExecuteNonQuery();
            OnTrigger(command);
        }
        #endregion
    }
}