Stored procedures provide a powerful extension to C# ASP .NET by allowing us to move large portions of data manipulation logic to the database server. Stored procedures are optimized to process data and their associated logic faster than the application layer and are therefore the preferred layer for database logic processing in .NET applications. While stored procedures can be designed to accept many different kinds of parameters as input, it’s only a matter of time before you have a need to pass a C# .NET array or collection as an input parameter.
This article explains a simple method for passing an array from your C# ASP .NET application to an MSSQL stored procedure by using a bit of dyanmic SQL.
MSSQL is a favorite database platform for C# ASP .NET developers. For the most part, MSSQL has everything you need for your application. However, one particular missing feature is support for arrays as inputs to stored procedures. At first glance, one may think passing in a series of input parameters, with a pre-defined limit (such as var1, var, var3, var4) would be a suitable workaround. However, there is an easier way to accept an array of parameters.
The above idea not only limits the usefulness and flexibility of the stored procedure, it is also cumbersome to work with. By changing the series of var parameters into a single array parameter, we can process the data using SQL’s IN command.
Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.
The above stored procedure definition will accept a comma-delimited string, which we process as an array using the SQL IN command. Note, we had to use dyanmic SQL to properly form the query (which involves expanding the comma-delimited string).
Next, we need to define the method to pass the data and execute the stored procedure from C# .NET.
The first step is to convert our array of data into a comma-delimited string, which is what the stored procedure expects to receive. Depending on your data type, this code may vary. For this example, we are using a .NET collection.
The code above will create a string in the following format:
Now that the collection has been converted to a string, we can pass the value as a parameter to the stored procedure by using the following code:
There are several other methods for passing arrays to SQL stored procedures, although they deal with increasing complexity. One such method includes parsing a comma-separated list of values into a temporary table which is then joined with a main table. Another method includes the usage of XML in MSSQL and the calling of sp_xml_preparedocument and sp_xml_removedocument. Note that the XML method requires the data that is passed in to be formatted in XML. For example:
One could always create a user-defined MSSQL function to parse a list of data and pass the appropriate values as required. The actual method chosen to pass arrays in MSSQL is ultimately up to the type of data you are dealing with in the core application.
While MSSQL does not contain native support for array parameters on stored procedure, there are several methods available for passing this type of data as input from your C# ASP .NET applications. The simplest method involves formatting your data or collection as a comma-delimited string, which is passed as input to the stored procedure. The stored procedure can then use dynamic SQL to query against the input using the SQL IN command or parse/process the data further. Several other methods exist for passing arrays to stored procedures, although they differ in complexity. Such methods includes parsing lists, joining temporary tables, and using the built-in XML functionality of SQL Server.
This article was written by Kory Becker, founder and chief developer of Primary Objects, a software and web application development company. You can contact Primary Objects regarding your software development needs at http://www.primaryobjects.com