Using of User-Defined Table Types in sql server 2008 example

Create the database and do the following

Create database MAKHAM;--Create database with name MAKHAM
Use MAKHAM;
Create Table Emp(EmpID int,EmpName nvarchar(50),Sal float);--Create a table Emp
Create Type EmpType as Table(_EmpID int,_EmpName nvarchar(50),_Sal float);--Create table type EmpType
Create Proc InsEmps(@Emps EmpType ReadOnly) -- Create a procedure to insert bulk records at a time
as 
Begin
   Insert Into Emp(EmpID,EmpName,Sal)
 Select _EmpID,_EmpName,_Sal from @Emps
End
Create Proc UpdEmps(@Emps EmpType ReadOnly) -- Create a procedure to update bulk records at a time
as 
Begin
   Update e Set e.EmpName=_EmpName,e.Sal=_Sal
 From Emp e inner join @Emps es on e.EmpID=es._EmpID 
End  

Create xml file with the name emps.xml

<?xml version="1.0" encoding="utf-8" ?>
<employees>
  <emp><empid>1</empid><empname>Raju</empname><sal>2473</sal></emp>
  <emp><empid>2</empid><empname>Hanu</empname><sal>653</sal></emp>
  <emp><empid>3</empid><empname>Vinay</empname><sal>4567</sal></emp>
  <emp><empid>4</empid><empname>Arun</empname><sal>2345</sal></emp>
  <emp><empid>5</empid><empname>Suman</empname><sal>567</sal></emp>
  <emp><empid>6</empid><empname>Kishore</empname><sal>234</sal></emp>
  <emp><empid>7</empid><empname>Kesav</empname><sal>2566</sal></emp>
</employees>

Create a page and use the code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        InsertData();
    
}

public void InsertData()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("emps.xml"));
    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=MAKHAM;Persist Security Info=True;User ID=sa;Password=sa;Pooling=False");
    SqlCommand cmd = new SqlCommand("InsEmps", con);
    con.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sp = new SqlParameter("@Emps", ds.Tables[0]);
    cmd.Parameters.Add(sp);
    cmd.ExecuteNonQuery(); //See the Emp table in db
}

Select * from Emp;
EmpID  EmpName  Sal 
-----  -------  ------
1      Raju     2473 
2      Hanu     653 
3      Vinay    4567 
4      Arun     2345 
5      Suman    567 
6      Kishore  234 
7      Kesav    2566

Now update the xml file like this
  
<?xml version="1.0" encoding="utf-8" ?>
<employees>
  <emp><empid>1</empid><empname>Raju</empname><sal>24373</sal></emp>
  <emp><empid>2</empid><empname>Hanu</empname><sal>6523</sal></emp>
  <emp><empid>3</empid><empname>Vinay</empname><sal>41567</sal></emp>
  <emp><empid>4</empid><empname>Arun</empname><sal>23345</sal></emp>
  <emp><empid>5</empid><empname>Suman</empname><sal>5617</sal></emp>
  <emp><empid>6</empid><empname>Kishore</empname><sal>27234</sal></emp>
  <emp><empid>7</empid><empname>Kesav</empname><sal>25696</sal></emp>
</employees>

and use the methodand use the method

public void UpdateData()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("emps.xml"));
    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=MAKHAM;Persist Security Info=True;User ID=sa;Password=sa;Pooling=False");
    SqlCommand cmd = new SqlCommand("UpdEmps", con);
    con.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sp = new SqlParameter("@Emps", ds.Tables[0]);
    cmd.Parameters.Add(sp);
    cmd.ExecuteNonQuery(); //See the Emp table in db
}

O/P:
Select * from Emp;
EmpID  EmpName  Sal 
-----  -------  ------
1      Raju     24373 
2      Hanu     6523 
3      Vinay    41567 
4      Arun     23345 
5      Suman    5617 
6      Kishore  2734 
7      Kesav    25696



Tags:User-Defined Table Types,Types,Types in sql server,sql server 2008 types