Developing a VB.Net VSIX package System.InvalidCastException: [A] cannot be cast to [B] error

Hi, the last months I've been developing some VSIX packages for VB.NET. Yesterday I started getting a weird error: 

"Type A originates from 'xxxxx, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' in the context  'LoadNeither' at location 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\ProjectAssemblies\-jqp5pix01\xxxxx.dll'. 

Type B originates from 'xxxxx, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' in the context 'LoadFrom' at location 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\Extensions\...\1.0\xxxxx.dll'."

In my case this problem occurred because both the package and the solution opened in the experimental instance use the same dll. In my package the dll reference was set to copy local. This causes the dll to be copied to: 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\Extensions\...\xxxxx.dll'

The solution I open in the experimental instance creates all the folders under: 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\ProjectAssemblies\'  and copies all the dll's needed for the solution to this location. The dll's however are created with a date created of when they were copied. This causes the debugger to think that it are two different dll versions. Setting the copy local property of the dll to false can solve your problem.

Use VB.NET to get the from part of SQL query PART 2

After posting the function to get the from part of a SQL query, the function has evolved :-)

If you for example would like to do a count(0) on the from part returned by the function, group and order by clauses should be stripped if present. I've added this functionality as an optional parameter. I've also updated the function to split the source query on union, intersect and except also taking into account that these keywords can be included in the query as an alias. Another important change is that square brackets [ ] are also taken into account as alias indicators.

Below is the new function, a bit more complicated as the first function and it probably needs some tidying up, but that's for when I've got some more time. I've also included an (absolutely ridiculous) source test query and the result the function produces

 Public Function ProfelGetFromPartOfAQuery(SourceQuery As String, Optional StripAfterWhere As Boolean = True) As List(Of String)  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim blnBracketOpen As Boolean = False  
       Dim lstReturn As New List(Of String)  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       Dim queries As New List(Of String)  
       Dim tmpQuery As String = SourceQuery  
       'First split the query on certain keywords, union, execpt, intersect  
       'This can't be done with a simple split because the keywords could also be used as aliases  
       'The string is split on except and intersect but the function doesn't tell which from part is  
       'from the except or intersect, this falls outside the scope of this function  
       For Each c As Char In SourceQuery  
         iIndex += 1  
         If c = "'"c And blnQuoteOpen = False Then  
           blnQuoteOpen = True  
         Else  
           If c = "'"c And blnQuoteOpen = True Then  
             blnQuoteOpen = False  
           End If  
         End If  
         If c = "["c And blnBracketOpen = False Then  
           blnBracketOpen = True  
         Else  
           If c = "]"c And blnBracketOpen = True Then  
             blnBracketOpen = False  
           End If  
         End If  
         If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel += 1  
         End If  
         If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel -= 1  
         End If  
         If c = " "c Then  
           strWord = ""  
         Else  
           strWord &= c  
         End If  
         If nestLevel = 0 Then  
           Select Case strWord  
             Case "union", "except", "intersect"  
               queries.Add(Mid(tmpQuery, 1, iIndex - strWord.Length))  
               tmpQuery = Mid(tmpQuery, iIndex + 1, tmpQuery.Length - iIndex)  
               iIndex = 0  
           End Select  
         End If  
       Next  
       If queries.Count = 0 Then  
         queries.Add(SourceQuery)  
       Else  
         queries.Add(tmpQuery)  
       End If  
       'Analyze the seperate queries  
       For Each query As String In queries  
         iIndex = 0  
         strWord = ""  
         blnQuoteOpen = False  
         blnBracketOpen = False  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "["c And blnBracketOpen = False Then  
             blnBracketOpen = True  
           Else  
             If c = "]"c And blnBracketOpen = True Then  
               blnBracketOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             Dim strtmp As String = Mid(query, iIndex - 3, query.Length - (iIndex - 4)).ToLower  
             'If you want to strip eveything after the where set StripAfterWhere = true  
             'this can be used if you want to do a count(0) on the from part returned  
             If StripAfterWhere = True Then  
               Dim iGroup As Integer = ProfelGetPartOfAQuery("group", strtmp)  
               If iGroup <> 0 Then  
                 lstReturn.Add(Mid(strtmp, 1, iGroup))  
               Else  
                 Dim iOrderBy As Integer = ProfelGetPartOfAQuery("order", strtmp)  
                 If iOrderBy <> 0 Then  
                   lstReturn.Add(Mid(strtmp, 1, iOrderBy))  
                 Else  
                   lstReturn.Add(strtmp)  
                 End If  
               End If  
             Else  
               lstReturn.Add(strtmp)  
             End If  
             Exit For  
           End If  
         Next  
       Next  
       Return lstReturn  
     End Function  
     Public Function ProfelGetPartOfAQuery(part As String, SourceQuery As String) As Integer  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim blnBracketOpen As Boolean = False  
       Dim lstReturn As New Integer  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       For Each c As Char In SourceQuery  
         iIndex += 1  
         If c = "'"c And blnQuoteOpen = False Then  
           blnQuoteOpen = True  
         Else  
           If c = "'"c And blnQuoteOpen = True Then  
             blnQuoteOpen = False  
           End If  
         End If  
         If c = "["c And blnBracketOpen = False Then  
           blnBracketOpen = True  
         Else  
           If c = "]"c And blnBracketOpen = True Then  
             blnBracketOpen = False  
           End If  
         End If  
         If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel += 1  
         End If  
         If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel -= 1  
         End If  
         If c = " "c Then  
           strWord = ""  
         Else  
           strWord &= c  
         End If  
         If strWord.ToLower = part And nestLevel = 0 Then  
           lstReturn = iIndex - (part.Length)  
           Exit For  
         End If  
       Next  
       Return lstReturn  
     End Function  

