Dirk.Net

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:

Programming against SQL Server 2005 Integration Services (SSIS)

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# is detailed in this article. However, SSIS are only installed with advanced versions (Standard upwards) of SQL Server and are not included with the free Express Edition nor the Workgroup edition (with the latter you apparently can open and save SSIS packages). I found this option not feasible, mainly because debugging is almost impossible and the Dts.Runtime.Package.Execute () method only returns very terse result code.

Using SQL

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.

SqlBulkCopy to the Rescue

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))
                    {
                        bcp.DestinationTableName =
                            "dbo." + s;
                        try
                        // Write from the source to the destination.
                        {
                            bcp.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            //handle error here


                        }
                        finally
                        {
                            conSource.Close();
                        }
                    }

                }

            }

        } 

Note here the extremely useful SqlBulkCopyOptions.KeepIdentity hint which enables inserting identity values into the remote table. This code can easily be called from ASP.NET, enabling fast and flexible content management and synchronization with the remote database from your local intranet.

While the basic cab creation is outlined here, this article describes the entire rather involved process of packaging the cab with a windows installer. This procedure takes some practice to master. No fewer than 3 major steps have to be taken outlined in this little cheat sheet:

Step 1. Package the output of your project (project type: Smart Device Cab Project)

• Add project output of your main project (which is usually a smart device app) and create a link to it for the mobile device’s programs menu.
• Set registry values your new project will need to create on the mobile device. 

Step 2. Create a custom installer package (project type: Windows Class Library).

• Put an .ini text file into the custom installer project, put the relevant info into this file and reference the file in the event handlers.

Step 3. Create the setup package (project type: Setup Project).

• Add the cab file from the cab project and the .ini and  the dll created by the custom installer project.
• Add a custom action (right click project, View>Custom Actions) with a reference to the custom installer dll.

There are several software packages that help you with this process. However, some practice will soon make you an expert and arguably give you more control than a prefab software package.

After not finding a simple app online calculating the seconds since a past point in time, I made this little web form.

It uses the rather obscure DateTime.ParseExact () function which provides an unforgiving way to parse a date string according to a specific date format string. The date string has to match the format string exactly, otherwise an exception will be thrown. Also, don’t forget to escape slashes in your date format string.

Here’s some sample code:

string theDate = tbDateInput.Text;
            System.IFormatProvider format =
                System.Globalization.CultureInfo.InvariantCulture;

            string expectedformat = "yyyyMMddHHmmss";

            DateTime startDate = DateTime.ParseExact(theDate,
                expectedformat, format, System.Globalization.
                DateTimeStyles.AllowWhiteSpaces
                );

            TimeSpan sp = DateTime.Now - startDate;
            lblResult.Text = String.Format("{0} seconds have passed since {1}",
                sp.TotalSeconds,
                startDate.ToLongDateString () + " " + startDate.ToLongTimeString ());

Integration services:

You may receive an error like the following:

MSP Error: 29513 SQL Server Setup Failed to compile the Managed Object Format (MOF) file C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof

The file may exist as sqlmgmprovider.mof, which is a former version created for a former version of the OS (example: you had Windows 2000 Server installed and then upgraded to Windows Server 2003).

The workaround suggested by Microsoft  did not work for me. So I simply renamed the file sqlmgmprovider.mof to sqlmgmproviderxpsp2up.mof which let me install the SP2 of Integration Services.

Database Engine:

A variety of issues, just look at the discussion with 136 replies regarding MSI installer files issues. For the relatively benign

Error 29528. The setup has encountered an unexpected error while Setting Internal Properties.

this easy fix did it for me: http://support.microsoft.com/kb/925976

Just throw away the quoted keys and they will be re-created by the subsequent installation of the service pack, which will hopefully be successful.

It seems SP 2 was rushed out the door, as this insider post confirms, so it might be good advice to wait for SP 3.

Scott Mitchell has what is probably the smoothest solution for the GridView which works for the DetailsView just the same:

1. Put a DetailsView into the EmptyDataTemplate of the “parent DetailsView”
2. Bind it to the same datasource as the parent
3. Set the DefaultMode of  the child to “Insert”

Alternatively, you can put a Button into the EmptyDataTemplate and define the click event handler as follows:

protected void Button1_Click(object sender, EventArgs e)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);

}