app.config:
http://yourserver/Service.asmx
using System;using System.Collections.Generic;using System.Text;using Microsoft.Synchronization.Data;using Microsoft.Synchronization.Data.SqlServerCe;using Microsoft.Synchronization;namespace GBADeviceClient.Sync{ public class ClientSyncAgent : SyncAgent { public ClientSyncAgent() { //Hook between SyncAgent and SqlCeClientSyncProvider this.LocalProvider = new SqlCeClientSyncProvider(Settings.Default.LocalConnectionString, true); //Adds the JobList and PropertyDetails tables to the SyncAgent //setting the SyncDirection to bidirectional //drop and recreate the table if exists this.Configuration.SyncTables.Add("JobList"); this.Configuration.SyncTables.Add("PropertyDetails"); this.Configuration.SyncTables["JobList"].SyncDirection = SyncDirection.Bidirectional; this.Configuration.SyncTables["JobList"].CreationOption = TableCreationOption.DropExistingOrCreateNewTable; this.Configuration.SyncTables["PropertyDetails"].SyncDirection = SyncDirection.Bidirectional; this.Configuration.SyncTables["PropertyDetails"].CreationOption = TableCreationOption.DropExistingOrCreateNewTable; // The ServerSyncProviderProxy is a type used to abstract the particular transport // It simply uses reflection to map known method names required by the SyncProvider // In this case, we hand edited a Web Service proxy // The web service proxy required editing as VS generates proxies for all types returned by a web servcie // In this case, we have all the types for Sync Services, and duplicating types will cause errors this.RemoteProvider = new ServerSyncProviderProxy( new Sync.ConfiguredSyncWebServiceProxy(Settings.Default.WebServiceURL)); } }}
using System;using System.Linq;using System.Collections.Generic;using System.Windows.Forms;using System.Data.SqlServerCe;namespace GBADeviceClient{ /// /// https://www.microsoft.com/zh-cn/download/details.aspx?id=15784 Microsoft Synchronization Services for ADO.NET - 简体中文 /// https://www.microsoft.com/zh-CN/download/details.aspx?id=6497 Microsoft SQL Server Compact 3.5 联机丛书和示例 /// System.Data.SqlServerCe /// C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices /// 如何：将本地数据库和远程数据库配置为双向同步 /// https://docs.microsoft.com/zh-cn/previous-versions/bb629326%28v%3dvs.110%29 /// https://www.codeproject.com/Articles/22122/Database-local-cache /// https://docs.microsoft.com/zh-cn/previous-versions/aa983341%28v%3dvs.110%29 SQL Server Compact 4.0 和 Visual Studio /// https://www.microsoft.com/en-us/download/details.aspx?id=21880 Microsoft SQL Server Compact 4.0 Books Online /// static class Program { /// /// The main entry point for the application. /// [MTAThread] static void Main() { //Validate the database exists // If the local database doesn't exist, the app requires initilization using (SqlCeConnection conn = new SqlCeConnection(Settings.Default.LocalConnectionString)) { if (!System.IO.File.Exists(conn.Database)) { DialogResult result = MessageBox.Show( "The application requires a first time sync to continue. Would you like to Sync Now?", "Fist Time Run", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); if (result == DialogResult.OK) { try { using (SynchronizingProgress progressForm = new SynchronizingProgress()) { // Pop a Progress form to get the cursor and provide feedback // on what's happening // The current UI is simply to make sure the wiat cursor shows progressForm.Show(); // Make sure the form is displayed Application.DoEvents(); Cursor.Current = Cursors.WaitCursor; Cursor.Show(); Sync.ClientSyncAgent syncAgent = new Sync.ClientSyncAgent(); syncAgent.Synchronize(); } } catch (Exception ex) { // Oooops, something happened MessageBox.Show( "Unable to synchronize..." + Environment.NewLine + ex.ToString(), "Error during initial sync", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } finally { //Always, always, be sure to reset the cursor Cursor.Current = Cursors.Default; } } else return; } // If database exists } // Using conn // Good to go Application.Run(new GBAppraiseUI()); } }}
https://www.codeproject.com/Articles/22122/Database-local-cache
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Globalization;using System.IO;using System.Reflection;using System.Text.RegularExpressions;using System.Data.Common;namespace Konamiman.Data{ /// /// Represents a local filesystem based cache for binary objects stored in a database https://www.codeproject.com/Articles/22122/Database-local-cache /// /// /// /// This class allows you to store binary objects in a database table, but using the a local filesystem cache /// to increase the data retrieval speed when requesting the same data repeatedly. /// /// /// To use the class, you need a table with three columns: a string column for the object name /// (objects are uniquely identified by their names), a binary column /// for the object value, and a timestamp column (any column type is ok as long as the column value automatically changes /// when the value column changes). You need also a directory in the local filesystem. You specify these values /// in the class constructor, or via class properties. /// /// /// When you first request an object, it is retrieved from the database and stored in the local cache. /// The next time you request the same object, the timestamps of the cached object and the database object /// are compared. If they match, the cached file is returned directly. Otherwise, the cached file is updated /// with the current object value from the database. /// /// class DatabaseFileCache { #region Fields and properties //SQL commands used for database access SqlCommand selectValueCommand; SqlCommand selectTimestampCommand; SqlCommand fileExistsCommand; SqlCommand insertCommand; SqlCommand getNamesCommand; SqlCommand deleteCommand; SqlCommand renameCommand; //The local cache directory DirectoryInfo cacheDirectory; /// /// Gets or sets the maximum execution time for SQL commands, in seconds. /// /// /// Default value is 30 seconds. A larger value may be needed when handling very big objects. /// public int CommandTimeout { get { return selectValueCommand.CommandTimeout; } set { selectValueCommand.CommandTimeout = value; selectTimestampCommand.CommandTimeout = value; fileExistsCommand.CommandTimeout = value; insertCommand.CommandTimeout = value; getNamesCommand.CommandTimeout = value; deleteCommand.CommandTimeout = value; renameCommand.CommandTimeout = value; } } private SqlConnection _Connection; /// /// Gets or sets the connection object for database access. /// public SqlConnection Connection { get { return _Connection; } set { _Connection=value; CreateCommands(); } } private string _TableName; /// /// Gets or sets the name of the table that stores the binary objects in the database. /// public string TableName { get { return _TableName; } set { _TableName=value; UpdateCommandTexts(); } } private string _CachePath; /// /// Gets or sets the local cache path. /// /// /// If a relative path is specified, it will be combined with the value of the global variable DataDirectory, /// if it has a value at all. If not, the path will be combined with the application executable path. You can set the DataDirectory /// variable with this code: AppDomain.CurrentDomain.SetData("DataDirectory", ruta) /// When retrieving the value, the full path is returned, with DataDirectory or the application path appropriately expanded. /// public string CachePath { get { return _CachePath; } set { string dataDirectory=(string)AppDomain.CurrentDomain.GetData("DataDirectory"); if(dataDirectory==null) dataDirectory=Path.GetDirectoryName(Assembly.GetEntryAssembly().Location); _CachePath=Path.Combine(dataDirectory, value); cacheDirectory=new DirectoryInfo(_CachePath); } } private string _NameColumn; /// /// Gets or sets the name of the column for the object name in the database table that stores the binary objects /// /// /// Binary objects are uniquely identified by their names. This column should be defined with a "unique" /// constraint in the database, but this is not mandatory. /// public string NameColumn { get { return _NameColumn; } set { _NameColumn=value; UpdateCommandTexts(); } } private string _ValueColumn; /// /// Gets or sets the name of the column for the object contents in the database table that stores the binary objects /// /// /// This column may be of any data type that ADO.NET can convert to and from byte arrays. /// public string ValueColumn { get { return _ValueColumn; } set { _ValueColumn=value; UpdateCommandTexts(); } } private string _TimestampColumn; /// /// Gets or sets the name of the column for the timestamp in the database table that stores the binary objects /// /// /// This column may be of any data type that ADO.NET can convert to and from byte arrays. /// Also, the column value must automatically change when the value column changes. /// public string TimestampColumn { get { return _TimestampColumn; } set { _TimestampColumn=value; UpdateCommandTexts(); } } #endregion #region Constructors // Parameterless constructor is declared as private to avoid creating instances with no associated connection object private DatabaseFileCache() { } /// /// Creates a new instance of the class. /// /// Connection object for database access. /// Name of the table that stores the binary objects in the database. /// Local cache path (absolute or relative, see property CachePath). /// Name of the column for the object name in the database table that stores the binary objects. /// Name of the column for the object contents in the database table that stores the binary objects. /// Name of the column for the timestamp in the database table that stores the binary objects. public DatabaseFileCache(SqlConnection connection, string tableName, string cachePath, string nameColumn, string valueColumn, string timestampColumn) { _TableName=tableName; CachePath=cachePath; _NameColumn=nameColumn; _ValueColumn=valueColumn; _TimestampColumn=timestampColumn; Connection=connection; } /// /// Creates a new instance of the class, assuming the default names Name, Value and timestamp for the names /// of the columns in the database table that stores the binary objects. /// /// Connection object for database access. /// Name of the table that stores the binary objects in the database. /// Local cache path (absolute or relative, see property CachePath). public DatabaseFileCache(SqlConnection connection, string tableName, string cachePath) : this(connection, tableName, cachePath, "Name", "Value", "timestamp") { } /// /// Creates a new instance of the class, assuming the default names Name, Value and timestamp for the names. /// Also, assumes that the table name is Objects, and sets the local cache path to the relative name DatabaseCache /// (see property CachePath). /// /// Connection object for database access. public DatabaseFileCache(SqlConnection connection) : this(connection, "Objects", "DatabaseCache") { } #endregion #region Public methods /// /// Obtains a binary object from the local cache, retrieving it first from the database if necessary. /// /// /// /// A database connection is first established to check that an object with the specified name actually exists in the database. /// If not, null is returned. /// /// /// Then the local cache is examinated to see if the object has been already cached. If not, the whole object is /// retrieved from the database, the cached file is created, and the file path is returned. /// /// /// If the object was already cached, the timestamp of both the database object and the cached file are compared. /// If they are equal, the cached file path is returned directly. Otherwise, the cached file is recreated /// from the updated object data in the database. /// /// /// Name of the object to retrieve. /// Full path of the cached file, or null if there is not an object with such name in the database. public string GetObject(string objectName) { Connection.Open(); try { //* Obtain object timestamp from the database selectTimestampCommand.Parameters["@name"].Value=objectName; byte[] timestampBytes=(byte[])selectTimestampCommand.ExecuteScalar(); if(timestampBytes==null) return null; //No object with such name found in the database string timestamp=""; foreach(byte b in timestampBytes) timestamp+=b.ToString("X").PadLeft(2, '0'); //* Checks that the object is cached and that the cached file is up to date string escapedFileName=EscapeFilename(objectName); FileInfo[] fileInfos=cacheDirectory.GetFiles(EscapeFilename(objectName)+".*"); if(fileInfos.Length>0) { string cachedTimestamp=Path.GetExtension(fileInfos[0].Name); if(cachedTimestamp==timestamp) return fileInfos[0].FullName; //Up to date cached version exists: return it else fileInfos[0].Delete(); //Outdated cached version exists: delete it } //* Object was not cached or cached file was outdated: retrieve it from database and cache it string fullLocalFileName=Path.Combine(CachePath, escapedFileName)+"."+timestamp; selectValueCommand.Parameters["@name"].Value=objectName; File.WriteAllBytes(fullLocalFileName, (byte[])selectValueCommand.ExecuteScalar()); return fullLocalFileName; } finally { Connection.Close(); } } /// /// Obtains the cached version of a database object, if it exists. /// /// Name of the object whose cached version is to be retrieved. /// Full path of the cached file, or null if there the specified object is not cached. /// /// This method does not access the database at all, it only checks the local cache. /// It should be used only when the database becomes unreachable, and only if it is acceptable /// to use data that may be outdated. /// public string GetCachedFile(string objectName) { FileInfo[] fileInfos=cacheDirectory.GetFiles(EscapeFilename(objectName)+".*"); if(fileInfos.Length>0) return fileInfos[0].FullName; else return null; } /// /// Creates or updates a binary object in the database from a byte array. /// /// Contents of the binary object. /// Object name. /// /// If there is already an object with the specified name in the database, its contents are updated. /// Otherwise, a new object record is created. /// public void SaveObject(byte[] value, string objectName) { insertCommand.Parameters["@name"].Value=objectName; insertCommand.Parameters["@value"].Value=value; Connection.Open(); try { insertCommand.ExecuteNonQuery(); } finally { Connection.Close(); } } /// /// Creates or updates a binary object in the database from the contents of a file. /// /// Full path of the file containing the object data. /// Object name. /// /// If there is already an object with the specified name in the database, its contents are updated. /// Otherwise, a new object record is created. /// public void SaveObject(string filePath, string objectName) { SaveObject(File.ReadAllBytes(filePath), objectName); } /// /// Creates or updates a binary object in the database from the contents of a file, /// using the file name (without path) as the object name. /// /// Full path of the file containing the object data. /// /// If there is already an object with the specified name in the database, its contents are updated. /// Otherwise, a new object record is created. /// public void SaveObject(string filePath) { SaveObject(filePath, Path.GetFileName(filePath)); } /// /// Deletes an object from the database and from the local cache. /// /// Object name. /// /// If the object does not exist in the database, nothing happens and no error is returned. /// public void DeleteObject(string objectName) { //* Delete object from database deleteCommand.Parameters["@name"].Value=objectName; Connection.Open(); try { deleteCommand.ExecuteNonQuery(); } finally { Connection.Close(); } //* Delete object from local cache FileInfo[] files=cacheDirectory.GetFiles(EscapeFilename(objectName)+".*"); foreach(FileInfo file in files) file.Delete(); } /// /// Changes the name of an object in the database, and in the local cache. /// /// Old object name. /// New object name. /// /// If the object does not exist in the database, nothing happens and no error is returned. /// public void RenameObject(string oldName, string newName) { //* Rename object in database renameCommand.Parameters["@oldName"].Value=oldName; renameCommand.Parameters["@newName"].Value=newName; Connection.Open(); try { renameCommand.ExecuteNonQuery(); } finally { Connection.Close(); } //* Rename object in local cache string escapedOldName=EscapeFilename(oldName); string escapedNewName=EscapeFilename(newName); FileInfo[] files=cacheDirectory.GetFiles(escapedOldName+".*"); foreach(FileInfo file in files) { string timestamp=Path.GetExtension(file.Name); file.MoveTo(Path.Combine(CachePath, escapedNewName+timestamp)); } } /// /// Deletes all cached files that have no matching object in the database. /// /// /// Cached files with no matching object in the database could appear if another user /// (or another application) deletes an object that was already cached. /// public void PurgeCache() { List databaseObjectNames=new List (GetObjectNames()); FileInfo[] files=cacheDirectory.GetFiles(); foreach(FileInfo file in files) { if(!databaseObjectNames.Contains(UnescapeFilename(Path.GetFileNameWithoutExtension(file.Name)))) file.Delete(); } } /// /// Checks whether an object exists in the database or not. /// /// Object name. /// True if there is an object with the specified name in the database, False otherwise. /// /// The local cache is not accessed, only the database is checked. /// public bool ObjectExists(string objectName) { fileExistsCommand.Parameters["@name"].Value=objectName; Connection.Open(); try { int exists=(int)fileExistsCommand.ExecuteScalar(); return exists==1; } finally { Connection.Close(); } } /// /// Obtains the names of all the objects stored in the database. /// /// Names of all the objects stored in the database. /// /// The local cache is not accessed, only the database is checked. /// public string[] GetObjectNames() { List names=new List (); Connection.Open(); try { SqlDataReader reader=getNamesCommand.ExecuteReader(); while(reader.Read()) { names.Add(reader.GetString(0)); } reader.Close(); return names.ToArray(); } finally { Connection.Close(); } } #endregion #region Private methods /// /// Escapes an object name so that it is a valid filename. /// /// Original object name. /// Escaped name. /// /// All characters that are not valid for a filename, plus "%" and ".", are converted into "%uuuu", where uuuu is the hexadecimal /// unicode representation of the character. /// private string EscapeFilename(string fileName) { char[] invalidChars=Path.GetInvalidFileNameChars(); // Replace "%", then replace all other characters, then replace "." fileName=fileName.Replace("%", "%0025"); foreach(char invalidChar in invalidChars) { fileName=fileName.Replace(invalidChar.ToString(), string.Format("%{0,4:X}", Convert.ToInt16(invalidChar)).Replace(' ', '0')); } return fileName.Replace(".", "%002E"); } /// /// Unescapes an escaped file name so that the original object name is obtained. /// /// Escaped object name (see the EscapeFilename method). /// Unescaped (original) object name. public string UnescapeFilename(string escapedName) { //We need to temporarily replace %0025 with %! to prevent a name //originally containing escaped sequences to be unescaped incorrectly //(for example: ".%002E" once escaped is "%002E%0025002E". //If we don't do this temporary replace, it would be unescaped to "..") string unescapedName=escapedName.Replace("%0025", "%!"); Regex regex=new Regex("%(? [0-9A-Fa-f]{4})"); Match m=regex.Match(escapedName); while(m.Success) { foreach(Capture cap in m.Groups["esc"].Captures) unescapedName=unescapedName.Replace("%"+cap.Value, Convert.ToChar(int.Parse(cap.Value, NumberStyles.HexNumber)).ToString()); m=m.NextMatch(); } return unescapedName.Replace("%!", "%"); } /// /// Creates the commands for database access. /// /// /// This method is executed when the Connection property changes. /// private void CreateCommands() { selectValueCommand=Connection.CreateCommand(); selectValueCommand.Parameters.Add("@name", SqlDbType.NVarChar); selectTimestampCommand=Connection.CreateCommand(); selectTimestampCommand.Parameters.Add("@name", SqlDbType.NVarChar); fileExistsCommand=Connection.CreateCommand(); fileExistsCommand.Parameters.Add("@name", SqlDbType.NVarChar); insertCommand=Connection.CreateCommand(); insertCommand.Parameters.Add("@name", SqlDbType.NVarChar); insertCommand.Parameters.Add("@value", SqlDbType.VarBinary); getNamesCommand=Connection.CreateCommand(); deleteCommand=Connection.CreateCommand(); deleteCommand.Parameters.Add("@name", SqlDbType.NVarChar); renameCommand=Connection.CreateCommand(); renameCommand.Parameters.Add("@oldName", SqlDbType.NVarChar); renameCommand.Parameters.Add("@newName", SqlDbType.NVarChar); UpdateCommandTexts(); } /// /// Updates the text of the commands used for database access. /// /// /// This method is executed when any of these properties change: TableName, NameColumn, ValueColumn, TimestampColumn. /// private void UpdateCommandTexts() { selectValueCommand.CommandText=string.Format( "select {0} from {1} where {2}=@name", ValueColumn, TableName, NameColumn); selectTimestampCommand.CommandText=string.Format( "select {0} from {1} where {2}=@name", TimestampColumn, TableName, NameColumn); fileExistsCommand.CommandText=string.Format( "if exists(select {0} from {1} where {0}=@name) select 1; else select 0;", NameColumn, TableName); insertCommand.CommandText=string.Format( "if exists (select {0} from {1} where {0}=@name) update {1} set {2}=@value where {0}=@name; else insert into {1} ({0}, {2}) values (@name, @value);", NameColumn, TableName, ValueColumn); getNamesCommand.CommandText=string.Format("select {0} from {1}", NameColumn, TableName); deleteCommand.CommandText=string.Format( "delete from {0} where {1}=@name", TableName, NameColumn); renameCommand.CommandText=string.Format( "update {0} set {1}=@newName where {1}=@oldName", TableName, NameColumn); } #endregion }}