Microsoft Data Access Application Block

[mendhak]

I was just reading an article on Data Access Application Blocks here, and had a few questions:

1) Is there a simpler way to type it? How about DAAB?

2) How often is this used? Do you use DAAB in your applications, or is the general practice to use normal coding?

[mendhak]

BURP.

I mean... BUMP.

[mendhak]

Nobody likes me.

Well, I just thought I'd share a bit of what I think of DAAB here. I tried it out, and I like the way that they have implemented this. Very good work by them. Who says they don't write good code!

Data access has now become extremely simple:

Connecting is as easy as performing brain surgery on one of my coworkers (not required):

Dim ds As DataSet
        Dim strConnection As String
        Dim categoryID As Integer
 
        strConnection = "Data Source=(local);" & _
                            "Initial Catalog=NorthWind;" & _
                            "User ID=sa;Password=;"
        categoryID = 4
 
 
'This line is for Stored Procs
        'ds = SqlHelper.ExecuteDataset(strConnection, CommandType.StoredProcedure, "getProductsByCategory", New SqlParameter("@CategoryID", categoryID))
 
'This is for SQL statements
        ds = SqlHelper.ExecuteDataset(strConnection, CommandType.Text, "SELECT * FROM Products")

And there, you're done.

The helpfile left me asking for more, but it's for the best. I can always keep working on it.

Good stuff, I definitely reccommend this.

[mendhak]

And look at this:

To update, using a stored proc:

Dim trans As SqlTransaction = conn.BeginTransaction()
 
       'Define the parameters.
 
        Dim params(1) As SqlParameter
 
        'Here is one way to do it... the detailed way...
        params(0) = New SqlParameter
 
        params(0).ParameterName = "@varQPU"
        params(0).DbType = SqlDbType.VarChar
        params(0).Size = 20
        params(0).Value = TextBox1.Text
 
 
        'The other way
        params(1) = New SqlParameter("@PID", SqlDbType.Int)
        params(1).Value = PID
 
 
 
        Try
 
            
'Update using a stored proc   
         'SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "update_products", params)
 
'Or update using a SQL statement.   
         SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE Products SET QuantityPerUnit = '" & TextBox1.Text & "' WHERE ProductID = " & PID & ";")
 
 
 
 
 
            trans.Commit()

This is great stuff. You should be using it too.

Ok, I'll shuttup now.

[CyberHawke]

Hey mendhak,

I read this a few weeks ago, (maybe months, you know how it is in a dungeon) and started looking into this and other blocks on MSDN. While the method structure follows microsofts best practices, the code contained within does not full use the best optimized method calls:

eg.

Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)

should be coded thus:

Dim paramInstance As IDbDataParameter = DirectCast(parameterValues(i), IDbDataParameter)

The entire block is rife with this kinds of errors, so while the general concept is a good one, the block should (and I am) be recoded.

[EricDalnas]

I use it.

[Edneeis]

Originally posted by CyberHawke
Hey mendhak,

I read this a few weeks ago, (maybe months, you know how it is in a dungeon) and started looking into this and other blocks on MSDN. While the method structure follows microsofts best practices, the code contained within does not full use the best optimized method calls:

eg.

Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)

should be coded thus:

Dim paramInstance As IDbDataParameter = DirectCast(parameterValues(i), IDbDataParameter)

The entire block is rife with this kinds of errors, so while the general concept is a good one, the block should (and I am) be recoded.

In your example why do you feel that Ctype is less efficent then DirectCast?

[Edneeis]

Originally posted by mendhak
And look at this:

To update, using a stored proc:

Dim trans As SqlTransaction = conn.BeginTransaction()
 
       'Define the parameters.
 
        Dim params(1) As SqlParameter
 
        'Here is one way to do it... the detailed way...
        params(0) = New SqlParameter
 
        params(0).ParameterName = "@varQPU"
        params(0).DbType = SqlDbType.VarChar
        params(0).Size = 20
        params(0).Value = TextBox1.Text
 
 
        'The other way
        params(1) = New SqlParameter("@PID", SqlDbType.Int)
        params(1).Value = PID
 
 
 
        Try
 
            
'Update using a stored proc   
         'SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "update_products", params)
 
'Or update using a SQL statement.   
         SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE Products SET QuantityPerUnit = '" & TextBox1.Text & "' WHERE ProductID = " & PID & ";")
 
 
 
 
 
            trans.Commit()

This is great stuff. You should be using it too.

Ok, I'll shuttup now.

Building your sql statement in that manner leaves you open to SQL injection attacks. You should use parameterized queries where ever possible.

SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE Products SET QuantityPerUnit = @Text WHERE ProductID = @ProductId",New SqlParameter("@Text", TextBox1.Text)New SqlParameter("@ProductId", PID)

As a simple example try typing the following into TextBox1 with your code:

' WHERE 1 = 1 --

[mendhak]

This thread is three months old!!

I was only experimenting at the time, and just thought I'd share some stuff...

I recently found out about the little textbox trick from Woka.

In a textbox, you could go something like

<removed: Should I even be discussing this here? >

and it'd wreak havoc, assuming you know a few details there.

[Kirun]

i am also using that application in my code and its working excellent for me ...

[CyberHawke]

It was not my intent to indicate that there was some problem with the code base, or that it does not work. Microsofts description of the Block states that it has been optimized to follow their best practices. If you review the code in the block you will find a number of things that are not optimized and also a number of inconsistencies.

Does the code work? Yes
Is it optimized? No

And because it is not optimized, you are no better off using it than if you had written the code yourself.

Am I suggesting that you not use it? No

Because it does wrap the functionality of accessing data using SqlClient methods quite nicely and provides an excellent resource for novice programmers who need help with rolling out a project and are looking for quick and easy ways to write their data access code.

Buf if you are an experienced programmer, and are familiar with Microsofts best practices, and are accustomed to writing consitent code then you will spot the problems in the Block that I have been speaking of.

Armed with that knowledge, you will probably want to rewrite the code in a more optimzed way, and also to ensure consistency throughout the code.

[CyberHawke]

Originally posted by mendhak
This thread is three months old!!

I was only experimenting at the time, and just thought I'd share some stuff...

I recently found out about the little textbox trick from Woka.

In a textbox, you could go something like

<removed: Should I even be discussing this here? >

and it'd wreak havoc, assuming you know a few details there.

I have attended several anti-hacker seminars, and it's my experience that you should not discuss even well known hacks because there is that person out there, who has never been a hacker, but sees your example, wants to try it out, and then the seed is planted.

[mendhak]

Agreed. I won't even mention that neat little DOS command line hack that you can use to <removed by mendhak> to gain access to <removed by aliens>