SQL Server TVP Procedure Compatibility in .NET

SQL Server TVP Procedure Compatibility in .NET

By Stephanie Giovannini, OCI Software Engineer

November 2013

Introduction

When calling SQL Server stored procedures from ADO.NET, all columns of the table type used by a table-valued parameter must be provided by the calling code. Thus, adding a nullable column to the table type breaks code that doesn't know about the new column. This limitation is part of ADO.NET, and this article provides a work-around.

Illustration

To illustrate the problem, a typical development timeline will be explored.

ABC Company has a web site that allows users to record their widget collection. ABC also has an internal administrative application giving their employees more advanced widget-storage functions, so the employees can resolve the issues sometimes faced by end-users. The web site application logic is in C# (.NET 4) and the widget database is SQL Server 2008.

Add a Widget

This is the procedure that is called (by the web site and the admin app) when a new widget is entered:

  1. CREATE PROCEDURE insert_widget @product_id INT, @product_name VARCHAR(50)
  2. AS
  3. INSERT widget (product_id, product_name)
  4. VALUES (@product_id, @product_name);

From the web site, the following C# code calls the insert procedure:

  1. public void InsertWidgets(IEnumerable<Widget> widgets)
  2. {
  3.     using (var connection = new SqlConnection(_connectionString))
  4.     {
  5.         using (var command = connection.CreateCommand())
  6.         {
  7.             command.CommandType = CommandType.StoredProcedure;
  8.             command.CommandText = "insert_widget";
  9.             var productIdParam = new SqlParameter("@product_id", SqlDbType.Int);
  10.             var productNameParam = new SqlParameter("@product_name", SqlDbType.VarChar, 50);
  11.             command.Parameters.Add(productIdParam);
  12.             command.Parameters.Add(productNameParam);
  13.             connection.Open();
  14.             foreach(var widget in widgets)
  15.             {
  16.                 productIdParam.Value = widget.ProductId;
  17.                 productNameParam.Value = widget.ProductName;
  18.                 command.ExecuteNonQuery();
  19.             }
  20.         }
  21.     }
  22. }

Notice that the C# code repeatedly calls the same procedure for insertion. Although the method can accept multiple widgets, the web-site interface doesn't allow the entry of more than 5 widgets at a time.

Changes to the Procedure

The admin application needs to add more information about the widget insertion operation. This information is optional, and doesn't apply to the web site. To support this requirement, the procedure is altered:

  1. ALTER PROCEDURE insert_widget @product_id INT, @product_name VARCHAR(50), @audit_user_id INT = NULL
  2. AS
  3. INSERT widget (product_id, product_name, audit_user_id)
  4. VALUES (@product_id, @product_name, @audit_user_id);

The web site application logic does not change. The calling code above still works.

Lots of Widgets

The users of the widgets web-site are annoyed by being limited to adding 5 widgets at a time. They want to be able to add an unlimited number of widgets at once. In fact, power users would like to be able to upload a spreadsheet containing the widget information that they exported from another system.

The user interface changes to support the widget file upload, but users quickly begin to complain about the performance. The widget table is large and has several indexes defined on it. Each insert procedure has the overhead of a procedure call, transaction, table insert and index inserts.

Table-Valued Parameter

The procedure is changed to have a table-valued parameter.

  1. CREATE TYPE widget_type
  2. AS TABLE (
  3. product_id INT NOT NULL,
  4. product_name VARCHAR(50) NOT NULL,
  5. audit_user_id INT NULL);
  6.  
  7. ALTER PROCEDURE insert_widget @widget_table widget_type READONLY
  8. AS
  9. INSERT widget (product_id, product_name, audit_user_id)
  10. SELECT product_id, product_name, audit_user_id
  11. FROM @widget_table;

