Table value parameter with .net (real world example) Part 01

hi all

these days dark clouds gives free bath chance while are traveling by bike. Happy feeling when my boots are getting wet.

In this post that I'm going to demonstrate another cool feature of .net with sql server. how to send bulk of data (ado.net) as parameter to stored procedure?  is it clear about the topic. if it's not here the scenario.

Suppose we're developing procurement systems which generate the purchase order .I'll take a small part of the system.There're lot of article in internet about the topic but I wanted to give it with real life example to get the idea.

In above has mentioned the tables which has one to - many relationship, it means one po has many items (simple example).in earlier what I did was when saving po , one sp for po and another sp for po details.

here is the code snippet c#.

first fill the user objects 

            List<PODetails> lstOfPoDetails = new List<PODetails>();

            PODetails pod;

            pod = new PODetails() {POItem="ABC",Qty=22 };

            lstOfPoDetails.Add(pod);

            pod = new PODetails() { POItem = "xyz",Qty=4 };

            lstOfPoDetails.Add(pod);

            PO po = new PO()

            {

                PODate = DateTime.Now,

                CreatedUser = 33,

                lstPODetails = lstOfPoDetails

            };

Save user objects to database

 using (var Ts=new  TransactionScope())

            {

                using (var con=new SqlConnection("con is here"))

                {

                    //First Save po get the po id to pass the po deatils

                    SqlCommand com;

                    com = new SqlCommand("InsertPO", con);

                    com.Parameters.AddWithValue("@podate", po.PODate);

                    com.Parameters.AddWithValue("@user", po.CreatedUser);

                    #region Get the POID to pass the po detils

                    SqlParameter para = new SqlParameter("@poid", 0);

                    para.Direction = ParameterDirection.InputOutput;

                    com.Parameters.Add(para);

                    com.ExecuteNonQuery();

                    int poID = (int)com.Parameters["@poid"].Value;//Task one completed

                    //Save po details with po id

                    foreach (PODetails p in po.lstPODetails)

                    {

                        com = new SqlCommand("InsertPODetails", con);

                        com.Parameters.AddWithValue("@poid", poID);

                        com.Parameters.AddWithValue("@item", p.POItem);

                        com.Parameters.AddWithValue("@Qty", p.Qty);

                        com.ExecuteNonQuery();

                    }

                    #endregion

                    Ts.Complete();

                }

            }

nothing wrong above code   ,but we have to specifically work with the stored procs, one for po and another for podetails.podeatils has iteration that calls to proc for its each iterations. This is a disadvantage of this method (performance issue).to alternative to this approach , sql serer database has table value parameter to accomplish this task it provides more efficient and less code approach.

accomplish this task in Table Type parameter in sql server.

In Sql server database under the programmability -> Types has User Defined Table Types

here are the script for the table
CREATE TYPE [dbo].[type_po] AS TABLE(
[poID] [int] NULL,
[CreatedDate] [datetime] NULL,
[poDetID] [int] NULL,
[Item] [varchar](50) NULL,
[qty] [decimal](18, 4) NULL
)

we have to use the create type script to create type table.

here are the script of insert po & podetails
create proc InsertPOs
-- which accepts one table value parameter. 
-- It should be noted that the parameter is readonly
@poType   dbo.typepo readonly

as
begin
declare @poID int

--Insert PO record and get poid
INSERT INTO  [dbo].[PO]  ([CreatedDate])    
(select top 1 [CreatedDate] from @poType)

set @poID=(select IDENT_CURRENT('PO'))

-- Insert PODetail reference with poid
INSERT INTO  [dbo].[PODetails] (poID,Item,qty)
select  @poID,Item,qty  from @poType
end

database part is done.
1)create type table
2)using that type table create proc to insert po

now we'll move on to c# side.now create DataTable (C#) mapping to typepo Type table.

//Create DataTable matching with the Type Table column names and data types
            //CREATE TYPE type_po AS TABLE 
            //(
            // [poID] [int]  ,
            // [CreatedDate] [datetime] ,
            // [poDetID] [int]  ,
            // [Item] [varchar](50) ,
            // [qty] [decimal](18, 4) 
            //)

            DataTable type_po = new DataTable();
            type_po.Columns.Add("poID", typeof(int));
            type_po.Columns.Add("CreatedDate", typeof(DateTime));
            type_po.Columns.Add("poDetID", typeof(int));
            type_po.Columns.Add("Item", typeof(string));
            type_po.Columns.Add("qty", typeof(decimal));

            DataRow dr;
            dr = type_po.NewRow();
            dr["poid"] = 0;
            dr["CreatedDate"] = DateTime.Now;
            dr["podetid"] = 0;
            dr["item"] = "ABC";
            dr["qty"] = 100;
            type_po.Rows.Add(dr);

            dr = type_po.NewRow();
            dr["poid"] = 0;
            dr["CreatedDate"] = DateTime.Now;
            dr["podetid"] = 0;
            dr["item"] = "xyz";
            dr["qty"] = 200;
            type_po.Rows.Add(dr);

pass data table to sp

                using (var ts = new TransactionScope())
                {
                    using (var con = new SqlConnection(Con))
                    {
                        con.Open();
                        SqlCommand com = new SqlCommand("DBO.InsertPOs", con);
                        com.Parameters.AddWithValue("@poType", t);
                        com.CommandType = CommandType.StoredProcedure;
                        int x = com.ExecuteNonQuery();
                    }
                    ts.Complete();
                }

that's all.this is much easy way to handle one to many scenario.
Advantages :round trip to the database is minimized(only one)
                   less coding and all logic in one place .

Disadvantage : can't alter type table and can't delete when have reference it.(solution : one type table to one sp )

in this part 01 , only demonstrate it's basic functionality of Table value parameters.in part 02 i intend to disuses some of the advance features of it (eg : debugging ).
download script
happy coding