TVP in SQL Server

Table-Valued Parameters (TVP) in SQL Server:

1. Create Type [TVP_Int]:


USE [DB01]
GO

CREATE TYPE [dbo].[TVP_Int] AS TABLE(
    [Val] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Val] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

 

SSMS : programmability => Types => User-Defined Table Types;

 
2. Stored Procedure:


CREATE PROCEDURE [dbo].[List_TestInfo] 
  @IDs As TVP_Int Readonly

AS
BEGIN

  SET NOCOUNT ON;

  SELECT 
    ID, Name 
  FROM
    TestInfo WITH(NOLOCK) 
  WHERE 
    ID IN (SELECT Val FROM @IDs)

END

 

3. Test SP:


DECLARE @Tbl AS [TVP_Int];
INSERT INTO @Tbl (Val) VALUES (1001)
INSERT INTO @Tbl (Val) VALUES (1002)
INSERT INTO @Tbl (Val) VALUES (1003)

EXEC [List_TestInfo]
    @IDs = @Tbl 

 

4. (C#) Convert SqlParameter (List to DataTable):


private DataTable ToDataTable<T>(List<T> list)
{
  DataTable t = new DataTable();
  t.Columns.Add("Val", typeof(T));

  foreach (var i in list)
  {
    t.Rows.Add(i);
  }

  return t;
}

 

Reference:

1) SQL 2008 TVP資料匯入之火力展示 https://blog.darkthread.net/blog/import-with-sql-tvp
2) SqlBulkCopy Vs. TVP Vs. XML Parameter Vs. Individual Insert Procs for 20-200 Inserts? https://stackoverflow.com/questions/8618261/sqlbulkcopy-vs-tvp-vs-xml-parameter-vs-individual-insert-procs-for-20-200-ins
3) Table-Valued Parameters https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

 

EXEC against SQL server database

EXEC against SQL server database:

Stored Procedure:


USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_Name_1]
  @Param1 INT,
  @Param2 VARCHAR(20),
  @RtnCode INT OUTPUT,
  @RtnMsg VARCHAR(100) OUTPUT
AS
BEGIN

  SET NOCOUNT ON;

  SELECT @RtnCode = 0, @RtnMsg = ''

  IF (@@ERROR <> 0)
    BEGIN
      SELECT @RtnCode = @@ERROR, @RtnMsg = '...'
    END

END

 

Utility:


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

namespace Test.Util
{
  public class Database
  {
    private int _timeout;
    private string _connStr;
    private SqlConnection _sqlConn;
    private SqlDataAdapter _sqlDataAdapter;

    public SqlConnection Connection
    {
      get
      {
        return _sqlConn;
      }
    }

    public Database(string connStr, int timeout = 5)
    {
      _timeout = timeout;
      _connStr = string.Format("{0}; Connection Timeout={1};", connStr, timeout);
      _sqlConn = new SqlConnection(_connStr);
    }

    public DataTable ExecStoredProcedure(string spName, SqlParameter[] paramArray)
    {
      DataTable result;

      try
      {
        Open();

        //#region If input SQL string
        //_sqlDataAdapter = new SqlDataAdapter(sqlStr, _sqlConn);
        //new SqlCommandBuilder(_sqlDataAdapter);
        //#endregion

        #region If input params
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = (_timeout > 0) ? _timeout : cmd.CommandTimeout;
        cmd.Connection = Connection;
        cmd.CommandText = spName;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(paramArray);
        _sqlDataAdapter = new SqlDataAdapter(cmd);
        #endregion

        DataTable table = new DataTable();
        _sqlDataAdapter.Fill(table);
        result = table;
      }
      catch (SqlException ex)
      {
        throw ex;
      }
      finally
      {
        Close();
      }

      return result;
    }

    private void Open()
    {
      try
      {
        if (_sqlConn.State != ConnectionState.Open)
        {
          _sqlConn.ConnectionString = _connStr;
          _sqlConn.Open();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }

    private void Close()
    {
      try
      {
        if (_sqlConn.State == ConnectionState.Open)
        {
          _sqlConn.Close();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }
  }
}

 

Test code:


public DataTable Test(int param1, string param2, out int rtnCode, out string rtnMsg)
{
  rtnCode = 0;
  rtnMsg = string.Empty;
  DataTable result = null;

  SqlParameter paramRtnCode = new SqlParameter("@RtnCode", SqlDbType.Int) { Direction = ParameterDirection.Output };
  SqlParameter paramRtnMsg = new SqlParameter("@RtnMsg", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output };

  SqlParameter[] paramArray = new SqlParameter[]
  {
    new SqlParameter("@Param1", param1),
    new SqlParameter("@Param2", param2),
    paramRtnCode,
    paramRtnMsg
  };

  Database db = new Database(decryptedConnStr);

  try
  {
    result = db.ExecStoredProcedure("SP_Name_1", paramArray);
  }
  catch (Exception ex)
  {
    _logger.Error(ex);
  }

  rtnCode = (int)paramRtnCode.Value;
  rtnMsg = paramRtnMsg.Value as string;

  return result;
}

 

Reference:

https://forums.asp.net/t/1798529.aspx?DataAdapter+vs+SqlCommand+or+DataAdapter+SqlCommand+vs+Impact+on+Performance