Asp.net: How to efficiently insert data into a database in batches

Asp tutorial. net batch database tutorial efficient data insertion method

There are three ways to insert data into the database in batches. dataset, transactions, and Table variables in the database can be used to insert data.
The premise of this method is that the database must support Table-type variables.

1. Transactions)

'Defines the function for executing batch data insertion. The parameter is an array of insert SQL statements.

Sub ExeTransaction (Byval Sqlstrlist as string ())

Dim Conn As String = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = Tax. mdb"

Dim trans as OledbTransaction = Nothing

Try

If conn. State = connectionstate. closed then

Conn. open ()

End if

Dim cmd as oledbCommand = new oledbcommand ()

Cmd. connection = conn

Cmd. commandtype = commandtype. text

Trans = conn. begintransaction ()

Cmd. transaction = trans

Dim I as integer

For I = 0 to Sqlstrlist. GetupperBound (0)

Cmd. commandtext = Sqlstrlist (I) 'Get the value in the parameter (array)

Cmd.exe cutenonquery ()

Next

Trans. commit ()

Catch ex as oledbexception

Trans. rollback ()

Fanlly

Conn. close ()

End try

End sub

Method 2: Use the Table type variable in the database to insert data
The premise of this method is that the database must support Table-type variables.

-- Create a custom table variable
Create Type PassportTableType as Table (columns... nvarchar (50 ))

-- Create a stored procedure

Create PROCEDURE [dbo]. [CreatePassportWithTVP]
@ TVP PassportTableType readonly
AS
BEGIN
Set nocount on;
Insert into Passport (PassportKey) select PassportKey from @ TVP
END

-- Called in a program

SqlParameter [] sqlParameter = {new SqlParameter ("@ TVP", dataTable )};
SqlHelper. ExecuteNonQuery (connectionString, CommandType. StoredProcedure, "CreatePassportWithTVP", sqlParameter );

Method 2: Use SqlBulkCopy

The principle of using the SqlBulkCopy class for data insertion is that the SQL Server BCP protocol is used for batch data replication, the columns in the data source table correspond to the columns in the target table one by one.

/// <Summary>
/// Insert data in batches
/// </Summary>
/// <Param name = "DestinationTableName"> target table </param>
/// <Param name = "dt"> data source table </param>
/// <Param name = "connectionString"> link string </param>
/// <Returns> </returns>
Public static bool SqlBulkCopyInsert (string DestinationTableName, DataTable dt, string connectionString)
{
Using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy (connectionString ))
{
Try
{
SqlBulkCopy. DestinationTableName = DestinationTableName;
SqlBulkCopy. BatchSize = dt. Rows. Count;
SqlConnection sqlConnection = new SqlConnection (connectionString );
SqlConnection. Open ();

If (dt! = Null & dt. Rows. Count! = 0)
{
SqlBulkCopy. WriteToServer (dt );
}
SqlBulkCopy. Close ();
SqlConnection. Close ();
}
Catch
{
Return false;
}
}
Return true;
}

Below we want to write a generic model. This is a semi-finished product and we haven't figured out how to achieve better universality.

/// <Summary>
///
/// </Summary>
/// <Typeparam name = "T"> </typeparam>
/// <Typeparam name = "P"> </typeparam>
/// <Param name = "DestinationTableName"> </param>
/// <Param name = "DataColumnName"> </param>
/// <Param name = "dictionary"> </param>
/// <Returns> </returns>
Public static bool SqlBulkCopyInsert <T, P> (string DestinationTableName, string [] DataColumnName, Dictionary <T, P> dictionary)
{
Using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy (WebConfig. AutoSiteV2String ))
{
Try
{
DataTable dataTable = based1c. GetTableSchema (DataColumnName );

Foreach (KeyValuePair <T, P> keyValuePair in dictionary)
{
DataRow dataRow = dataTable. NewRow ();
DataRow. ItemArray = new object [] {keyValuePair. Key, keyValuePair. Value };
DataTable. Rows. Add (dataRow );
}

SqlBulkCopy. DestinationTableName = DestinationTableName;
SqlBulkCopy. BatchSize = dataTable. Rows. Count;
SqlConnection sqlConnection = new SqlConnection (WebConfig. AutoSiteV2String );
SqlConnection. Open ();

If (dataTable! = Null & dataTable. Rows. Count! = 0)
{
SqlBulkCopy. WriteToServer (dataTable, DataRowState. Detached );
}
SqlBulkCopy. Close ();
SqlConnection. Close ();
}
Catch
{
Return false;
}
}
Return true;
}

Iii. Use dataset

Public sub Insert ()

'Create a able Data Source

Dim dt as DataTable = New DataTable ()

Dim dr as DataRow

Dt. columns. add (new DataColumn ("name "))

Dim j as Integer

For j = 0 to 10

Dr = dt. newrow ()

Dr (0) = "name" + j. tostring

Dt. rows. add (dr)

Next

Dim Conn As String = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = Tax. mdb"

Conn. open ()

Dim MyAdapter as Oledbdataadapter = new OleDataAdapter ()

....

Dim cmd as Oledbcommand = New Oledbcommand ("Insert Into table (name) values (@ name)", conn)

Cmd. parameters. item ("@ name"). SourceColumns = dt. columns ("name"). columnsName

Myadapter. update (dt)

Conn. close ()

End sub

You can use the preceding three methods to insert data into a database in batches.