Monthly Archives: January 2014

RESTful Webservice testing using cURL on Windows

Testing RESTful webservices on Windows can be done outside of the browser using an open source command tool called cURL, which can be donwloaded here: http://curl.haxx.se/download.html After downloading the Windows version based on your operation system, remember to add the location of the cURL binary to your system’s path environment variable.

The advantage of cURL over conventional forms such as browser is the ability to automate testing tasks and execute different types of HTTP requests. See the cURL online documentation for details.

Assume you have a RESTFul webservice available locally at http://localhost:8080/controlroomws. This webservice exposes a list of users via /UserList and the details of a specific user can be retrieved via /UserList/username. Through the webservice API, you can also persist users to a back end database using an HTTP POST request.

Say we will like to persist the following user to the back end database via the webservice API:

<user role="ROLE_OPERATOR" loginName="test_login1"></user>

Submitting an HTTP POST using inline XML will not work. For example, running this on the command line will return an exception:

curl -X POST -d "<userList xmlns="urn:user"><user role="ROLE_OPERATOR" loginName="chuck_norris"></user></userList>" -H "Content-Type: application/xml" --basic --user username:password http://localhost:8080/controlroomws/UserList/

as posted in this StackOverflow thread.

To circumvent this problem create an XML file containing your data and
submit to the RESTful service as follows:

curl -X POST -d @userList.xml -H "Content-Type: application/xml" --basic --user username:password http://localhost:8080/commandcontrolws/UserList

To confirm that your data has been persisted to the underlying data store, perform a HTTP GET request that will return the list of users as follows:

curl -X GET --basic --user username:password http://localhost:8080/commandcontrolws/UserList

To return details of the specific user you just added, issue another HTTP GET command:

curl -X GET --basic --user username:password http://localhost:8080/commandcontrolws/UserList/chuck_norris
Advertisements

Configuring NHibernate to execute a Stored Procedure that returns a result set

I recently spent about 5 hours trying to get NHibernate to successfully execute a stored procedure that returns a result set in SQL Server 2012. Given that it took me less than an hour to put all the pieces together for NHibernate to execute a similar stored procedure but one that returns a scalar, I thought this would be easy. Surely did not turn out that way.

This blog post explains exactly how you need to configure NHibernate to execute a stored procedure that returns a result set.

Tools
 The tools I used were Visual Studio 2010, SQL Studio Management Express 2012 and Express Profiler v2.0.   NHibernate version is 3.3.1.4000 pulled into the project via NuGet.

Requirement
Say, you have the following stored procedure:

if OBJECT_ID ( '[dbo].[sp_get_deactivation_list]', 'P' ) is not null
    drop procedure [dbo].[sp_get_deactivation_list];
go
create procedure [dbo].[sp_get_deactivation_list]
	@companyId int,
	@startDate DateTime,
	@endDate DateTime
as
begin
	select
   	        tblRadioinfo.ID as Id,
		tblRadioinfo.Mobile_ID as MobileId,
		tblRadioinfo.Radio_Address as RadioAddress,
		tblRadioinfo.Deactivation_Date as DeactivationDate
	from tblRadioinfo
	where tblRadioinfo.Radio_Type_ID in (2, 4, 7)
	and tblRadioinfo.Company_ID = @companyId
	and tblRadioinfo.Deactivation_Date <= @endDate
	and tblRadioinfo.Deactivation_Date >= @startDate
	and tblRadioinfo.Radio_Address in (select IMEI from [airtime_cdrs] where  Effective_Date > @startDate and Effective_Date < @endDate)
<p>	ORDER BY tblRadioinfo.Deactivation_Date
end

which when executed using the following simple test in SQL Studio Management Express 2012.

declare @route_id_param as varchar(10), @start_time as datetime, @start_date as datetime, @end_date as datetime
set @start_time = GETDATE()
set @start_date = CONVERT(DATETIME,'10/26/2013',101)
set @end_date = CONVERT(DATETIME,'12/26/2020',101) 
<p>exec dbo.sp_get_deactivation_list @companyId=1, @startDate = @start_date, @endDate = @end_date;
select execution_time_in_ms = DATEDIFF(millisecond, @start_time, getdate())
GO

returns two results as follows:

Id            MobileId              RadioAddress    DeactivationDate
3              MobileID_2        300034013417890             2020-12-26 00:00:00.000
4              MobileID_3        300034012356790             2020-12-26 00:00:00.000

Now, say you will like to use NHibernate to execute this stored procedure and return the same two records.

