A lightweight AJAX.NET-enabled grid control

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

public partial class AjaxGrid
{
    // for best connection pooling, ALL your SQL connections in the website should also use "MultipleActiveResultSets=True"
    // I also recommend storing the connection string in the registry (rather than web.config) - it's much easier
    // to copy websites if the machine-specific configuration is outside the website
    private const string connect = @"Trusted_Connection=true;Asynchronous Processing=True;MultipleActiveResultSets=True;Server=BETSY;Database=tackle";

    // SqlCommand dynamically builds the SQL to generate the data for the grid
    // add "TOP n" to SELECT (if appropriate), append valid freetext and field filters, and add sort order
    public static SqlCommand GridSql(SqlConnection con, GridProfileData profile, int gridId, bool includeFilterSql)
    {
        GridData  me = grid[gridId];
        SqlCommand cmd = new SqlCommand();
        StringBuilder sql = new StringBuilder();

        if (includeFilterSql)   // pack any custom filter SQL into a multiple result set
        {
            foreach (GridColumnData column in me.columns)
                if (column.customUpdateSql != null)
                    sql.Append(column.columnSelectSql);
        }
        // now build the main read SQL
        sql.Append(me.gridSql);
        if (profile.currentTableMax > 0)
            sql.Replace("SELECT", "SELECT TOP " + profile.currentTableMax.ToString());

        // add any free-text filter using parameters to prevent SQL injection
        if (me.gridFreeText != null && profile.currentFreeTextFilter.Length > 0)
        {
            sql.AppendFormat(" AND ( {0} )",me.gridFreeText);
            cmd.Parameters.Add("@freeTextFilter", SqlDbType.VarChar).Value = profile.currentFreeTextFilter;
        }

        // Add each non-null filter as a parameter to prevent SQL injection
        for (int col = 0; col < profile.columns.Count; col++)
            if (profile.columns[col].currentFilter != null)
            {
                string fldname = me.columns[col].gridDbField;
                string filterValue = profile.columns[col].currentFilter;
                switch (me.columns[col].columnType)
                {
                    case columnType.Dropdown:
                        sql.AppendFormat(" AND {0} = @{0}", fldname);
                        cmd.Parameters.Add("@" + fldname, SqlDbType.Int).Value = filterValue;
                        break;
                    case columnType.Boolean:
                        sql.AppendFormat(" AND {0} = {1}", fldname, (filterValue == "1") ? 1 : 0);
                        break;
                    case columnType.Float:
                        switch (filterValue)
                        {
                            case "-": 
                                sql.AppendFormat (" AND {0} < 0", fldname);
                                break;
                            case "+": 
                                sql.AppendFormat (" AND {0} >= 0", fldname);
                                break;
                            default:
                                sql.AppendFormat(" AND {0} = @{0}", fldname);
                                cmd.Parameters.Add("@" + fldname, SqlDbType.Float).Value = filterValue;
                                break;
                        }
                        break;
                    case columnType.Integer:
                        switch (filterValue)
                        {
                            case "-":
                                sql.AppendFormat(" AND {0} < 0", fldname);
                                break;
                            case "+":
                                sql.AppendFormat(" AND {0} >= 0", fldname);
                                break;
                            default:
                                sql.AppendFormat(" AND {0} = @{0}", fldname);
                                cmd.Parameters.Add("@" + fldname, SqlDbType.Int).Value = filterValue;
                                break;
                        }
                        break;
                    default:
                        sql.AppendFormat(" AND {0} LIKE @{0}", fldname);
                        cmd.Parameters.Add("@" + fldname, SqlDbType.VarChar).Value = filterValue + '%';
                        break;
                } 
            }
        // there is always a sort column defined
        string sort = me.columns[profile.currentSortCol].columnSortname;
        sql.AppendFormat(" ORDER BY {0}", sort);
        if (profile.currentSortDirection)
            sql.Append(" DESC");

        cmd.CommandText = sql.ToString();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        return cmd;
    }

    // Validate Cell checks a column value to insert or update
    // firstly it applies general rules (depending on the column type in AjaxGrid.xml)
    // then it calls any specific validation method which has been specified for this grid and column
    // this current implementation is a bit basic...
    private static void ValidateCell(StringBuilder response, GridProfileData profile, int gridId, int col, ref string value)
    {
        if (value == null)
            value = "";
        switch (grid[gridId].columns[col].columnType)
        {
            case columnType.Float:
            case columnType.Integer:
                if (value.Length == 0)
                    response.Append("number required\n");
                return;
        }
        response.Append(profile.ValidateProc(grid[gridId].columns[col].GridDbFieldName, value));
    }

