同步客户端数据库(SSCE)跟服务器端数据库

(本篇为SqlServer给SSCE中导入数据)

 

//The Function Can Synchronous any Sever Database with Interface of System.Data.IDbConnection
//This Procedures can be used in Embed Platform of wince becouse
//its resource can be supported by .NET Compact Framework
//This Class is in different Project of Wince Platform and can be used by the Windows Application
using System;
using System.Data ;
using System.Data .SqlServerCe ;
namespace EmbedDB
{
    public class SyncServer
    {
        /// <summary>
        /// Copy DataBase Data from any Server Edition to Client of Sql Server Compact Edition
        /// </summary>
        /// <param name="srcConnection">connection object of Source Database</param>
        /// <param name="destConnection">connection object of Target Database</param>
        /// <param name="queryString">inquire clause of Source Database</param>
        /// <param name="destTableName">Table Name of Target Database</param>
        /// <remarks >Suppose the target Table of SSCE Database is Exist</remarks>
        public static void CopyTable(
            IDbConnection srcConnection,
            SqlCeConnection destConnection,
            string queryString,
            string destTableName)
        {
            IDbCommand srcCmd = srcConnection.CreateCommand();
            srcCmd.CommandText = queryString;

            SqlCeCommand destCmd = destConnection.CreateCommand();
            //SSCE provide the visit pattern of table，with the parameter of seek inquirying is more efficiency
            //than the pattern of  where.
            destCmd.CommandType = CommandType.TableDirect;
            destCmd.CommandText = destTableName;

            try
            {
                IDataReader srcReader = srcCmd.ExecuteReader();

                SqlCeResultSet resultSet = destCmd.ExecuteResultSet(
                    ResultSetOptions.Sensitive |         //The ResultSet detects changes made to the data source
                    ResultSetOptions.Scrollable |       //The ResultSet can be Scrolled both forward and backward
                    ResultSetOptions.Updatable);      //The ResultSet allows Updates

                object [] values;
                SqlCeUpdatableRecord record;
                while (srcReader.Read())
                {
                    //Read Items From Resource DataSet
                    values = new object [srcReader.FieldCount];
                    srcReader.GetValues(values);

                    //Insert Items Through RecordSet
                    record = resultSet.CreateRecord();
                    //the fields of the DataBase Table in Server Must Keep compatible with The Client Table
                    record.SetValues(values);
                    resultSet.Insert(record);
                }
                srcReader.Close();
                resultSet.Close();
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
            }
        }
    }
}

//The Master of Test on PC,IF Running on Wince or other embed system
// need to Import System.Data.SqlClient.dll and alter the connective style with server
// To Get System.Data.SqlClient.dll:
//(VS2008)Upload a sql.ppc.wce4.arcv4.CAB to WinCE Emulator from
//D:/Program Files/Microsoft Visual Studio 9.0/SmartDevices/SDK/SQL Server/Client/v2.0/wce400/armv4,
//and double clicking will Create a folder under windows,enter the folder will find two files,its name
//are dbnetlib.dll and system.data.sqlclient.dll.The second are the needed.
//(OR)find System.Data.SqlClient.dll from
//C:/Program Files/Microsoft SQL Server Compact Edition/v3.5/Devices/Client
//(OR VS2005) can find it from
//D:/Program Files/Microsoft Visual Studio 8.0/SmartDevices/SDK/SQL Server/Client/v2.0,
//This Procedures Only Run on the Windows for the namespace of
//System.Data.SqlClient living on the windows only
using System;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using EmbedDB;

namespace TestWindows
{
    class Program
    {
        static void Main(string[] args)
        {
            TestLink();
        }

        /// <summary>
        /// Test The Function of EmbedDB.SyncServer.CopyTable(...)
        /// </summary>
       public static void TestLink()
        {
            string srcConnstring = "Data Source=.;Initial CataLog=ManufactureManage;Integrated Security=True";
            //string srcConnstring = "Data Source=123-suoxd;Initial Catalog=ManufactureManage;User Id=sa;Password=suoxd123;"
            SqlConnection srcConnection = new SqlConnection(srcConnstring);

            EmbedDB.SqlCE asd = new SqlCE("DBRunInfo.sdf");
            string destConnstring = "Data Source=DBRunInfo.sdf";
            SqlCeConnection destConnction = new SqlCeConnection(destConnstring);

            try
            {
                srcConnection.Open();
                destConnction.Open();

            EmbedDB.SyncServer.CopyTable(srcConnection, destConnction,
                "Select ONumber as uNumber,OPower as uGrade,OID as userID,OName as uName,OPwd as uPwd From dbo.Operator",
                "UserInfo");
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                srcConnection.Close();
                destConnction.Close();
            }
        }
    }
}

reference from :http://www.searchdatabase.com.cn/showcontent_21968.htm