The first method:
Insert
Some of the data is also OK for bulk data performance issues.
The second method:
The main idea of the SqlBulkCopy method is to cache data into a DataTable at the client, and then insert the data into the database table with SqlBulkCopy once
Here's how:
System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy (connectionString); Specifies the table name of the target database, bcp. DestinationTableName = "Resulttable"; Establishes a mapping bcp between the data source table field and the columns in the target table. Columnmappings.add ("SheetName", "SheetName"); Bcp. Columnmappings.add ("meters", "kilometer mark"); Bcp. Columnmappings.add ("Twista", "Twista"); Write database table dt is the data source DataTable bcp. WriteToServer (DT); Close SqlBulkCopy Instances
Bcp. Close ();
If a primary key conflict rolls back the insert operation, either guarantee no conflict or create a temporary table!
The third method:
Table-valued parameter TVPs need to establish table-valued parameters on SQL first
--create databasecreate database bulktestdb;gouse bulktestdb;go--create tablecreate table bulktesttable (Id int Primary Key,username nvarchar (+), Pwd varchar (+)) go--create table Valuedcreate TYPE Bulkudt as Table (Id int, UserName nvarcha R (32),
PWD varchar (16))
public static void Tablevaluedtodb (DataTable dt) { SqlConnection sqlconn = new SqlConnection ( &NB Sp configurationmanager.connectionstrings["ConnStr"]. ConnectionString); const string tsqlstatement = "INSERT into bulktesttable (Id,username, PWD) "+ " Select NC. Id, NC. Username,nc. PWD "+ " from @NewBulkTestTvp as NC "; SqlCommand cmd = new SqlCommand (tsqlstatement, Sqlco nn); SqlParameter catparam = cmd. Parameters.addwithvalue ("@NewBulkTestTvp", DT); Catparam.sqldbtype = sqldbtype.structured; The name of the table-valued parameter is Bulkudt, which is in the SQL that created the test environment above. Catparam.typename = "dbo. Bulkudt "; try { Sqlconn.open (); if (dt! = NULL && ; Dt. Rows.Count! = 0) { CMD. ExecuteNonQuery (); } } catch (exceptiOn ex) { Throw ex; } finally { Sqlconn.close (); }}
DataTable serialized into JSON
Call Jsonnet Library
Jsondata = jsonconvert.serializeobject (dt, New Datatableconverter ());
Bulk INSERT data to SQL Server