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");

As you can see, as usual, I first create DataBaseEntity object and bind it with the stored procedure "GetProducts" (which is not shown here, but we assume it resides on SQL server). Lets assume that that stored procedure has one output parameter named @NumberOfProducts and it stores the number of products returned by the stored procedure. (This may be trivial example, but its nature is purely for demonstration purposes). Then I create a DataBaseOutputEnabledEntity object and bind it with the DataBaseEntity I have previously created. When using the AddCommandParameter() method from the DataBaseOutputEnabledEntity class, it basically adds DbParameter-s with the direction set to Output. In order to invoke the stored procedure, the only thing that needs to be called is the Select() method. If you are wondering how are you going to read the value from the output parameter after the query execution, here is the solution: you only need to invoke the GetOutputParameterValue() with the name of the parameter. It is that easy to invoke stored procedures with output parameters using the CsharpGears Framework. If you have not downloaded it that yet, I encourage you to do it here

Little note for the software designers here: DatabaseOutputEnabledEntity basically follows the Decorator Design Pattern as its constructor takes an IDatabase object. I have made this decision so that it offers more flexibility and extensibility. In this case, that proved to be a good decision. But as I learn more and more about design patterns, it becomes clear that they should be the applied as a refactoring method, because when you are trying to develop a solution to a particular problem, you need to focus on the problem, not on some UML diagrams for patterns. In future I will write more about design patterns. That's it, for now.