CsharpGears: New class for Dynamic Discovery of Stored Procedure Parameters

Yesterday I wrote a new class in the CSharpGears Framework. It is called ParameterExtractor and it's purpose is to discover the parameters of a given stored procedure and retrieve them in a collection. This class utilizes the method DeriveParameters in order to discover the parameters of the procedure and therefore, it can be only used with SQL Server unfortunately, but since SQL Server is quitte often a choice for database solution, this class might come in handy. The class also has caching capabilities, which can help saving performance since DeriveParameters method causes a roundtrip to the database, and you certainly don't want to have two accesses to the database for each query. Because of that, it uses static caching and keeps the list of parameters in a collection for further reuse.  I have to admit that I am not certain about the thread safety of this operation, it is possible that a lock statement is required as a critical region.

  My intention is to combine the ParameterExtractor and integrate it with my N-tier architecture, so that it will help me automate the parameter adding process from my business objects to the stored procedures. The framework already supports the reverse process, namely to map the datareader's columns into a list of business objects ( you can read my post about Object relational mapping if you are interested). I hope that soon my project architecture will contain clean, separated code which is also easy to write, due to the generics of C#.

  However, one should be aware of the limitations of this class, it only works with SQL Server, and of course, it needs more memory to keep the stored procedure parameters, but its greatest adventage is that it is possible to write a code that will examine a business object and it will decide alone which attributes are needed to be mapped with which parameters - AUTOMATICALLY :).

"I am thinking that I am lazy..." - David Byrne

C# RSS Builder : How to Build Really Simple Syndications

RSS has become quite popular recently, and from developer's point of view, it's a quite common requirement to implement. So I have decided to make an object-oriented approach and write a few classes that might come in handy. You can see the source code below, it is quite simple and straightforward. One note to those who haven't read my previous posts: The retrieval from the database is made by another set of classes called DatabaseEntities. These classes allow me to directly bind C# classes with SQL table columns and return lists of business objects instead of DataTables.

Back to the RSS feed, there are a few things you need to do: first you need RSS items, as by the standard of the RSS XML tags. Then you need to create a logo of the RSS feed. This is optional, but it is a nice way to offer your readers visual connection to your brand. You will also need a Stream object where the feed should be output. It could be any type of stream. For the purposes of this example, I am using a FileStream, but for a real scenario (ASP.NET) you would need to use the Response's output stream. Take a look at the code, and please tell me if it is REALLY SIMPLE :)

Writing Data Access Layer (DAL) and Business Access Layer (BAL) with CsharpGears Framework

Today I am going to show you how to write Data Access Layer (DAL) and Business Access Layer (BAL) using the CsharpGears Framework.

One of the most accepted approaches of the today's application architecture is the 3-tier architecture, or it's general version n-tier architecture. The idea is to separate the logic of the application in separate layers, each of which will be able to communicate in a standardized way with another layer. Most often, the tier that is responsible for displaying the data is called the presentation layer, which is capable of invoking the layer that holds all the business logic - namely the business layer. The business layer, however can either be invoked by the presentation layer and return the results of the execution to it. In certain situations, the business layer will need to access some amount of data in order to perform all the calculations. In that case, the business layer invokes the third layer - data layer. The data layer is responsible for retrieving the sets of data from some source - be it a database or xml file or web service - it can be literally anything.

CsharpGears Framework : Object-Relational Mapping

Reasons for Object-Relational Mapping

Since the rapid development of the relational databases and the acceptance of the Object-Oriented Programming model, a new need rose: to quickly and correctly map the persistance medium model (namely, the SQL tables) to the business objects in some high level programming language (C# for instance). Developers noticed that they invest significant amount of time writing the Data Access Layer (DAL) when starting off a new project, so they started looking for ways to decrease that time and effort. As time went by, several companies started producing software to efficiently map the SQL tables into business classes. Today, numerous of them are fairly famous, such as the NHibernate, SubSonic, Microsoft's Entity Framework, LINQ, etc. You can find more detailed list of ORM software on wikipedia.

Benefits of the ORM

CsharpGears Framework : Using Different SQL Providers

Hello, I am about to present you how easily can you connect different types of databases with the C# Gears Framework. First of all, note that the IDatabase interface implementators do not care to what type of database they are connected to, they only care about the SQL query they need to execute. For example, here is how would you use C# Gears Framework in order to connect MySQL Database with .NET:

Connecting MySQL with C#

Step 1:Download and install the MySQL provider for .NET. Simply go to MySQL's download site and create an account if you already don't have one. The installation is pretty straightforward - there is a MSI installer.

Step 2:Add the MySQL.data.dll to your project. Ordinarily create new project in Visual Studio, go to References, right click on it and choose Add Reference. Then navigate to the path where the installation of the MySQL provider resides, and choose the mentioned dll.

Step 3: Start using the MySQL data provider. Now I will paste the whole code from my Default.aspx.cs file:

CsharpGears Framework : Database Access: Output Parameters

Sometimes it is required that the stored procedures have output parameters. The CsharpGears can handle that too. There is another class in the Database namespace, that specializes working with output parameters. It is called DataBaseOutputEnabledEntity . Here is an example on how it's used:

DataBaseEntity dbEntity = new DataBaseEntity("GetProductByID");
DataBaseOutputEnabledEntity outputEnabledDbEntity = new DataBaseOutputEnabledEntity(dbEntity);
            outputEnabledDbEntity.AddCommandParameter("@NumberOfProducts", null, DbType.Int32);
            DataTable results = (DataTable)outputEnabledDbEntity.Select();
            int outputParamValue = (int)outputEnabledDbEntity.GetOuputParameterValue("@NumberOfProducts");

CsharpGears Framework : Database Access

You have worked with databases, right ? Has it happened to you, to figure it out that it would be great if you could somehow magically map your SQL tables in neat C# classes ? Well, it's not that I am discovering new continents here, but I personally think that the Database Access sublibrary will effectively and easily allow you to map your database tables into C# classes. Among the other benefits, these classes can also come in handy if you wanted to treat some SQL queries as transactions and roll them back in case of error. You can also invoke stored procedures or simply pass ad-hoc queries to the database with just a few lines of code.

But what truely makes this library awesome, is it's ability to directly return lists of business objects. You don't even have to explicitely bind the class' properties to the SQL tables (as with Attributes). The Framework only requires that the name of the public property is the same as the name of the SQL table column. Have I mentioned that the library is absolutely generic ? It can accept any types of objects, as long as they have default constructor (which could be observed as limitation, but it is so simple to write it, that is no big deal). Well, enough introductory notes, let me show you what library can really do.

DatabaseEntity
This is the basic class in this library. It provides the basic functionality such as communicating with the database by executing queries. There are several constructors available which will allow you to choose your connection string to the database, the SQL provider, the type of the query (Stored procedure or ad-hoc query) etc. The DatabaseEntity class provides the four main query types in SQL: Select, Insert, Update, Delete. Please note that the Select query returns object which is actually DataTable (boxed). You need to cast it to DataTable for the sake of reusability of the code. So here is how it works.
Say you have the following table named Product  with the following columns in the database:
ID | Description | Price

Then suppose that I wanted to retrieve all Products from the SQL table Product. I write the following procedure: