TestingMisconfused

ASP.NET WebService

Note that the original SQL calls have been commented out where they need replacing by MySQL.

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)
{
    MySqlConnection SQLconn =
        new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[
            "MySQLTestDBConnectionString"
        ].ConnectionString);
    MySqlCommand SQLcmd = new MySqlCommand(SQLstr.Replace("#", "'"), SQLconn);
    //    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("CALL " + 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)
{
    MySqlConnection SQLconn =
        new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[
            "MySQLTestDBConnectionString"
        ].ConnectionString);
    MySqlCommand SQLcmd = new MySqlCommand(SQLstr.Replace("#", "'"), SQLconn);
    //    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();
    MySqlDataAdapter da = new MySqlDataAdapter(SQLcmd);
//    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));
}

MySQL 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 MySQL, rather than relying on client-side code to manipulate the data.
Note that there are many differences between SQL Server and MySQL syntax when it comes to Stored Procedures.

/*
    MySQL version (created in Catalogs section of MySQL Administrator)
*/
CREATE PROCEDURE `SimulatedTestRun`(
    runStarted DATETIME,
    testPC VARCHAR(50),
    testBuild VARCHAR(50),
    testCode INT,
    outputMatch TINYINT(1))
BEGIN
    INSERT INTO TestRun (RunStarted,TestPC,TestBuild) 
    VALUES (runStarted, testPC, testBuild);
    SELECT MAX(RunID) FROM TestRun INTO @LastRunID;
    INSERT INTO TestResults (TestCode,RunID,OutputMatch)
    VALUES (testCode,@LastRunID,outputMatch);
END
/*
SQL Server version
*/

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

/*
MySQL version
*/
CREATE PROCEDURE `CleanSimulatedTestRun`()
BEGIN
    DELETE FROM TestRun WHERE (RunID > 1003);
    DELETE FROM TestResults WHERE (TestResultsID > 4);
END
/*
SQL Server version
*/

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.
Note here that the only change required is the name of the WebService call.

//_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: "/MySQLService.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.
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.
Nobody for info on converting SQL Server Stored Procedures to MySQL - why is that info so hard to find? I just used trial and error.