    // UpdateCell is called when a single cell is updated
    // It first validates the value, then builds and calls the appropriate update sql
    public static string UpdateCell(int gridId, GridProfileData profile, int dbid, int column, string value)
    {
        GridData table = grid[gridId];
        GridColumnData col = table.columns [column];
        StringBuilder sql = new StringBuilder ();
        ValidateCell(sql, profile, gridId, column, ref value);
        if (sql.Length > 0)
            throw new Exception (sql.ToString());

        switch (col.columnType)
        {
            case columnType.Text: // needs quotes around updated value
                sql.AppendFormat("UPDATE {0} SET {1}='{2}' WHERE {3}={4}",
                    table.gridDbTable, table.columns [column].GridDbFieldName, value, table.gridDbIdField, dbid);
                break;
            case columnType.Boolean:
                value = (value == "1") ? "1" : "0";
                goto default;
            default:             // doesn't need quotes around updated value
                sql.AppendFormat("UPDATE {0} SET {1}={2} WHERE {3}={4}",
                    table.gridDbTable, table.columns [column].GridDbFieldName, value, table.gridDbIdField, dbid);
                break;
        }

        SqlConnection con = new SqlConnection(connect);
        con.Open();
        using (con)
        {
            SqlCommand cmd = new SqlCommand(sql.ToString(), con);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        return null;
    }

    // InsertRow inserts a complete new row into the database
    // Each column is validated before the command is sent to the database
    public static AjaxResponse UpdateNewRow(int gridId, int col, string value, GridProfileData profile)
    {
        profile.columns[col].currentNewRow = value;
        return Repaint(profile, gridId);
    }
    
    // InsertRow inserts a complete new row into the database
    // Each column is validated before the command is sent to the database
    public static AjaxResponse InsertRow(int gridId, GridProfileData profile)
    {
        GridData table = grid[gridId];
        StringBuilder response = new StringBuilder();
        for (int col = 0; col < table.columns.Count; col++)
        {
            ValidateCell(response, profile, gridId, col, ref profile.columns[col].currentNewRow);
        }

        if (response.Length > 0)
            return new AjaxResponse(-1, response.ToString());

        StringBuilder sql = new StringBuilder();
        sql.Append("INSERT into ");
        sql.Append(table.gridDbTable);
        sql.Append(" (");
        try
        {
            for (int col = 0; col < table.columns.Count; col++)
            {
                sql.Append(table.columns[col].GridDbFieldName);
                sql.Append(",");
            }
            sql.Length = sql.Length - 1; // strip trailing comma
            sql.Append(") VALUES (");
            for (int col1 = 0; col1 < table.columns.Count; col1++)
            {
                string columnText = profile.columns[col1].currentNewRow;
                switch (table.columns[col1].columnType)
                {
                    case columnType.Boolean: 
                        int val = (columnText == boolpick[0]) ? 1 : 0;
                        sql.AppendFormat("{0},", val);
                        break;
                    case columnType.Text:
                        sql.AppendFormat("'{0}',", columnText);
                        break;
                    default:
                        sql.AppendFormat("{0},", columnText);
                        break;
                }
            }
            sql.Length = sql.Length - 1; // strip trailing comma
            sql.Append(")");

            SqlConnection con = new SqlConnection(connect);
            con.Open();
            using (con)
            {
                SqlCommand cmd = new SqlCommand(sql.ToString(), con);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            return new AjaxResponse(-1, ex.Message);
        }

        profile.addingNewRow = false;
        return Repaint(profile, gridId);
    }

    // utility method to generate a csv from a SqlDataReader
    public static string CreateCSV(SqlDataReader reader, int gridId)
    {
        StringBuilder csv = new StringBuilder();
        while (reader.Read())
        {
            for (int col = 0; col < grid[gridId].columns.Count; col++)
            {
                if (col != 0)
                    csv.Append(",");
                csv.Append(reader[col+1].ToString());
            }
            csv.Append("\r\n");
        }
        return csv.ToString();
    }

    // StateObject enables asynchronous database access
    // it holds all data which will be required to render the data
    // once the asynchronous read has completed
    public class StateObject : IDisposable
    {
        private SqlConnection con;
        private SqlCommand cmd;
        private SqlDataReader reader;
        public StateObject(GridProfileData profile, int gridId, bool readHeaderOptions)
        {
            con = new SqlConnection(connect);
            con.Open();
            cmd = GridSql(con, profile, gridId, readHeaderOptions);
        }
        public IAsyncResult BeginProc(object sender, EventArgs e, AsyncCallback callback, object state)
        {
            return cmd.BeginExecuteReader(callback, state);
        }
        public SqlDataReader EndProc(IAsyncResult result)
        {
            return reader = cmd.EndExecuteReader(result);
        }
        #region StateObject Dispose logic
        ~StateObject()
        {
            Dispose(false);
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {   // slightly simplified implementation (can run safely multiple times)
            if (reader != null)
            {
                reader.Close();
                reader = null;
            }
            if (con != null)
            {
                con.Close();
                con = null;
            }
        }
        #endregion StateObject Dispose logic

    }
}