TestingMisconfused

ASP.NET WebService

SQLexecuteNonQuery()

SQL commands that don't return data need to be executed as NonQuery.
I haven't implemented a wrapper for DELETE due to the inherent danger - the examples below use a stored procedure to perform a specific, limited delete.
The #s are in the parameter string because jQuery needs to pass the JSON parameters as a quoted string within a string, so I used #s to replace quotes within those parameters.

[WebMethod]
//The WebService needs to be declared as [ScriptService]
//to allow the WebMethods to be called from JavaScript
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
//NonQuery methods
public void SQLexecuteNonQuery(string SQLstr)
{
    SqlConnection SQLconn =
        new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[
            "SQLTestDBConnectionString"
        ].ConnectionString);
    SqlCommand SQLcmd = new SqlCommand(SQLstr.Replace("#", "'"), SQLconn);
    SQLconn.Open();
    SQLcmd.ExecuteNonQuery();
    SQLconn.Close();
}

SQLinsert

A wrapper for a SQL INSERT command.

[WebMethod]
[ScriptMethod]
public void SQLinsert(string SQLparm)
{
    SQLexecuteNonQuery("INSERT INTO " + SQLparm);
}

SQLexec

A wrapper for a SQL EXEC command. The stored procedure examples I have below do not return useful data, so it uses the NonQuery method.

[WebMethod]
[ScriptMethod]
public void SQLexec(string SQLparm)
{
    SQLexecuteNonQuery("EXEC " + SQLparm);
}

SQLexecuteQuery()

Performs a SQL command that does return data
Returns a DataTable (presuming the command is valid).

    
//Query methods
public DataTable SQLexecuteQuery(string SQLstr)
{
    SqlConnection SQLconn =
        new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[
            "SQLTestDBConnectionString"
        ].ConnectionString);
    SqlCommand SQLcmd = new SqlCommand(SQLstr.Replace("#", "'"), SQLconn);    DataSet ds = new DataSet();
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(SQLcmd);
    da.Fill(ds, "reading");
    SQLconn.Close();
    return ds.Tables[0];
}

DataTable2JSON()

Takes a DataTable and converts it to a JSON string - you used to be able to return a List of Dictionary types, but this is no longer allowed, so a JavaScriptSerializer is used to translate the List into JSON.
I'm only using the data for display purposes, so I avoid the hassle of translating datetime objects by converting each column value to a string (or null).

public string DataTable2JSON(DataTable dt)
{
    List<object> RowList = new List<object>();
    foreach (DataRow dr in dt.Rows)
    {
	Dictionary<string, object> ColList =
	    new Dictionary<string, object>();
	foreach (DataColumn dc in dt.Columns)
	{
	    ColList.Add(dc.ColumnName,
		(string.Empty == dr[dc].ToString()) ? null : dr[dc].ToString()); ;
	}
	RowList.Add(ColList);
    }
    JavaScriptSerializer js = new JavaScriptSerializer();
    string JSON = js.Serialize(RowList);
    return JSON;
}

SQLselect

Acts as an interface to SQL specifically for SELECT commands - if a WebMethod was offered that allowed free-format SQL commands to be run, your database would not be around for long.

public string SQLselect(string SQLparm)
{
    return DataTable2JSON(SQLexecuteQuery("SELECT " + SQLparm));
}

SQL Stored Procedures

The SQLexec() WebMethod defined in the WebService allows us to call Stored Procedures, so we can go beyond simple SQL commands and exploit the performance of SQL Server, rather than relying on client-side code to manipulate the data.

CREATE PROCEDURE dbo.SimulatedTestRun
	/*
	Insert a new TestRun record
	Use the RunID to create a new TestResults record
	*/
	@runStarted datetime,
	@testPC text,
	@testBuild text,
	@testCode int,
	@outputMatch bit
AS
	INSERT INTO TestRun (RunStarted,TestPC,TestBuild) 
	VALUES (@runStarted, @testPC, @testBuild)
	DECLARE @LastRunID int
	SELECT @LastRunID = MAX(RunID) FROM TestRun
	INSERT INTO TestResults (TestCode,RunID,OutputMatch)
	VALUES (@testCode,@LastRunID,@outputMatch)
	RETURN

CREATE PROCEDURE dbo.CleanSimulatedTestRun
	/*
	Delete latest TestRun and TestResults records
	*/
AS
	DECLARE @LastRunID int
	SELECT @LastRunID = MAX(RunID) FROM TestRun
	DELETE FROM TestRun WHERE (RunID = @LastRunID)
	DECLARE @LastTestResultsID int
	SELECT @LastTestResultsID = MAX(TestResultsID) FROM TestResults
	DELETE FROM TestResults WHERE (TestResultsID = @LastTestResultsID)
	RETURN

