Summary: C # winform calls SQL2000 to compare the old table data with the new table data, add the data differently, and use the progress bar to show the lessons and experiences of the progress.

This time, the old database has about 0.3 million records. I did this for the first time and didn't think too much about the efficiency. This was implemented with the help of a friend. It took about two hours to complete the process (CPU: Sai Yang 3.06, memory: 1.5 GB, environment: vs2005 + SQL2000), haha!

Experience 1: Do not laugh when executing SQL statements. Before coming in, I don't know how to execute multiple SQL statements. This may be a common problem for many interns. To implement this, you can create multiple SQL statements, such as strsql, strsql1, strsql2 ...... Strsqli ......, Execute in multiple sqlcommand objects.

 

Experience 2: choosing a data source may be daunting for many first-time practitioners, because it is too difficult to access multiple databases on one server! In fact, this is indeed difficult to implement, but our predecessors have already paved the way for us. All you need is to take the first step and know how to tell the database to connect to which database, no matter which machine it is on. So how can we do it?

Yes: defines the string:

StringStrsqlsource = "Data Source = IP address or server name; initial catalog = Name of the database on the machine you want to access; user id = username is generally SA; Password = password ";

Data Source = IP address or server name indicates that you can only access servers in the same domain. I do not know how to access servers outside the domain.

You don't have to explain it later ......

Of course, if you want to operate on multiple servers, you can also define strsqlsource1, strsqlsource2,…, as experienced 1 does ,......, Strsqlsourcei, etc. Run the command with the object defined by sqlconnection.

 

Paste a bitSource code:

String strsqlsource, strsql

Strsqlsource = "Data Source = 192.168.111.226; initial catalog = oldwxjs; user id = sa; Password = ";

Strsql = "select * From TMK ";
Sqlconnection sqlconn = new sqlconnection (strsqlsource );
Sqlcommand sqlcomm = new sqlcommand (strsql, sqlconn );
Sqlconn. open ();

 

Experience 3: WhenProgramIf you have not accessed the database yet, an error occurs. You can troubleshoot the error using the conventional method. What is the conventional method? Is the type, name, and operator errors that the machine can help you check. These are easy to troubleshoot. When a database connection error occurs, many beginners forget to open or close the database. This part is actually quite regular: Common formats are as follows:

sqlconnection sqlconn2 = new sqlconnection (strsqlsource);
sqlcommand sqlcomm2 = new sqlcommand (strsql2, sqlconn2 );
sqlconn2.open ();
int nflag = convert. toint32 (sqlcomm2.executescalar ();
sqlconn2.close ();

In addition, errors that forget to convert the sqlcomm2.executescalar () type will often occur, as shown in the preceding figure.Int nflag = convert. toint32sqlcomm2. executescalar ());Int nflag = sqlcomm2.executescalar ();

 

experience 4: how to Use breakpoints to obtain information. When there are no debugging errors, you may not be able to wait until you click the button to perform database operations. At this time, it is certainly a strange error for beginners. What I saw this time is: intercept the server: " message 170, Level 15, status 1, row 34
row 34th: there is a syntax error near the 'new." " truncates string or binary data. The statement has been terminated ." and other errors are mainly caused by database errors. For example, if the former has special characters, how do I know that special characters appear? It is to use breakpoint or vs error prompts. First, let's look at the yellow part of the error prompts, and then look at the variable values in the temporary variable box below, this box usually appears in the lower part of the main window together with the error prompt. Drop down the scroll bar of this box and you will see the wrong value of each variable, generally, the box corresponding to the SQL statement appears in the lower triangle on the right. If you right-click the triangle, three options are displayed. You can select one option to view the current SQL statement, if you don't understand it, go to the SQL query analyzer and check out what went wrong. Maybe you can change the character in a character string in the SQL statement and run it! This is a special character. The latter's error is usually because you have defined a char field in the database, and the Data Length of the field to be added in the old database exceeds the length of the field. Change it to nvarchar or varcar or extend the char length.

 

Experience 5: How to Write a filter function? Like this:

Public String filterstring (string filteringstring)
{
String str_filteringstring;

// Variable Initialization
Str_filteringstring = filteringstring;

// Filter key SQL strings
Str_filteringstring = str_filteringstring.replace (";",";");
Str_filteringstring = str_filteringstring.replace ("'","'");
Str_filteringstring = str_filteringstring.replace ("-","");
Str_filteringstring = str_filteringstring.replace (":",":");

Return str_filteringstring;
}
Replace ("'", "'") indicates to use 'to replace' in the string you read from the old database '.

How to use it? Like this:

Strtflh = filterstring (Dr ["tflh"]. tostring (). Trim ());
Strttm = filterstring (Dr ["TTM"]. tostring (). Trim ());
Strtzz = filterstring (Dr ["tzz"]. tostring (). Trim ());

 

Experience 6: set the maximum value for the progress bar!

Like this:

