My Technical Notes

Thursday, 23 May 2013

Passing Arrays/Lists/IEnumerable to SQL Server 2008

When defining stored procedures in SQL Server, it is quite easy to add a parameter for passing an integer or a string to it. In the following example, we have two parameters, one an integer, for which we use an INT parameter; and the other parameter is a string, for which we use the NVARCHAR(MAX) type:


CREATE PROCEDURE dbo.TestProc
 @myIntParam INT,
 @myStringParam NVARCHAR(MAX)
AS
BEGIN
 SELECT @myIntParam, @myStringParam
END

Calling it from SQL is also quite straight-forward:


EXEC dbo.TestProc @myIntParam = 0, @myStringParam = N'Tahir' 

Calling it from C# is also fairly straight-forward:


var connectionstring = "...";
DataSet ds = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionstring))
{
 connection.Open();

 SqlCommand cmd = new SqlCommand("dbo.TestProc", connection) { CommandType = CommandType.StoredProcedure };
 
 cmd.Parameters.AddWithValue("myIntParam", 0);
 cmd.Parameters.AddWithValue("myStringParam", "Tahir");

 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 adapter.Fill(ds);
}

var row = ds.Tables[0].Rows[0];
Console.WriteLine("int: {0}, string: {1}", row[0], row[1]);

However, adding a parameter to the stored procedure for a list of strings is not as easy as adding another int or string parameter. For this we first need to define a table type within SQL Server, then add a parameter to the stored procedure of this newly-defined-type, and then lastly add code to our C# application to send the list of strings as a DataTable to SqlCommand object.

The first step mentioned is to define a new table type within SQL Server, which can be used to pass a list of strings. We are defining a table type with a single column for this purpose:


CREATE TYPE dbo.NvarcharTable
AS TABLE
(
    value NVARCHAR(MAX)
);

The next step is to add the parameter to our stored procedure, which is of the above type dbo.NvarcharTable:


CREATE PROCEDURE dbo.TestProc
 @myIntParam INT,
 @myStringParam NVARCHAR(MAX),
 @myStringList AS dbo.NvarcharTable READONLY -- add this line
AS
BEGIN
 SELECT @myIntParam, @myStringParam;
 
 SELECT value FROM @myStringList; -- treat it as any other regular table
END

The next step is to pass a list of strings to the stored procedure from C#. To do this we need to create a DataTable which has the same structure as our dbo.NvarcharTable type. We then add each string as a row to this DataTable. The DataTable is then added to the SqlCommand's Parameters collection:


DataTable dt = new DataTable();
dt.Columns.Add("value", typeof(string));
foreach (string str in new[] { "Alpha", "Bravo", "Charlie" })
{
 var valueRow = dt.NewRow();
 valueRow["value"] = str;
 dt.Rows.Add(valueRow);
}

SqlParameter stringsParam = new SqlParameter("myStringList", dt)
{
 SqlDbType = SqlDbType.Structured
};

cmd.Parameters.Add(stringsParam);

I suppose we could add some diagnostic code at the end to check the return values:


Console.WriteLine("strings: {0}", string.Join(", ", ds.Tables[1].Select().Select(x => x[0])));

I tried initially to use the SQL_VARIANT type to transfer a list of things. The advantage of doing so would be that I define only a single "generic" table type, instead of creating a separate one for int and decimal and NVARCHAR etc. However, when I tried sending a list of strings across, the following SqlException occurred:


Operand type clash: nvarchar(max) is incompatible with sql_variant
The data for table-valued parameter ... doesn't conform to the table type of the parameter.

Another thought that crossed my mind is that SQL Server does not have the syntax support to define the table parameter type within the definition of the stored procedure. Having implemented a few stored procedures which have a table-value parameter, usually we define a table type for a single stored procedure rather than re-use it across stored procedures. I would have through that it would be easy for the SQL Server team to add following syntax support, which is the same as that of table-valued functions:


CREATE PROCEDURE dbo.TestProc
 @myIntParam INT,
 @myStringParam NVARCHAR(MAX),
 @myStringList TABLE (value NVARCHAR(MAX))
AS
BEGIN
 ...
END

To run the above code, 1. download the following Zip file, 2. copy the C# file to a console application, 3. run the SQL Script against a SQL Server, 4. edit the connection string within the C# file to point to the SQL Server, 5. run the code. Download: Passing_List_To_StoredProc.zip

No comments: