Dealing with SQL Server Timestamp columns in ADO.NET

I recently had a requirement to copy/export SQL Server data to MS Access.

Since I could not use SSIS, I used ADO.NET to perform the table copy based on this article: http://stackoverflow.com/questions/17253453/how-to-export-data-from-sql-server-compact-to-access-mdb

Unfortunately, one of the limitations with doing this is that you have to manually create the SQL to generate the MS Access database tables which therefore requires a knowledge of the underlying DataTable datatypes and what they map to in MS Access.

I found this article which describes the MS Access datatypes: https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx

I was able to map most of the SQL Server datatypes accurately based on the listing, however, I encountered a hurdle when I encountered the SQL Server timestamp column!!

After doing some debugging, I discovered that it was translating the timestamp column into a Byte[] array, therefore, I needed to do some special handling for Byte[] and figure out its appropriate datatype mapping.

Based on this SQL Server MSDN article: https://msdn.microsoft.com/en-us/library/ms182776%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396

I discovered that I could map the timestamp column to a VarBinary data type in MS Access when creating the target database table in MS Access.

Once I did that, the copy table operation worked beautifully!!

 

This was the code that I used to generate the MS Access database table SQL:

 

public string GetCreateTableSql(DataTable table)

{

    StringBuilder sql = new StringBuilder();

    int i = 0;

    sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);

    while (i < table.Columns.Count)

    {

        bool isNumeric = false;

        bool usesColumnDefault = true;

        string columnSeparator = ",";

        sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);

        switch (table.Columns[i].DataType.ToString().ToUpper())

        {

            case "SYSTEM.BYTE":

                sql.Append(" Byte");

                break;

            //The SQL timestamp column is translated into a System.Byte[] array data type

            case "SYSTEM.BYTE[]":

                sql.Append(" VarBinary");

                break;

            case "SYSTEM.INT16":

                sql.Append(" Integer");

                isNumeric = true;

                break;

            case "SYSTEM.INT32":

                sql.Append(" Integer");

                isNumeric = true;

                break;

            case "SYSTEM.INT64":

                sql.Append(" Long");

                isNumeric = true;

                break;

            case "SYSTEM.DATETIME":

                sql.Append(" DateTime");

                usesColumnDefault = false;

                break;

            case "SYSTEM.BOOLEAN":

                sql.Append(" YesNo");

                break;

            case "SYSTEM.CHAR":

                sql.Append(" Text");

                break;

            case "SYSTEM.STRING":

                sql.AppendFormat(" Text");

                break;

            case "SYSTEM.SINGLE":

                sql.Append(" Single");

                isNumeric = true;

                break;

            case "SYSTEM.DOUBLE":

                sql.Append(" Double");

                isNumeric = true;

                break;

            case "SYSTEM.DECIMAL":

                sql.AppendFormat(" Double");

                isNumeric = true;

                break;

            default:

                sql.AppendFormat(" Text");

                break;

        }//switch

        if (table.Columns[i].AutoIncrement)

        {

            sql.AppendFormat(" AutoNumber");

        }//if

        //Increment the counter

        i++;

        //Add a column separator for the Create Table SQL statement

        if (i < table.Columns.Count)

        {

            sql.AppendFormat("{0}", columnSeparator);

        }//if

    }//while

    sql.AppendFormat(" {0}", ")");

    return sql.ToString();

}//method: GetCreateTableSQL()