Batch insert data to the database in ASP. NET

During our normal development process, we often need to insert data into the database. Sometimes we may need to perform many similar operations, such as inserting data to the same table in the database at the same time, insert data in batches.

Batch insert data into the database. you can insert several pieces of data into the database at a time to improve program execution efficiency and reduce our workload.

You can use either of the following two methods to insert data in batches.

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

Ii. 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

The preceding two methods can be used to insert data into the database in batches.

-- In SQL, you can directly use SQL statements to read data from several common file formats.

--/* Text File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Text; HDR = no; database = C:/'-- C:/is the Directory
, Aa # txt) --aa20.txtis the alias aa.txt
--*/
--/* Excel File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 8.0; IMEX = 1; HDR = yes; database = C:/test.xls '-- C:/test.xls is the Excel file name
, Sheet1 $)
--/* Dbase iv File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase IV; database = C:/'-- C:/is the directory.
, 'Select * from [customer profile 4.dbf] ') -- customer profile 4. DBF is the file name
--*/
--/* Dbase iii File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase III; database = C :/'
, 'Select * from [customer profile 3.dbf] ')
--*/
--/* FoxPro Database
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C:/', -- C:/is the Directory
'Select * from [AA. DBF] ') -- AA. DBF is the file name.
/* -- Description:
Sourcedb = C:/is the directory where DBF Files are stored.
[AA. DBF] is the name of the DBF file.
--*/

BULK INSERT
There is a file [D:/msql/bulk.txt], which is separated by spaces.
01 gwf Dongguang csdn.net
02 sxl dongguangeric csdn.net
-- Create a table:
If object_id ('tbulk') is not null drop table tbulk
Create Table tbulk (ID int, name varchar (100), ADDR varchar (100), tag varchar (10 ))
Go
-- Batch Processing
Bulk insert tbulk from 'd:/msql/bulk.txt'
With (
Fieldterminator = ''-- column Separator
-- Rowterminator = '/N' -- line Separator
)
-- Result
Select * From tbulk