ANN: New Product SQL QueryToCode coming soon...

A

Adrian Moore

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 address removed) if you would like to become a beta
tester.

Adrian Moore

http://www.queryadataset.com
 
S

Sahil Malik [MVP]

Hey Adrian,

This is quite a neat concept. I have a question though, do you, or do you
have any plans, to add optimization to queries - say indexes. For instance
if I intend to query based on a column repeatedly, maybe I should add an
in-memory index. That would be ultra neat.

Just curious :)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Adrian Moore said:
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 address removed) if you would like to become a beta
tester.

Adrian Moore

http://www.queryadataset.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hashtable fails miserably.. i not sure why this cannot work??? 1

Top