private void FillComboBox_Load(object sender, System.EventArgs e)
{
//-----------------------------------------------------
// ** dOOdad Tip **
//-----------------------------------------------------
// You will find that a dOOdad can do almost anything, no need to write a million little
// specific stored procedures, this code limits the columns, sorts, and fills a combo-box
// there's nothing to it
// Let's query on the Product Name and sort it
Products prds = new Products();
// Note we only bring back these two columns for performance, why bring back more?
prds.Query.AddResultColumn(Products.ColumnNames.ProductID);
prds.Query.AddResultColumn(Products.ColumnNames.ProductName);
// Sort
prds.Query.AddOrderBy(Products.ColumnNames.ProductName, MyGeneration.dOOdads.WhereParameter.Dir.ASC);
// Load it
try
{
prds.Query.Load();
}
catch {}
// Bind it
this.cmbBox.DisplayMember = Products.ColumnNames.ProductName;
this.cmbBox.DataSource = prds.DefaultView;
}
public void AggregateTest()
{
// AVG
Products prds = new Products();
prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Avg;
prds.Aggregate.UnitsInStock.Alias = "Average Units in Stock";
prds.Query.Load();
Console.WriteLine(prds.Query.LastQuery);
// COUNT - To include a COUNT(*) with NULLs included
prds = new Products();
prds.Query.CountAll = true;
prds.Query.CountAllAlias = "Product Count";
prds.Query.Load();
Console.WriteLine(prds.Query.LastQuery);
// COUNT - To exclude NULLs in the COUNT for a column
prds = new Products();
prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
prds.Aggregate.UnitsInStock.Alias = "With Stock";
prds.Query.Load();
Console.WriteLine(prds.Query.LastQuery);
// To have two aggregates for the same column, use a Tearoff
prds = new Products();
prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
prds.Aggregate.UnitsInStock.Alias = "With Stock";
AggregateParameter ap = prds.Aggregate.TearOff.UnitsInStock;
ap.Function = AggregateParameter.Func.Sum;
ap.Alias = "Total Units";
prds.Query.Load();
Console.WriteLine(prds.Query.LastQuery);
// AddGroupBy
prds = new Products();
prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
prds.Aggregate.UnitsInStock.Alias = "In Stock";
prds.Query.AddResultColumn(Products.ColumnNames.CategoryID);
prds.Query.AddGroupBy(Products.ColumnNames.CategoryID);
// prds.Query.AddGroupBy(prds.Aggregate.UnitsInStock);
prds.Query.Load();
Console.WriteLine(prds.Query.LastQuery);
}
public void FillComboBox()
{
Products prds = new Products();
// Note we only bring back these two columns for performance reasons, why bring back more?
prds.Query.AddResultColumn(Products.ColumnNames.ProductID);
prds.Query.AddResultColumn(Products.ColumnNames.ProductName);
// Sort
prds.Query.AddOrderBy(Products.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
//-----------------------------------------------------------
}
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.
//
//-----------------------------------------------------------
}
private void btnProductSearch_Click(object sender, System.EventArgs e)
{
prds = new Products();
try
{
if(this.txtUnitPrice.Text.Trim() != String.Empty)
{
prds.Where.UnitPrice.Value = this.txtUnitPrice.Text.Trim();
switch(this.cmbOperator.SelectedItem.ToString())
{
case "Equal":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.Equal;
break;
case "Not Equal":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.NotEqual;
break;
case "Greater Than":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.GreaterThan;
break;
case "Greater Than Or Equal":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.GreaterThanOrEqual;
break;
case "Less Than":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.LessThan;
break;
case "Less Than Or Equal":
prds.Where.UnitPrice.Operator = WhereParameter.Operand.LessThanOrEqual;
break;
}
}
if(this.chkDiscontinued.Checked)
{
prds.Where.Discontinued.Value = this.chkDiscontinued.Checked;
}
// Could it be any easier?
prds.Query.Load();
}
catch(Exception) {}
this.DataGrid2.DataSource = prds.DefaultView;
}
private void BindProductsGrid()
{
prds = new Products();
try
{
prds.Query.Load();
}
catch(Exception)
{
MessageBox.Show("Edit your 'app.config' file to correct the connection string for your SQL Northwind database");
}
this.DataGrid2.DataSource = prds.DefaultView;
}
