Patrick Wellink

dOOdad visual architecture

Prevent SQL injection !

In the NorthWind Database the Customer table has a CustomerID field and the field is 5 long.

I am using the MyGeneration dOOdad architecture and I query the database for every customet that has a customerid of 'ANTON'

In my code this looks like :

Dim cust As New Customers
cust.Where.CustomerID.Value = "ANTON --AND HERE IS SOME STUFF TOO--"
cust.Query.Load()

So in the code I actually query on : ANTON --AND HERE IS SOME STUFF TOO--

But if I look in the SQL-Profiler I see the following :

exec sp_executesql N'SELECT * FROM [Customers] WHERE [CustomerID] = @CustomerID1 ', N'@CustomerID1 nchar(5)', @CustomerID1 = N'ANTON'

So the variable @CustomerID is cutoff at the correct length. This feature could prevent SQL Injection.

The more I look at the dOOdad architecture the more I like it.

Database Documenter MyGeneration Templates

On the MyGeneration WebSite there are  Templates that create database documentation in a very nice (and goodlooking) html format. Tables, Stored Procedures and such are placed in nice html documents.

They're extremely easy to create and it's really easy to browse all objects. Can come in pretty handy when you're doing some coding and want a clean look at your database, or just need the extra documentation for a client or something.

To See what the database reports look like Click here....

Data Access Application Block Support....

Yep.....

If you don't like the dOOdad architecture, you can use MyGeneration to generate Stored Procedures and a complete DAL that make use of the Data Access Application Block in VB.Net or C# that use the more classic approach.

have a look here at the DAAB architecture.

dOOdad, Sorting in an ASP datagrid made easy

I have been playing around with the dOOdad architecture and  found out something very nice about this architecture.

The nice part about it is that this piece of code wil look the same for every datagrid. Only the BusinessClass will vary. And presto we have sorting on every column, Ascending and descending !.

MyGeneration Makes life Very easy....

Well you prabably heard about MyGeneration by now if you are a regular BloggingAbout.Net reader. If you haven't go and download it now here.

I am currently in the process of rewriting a web application and I am completely doing away with the old DAL. Instead I am using the dOOdad architecture that comes with MyGeneration.

Here are some of the advantages I found so far....

1. About 40 % less code in the presentation layer ! ( So there is less to review as well )
2. The code is much more readable.
3. The Stored Procedures in the database all have a Uniform Name
4. It's Faster ! ( probably my old DAL wasn't as fast as I thought)
5. 0 lines of code in my DAL. Everything is handled in the generated Classes.
6. I guess an overall code reduction of 70%
7. Even the Dynamic SQL is handled in the correct SQL way (optimizing it for successive executes)
8. Doing away with a lot of Stored procedures that would return a very specific subset

And since it's a Web application you can do really nice stuff to the sort orders as well. Like the following....

       If Not IsPostBack Then
            Dim PubType As String = Request.QueryString.Item("PubType")
            If PubType = Nothing Then
                MyPublications.LoadAll() ' Load Them all
            Else
                MyPublications.Where.PublicationType.Value = PubType
                MyPublications.Query.Load() ' Load the specific subset
            End If
            ' Set Sort order
            MyPublications.Sort = MyPublications.ColumnNames.PublicationDate + " DESC" 
            ' Add to cache
            Cache.Remove("Publications")
            Cache.Insert("Publications", MyPublications)
            BindGrid()
        End If

