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.

//_jsSQL() a class to pass messages between Javascript and SQL
//via a WebService
function _jsSQL() {
//_ajaxSQL() is a private method used to pass SQL commands via jQuery's .ajax() method
    function _ajaxSQL(_url, _data, _error, _success) {
        $.ajax({
            type: "POST",
            url: "/WebService1.asmx/" + _url,
            data: _data,
            contentType: "application/json; charset=utf-8",
            dataType: "json",            
            error: function (msg) {
                _error(msg);
            },
            success: function (msg) {
                _success(msg);
            }
        });
    };
//_SQLtable() is a private method to handle requests to turn SQL results into HTML tables
// it allows for an optional callback function when the SQL call is successful
    function _SQLtable(_url, _data, _TableName, _fnCallback) {
        _ajaxSQL(_url, _data, 
            function () {
                _SQLtableError(_TableName);
            },
            function(msg) {
                _SQLtableSuccess(msg, _TableName, _fnCallback);
            });
    }
//_SQLtableSuccess is a private method to iterate through the JSON returned from SQL
// and construct an HTML table with a caption and header row based on the table and column names.
// The table is inserted into an HTML element with an ID based on the table name
    function _SQLtableSuccess(msg, _TableName, _fnCallback) {
        var strJSON = eval(msg.d);
        _TableName = _TableName.split(" ")[0];
        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();
        }
    }
//_SQLtableError() replaces the table with an error message if there's a problem
    function _SQLtableError(_TableName) {
        $("#" + _TableName).text("Sorry - can't fetch " + _TableName + "  from the database")
    }
//_getTable() creates a table based on the table name with an optional callback
    function _getTable(_TableName, _fnCallback) {
        _SQLtable(
            "SQLselect", 
            "{SQLparm: '* FROM " + TableName + "'}",
            _TableName,
            _fnCallback
        );
    }

//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:            

//_cleanRun() resets and scrolls to the tables displayed on this page
    function _cleanRun() {
        _ajaxSQL(
            "SQLexec", 
            "{SQLparm: 'CleanSimulatedTestRun'}",
            function () {
                alert("Can't clean Test Runs");
            },
            function() {                _getTable("TestBed");
                _getTable("TestConditions");
                _getTable("TestRun ORDER BY RunID", 
                    function () {
                        $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
                    });
                _getTable("TestResults ORDER BY TestResultsID", 
                    function () {                        $("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
                        $("#lblSQL").fadeIn("slow");
                        $("#frmSQL").removeClass("loading");
                    });
	        }                
        );
    }
//_generateRun() simulates a new test run and updates the tables
    function _generateRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
        _ajaxSQL(
            "MYSQLexec", 
            "{SQLparm: 'SimulatedTestRun #" + rtRunStarted + "#,#" + rtTestPC + "#,#" +
	            rtTestBuild + "#,#" + rtTestCode + "#,#" + rtOutputMatch + "#'}",
            function () {
                alert("Can't create new test run");
            },
            function () {
                _getTable("TestRun ORDER BY RunID", 
                    function () {
                        $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
                    });
                _getTable("TestResults ORDER BY TestResultsID", 
                    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);
                    });
            }
        );
    }
//finally, close out the class by exposing some of the methods publicly
//also return this so calls can be chained
    return {
        getTable: function (_TableName, _fnCallback) {
            _getTable(_TableName, _fnCallback);
            return this;
        },
        cleanRun: function () {
            _cleanRun();
            return this;
        },
        generateRun: function (rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
            _generateRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch);
            return this;
        }
    }
};

Then, within our onload function, we can create a _jsSQL() object and call its public methods, e.g.:

    var jsSQL = _jsSQL();
    jsSQL.cleanRun();
    jsSQL.getTable("TestBed")
        .getTable("TestConditions")
        .getTable("TestRun")
        .getTable("TestResults");
    jsSQL.generateRun("2009/10/19 6:02:00","TestVista","1.1.0.1",100000,0);    

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.