//Table-Valued Parameters In SQL Server
//Table Data Pass to another Same Table
--Frist Create User Define Table Type
EX.
create type T_Demo as Table
(
Field Name DataType
)
//C# Code to Pass Sql Server
SqlCommand _LocalCommnad = new SqlCommand();
SqlConnection LocalCon = new SqlConnection("Data Source=.\sql2012;Initial Catalog=demo;User ID=sa;Password=123;Persist Security Info = false;");
if (LocalCon.State == ConnectionState.Open)
{
_LocalCommnad.Connection = LocalCon;
_LocalCommnad.CommandType = CommandType.StoredProcedure;
_LocalCommnad.CommandText = "SP_DEMO";
_LocalCommnad.Parameters.Clear();
_LocalCommnad.Parameters.AddWithValue("Table", _dt);
_LocalCommnad.CommandTimeout = 0;
_LocalCommnad.ExecuteNonQuery();
}
_LocalCommnad.Connection = null;
LocalCon.Close();
==============================================================
//Update Flg in Table
Create proc [dbo].[SP_DEMO] ( @Table T_Demo READONLY )
as begin
update Demo set Trf=1,TrfDate=Getdate()
from Demo
inner join @Table as TT on TT.INVNO=BILLH.INVNO and TT.TrfDate=BILLH.TrfDate
End
//Table Data Pass to another Same Table
--Frist Create User Define Table Type
EX.
create type T_Demo as Table
(
Field Name DataType
)
//C# Code to Pass Sql Server
SqlCommand _LocalCommnad = new SqlCommand();
SqlConnection LocalCon = new SqlConnection("Data Source=.\sql2012;Initial Catalog=demo;User ID=sa;Password=123;Persist Security Info = false;");
if (LocalCon.State == ConnectionState.Open)
{
_LocalCommnad.Connection = LocalCon;
_LocalCommnad.CommandType = CommandType.StoredProcedure;
_LocalCommnad.CommandText = "SP_DEMO";
_LocalCommnad.Parameters.Clear();
_LocalCommnad.Parameters.AddWithValue("Table", _dt);
_LocalCommnad.CommandTimeout = 0;
_LocalCommnad.ExecuteNonQuery();
}
_LocalCommnad.Connection = null;
LocalCon.Close();
==============================================================
//Update Flg in Table
Create proc [dbo].[SP_DEMO] ( @Table T_Demo READONLY )
as begin
update Demo set Trf=1,TrfDate=Getdate()
from Demo
inner join @Table as TT on TT.INVNO=BILLH.INVNO and TT.TrfDate=BILLH.TrfDate
End
No comments:
Post a Comment