In most cases multi tier application needs to have database. And everybody knows that good database design is a 25% of application. But have you think before about the information duplication which we have in code. Hublo should decrease duplication of a code.

Let me introduce our data layer – Diego. Many thanks to the Roman Pavlyuk for the idea of using refactoring as the data layer base instead of generated typed datasets. Some few words about my point on how data layer should look.

There was a lot of discussions about pros and cons on storing SQL queries on database server in stored procedures. My point is to store every SQL query on database. There are many causes : SQL queries should be easy accessed by the database administrator (who probably will need to improve performance of an application) ; SQL queries should be edited easily without compilation. And main point (as for me) is that a lot of work is about data checking/reading/moving/updating and data is stored on the database, so isn’t it better to have one call to one stored procedure to have everything done faster and easier. I saw peoples who retrieving the data from the database, and then iterate that data inside a loop and so on. Isn’t it faster to have one SQL query with a join.

Ok, now let’s go ahead with Diego. So, let’s imagine that we have a lot of methods wrapped in stored procedures. Now we need to call that procedures and handle response. About the response : in most cases in response we will have some entities of the system. Let’s imagine that we have procedure spPortalUpdate with all necessary parameters to update the our entity “Portal” and return of the procedure is updated portal instance. So, how is it looking now? On c# you will write some code like this :

As you can understand from the code – type of field named Parameter contains parametes description for stored procedure. And Row type is used to hold returns of the procedure. Let’s discuss format of procedure. When system is dealing with Diego procedures should have specialized output format. When procedure should return a result it should return name of field for Diego class and return actual data in the next row. So first row here is called “HEADER” and secodn “DATA”. In spPortalUpdate we have following section for header and data :

select‘ReturnedPortal’

select*from vwPortal where ID = @ID

As you can see header is “ReturnedPortal”. So system will look for a field named “Portal” in the class and then will try to deserialize date to specified data type (Portal here).

Parameter can hold complex types and simple types. If we are passing complex type – it will parse passed type and every property of the type will be passed as parameter. Of course it’s possible to handle parameters passing, I will discuss that feature in the next article. And if simple type (integer, string and so on) passed – system will pass to stored procedure parameter directly with a name of field.