How to work with generic date format

Luc Pattyn

cmd.Parameters.Add("@aDate", OleDbType.Date).Value=DateTime.Today;

these force me to duplicate a lot of code when I need to support more than one database type (Access and SQLServer). :doh:

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

I don't do it that way. I never set the type; it seems unnecessary. Here's how I create parameters currently:

protected virtual System.Data.IDbDataParameter
AddParameter
(
string Name
,
object Value
)
{
System.Data.IDbDataParameter result = this.command.CreateParameter() ;

result.ParameterName = Name ;

result.Value = Value ;

this.command.Parameters.Add ( result ) ;

return ( result ) ;

}

(I hope to pubish my latest data access library before the end of the year.)

Luc Pattyn

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=val which is target specific but works fine. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

Glad to be of service.

Luc Pattyn wrote:

interfaces here, it allowed me to reduce my code

That's what they're for.

Luc Pattyn wrote:

Access doesn't want to store DateTime

Huh, that's odd. I guess I haven't tried it. I'll give it a shot and see whether or not I can replicate it.

PIEBALDconsult

Well, I certainly was able to replicate it. In my derived class for OleDb, I have added:

protected override System.Data.IDbDataParameter
AddParameter
(
string Name
,
object Value
)
{
System.Data.OleDb.OleDbParameter result =
(System.Data.OleDb.OleDbParameter) base.AddParameter ( Name , Value ) ;

if ( Value is System.DateTime )
{
    result.OleDbType = System.Data.OleDb.OleDbType.Date ;
}

return ( result ) ;

}

Which solved the immediate problem, but I suppose there are others out there waiting to pounce.

Luc Pattyn

Thanks for the confirmation and the suggestions, I'm good now. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

Luc Pattyn wrote:

I'm good now

Don't sell yourself short. :-D

Luc Pattyn

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

PIEBALDconsult

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

Richard Deeming

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