Archive

Posts Tagged ‘ado.net’

Using SQL Table Parameters in ADO.NET

Some time is much better to use in your Store Procedures Table parameters for reduce the round-trip with the server and have better performances…. this is an example on how to do implement it.

SQL Store Procedure with a TABLE Parameter

   1: Create PROC [dbo].[getData]

   2:  

   3:  @MyTableParam ProductsTableType  READONLY ,

   4:  @InputId varchar (200)

   5:  

   6:  AS

   7:  

   8:       SELECT  *

   9:       FROM  table1 sub

  10:       

  11:       inner join @MyTableParam p on  sub.Productid = p.productId and sub.VariantId = p.VariantId

  12:       

  13:       INNER JOIN somethingelse ON sub.SubscriptionID = Subscription.ID

  14:         

  15:     where Field =@InputId

  16:       

and this is the code to call the storeProc

   1: private void AssignProductDataTableRow(DataTable products, CatalogItemsDataSet.CatalogItem catalogItem)

   2:        {

   3:            DataRow dr = products.NewRow();

   4:            dr[0] = catalogItem.ProductId;

   5:            dr[1] = catalogItem.VariantId;

   6:            products.Rows.Add(dr);

   7:        }

   8:  

   9:        public static DataTable PrepareProductsDataTable()

  10:        {

  11:            var products = new DataTable();

  12:  

  13:            var prIdCol = new DataColumn("ProductId", Type.GetType("System.String"));

  14:            var varIdCol = new DataColumn("VariantId", Type.GetType("System.String"));

  15:  

  16:            products.Columns.Add(prIdCol);

  17:            products.Columns.Add(varIdCol);

  18:            return products;

  19:        }

  20:  

  21:      using (

  22:                var conn =

  23:                    new SqlConnection(ConfigurationManager.ConnectionStrings["CustomProductData"].ToString()))

  24:            {

  25:                SqlCommand cmd = conn.CreateCommand();

  26:                cmd.CommandType = CommandType.StoredProcedure;

  27:                cmd.CommandText = "dbo.getData";

  28:  

  29:                cmd.Parameters.AddWithValue("@MyTableParam", products);

  30:                cmd.Parameters.AddWithValue("@InputId", "1");

  31:  

  32:                conn.Open();

  33:                SqlDataReader res = cmd.ExecuteReader();

  34:  

  35:  

  36:                while (res.Read())

  37:                {

  38:                 //Read Row...

  39:                 }

  40: }

Categories: DEV Tags: , ,