Ajax: Creating Native JavaScript Objects From SQL - The .NET server page
(Page 3 of 4 )
I have designated response.aspx as the "server." This is because it was not intended to be directly consumed by the user; instead, it returns JSON formatted data to the front-end, ajaxtest.html, the "client." We will review the server side first.
The C# code for response.aspx resides in "response.aspx.cs." I begin modifying this file by including two classes that provide the SQL data object and convenient string array handling.
using System.Data.SqlClient;
using System.Collections.Specialized;
I then create a simple JSON object template using a multiline string. All of the text that begins with "__JTEMPL__" are tags that will be replaced when the request is processed against SQL data.
private string jsonTemplate = @"(
{
""rows"" :
[
__JTEMPL__COLUMNS__
],
""totalRows"" : __JTEMPL__TOTALROWS__,
""error"" : __JTEMPL__ERROR__,
""status"" : __JTEMPL__STATUS__
}
)
";
Text in a JSON string that has the format string : value is a name/value pair. Anything enclosed in brackets ([]) refers to an array. So in the string jsonTemplate above, there is a variable (or member, to be more correct) called "rows" that will contain an array built from the table data returned by SQL. This array will in turn contain rows of name/value pairs that represent the column and data from the SQL table (see Figure 2). This will become more clear as we work through the rest of the code. totalRows is an object member that will contain the number of rows returned, and error and status are members that will contain any errors or status messages, as required. For a more detailed reference on the JSON format, please visit json.org.
Next, I define connStr, a connection string variable which provides access to my SQL server.
private string connStr =
"server=SQL-SERVER;database=Northwind;uid=sa;password=PASSWORD";
You will need to modify SQL-SERVER and PASSWORD in the sample to match your server setup before AJAJ will run properly. If you are using Microsoft SQL, you should have a Northwind sample database available, and the queries should run.
When an .aspx file is requested from the server, the action typically begins in the Page_Load() method.
private void Page_Load(object sender, System.EventArgs e)
{
// Check parameters -- we must be given an SQL command
if ( Request.Params["sql"] == null )
{
Response.Write( SetJsonError("No SQL statement given!"),
"null" );
}
else // If we have, do the work and output
{
Response.Write( SqlToJson() );
}
}
The Page_Load() method is very simple. I merely check that the parameter "sql" has been passed to response.aspx, and if it hasn't, I call and return SetJsonError() to the requester, setting the error value to the appropriate message. The SetJsonError() method sets values properly in jsonTemplate for the "error" and "status" members, and is used any time something goes wrong, or you need to inform the user of something. This way, no matter what parameters you give or don't give to response.aspx, you will always get a properly formatted JSON object.
If the "sql" parameter has been passed, e.g. http://devweb/AJAJ/response.aspx?
sql=select%20CategoryName,Description%20from%20categories, I then call SqlToJson() and return its output to the user. SqlToJson() is where the majority of the logic of AJAJ lives. I will describe it next. (Note: ellipses "..." in the code below indicate the method continues).
private string SqlToJson()
{
// Instantiate the SQL connection
object sqlConn = new SqlConnection(connStr);
// Connect, trapping any errors into the JSON
// object, and returning
try
{
sqlConn.Open();
}
catch (Exception e)
{
return SetJsonError(
e.ToString().Replace(Environment.NewLine, " ").Replace(
""", "'").Replace("", ""), "null" );
}
// Since we have successfully opened the
// connection, create a command object
SqlCommand sqlCmd = new SqlCommand(Request.Params["sql"],
sqlConn);
...
First, I create a new SqlConnection object, and then I try to open the connection. If an exception occurs, I catch it and return it in the error portion of the JSON object, using, of course, SetJsonError(). I make sure to Replace() things like newline and double quotes (") in the exception message so that they are either removed or escaped in the JSON error string. If no exception occurs, I go ahead and create a new SqlCommand object using the connection string and the "sql" parameter.
Because SQL commands may come as query ("select") or non-query ("insert/update/delete") statements, and these are handled by different methods in the SqlCommand object, I check what type of command has been sent in the "sql" parameter.
...
// Determine what kind of SQL command
// we've been given, then act on it
string [] sqlCmdType = Request.Params["sql"].ToString().Split(null,2);
if (sqlCmdType[0].ToLower() == "select") // Data to be returned
{
...
I do this by splitting the parameter on whitespace, saving the result in a string array. If the first element in the array is "select," I know that a query has been requested. If not, then I assume a non-query, and handle the error if the SQL command is merely ill-formed.
On a "select" statement, I run the command against the SQL server, saving the data into a SqlDataReader object. Then I iterate through this object and return the data as JSON formatted elements of the "rows" member. If an error occurs, I catch it and return.
...
try
{
// Execute the SQL statement SqlDataReader
sdr = sqlCmd.ExecuteReader();
// Determine the column names, save in string array
StringCollection scl = new StringCollection();
int _columncount = sdr.FieldCount;
for (int i = 0; i < _columncount; i++)
{
scl.Add(sdr.GetName(i));
}
// Now, read SQL data and construct array of
// values to add to the JSON template
StringCollection jca = new StringCollection();
// Populate array from given column names:
while (sdr.Read())
{
string outStr = "";
foreach (string col in scl)
{
outStr += """ + col + "" : "" + sdr[col].ToString() +
"", ";
}
outStr = outStr.Substring(0, outStr.Length-2); // Remove
last comma/space
jca.Add( "{ " + outStr + " }" ); // Add to collection
}
sdr.Close();
// Create new string array, and copy collection to it:
string [] jtmplCols = new string [jca.Count];
jca.CopyTo(jtmplCols,0);
// Build output from JSON template, return
string jtRet = jsonTemplate;
jtRet = jtRet.Replace(
"__JTEMPL__COLUMNS__", String.Join("," +
Environment.NewLine, jtmplCols, 0, jtmplCols.Length));
jtRet = jtRet.Replace("__JTEMPL__TOTALROWS__",
jca.Count.ToString() );
jtRet = jtRet.Replace("__JTEMPL__ERROR__", "null"); // No
error occurred
jtRet = jtRet.Replace("__JTEMPL__STATUS__", "null" );
// Clean up
sqlCmd.Dispose();
sqlConn.Close();
return jtRet;
}
catch (Exception e)
{
return SetJsonError(
e.ToString().Replace(Environment.NewLine, " ").Replace
(""", "'").Replace("", ""), "null" );
}
...
First, in the code above, I create a SqlDataReader object, sdr, from the output of the ExecuteReader() method.
Because the "rows" member of the JSON object is an array of name/value pairs matching the column name to the column's data for a particular row (Figure 2), I have to figure out what the column names are for any given SQL statement. I use a StringCollection object, scl, to dynamically build a collection of the column names. To do this I iterate over the index of the columns in sdr, and add their names to scl using sdr.GetName().
Once I've determined the columns, I iterate through the records in sdr and build the JSON text that will represent the array elements of the member "rows." Every iteration through the while(sdr.Read()) loop, a row of SQL data is returned. I take this data and format it as a JSON array element, like { "column1_name" : "column1_value", "column2_name" : "column2_value", ... }. I add this value to another StringCollection object, jca. After I've iterated over all the records in sdr, I copy jca to a standard string array, jtmplCols. I do this so I can use String.Join() when I replace the template tags in jsonTemplate.
Next I create a local string named jtRet from jsonTemplate and replace all the template tags with the appropriate data. __JTEMPL__COLUMNS__ is replaced by the joined data in the string array jtmplCols, using comma plus newline as the delimiter. String.Join() is a convenient way of ensuring that there is no trailing delimiter after the last element. I use jca for one more thing, and that is to set the JSON member "totalRows" to the number of rows returned from SQL. At this point we've encountered no errors, so I set "error" and "status" to null, and return.
If the "select" statement above is sent to response.aspx, the following JSON data is returned and ready for consumption by the JavaScript code.
Figure 2 ("View Source" of response.aspx)
If a non-query SQL statement is passed via the "sql" parameter, I call the ExecuteNonQuery() method of the SqlCommand object. This returns a status value stating whether the command was successful. I use this value to set the "status" member of the JSON object before returning.
try
{
string ret = SetJsonError(
"null", sqlCmd.ExecuteNonQuery().ToString() );
// Clean up
sqlCmd.Dispose();
sqlConn.Close();
return ret;
}
catch(Exception e)
{
return SetJsonError(e.ToString().Replace(Environment.NewLine, " ").Replace(""", "'").Replace("", ""), "null" );
}
Exceptions, of course, are caught and propagated to the JSON object. This gives you excellent information at the client level. For instance, if you pass response.aspx a bad SQL statement, the detailed exception that is generated in .NET would end up in the alert() output of ajaxtest.html.
Next: The Ajax client page >>
More ASP.NET Articles
More By James Robson
