欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

csharp: LocalDataCache.sync

程序员文章站 2022-04-08 18:52:20
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
    }
}