Sunday, August 26, 2012

.Net Class Diagrams

.Net Class Diagrams
A class diagram is an effective way to map out and clarify the design of a .Net object.  Here’s an example:

A Class Diagram Example






ExcelWorker

Class Name












int customerId

Class Field Names with Data Types

string itemName











String
ConnectToExcel
Class Methods and Return Values

DataTable
ReadExcel

Boolean
UpdateExcel








How to Build from the Class Diagram

1. Add a Class to the Project
In the solution explorer, right click on the project, select add class and provide a descriptive name.  In my example, the class is named ExcelWorker since it will be working with data in a spreadsheet.    

2. Add the Class Fields
Although it is not always apparent up front how many variables and what type will be needed, the class diagram indicates that we at least need an int and a string variable named customerId and customerName respectively.  Class variables are known as “fields.” 
These are declared at the top of the class within the first class brace:

class ExcelWorker
    {
        public int CustomerId;
        public string CustomerName;
    }

These can be initialized in one of two ways: either after the object has been initialized …
ExcelWorker builder = new ExcelWorker();
        
builder.CustomerId = 123;
builder.CustomerName = "Bob";

or they can be initialized when the object is initialized:
ExcelWorker builder = new ExcelWorker() {CustomerId = 123, CustomerName = "Bob"};

The Importance of Class Fields

The fields will keep track of the data for each instance of the class.  For example, if a second ExcelWorker object named ratchet is declared and its fields initialized, the builder and ratchet class objects will have separate field values.

ExcelWorker ratchet = new ExcelWorker() {CustomerId = 456, CustomerName = "Page"};

The CustomerId and CustomerName values for builder will be 123 and “Bob” respectively and 456 and “Page” for ratchet.

3. Add the Class Methods and Return Types

The class diagram indicates that three methods are needed in the class:
Return Type
Method Name
String
ConnectToExcel
DataReader
ReadExcel
Boolean
UpdateExcel

The ConnectToExcel method declares and uses a connection string, database connection and database command in order to connect to the excel workbook.  The ReadExcel method uses the open connection along with a DataReader to read and loop through the data in the excel sheet.  Finally, UpdateExcel is used to make updates to the worksheet using the command object.

4. Declare a New Instance of the Object

The plain vanilla way to instantiate an object:
ExcelWorker builder = new ExcelWorker();
The second way:
ExcelWorker builder;
builder = new ExcelWorker();

And of course the third way which initializes the class fields as mentioned previously:
ExcelWorker builder = new ExcelWorker() {CustomerId = 123, CustomerName = "Bob"};

5. Static Classes and Methods

If the entire class is declared as static - public static int Counter(int theNumber)
 - then it will not be necessary to declare a new class object.  Instead the class and its methods, which also must be static, can be referenced directly in the code.   In this example, the ReadExcel method of the ExcelWorker class is called directly and the int 456 is passed to it.

private void button1_Click(object sender, EventArgs e)
       
 {
           
            ExcelWorker.ReadExcel(456);

        }

Also, if only the ReadExcel method is declared as static, then it can be referenced directly in the code but the other methods, ConnectToExcel and UpdateExcel, if not static, must be referenced by creating a new instance of the class.
class ExcelWorker
    {
        public int customerId;
        public string customerName;

        public static int ReadExcel(int theNumber)
        {
            return 123 + theNumber;
        }
         
    }

private void button1_Click(object sender, EventArgs e)
        {
           
            ExcelWorker.ReadExcel(456);

        }

So, Why Not Make All Methods Static?

If you declare multiple instances of a class, then each object will be able to keep up with distinct data via the class fields.  For example:  I might need two instances of the ExcelWorker class bulderOne and builderTwo.  The first object might need a string, customerName value of “Bob” while the second is named “Joe”.  If the class is static, it would not maintain separate values for the objects since two separate objects do not exist.

Summary

Class diagrams are useful in capturing the “mental model” of a class and mapping out its desired functionality through the detail of necessary fields and methods.  Although it is not always clear up front what methods and fields will be needed, this pre-planning process can help flesh out the needed functionality for the class and it may reveal some caveats in the model.  It should lead to more robust, quality code and time savings down the road. 

Saturday, August 18, 2012

Goal Seek - a Simple, Powerful Excel Tool



Goal Seek can be used to find a desired value by changing a dependent variable.  For example:  In the following income statement, 100 units have been sold at $1,000 a piece resulting in $100,000 in total sales.  Cost of Goods sold runs 60% of sales or $60,000 in this case, variable costs comes in at 15% of sales and fixed costs come in at a static $10,000.  The resultant EBITDA is $15,000 and after taxes of 30% we are left with a Net Income of $10,500.  It is important to note that each formula is wired into the spreadsheet; if the number of units change, the impact on the various line items will be reflected in the statement.
Income Statement
# of Units Sold
            100
