La classe .NET SqlHelper e i parametri Table-value

I parametri Table-value rappresentano una tecnica di facile realizzazione per passare una matrice di dati mono/multi dimensionale ad una stored procedure. I dati vengono passati come parametro di solo input con una struttura simile a quella di una tabella e pertanto accessibili utilizzando il tradizionale comando SELECT nella stored procedure.

SqlHelper è una delle classi disponibili nella DLL Data Access Application Block contenete codice ottimizzato per richiamare stored procedure ed inviare comandi SQL a un database SQL Server.

I passi da seguire per utilizzare un parametro Table-value in un’applicazione web .NET sono

1. nel database SQL Server
   1. dichiarare il parametron table-value
   2. creare una stored procedure che accetti anche/solamente il parametron table-value dichiarato
       
2. nell’applicazione web .NET
   1. dichiarare un SQL MetaData che corrisponda alla dichiarazione del table-value fatta nel database SQL Server (il nome assegnato al meta data e ai campi può essere diverso da quello utilizzato nel database mentre il tipo di dati deve coincidere)
   2. dichiarare una lista di SQL DataRecords e a questa aggiungere data record del tipo di meta data appena dichiarato
   3. invocare un qualsiasi metodo tra quelli messi a disposizione dalla classe SqlHelper

Segue un esempio che realizza i passi sopra riportati.

Database SQL Server

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

Applicazione web in C#.Net

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

Quando viene passato un parametron di tipo table-value ad una stored procedure utilizzando la clasee SqlHelper, il metodo ritorna con errore 8047 “Il flusso del protocollo RPC (Remote Procedure Call) TDS (Tabular Data Stream) in entrata non è corretto. Parametro con valori di tabella %d ("%.*ls"), riga %I64d, colonna %d: specificato un nome di database di lunghezza diversa da zero per il tipo di dati 0x%02X (tipo di tabella definita dall'utente). Il nome di database non è consentito con un parametro con valori di tabella. Sono validi solo i nomi di schema e di tipo”.

Questo errore si verifica perché la classe SqlHelper ottiene la lista dei parametri richiesti e il loro tipo di dati direttamente dalla stored procedure che si trova nel database SQL Server prima dell’effettiva chiamata (per intercettare in anticipo eventuali errori dovuti a discrepanze nei parametri in termini di numero e tipo di dati). Ogni tipo di dati di ciascun parametro è prefissato dal nome del database e dello schema.

Successivamente, SqlHelper prepara l’effettivo commando utilizzando i parametri forniti dall’applicazione web e i tipi di dati precedentemente ottenuti (tra i quali compare anche il table-value dichiarato) e lo invia al database utilizzando l’RPC (Remote Procedure Call).

Tuttavia, l’RPC non ammette che il tipo di dati table-value sia prefissato dal nome di database (mentre per gli altri tipi di dati questo è consentito) e solleva l’eccezione precedentemente mensionata.

Ovviare a questo problema sarebbe relativamente semplice. Basterebbe infatti rimuovere il nome del database da qualsiasi tipo di dati table-value. Purtroppo, i metodi coinvolti nella classe SqlHelper non possono essere sovrascritti (overvrite) poiché la classe è stata dichiarata sealed.

Una possibile soluzione potrebbe essere quella di scrivere la propria DLL di rettifica (tra l’altro riutilizzabile anche per altri futuri sviluppi di applicazioni web) che simuli i primi passaggi eseguiti dal metodo originale (nei quali è facilmente rimovibile il nome del database) e che invece utilizzi i metodi originali della classe SlqHelper per i restanti passaggi.

Per esempio, il metodo SqlHelper.ExecuteReader potrebbe diventare SqlHelperFix.ExecuteReaderWithTableValueParameter (SqlHelperFix è la DLL di rettifica). Il codice sarebbe lo stesso di quello utilizzato nel metodo 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);
}

I return che appaiono alla fine di metodo di rettifica richiamano quelli originali dell’SqlHelper ExecuteReader.

Bisognerà dichiarare anche due metodi privati nella DLL SqlHelperFix DLL: FixTableValueTypeNames e AssignParameterValues (in quest’ultimo verrà rimosso il nome di database dagli eventuali parametri di tipo table-value).

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)
      {
         // Questo è il codice aggiunto per rimuovere il nome di database da qualsiasi parameter on table-value
         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;
   }
}