Support

Please use our contact us page to send us a message. We will get back to you as soon as we can. Please make sure your spam blockers allow our medianotion.com email address.

The DOM Wizard does more than just generate strongly typed objects. It also generates many helper methods to make using your new objects easier. Most of the methods are what we call "Fetch" methods. These methods help you populate your objects without typing any SQL. All you have to do is fill in the parameters and let the DOM fill your object or collection of objects.  Here is a list of all the types of helper methods the DOM Wizard produces.  You will find examples of many of these below.

NOTE:  All methods are static and hang off the base class ( not the collection class )

• FetchBy_ <column_name>  - every column in the table has a FetchBy generated for it.  This will return a single row from the table.
• FetchBy_OpenChoice - returns a single row but allows for multiple Where Clause parameters.
• FetchBy_OpenChoice_Collection - returns 1 or more rows and populates the related "Collection" class for the base class.
• FetchBy_OpenChoice_Collection_Paging - returns 1 or more rows and populates the related "Collection" class for the base class.  However, this method returns the paged collection.  It will calculate the total pages if 0 is sent in and return the appropriate page and page size.  *** ONLY AVAILABLE FOR SQL 2005 DATABASES.
• FetchBy_OpenChoice_RowCount - returns only the number of rows in the collection.
• DeleteBy_<column_name> - deletes by the column name specified.  Call is wrapped in a transaction.
• DeleteBy_OpenChoice - Deletes rows based on parameters passed.  Call is wrapped in a transaction.
• Save - Inserts or Updates rows based on the values set on the object.  Call is wrapped in a transaction.

All the static Fetch methods generated by the DOM Wizard require a connection string variable. To make coding easier, the DOM will allow you to pass a connection string alias from your configuration/connectionString section of your app.config/web.config. Of course if you are storing your connection string somewhere else, you will need to pass in the full connection string.

Anybody with a Microsoft SQL Server database can use the DOM Wizard. However, there are some well known best practices that we would like to reiterate here to help you get the most out of the DOM Wizard.

• Set primary keys on all your tables. This is the one requirement to using the DOM Wizard. If a table does not have a primary key, the DOM Wizard can not generate an object for the table.
• Define foreign keys. Foreign keys make your database relational and maintain your data integrity. Plus, the DOM Wizard will use those foreign keys to build objects that are aware of related objects (i.e. Customers object is aware of the Orders object). Object awareness is one of the most powerful features of the DOM Wizard and without it you will be missing out on significant efficiencies.
• Define Lookup Tables (Enumerated Tables). For data that is static, often times developers will hard-code the values and fail to define the data into tables within the database. This is a bad practice because the database foreign keys can not be defined to the lookup table and therefore data integrity is compromised. Now with the DOM Wizard we have the ability to define Enumerated Tables. Enumerated Tables are lookup tables that exist in the database and are enumerated in the code. If the enumerated table changes, simply regenerate your code with the DOM Wizard to update your code. The compiler will tell you if your data changes impact the code. Now that the data is in the database, you may define foreign keys to your enumerated tables for data integrity.

All the code examples include C# and VB.Net source. We based all our examples off the Microsoft Northwind example database. On our download page, you will find entire example solutions. The example solutions include the Northwind database. You need to use our Northwind database for the Store Procedure example.

Steps to using the example applications:

1. Restore the Northwind-Full.bak file to your SQL Server 2005 ( any edition ). Keep the name northwind.
2. Use the DOM Wizard to generate a project for the northwind database. Accept all the defaults during the DOM Wizard steps except for selecting the language you prefer.
3. Open an example folder relative to the language you prefer. Open the solution file for the example. Add the project from step 2 to this solution.
4. Alter the app.config file by entering your server where it says ###YOURSERVERHERE###. If you are using SQL Server authentication you will need to modify the connection string to use your username and password.

Below is an example of data binding to a combo box. All the classes generated by the DOM Wizard have a related "Collection" class. Any DOM Wizard generated object can be used for data binding. Below we use the the FetchAll method to populate a collection of Customers.

VB.Net

 
Friend WithEvents cbCustomer As System.Windows.Forms.ComboBox
cbCustomer.DisplayMember = "CustomerID"
cbCustomer.ValueMember = "CustomerID"
cbCustomer.DataSource = Customers.FetchAll("SiteSqlServer")

C#

 
internal System.Windows.Forms.ComboBox cbCustomer;
cbCustomer.DisplayMember = "CustomerID";
cbCustomer.ValueMember = "CustomerID";
cbCustomer.DataSource = Customers.FetchAll("SiteSqlServer");