Selling Price Per Unit
 $            1,000
Total Sales
 $        100,000
COGS
 $          60,000
Variable Costs
 $          15,000
Fixed Costs
 $          10,000
EBITDA
 $          15,000
Taxes
 $            4,500
Net Income
 $          10,500

Table 1.1

Goal Seek
If I wanted to find how many units need to sell in order to reach a net income of $30,000, I could hunt and peck, typing in unit numbers until I reach the desired $30,000 net income.  Or I could use goal seek.
I start by selecting the cell containing the net income number of $10,500 and then under the data tab, “What if Analysis” drop-down, I select “Goal Seek.”  The dialog box that opens asks for values for “Set cell”, “To value” and “By changing cell”.  As you can see, by initially highlighting the net income cell, “Set cell” is already pointed to cell C20, the net income cell in my case.
Figure 1.1
“To value” is simply the desired net income I am seeking or $30,000.  “By changing cell” is the number of units sold cell; I want to determine how many units I must sell in order to reach a net income of $30,000.  I do this by setting the net income cell to $30,000 by changing the number of units sold cell. 
Figure 1.2
Remember also, it is imperative to have all of the formulas plugged into the worksheet in order to get an accurate result.  Total Sales in this case is number of units sold multiplied by the selling price per unit.  COGS is 60% of total sales, so on and so forth, with each formula entered in the appropriate cell. 
Hit okay and goal seek will work to find a solution.  In this example it determines that 211 units must be sold in order to reach a net income of $30,000.

Figure 1.3

Income Statement
# of Units Sold
211
Selling Price Per Unit
 $            1,000
Total Sales
 $        211,429
COGS
 $        126,857
Variable Costs
 $          31,714
Fixed Costs
 $          10,000
EBITDA
 $          42,857
Taxes
 $          12,857
Net Income
 $          30,000

Table 1.2
In Conclusion
Goal Seek is a data analysis tool found in Excel under the Data Tab - What If Analysis.  It can be used to determine a resultant value by changing a variable it depends on.  In our example we sought to find how many units must be sold at $1,000 a piece in order to reach a net income of $30,000.  Given our cost structure composed of Cost of Goods Sold, Variable Costs, Fixed Costs and Taxes, goal seek found that 211 units must be sold at $1,000 a piece in order to reach a net income of $30,000.

Sunday, August 12, 2012

A Clean Database Connection and Command Architecture for C#.Net

The Darie and Watson architecture for database connection and command building as featured in their book ASP.NET 2.0 E-commerce in C# is a fairly clean methodology for establishing configuration properties, building a generic connection and command builder and querying data using stored procedures.  It involves three separate class files:  a Configuration.cs deigned to hold and return the values of the connectionString and dbProvider,  a GenericDataAccess.cs designed to build the command, the database agnostic DB Factory, receive the stored procedure name,open and close the connection, returning a datatable, and finally, the specific data access file I call DatabaseCustomerService.cs.

Configuration.cs
By placing the program’s configuration settings in a separate file, it is easier to make future changes.  In the specific case of our program’s database connection and querying architecture, the Configuration file simply holds the values of the database connection string and db provider and provides a simple return method.

GenericDataAccess.cs
The Generic Data Access class simply calls the connection string and db provider values from the Configuration.cs file, sets up the database agnostic factory (allowing the developer to use db connections and commands without database specifity, except when the factory is initialized), builds the command., opens the connection, quickly performs its work returning a datatable in this case and quickly closes the connection.
The class and its methods are generic enough that it can be used by multiple objects that need database connection, command and stored procedure execution.  GenericDataAccess accepts a command object as a parameter which holds the stored procedure designation in its CommandText property.   

DatabaseCustomerService.cs
This class serves as the “front counter” to the database and is used to create the command object using a specific store procedure name. It calls GenericDataAccess to build a command and query the database. 
It calls GenericDataAccess to build the command which again uses the database agnostic factory to build the command, it obtains the provider name and connection string from the configuration file and stores them as fields and sets them to the properties of the connection string and command object,  setting the ComandType property of the command object to a stored procedure as well.  The method finishes by setting the CommandText property of the command object to the specific stored procedure name  (comm.CommandText = “Get Customers”) for instance and calls the GenericDataAccess.ExecuteSelectCommand(comm), which returns a datatable … return GenericDataAccess.ExecuteSelectCommand(comm);.