Migrating data using the SqlBulkCopy class in ASP. NET 2.0

Migrating data using the SqlBulkCopy class in ASP. NET 2.0 (RPM)

Http://jackyrong.cnblogs.com/archive/2005/08/29/225521.html

We often want to migrate data to another table in one table, but there are a lot of ways to do it. In. NET 2.0, a SqlBulkCopy class is provided, and you can do the following briefly. For example, a table follows
create table   Person3
(

PersonID int IDENTITY (PRIMARY key, 
Name nvarchar (200),  
Email nvarchar ($),  
picture image 

)

INSERT into Person3 (name,email,picture  
SELECT name,email,picture from person

assumes that the person table already exists, the above statement can insert data into the Person3 table (executed in SQL Server 2005). Now we use the following code to implement the
   string  connectionstring = configurationmanager . Connectionstrings[ "MyDatabase"

SqlConnection myconnection = new SqlConnection (connectionString);

SqlCommand mycommand = new SqlCommand(" SELECT *from person ", MyConnection);

Myconnection.open ();

SqlDataReader dr = Mycommand.executereader ();

SqlConnection mynewconnection = new SqlConnection (connectionString);

Mynewconnection.open ();

SqlBulkCopy bulk = new SqlBulkCopy (mynewconnection);

Bulk. DestinationTableName = "[Person3]";

Try

{

Bulk. WriteToServer (DR);

}

catch (Exception ex)

{

Response.Write (ex. Message);

}

Finally

{

Mynewconnection.close ();

Dr. Close ();

Myconnection.close ();

Bulk. Close ();

}

below to parse. First, create a new database connection, then a very classic code, take the data from the person table and go to DataReader. After that, we set up a new data connection and then use the

SqlBulkCopy bulk = new SqlBulkCopy (mynewconnection);

Bulk. DestinationTableName = "[Person3]";

Where Mynewconnection is passed as a parameter to the constructor of the SqlBulkCopy class and specifies that the target migrated table name is Person3.
After that, then usebulk. WriteToServer (DR); you can migrate.
    and the structure of the person table and the Person3 is exactly the same, so what if the structure is different? Here is an example of creating a table Person2
   FirstName nvarchar ($),
LastName nvarchar ($),  
Email nvarchar ($),  
Picture image
)
  If we move the person table to the Person2 table as above, we will get an error because the fields are different, and we will use the following code
  string  connectionstring =  configurationmanager . Connectionstrings[ "MyDatabase"

SqlConnection myconnection = new SqlConnection (connectionString);

SqlCommand mycommand = new SqlCommand(" SELECT *from person ", MyConnection);

Myconnection.open ();

SqlDataReader dr = Mycommand.executereader ();

SqlConnection mynewconnection = new SqlConnection (connectionString);

Mynewconnection.open ();

SqlBulkCopy bulk = new SqlBulkCopy (mynewconnection);

Bulk. DestinationTableName = "[Person2]";

Bulk. Columnmappings.add ("Name", "LastName");

Bulk. Columnmappings.add ("email", "email");

Bulk. Columnmappings.add ("Picture", "picture");

Try

{

Bulk. WriteToServer (DR);

}

catch (Exception ex)

{

Response.Write (ex. Message);

}

Finally

{

Mynewconnection.close ();

Dr. Close ();

Myconnection.close ();

Bulk. Close ();

}

As you can see, bulk is used here . Columnmappings.add ("... ”，“。。。 ) to enforce that the source Target field matches the field of the target table.
It is said that with SqlBulkCopy class, in the case of many data, performance is very good, hehe.

Migrating data using the SqlBulkCopy class in ASP. NET 2.0