Then when you click on a header you can do stuff like....

    Private Sub dgPublications_SortCommand(ByVal source As Object, ByVal e As  System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgPublications.SortCommand
        ' Get from the cache
        MyPublications = Cache.Get("Publications")
        ' Set new sortexpression but keep results sorted on date
        MyPublications.Sort = e.SortExpression + " , " + MyPublications.ColumnNames.PublicationDate + " DESC"
        MyPublications.Rewind()
        ' Add to cache, but first delete the old one
        Cache.Remove("Publications")
        Cache.Insert("Publications", MyPublications)
        BindGrid()
    End Sub

You don't have to requey anything just set the sortcommand on the Entity, rewind the stuff and bind it again....
And you could do the same with filters although I haven't used that yet....

Also a deletecommand on the datagrid is easy as .....

            Dim key As Integer = CInt(dgPublications.DataKeys(e.Item.ItemIndex).ToString())
            Dim DelPublication As New Publications
            DelPublication.LoadByPrimaryKey(Key)
            DelPublication.MarkAsDeleted()
            DelPublication.Save()
            Response.Redirect("Start.aspx?PageID=ListPublications")

Really easy ! No forgotten connetcions or stuff like that. and the code is still easy to read.......

Also note the Difference between the following....
           MyPublications.LoadByPrimaryKey(Key) ' Load the specific record
And
          MyPublications.Where.PublicationType.Value = PubType
     MyPublications.Query.Load() ' Load the specific subset
And
          MyPublications.LoadAll() ' Load them all !

This made me do away with a couple of stored procedures........... Reducing the SQL Code as well.....

Awesome Dynamic SQL generation....

Hmmm,

Played around a littlebit with the dOOdad architecture of MyGeneration. Besides a lot of nice features there is also the the dynamic query feature.... Let me show what I mean.....

' See if it is in cache
Mymenu = Cache.Get("DefaultMenu")
If Mymenu Is Nothing Then
   'get the default Menu
   Dim MyMenuID As New MenuID
   MyMenuID.Where.MenuDescription.Value = "DefaultMenu"
   MyMenuID.Query.Load()
   'Now get the MenuEntries for the menu !
   Mymenu = New MenuData
   Mymenu.Where.MenuID.Value = MyMenuID.ID
   Mymenu.Where.MenuID.Operator = MyGeneration.dOOdads.WhereParameter.Operand.Equal
   ' Only those rows that we are Authenticated to see
   Mymenu.Where.AuthenticationLevel.Value = Session("AuthenticationLevel")
   Mymenu.Where.AuthenticationLevel.Operator = MyGeneration.dOOdads.WhereParameter.Operand.LessThanOrEqual
   Mymenu.Query.AddOrderBy(Mymenu.ColumnNames.MenuOrder, ,myGeneration.dOOdads.WhereParameter.Dir.ASC)
   Mymenu.Query.Load()
   'Cache this menu.
   Cache.Insert("DefaultMenu", Mymenu)
End If
'Bind to the grid
MenuGrid.DataSource = Mymenu.DefaultView
MenuGrid.DataBind()

In the blue piece of code we set some where parameters and add a sortorder. Everything resulted in the following query beeing executed :

And this is a really nice query, nothing wrong with that !. I just was surprised how easy it was !.

Awesome Screen Generation..... (part Two)

Yep I know, i shouldn't scream so much.......
but did you know you can generate entry screens as well with myGeneration ????

Oh yes and they work !!!!!

Please click the link and investigate it, maybe it is something we can use ......

Awesome Code generation !!!!

Have a look at MyGeneration. ( Click the link !!!!!!!!!!!, don't miss out..... )

This is a freeware code generation tool that really works.....Normally I use the NorthWind database to test code generation and I test it in VB.Net and C#. Usually I have to modify stuff on some points to get the stuff working.

And now I came across this beauty !!!!.

Really try it...... !!! ..............There are several supported architectures but the best (until now) is the dOOdad architecure... (Yes click the link to see what it is...........)

I think it is really awesome..... I just pointed it to the NortWind Database and it worked at once.....
If you are interested in productivity tools have a look at it.

Oh yes and the beauty of it all.... It is all NATIVE .Net.... Not an ugly unknown DLL somewhere NO... everything is compiled. You only have to add a referrence to the dOOdad project.

Ok..... so far I told you to go and visit the website ....Here are some reasons why........

Very easy dynamic queries.........

Dim emps As New Employees
' LastNames that have "A" anywhere in them
emps.Where.LastName.Value = "%A%"
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
emps.Query.Load()

or

Dim emps As New Employees
' LastNames that have "A" anywher in them
emps.Where.LastName.Value = "%A%"
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
' Only return the EmployeeID and LastName
emps.Query.AddResultColumn(Employees.ColumnNames.EmployeeID)
emps.Query.AddResultColumn(Employees.ColumnNames.LastName)
' Order by LastName
' (you can add as many order by columns as you like by repeatedly calling this)
emps.Query.AddOrderBy(Employees.ColumnNames.LastName, WhereParameter.Dir.ASC)
' Bring back only distinct rows
emps.Query.Distinct = True
' Bring back the top 10 rows
emps.Query.Top = 10
emps.Query.Load()

Easy Iteration