jQuery

$.ajax() is the best way to handle an ASP.NET WebService within jQuery. The JSON string returned is translated into a JSON object for parsing in the success: callback.
As an example here, a table is constructed using the JSON names as column headers and the values as the table data.

//jQuery method to pass a select command to SQL
//(via the WebService)
//and format the JSON result as a table
//an optional callback is provided to allow any additional work after the table is constructed
//the HTML tables are contained in divs with ids the same as the SQL table names
function getSQLtable(TableName, fnCallback) {
    $.ajax({
	type: "POST",
	url: "/mono/SQLconnect/WebService1.asmx/SQLselect",
	data: "{SQLparm: '* FROM " + TableName + "'}",
	contentType: "application/json; charset=utf-8",
	dataType: "json",
	error: function(msg){
	    $("#" + TableName).text("Sorry - can't fetch " +
		TableName + "  from the database")
	}, 
	success: function(msg) {
	    var strJSON = eval(msg.d);
	    var JSONtable = "<table><caption>" + TableName + "</caption>";
	    JSONtable += "<thead><tr>";
	    for (var field in strJSON[0]) {
		JSONtable += "<th>" + field + "</th>";
	    }
	    JSONtable += "</tr></thead>";
	    for (var i = 0, l = strJSON.length; i < l; i++) {
		JSONtable += "<tr>";
		var thisRecord = strJSON[i];
		for (var field in thisRecord) {
		    JSONtable += "<td>" + thisRecord[field] + "</td>";
		}
		JSONtable += "</tr>";
	    }
	    JSONtable += "</table>";
	    $("#" + TableName).html(JSONtable);
	    if (fnCallback){
		fnCallback();
	    }
	}
    });
}
//example uses
getSQLtable("TestBed");
getSQLtable("TestConditions");
//this passes an anonymous function as the optional callback to add a pushbutton to the table's caption
getSQLtable("TestRun ORDER BY RunID", function(){
    $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
});
//this scrolls the browser viewport so that the button that fetches
//the tables is scrolled to the top of the screen - to bring attention to the tables.
getSQLtable("TestResults", function(){
    $("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
    $("#lblSQL").fadeIn("slow");
    $("#frmSQL").removeClass("loading");
});

Calling a Stored Procedure is slightly more difficult as we've already used double and single quotes in the JSON parameter, so I use #s to define quoted string parameters for the stored procedure:

function generateTestRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
    $.ajax({//create new TestRun and TestResults records
	type: "POST",
	url: "/mono/SQLconnect/WebService1.asmx/SQLexec",
	data: "{SQLparm: 'SimulatedTestRun #" + rtRunStarted + "#,#" + rtTestPC + "#,#" +
	    rtTestBuild + "#,#" + rtTestCode + "#,#" + rtOutputMatch + "#'}",
	contentType: "application/json; charset=utf-8",
        
	success: function() {
	    getSQLtable("TestRun", function(){
		$("#TestRun caption").append(
		    "<form>" +
		    "<input id='btnSTR' type='button' value='Simulate Test Run' />" +
		    "</form>");
	    });
	    getSQLtable("TestResults", function(){
		$("#TestRun tr:last td:eq(0)").addClass("highlight");
		$("#TestResults tr:last td:eq(0)").addClass("highlight");
		$("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
	    });
	}
    });
}
$("#btnSTR").live("click",function(){
    generateTestRun("10/19/2009 6:02:00","TestVista","1.1.0.1",100000,"False");    
    return false;
});

Possible Enhancements

The length of the JSON string returned by the WebService needs to be set to a sensible limit (if you find the default inadequate) - but if you were expecting more than a screenful of results, you would page it, wouldn't you?...

Questions

You said this was for performance - why are you using eval() and <table>?

• Because I'm in control of the string being evaluated.
• Because it's tabular data - I'm not using tables for layout.

Well, it's not that fast, is it?

I've seen worse. Besides, this is an 8 year old 32-bit desktop with 1GB of RAM running 3 websites and a mailserver off Apache and IIS on 2003 Server and SQL Server 2008 inside VirtualBox simultaneously, so it ain't doing badly.

Oh, that sounds really quite good - how did you do that then?

Setting up this rig with a Windows Server and SQL Server inside a virtual machine and playing nice with Apache on Linux was not easy, believe me...

Props

Robert Robbins at williamsportwebdeveloper.com for the WebService.
Dave Ward at encosia.com for the jQuery.
Ubuntu Community docs for mod_mono installation instructions
Jonathan Pryor for the hints on connecting to SQL with mono.
David Longnecker at Ramblings of the Sleepy for the skinny on getting permissions for a SQL authenticated account to run stored procedures.