Patterns and Practices - SQL Database Question

29 Mar 2005 4:53 PM

Mythran

One of the major pitfalls of the new Patterns and Practices Enterprise
Library, compared to the old version of the SQL Helper libraries, is that
the code gets blown up. Before, using the SQLHelper library, I could write
code as follows, while the new library I must do stuff following the first
example TO DO the same thing:

Please note, these are not complete examples...not worrying about exception
handling or anything else...just to get the point across and my question at
the end.

SQL Helper Example:

Public Sub Update(ByVal Row As MyTypedDataSet)
    SQLHelper.ExecuteNonQueryTypedParams( _
        Common.ConnectionString, _
        "MyTable_Update",
        Row,
        New String() { "tblMyTable" },
        New Object() { }
    )
End Sub

Patterns and Practices Enterprise Library Example:

Public Sub Update(ByVal Row As MyTypedDataSet)
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Dim cmd As DBCommandWrapper =
db.GetStoredProcCommandWrapper("MyTable_Update")

    ' Manually retrieve and set the values for each sp parameter.
    cmd.AddInParameter("@MyId", DbType.Int32, Row.MyId)
    cmd.AddInParameter("@Name", DbType.String, Row.Name)
    cmd.AddInParameter("@Address", DbType.String, Row.Address)
    cmd.AddInParameter("@City", DbType.String, Row.City)
    cmd.AddInParameter("@State", DbType.String, Row.State)
    cmd.AddInParameter("@Zip", DbType.String, Row.Zip)
    cmd.AddInParameter("@Region", DbType.String, Row.Region)
    cmd.AddInParameter("@Phone", DbType.String, Row.Phone)
    cmd.AddInParameter("@DateOfBirth", DbType.DateTime, Row.DateOfBirth)

    ' Execute the stored procedure.
    db.ExecuteNonQuery(cmd)
End Sub

So, see the difference? The latter example, we have to manually set each
parameter for the stored procedure. Why didn't they add the ability to
"sniff" out the sp to retrieve and match stored procedure parameters (or any
parameters for any type of query)?

Thanks in advance,

Mythran

30 Mar 2005 2:15 AM

Jim Hughes

Check out the rest of the overloads for ExecuteNonQuery

The only real difference is the need to use the Configuration library and
having to create a Database object using the DatabaseFactory.CreateDatabase

[Visual Basic]
Overridable OverloadsPublic Function ExecuteNonQuery( _
   ByVal storedProcedureName As String, _
   ByVal ParamArray parameterValues As Object() _
) As Integer

