How to work with generic date format

[Luc Pattyn]

I just performed a simple experiment with @name and it works just fine, so now my code for Access (using Provider=Microsoft.Jet.OLEDB.4.0) and for SQLServer looks pretty much the same, except for those stupid OleDbTypes/SqlTypes enums the parameters need. [ADDED] The MSDN doc[^] doesn't mention named parameters in a positive way, all it says is: The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters. [/ADDED] :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

modified on Monday, August 1, 2011 11:05 PM

[Peter_in_2780]

Do I smell a Tip/trick or article in the making? Good to see you again. :) Cheers, Peter

Software rusts. Simon Stephenson, ca 1994.

[Luc Pattyn]

Peter_in_2780 wrote:

Do I smell a Tip/trick or article in the making?

Not really. I don't have any reference to this, and I tend not to rely on, nor propagate, undocumented ways that seem to work but aren't promised to work.

Peter_in_2780 wrote:

Good to see you again

Thanks. I have reduced my presence, I'm visiting and posting sporadically now. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[PIEBALDconsult]

I think both uses of @anInt have to be consecutive, but it has been a while since I last experimented with it.

Luc Pattyn wrote:

Do you have any reference?

Just experience.

[PIEBALDconsult]

Luc Pattyn wrote:

those stupid OleDbTypes/SqlTypes enums the parameters need.

The what?

[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