A good habit for SQL Server administrators (with a small tool)

I. Original development intention

I believe that when you write SQL statements at ordinary times, in order to save time, you do not like to put the pointer ON THE Shift key to play the keyword in the upper case, such as creating a database, directly create database... instead of creating DATABASE, SQL SERVER can execute statements, so it does not matter the effect of the Code.

This is true, but there are specifications in programming. In SQL writing, it is a standard to write keywords in uppercase. Therefore, if such a tool is used to make it easier for me to be a lazy person, it is good to replace keywords with big ones in batches, but it is not a good habit to open your mouth and put your clothes in your hand, so do DIY...

Ii. Development Ideas

To build a Winform application, this tool must implement three aspects as needed:

1. Open the dialog box -- select the path of the SQL file to be processed;

2. Save dialog box -- select the path of the output file after processing;

3. Core Function-use regular expressions to search for keywords in SQL files. If there is a match, convert it to uppercase.

Iii. Development Process

As the saying goes: the plan cannot keep up with changes. It was originally planned to be done in an hour or two, but it was still a full afternoon, and it was a bit of shame. Let's take a look at the process in detail below:

1. Collect SQL keywords

In addition to common CRUD-related statements, SQL keywords include functions, CURSOR, PROCEDURE, built-in system VARIABLE, VIEW, SCHEMA, and so on, and it seems to be beyond the scope of keywords. If you want to solve so many things completely, you can only say that you can't do anything about it. So I only collected some keywords that are frequently used by everyone, the code below will show you.

PS: of course, if anyone in the garden can make a powerful one that covers all SQL statements, the younger brother will definitely read it.

2. SQL file statement Traversal

It is not as difficult to use as SQL plus in ORACLE (mainly because it is too difficult to write SQL statements ), in SQL server, the content or format of SQL statements can be changed by pressing up or down, and the statement does not end with a semicolon, therefore, a few or even a dozen rows are a complete SQL statement in a complex scenario. It seems unrealistic to try to traverse the entire file from the beginning to the end, the final solution is to divide by rows and process the rows as an array. The Code is as follows:

 
 

  
  
// Divide the data by rows to obtain an array.
  
  
Regex rowReg = new Regex ("\ r \ n ");
  
  
String [] strRow = rowReg. Split ("converted SQL source file content ");
 
 

3. Currently, you still cannot rush to process every element in the strRow array, because the element may have "create database", that is, one or more space characters on either side of the keyword, if you do not perform unified processing, it will bring a lot of trouble to the subsequent replacement. So here we use a regular expression to convert the space into one, and then use the space as the delimiter, in this way, we get the array that truly requires matching and replacement operations:

 
 

  
  
for (int i = 0; i < strRow.Length; i++)  
  
  
 {  
  
  
strRow[i] = Regex.Replace(strRow[i], @"\s+", " ");  
  
  
string[] strRowDetail = strRow[i].Split(new char['\0']);  
  
  
} 
 
 

4. loop through each item in the strRowDetail array below:

 
 

  
  
for (int j = 0; j < strRowDetail.Length; j++)  
  
  
 {  
  
  
      if (regex.regRow.IsMatch(strRowDetail[j]))  
  
  
      {  
  
  
   Match match = regex.Match(strRowDetail[j]);  
  
  
            ...  
  
  
     }  
  
  
 } 
 
 

 

Why is the subsequent Code omitted for the time being? This write is not well considered. Imagine if an SQL statement is as follows:

 
 

  
  
create table student  
  
  
 (  
  
  
     cno varchar(50),  
  
  
     ...  
  
  
 ) 
 
 

If such a keyword is one, it is naturally no problem to match, but in many cases it is not that simple, and some SQL statements contain many keywords.

The most typical operation is a date operation, for example:

 
 

  
  
