C#

string SQL="UPDATE tableName SET anInt=@anInt, aString=@aString, anotherInt=@anInt, aDate=@aDate WHERE ..."
OleDbCommand cmd=new OleDbCommand(...);
cmd.Parameters.Add("@anInt", OleDbType.Int).Value=12;
cmd.Parameters.Add("@aString", OleDbType.VarChar).Value="abc";
// no need to give the third parameter as it re-uses the first one
cmd.Parameters.Add("@aDate", OleDbType.Date).Value=DateTime.Today;

Luc Pattyn wrote:

Do you have any reference?

Peter_in_2780 wrote:

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

Peter_in_2780 wrote:

Good to see you again

Luc Pattyn wrote:

those stupid OleDbTypes/SqlTypes enums the parameters need.

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

                                 ^
                                 |
                                 |
                                 |

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 ) ;
}

Luc Pattyn wrote:

interfaces here, it allowed me to reduce my code

Luc Pattyn wrote:

Access doesn't want to store DateTime

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 ) ;
}

Luc Pattyn wrote:

I'm good now

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

typemap = new System.Collections.Generic.Dictionary<System.Type,System.String>() ;

typemap [ typeof(System.String)   ] = " [{0}] TEXT({1}) " ;
typemap [ typeof(System.Boolean)  ] = " [{0}] YESNO " ;
typemap [ typeof(System.Int32)    ] = " [{0}] INT " ;
typemap [ typeof(System.DateTime) ] = " [{0}] DATETIME " ;
typemap [ typeof(System.Guid)     ] = " [{0}] UNIQUEIDENTIFIER " ;

^        beginning of string
[<>]?   < or >, zero or one times
\d+      one or more digits
\.?      zero or one decimal points
\d*      zero or more digits
$        end of string