Executing stored procedure that returns DTO using NHibernate

Lately, I was creating a BaseRepository (repository pattern) using NHibernate while creating the infrastructure that could be used by the developers who work on the DAL.

There are several stored procedures which returned some custom result sets (classes which didn't have any mappings in .hbm). The idea was to provide a generic method to the developers which can be used without implementing underlying logic each time to call these stored procedures.

So, what I did is create method that takes a sql statement or a stored procedure's name, the list of the parameters and execute the sql statement or stored procedure and return the custom result set.

With these requirements I came up with a generic method something like below:

public IList < TReturnType > ExecuteAdhocQueryAndReturn< TReturnType >(string sql, params KeyValuePair[] parms)
  where TReturnType : class
{
  using (ISession session = sessionFactory.OpenSession())
  {
using (ITransaction transaction = session.BeginTransaction())
{
 try
 {
var sqlQuery = session.CreateSQLQuery(sql);
parms.ToList().ForEach(parm => sqlQuery.SetParameter(parm.Key, parm.Value));
var transformedQuery = sqlQuery.SetResultTransformer(Transformers.AliasToBean());
var result = transformedQuery.List();
transaction.Commit();
return result;
 }
 catch
 {
transaction.Rollback();
throw;
 }
}
  }
}

And we can call this method like following:


var parms = new Dictionary();
      parms["id"] = 2;
      var userRepo = new BaseRepository();
      var users = userRepo.ExecuteAdhocQueryAndReturn("select ID, LoginName, Code from dbo.[user] where Id = :id", parms.ToArray());


Or if we had a stored procedure then we'll be doing this:


var parms = new Dictionary();
      parms["id"] = 2;
      var userRepo = new BaseRepository();
      var users = userRepo.ExecuteAdhocQueryAndReturn("exec dbo.GetUsers :id", parms.ToArray());


GetUsers is the stored procedure which takes id as parameter.

And if there is no parameter, then can we go like this:


 var userRepo = new BaseRepository();
 var users = userRepo.ExecuteAdhocQueryAndReturn("select ID, LoginName, Code from dbo.[user]");


We can use the similar concept for the stored procedures which don't return anything. What we need to do is skip the applying of Transformer and do sqlQuery.ExecuteUpdate instead.

How to: Host a WCF Service in IIS 7.5 (Visual Studio 2010)

Here is a quick list of tasks that we'll need to do for hosting a WCF Service in IIS:

• Open VS 2010 and create a new project of type WCF Service Library under Visual C#=>WCF.
• Give a name to your project say: MyFirstWcfServiceLibrary and click OK. This will create a sample working WCF service which you can run by simply pressing F5. Solution that is generated automatically is shown in figure below. We are not done as yet, are we? We have to host this service in IIS.

• Now, build the solution in Release mode. This will create the MyFirstWcfServiceLibrary.dll assembly.
• Create a new folder where you want to host your WCF service say: we want to host our service in folder named D:\MyFirstWcfServiceLibrary. Create another folder named bin copy the MyFirstWcfServiceLibrary.dll into D:\MyFirstWcfServiceLibrary\bin
• Create two files in folder: D:\MyFirstWcfServiceLibrary
• Create a .svc file named: IService1.svc (IService is the name of service created by VS) and copy the following text into it:

<% @ServiceHost Service="MyFirstWcfServiceLibrary.Service1" %>

· Create a new file named: web.config and type in the following contents into it:

     <?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.serviceModel>
        <services>
            <service behaviorConfiguration="MyFirstWcfServiceLibrary.Service1Behavior" name="MyFirstWcfServiceLibrary.Service1">
                <endpoint address="" binding="wsHttpBinding" contract="MyFirstWcfServiceLibrary.IService1" />
                <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
                <host>
                    <baseAddresses>
                        <add baseAddress="http://localhost:8000/" />
                    </baseAddresses>
                </host>
            </service>
        </services>
        <behaviors>
            <serviceBehaviors>
                <behavior name="MyFirstWcfServiceLibrary.Service1Behavior">
                    <serviceMetadata httpGetEnabled="true" />
                    <serviceDebug includeExceptionDetailInFaults="false" />
       </behavior>
            </serviceBehaviors>
        </behaviors>
    </system.serviceModel>
</configuration>

• Now, ensure that ASP.NET and WCF are correctly installed and registered.

• Register WCF by executing the following utility in console:
  

CD C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\

ServiceModelReg.exe -i -x

• Register ASP.Net by executing the following command in console: aspnet_regiis -i
• Now open Run and type inetmgr. This will open the IIS.
• Create a new Application Pool and set the setting as shown in the following figure:

• Create a new Application and set the setting as shown in the following figure (D:\MyFirstWcfServiceLibrary is where your virtual directory will be created. Remember we have MyFirstWcfServiceLibrary.dll in folder: D:\MyFirstWcfServiceLibrary\bin and web.config and service1.svc files in folder: D:\MyFirstWcfServiceLibrary):

• WCF Service has been deployed successfully!!! Now, you are ready to access your service from here:

http://localhost/MyFirstWCFService/IService1.svc

You’ll see the following screen:

Running the svcutil.exe generates two files: A code file (Service1.cs) and a configuration file (output.config)

Please feel free to post any comments.

Piya Rang Kala

Hey, Faisal its for u!

have a look at this one:

http://www.urdupoint.com/adab/YahyaKhan/7.shtml

Just got introduced to it...

So, faisal introduced me to it and it looks amazing having own place on web to share ideas, experiences etc. Looking forward to be in touch with it...