The (absolutely ridiculous) query

 select replace(replace(eb001.kltnr, '(',' '),']','')  
 from eb001  
     left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
     left outer join ea007 on eb001.taalc = ea007.taalc  
     left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'  
 or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN')  
 group by replace(replace(eb001.kltnr, '(',' '),']','')  
 order by (select distinct replace(replace(eb001.kltnr, '(',' '),']','')  
 from eb001  
     left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
     left outer join ea007 on eb001.taalc = ea007.taalc  
     left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN'))  

The result

 from eb001  
      left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
      left outer join ea007 on eb001.taalc = ea007.taalc  
      left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'  
  or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'benthin')  

Use VB.NET to get the from part of SQL query

Hello, long time no post...

this is a function I've written to extract the from part of a SQL query. When you first get this assignment it sounds simple. But once you start thinking about it, it becomes more and more complex. How do you handle sub-queries in the select and where clause. How do you handle unions, ...

The function I've written works by calculating the nesting level of your query. This is done by adding 1 to a counter if you encounter a ( and subtracting 1 from the counter if you find a ). If you then find the word "from" and the nesting level = 0 you've got the from part. The next problem is what if the query contains an extra ( or )
This can happen for example with the following query: select replace(field,'(',' '),* from table
So this simple query would ruin my whole function :-)

I solved this by only increasing and decreasing the nesting level if there are no quotes open.

I've included two functions, one which can handles union and returns the from part of every separate query joined by a union. The first function can't cope with a union, but you can use this if you know there won't be a union. If anyone finds other queries which these functions can't handle, please let me know.

 Public Function GetFromPartOfAQuery(SourceQuery As String) As String  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim strReturn As String = ""  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             strReturn = Mid(query, iIndex - 3, query.Length - (iIndex - 4))  
             Exit For  
           End If  
         Next  
       Return lstReturn  
     End Function  

 Public Function GetFromPartOfAQuery(SourceQuery As String) As List(Of String)  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim lstReturn As New List(Of String)  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       Dim queries As String() = SourceQuery.ToLower.Split(New String() {"union"}, StringSplitOptions.None)  
       For Each query As String In queries  
         iIndex = 0  
         strWord = ""
         blnQuoteOpen = False  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             lstReturn.Add(Mid(query, iIndex - 3, query.Length - (iIndex - 4)))  
             Exit For  
           End If  
         Next  
       Next  
       Return lstReturn  
     End Function