Web Server handler class to pass IIS http requests to SQL Server stored procedures

Question

This is a c# class I use to implement web database applications with Microsoft IIS and Microsoft SQL server. It passes the http context to a selected SQL stored procedure, and writes a response based on the result sets generated by the stored procedure. Thus the entire application is coded as SQL stored procedures, apart from this c# code.

using Data = System.Data;
using Net = System.Net;
using String = System.String;
using Object = System.Object;

/* WebServer which runs under IIS, passing requests to SQL server. Typical web.config file to pass all requests to WebServer is:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.web>
    <customErrors mode="Off"/>
  </system.web>
  <system.webServer>
    <handlers>
      <add name="WebServer" path="*" verb="*" type="WebServer" />
    </handlers>
  </system.webServer>
</configuration>
Typical SQL declaration of tables passed to SQL:
CREATE TYPE [perfect].[InfoT] AS TABLE( Kind int NOT NULL, Name nvarchar(100) NOT NULL, Value nvarchar(max) NOT NULL,
  PRIMARY KEY ( Name, Kind )
)
CREATE TYPE [perfect].[FileT] AS TABLE( id int NOT NULL, Name varchar(50) NOT NULL, ContentLength int NOT NULL,
  ContentType nvarchar(max) NULL, FileName nvarchar(200) NOT NULL, [File] image NULL, PRIMARY KEY ( id ) )
)
*/

public class WebServer : System.Web.IHttpHandler 
{

  private static Data.SqlClient.SqlConnection GetConn( ) 
  {
    return new Data.SqlClient.SqlConnection
    ( "Initial Catalog=redacted;Data Source=(Local);Max Pool Size=500;User Id=redacted;Password=redacted" );
  }

  public void ProcessRequest( System.Web.HttpContext ctx ) 
  {  
    // Each http request is handled by two SQL procedure calls. The first gets the name of the handling procedure, the second computes the response.

    try 
    { 
      using (  Data.SqlClient.SqlConnection sqlconn = GetConn() ) 
      using ( Data.SqlClient.SqlCommand cmd = new Data.SqlClient.SqlCommand( "perfect.GetHandler", sqlconn ) )
      {
        Data.SqlClient.SqlParameter p = null;

        { /* Set up table of info to be passed to stored procedure */
          Data.DataTable t = new Data.DataTable( );

          t.Columns.Add( "Kind", typeof( int ) );
          t.Columns.Add( "Name", typeof( string ) );
          t.Columns.Add( "Value", typeof( string ) );

          AddToDataTable( t, 0, ctx.Request.QueryString );
          AddToDataTable( t, 1, ctx.Request.Form );
          AddToDataTable( t, 2, ctx.Request.Cookies );

          t.Rows.Add( 3, "Host", ctx.Request.Url.Host );
          t.Rows.Add( 3, "Path", ctx.Request.Path );         
          t.Rows.Add( 3, "PathAndQuery", ctx.Request.Url.PathAndQuery );
          t.Rows.Add( 3, "IpAddress", ctx.Request.UserHostAddress );

          p = cmd.Parameters.AddWithValue( "@Info", t );
          p.SqlDbType = Data.SqlDbType.Structured; 
        }

        sqlconn.Open();
        cmd.CommandType = Data.CommandType.StoredProcedure;
        cmd.CommandText = ( string ) cmd.ExecuteScalar();

        if ( ctx.Request.Files.Count > 0 )
        {
          Data.DataTable ft = GetFileTable( ctx.Request.Files );
          p = cmd.Parameters.AddWithValue( "@Files", ft ); 
          p.SqlDbType = Data.SqlDbType.Structured;
        }

        Data.DataSet ds = new Data.DataSet( );
        using ( Data.SqlClient.SqlDataAdapter da = new Data.SqlClient.SqlDataAdapter( cmd ) )
        {
          da.Fill( ds );
        }

        // Interpret the dataset

        ctx.Response.ContentType = "text/html";
        String ShowRecordCount = null;
        for ( int i = 0; i < ds.Tables.Count; i += 1 )
        {
          Data.DataTable t = ds.Tables[i];
          if ( ShowRecordCount != null )
          {
            PutUtf8( ctx, ShowRecordCount + t.Rows.Count );
            ShowRecordCount = null;
          }
          for ( int j = 0; j < t.Rows.Count; j += 1 )
          { 
            Data.DataRow r = t.Rows[j];
            int code = 0; 
            Object value = r[0];
            if ( r.ItemArray.Length > 1 ) 
            {
              code = (int) value;
              value = r[1];
            }

            if ( code == 0 ) PutUtf8( ctx, (string) value );
            else if ( code == 1 ) ctx.Response.ContentType = (string) value; 
            else if ( code == 2 )
            {
              byte[] b = (byte[]) value;
              ctx.Response.OutputStream.Write( b, 0, b.Length );
            }
            else if ( code == 4 ) ctx.Response.Expires = (int) value;
            else if ( code == 14 ) ctx.Response.StatusCode = (int) value;
            else if ( code == 15 ) ctx.Response.Redirect( (string) value );
            else if ( code == 16 )
            {
              System.Web.HttpCookie ck = new System.Web.HttpCookie( (string) value, (string) r[2] );
              String Expires = (string) r[3];
              if ( Expires != "" ) ck.Expires = System.DateTime.Parse( Expires );
              ctx.Response.Cookies.Add( ck );
            }
            else if ( code == 17 ) ShowRecordCount = (string)value;
          }          
        }
      } 
    }
    catch ( System.Exception e )
    {
      ctx.Response.Write( e );
    }
  }