The web site application logic also changes. Since the web site code is being changed, the optional parameter is now added. But the code sets it null every time because it still isn’t applicable for the web site.

  1. class WidgetSqlDataRecord : SqlDataRecord
  2. {
  3.     private static readonly SqlMetaData[] _metaData;
  4.     private const int _productIdOrdinal = 0;
  5.     private const int _productNameOrdinal = 1;
  6.     private const int _userIdOrdinal = 2;
  7.  
  8.     public int ProductId
  9.     {
  10.         get
  11.         {
  12.             return GetInt32(_productIdOrdinal);
  13.         }
  14.         set
  15.         {
  16.             SetInt32(_productIdOrdinal, value);
  17.         }
  18.     }
  19.  
  20.     public string ProductName
  21.     {
  22.         get
  23.         {
  24.             return GetString(_productNameOrdinal);
  25.         }
  26.         set
  27.         {
  28.             SetString(_productNameOrdinal, value);
  29.         }
  30.     }
  31.  
  32.     static WidgetSqlDataRecord ()
  33.     {
  34.         _metaData = new[]
  35.         {
  36.             new SqlMetaData("product_id", SqlDbType.Int),
  37.             new SqlMetaData("product_name", SqlDbType.VarChar, 50),
  38.             new SqlMetaData("audit_user_id", SqlDbType.Int),
  39.         };
  40.     }
  41.  
  42.     public WidgetSqlDataRecord ()
  43.         : base(_metaData)
  44.     {
  45.         SetInt32(_userIdOrdinal, DbNull.Value);
  46.     }
  47. }
  48.  
  49. public void InsertWidgets(IEnumerable<Widget> widgets)
  50. {
  51.     using (var connection = new SqlConnection(_connectionString))
  52.     {
  53.         using (var command = connection.CreateCommand())
  54.         {
  55.             command.CommandType = CommandType.StoredProcedure;
  56.             command.CommandText = "insert_widget";
  57.             var tableParam = new SqlParameter("@widget_table", SqlDbType.Structured);
  58.             command.Parameters.Add(tableParam);
  59.             var widgetRows = widgets.Select(w => new WidgetSqlDataRecord { ProductId = w.ProductId, ProductName = w.ProductName }).ToList();
  60.             tableParam.Value = widgetRows;
  61.             connection.Open();
  62.             command.ExecuteNonQuery();
  63.         }
  64.     }
  65. }

This design mitigates the performance issue and satisfies the end users. SQL Server is now able to insert multiple rows using one procedure call and one transaction, and can optimize the index insertions.

Another Change

The admin application needs to add yet another optional parameter. Since the procedure now uses a TVP, the table type and procedure are changed as follows:

  1. ALTER TYPE widget_type
  2. AS TABLE (
  3. product_id INT NOT NULL,
  4. product_name VARCHAR(50) NOT NULL,
  5. audit_user_id INT NULL,
  6. audit_timestamp DATETIME NULL);
  7.  
  8. ALTER PROCEDURE insert_widget @widget_table widget_type READONLY
  9. AS
  10. INSERT widget (product_id, product_name, audit_user_id, audit_timestamp)
  11. SELECT product_id, product_name, audit_user_id, audit_timestamp
  12. FROM @widget_table;

The developers who make this change to the admin app don’t think that the web site needs to be changed. After all, the new column in the table type is nullable.

Problem

Unfortunately, the changes made above break the web site. The error is "System.Data.SqlClient.SqlException (0x80131904): Trying to pass a table-valued parameter with 3 column(s) where the corresponding user-defined table type requires 4 column(s)."

This error is specific to .NET. SQL Server does not have a problem with the missing column. In fact, if another SQL Server procedure were calling the TVP, and that procedure didn't provide any value for the nullable TVP column, it would be just fine.

But ADO.NET is interrogating the table type and comparing it to the SQL Data Record. Apparently, someone made the assumption that if the number of columns doesn't match, then something is wrong. This code is part of ADO.NET and isn’t accessible to the developer.

So, while adding optional parameters is a viable way to maintain backward compatibility, adding an optional column to a table type is not backward compatible. This leads to several ugly work-arounds:

Solution

This solution requires that the calling code does not need to be touched to be backward compatible when calling a procedure with a TVP. Ideally, the solution will apply to any procedure that uses TVP parameters.

The problem is in .NET, so the solution lies there as well. The calling code needs to interrogate the table type and add the missing columns at the end. This interrogation only needs to take place once, at application start-up, given the assumption that the procedure will not be altered while the application is running.

