Introduction
The purpose of this small .aspx web app is to allow users to execute SQL Stored Procedures. It will build an input form to gather parameter information and return the results generated by the stored proc to a simple gridview or as an .xlsx file downloaded directly to the user’s PC. It allows developers to exclusively work in SQL. When the stored proc is complete, it can be made available for users to call by just calling the .aspx page and passing it the name of the Stored Proc. It was designed mainly to allow developers to create parameter-based queries in the form of SQL Stored Procedures and turn them directly over to users without any additional coding.
Background
Clients frequently request new data-extracts, usually needing to display only a data grid or to just push the results into an .xlsx file so users could perform further ops on the result set(s). To run the Stored Proc, pass its name to the page either via querystring or as a Session var. That’s all the utility needs – it will build an input form to collect parameters, populate controls with default values if there are any, validate that the parameter values input by the user, and when submitted the stored procedure will be executed. Results are sent to a raw data grid on the web page or directly downloaded into Excel.
While new development using EF and other ORM tools makes working with Stored Procedures a bit old-fashioned, a lot of companies still rely on SQL coding to handle simple data management and querying tasks. With this tool in place, client requests for new reports or data extracts can be handled without any re-compiling or coding of middle tier or data-access tiers, so no .NET code running in production needs to be modified re-tested.
Using the Code
The results from the stored procedure are in a DataSet, so they can be passed to a variety of operations – Microsoft Report Viewer, for example, or any other Control that can be bound to a DataTable. With the use of a simple extension method, the results of the Stored Procedure can be imported directly into a collection of a known data type.
I’ve included a default.aspx page that demonstrates how to call spRunStoredProc.aspx. It lists all the stored procs in the db (I’ve supplied a version of the Northwind database with some stored procs added). For any stored proc selected, a URI will be created and displayed -- navigating to the URI will run the utility and present the stored proc to the user. Also included is a simple .html page to demonstrate how the utility can be called via client-code only. All values passed to the utility – stored proc name, output type, etc., can be passed either via the query string or via session variables.
Building the Input Form Based on Stored Procedure Parameters
When the .aspx page is loaded, it gathers information on the Stored Procedure’s parameters and constructs user-input objects on a dynamic web form. The goal was to reproduce the work a developer would do if they had to construct a web form specifically for the stored proc, which is a pretty common task.
Text input is either presented as a text input box or, if a stored procedure with a specific name is found, a drop down list is created (see below). Date field parameters are be handled by a jQuery date picker, boolean or byte values by a check box, numeric input is validated as numeric, and all parameters are assumed to be required. If the stored procedure SQL defines default values for any parameters, they will be used to pre-populate the input controls on the form. This is done by using some pattern-matching code to extract default values defined in the stored procedures – it may not be 100% effective, but we have been able to find the correct default values for all the stored procedures we tested.
To present a parameter input as a drop-down list, we use simple naming conventions. So the “Country” parameter can be a drop-down list of each country value in the select statement. It is even possible to have dynamic filtering drop-downs, where user selection on one drop-down list filters the available choices on another (i.e. selecting “make” of car resets the values in the “model” field, as on car-search web sites). This is all done by creating Stored Procedures that return the appropriate lists for the drop downs, and naming them in such a way that the utility will find them at run-time.
When the stored procedure is run, the names of all the other stored procedure’s in the database are gathered into list. For each text input param, we search the list of stored procedure names for a procedure named STOREDPROC_PARAMNAME. So if we have a “@country” input parameter for the stored procedure “sp_GetCustomers”, we would search for a stored procedure named “sp_GetCustomers_@country”. If we find it, we run it and use the results to populate the drop-down list on the input form. If there are two fields, we assume the first is an “id” of the ddl item, and the second field is the “text” to display.
The “sp_GetCustomers_@country” might look like this:
CREATE PROCEDURE sp_GetCustomers_@country
AS
SELECT DISTINCT countryId, CountryName as 'Country' FROM Customers
We also wanted to allow “any values” selection to be added to drop-down list, if desired. This is achieved by changing the stored procedure creating the resultset to be bound to the drop down as follows:
CREATE PROCEDURE sp_SearchInvoices_@SalesPerson
AS SELECT DISTINCT i.Salesperson INTO #temp
from
Invoices as I
INSERT INTO #temp Values('%%')
SELECT * FROM #temp
DROP TABLE
#temp
This stored proc will create a list based on SalesPerson and add ‘%%’ to column values. The ‘%%’ will subsequently be translated by the app to show *Any* in the drop-down selection.
To create dynamically create filtering drop downs. When a drop down is created based on a correctly named stored procedure as outlined above, it can have its own input parameter(s). If this parameter matches the name of one of the other parameters in the main stored procedure being called, then the value the user selected for that parameter will be used to filter the results of the drop down, i.e., you can have a drop down selection for Country, and then one for City which accepts a parameter Country value, and the City drop-down will be built based on the users Country selection. Since the drop-down lists are set to automatically cause a post-back, the contents of the drop-down will be re-set to reflect the filtering drop-down’s value. So the stored procedure to create the city drop down will look like this:
CREATE PROCEDURE
sp_SearchInvoices_@ShipCity @ShipCountry VARCHAR(50) = '%%'
AS
SELECT DISTINCT
i.ShipCity from Invoices as I
WHERE I.ShipCountry LIKE @ShipCountry
If a value for ShipCountry is supplied, results will filtered by it. If no value is supplied, all Cities will be returned to build the drop down.
Using these naming conventions, a sophisticated data-input form can be created for any stored procedure. While this might not be the optimal way to call SQL Stored Procedures, it meets the requirement of being 100% coded within SQL, without the requirement for any dot net coding.
Installation
By default, the last connection string defined in the web.config of AspSqlQuery.aspx will be used. A connection string name can be passed in the querystring or as a session variable, so it’s possible to use multiple connections for differing calls to AspSqlQuery.aspx, so long as each connection string name is matched to a valid connection string in web.config.
The user that runs the Web Page must have rights to run the Stored Procedure on the database. The name passed to the AspSqlQuery.aspx web page has to be a valid stored procedure name. Web page cosmetics are pretty raw, so I’d suggest a bit of customization -- style sheets, master page, etc.
Summary
This tool might be useful for any enterprise that has an easier time writing SQL Stored Procedures then in coding middle tier C# dot net apps. All the coding can be done in the database, and when the Stored Procedure is complete, it can be called via the Web Form and executed without any additional development. Some development groups find themselves often writing new queries or data-extracts based on specific user requests for data, and want to be able to just write the Stored Procedure itself, add it to the DB, and not have to spend time on the client or output. Also, for systems with large numbers of Stored Procedure, this tool will allow any of them to be turned into a fully functional ad-hoc reporting web page.
We are planning to migrate the code to MVC 4 or Asp Web API. The original plan was to create an ascx control that could be dropped on any web page, but I’m not sure if we’re going to have time to implement that as well.
Here are some of the features and limitations of AspRunStoredProc utility:
- Creates multiple gridviews or Excel worksheet for each result set from stored proc.
- Gets the full text of any stored procedure
- Finds Default Value(s) for parameter(s) of stored procedure
- Auto-Generates complete input form based on stored procedure parameters
- Use of Wild Cards (will accept “%” or convert “*” to “%”) for parameter inputs.
- JQuery date picker for date params, checkboxes for boolean params.
- Param inputs validated for completeness and data type using ASP validation.
- Drop-Down lists based on selection statements in supporting procedures.
- Dynamically Filtering drop-down on other drop-down selections.
- “Select All” option for drop-down lists if desired.
Some limitations of the utility as it is now setup:
- Flat result sets only, no master-child, no drill-thrus.
- No pagination or Column sorting, although this could be added to the grid if necessary
- Exports only to Excel (.xlsx), ReportViewer, PDF or Microsoft Word could be added.
- Not tested with all data types (i.e. Binary) but most basic types should work fine.
- Will be slow for huge queries (adding pagination might help).
- No Summary/Grouping -- could be done after dataset is loaded into Excel.
Our goal was to allow the development of a reporting utility that required no work at all in compiled code -- everything can be accomplished using SQL. I hope other developers find the code interesting and helpful. Developers who don't use the utility as-is may still find the code that builds the input-form based on the SQL Stored Procedure Parameters useful in other scenarios. Any improvements or comments would be welcome, and if a new version for MVC or Web API is created, I’ll post it.
