Use OLEDB and SqlBulkCopy to import Multiple ACCESS mdb data files in different folders to MSSQL and sqlbulkcopymssql

Use OLEDB and SqlBulkCopy to import Multiple ACCESS mdb data files in different folders to MSSQL and sqlbulkcopymssql

Note: Indicate the original source and author information of the document for reprinting.

Preface

Ole db is Microsoft's strategic low-level application interface to different data sources. Ole db not only supports the Structured Query Language (SQL) of Open Database Connectivity (ODBC), but also supports other non-SQL data types. The existence of ole db provides users with a unified method to access all different types of data sources. Ole db can be converted in different data sources. With ole db, client developers only need to focus on a few details when accessing data, rather than understanding a large number of access protocols for different databases. Ole db is a set of ActiveX interfaces that access data through COM interfaces. This ole db interface is quite common enough to provide a unified means of accessing data, regardless of the method used to store data. At the same time, ole db also allows developers to continue to take advantage of the advantages of the basic database technology, without the need to use these advantages to move data out.

 

Interface Introduction Some members in System. Data. OleDb (I believe readers are familiar with it)

OleDbConnection	Indicates that the connection to the data source is open.
OleDbCommand	Indicates the SQL statement or stored procedure to be executed on the data source.
OleDbDataReader	Provides a stream-only method for reading data rows from a data source.
OleDbDataAdapter	Indicates a set of Data commands connected to a database. They are used to fill in System. Data. DataSet and update the Data source.
......	......

 

 

 

 

 

 

 

SqlBulkCopy allows data from other sources to be effectively loaded to SQL Server tables in batches (this is a stranger)

BatchSize	The number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server.
BulkCopyTimeout	The number of seconds allowed to complete the operation before the timeout. The default value is 30 seconds. The value 0 indicates that there is no limit; batch replication will wait for an indefinite period of time.
ColumnMappings	Column ing defines the relationship between columns in the data source and the columns in the target table.
DestinationTableName	The name of the target table.
Policyafter	Defines the number of rows to be processed before a notification event is generated.
SqlRowsCopied	Event. This occurs every time the number of rows specified by the System. Data. SqlClient. SqlBulkCopy. policyafter attribute is processed.
WriteToServer	Copy data to the destination table specified by the DestinationTableName attribute.
......	......

 

 

 

 

 

 

 

 

 

Finally, the Code is ready.

Static void BulkCopy (string fileName) {try {string connectionString = "server = 192.168.1.250; Initial Catalog = demo; user = sa; password = 123 ;"; string strConn = $ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = {fileName}; "; string strCom = string. format ("select * from content"); OleDbConnection conn = new OleDbConnection (strConn); OleDbCommand com = new OleDbCommand (strCom, conn); conn. open (); IDataReader Reader = com. executeReader (); using (SqlBulkCopy bcp = new SqlBulkCopy (connectionString, SqlBulkCopyOptions. useInternalTransaction) {bcp. batchSize = 1000; // bcp. policyafter = 10000; bcp. bulkCopyTimeout = 3600; // bcp. sqlRowsCopied + = new SqlRowsCopiedEventHandler (bulkCopy_SqlRowsCopied); // each batch of triggered events processed bcp. destinationTableName = "content1"; bcp. columnMappings. add ("id", "id"); bcp. columnMappings. add ("Type1", "typeA"); bcp. columnMappings. add ("type2", "typeB"); bcp. columnMappings. add ("type3", "typeC"); bcp. columnMappings. add ("type4", "typeD"); bcp. columnMappings. add ("type5", "typeE"); bcp. writeToServer (reader);} reader. close ();} catch (Exception ex) {Console. writeLine (ex. message) ;}} private static void bulkCopy_SqlRowsCopied (object sender, SqlRowsCopiedEventArgs e) {count ++ = 10000; // var Count = e. RowsCopied; // number of rows copied during the current batch copy operation. Console. Clear (); Console. WriteLine ($ "{count} data has been copied .");}

In many cases, there is not only one data file, for example:

 

Traverse and retrieve the mdb files in each folder

Static void ProRun () {List <FileInfo> list = new List <FileInfo> (); DirectoryInfo dir = new DirectoryInfo (@ "E: \ XXX"); var dirInfo = dir. getDirectories (); dirInfo. toList (). forEach (x => {x. getFiles ("*. mdb "). toList (). forEach (file => {// exclude unnecessary files if (! File. name. equals ("XXX. mdb ") {list. add (file) ;}}) ;}); list. forEach (file => {BulkCopy (file. fullName );});}

Conclusion: It can be changed to SQL, ACCESS, ORACLE, EXCEL... and other data sources because of OLE DB.