Are you frustrated with having to write code to query a DataSet when you
know the equivalent SQL statement that would return the correct results.
Don't want to redistribute a 3rd party control to do the job? Coming soon
from the Peersoft development lab, a tool that converts a SQL SELECT
statement to the equivalent ADO.NET source code. Picture this...
a.. Add a new .Query file to your Visual Studio project
b.. Set a property to associate the .Query with a Dataset
c.. Enter a SELECT statement and review the results
d.. View the code-behind source code equivalent to the query
e.. Set a property to name the resulting query class
Turn this:
SELECT DISTINCT Categories.CategoryName, Products.ProductName
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID)
INNER JOIN (Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) BETWEEN '1997-01-01' AND '1997-12-31')
into this:
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports QueryOperators
Class GeneratedQuery
Inherits BaseQuery
Public Overrides Function Execute(ByVal ds As DataSet) As
System.Data.DataView
Dim input As DataTable = Nothing
Dim value1 As BaseOperator
Dim value2 As BaseOperator
Dim value3 As BaseOperator
Dim value4 As DataTable
value4 = ds.Tables("Categories")
value3 = New IndexScan(value4, "", "")
Dim value5 As BaseOperator
Dim value6 As DataTable
value6 = ds.Tables("Products")
value5 = New IndexScan(value6, "", "")
value2 = New InnerJoin(value3, "Categories.CategoryID", value5,
"Products.CategoryID")
Dim value7 As BaseOperator
Dim value8 As BaseOperator
Dim value9 As DataTable
value9 = ds.Tables("Orders")
value8 = New IndexScan(value9, "", "")
Dim value10 As BaseOperator
Dim value11 As DataTable
value11 = ds.Tables("Order Details")
value10 = New IndexScan(value11, "", "")
value7 = New InnerJoin(value8, "Orders.OrderID", value10, "Order
Details.OrderID")
value1 = New InnerJoin(value2, "Products.ProductID", value7, "Order
Details.ProductID")
input = value1.Execute()
Const OrdersShippedDateInputOrdinal As Integer = 19
Dim output As New DataTable
Dim col As DataColumn
col = Utilities.GetColumn(input.Columns, "Categories.CategoryName")
If col Is Nothing Then Throw New DataException("Invalid column name
'Categories.CategoryName'.")
Dim CategoriesCategoryNameOrdinal As Integer = col.Ordinal ' ordinal of
column in input table
col = Utilities.CloneColumn(col)
output.Columns.Add(col)
col = Utilities.GetColumn(input.Columns, "Products.ProductName")
If col Is Nothing Then Throw New DataException("Invalid column name
'Products.ProductName'.")
Dim ProductsProductNameOrdinal As Integer = col.Ordinal ' ordinal of
column in input table
col = Utilities.CloneColumn(col)
output.Columns.Add(col)
Dim rows() As DataRow
rows = input.Select()
output.BeginLoadData()
For Each row As DataRow In rows
Dim value13 As Object = False
Dim value14 As Object
value14 = row(OrdersShippedDateInputOrdinal)
Dim value15 As Object
value15 = "1/1/1997 12:00:00 AM" ' constant
Dim value16 As Object
value16 = "12/31/1997 12:00:00 AM" ' constant
If Not TypeOf value14 Is System.DBNull Then
value13 = value14 >= value15 And value14 <= value16
Else
value13 = False
End If
If value13 Then
Dim values(output.Columns.Count - 1) As Object
Dim value As Object
Dim count As Integer = 0
values(count) = row(CategoriesCategoryNameOrdinal)
count += 1
values(count) = row(ProductsProductNameOrdinal)
Try
output.LoadDataRow(values, True)
Catch ex As Exception
Throw New DataException(ex.Message, ex)
End Try
End If
Next
output.EndLoadData()
Dim view As New DataView(output)
Utilities.Distinct(view)
Utilities.CorrectColumnNames(output.Columns)
Return view
End Function
End Class
Contact me at
(E-Mail Removed) if you would like to become a beta
tester.
Adrian Moore
http://www.queryadataset.com
Similar Threads