-- Calculate the number of days of the month: select day (dateadd (mm, 1, getdate ()-day (getdate ()));
 
 

 

After such an SQL statement is divided by space, the second element of the array will be day (dateadd (mm, 1, getdate ()-day (getdate ())), if the above Match is performed, only the first day will be converted to uppercase, while others will not be processed.

After some tests, I switched to the MatchCollection class. The Code is as follows (Note: tbSource refers to the text box that displays the SQL code ):

 
 

  
  
For (int j = 0; j <strRowDetail. Length; j ++)
  
  
{
  
  
// Regex. regRow refers to the regular expression used to search for matching keywords. It is displayed in the Code download.
  
  
// First check whether strRowDetail [j] can match
  
  
If (regex. regRow. IsMatch (strRowDetail [j])
  
  
{
  
  
// Record the matched content if matching is possible
  
  
MatchCollection mc = regex. regRow. Matches (strRowDetail [j]);
  
  
// Traverse the matched set
  
  
For (int k = 0; k <mc. Count; k ++)
  
  
{
  
  
// Replace matching items cyclically
  
  
StrRowDetail [j] = strRowDetail [j]. Replace (
  
  
Mc [k]. Value, mc [k]. Value. ToUpper ());
  
  
}
  
  
// Additional content after replacement
  
  
This. tbSource. Text + = strRowDetail [j] + "";
  
  
}
  
  
// If no match exists, the original content is directly appended.
  
  
Else
  
  
{
  
  
This. tbSource. Text + = strRowDetail [j] + "";
  
  
}
  
  
}
  
  
// Because it is first divided by rows, \ r \ n should be appended here to keep its original format
  
  
This. tbSource. Text + = "\ r \ n ";
 
 

5. Finally, use StreamWriter to write the obtained content to the file. In addition to the encoding problem, it seems that there is nothing to say:

 
 

  
  
StreamWriter writer = new StreamWriter(this.tbExportPath.Text, false, 
  
  
Encoding.Default);  
  
  
writer.WriteLine(this.tbSource.Text);  
  
  
writer.Flush(); 
 
 

Iv. Efficiency Testing

When you read the code carefully, you may find that I have been using the common string "+ =" Method for a lot of string processing operations, in fact, this is what I prepared for my efficiency quiz. Now I can try StringBuilder to improve efficiency.

If you want to test it, you still need to get out of Stopwatch. I believe everyone will, and the following code will be ignored:

 
 

  
  
StreamWriter writer = new StreamWriter(this.tbExportPath.Text, false,                                              Encoding.Default);  
  
  
writer.WriteLine(this.tbSource.Text);  
  
  
writer.Flush(); 
 
 

 

If I used the original normal string connection method, I waited 5347 milliseconds!

If the StringBuilder object is used for string operations, and to improve the for loop efficiency and avoid repeated array length calculation each time, the array length is first stored in the variable. The optimization code is as follows:

 
 

  
  
int strRowDetailLength = strRowDetail.Length;   
  
  
 for (int j = 0; j < strRowDetailLength; j++)   
  
  
 {   
  
  
     if (regex.regRow.IsMatch(strRowDetail[j]))  
  
  
     {   
  
  
       MatchCollection mc = regex.regRow.Matches(strRowDetail[j]);   
  
  
         int mcCount = mc.Count;  
  
  
         for (int k = 0; k < mcCount; k++)   
  
  
         {  
  
  
    strRowDetail[j] = strRowDetail[j]                            
  
  
.Replace(mc[k].Value, mc[k].Value.ToUpper());  
  
  
         }  
  
  
         strBuilder.Append(strRowDetail[j] + " ");  
  
  
     }  
  
  
   else15     {  
  
  
   strBuilder.Append(strRowDetail[j] + " ");  
  
  
      }  
  
  
}  
  
  
strBuilder.Append("\r\n"); 
 
 

The test result is 34 Ms. The power of hoho and StringBuilder is really powerful...

V. Project Description

1. The regular expression is really out of the way, so I didn't post it. To tell the truth, I can't bear to read it myself...

2. Although the sparrow is small, it must be a dirty one. This mini tool uses Qian lifeng's simplified Winform on the interface. Without the consent of the author, the ads will be messy. Forget the author's mind. ^_^

Vi. follow-up questions

1. I hope that after reading this article, you will be able to point out whether there are any problems in my solution and whether there are any simple methods. Although this is done, I personally feel that the method is a bit difficult...

2. Which of the following tools is expected to develop a better and more elegant SQL keyword replacement tool...

3. If you have any questions, please leave a message in time...

VII. Project download

Http://files.cnblogs.com/RockyMyx/Solution.rar

Original article title: DIY gadgets Development --- SQL keyword batch conversion

Link: http://www.cnblogs.com/RockyMyx/archive/2010/04/21/Convert-Sql-Keyword.html