How to work with generic date format
-
Since you asked, here is my next obstacle: I have a legacy Access database I want to convert to something more modern, probably SQLServer Express. The built-in "Upgrade Wizard" produced a working database, except for the primary keys: all fields of type "Autonumber" became regular integer fields, without "Identity ON". I know I could convert each table individually, adding a column, marking it identity, then force it to accept the existing values, finally removing and renaming columns. But that is so messy and cumbersome. Do you have any suggestions? or did I miss something? TIA.
Luc Pattyn [My Articles] Nil Volentibus Arduum
I dunno. I avoid wizards; I prefer to maintain control. On the other hand, the closest I've come to that was writing an app to create a clone of an Ingres database in SQL Server -- which had no keys and hardly any indices, so I didn't bother copying them. Of course, I also avoid "Autonumber" fields, so you'll get little sympathy from me. :-D Here's what I currently have for doing that sort of thing:
protected virtual System.Collections.Generic.Dictionary<System.Type,System.String> TypeMap { get ; set ; }
...
public virtual int
CreateTable
(
string TableName
,
System.Data.IDataReader SchemaSource
)
{
return ( this.CreateTable ( TableName , SchemaSource.GetSchemaTable() ) ) ;
}public virtual int
CreateTable
(
string TableName
,
System.Data.DataTable SchemaSource
)
{
if ( this.TypeMap == null )
{
throw ( new System.InvalidProgramException ( "You can't translate without a TypeMap" ) ) ;
}System.Text.StringBuilder cmd = new System.Text.StringBuilder() ; cmd.AppendFormat ( "CREATE TABLE \[{0}\] ( " , TableName ) ; foreach ( System.Data.DataRow col in SchemaSource.Rows ) { if ( !this.TypeMap.ContainsKey ( (System.Type) col \[ "DataType" \] ) ) { throw ( new System.InvalidProgramException ( "The TypeMap doesn't contain type " + col \[ "DataType" \].ToString() ) ) ; } cmd.AppendFormat ( this.TypeMap \[ (System.Type) col \[ "DataType" \] \] , col \[ "ColumnName" \] , col \[ "ColumnSize" \] , col \[ "NumericPrecision" \] , col \[ "NumericScale" \] , (bool) col \[ "AllowDBNull" \]?" NULL ":" NOT NULL " ) ; cmd.Append ( " , " ) ; } cmd.Replace ( "," , ")" , cmd.Length - 2 , 1 ) ; return ( this.ExecuteNonQuery ( cmd.ToString() ) ) ;}
I know I've used this method, but only to clone tables from SQL Server to Access, so I'm unsure if it will work the other way. Here's the TypeMap I have for Access:
typemap = new System.Collections.Generic.Dictionary<System.Type,System.String>() ; typemap \[ typeof(Syst -
Thanks Piebald, great idea of using interfaces here, it allowed me to reduce my code a lot. I got it all working with a single IDbConnection and IDbCommand (serving either OleDb/Access or Sql/SQLServer), except for one issue: Access doesn't want to store DateTime values in Date/Time fields when using IDbCommand and CreateParameter, not even when I force the field type (with result.DBType, which is generic, not OleDb-specific). I'm getting a generic message about a criteria datatype mismatch, and all Google turns up is adding or removing quotes, which I don't have and don't want. So I reverted to using
cmd.Parameters.Add(name,type).Value=valwhich is target specific but works fine. :)Luc Pattyn [My Articles] Nil Volentibus Arduum
The DateTime error is not a new problem, and is apparently "by design": https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
