X
GO
English|Italiano

Edisfera Blog

03 March 2015

.NET SqlHelper class and Table-value parameters

-- Gaspare Carrani, General Manager & eBusiness Specialist

Table-value parameters are an easy-to-implement technique used to provide a mono/multi-dimensional array of data to a stored procedure. The data is provided in an input-only table-like parameter and therefore accessible thru standard SELECT statements from the stored procedure.

SqlHelper is one of the classes available in the Data Access Application Block DLL that contains optimized code to call stored procedures and issue SQL text commands against a SQL Server database.

The steps to use a Table-value parameter in a .NET web application are

  1. in SQL Server database
    1. declare the table-value parameter
    2. create a stored procedure accepting also/only the declared table-value parameter
       
  2. in.NET web application
    1. declare a SQL MetaData matching the table-value declared in the SQL Server database (meta data name and field names can be different, field data types must be the same)
    2. declare a list of SQL DataRecords and add to it data records of the just declared meta data type
    3. invoke one of the SqlHelper methods

This is an example implementing the above steps.

SQL Server database

  1. CREATE TYPE dbo.tbltype_ ListOfProductCodes AS TABLE (Code bigint NOT NULL PRIMARY KEY);
  2. CREATE PROCEDURE dbo.GetProductsByListOfCodes @ListOfCodes tbltype_ ListOfProductCodes READONLY AS SELECT * FROM dbo.Products WHERE Code IN (SELECT Code FROM @ListOfCodes);

C#.Net web application

  1. SqlMetaData[] tbltype_ListOfCodes = {new SqlMetaData("Code", SqlDbType.BigInt, false, true, SortOrder.Ascending, 0)};
  2. List ListOfCodes = new List();
    SqlDataRecord r = new SqlDataRecord(tbltype_ListOfCodes);
    r.SetInt64(0,product.code);
  3. System.Data.IDataReader dr = SqlHelper.ExecuteReader(ConnectionString, "dbo.GetProductsByListOfCodes", new SqlParameter("@ListOfCodes", ListOfCodes));

When supplying a table-value parameter to a stored procedure using SqlHelper class, the method returns the error 8047 “The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter %d ("%.*ls"), row %I64d, column %d: Data type 0x%02X (user-defined table type) has a non-zero length database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid”.

This error occurs because the SqlHelper class gets the required parameters and their data type directly from the stored procedure in the SQL Server database before actually calling it (to trap in advance any parameter mismatch in terms of number or data type). Each returned parameter data type is prefixed by the database name and schema.

Then, SqlHelper prepares the actual statement using the web-application-supplied parameters and the SQL-Server-obtained data type (among those there also is the declared table-value) and sends it to the database using RPC (Remote Procedure Call).

However, RPC won’t like that a table-value data type is prefixed by the database name (while for other data type this is accepted) and throws the above exception.

The fix would be quite simple. In fact, it would suffice removing the database name from any table-value data type. Unfortunately, the involved methods in the SqlHelper class cannot be overwritten, as the class is declared sealed.

An option could be to write your on fixing DLL (so that future web application developments will also benefit from this fix, as the DLL can be reused in these projects) that mimes the first steps carried out by the invoked method of that class (where the database name can be easily removed) and continue with the other left steps of the SqlHelper.

For example, method SqlHelper.ExecuteReader could become SqlHelperFix.ExecuteReaderWithTableValueParameter (SqlHelperFix is your fixing DLL). The code would be exactly the same as in the ExecuteReader:

public static SqlDataReader ExecuteReaderWithTableValueParameter(string connectionString, string spName, params object[] parameterValues)
{
   if ((connectionString == null) || (connectionString.Length == 0))
   {
      throw new ArgumentNullException("connectionString");
   }
   if ((spName == null) || (spName.Length == 0))
   {
      throw new ArgumentNullException("spName");
   }
   if ((parameterValues != null) && (parameterValues.Length > 0))
   {
      SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
      FixTableValueTypeNames(spParameterSet, connectionString);
      AssignParameterValues(spParameterSet, parameterValues);
      return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, spParameterSet);
   }
   return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}

The two return statements at the end of the fixing method invoke the standard SqlHelper ExecuteReader.

In addition, two private methods must be declared within SqlHelperFix DLL: FixTableValueTypeNames and AssignParameterValues (the latter removes the database name from the table-value data types, if any).

private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
   if ((commandParameters != null) || (parameterValues != null))
   {
      if (commandParameters.Length != parameterValues.Length)
      {
         throw new ArgumentException("Parameter count does not match Parameter Value count.");
      }
      int num3 = commandParameters.Length - 1;
      for (int i = 0; i <= num3; i++)
      {
         if (parameterValues[i] is IDbDataParameter)
         {
            IDbDataParameter parameter = (IDbDataParameter)parameterValues[i];
            if (parameter.Value == null)
            {
               commandParameters[i].Value = DBNull.Value;
            }
            else
            {
               commandParameters[i].Value = RuntimeHelpers.GetObjectValue(parameter.Value);
            }
         }
         else if (parameterValues[i] == null)
         {
            commandParameters[i].Value = DBNull.Value;
         }
         else
         {
            commandParameters[i].Value = RuntimeHelpers.GetObjectValue(parameterValues[i]);
         }
       }
    }
}
private static void FixTableValueTypeNames(SqlParameter[] commandParameters, string connectionString)
{
   SqlConnection connection = null;

   if ((connectionString == null) || (connectionString.Length == 0))
   {
      throw new ArgumentNullException("connectionString");
   }

   try
   {
      if (commandParameters != null)
      {
         // This is the added code in charge to remove the database name from any table-value parameter
         connection = new SqlConnection(connectionString);

         string DBNamePart = connection.Database + ".";

         foreach (SqlParameter s in commandParameters)
         {
            if (s.SqlDbType == SqlDbType.Structured)
            {
               if (s.TypeName.Contains(DBNamePart))
               {
                  s.TypeName = s.TypeName.Replace(DBNamePart, "");
               }
            }
         }

         connection.Dispose();

      }
   }
   catch (Exception exception1)
   {
      ProjectData.SetProjectError(exception1);
      if (connection != null)
      {
         connection.Dispose();
       }
       ProjectData.ClearProjectError();
       throw;
   }
}

Related

Not any article