[C#]
public virtual int ExecuteNonQuery(
   string storedProcedureName,
   params object[] parameterValues
);

Parameters
storedProcedureName
The command that contains the query to execute.

parameterValues
An array of paramters to pass to the stored procedure. The parameter values
must be in call order as they appear in the stored procedure.

Return Value

The number of rows affected

Show quote

"Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message
news:OG93Q$HNFHA.3296@TK2MSFTNGP15.phx.gbl...
> One of the major pitfalls of the new Patterns and Practices Enterprise
> Library, compared to the old version of the SQL Helper libraries, is that
> the code gets blown up. Before, using the SQLHelper library, I could
> write code as follows, while the new library I must do stuff following the
> first example TO DO the same thing:
>
> Please note, these are not complete examples...not worrying about
> exception handling or anything else...just to get the point across and my
> question at the end.
>
> SQL Helper Example:
>
> Public Sub Update(ByVal Row As MyTypedDataSet)
>    SQLHelper.ExecuteNonQueryTypedParams( _
>        Common.ConnectionString, _
>        "MyTable_Update",
>        Row,
>        New String() { "tblMyTable" },
>        New Object() { }
>    )
> End Sub
>
>
> Patterns and Practices Enterprise Library Example:
>
> Public Sub Update(ByVal Row As MyTypedDataSet)
>    Dim db As Database = DatabaseFactory.CreateDatabase()
>    Dim cmd As DBCommandWrapper =
> db.GetStoredProcCommandWrapper("MyTable_Update")
>
>    ' Manually retrieve and set the values for each sp parameter.
>    cmd.AddInParameter("@MyId", DbType.Int32, Row.MyId)
>    cmd.AddInParameter("@Name", DbType.String, Row.Name)
>    cmd.AddInParameter("@Address", DbType.String, Row.Address)
>    cmd.AddInParameter("@City", DbType.String, Row.City)
>    cmd.AddInParameter("@State", DbType.String, Row.State)
>    cmd.AddInParameter("@Zip", DbType.String, Row.Zip)
>    cmd.AddInParameter("@Region", DbType.String, Row.Region)
>    cmd.AddInParameter("@Phone", DbType.String, Row.Phone)
>    cmd.AddInParameter("@DateOfBirth", DbType.DateTime, Row.DateOfBirth)
>
>    ' Execute the stored procedure.
>    db.ExecuteNonQuery(cmd)
> End Sub
>
>
> So, see the difference? The latter example, we have to manually set each
> parameter for the stored procedure. Why didn't they add the ability to
> "sniff" out the sp to retrieve and match stored procedure parameters (or
> any parameters for any type of query)?
>
> Thanks in advance,
>
> Mythran
>
>

30 Mar 2005 4:25 PM

Mythran

Show quote

"Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
news:%231Pzb6MNFHA.3788@tk2msftngp13.phx.gbl...
> Check out the rest of the overloads for ExecuteNonQuery
>
> The only real difference is the need to use the Configuration library and
> having to create a Database object using the
> DatabaseFactory.CreateDatabase
>
> [Visual Basic]
> Overridable OverloadsPublic Function ExecuteNonQuery( _
>   ByVal storedProcedureName As String, _
>   ByVal ParamArray parameterValues As Object() _
> ) As Integer
>
>
> [C#]
> public virtual int ExecuteNonQuery(
>   string storedProcedureName,
>   params object[] parameterValues
> );
>
>
> Parameters
> storedProcedureName
> The command that contains the query to execute.
>
> parameterValues
> An array of paramters to pass to the stored procedure. The parameter
> values must be in call order as they appear in the stored procedure.
>
> Return Value
>
> The number of rows affected
>

This is just my point. You still have to pass each parameter directly,
rather than just a single Row where the ExecuteNonQuery would pull the
values from the datarow without the developer having to manually pass each
value. In SqlHelper, they had ExecuteNonQueryTypedParams that would allow
you to pass the connection string, sp name, and the data row that contained
the values.

Mythran

31 Mar 2005 9:04 PM

Jim Hughes

Sorry, I missed the Row argument as the point you were trying to make, I
jumped on the manual creation of all of the Parameter objects.

The array of Parameters that I was referring to was not actual Paramter
objects, but just a an array of values. So yes, you can't currently pass a
datarow but you could pass New Object { 1, "Jim", "Hughes" } as a single
argument.

Shouldn't be to hard to add an overload that accepts a datarow, we do have
the source code after all :)

Show quote

"Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message
news:eF5ucUUNFHA.2384@tk2msftngp13.phx.gbl...
>
> "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
> news:%231Pzb6MNFHA.3788@tk2msftngp13.phx.gbl...
>> Check out the rest of the overloads for ExecuteNonQuery
>>
>> The only real difference is the need to use the Configuration library and
>> having to create a Database object using the
>> DatabaseFactory.CreateDatabase
>>
>> [Visual Basic]
>> Overridable OverloadsPublic Function ExecuteNonQuery( _
>>   ByVal storedProcedureName As String, _
>>   ByVal ParamArray parameterValues As Object() _
>> ) As Integer
>>
>>
>> [C#]
>> public virtual int ExecuteNonQuery(
>>   string storedProcedureName,
>>   params object[] parameterValues
>> );
>>
>>
>> Parameters
>> storedProcedureName
>> The command that contains the query to execute.
>>
>> parameterValues
>> An array of paramters to pass to the stored procedure. The parameter
>> values must be in call order as they appear in the stored procedure.
>>
>> Return Value
>>
>> The number of rows affected
>>
>
> This is just my point. You still have to pass each parameter directly,
> rather than just a single Row where the ExecuteNonQuery would pull the
> values from the datarow without the developer having to manually pass each
> value. In SqlHelper, they had ExecuteNonQueryTypedParams that would allow
> you to pass the connection string, sp name, and the data row that
> contained the values.
>
> Mythran
>
>

31 Mar 2005 10:45 PM

Mythran

Show quote

"Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
news:OORPTWjNFHA.2748@TK2MSFTNGP09.phx.gbl...
> Sorry, I missed the Row argument as the point you were trying to make, I
> jumped on the manual creation of all of the Parameter objects.
>
> The array of Parameters that I was referring to was not actual Paramter
> objects, but just a an array of values. So yes, you can't currently pass a
> datarow but you could pass New Object { 1, "Jim", "Hughes" } as a single
> argument.
>
> Shouldn't be to hard to add an overload that accepts a datarow, we do have
> the source code after all :)
>
>

Aye, I know that :) And I have begun making one...but the problem is that
we have to create one for every database that we may use. One for Oracle,
Sql Server, and DB2 (just listing those that are in the Enterprise package
when you download it). I can't remember if Database was a class or
interface....but in any case, how you get sp parameter names for each dbms
may differ...so you couldn't write just one overload for the Database class,
you'd have to write one for each dbms. Anywho, I'll use this one I'm
working on for Sql Server for now...but hopefully it does get added to the
base class or at least to the derived classes in the primary download in
future updates ;)

To explain why I would rather not use New Object { } to pass the values....I
have 4 projects (DAL, BLL, Schema, and WebUI). The WebUI sends a typed
DataRow to the BLL. The BLL validates and massages the data. If invalid,
it rejects it and throws a validation exception, otherwise it will send the
typed DataRow to the DAL (using a method named Update or Insert on the DAL
object that represents the item in the database). From the DAL, it handles
the data retrieval/updates without knowledge of what dbms it is using (using
the Enterprise Patterns and Practices for that).

Here are some problems,

1.) The destination dbms may not support stored procedures, therefore the
DAL project will have to be rewritten.
2.) For our larger projects (accounting projects are huge!), we can have
hundreds of DAL objects that have to be written. If we were to pass the
values of each typed DataRow to insert and/or update, you can only imaging
how long it takes to do this. It's a pain to read as well.

Anywho, my 2 1/2 cents, thanks for your help :)

Mythran