Dirk Bergmann

Developer, panographer. Does consultancy work from own company. Lives in Chiang Mai, Thailand.

Twitter

Chiang Mai

Hua Hin

Thailand Hotels

ListVote - List. Vote. Learn.

Partnerseek - Find Partners

Synchronizing Local Data with a Remote Web Hosting Database

Many webmasters of smaller web sites use the services of web hosting companies located in locations around the globe. Here in Thailand running your own web server is not feasible, as connections from the important markets of Europe, the Americas and even other parts of Asia might be too slow and unreliable. Using the services of a web hosting company usually involves running a local development database and an identical database running on the web hosting company's servers.

To keep the contents in sync, it is desirable to have an easy-to-use system of uploading refreshed data to the remote database, ideally from the desk of every employee on the floor via an ASP.NET web app on the local intranet. There are several options available to achieve this:

##

The successor of DTS is far more flexible and powerful than its predecessor, with great enhancements of the programming model in terms of extensibility and flexibility. How to call SSIS from C

##

Of course you can issue INSERT queries that transfer your data to a remote database. If your remote server is a linked server you can simply issue a query like the following:

INSERT INTO [my.linked.server].myremotedb.user.mytable

SELECT * FROM localdb.user.mytable WHERE id = @ID

GO

However, if id is an identity column (as the name suggests), you're out of luck as you have trouble with this query:

SET IDENTITY_INSERT [my.linked.server].myremotedb.user.mytable ON

This query issued to the linked server from your local db will usually fail in a hosting environment due to a lack of pertinent permissions.

An OPENROWSET ad-hoc connection won't help you either because the KEEPIDENTITY table hint provided by an OPENROWSET query can only be used when inserting from a data file, while reading data from an OLEDB data source also might fail due to permission restraints.

##

ADO.NET 2.0 offers a fast and efficient way to transfer data to a remote database with the SqlBulkCopy class.

The following code illustrates this feature:

private void transferWidgetData(int widgetID)
        {
            //tables in the local and remote database are identical
            string[] myTables = { "widgets", "widgetDetails", "widgetPrice" };

            string connectionStringLocal = "...";
            string connectionStringRemote = "...";
            using (SqlConnection conLocal =
                   new SqlConnection(connectionStringLocal))
            {
                foreach (string s in myTables)
                {

                    //create the source command
                    SqlCommand commandSourceData = new SqlCommand(
                            "SELECT * " +
                            "FROM " + s + " WHERE WidgetID = " + widgetID, conSource);

                    SqlDataReader reader;
                    //connection has to be re-opened for each datareader
                    conSource.Open();
                    reader = commandSourceData.ExecuteReader();

                    using (SqlBulkCopy bcp =
                            new SqlBulkCopy(
                            connectionStringRemote,
                            SqlBulkCopyOptions.KeepIdentity))
                    {