public void SimpleLoad()
{
// LoadAll() expects a stored proc to exist in your database
Employees emps = new Employees();
if(emps.LoadAll()) // [proc_EmployeesLoadAll]
{
// At least one row was loaded
}
// LoadAll is the same as (but maybe less efficient) than this
emps = new Employees();
if(emps.Query.Load()) // SELECT * FROM Employees
{
// At least one row was loaded
}
//-----------------------------------------------------------
// 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
//-----------------------------------------------------------
}
public void TheDeluxeQuery()
{
Employees emps = 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"
//-----------------------------------------------------------
}
public void MoreComplexLoad()
{
Employees emps = 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
//-----------------------------------------------------------
}
public void DataSetSerialize()
{
Employees emps = new Employees();
emps.LoadAll(); // emps.RowCount = 200
emps.LastName = "Griffinski"; // Change first row
emps.GetChanges(); // emps.RowCount now = 1
string str = emps.Serialize();
// Now reload that single record into a new Employees object and Save it
Employees empsClone = new Employees();
empsClone.Deserialize(str);
empsClone.Save();
}
public void Serialize()
{
Employees emps = new Employees();
emps.LoadAll(); // emps.RowCount = 200
emps.LastName = "Griffinski"; // Change first row
emps.GetChanges(); // emps.RowCount now = 1
string str = emps.ToXml();
// Now reload that single record into a new Employees object and Save it
Employees empsClone = new Employees();
empsClone.FromXml(str);
empsClone.Save();
//-----------------------------------------------------------
// Moral:
//-----------------------------------------------------------
// This really only serializes the data in the embedded DataTable.
// However, the methods used in the sample above our virtual
// so you can override them.
//-----------------------------------------------------------
}
public void AddColumn()
{
Employees emps = new Employees();
if(emps.LoadAll())
{
DataColumn col = emps.AddColumn("FullName", Type.GetType("System.String"));
col.Expression = Employees.ColumnNames.LastName + "+ ', ' + " + Employees.ColumnNames.FirstName;
string fullName;
do
fullName = emps.GetColumn("FullName") as string;
while(emps.MoveNext());
}
//-----------------------------------------------------------
// 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
//-----------------------------------------------------------
}
public void Transactions()
{
TransactionMgr tx = TransactionMgr.ThreadTransactionMgr();
try
{
Employees emps = new Employees();
emps.AddNew();
emps.FirstName = "Jimmy";
emps.LastName = "Lunch Box";
Products prds = new Products();
prds.AddNew();
prds.ProductName = "dOOdads";
prds.Discontinued = false;
tx.BeginTransaction();
emps.Save();
prds.Save();
tx.CommitTransaction();
}
catch(Exception)
{
tx.RollbackTransaction();
TransactionMgr.ThreadTransactionMgrReset();
}
//-----------------------------------------------------------
// 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 : System.Web.UI.Page
// {
// private void Page_Init(System.Object sender, System.EventArgs e)
// {
// TransactionMgr.ThreadTransactionMgrReset();
// }
// }
//
// And then make sure all of your ASPX pages inherit from MyPage.
//
//-----------------------------------------------------------
}
public void DemonstrateBulkUpdates()
{
Employees emps = new Employees();
if(emps.LoadAll())
{
// Modify the LastName column in every row
do
emps.LastName += "W";
while(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();
//-----------------------------------------------------------
// 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.
//-----------------------------------------------------------
}
public void FilterAndSort()
{
Employees emps = new Employees();
if(emps.LoadAll())
{
// 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)
{
string lastName;
// 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;
while(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
//-----------------------------------------------------------
}
}
public void Iteration()
{
Employees emps = new Employees();
if(emps.LoadAll())
{
string lastName;
// Iteration walks the DataTable.DefaultView, see the FilterAndSort
// sample for further clarification.
do
lastName = emps.LastName;
while(emps.MoveNext());
emps.Rewind();
do
lastName = emps.LastName;
while(emps.MoveNext());
}
//-----------------------------------------------------------
// Moral:
//-----------------------------------------------------------
// Iteration is simple, you can rewind and restart at any time
//-----------------------------------------------------------
}
public void DataReader()
{
Employees emps = new Employees();
// LastNames that have "A" anywhere in them
emps.Where.LastName.Value = "%A%";
emps.Where.LastName.Operator = WhereParameter.Operand.Like;
SqlDataReader reader = emps.Query.ReturnReader() as 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
//-----------------------------------------------------------
}
public void GenerateSql()
{
Employees emps = new Employees();
string query = 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"
//-----------------------------------------------------------
}
private void btnEmployeesEditRow_Click(object sender, System.EventArgs e)
{
DataRow row = SelectedRow(DataGrid1);
if ( row != null)
{
string sEmployeeID = row["EmployeeID", DataRowVersion.Current].ToString();
if(sEmployeeID != string.Empty)
{
EmployeesEdit empEdit = new EmployeesEdit(Convert.ToInt32(sEmployeeID));
DialogResult result = empEdit.ShowDialog();
if(result == DialogResult.OK)
{
// Employees Grid
emps = new Employees();
emps.Query.Load();
this.DataGrid1.DataSource = emps.DefaultView;
}
}
}
}
private void btnEmployeeSearch_Click(object sender, System.EventArgs e)
{
emps = new Employees();
try
{
if(this.txtLastName.Text.Trim() != string.Empty)
{
emps.Where.LastName.Operator = WhereParameter.Operand.Like;
emps.Where.LastName.Value = this.txtLastName.Text.Trim();
}
if(this.txtTitle.Text.Trim() != string.Empty)
{
emps.Where.Title.Operator = WhereParameter.Operand.Like;
emps.Where.Title.Value = this.txtTitle.Text.Trim();
}
// Could it be any easier?
emps.Query.Load();
}
catch {}
this.DataGrid1.DataSource = emps.DefaultView;
}
private void BindEmployeesGrid()
{
emps = new Employees();
try
{
emps.Query.Load();
}
catch(Exception)
{
MessageBox.Show("Edit your 'app.config' file to correct the connection string for your SQL Northwind database");
}
this.DataGrid1.DataSource = emps.DefaultView;
}
