Wednesday, September 9, 2015

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() 


 

 

No comments:

Post a Comment