app.config: https://www.codeproject.com/Articles/22122/Database-local-cache ...
app.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="GBADesktopClient.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</sectionGroup>
</configSections>
<connectionStrings>
<add name="GBADesktopClient.Properties.Settings.ServerGBAppraiseDemoConnectionString"
connectionString="Data Source=.;Initial Catalog=GBAppraiseDemo;Persist Security Info=True;User ID=gbauser;Password=gbauser"
providerName="System.Data.SqlClient" />
<add name="GBADesktopClient.Properties.Settings.ClientGBAppraiseDemoConnectionString"
connectionString="Data Source=|DataDirectory|\GBAppraiseDemo.sdf;Max Database Size=2047"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>
<applicationSettings>
<GBADesktopClient.Properties.Settings>
<setting name="SyncWebServiceURL" serializeAs="String">
<value>http://yourserver/Service.asmx</value>
</setting>
</GBADesktopClient.Properties.Settings>
</applicationSettings>
<system.serviceModel>
<bindings>
<wsHttpBinding>
<binding name="WSHttpBinding_IGBACacheSyncContract" closeTimeout="00:01:00"
openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
bypassProxyOnLocal="false" transactionFlow="false" hostNameComparisonMode="StrongWildcard"
maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true"
allowCookies="false">
<readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
maxBytesPerRead="4096" maxNameTableCharCount="16384" />
<reliableSession ordered="true" inactivityTimeout="00:10:00"
enabled="false" />
<security mode="Message">
<transport clientCredentialType="Windows" proxyCredentialType="None"
realm="" />
<message clientCredentialType="Windows" negotiateServiceCredential="true"
algorithmSuite="Default" establishSecurityContext="true" />
</security>
</binding>
</wsHttpBinding>
</bindings>
<client>
<endpoint address="http://localhost:8080/GBACacheSyncService/"
binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IGBACacheSyncContract"
contract="GBAConfiguredSyncWcfService.IGBACacheSyncContract"
name="WSHttpBinding_IGBACacheSyncContract">
</endpoint>
</client>
</system.serviceModel>
</configuration>
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
{
/// <summary>
/// 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
/// </summary>
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[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
{
/// <summary>
/// Represents a local filesystem based cache for binary objects stored in a database https://www.codeproject.com/Articles/22122/Database-local-cache
/// </summary>
/// <remarks>
/// <para>
/// 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.
/// </para>
/// <para>
/// 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.
/// </para>
/// <para>
/// 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.
/// </para>
/// </remarks>
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;
/// <summary>
/// Gets or sets the maximum execution time for SQL commands, in seconds.
/// </summary>
/// <remarks>
/// Default value is 30 seconds. A larger value may be needed when handling very big objects.
/// </remarks>
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;
/// <summary>
/// Gets or sets the connection object for database access.
/// </summary>
public SqlConnection Connection
{
get
{
return _Connection;
}
set
{
_Connection=value;
CreateCommands();
}
}
private string _TableName;
/// <summary>
/// Gets or sets the name of the table that stores the binary objects in the database.
/// </summary>
public string TableName
{
get
{
return _TableName;
}
set
{
_TableName=value;
UpdateCommandTexts();
}
}
private string _CachePath;
/// <summary>
/// Gets or sets the local cache path.
/// </summary>
/// <remarks>
/// <para>If a relative path is specified, it will be combined with the value of the global variable <b>DataDirectory</b>,
/// 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: <code>AppDomain.CurrentDomain.SetData("DataDirectory", ruta)</code></para>
/// <para>When retrieving the value, the full path is returned, with DataDirectory or the application path appropriately expanded.</para>
/// </remarks>
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;
/// <summary>
/// Gets or sets the name of the column for the object name in the database table that stores the binary objects
/// </summary>
/// <remarks>
/// 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.
/// </remarks>
public string NameColumn
{
get
{
return _NameColumn;
}
set
{
_NameColumn=value;
UpdateCommandTexts();
}
}
private string _ValueColumn;
/// <summary>
/// Gets or sets the name of the column for the object contents in the database table that stores the binary objects
/// </summary>
/// <remarks>
/// This column may be of any data type that ADO.NET can convert to and from byte arrays.
/// </remarks>
public string ValueColumn
{
get
{
return _ValueColumn;
}
set
{
_ValueColumn=value;
UpdateCommandTexts();
}
}
private string _TimestampColumn;
/// <summary>
/// Gets or sets the name of the column for the timestamp in the database table that stores the binary objects
/// </summary>
/// <remarks>
/// 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.
/// </remarks>
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() { }
/// <summary>
/// Creates a new instance of the class.
/// </summary>
/// <param name="connection">Connection object for database access.</param>
/// <param name="tableName">Name of the table that stores the binary objects in the database.</param>
/// <param name="cachePath">Local cache path (absolute or relative, see property CachePath).</param>
/// <param name="nameColumn">Name of the column for the object name in the database table that stores the binary objects.</param>
/// <param name="valueColumn">Name of the column for the object contents in the database table that stores the binary objects.</param>
/// <param name="timestampColumn">Name of the column for the timestamp in the database table that stores the binary objects.</param>
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;
}
/// <summary>
/// Creates a new instance of the class, assuming the default names <b>Name</b>, <b>Value</b> and <b>timestamp</b> for the names
/// of the columns in the database table that stores the binary objects.
/// </summary>
/// <param name="connection">Connection object for database access.</param>
/// <param name="tableName">Name of the table that stores the binary objects in the database.</param>
/// <param name="cachePath">Local cache path (absolute or relative, see property CachePath).</param>
public DatabaseFileCache(SqlConnection connection, string tableName, string cachePath)
: this(connection, tableName, cachePath, "Name", "Value", "timestamp") { }
/// <summary>
/// Creates a new instance of the class, assuming the default names <b>Name</b>, <b>Value</b> and <b>timestamp</b> for the names.
/// Also, assumes that the table name is <b>Objects</b>, and sets the local cache path to the relative name <b>DatabaseCache</b>
/// (see property CachePath).
/// </summary>
/// <param name="connection">Connection object for database access.</param>
public DatabaseFileCache(SqlConnection connection)
: this(connection, "Objects", "DatabaseCache") { }
#endregion
#region Public methods
/// <summary>
/// Obtains a binary object from the local cache, retrieving it first from the database if necessary.
/// </summary>
/// <remarks>
/// <para>
/// A database connection is first established to check that an object with the specified name actually exists in the database.
/// If not, <b>null</b> is returned.
/// </para>
/// <para>
/// 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.
/// </para>
/// <para>
/// 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.
/// </para>
/// </remarks>
/// <param name="objectName">Name of the object to retrieve.</param>
/// <returns>Full path of the cached file, or <i>null</i> if there is not an object with such name in the database.</returns>
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();
}
}
/// <summary>
/// Obtains the cached version of a database object, if it exists.
/// </summary>
/// <param name="objectName">Name of the object whose cached version is to be retrieved.</param>
/// <returns>Full path of the cached file, or <i>null</i> if there the specified object is not cached.</returns>
/// <remarks>
/// 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.
/// </remarks>
public string GetCachedFile(string objectName)
{
FileInfo[] fileInfos=cacheDirectory.GetFiles(EscapeFilename(objectName)+".*");
if(fileInfos.Length>0)
return fileInfos[0].FullName;
else
return null;
}
/// <summary>
/// Creates or updates a binary object in the database from a byte array.
/// </summary>
/// <param name="value">Contents of the binary object.</param>
/// <param name="objectName">Object name.</param>
/// <remarks>
/// If there is already an object with the specified name in the database, its contents are updated.
/// Otherwise, a new object record is created.
/// </remarks>
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();
}
}
/// <summary>
/// Creates or updates a binary object in the database from the contents of a file.
/// </summary>
/// <param name="filePath">Full path of the file containing the object data.</param>
/// <param name="objectName">Object name.</param>
/// <remarks>
/// If there is already an object with the specified name in the database, its contents are updated.
/// Otherwise, a new object record is created.
/// </remarks>
public void SaveObject(string filePath, string objectName)
{
SaveObject(File.ReadAllBytes(filePath), objectName);
}
/// <summary>
/// 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.
/// </summary>
/// <param name="filePath">Full path of the file containing the object data.</param>
/// <remarks>
/// If there is already an object with the specified name in the database, its contents are updated.
/// Otherwise, a new object record is created.
/// </remarks>
public void SaveObject(string filePath)
{
SaveObject(filePath, Path.GetFileName(filePath));
}
/// <summary>
/// Deletes an object from the database and from the local cache.
/// </summary>
/// <param name="objectName">Object name.</param>
/// <remarks>
/// If the object does not exist in the database, nothing happens and no error is returned.
/// </remarks>
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();
}
/// <summary>
/// Changes the name of an object in the database, and in the local cache.
/// </summary>
/// <param name="oldName">Old object name.</param>
/// <param name="newName">New object name.</param>
/// <remarks>
/// If the object does not exist in the database, nothing happens and no error is returned.
/// </remarks>
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));
}
}
/// <summary>
/// Deletes all cached files that have no matching object in the database.
/// </summary>
/// <remarks>
/// Cached files with no matching object in the database could appear if another user
/// (or another application) deletes an object that was already cached.
/// </remarks>
public void PurgeCache()
{
List<string> databaseObjectNames=new List<string>(GetObjectNames());
FileInfo[] files=cacheDirectory.GetFiles();
foreach(FileInfo file in files)
{
if(!databaseObjectNames.Contains(UnescapeFilename(Path.GetFileNameWithoutExtension(file.Name))))
file.Delete();
}
}
/// <summary>
/// Checks whether an object exists in the database or not.
/// </summary>
/// <param name="objectName">Object name.</param>
/// <returns><b>True</b> if there is an object with the specified name in the database, <b>False</b> otherwise.</returns>
/// <remarks>
/// The local cache is not accessed, only the database is checked.
/// </remarks>
public bool ObjectExists(string objectName)
{
fileExistsCommand.Parameters["@name"].Value=objectName;
Connection.Open();
try
{
int exists=(int)fileExistsCommand.ExecuteScalar();
return exists==1;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// Obtains the names of all the objects stored in the database.
/// </summary>
/// <returns>Names of all the objects stored in the database.</returns>
/// <remarks>
/// The local cache is not accessed, only the database is checked.
/// </remarks>
public string[] GetObjectNames()
{
List<string> names=new List<string>();
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
/// <summary>
/// Escapes an object name so that it is a valid filename.
/// </summary>
/// <param name="fileName">Original object name.</param>
/// <returns>Escaped name.</returns>
/// <remarks>
/// 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.
/// </remarks>
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");
}
/// <summary>
/// Unescapes an escaped file name so that the original object name is obtained.
/// </summary>
/// <param name="escapedName">Escaped object name (see the EscapeFilename method).</param>
/// <returns>Unescaped (original) object name.</returns>
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("%(?<esc>[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("%!", "%");
}
/// <summary>
/// Creates the commands for database access.
/// </summary>
/// <remarks>
/// This method is executed when the Connection property changes.
/// </remarks>
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();
}
/// <summary>
/// Updates the text of the commands used for database access.
/// </summary>
/// <remarks>
/// This method is executed when any of these properties change: TableName, NameColumn, ValueColumn, TimestampColumn.
/// </remarks>
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
}
}
