Passing Arrays to SQL Stored Procedures with C# ASP .NET

Introduction

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 Limitations and Arrays

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.

A First Glance Method for Accepting an Array

CREATE PROCEDURE [dbo].[GetData]
        @Var1 as varchar(50),
        @Var2 as varchar(50),
        @Var3 as varchar(50),
        @Var4 as varchar(50),
        @Var5 as varchar(50)
AS
BEGIN
   SELECT * FROM DATA WHERE Code = @Var1 OR Code = @Var2 OR Code = @Var3 OR Code = @Var4 OR Code = @Var5
END

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.

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

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.

CREATE PROCEDURE [dbo].[GetData]
 @MyCodes as varchar(500) = '', -- comma delimited list of codes, ie: '''ABC'', ''DEF'', ''GHI'''
AS
BEGIN
   DECLARE @query as nvarchar(500)

   set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

   exec SP_EXECUTESQL @query, N'@p_MyCodes varchar(500)', @p_MyCodes = @MyCodes
END

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).

Simple Method to Pass Array to a Stored Procedure - C# .NET Side

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.

string myCodes = string.Empty; // Initialize a string to hold the comma-delimited data as empty

foreach (MyItem item in MyCollection)
{
   if (myCodes.Length > 0)
   {
      myCodes += ", "; // Add a comma if data already exists
   }

   myCodes += "'" + item.Name + "'";
}

The code above will create a string in the following format:
‘One’,’Two’,’Three’

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:

using System;
using System.Data;
using System.Data.SqlClient;

SqlConnection MyConnection = null;
SqlDataReader MyReader = null;

try
{
   // Create the SQL connection.
   MyConnection = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))
   MyConnection.Open();

   // Create the stored procedure command.
   SqlCommand MyCommand = new SqlCommand("GetData", MyConnection);

   // Set the command type property.
   MyCommand.CommandType = CommandType.StoredProcedure;

   // Pass the string (array) into the stored procedure.
   MyCommand.Parameters.Add(new SqlParameter("@MyCodes", myCodes));

   // Execute the command
   MyReader = MyCommand.ExecuteReader();

   // ...
}
catch (Exception excep)
{
}
finally
{
   if (MyReader != null)
   {
      MyReader.Close();
      MyReader.Dispose();
   }

   if (MyConnection != null)
   {
      MyConnection.Close();
      MyConnection.Dispose();
   }
}

Other Methods to Pass Arrays to an SQL Stored Procedure

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:

<ROOT>
<Ord MyID = "123"/>
<Ord MyID = "456"/>
<Ord MyID = "789"/>
</ROOT>

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.

Conclusion

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.

About the Author

This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.

Sponsor Me