Пример #1
0
		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
			//-----------------------------------------------------------
		}
Пример #2
0
		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"
			//-----------------------------------------------------------
		}
Пример #3
0
		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
			//-----------------------------------------------------------
		}
Пример #4
0
		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();
		}
Пример #5
0
		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.
			//-----------------------------------------------------------
		}
Пример #6
0
		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
			//-----------------------------------------------------------
		}
Пример #7
0
		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.
			//                        
			//-----------------------------------------------------------
		}
Пример #8
0
		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.         
			//-----------------------------------------------------------
		}
Пример #9
0
		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
				//-----------------------------------------------------------
			}
		}
Пример #10
0
		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
			//-----------------------------------------------------------
		}
Пример #11
0
		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
			//-----------------------------------------------------------
		}
Пример #12
0
		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"
			//-----------------------------------------------------------
		}
Пример #13
0
		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;
					}
				}
			}
		}
Пример #14
0
		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;	
		}
Пример #15
0
		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;
		}