Use LINQ to SQL to copy data from one database to another

Author: barefoot thinking
Time: 8/30/2010 5:04:58

The database access technology is generally used to study databases. It may be more about northwind. Haha! At least, I often make such a sample database. Even so, the understanding of this database is quite superficial. Or, I think it is not necessary to thoroughly read this database. ^_^ I'm afraid it's because of this superficial understanding that at last such a code was generated!
The purpose of this Code is to study the many-to-many ing relationships of the LINQ to SQL statements. Because you are not familiar with the northwind database, you do not know which data tables have such a many-to-many relationship. As a result, I tried to prepare such a test database. Otherwise, how will I verify my speculation? Well, this kind of necessary preparation still needs to be done, although it takes less time. It would be even better if you could just start with the topic of the test argument.
Let's talk about database definition first! In fact, it is also very simple. The data model I want is as follows: there is a data table that stores customer information (MERS mers), a data table that stores product information (products ), and mersproducts ). With these three data tables, it is enough for me to test and use them. The relationship between them is also very clear, a customer can order any number of products, and a product can be ordered by any number of customers. Therefore, there is a many-to-many relationship between the customer and the product, and the relationship is established through the mersproducts join table. It's so simple! Click here!
Is the definition of a data table (MERS mers) that stores customer information:
 

Is the definition of the data table (products) that stores product information:

 

It is the definition of the mersproducts table used to connect the customer table and the product table:

 

Is it easy? Is it silly? Well, you just need to do it! This is only the data table used for testing. It is not too strict! Pai_^

The definition of the data table is ready, and the data is prepared. Haha, I thought I was not too stupid. At least I wouldn't be too stupid to manually add such data one by one. If this is the case, you should pay a terrible price for your stupidity. At last, I finally couldn't help calling "Niang !" Ah, this world.
Where does the data come from? The first thing that comes to mind is the northwind database. In fact, it already contains the customer's data table (MERS mers) and the data table (products) of the storage product ). You only need to read the data and put it in the test data table. If you look at the definition of the database I have previously made, you will find that, in fact, I just moved the definition in northwind. ^_^ If you can be lazy, you will be lazy! Why should we kill our brain cells for no reason?
In this way, there is a task to copy data from one database to another. Although there are still many ways to achieve it, of course, I know little about it. In addition, I recently have a special liking for the use of LINQ. Even the use of LINQ to SQL is also a topic of great interest to me. Therefore, we plan to use LINQ to SQL to complete such a data replication task.
Simply put, this means that one reads data from the database and then saves the data to another database. However, if we want to implement this using LINQ to SQL, you cannot have only one object model. Because two databases are involved, at least two object models are required, only two object models are required. One is the object model of the northwind database, and the other is the object model of the test database. The main purpose of the previous object model is to read data, and the main task of the next object model is to add data.
First of all, I have to declare that in this case, I will not create such two object models manually. It is unwise to do that! At this time, it is natural that the convenience provided by the object relation designer of Vs will not be spared. Two dbml files were created in one breath, and some operations were performed using the object relationship designer. Finally, I thought it was not ideal. I simply went straight to the ground and thoroughly performed a internal medicine operation on the dbml file. After this operation, the dbml file suddenly understood my coding rules and it felt much better. Let me release three in one breath!
Is the project file in Solution Explorer:

The northwind. dbml file is used to establish the object model of the northwind database, while the database. dbml file is used to establish the object model of the test database. Let's also look at what they look like in the object link designer!
Is the posture of northwind. dbml in the object link designer:

Ah! It's a pity that they are not holding hands when they are so close to each other! -_-
It is the shape of database. dbml in object Link Design:

 

However, this picture expresses the naked real triangular relationship in the sky! ^_^ Haha, even the object relation designer of vs understands this!
So much nonsense.
Finally, let's release the code. Let's take a look at the rest!

Static void main (string [] ARGs) {northwinddatacontext NDC = new northwinddatacontext (); databasedatacontext DDC = new databasedatacontext (); //************************************** * ***************************** // convert the cutomers data table from the northwind database all customers, and save it to the test database. //************************************** * ****************************** Var allcustomersinnorthwind = from customerobject in NDC. customers select customerobject; foreach (VAR customerobject in allcustomersinnorthwind) {customer newcustomer = new customer () {customerid = customerobject. customerid, contactname = customerobject. contactname, contacttitle = customerobject. contacttitle, Country = Cu Stomerobject. country, city = customerobject. city, address = customerobject. address, region = customerobject. region}; DDC. MERs. insertonsubmit (newcustomer);} DDC. submitchanges (); //************************************** * ***************************** // set the products data table from the northwind database all customers, and save it to the test database. //************************************** * ****************************** Var allproductsinnorthwind = from productobject in NDC. products select productobject; foreach (VAR productobject in allproductsinnorthwind) {product newproduct = new product () {productname = productobject. productname, unitprice = productobject. unitprice, unitsinstock = productobject. unitsinstock, unitsonorder = productobje Ct. unitsonorder}; DDC. products. insertonsubmit (newproduct);} DDC. submitchanges (); //************************************** * ****************************** // automatically based on the data in the Test Database generate the customer's product order information. //************************************** * ****************************** Var allcustomers = from customerobject in DDC. customers select customerobject; var allproducts = from productobject in DDC. products select productobject; random randomobject = new random (); foreach (VAR customerobject in allcustomers) {int skipcount = randomobject. next (0, allcustomers. count ()-1); int takecount = randomobject. next (1, allcustomers. count ()-skipcount); var randomproducts = allproducts. skip (skipcount ). take (takecount); foreach (VAR productobject in randomproducts) {customerproduct newcustomerproduct = new customerproduct () {customerid = customerobject. customerid, productid = productobject. productid, unitprice = productobject. unitprice}; DDC. customersproducts. insertonsubmit (newcustomerproduct) ;}} DDC. submitchanges ();}