Wednesday, April 16, 2008

Asshole XQuery -MS SQL 2005

Damn, XQuery is difficult for dummy like me!!


let's say I have xml column in a table with this kind of data


row1 ) <root><node id="1" value="x"/><node id="2" value="y"/></root>

row2 ) <root><node id="1" value="haha"/><node id="2" value="ykkk"/></root>



and u want to search

"select * from myTable where xml contains node which contains both (id=1 , value=x)"

select * from myTable where
myXML.query('(/row/node[./@id="1"])').value('(/node/@value)[1]','nvarchar(1000)')=N'x'


"select * from myTable where xml contains nodes which contains both(id=2,value LIKE 'y%')"
select * from myTable where
myXML.query('(/row/node[./@id="2"])').value('(/node/@value)[1]','nvarchar(1000)') like N'y%'

"select * from myTable where xml contains any nodes containing value=x"
select * from myTable where myXML.exist('//node[./@value = "x"]') = 1

That's all for now folks !

Wednesday, April 2, 2008

your own "For-Each" in Vb.net

[EDIT]
Article now defunct because of .net's generic lists
[/EDIT]

You need 3 classes,

CCar = the single item
CCarCollection = the collection class - implements IEnumerable

CGenericEnumerator = the enumerator class - implements IEnumerator
CGenericEnumerator can be reused/shared by other collection classes with the following conditions:
1) Your collection class has property called Count that returns count of items
2) Your collection class has default property that returns the single item based on array index

CCar
Public Class CCar
Private _brand, _model As String
Public Property Brand() As String
Get
Return _brand
End Get
Set(ByVal value As String)
_brand = value
End Set
End Property
Public Property Model() As String
Get
Return _model
End Get
Set(ByVal value As String)
_model = value
End Set
End Property
Public Sub New(ByVal ABrand As String, ByVal AModel As String)
_brand = ABrand
_model = AModel
End Sub
End Class
CCarCollection
Public Class CCarCollection
Implements IEnumerable
Private _cars() As CCar
Public Sub Add(ByVal ACar As CCar)
If _cars Is Nothing Then
ReDim Preserve _cars(0)
Else
ReDim Preserve _cars(_cars.Length)
End If
_cars(_cars.Length - 1) = ACar
End Sub
Public ReadOnly Property Count() As Integer
Get
If _cars Is Nothing Then
Return 0
Else
Return _cars.Length
End If
End Get
End Property
Default ReadOnly Property Item(ByVal Index As Integer) As CCar
Get
Return _cars(Index)
End Get
End Property
Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
Return New CGenericEnumerator(Me)
End Function
End Class

CGenericEnumerator
Public Class CGenericEnumerator
Implements IEnumerator

Private _collectionAs Object
Private _index As Integer = -1


Public Sub New(ByVal GenericCollection As Object)
_collection = GenericCollection
End Sub

Public ReadOnly Property Current() As Object Implements System.Collections.IEnumerator.Current
Get
Return _collection(_index) 'This calls _collection.Item(Index)
End Get
End Property

Public Function MoveNext() As Boolean Implements System.Collections.IEnumerator.MoveNext
_index += 1
Return Not (_index >= _collection.Count)
End Function

Public Sub Reset() Implements System.Collections.IEnumerator.Reset

End Sub

End Class

Usage :
Dim cars As New CCarCollection
cars.Add(New CCar("Ford", "Mustang"))
cars.Add(New CCar("BMW", "X5"))
cars.Add(New CCar("Delorian", "DMC-12"))
For Each car As CCar In cars
Response.Write(car.Brand & " " & car.Model & ",")
Next