When doing a large web site or system, you often encounter a problem is to bulk INSERT or modify the database. If you encounter this problem, or a record to deal with, this is too inefficient, so consider the bulk INSERT or modify
Today, this is not about SqlBulkCopy, just a simple SQL custom table type. Because the current project I used the way of the table, using stored procedures more convenient to automatically create a table. Now I write the function of a simple record, but also convenient to check their own later
First, click New query in the database to create the appropriate SQL
Copy Code code as follows:
--====== Custom Table Type 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 a stored procedure
CREATE PROCEDURE P_tab
(
@ptab Type_tab readonly
)
As
Begin
Insert into the 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 the 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
Second step asp.net call the stored procedure
Copy Code code as follows:
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. A list is a collection of modelent that enables multiple row entries so that you can insert multiple records at once.