strsql1 =" select count (*) from TMK ";
sqlconnection sqlconncount = new sqlconnection (strsqlsource);
sqlcommand sqlcommcount = new sqlcommand (strsql1, sqlconncount);
sqlconncount. open ();
int COUNT = convert. toint32 (sqlcommcount. executescalar ();
progressupdate. maximum = count;

Progressupdate is a progress bar object.

Use sqlcommcount. executescalar () instead of sqlcommcount. executenonquery () because:

Executescalar: executes the query and returns the returned result set.First column of the first row. Queries the number of rows in the database table and returns ignore extra columns or rows.

ExecutenonqueryIt is mainly used to perform updates and deletions without returning any values. In fact, it indicates whether the operation is successful or not.

 

Let's sum up this. The boss is back! Next, multithreading.

Paste the completeCodeRight! Everyone can share it! Haha

 

 

Private void btnupdate_click (Object sender, eventargs E)
{
String struct, strttm, strtzz, struct, strtcbs, strtjh, strtys, strtch, substring, strexchange, strtwz, strtuser, strttm2, strtflh2, strtflh3, strtflh4, struct, strtkeyword4;
Int strclicks, strrecord;
Datetime dt_indate = system. datetime. now;
String strsqlsource, strsql, strsql1, strsql2, strsql3;

Strong = strttm = strtzz = strtwxly = strtcbs = strtjh = strtys = strtch = strtwxlx = strexchange = strtwz = strtuser = strttm2 = strong = strtflh4 = strong "";
Strclicks = strrecord = 0;

Strsqlsource = "Data Source = 192.0.0.0; initial catalog = oldwxjs; user id = sa; Password = ";

strsql = "select * From TMK";
sqlconnection sqlconn = new sqlconnection (strsqlsource);
sqlcommand sqlcomm = new sqlcommand (strsql, sqlconn );
sqlconn. open ();

// Returns the number of rows in the old database.
Strsql1 = "select count (*) from TMK ";
Sqlconnection sqlconncount = new sqlconnection (strsqlsource );
Sqlcommand sqlcommcount = new sqlcommand (strsql1, sqlconncount );
Sqlconncount. open ();
Int COUNT = convert. toint32 (sqlcommcount. executescalar ());
Progressupdate. Maximum = count;

System. Data. sqlclient. sqldatareader DR = sqlcomm. executereader ();

Progressupdate. Visible = true;

If (Dr! = NULL)
{
While (dr. Read ())
{
Progressupdate. Minimum = 0;
Strrecord = convert. toint32 (dr. getint32 (dr. getordinal ("record ")));
Strtflh = filterstring (Dr ["tflh"]. tostring (). Trim ());
Strttm = filterstring (Dr ["TTM"]. tostring (). Trim ());
Strtzz = filterstring (Dr ["tzz"]. tostring (). Trim ());

strtwxly = filterstring (Dr ["twxly"]. tostring (). trim ();
strtcbs = filterstring (Dr ["TCBs"]. tostring (). trim ();
strtjh = filterstring (Dr ["tjh"]. tostring (). trim ();
strtch = filterstring (Dr ["tch"]. tostring (). trim ();

strtys = filterstring (Dr ["tys"]. tostring (). trim ();
strtwxlx = filterstring (Dr ["twxlx"]. tostring (). trim ();
dt_indate = dr. getdatetime (dr. getordinal ("indate");
strexchange = filterstring (Dr ["Exchange"]. tostring (). trim ();
strtwz = filterstring (Dr ["twz"]. tostring (). trim ();

Strsql2 = "select count (*) as number from test1 where record = '"+ strrecord +"' and tflh = '"+ strtflh +"' and TTM = '"+ strttm +"' and tzz = '"+ strtzz + "'and twxly ='" + strtwxly + "'and TCBs ='" + strtcbs + "'and tjh ='" + strtjh + "'and TCH ='" + strtch + "'and tys ='" + strtys + "'and twxlx ='" + strtwxlx + "'and indate ='" + dt_indate + "'and exchange ='" + strexchange + "'and twz ='" + strtwz + "'";

Sqlconnection sqlconn2 = new sqlconnection (strsqlsource );
Sqlcommand sqlcomm2 = new sqlcommand (strsql2, sqlconn2 );
Sqlconn2.open ();
Int nflag = convert. toint32 (sqlcomm2.executescalar ());
Sqlconn2.close ();

If (nflag <0)
{
// An error occurred while querying. The MessageBox. Show () method is used to indicate an error,

Return; // jump out when an error occurs. Do not continue to the next loop.
}
Else if (nflag = 0)
{
Strsql3 = "insert into test1 values ('" + strrecord + "', '" + strtflh + "', '" + strttm + "', '" + strtzz + "', '"+ strtwxly +"', '"+ strtcbs +"', '"+ strtjh +"', '"+ strtch +"', '"+ strtys + "', '"+ strtwxlx +"', '"+ dt_indate +"', '"+ strexchange +"', '"+ strtwz +"', '"+ strtuser + "', '"+ strclicks +"', '"+ strttm2 +"', '"+ strtflh2 +"', '"+ strtflh3 +"', '"+ strtflh4 + "', '"+ strtkeyword1 +"', '"+ strtkeyword2 +"', '"+ strtkeyword3 +"', '"+ strtkeyword4 + "')";
Sqlconnection sqlconn3 = new sqlconnection (strsqlsource );
Sqlcommand sqlcomm3 = new sqlcommand (strsql3, sqlconn3 );
Sqlconn3.open ();
Int nflag2 = sqlcomm3.executenonquery ();
Sqlconn3.close ();

If (nflag2! = 1)
{
// An error occurred while prompting insertion. Use the MessageBox. Show () method.

Return; // jump out when an error occurs. Do not continue to the next loop.
}
Progressupdate. Value + = 1;
}
}
Progressupdate. Visible = false;
MessageBox. Show (this, "update complete! ");
}
Else
{
MessageBox. Show (this, "Sorry! The data you want to add is empty! ");
}

}