C # functions of SQL file Actuators

I haven't written a blog for a while. This time we will talk about the implementation of SQL file executor functions. During the ERP software upgrade, we often need to upgrade the database while updating the client program, ERP program developers will record the code executed for Database Upgrade in the form of an SQL file during development or save it to a file in a specific format for use during software upgrade, some ERP software will be accompanied by development Database Upgrade tools to facilitate implementation personnel to perform software upgrade operations or ERP software built-in Database Upgrade functions, No matter what method is used to achieve the purpose of software upgrade is a good way, this time we will strip this part of function to implement an SQL file executor, not specific to SQL files, as long as the file contains SQL syntax, and can be executed normally, in this executor, We can correctly execute it (this time we mainly compete for SQL files. For other format files, we only need to remove the relevant control, but it is not well controlled, or you have a better method ). This is the end of the introductory introduction. Next, let's start with the topic and study the principles and design ideas of the SQL executor. I. Before analyzing the principles of SQL file actuators, we should standardize the writing of SQL documents, we wrap a GO keyword at the end of an SQL statement and continue to write the next SQL statement. When we encounter the "USE [database name]" statement, we hope to put this statement in a separate line. To achieve the above writing specifications, we start to analyze the principles: 1. the above SQL specifications have been met, but the content in the SQL file is not what I want. I want to standardize My SQL documents again and I want to idealize it, so I need to re-shuffles the SQL file. I think I should read it in a row and extract it to remove the leading and trailing spaces, I should reorganize the content of the file and write a line feed return character at the end of each line while reading each line, in addition, I want to count the number of SQL statements in each SQL file (used in cycles). I use the GO keyword to mark the number of SQL statements, when I read a line with no spaces at the beginning and end left with case-insensitive GO, my statistics will be + 1 (Statistics start from 1). Oh, this is never enough, I have not considered the USE keyword processing. I usually select a database to execute the corresponding SQL file. The USE keyword is switching the database, when detecting the USE statement, I need to switch the database to the database followed by the USE statement to execute the subsequent code. Therefore, I need to separate the USE statement for special processing, when we write SQL files I need to add the GO keyword to it. Note that the USE [database name] is followed by the SQL statement directly (without carriage return). This syntax is completely correct, but it does not look very beautiful. I will not handle this method here. Please abide by the above specifications. If you do so, the program performance will seriously decline, during database switching, this program starts from the character after USE and ends with the carriage return line break to obtain the database name. This write method causes an SQL exception. I need to build a method like this. 2. in the first point, we formatted the SQL file. Now I want to use the GO keyword as the segmentation point to segment the SQL syntax in the SQL file, I want the statement segment to be executed and return the execution result. At this time, I need a loop to traverse the SQL statement in the file and execute it. The tool named SQL file executor is obviously competing for processing batch SQL files, so a loop is never enough, I have to set up another loop to traverse all the SQL files, analyze each SQL file, and traverse the SQL statements to execute it, so that we can achieve our goal. There seems to be another problem that has not been solved. For example, if I do not execute it, I need to force it to stop when it reaches half of the execution time. How can this problem be solved, forced program shutdown is obviously not desirable and can easily lead to unknown database exceptions or data loss, which we don't want to see. So what are the best solutions? At this time, I enabled the highly advanced goto statement to jump out of the loop from the deep nested loop. I asked the user to jump out of the loop after executing the current SQL syntax segment after sending the Stop command, thus, the subsequent SQL syntax execution is stopped, which ensures the security of SQL data. During SQL file execution, the program cannot be closed unless a Stop command is issued to the program, the program is allowed to be closed only when it is stopped successfully. For a simple program, I will roughly analyze the principles of the above two points. Next I will upload my program interface design diagram, for your reference: next, I will post some major source code for reference. The Code may seem difficult to understand, but its expressiveness is not very good. I hope you can improve it: this is a method for shuffling the syntax in the SQL file.

/// <Summary> /// read the file content (Special SQL keyword processing mainly applies to the GO keyword) /// </summary> /// <param name = "path"> file path </param> /// <param name = "keywords"> keyword </param> /// <param name = "str"> Number of output strings </param> /// <param name = "I"> keywords </param> public void FileReader (string path, string keywords, out int I, out string str) {bool useplusgo = false; // whether to follow go bool goplususe = false after use; // whether the go StreamReader sr = new StreamReader (p Ath, Encoding. getEncoding ("GB2312"); // str = sr. readToEnd (); string s = null; string temp = null; int x = 0; while (temp = sr. readLine ())! = Null) {if (temp. trim (). toUpper () = keywords. toUpper () {useplusgo = false; // The use statement is followed by the go statement, and the use statement is disabled to judge that goplususe = true; // The go x ++ exists before use; s + = "\ r \ n" + keywords + "\ r \ n";} else if (temp. trim (). length> = 4 & temp. trim (). toUpper (). substring (0, 4 ). trim () = "USE") {temp = temp. trim (). replace ("[",""). replace ("]", ""); // Add if (! Goplususe & s! = "\ R \ n" & s! = Null) {x ++; s + = "\ r \ n" + keywords + "\ r \ n" + temp + "\ r \ n ";} else {s + = temp + "\ r \ n";} useplusgo = true;} else {goplususe = false; // judge whether to disable use after go. // if use is not followed by go, add go if (useplusgo) {x ++; s + = "\ r \ n" + keywords + "\ r \ n" + temp + "\ r \ n"; useplusgo = false; // disable use judgment} else {s + = temp + "\ r \ n" ;}}} I = x; str = s; sr. close (); // Close the currently opened file}

 

This is the method for processing the USE statement.

/// <Summary> /// obtain the database name after use /// </summary> /// <param name = "str"> use string line </param> /// <returns> </returns> public string UseStatementProcessing (string str) {string [] strSplit = Regex. split (str, "\ r \ n", RegexOptions. ignoreCase); int x = strSplit. length; string s = ""; if (/* strSplit [0]. toUpper (). indexOf ("USE", 0)> = 0 */Regex. isMatch (strSplit [0]. toUpper (), "USE", RegexOptions. ignoreCase) {s = strSplit [0]. substring (4, strSplit [0]. length-4 ). trim ();} else if (/* strSplit [1]. toUpper (). indexOf ("USE", 0)> = 0 */Regex. isMatch (strSplit [1]. toUpper (), "USE", RegexOptions. ignoreCase) {s = strSplit [1]. substring (4, strSplit [1]. length-4 ). trim ();} else {s = "";} return s ;}

 

This topic is so far. This program is relatively simple. You can write and play it. It is also good as a trainer, mainly in file operations and string processing.