Parsing Your SQL StatementA Better Way

Team-Fly team-fly    

 
ADO.NET Programming in Visual Basic .NET
By Steve Holzner, Bob Howell

Table of Contents
Chapter 6.  ADO .NET DataAdapters


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.


Team-Fly team-fly    
Top


ADO. NET Programming in Visual Basic. NET
ADO.NET Programming in Visual Basic .NET (2nd Edition)
ISBN: 0131018817
EAN: 2147483647
Year: 2005
Pages: 123

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net