One of the greatest helper methods generated by the DOM Wizard is the Save method. No more insert or update SQL to write. Just type your object name and find the Save method; Pass the connection string and everything is done for you. All Save and Delete methods are wrapped in transactions.
Below, we save the Customer object after updating the last name.

VB.Net

 ' fetch the customer and populate the customer object 
Dim customer As Customers = Customers.FetchBy_CustomerID("SiteSqlServer", myCustomerID)

' update the last name with a new value.
customer.LastName = "My New Last Name"
' now, save the customer
customer.Save("SiteSqlServer")

C#

 
// fetch the customer and populate the customer object
Customers customer = Customers.FetchBy_CustomerID("SiteSqlServer", myCustomerID);

// update the last name with a new value.
customer.LastName = "My New Last Name";
// now, save the customer
customer.Save("SiteSqlServer"); 

Every column in a table has a static FetchBy method generated for it. These methods populate the object with a single row from the table. Below we use the FetchBy_CustomerID method to populate the Customer object.

VB.Net

 
Dim customer As Customers = Customers.FetchBy_CustomerID("SiteSqlServer", myCustomerID)

C#

 
Customers customer = Customers.FetchBy_CustomerID("SiteSqlServer", myCustomerID);

Each FetchBy has an overload that includes the parameter "GetChildren". This parameter will cause the FetchBy to return any child objects. The call is recursive so the FetchBy will only stop when it terminates in the object hierarchy. By default this parameter is False and should be used with caution due to the performance implications. Below we populate a TreeView control using the GetChildren overload.

VB.Net

 
Friend WithEvents tvCustomer As System.Windows.Forms.TreeView
Dim myCustomers As Customers = Customers.FetchBy_CustomerID("SiteSqlServer", True, myCustomerID)

Dim custNode As TreeNode = tvCustomer.Nodes.Add(myCustomers.CompanyName)
For Each order As Orders In myCustomers.OrdersCollection
Dim orderNode As TreeNode = custNode.Nodes.Add(order.OrderID.ToString)
For Each orderDetail As Order_Details In order.Order_DetailsCollection
        orderNode.Nodes.Add(orderDetail.ProductID.ToString)
    Next
Next

C#

 
internal System.Windows.Forms.TreeView tvCustomer;
Customers myCustomers = Customers.FetchBy_CustomerID("SiteSqlServer", true, myCustomerID);

TreeNode custNode = tvCustomer.Nodes.Add(myCustomers.CompanyName);
foreach (Orders order in myCustomers.OrdersCollection)
{
    TreeNode orderNode = custNode.Nodes.Add(order.OrderID.ToString());
    foreach (Order_Details orderDetail in order.Order_DetailsCollection)
    {
        orderNode.Nodes.Add(orderDetail.ProductID.ToString());
    }
}

Other useful methods created for you by the DOM Wizard are the FetchBy_OpenChoice methods. FetchBy_OpenChoice method allows for multiple Where Clause parameters. It also allows for Equalities, Operators and Order By Clauses. The default quality is EqualTo (=).  The default Operator is "And".   The following example demonstrates how to specify a GreaterThanOrEqual equality.

VB.Net

 
dgOrders.DataSource = Order_Details.FetchBy_OpenChoice_Collection("SiteSqlServer", _
New MediaNotion.DOM.DOMBase.Parameter("Quantity", myQuantity, _
MediaNotion.DOM.DOMBase.Parameter.enuEqualities.GreaterThanOrEqualTo))

C#

 
dgOrders.DataSource = Order_Details.FetchBy_OpenChoice_Collection("SiteSqlServer", 
new MediaNotion.DOM.DOMBase.Parameter("Quantity", myQuantity, 
MediaNotion.DOM.DOMBase.Parameter.enuEqualities.GreaterThanOrEqualTo));

The In Clause is also supported by the DOM Wizard. Use the In or Not In equality to return a collection of the desired object. The In Clause requires a comma delimited list of values. Below we return a collection of Order_Details that match the Quanities passed.

VB.Net

 
dgOrders.DataSource = Order_Details.FetchBy_OpenChoice_Collection("SiteSqlServer", _
New MediaNotion.DOM.DOMBase.Parameter("Quantity", _
myCommaDelimitedQuanities, _
MediaNotion.DOM.DOMBase.Parameter.enuEqualities.In))

C#

 
dgOrders.DataSource = Order_Details.FetchBy_OpenChoice_Collection("SiteSqlServer",
new MediaNotion.DOM.DOMBase.Parameter("Quantity", myCommaDelimitedQuanities, 
MediaNotion.DOM.DOMBase.Parameter.enuEqualities.In));

The Order By Clause is easy to implement. The Order By MUST be the last parameter in the DOMBase.Parameter array. Below we fetch the Customers filtered on city and country and ordered by the CompanyName.

