csharp: LocalDataCache.sync

<?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());
        }
    }
}

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
    }
}