  public bool IsReusable { get { return true; } }

  private void AddToDataTable( Data.DataTable dt, int Kind, System.Collections.Specialized.NameValueCollection nvc )
  { 
    foreach ( string key in nvc.Keys ) 
      if ( key != null ) dt.Rows.Add( Kind, key, nvc[key] ); 
  }

  private void AddToDataTable(  Data.DataTable dt, int Kind, System.Web.HttpCookieCollection nvc )
  {
    foreach ( string key in nvc.Keys ) 
    {
      dt.Rows.Add( Kind, key, nvc[key].Value );
    }
  }

  private Data.DataTable GetFileTable( System.Web.HttpFileCollection fc )
  {
    Data.DataTable ft = new Data.DataTable();
    ft.Columns.Add( "id", typeof(int) );
    ft.Columns.Add( "Name", typeof(string) );
    ft.Columns.Add( "ContentLength", typeof(int) );
    ft.Columns.Add( "ContentType", typeof(string) );
    ft.Columns.Add( "FileName", typeof(string) );
    ft.Columns.Add( "File", typeof(byte[]) );
    for ( int id = 0; id < fc.Count; id += 1 )
    {
      System.Web.HttpPostedFile pf = fc[ id ];
      int length = pf.ContentLength;
      byte [] bytes = new byte[ length ];
      pf.InputStream.Read( bytes, 0, length );
      ft.Rows.Add( id, fc.GetKey(id), length, pf.ContentType, pf.FileName, bytes );
    }
    return ft;
  }

  // Output

  private byte [] EncBuffer = new byte[512];

  private static byte[] GetBuf( int need )
  { int n = 512; while ( n < need ) n *= 2; return new byte[n]; }

  private void PutUtf8( System.Web.HttpContext ctx, String s )
  {
    int len = s.Length;
    int need = System.Text.Encoding.UTF8.GetMaxByteCount( len );
    if ( need > EncBuffer.Length ) EncBuffer = GetBuf( need );
    int nb = System.Text.Encoding.UTF8.GetBytes( s, 0, len, EncBuffer, 0 );
    ctx.Response.OutputStream.Write( EncBuffer, 0, nb );
  }

  // Logging

  static WebServer()
  {
    System.AppDomain cd = System.AppDomain.CurrentDomain;
    cd.UnhandledException += new System.UnhandledExceptionEventHandler( LogException );
    Log( "Unhandled exception handler set" );
  }

  static void LogException( object sender, System.UnhandledExceptionEventArgs args ) 
  {
    System.Exception e = (System.Exception) args.ExceptionObject;
    Log( "Unhandled exception: " + e.ToString() );
  }

  static void Log( String message )
  {
    using ( Data.SqlClient.SqlConnection sqlconn = GetConn() )
    {
      using ( Data.SqlClient.SqlCommand cmd = new Data.SqlClient.SqlCommand( "perfect.Log", sqlconn ) )
      {
        cmd.CommandType = Data.CommandType.StoredProcedure;
        Data.SqlClient.SqlParameter p = cmd.Parameters.AddWithValue( "@Message", message );
        p.SqlDbType = Data.SqlDbType.NVarChar; 
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
      }
    }
  }

} // End class WebServer

