'
Imports System.Data
' By using the Import statement your code will be more readable when using enums and such
Imports MyGeneration.dOOdads
'
'
Public Class TheMasterSample
' Some methods expect a stored proc to exist in your database, you can build the required
' stored procs with 'SQL_StoredProcs.vbgen'
Public Sub SimpleLoad()
' LoadAll() expects a stored proc to exist in your database
Dim emps As New Employees
If emps.LoadAll() Then ' [proc_EmployeesLoadAll]
' At least one row was loaded
End If
' LoadAll is the same as (but maybe less efficient) than this
emps = New Employees
If emps.Query.Load() Then ' SELECT * FROM Employees
' At least one row was loaded
End If
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' LoadAll() and Query.Load() with no Where clause yield the same results
' however, Query.Load() builds dynamic SQL, all query data is passed
' in via SqlParameters so there is no chance for hackers to attempt
' "sql injection" techniques
'-----------------------------------------------------------
End Sub
Public Sub MoreComplexLoad()
Dim emps As New Employees
' LastNames that have "A" anywhere in them
emps.Where.LastName.Value = "%A%"
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
emps.Query.Load()
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' You can Query just about anyway you need to with the Where
' clause, don't bother writing tons of specific stored procedures
' that you'll later have to maintain
'-----------------------------------------------------------
End Sub
Public Sub TheDeluxeQuery()
Dim emps As New Employees
' LastNames that have "A" anywher in them
emps.Where.LastName.Value = "%A%"
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
' Only return the EmployeeID and LastName
emps.Query.AddResultColumn(Employees.ColumnNames.EmployeeID)
emps.Query.AddResultColumn(Employees.ColumnNames.LastName)
' Order by LastName
' (you can add as many order by columns as you like by repeatedly calling this)
emps.Query.AddOrderBy(Employees.ColumnNames.LastName, WhereParameter.Dir.ASC)
' Bring back only distinct rows
emps.Query.Distinct = True
' Bring back the top 10 rows
emps.Query.Top = 10
emps.Query.Load()
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' There are many morals here, again, the power of the dynamic
' query shines through, you'll create screens so quick even you
' will be surprized.
'
' 1) Never use strings like "FirstName" for anything, use your
' business entities ColumnsNames data, such as "Employees.ColumnNames.EmployeeID"
' This way if you drop the column from the table and regenerate you'll
' get a compile error anywhere it was used. Let the compiler do the
' work for you
'
' 2) You can reduce the number of columns returned in your result set
' though "ResultColumnAdd" but just remember that if you access
' poperties for columns that you didn't return an exception will
' be thrown.
'
' 3) Add as many order by columns that you need by repeatedly calling "AddOrderBy"
'-----------------------------------------------------------
End Sub
Public Sub GenerateSql()
Dim emps As New Employees
Dim query As String = emps.Query.GenerateSQL()
'NOTE: It's better to use 'emps.Query.LastQuery'
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' Below is the query text generated by the query in TheDeluxeQuery
' GenerateSQL was created as a unit testing device and was left in
' for debugging purposes. After calling this you cannot load the object.
' If you want to see the sql after the call use 'emps.Query.LastQuery'
'
' "SELECT DISTINCT TOP 10 EmployeeID,LastName FROM [Employees] WHERE [LastName] "
' "LIKE @LastName ORDER BY [LastName] ASC"
'-----------------------------------------------------------
End Sub
Public Sub DataReader()
Dim emps As New Employees
' LastNames that have "A" anywhere in them
emps.Where.LastName.Value = "%A%"
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
Dim reader As SqlDataReader = CType(emps.Query.ReturnReader, SqlDataReader)
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' This can be useful for Quick binding, however, you can always
' bind to emps.DefaultView.
'
' ReturnReader doesn't actually populate your buisness entity
' it merely returns data in the SqlDataReader
'-----------------------------------------------------------
End Sub
Public Sub Iteration()
Dim emps As New Employees
If emps.LoadAll() Then
Dim lastName As String
' Iteration walks the DataTable.DefaultView, see the FilterAndSort
' sample for further clarification.
Do
lastName = emps.LastName
Loop Until Not emps.MoveNext
emps.Rewind()
Do
lastName = emps.LastName
Loop Until Not emps.MoveNext
End If
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' Iteration is simple, you can rewind and restart at any time
'-----------------------------------------------------------
End Sub
Public Sub FilterAndSort()
Dim emps As New Employees
If emps.LoadAll Then
' After you load your business entity you can apply Sort and Filter,
' you could also potentially do this in the Where clause too
emps.Filter = Employees.ColumnNames.City + " = 'Berlin'"
emps.Sort = Employees.ColumnNames.LastName + " DESC"
' Filter might have "hidden" all of the rows
If emps.RowCount > 0 Then
Dim lastName As String
' Remember, iteration walks the DataTable.DefaultView which is
' effected by Sort and Filter, so you'll only get Employees who
' live in Berlin, and they'll fed to your MoveNext loop in decending
' order by LastName
Do
lastName = emps.LastName
Loop Until Not emps.MoveNext
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' Sort and Filter can be great ways to implement data grid
' filtering in your user interface when you don't want to requery
' the database, just bind your grid to emps.DefaultView
'-----------------------------------------------------------
End If
End If
End Sub
Public Sub DemonstrateBulkUpdates()
Dim emps As New Employees
If emps.LoadAll() Then
' Modify the LastName column in every row
Do
emps.LastName = emps.LastName + "W"
Loop Until Not emps.MoveNext
' Rewind and mark the first row as Deleted
emps.Rewind()
emps.MarkAsDeleted()
' Add a new row and fill it in
emps.AddNew()
emps.FirstName = "Jimmy"
emps.LastName = "Lunch Box"
' Save all modifications, deletes, and new rows
emps.Save()
End If
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' This is a very nice way to work. When you generate your
' stored procedures using 'SQL_StoredProcs.vbgen' you'll find
' it is pretty smart. It makes any identity columns
' and or computed columns as OUTPUT parameters. Thus, after Save()
' any new rows or updated rows have their identity
' columns or calulated columns already in them, no
' requerying the database
'
' You never have to use a transaction when saving a single object.
' The dOOdad architecture always does this for you.
'-----------------------------------------------------------
End Sub
Public Sub Transactions()
Dim tx As TransactionMgr
tx = TransactionMgr.ThreadTransactionMgr()
Try
Dim emps As New Employees
emps.AddNew()
emps.FirstName = "Jimmy"
emps.LastName = "Lunch Box"
Dim prds As New Products
prds.AddNew()
prds.ProductName = "dOOdads"
prds.Discontinued = False
tx.BeginTransaction()
emps.Save()
prds.Save()
tx.CommitTransaction()
Catch ex As Exception
tx.RollbackTransaction()
tx.ThreadTransactionMgrReset()
End Try
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' Modeled after COM+ transactions, but still using ADO.NET
' connection based transactions you have the best of both
' worlds.
'
' 1) Your transactions paths do not have to be pre-planned.
' At any time you can begin a transaction
'
' 2) You can nest BeginTransaction/CommitTransaction any number of times as
' long as they are sandwiched appropriately
'
' BeginTransaction
' BeginTransaction
' emps.Save
' CommitTransaction
' CommitTransaction
'
' Only the final CommitTransaction will commit the transaction
'
' 3) Once RollbackTransaction is called the transaction is doomed,
' nothing can be committed even it is attempted.
'
' 4) Transactions are stored in the Thread Local Storage or
' TLS. This way the API isn't intrusive, ie, forcing you
' to pass a SqlConnection around everywhere. There is one
' thing to remember, once you call RollbackTransaction you will
' be unable to commit anything on that thread until you
' call ThreadTransactionMgrReset().
'
' In an ASP.NET application each page is handled by a thread
' that is pulled from a thread pool. Thus, you need to clear
' out the TLS (thread local storage) before your page begins
' execution. The best way to do this is to create a base page
' that inhertis from System.Web.UI.Page and clears the state
' like this
'
' Public Class MyPage
' Inherits System.Web.UI.Page
'
' Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
' TransactionMgr.ThreadTransactionMgrReset()
' End Sub
'
' End Class
'
' And then make sure all of your ASPX pages inherit from MyPage.
'
'-----------------------------------------------------------
End Sub
Public Sub FillComboBox()
Dim prds As New Products
' Note we only bring back these two columns for performance reasons, why bring back more?
prds.Query.AddResultColumn(prds.ColumnNames.ProductID)
prds.Query.AddResultColumn(prds.ColumnNames.ProductName)
' Sort
prds.Query.AddOrderBy(prds.ColumnNames.ProductName, MyGeneration.dOOdads.WhereParameter.Dir.ASC)
' Load it
prds.Query.Load()
' Bind it (there no combo box in this code, see demo)
Me.cmbBox.DisplayMember = prds.ColumnNames.ProductName
Me.cmbBox.DataSource = prds.DefaultView
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' You will find that a dOOdads can do almost anything, no need to write a million little
' specific stored procedures, this code limits the columns, sorts, and fills a combobox
' there's nothing to it
'-----------------------------------------------------------
End Sub
Public Sub AddColumn()
Dim emps As New Employees
If emps.LoadAll() Then
Dim col As DataColumn = emps.AddColumn("FullName", Type.GetType("System.String"))
col.Expression = Employees.ColumnNames.LastName + "+ ', ' + " + Employees.ColumnNames.FirstName
Dim fullName As String
Do
fullName = CType(emps.GetColumn("FullName"), String)
Loop Until Not emps.MoveNext
End If
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' Of course if you add a column Dynamically as the code does above
' you'll have no strongly typed accessor like emps.FullName, but you
' can use GetColumn() to access dynamic columns.
'
' Never use this to access other fields in your business entity
' although it may work, it's poor programming and always use
' your ColumnNames property and not hardcoded strings when possible
'-----------------------------------------------------------
End Sub
Public Sub Serialize()
Dim emps As New Employees
emps.Query.Load() ' emps.RowCount = 200
emps.FirstName = "Griffinski" ' Change first row
emps.GetChanges() ' emps.RowCount now = 1
Dim xml As String = emps.ToXml()
' Now reload that single record into a new Employees object and Save it
Dim empsClone As New Employees
empsClone.FromXml(xml)
empsClone.Save()
'-----------------------------------------------------------
' Moral:
'-----------------------------------------------------------
' This really only serializes the data in the embedded DataTable.
' However, the methods used in the sample above our Overridable
' so you can override them.
'-----------------------------------------------------------
End Sub
End Class