An Approach
Assume you have the following class and interface definitions:

 public class HibernateStoredProcedureExecutor : IExecuteStoredProcedure
    {
        private readonly ISessionFactory _sessionFactory;

        public HibernateStoredProcedureExecutor(ISessionFactory sessionFactory)
        {
            _sessionFactory = sessionFactory;
        }

        public IEnumerable<TOut> ExecuteStoredProcedure<TOut>(string procedureName, IList<SqlParameter> parameters)
        {
            IEnumerable<TOut> result;

            using (var session = _sessionFactory.OpenSession())
            {
                var query = session.GetNamedQuery(procedureName);
                AddStoredProcedureParameters(query, parameters);
                result = query.List<TOut>(); 
            }

            return result;
        }
        
        public TOut ExecuteScalarStoredProcedure<TOut>(string procedureName, IList<SqlParameter> parameters)
        {
            TOut result;

            using (var session = _sessionFactory.OpenSession())
            {
                var query = session.GetNamedQuery(procedureName);
                AddStoredProcedureParameters(query, parameters);
                result = query.SetResultTransformer(Transformers.AliasToBean(typeof(TOut))).UniqueResult<TOut>();
            }

            return result;
        }

        public static IQuery AddStoredProcedureParameters(IQuery query, IEnumerable<SqlParameter> parameters)
        {
            foreach (var parameter in parameters)
            {
                query.SetParameter(parameter.ParameterName, parameter.Value);
            }

            return query;
        }
    }

  public interface IExecuteStoredProcedure
    {
        TOut ExecuteScalarStoredProcedure<TOut>(string procedureName, IList<SqlParameter> sqlParameters);
        IEnumerable<TOut> ExecuteStoredProcedure<TOut>(string procedureName, IList<SqlParameter> sqlParameters);
    }

and somewhere within your project, you are invoking an instance of this class like this;

  public IEnumerable<Activation> GetDeactivationList(int companyId, DateTime startDate, DateTime endDate)
        {
             var sessionFactory = BuildSessionFactory();
            var executor = new HibernateStoredProcedureExecutor(sessionFactory);
            var deactivations = executor.ExecuteStoredProcedure<Activation>(
              "GetDeactivationList",
              new[]
                {
                    new SqlParameter("companyId", companyId), 
                    new SqlParameter("startDate", startDate), 
                    new SqlParameter("endDate", endDate), 
                });

            return deactivations;
        }

To get this all working, you will need a mapping for your stored procedure and one for the return type of your stored procedure, if your stored procedure is returning a result set of complex types.  The mapping file for the stored procedure looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2" 
  assembly="HGT.IridiumAirtime.Service" 
  namespace="HGT.IridiumAirtime.Service.Model">
  <sql-query name="GetDeactivationList" callable="true">
    <query-param name="companyId" type="int"/>
    <query-param name="startDate" type="DateTime"/>
    <query-param name="endDate" type="DateTime"/>
    <return class="Activation">
      <return-property column="Id" name="Id" />
      <return-property column="MobileId" name="MobileId" />
      <return-property column="RadioAddress" name="RadioAddress" />
      <return-property column="DeactivationDate" name="DeactivationDate" />
    </return>
    exec [sp_get_deactivation_list] @companyId=:companyId, @startDate=:startDate, @endDate=:endDate
  </sql-query>
</hibernate-mapping>

and for the return type of our stored procedure, which is not really associated with any table in the database, is listed below.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2" 
  assembly="HGT.IridiumAirtime.Service" 
  namespace="HGT.IridiumAirtime.Service.Model">
  <class name="Activation" >
    <id name="Id">
      <generator class="identity"/>
    </id>
    <property column="MobileId" name="MobileId" />
    <property column="RadioAddress" name="RadioAddress" />
    <property column="DeactivationDate" name="DeactivationDate" />
  </class>
</hibernate-mapping>

Once you have all of the aforementioned in place, you should be able to write and execute successfully a unit test against the GetDeactivationList method. If you use the same input parameters as those used earlier you should get the same two records.

The Gotchas
Bold lines in the aforementioned code sections were stumbling points and are worth mentioning. These were missing in my original iteration and were arrived at after numerous consultations against the document and StackOverflow

Since one cannot define a Hibernate mapping file without the Id column, you must add one, even if it means nothing to your entity.

 <id name="Id">
      <generator class="identity"/>
 <id/>

An Id column defined by the above Hibernate mapping mandates its inclusion in the return type definition of your stored procedure, hence the reason of this entry in the stored procedure’s mapping file:

    <return-property column="Id" name="Id" />

Otherwise, you will get an exception similar to this:

NHibernate.Exceptions.GenericADOException: could not execute query
[ exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, @endDate=@p2 ]
Name:companyId – Value:1 Name:startDate – Value:10/26/2013 12:00:00 AM Name:endDate – Value:12/26/2020 12:00:00 AM
[SQL: exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, @endDate=@p2] —> System.IndexOutOfRangeException: Id2_0_

An Id column defined in a Hibernate mapping file also mandates a corresponding public virtual property defined on the class. So we need this:

public virtual int Id { get; set; }

in the Activation class.

Finally, since you mapping has instructed NHibernate to expect an Id column in the result set, you must ensure one is returned by the stored procedure, hence the reason for this line:

assMobileRadio.ID as Id

In Conclusion
Relatively painful getting here but it now works. Hopefully this helps out others running into a similar problem. If any of my statements are technically inaccurate, please let me know. Also, much thanks to some good folks at StackOverflow for their help.