The meta-data adjuster performs the one-time adjustment:

  1. class SqlDataRecordMetaDataAdjuster
  2. {
  3.     private static readonly ILog _log = LogManager.GetLogger(typeof(SqlDataRecordMetaDataAdjuster));
  4.     private readonly string _connectionString;
  5.  
  6.     public SqlDataRecordMetaDataAdjuster(string connectionString)
  7.     {
  8.         _connectionString = connectionString;
  9.     }
  10.  
  11.     public SqlMetaData[] AdjustSqlMetaData(string tableTypeName, SqlMetaData[] knownSqlMetaData)
  12.     {
  13.         SqlMetaData[] extraColumnMetaData = null;
  14.         var commandText = string.Format("DECLARE @t {0}; SELECT * FROM @t;", tableTypeName);
  15.         try
  16.         {
  17.             using (var connection = new SqlConnection(_connectionString))
  18.             {
  19.                 using (var command = connection.CreateCommand())
  20.                 {
  21.                     command.CommandType = CommandType.Text;
  22.                     command.Text = commandText;
  23.                     DataTable schemaTable;
  24.  
  25.                     using (var datareader = command.ExecuteDataReader())
  26.                     {
  27.                         schemaTable = datareader.GetSchemaTable();
  28.                     }
  29.  
  30.                     if (schemaTable != null)
  31.                     {
  32.                         extraColumnMetaData = new SqlMetaData[schemaTable.Rows.Count - knownSqlMetaData.Length];
  33.                         for (var i = knownSqlMetaData.Length; i < schemaTable.Rows.Count; ++i)
  34.                         {
  35.                             extraColumnMetaData[i - knownSqlMetaData.Length] = CreateSqlMetaData(schemaTable.Rows[i]);
  36.                         }
  37.                     }
  38.                 }
  39.                 catch (Exception ex)
  40.                 {
  41.                     _log.Warn(string.Format("Error interrogating table type {0}", tableTypeName), ex);
  42.                 }
  43.             }
  44.             var adjustedMetaData = knownSqlMetaData;
  45.             if (extraColumnMetaData != null && extraColumnMetaData.Any() && extraColumnMetaData.All(e => e != null))
  46.             {
  47.                 adjustedMetaData = new SqlMetaData[knownSqlMetaData.Length + extraColumnMetaData.Length];
  48.                 knownSqlMetaData.CopyTo(adjustedMetaData, 0);
  49.                 extraColumnMetaData.CopyTo(adjustedMetaData, knownSqlMetaData.Length);
  50.  
  51.             }
  52.             return adjustedMetaData;
  53.         }
  54.  
  55.         private static SqlMetaData CreateSqlMetaData(DataRow schemaRow)
  56.         {
  57.             var type = (SqlDbType)(int)schemaRow["ProviderType"];
  58.             var name = (string)schemaRow["ColumnName"];
  59.             int precision;
  60.  
  61.             switch (type)
  62.             {
  63.                 // no length, no precision/scale
  64.                 case SqlDbType.BigInt:
  65.                 case SqlDbType.Bit:
  66.                 case SqlDbType.Int:
  67.                 case SqlDbType.Money:
  68.                 case SqlDbType.UniqueIdentifier:
  69.                 case SqlDbType.DateTime:
  70.                 case SqlDbType.SmallDateTime:
  71.                 case SqlDbType.SmallInt:
  72.                 case SqlDbType.SmallMoney:
  73.                 case SqlDbType.Float:
  74.                 case SqlDbType.Real:
  75.                 case SqlDbType.Timestamp:
  76.                 case SqlDbType.TinyInt:
  77.                 case SqlDbType.Date:
  78.                 case SqlDbType.NText:
  79.                 case SqlDbType.Text:
  80.                 case SqlDbType.Image:
  81.                     return new SqlMetaData(name, type);
  82.  
  83.                 // length
  84.                 case SqlDbType.Binary:
  85.                 case SqlDbType.Char:
  86.                 case SqlDbType.NChar:
  87.                 case SqlDbType.NVarChar:
  88.                 case SqlDbType.VarBinary:
  89.                 case SqlDbType.VarChar:
  90.                     var columnSize = (int)schemaRow["ColumnSize"];
  91.                     return new SqlMetaData(name, type, columnSize);
  92.  
  93.                 // precision
  94.                 case SqlDbType.Time:
  95.                 case SqlDbType.DateTime2:
  96.                 case SqlDbType.DateTimeOffset:
  97.                     precision = (short)schemaRow["NumericPrecision"];
  98.                     return new SqlMetaData(name, type, (byte)precision, 0);
  99.  
  100.                 // precision and scale
  101.                 case SqlDbType.Decimal:
  102.                     precision = (short)schemaRow["NumericPrecision"];
  103.                     var scale = (short)schemaRow["NumericScale"];
  104.                     return new SqlMetaData(name, type, (byte)precision, (byte)scale);
  105.  
  106.                 default:
  107.                     throw new InvalidOperationException(
  108.                         string.Format("SQL type {0} cannot be added as an unknown table type column.", type));
  109.         }
  110.     }
  111. }

The metadata adjuster only needs to run at application startup. It queries the table type by selecting all columns from the type. The application does not need special permission to run this select statement. If the application has permission to use the table type as a procedure parameter, then it also has permission to select all columns from a variable of that type.

The meta-data that is already known about the table type is passed to the adjuster. The adjuster only adds new columns to the end of the metadata array. The metadata construction is specific to Microsoft SQL Server. Note that the schema table column ProviderType is the integer value of the SqlDbType enumeration. The CreateSqlMetaData method carefully calls the correct constructor for each data type. ADO.NET will throw exceptions at runtime if the wrong constructor is called for a type.

SQL Data Record Changes

The SqlDataRecord must change to expose the known metadata and allow changes to the metadata.

  1. class WidgetSqlDataRecord : SqlDataRecord
  2. {
  3.     private static readonly SqlMetaData[] _metaData;
  4.     private const int _productIdOrdinal = 0;
  5.     private const int _productNameOrdinal = 1;
  6.     private const int _userIdOrdinal = 2;
  7.  
  8.  
  9.     public static SqlMetaData[] KnownMetaData
  10.     {
  11.         get
  12.         {
  13.             return _metaData;
  14.         }
  15.     }
  16.  
  17.     public static string TableTypeName
  18.     {
  19.         get
  20.         {
  21.             return "widget_type";
  22.         }
  23.     }
  24.  
  25.     public int ProductId
  26.     {
  27.         get
  28.         {
  29.             return GetInt32(_productIdOrdinal);
  30.         }
  31.         set
  32.         {
  33.             SetInt32(_productIdOrdinal, value);
  34.         }
  35.     }
  36.  
  37.     public string ProductName
  38.     {
  39.         get
  40.         {
  41.             return GetString(_productNameOrdinal);
  42.         }
  43.         set
  44.         {
  45.             SetString(_productNameOrdinal, value);
  46.         }
  47.     }
  48.  
  49.     static WidgetSqlDataRecord ()
  50.     {
  51.         _metaData = new[]
  52.         {
  53.             new SqlMetaData("product_id", SqlDbType.Int),
  54.             new SqlMetaData("product_name", SqlDbType.VarChar, 50),
  55.             new SqlMetaData("audit_user_id", SqlDbType.Int),
  56.         };
  57.     }
  58.  
  59.     public WidgetSqlDataRecord (SqlMetaData[] metaData)
  60.         : base(metaData)
  61.     {
  62.         SetInt32(_userIdOrdinal, DbNull.Value);
  63.     }
  64. }

The class still has static metadata, but the constructor uses an instance of adjusted metadata. The static metadata and table type name are made publicly available so they can be used by the metadata adjuster.

Hooking the Metadata Adjuster to the Data Accessor

Now we hook up the metadata adjuster and the table type in the data accessor. The class that calls the procedure is now detailed with the metadata members.

  1. class WidgetDataAccessor
  2. {
  3.     private readonly string _connectionString;
  4.     private readonly Dictionary<Type, SqlMetaData[]> _adjustedTypes = new Dictionary<Type, SqlMetaData[]>();
  5.  
  6.     public WidgetDataAccessor(string connectionString)
  7.     {
  8.         _connectionString = connectionString
  9.         var adjuster = new SqlDataRecordMetaDataAdjuster(_connectionString);
  10.  
  11.         _adjustedTypes.Add(typeof(WidgetSqlDataRecord),
  12.             adjuster.AdjustSqlMetaData(WidgetSqlDataRecord.TableTypeName, WidgetSqlDataRecord.KnownMetaData));
  13.     }
  14.  
  15.  
  16.     public void InsertWidgets(IEnumerable<Widget> widgets)
  17.     {
  18.         using (var connection = new SqlConnection(_connectionString))
  19.         {
  20.             using (var command = connection.CreateCommand())
  21.             {
  22.                 command.CommandType = CommandType.StoredProcedure;
  23.                 command.CommandText = "insert_widget";
  24.                 var tableParam = new SqlParameter("@widget_table", SqlDbType.Structured);
  25.                 command.Parameters.Add(tableParam);
  26.                 var widgetRows = widgets.Select(w => new WidgetSqlDataRecord(_adjustedTypes[typeof(WidgetSqlDataRecord)])
  27.                     { ProductId = w.ProductId, ProductName = w.ProductName }).ToList();
  28.                 tableParam.Value = widgetRows;
  29.                 connection.Open();
  30.                 command.ExecuteNonQuery();
  31.             }
  32.         }
  33.     }
  34. }

The adjusted metadata is kept in a dictionary in case WidgetDataAccessor has more table-valued parameters to use. In this example, an instance of the class is created at application start-up and kept in a static context. If that were not the case for the data accessor class, then a different method could be implemented to store and access the adjusted metadata from a context that is appropriately scoped.

Possible enhancements include a timed refresh of the metadata and a refresh/retry mechanism triggered by a metadata-related exception upon calling the procedure with a table-valued parameter.

Conclusion

The metadata adjuster allows SQL Server procedures using table-valued parameters the same optional parameter flexibility that single-row procedures enjoy. The improved performance of table-valued parameters can be achieved without versioning headaches.