VB.Net

 
' Get the customers. Filter by city and country. Order by the CompanyName
' The default Operator is AND.
' The default Equality is =
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection("SiteSqlServer", _
New MediaNotion.DOM.DOMBase.Parameter("City", cbCity.Text), _
New MediaNotion.DOM.DOMBase.Parameter("Country", cbCountry.Text), _
New MediaNotion.DOM.DOMBase.Parameter("CompanyName", _
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc))

C#

 
// Get the customers. Filter by city and country. Order by the CompanyName
// The default Operator is AND.
// The default Equality is =
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection("SiteSqlServer",
new MediaNotion.DOM.DOMBase.Parameter("City", cbCity.Text),
new MediaNotion.DOM.DOMBase.Parameter("Country", cbCountry.Text),
new MediaNotion.DOM.DOMBase.Parameter("CompanyName", 
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc));

Using the Or operator is demonstrated below. All operators should be set between Where Clause parameters.

VB.Net

 
' Get the customers. Fetch by city OR country. Order by the CompanyName
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection("SiteSqlServer", _
New MediaNotion.DOM.DOMBase.Parameter("City", cbCity.Text, _
MediaNotion.DOM.DOMBase.Parameter.enuOperators.OR), _
New MediaNotion.DOM.DOMBase.Parameter("Country", cbCountry.Text), _
New MediaNotion.DOM.DOMBase.Parameter("CompanyName", _
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc))

C#

 
// Get the customers. Fetch by city OR country. Order by the CompanyName 
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection("SiteSqlServer", 
new MediaNotion.DOM.DOMBase.Parameter("City", cbCity.Text, 
MediaNotion.DOM.DOMBase.Parameter.enuOperators.OR), 
new MediaNotion.DOM.DOMBase.Parameter("Country", cbCountry.Text), 
new MediaNotion.DOM.DOMBase.Parameter("CompanyName", 
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc));

Paging object collections is a powerful feature in the DOM Wizard. The method passes the TotalPages parameter by reference so that the developer can use the variable to display custom paging. For efficency, the TotalPages is only calculated if TotalPages value is 0. An Order By is REQUIRED for the Paging method. ** Paging is only available for SQL 2005 databases.

VB.Net

 
PageNumber = PageNumber + 1
' TotalPages is ByRef so that it may be used for displaying custom paging controls.
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection_Paging("SiteSqlServer", _
TotalPages, _
PageSize, _
PageNumber, _
New MediaNotion.DOM.DOMBase.Parameter("CompanyName", _
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc))
 
lblTotalPages.Text = TotalPages.ToString()
lblPageNumber.Text = PageNumber.ToString()

C#

 
PageNumber = PageNumber + 1;
// TotalPages is ref so that it may be used for displaying custom paging controls.
dgCustomer.DataSource = Customers.FetchBy_OpenChoice_Collection_Paging("SiteSqlServer",
ref TotalPages,
PageSize,
PageNumber,
new MediaNotion.DOM.DOMBase.Parameter("CompanyName", 
MediaNotion.DOM.DOMBase.Parameter.enuOrderBys.Asc));
 
lblTotalPages.Text = TotalPages.ToString();
lblPageNumber.Text = PageNumber.ToString();

Sometimes it is not necessary to return a collection full of values when all that is needed is the number of rows in the collection. For this scenario, DOM Wizard has a OpenChoice_RowCount method. It works the same as any other OpenChoice and supports all the same parameters. However, it only returns a row count as an integer. Below we return the number of Orders a Customer has made.

VB.Net

 
Dim OrderCount As Integer = Orders.FetchBy_OpenChoice_RowCount("SiteSqlServer", _
New MediaNotion.DOM.DOMBase.Parameter("CustomerID", cbCustomer.Text)) 

C#

 
int OrderCount = Orders.FetchBy_OpenChoice_RowCount("SiteSqlServer", 
new MediaNotion.DOM.DOMBase.Parameter("CustomerID", cbCustomer.Text)); 

The OpenChoice_StoredProcedure method is another invaluable feature of the DOM Wizard. Complex queries will require the use of a stored procedure. The demonstration below uses a stored procedure to get the orders per customer.

VB.Net

 
dgOrders.DataSource = Orders.FetchBy_StoredProcedure_Collection("SiteSqlServer", _
"domS_Orders", _
New MediaNotion.DOM.DOMBase.Parameter("CustomerID", cbCustomer.Text))

C#

 
dgOrders.DataSource = Orders.FetchBy_StoredProcedure_Collection("SiteSqlServer", 
"domS_Orders",
new MediaNotion.DOM.DOMBase.Parameter("CustomerID", cbCustomer.Text));