Blog from sqlbulkcopy to Zhou Gong

In the past two days, a small program that can be converted into a database is created to export data between two databases with different structures. I have almost never touched the database before, but will simply delete and insert data. This time, in the face of complicated and completely different table structures, I have no idea at all. Baidu and Google used it, and finally decided to use the sqlbulkcopy class.

As a newbie, there is always fear of a demo that has never been done before, for fear that you may accidentally go the wrong way. Although from the perspective of learning, it is good to walk along the road in the design process. However, work may not be delayed. 7 days of development cannot be careless.

 

It takes two days to clarify the relationship between the table structures. Finally, I decided to use sqlbulkcopy. It was very easy to understand how to use the sqlbulkcopy class on Zhou Gong's blog. The sqlbulkcopy class can be used in data tables with different field names and table structures, extract the required data and import it to the table fields you need. Before that, you only need to set the corresponding relationship. There is no doubt that this can solve my needs. It took two days to complete the demo. by reading the information from the configuration file, the corresponding relationship, SQL statement, and target table name were separated, pass in to the function for data import.

 

The SQL statement and table name are both string, and the corresponding relationship can be stored in a hash table. It is best to store all three attributes in an object. The following are the key code:

 

Private void convert (string SQL, string tablenmae, hashtable st)
{
Sqldatareader SDR = NULL;
Count = 0;
Try
{
Sqlcommand cmd = new sqlcommand (SQL, dbcold. db_sqlconnection );
SDR = cmd. executereader ();
Sqlbulkcopy bulkcopy = new sqlbulkcopy (dbcnew. db_sqlconnection );

// Record the corresponding relationship by extracting the elements of the hash table
Foreach (string STR in st. Keys)
{
Bulkcopy. columnmappings. Add (STR, St [STR]. tostring ());
}

 

// Set the target table name
Bulkcopy. destinationtablename = tablename;

// Execute the sqlrowscopied method every time one piece of data is imported.
Bulkcopy. policyafter = 1;

Bulkcopy. sqlrowscopied + = new sqlrowscopiedeventhandler (bulkcopy_sqlrowscopied );
Bulkcopy. writetoserver (SDR );

}
Catch (exception ex)
{
Throw ex;
}
Finally
{
SDR. Close ();
}
}

 

The manager said that the data volume is large and a progress bar needs to be set. There are about 6000 records in five tables. It takes about 1 second for me to import them. I joked, unfortunately, this progress bar!

 

Today is the first day, and the end of the hard work, I feel that there is time to sum up. So I went to Zhou Gong's blog and found that most of the articles in the blog are very valuable practical development experience, which covers a wide range from C # development and Java development, there are also practical database statements. It seems like his journey is like writing his footprints in his blog. It is very enlightening for new people.

Zhou Gong's blog address:

Http://hi.csdn.net/space-609.html