SQL bulk new data through stored procedures and custom types

1, creating a stored procedure

Create PROCEDURE [dbo]. [P_company_insert] @CompanyCollection [Companytabletype] Readonlyasinsert into Tb_company (            [CpID]           , [cphiid]           , [Cpbuyselltypeid]           , [CPName]           , [cpshortname]           , [cpindustry]           , [Cpsell]           , [cpbuy]           , [ CPADDRESSID]           , [cpaddress]           , [cppost]           , [Cptel])    SELECT oc.[cpid]           , oc.[cphiid]           , oc.[ Cpbuyselltypeid],           oc.[cpname]           , Oc.[cpshortname],           oc.[cpindustry]           , Oc.[cpsell]           , oc.[ Cpbuy],           Oc.[cpaddressid]           , oc.[cpaddress]           , Oc.[cppost]           , Oc.[cptel] from    @ Companycollection as OC; GO

2, establish the corresponding data type

/****** Object:  userdefinedtabletype [dbo].[ Companytabletype]    Script date:07/04/2014 10:20:51 ******/create TYPE [dbo].[ Companytabletype] as TABLE ([CpID] [int] not NULL,[CPHIID] [int] null,[cpbuyselltypeid] [nvarchar] (+) null,[cpname] [ NVARCHAR] (+) not null,[cpshortname] [nvarchar] (null,[cpindustry] [nvarchar] (+) Null,[cpsell] [nvarchar] (200) Null,[cpbuy] [nvarchar] (+) NULL,[CPADDRESSID] [int] not null,[cpaddress] [nvarchar] (+) null,[cppost] [nvarchar] ( (+) Null,[cptel] [nvarchar] (+) NULL,) GO

3, Code execution

        <summary>///single Add, pass datatable as parameter, return DataTable's self-growth number (call stored procedure)///</summary> <param name= "DT" ></param>///<returns></returns> [WebMethod] public Dat            ATable Buysell_insert (DataTable dt, string token) {checklogineds (token); if (dt. Rows.Count > 0 && dt.                Rows! = null) {Tb_buysell bs = new Tb_buysell (); DataTable BT = BS.                Buysell_insert (DT);            Return BT;            } else {return null;        }}////<summary>////For the DataTable as a parameter, batch add database, return the new row of DataTable///</summary> <param name= "TB" ></param>///<returns></returns> public DataTable Buysell_inse            RT (DataTable TB) {datatable dt = null;            Cmd.commandtext = "P_buysell_insert"; Cmd.commandtype= CommandType.StoredProcedure; Cmd.            Parameters.clear (); Cmd.            Parameters.addwithvalue ("@BuySellCollection", TB); DT = DB.            DataTable (CMD);        return DT; }

　　

SQL bulk new data through stored procedures and custom types