Batch Execution in Firebird

Executing a .sql file on Firebird Database can be quite a messy job. The lack of proper documentation and support would hurt you more when encountered issues and that is what I faced when I required to do execute contends of a .Sql File. Having understood that FbBatchExecution is the command I would required, I went to write following Function in C#.
public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}

This looked reasonable, I am reading out the Contends of the File using FbScript object and using the FbBatchExecution Object for bulk queries. I am managing the whole operation using FbTransaction. All seems well until I ran it and came up with following Exception.
“Execute requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized”
After hours of browsing, it turned out to be simple actually. All I needed for to add following lines.
 BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
{
      args.SqlCommand.Transaction = fbTransaction;
};

The new Method would like following and you all set to go.
 public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
			BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
            {
                args.SqlCommand.Transaction = fbTransaction;
            };
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}

Exception Handling in Web API (Part 2): Exception Filter (Extended)

The Exception Filter implementation mentioned in the Part1 of the article is fairly simple and straightforward.  But as you start supporting more and more Exceptions, the Cyclomatic Complexity of the “OnException” method would increase because of the “if” condition nature.

A cleaner implementation of the Filter is shown below using Dictionary.

 public class DemoExceptionFilter : ExceptionFilterAttribute
 {
 public DemoExceptionFilter()
 {
 this.ExceptionDictionary = new Dictionary();
 this.ExceptionDictionary.Add(typeof(NotImplementedException), HttpStatusCode.ServiceUnavailable);
 this.ExceptionDictionary.Add(typeof(ArgumentOutOfRangeException), HttpStatusCode.BadRequest);
 }
 public IDictionary ExceptionDictionary
 {
 get;
 private set;
 }
 public override void OnException(HttpActionExecutedContext ExecutedContext)
 {

 if (ExceptionDictionary.ContainsKey(ExecutedContext.GetType()))
 {
 ExecutedContext.Response = new HttpResponseMessage(ExceptionDictionary[ExecutedContext.GetType()]);
 
 }
 else
 {
 throw new HttpResponseException( new HttpResponseMessage(HttpStatusCode.InternalServerError));
 }
 base.OnException(ExecutedContext);
 }

 }

Isn’t that cleaner ?
 

Exception Handling in Web API (Part 1): Exception Filter

The Web World, especially Web Services is fast moving towards the more abstract simpler Web API. This article is not focused on comparing Web Service and Web API, but rather it focuses on Exception Handling in Web API.

By default,  the most common exception that an API Controller raises are translated into an HTTP Status Code 500 (Internal Server Error) (HTTP Status Code ). But what if we want to customize the Error Code ? There are couple of ways to achieve it, the first part of this article focuses on Exception Filters.
An Exception Filter is probably the easiest way to handle the exception efficiently and it would handle any exception which the controller throws except for the HttpResponseException (Why HttpResponseException is not handled by Exception Filter will be discussed later).
The simplest way to create you own customized Exception Filter is to derive from ExceptionFilterAttribute Class under System.Web.Http.Filters namespace and override the OnException Method
An Example of Exception Filter is shown below.
public class DemoExceptionFilter : ExceptionFilterAttribute
{
public override void OnException( HttpActionExecutedContext ExecutedContext)
{
if (ExecutedContext.Exception is NotImplementedException)
{
ExecutedContext.Response = new HttpResponseMessage (HttpStatusCode.ServiceUnavailable);
ExecutedContext.Response.ReasonPhrase += " : Function Not Implemented";
}
base.OnException(ExecutedContext);
}
}
The code is pretty self explanatory. I have modified all the NotImlementedException to change the Http Status Code to 503 (Service Unavailable). I have also appended “Function Not Implemented”  message to the Reason Phrase . The next obvious step is to ensure our Exception Filter is used by the WebAPI Pipeline. To ensure that the DemoExceptionFilter is used globally, all we need to do is to add it to the list of filters in WebApiConfig. We use the Filters.Add Method in HttpConfiguration to do the same. For Example,
	public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services

            // Web API routes
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

            config.Filters.Add(new ExceptionManagement.DemoExceptionFilter());
        }
That’s it !!!. Now every Exception your APIController throws , with the exception of HttpResponseException , would have to go through your DemoExceptionFilter. To test the code, I have thrown an exception from my controller as seen in example below.
    public class ValuesController : ApiController
    {
        // GET api/values
        public IEnumerable  Get()
        {
            throw new NotImplementedException ();
        }

        // GET api/values/5
        public string Get( int id)
        {
            return "value" ;
        }
     }
     
Now run your application and check the response in Fiddler.
 NotImplemented
Wasn’t that easy ? Yes, but Exception Filters has its own short-comings, which would be explained in the next part. Happy Coding !!