Wednesday, April 16, 2014     | Register



dOOdads C# Usage Example
using System; using System.Data; using MyGeneration.dOOdads; namespace dOOdad_Demo { /// <summary> /// Summary description for TheMasterSample. /// </summary> public class TheMasterSample { 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 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 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 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" //----------------------------------------------------------- } 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 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 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 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 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 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 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 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. //----------------------------------------------------------- } } }
Copyright © 2004-2005 MyGeneration Software. All rights reserved.   |  Privacy Statement  |  Terms Of Use
>