Data Synchronization Methods for two databases that do not connect to each other: serialized Transmission

There is a need: There are two data on different platforms that cannot be directly connected. Now we need to synchronize the content of one database to another, at the same time, data needs to be encrypted during the transfer process.

Solution 1:

It is to first export data from the source database to a text file, encrypt the file, then transmit it, and perform decryption and import operations on the target. In this solution, the text format uses the first row column name and delimiter to separate fields to save files. This is a common data export format, however, because the data content contains special characters such as carriage return, line breaks, semicolons, and quotation marks, it brings complexity to text reading, you cannot simply read a row as a record. At last, you use a complex regular expression to obtain the record.

Better solution:

Use serialized able objects to simplify Code complexity.

The data is read from the source table and stored in the datatable.BinaryformatterSerialize it to get a file, encrypt and transmit the file, and deserialize it into a able after receiving the object. Then you can useSqlbulkcopyImport data to the database. The biggest advantage of this method is that it does not need to process complicated text formats.

Here is a piece of test code:

Using system; using system. collections. generic; using system. data. sqlclient; using system. data; using system. runtime. serialization. formatters. binary; using system. diagnostics; using system. io; namespace consoleapplication1 {class program {static void main (string [] ARGs) {stopwatch mytime = new stopwatch (); mytime. start (); string connstr = @ "Server =. \ sqlexpress; database = iap_jy; user id = sa; Password = lixin520; pooling = true; timeout = 60 "; sqlconnection conn = new sqlconnection (connstr ); sqldataadapter ADP = new sqldataadapter ("select * From _ User", Conn); Conn. open (); datatable dt = new datatable (); ADP. fill (DT); Conn. close (); mytime. stop (); console. writeline ("read data rows:" + dt. rows. count. tostring (); console. writeline ("Data Reading Time:" + mytime. elapsedmilliseconds. tostring (); mytime. restart (); memorystream MS = new memorystream (); binaryformatter myformatter = new binaryformatter (); myformatter. serialize (MS, DT); mytime. stop (); console. writeline ("serialization Duration:" + mytime. elapsedmilliseconds. tostring (); Ms. position = 0; mytime. restart (); binaryformatter myformatter2 = new binaryformatter (); datatable dt2 = (datatable) myformatter2.deserialize (MS); mytime. stop (); console. writeline ("deserialization time spent:" + mytime. elapsedmilliseconds. tostring (); Ms. close ();}}}

 

From the code running effect, the serialization speed is acceptable. If there is no special requirement, you can simply use this method without looking for other high-speed serialization tools such as protobuf.net.