mssql2008 custom table type implements of bulk insert or modification
- 2020-05-15 02:20:35
- OfStack
When doing large websites or systems, there is often a problem with bulk insert or modification of the database. If you encounter this problem, still 1 record to deal with, such efficiency is too low, so you should consider batch insert or modify
Today we won't talk about SqlBulkCopy, just sql custom table types. Because I have used the method of dividing tables in the current project, it is more convenient to use the stored procedure to automatically create the dividing tables. Now the function that I write at present is simple make a record, also convenient oneself look up later
Step 1, click the new query in the database to create sql
Step 2. asp.net calls the stored procedure
ModelEnt is the entity class corresponding to the tab table. list is a collection of ModelEnt, so you can implement multiple row, so you can insert multiple records at once.
Today we won't talk about SqlBulkCopy, just sql custom table types. Because I have used the method of dividing tables in the current project, it is more convenient to use the stored procedure to automatically create the dividing tables. Now the function that I write at present is simple make a record, also convenient oneself look up later
Step 1, click the new query in the database to create sql
--====== Custom table types Demo======
-- Create a table
create table tab
(
tabvalue varchar(10)
)
go
-- Create a custom table type
create type Type_tab as table
(
Type_tabvalue varchar(10)
)
go
-- create The stored procedure
create procedure p_Tab
(
@ptab Type_tab readonly
)
as
begin
insert into tab select * from @ptab
end
--sql Test table type type1
declare @tt Type_tab
insert into @tt values('a')
insert into @tt values('b')
insert into @tt values('c')
insert into tab select * from @tt
select * from tab
--sql Test table type type2
declare @tt2 Type_tab
insert into @tt2 values('a')
insert into @tt2 values('b')
insert into @tt2 values('c')
execute p_Tab @tt2
Step 2. asp.net calls the stored procedure
using (SqlConnection conn =SqlHelper.Settings.GetSqlConnection(true))
{
try
{
DataTable DtAdd = new DataTable();
DtAdd.Columns.Add("tabvalue");
DtAdd.Columns["tabvalue"].DataType = System.Type.GetType("System.String");
foreach (ModelEnt rl in List)
{
DataRow Dr = DtAdd.NewRow();
Dr["tabvalue"] = rl.tabvalue;
DtAdd.Rows.Add(Dr);
}
//int Rt = SqlHelper.SqlHelper.SqlBulkCopy(DtAdd, TableName, conn);
SqlCommand cmd = new SqlCommand("p_Tab", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ptab", SqlDbType.Structured).Value =DtAdd;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
#if DEBUG
throw ex;
#else
return -10000;
#endif
}
finally
{
conn.Close();
}
ModelEnt is the entity class corresponding to the tab table. list is a collection of ModelEnt, so you can implement multiple row, so you can insert multiple records at once.