SqlDataAdapter, SqlCommand, SqlConnection all implement IDisposable and should be disposed. — Brad M
– Brad M, Commented Apr 9, 2019 at 20:28
@BradM Thanks, it's interesting the Microsoft examples ( e.g. docs.microsoft.com/en-us/dotnet/api/… ) only dispose SqlConnection. — George Barwood
– George Barwood, Commented Apr 10, 2019 at 4:16
These examples are written by humans too - they might have made a mistake or they have purposely ommited it for brevity. Who knows. — t3chb0t
– t3chb0t, Commented Apr 10, 2019 at 7:43
DataSet and DataTable also implement IDisposable. My suspicion is that it's only important to dispose of SqlConnection, and with the others it makes no difference, and that's why the Microsoft examples only dispose SqlConnection. But it's only a suspicion, so I decided to Dispose everything that is disposable! — George Barwood
– George Barwood, Commented Apr 10, 2019 at 20:59

Pieter Witvoet · Accepted Answer · 2019-04-24 15:36:23Z

Main issues:

If the main purpose of this webserver is to call stored procedures, then I'd expect to see a CallStoredProcedure method somewhere. That should make it easier to understand the purpose of the code, and it lets you clean up ProcessRequest, so it can call procedures and prepare a response without getting bogged down in the details.
There's no documentation about what sort of requests this webserver can handle, and how they should be formatted. The same goes for responses. Maybe you've got documentation elsewhere? Personally I prefer to keep it close to the code itself, to increase the likelyhood that it will be kept in sync.
It's difficult to discern the meaning of kind and code. I'd use enums, or at least named constants, to make it apparent what values the code expects to handle, and what the meaning of those values is.

Inconsistencies:

There are a lot of inconsistencies in your code:

Some type names use an alias, others are written out fully.
Sometimes you're using C# aliases for basic types like string and object, sometimes not.
Sometimes an inner scope is indented and put on a separate line, sometimes it's put on the same line.
Sometimes an inner scope is surrounded by braces, sometimes not.
Some names are abbreviated, some are not.
Most parameter names are written in camelCase, as is usual, but a few are not.

All of this makes the code harder to read and understand than it needs to be. Here's what I would change:

Unless you have a specific reason for using namespace aliases the way you do, I'd do what everyone else does: using System;, using System.Data;, using System.Data.SqlClient;, and so on. With that, you don't need to write fully qualified names anywhere, which reduces clutter.
Most people use string and object for variables, parameters and the like, and String and Object when they're calling a static method on one of those types.
Always putting inner scopes on a separate line, and indenting them, makes control flow easier to see.
Regarding braces, some people recommend always use braces, no matter what, to prevent certain kinds of bugs. I think that's a bit excessive for conditional return and throw statements, but I do try to be consistent.
GetConnection, table, row, context, dataTable, collection, fileCollection and so on should be easier to understand than GetConn, t, r, ctx, dt, nvc and fc, without being too long for comfort.
Fields, parameter names and local variables are normally written in camelCase. PascalCase is used for type, method and property names.

Other notes:

Duplicate type annotations can be removed by relying on type inference: var table = new DataTable(); is equivalent to DataTable table = new DataTable();.
DataSet.Tables and DataTable.Rows can be iterated directly with foreach.
IsReusable can be simplified to public bool IsReusable => true;.
UnhandledException += new UnhandledExceptionEventHandler(LogException); can be simplified to UnhandledException += LogException;.
"Unhandled exception: " + ex.ToString() can be simplified to $"Unhandled exception: {ex}".

Thanks for the answer. There is no restriction on the requests or the responses, this is just a "dumb" adapter layer which passes any request to an SQL proc, and the response back to the http client. The SQL execution happens when ExecuteScalar and Fill are called, perhaps that could be explained in a comment. — George Barwood
– George Barwood, Commented Apr 24, 2019 at 17:20

Stack Exchange Network

Web Server handler class to pass IIS http requests to SQL Server stored procedures

1 Answer 1

Main issues:

Inconsistencies:

Other notes:

You must log in to answer this question.

Hot Network Questions

Web Server handler class to pass IIS http requests to SQL Server stored procedures

1 Answer 1

Main issues:

Inconsistencies:

Other notes:

You must log in to answer this question.

Related

Hot Network Questions