database.cs

微软的行业应用解决方案实例！非常优秀的Windows Mobile开发案例

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using HardwareDistributor.Business;
using System.IO;
using System.Collections;

namespace HardwareDistributor.Data
{
    /// <summary>
    /// Data layer class that interacts with the SQL Server Everywhere
    /// </summary>
    public class Database
    {
        /// <summary>
        /// Replicates database with SQL Server 2005
        /// </summary>
        public static void Synchronize()
        {
            SqlCeReplication repl = null;

            try
            {
                //Instantiate Replication Object
                repl = new SqlCeReplication();

                //Set Internet Properties
                repl.InternetUrl = GlobalCache.Instance.InternetUrl;
                repl.InternetLogin = GlobalCache.Instance.InternetLogin;
                repl.InternetPassword = GlobalCache.Instance.InternetPassword;

                //Set Publisher Properties
                repl.Publisher = GlobalCache.Instance.Publisher;
                repl.PublisherDatabase = GlobalCache.Instance.PublisherDatabase;
                repl.Publication = GlobalCache.Instance.Publication;

                //Set Security Property
                repl.PublisherSecurityMode = SecurityType.NTAuthentication;

                //Set Subscriber Properties
                repl.SubscriberConnectionString = GlobalCache.Instance.ConnectionString;
                repl.Subscriber = GlobalCache.Instance.Subscriber;

                //Set Compression Level
                repl.CompressionLevel = GlobalCache.Instance.CompressionLevel;

                //Check to see if Local Database Exists
                if (!File.Exists(GlobalCache.Instance.AppPath + GlobalCache.Instance.SqlEvDatabase))
                {
                    //If not, have the AddSubscription method create a new database
                    repl.AddSubscription(AddOption.CreateDatabase);
                }

                //Synchronize with SQL Server 2005
                repl.Synchronize();
            }
            finally
            {
                //Displose of Replication Object
                repl.Dispose();
            }
        }


        /// <summary>
        /// Creates a database connection and adds it to the global cache
        /// </summary>
        public static void AddConnectionToCache()
        {
            //Test to see of Connection is already cached
            if (Business.GlobalCache.Instance.Connection == null)
            {
                //Create and Open a connection to the database
                SqlCeConnection _cn = new SqlCeConnection(Business.GlobalCache.Instance.ConnectionString);
                _cn.Open();

                //Add the open connection to cache
                Business.GlobalCache.Instance.Connection = _cn;
            }
        }


        /// <summary>
        /// Retrieves data from database by filling object arrays
        /// with rows of data and adding those objects to an ArrayList
        /// </summary>
        /// <param name="command"></param>
        /// <param name="rowLimit"></param>
        /// <returns></returns>
        public static ArrayList ReadValues(SqlCeCommand command, int rowLimit)
        {
            SqlCeDataReader _dr = null;
            object[] _values;
            int rowCount = 0;

            try
            {
                //Create ArrayList to fill with data
                ArrayList _list = new ArrayList();

                //Set command's connection property equal to cached connection
                command.Connection = GlobalCache.Instance.Connection;

                //Return DataReader from Command
                _dr = command.ExecuteReader();

                //Ensure DataReader isn't null
                if (_dr != null)
                {
                    //Loop through DataReader
                    while (_dr.Read())
                    {
                        //Increment row counter
                        rowCount++;

                        //Set length of Object array to the number of columns returned
                        _values = new object[_dr.FieldCount];

                        //Populate Object array with entire row of data
                        _dr.GetValues(_values);

                        //Add contents of Object array to ArrayList
                        _list.Add(_values);

                        //If row count equals row limit, break out of loop
                        if (rowCount == rowLimit) break;
                    }
                }
                //Return Arraylist Business Layer
                return _list;
            }
            finally
            {
                //Dispose of DataReader Object
                _dr.Close();
            }
        }


        /// <summary>
        /// Retrieves a single value from the database
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static object ReadSingleValue(SqlCeCommand command)
        {
            //Set command's connection property equal to cached connection
            command.Connection = GlobalCache.Instance.Connection;
            //Return Object from Command
            return command.ExecuteScalar();
        }


        /// <summary>
        /// Performs an insert, update or delete against the database
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static int InsertUpdateDelete(SqlCeCommand command)
        {
            //Set command's connection property equal to cached connection
            command.Connection = GlobalCache.Instance.Connection;
            //Return number of rows affected by operation
            return command.ExecuteNonQuery();
        }


    }
}