| Now that we've seen the program work with manually creating the SQL statements let's look at a quick procedure to parse the SQL statement and create the parameters for us. Of course you could use the CommandBuilder object, but that would be too easywe are programmers after all. To prepare for this project, create a new Windows Forms project and call it ADOBook06-04. You can copy the form file from the previous project into this project. We will then modify the code in the new project. You can also download the code from the book's web site if you don't want to type it in. As it turns out, SQL statements are well formed and have a very predictable pattern. This makes parsing them relatively easy. If you remember your computer science compiler course, you remember that parsing a string can be a complex process. First you have to tokenize it, converting the text into numerical values that can be used in a look-up table. Then there is usually some mapping of all identifiers to another table. From there on it gets even more complicated depending on whether you are building a compiler or an interpreter. Thankfully, SQL statements are considerably easier to parse. We are not dealing with a sequential set of instructions. The SQL statement is usually just one line. Our parser will be somewhat simplified. We do not have to parse the Select statement. We only have to parse the Update statements (Insert, Update, and Delete). We will use the following sequence of steps: -
Remove all white space from the string. This includes carriage return-line feed pairs, tabs, and multiple space characters. Multiple space characters will be replaced by a single space. Tabs will also be replaced by a single space. -
The string will then be converted to all lower case. This will avoid issues with case sensitivity. -
We will then determine whether we are dealing with an Insert, Update, or Delete statement. Each of these have different, but very predictable, formats. The SQL Parser Code Let's take a look at the code and see what it does. Again the code block is lengthy, but it is still worthwhile to look through. ' This procedure creates the parameters for a Data Adapter command object. Private Sub CreateParameters(ByVal strSQLIn As String, ByRef cmd As SqlClient.SqlCommand) Dim strClauses As String Dim strSetsList() As String Dim strWhereList() As String Dim strAssigns() As String Dim strWhere As String Dim strSets As String Dim strColList As String Dim strValList As String Dim strInsCols() As String Dim strInsVals() As String Dim i As Integer Dim prm As SqlClient.SqlParameter Dim strSQL As String Try ' If it's blank, skip the whole thing. If strSQLIn = "" Then Exit Sub ' Strips extra white space from string strSQL = StripWhite(strSQLIn) ' First we convert the SQL statement to lower case to eliminate ' casing issues. strSQL = strSQL.ToLower Select Case strSQL.Substring(0, 6) Case "update" strClauses = Split(strSQL, "set ")(1) strSets = Split(strClauses, "where ")(0) strSetsList = Split(strSets, ", ") For i = strSetsList.GetLowerBound(0) To strSetsList.GetUpperBound(0) strAssigns = strSetsList(i).Split(" ") prm = New SqlClient.SqlParameter() prm.ParameterName = strAssigns(2).Trim prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = strAssigns(0).Trim cmd.Parameters.Add(prm) Next strWhere = Split(strSQL, "where ")(1) strWhereList = Split(strWhere, " and ") For i = strWhereList.GetLowerBound(0) To strWhereList.GetUpperBound(0) strAssigns = strWhereList(i).Split(" = ") prm = New SqlClient.SqlParameter() prm.ParameterName = strAssigns(2).Trim prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = strAssigns(0).Trim cmd.Parameters.Add(prm) Next Case "insert" strColList = Split(strSQL, "(")(1) strColList = Split(strColList, ")")(0) strValList = Split(strSQL, "values (")(1) strValList = Split(strValList, ")")(0) strInsCols = Split(strColList, ", ") strInsVals = Split(strValList, ", ") For i = strInsCols.GetLowerBound(0) To strInsCols.GetUpperBound(0) prm = New SqlClient.SqlParameter() prm.ParameterName = strInsVals(i).Trim prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = strInsCols(i).Trim cmd.Parameters.Add(prm) Next Case "delete" strWhere = Split(strSQL, "where ")(1) strWhereList = Split(strWhere, " and ") For i = strWhereList.GetLowerBound(0) To strWhereList.GetUpperBound(0) strAssigns = strWhereList(i).Split(" = ") prm = New SqlClient.SqlParameter() prm.ParameterName = strAssigns(2).Trim prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = strAssigns(0).Trim cmd.Parameters.Add(prm) Next End Select Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub In a normal parser, you would use two pointers to feed a text stream into the parser. Since our SQL commands are short, we will just loop through strings ignoring multiple spaces. In this code example we first call StripWhite to strip the white space from the command. Then we check the first six characters of the string to see what we're dealing with. This makes it easier to work with. The Select case block then processes the statements based on whether they are Update, Insert, or Delete statements. To parse the statements I use the Visual Basic Split function. Why not use the Split method of the String class? I did try this, but the delimiter in the Split method is limited to one character. The Split function, on the other hand, allows the delimiter to be a sub-string. I do not know why Microsoft did not bring the same functionality forward with the String class. For you .NET purists , this may seem like a throwback to VB 6. I am very pragmatic, I use what works best. Sometimes the older functions have better functionality than the new Class methods . When that is the case I will always use the older function. StripWhite Function Here is the function to strip white space from the file: Private Function StripWhite(ByVal strIN As String) As String Dim i, j As Integer Dim strOut As String Try If strIN = "" Then Exit Function ' Strip out any embedded cr-lf's strIN = strIN.Replace(vbCrLf, " ") ' Strip out any embedded tabs strIN = strIN.Replace(vbTab, " ") Do Until i > strIN.Length - 1 If strIN.Substring(i, 1) = " " Then strOut = strOut & " " j = i + 1 Do Until j > strIN.Length - 1 Or strIN.Substring(j, 1) <> " " j += 1 Loop i = j - 1 Else strOut = strOut & strIN.Substring(i, 1) End If i += 1 Loop Return strOut Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Function To test the application use the same procedure we used before. Load some data, make some changes, and update the database. Repeat this process for each of the Insert, Update, and Delete functions. Keep using the Refresh button to make sure your changes were properly updated in the database. |
