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
