how to use table variable in Microsoft Practices EnterpriseLibrary

Topics: Enterprise Library Core, Exception Handling Application Block, General discussion
Jul 27, 2010 at 11:49 AM

Hi I got an issues while using "Microsoft Practices EnterpriseLibrary"

sql server 2008 side -

I have created one "User-defined Table Type" , and i used the "User-defined Table Type" as input parametr in the stored procedure. i have complied and run the sp. i find that it is working fine.

c# (dotnet 2008)

I have created one DataTable dt with the same structure in the DB which i created. now my probelm is how to send this variable to sp.

if its a string -> normaly i used like

_dbFactory.AddInParameter(_dbCommand,"@UserName", DbType.String, strUserName);

what about the DataTAble time ? we dont have DbType.DataTable...?

any one help me?

Jul 27, 2010 at 12:29 PM

The SqlParameter DbType should be SqlDbType.Structured. Here's a sample of using a table-valued parameter from a related thread http://entlib.codeplex.com/Thread/View.aspx?ThreadId=215754 that I hope would help. Please take note of the added comments in the code.

Table-Valued Parameter sql script

CREATE TYPE [dbo].[ProductTableType] AS TABLE(
	[PRODUCTID] [int] NULL, [ProductName] [nvarchar](500) NULL)

Application Code

            //This datatable represents your Table-Value Parameter Type created
            //placeholder for parameter values to query in the "IN" statement
            //Note: Make sure that the number and datatype of Columns added here is same with the created TVP
            DataTable dt = new DataTable("ListOfProductIds");
            dt.Columns.Add("Id", typeof(System.Int32));
            dt.Columns.Add("Name", typeof(System.String));
            dt.Rows.Add("1");
            dt.Rows.Add("2");
            
            //use SqlDatabase to utilize SqlDbType
            SqlDatabase DB = EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;


            SqlConnection connection = new SqlConnection(DB.ConnectionString);
            string sqlInsert = "SELECT * FROM [Products].[dbo].[Products]" +
                               "WHERE [ProductID] IN (SELECT ProductID FROM @TVPProductName)";
            
            //use SqlCommand instead of DbCommand
            SqlCommand sqlCommand = new SqlCommand(sqlInsert, connection);
            
            //use SqlParameter to be able to define the TypeName of the parameter
            SqlParameter tvpParam = sqlCommand.Parameters.AddWithValue(
                "@TVPProductName", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            //should be the name of your TVP
            tvpParam.TypeName = "dbo.ProductTableType";

            sqlCommand.Connection.Open();
            using (IDataReader reader = sqlCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader[1].ToString());
                }
            }
            sqlCommand.Connection.Close();
            Console.ReadLine();

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com