ADO.Net to create unique DataTable values

The application I'm building uses a .NET BLL referenced by Flex to build views. I've come upon a situation where a series of table queries creates a duplicate list of what are unique values. This throws an error from .NET. The layer queries generate column values that begin as non-unique and become unique upon subsequent table queries.

I have been looking into solutions to update my BLL to remove duplicates of the offending column before the subsequent queries. Initially, as late as 2005, developers suggested writing a custom routine to do this. Here is one written in VB from Julie Lerman:

(note - the int32_key is in there as a reminder in case I need to create another one for non-int keys)

Public Function DistinctRows_Int32_Key(ByVal dt As DataTable, ByVal keyfield As String) As DataTable
Dim newTable As DataTable = dt.Clone
Dim keyval As Int32 = 0
Dim dv As DataView = dt.DefaultView
dv.Sort = keyfield
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
If Not dr.Item(keyfield) = keyval Then
newTable.ImportRow(dr)
keyval = dr.Item(keyfield)
End If
Next
Else
newTable = dt.Clone
End If
Return newTable
End Function

It has since been shoehorned into an ADO.NET 2.0 method called ToTable that works thus:

ADO.Net 2.0 prevents this difficulty by exposing method called DataView.ToTable() with 4 overloads.

DataView.ToTable ()
Creates and returns a new DataTable based on rows in an existing DataView.

DataView.ToTable (String)
Creates and returns a new DataTable based on rows in an existing DataView.

DataView.ToTable (Boolean, String[])
Creates and returns a new DataTable based on rows in an existing DataView.

DataView.ToTable (String, Boolean, String[])
Creates and returns a new DataTable based on rows in an existing DataView.

This is an improvement over writing a custom routine. The hitch is getting the resultant table back to the correct datatype in order to call the row values by name. Here's my solution:

DataView tempView = stronglyTypedTableInstance.DefaultView;

stronglyTypedTableInstance = (StronglyTypedDataTable)(StronglyTypedTableInstance.DefaultView.ToTable(true, "name"));

foreach (StronglyTypedRow stronglyTypedRowInstance in stronglyTypedTableInstance)
{
runLogic...
}
return newlyBuiltTable;


And so on and so forth.

Now using email settings

Publishing by email today. For some new content, you could visit Adobe Site of the Day.

ToolTips in Flex 3

Tooltips. They pop up to provide contextual expansion. They enhance your workspace. They gratify you with sleek utility. They're an integral Flex component. You will probably be customizing them.

Flex Docs is a good starter on custom and standard tooltips:
http://www.adobe.com/devnet/flex/quickstart/using_tooltips/

Adobe docs are not so great when it comes to positioning custom tooltips, but fear not, it's simple once you know how :).

Just be sure to utilize both the toolTipCreate AND toolTipShow methods of the mx.events.ToolTipEvent class and you'll be golden.

Another plus, tooltips are a great use of MVC and you can encapsulate calls to events via event.toolTip.

Another good tutorial resource:
http://blog.flexmonkeypatches.com/2008/09/10/flex-custom-tooltip-speech-bubble/

Happy tooltipping.

Examination of the Model-View-Controller model

The MVC is supposed to make life easy, but it's often hard to visualize.

Model View Controller requires the M, the V and the C.

The "Model" component holds the generic interface.
The "View" event returns the data to the application.
The "Controller" application implements the component with specific data.

So
Model = interface
View = event
Controller = application

Now that that's out of the way, here's a simple example of MVC in action.

Time Spent on Technology

My most recent client project tossed me headlong me into the programmer's briar patch. And there were some sharp thorns in there. C Sharp to be exact. (C# is the programming language of ASP.NET).

Digressions aside, there's an unmistakable Pyhrric thorn in a technical victory. Namely, it is not a visual victory. It is only part of the solution.

Design | Develop.

Web projects are a calico of the visual and the symbolic. Envisioning a project requires understanding it inside out. Learning the development side dispels the magic inside the application. When you have parsed it, it's obvious.

On the other hand, a technical project might drain visual creativity or vice versa. Math may diminish poetry, or medicine smoking. If a designer develops, can a developer design?

In a word, yes.

(but it takes more time)

Example of GridView Architecture

I'm pasting this code snippet in for posterity. I had intended to make use of the RowDataBound event to alter Web Controls nested within the rows of a GridView.

I successfully obtained all the properties I needed. In the final implementation, the code will be in the main constructor. This is due to the lack of a GridView in its entirety as GridViewRow events are called.

protected void PhotoGalleryGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (!IsPostBack)
{
//Test to see which Row was bound for each RowDataBound event
GridView theGridView = (GridView)sender;

//get rownum for the event
int myRow = e.Row.RowIndex;

//init rowID to value of 0
int rowID = 0;

//if rownum is a body row, run the operation
if (myRow > -1)
{
//get the ID to associate
rowID = int.Parse(theGridView.DataKeys[myRow].Value.ToString());
int numRows = PhotoGalleryGridView.Rows.Count;

//locate the checkboxlist
CheckBoxList CheckBoxList1 = (CheckBoxList)PhotoGalleryGridView.Rows[myRow].FindControl("NonEditableCheckBoxList");
CheckBoxList CheckBoxList2 = (CheckBoxList)PhotoGalleryGridView.Rows[myRow].FindControl("EditableCheckBoxList");

//create an API for testing values
ApplicationAssociationsBLL appAssociationsAPI = new ApplicationAssociationsBLL();
ImperialConcrete.ApplicationAssociationsDataTable associations = appAssociationsAPI.GetApplicationAssociationsByPhotoID(rowID);
//ImperialConcrete.ApplicationAssociationsRow association = associations[0];

//update the values for the lists
for (int i = 0; i < CheckBoxList1.Items.Count; i++)
{
//Get CheckBoxAttributeID for sql operation
int CheckBoxID = int.Parse(CheckBoxList1.Items[i].Value);
foreach (ImperialConcrete.ApplicationAssociationsRow association in associations)
{
if (association.ApplicationID == CheckBoxID)
{
CheckBoxList1.Items[i].Selected = true;
break;
}
else
CheckBoxList1.Items[i].Selected = false;
}
}
}
int stopper = 1;
}
}

flying colors

A custom asp.net module by nathanaeljones.com was a propos for working with stored images on this project. He also customized it for me so I could use datastreams to resize images dynamically. Highly recommended A+++;

Next order of business:

Work on batch methods to clear all Foreign Key Associations when performing record deletes.

Here's a great Auto-Format key combo

Auto-Format your Code in Visual Studio

Many a times we often come across unformatted code, which is almost impossible to understand. For instance, some lines of code may be indented properly while others might not be. Visual Studio provides a great feature for auto-formatting your unformatted code. Just press Ctrl+K followed by Ctrl+D and voila the code is now in a well-indented, easy to understand format. It works for markup code like HTML, XML etc. as well as for language codes such as C# and VB.
So, next time you copy some code from the net you can easily reformat it by using the key combination. Alternately, you can also use Ctrl+K followed by Ctrl+F.

Update on web project

Just spent the last few weeks absorbing all there is to know about theory and practice of

• planning a database, data access layer and business layer
• security concerns in areas of web access, user accounts and admin roles

in an ASP.NET IDE.

Now establishing a live test site to begin building the working DB model. I am backing up the live site before I create a live test site in a subdirectory of the root dir. Ideally, I would see about a test server, but I am looking to turn this around quickly, so I'm going to kick out the jams in a subdirectory instead.

Next up, restricting access to the live database-linked forms and providing a layer of validation. Also, working with binary files, as images will be a large part of the site.

Data Access Layer adding custom classes

When you create DataTables, the custom code is generated as an XML Schema Definition file, or .xsd.  If you right-click the definition for your DataTable in the Solution Explorer you can see the generated markup.  

This is useful to know, because while the autogenerated content does its job fine as is, you can add further stubs of code to modify Data Access operations or customize your results.  I did this by creating a new class file in the App_Code folder.  The new class file is a partial class that allows me to perform a custom method on a Row within a Table once the row has been instantiated.  This is a handy technique to cleanly procedureize a complex filter operation.  

More info on this technique is available at asp.net.

Data Access Layer Last Look at Basics

Now a brief look at how to write SQL Queries for TableAdapters. Consider that we want the table Adapter to:

• Hold a custom set of table values, often a collection of values from several tables
• Handle INSERT, UPDATE and DELETE commands on the TableAdapters

If we make a table with SQL Queries that assemble data from different tables, having this functionality is simple as long as the Queries are structured with subqueries for multiple tables. Stated another way, don't use Joins, use Select statements.  Here is an example of this:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, 

(SELECT CategoryName from Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName

FROM Products

Following this, you can perform SQL queries with IntelliSense evaluation of changed data.  Next an examination of Business Layer Logic.

Inserting, Updating and Deleting Data using the Data Access Layer

There are two patterns commonly used for inserting, updating and deleting data.  

1. A database direct pattern - a rigid 1-to-1 replacement command
   
2. A batch update pattern - pass entire DataSet, DataTable or collection of DataRows to method call that determines appropriate request for each record

Guess which one is more flexible? Indeed, we will be looking at DAL implementations of pattern 2, batch update. By default the TableAdapter uses the batch update pattern. The capability to use this functionality is set in the wizard template under Advanced Options.  You can check the box "Generate Insert, Update and Delete statements," which I did.

If you look at the properties panel of the PropertiesTableAdapter, you will see the commands carried out using the InsertCommand, UpdateCommand and DeleteCommand.  They are available as well.

A generalized use of the batch update pattern to update a DB works like this:

1. Create instance of the ProductsDataTable
2. Call the Adapter method GetProducts() on the instance to populate it.
3. Crawl through each row, performing assignment operations where fitting.
4. Call the Adapter method Update(productsDataTableInstance) on the productsAdapter

That's all it takes.  The method evaluates changes to the table and assigns them to DB.

Now, what about creating a custom method to insert data?  Start by right-clicking on the TableAdapter and going into the wizard. Let's look at the procedure required to add a new product and then return the value of the newly added record's ProductID. Therefore, opt to create an INSERT query in the wizard.

When you get to the SQL Query window, type in this string in preparation of the new method:

INSET INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 

VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);

--Return the ProductID for the newly created record...

SELECT SCOPE_IDENTITY()

Finally, name the new method InsertProduct.

Now all you have to do to add a new product is create parameters for the matching values:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();

//add a new product

int new_productID = Convert.ToInt32(productsAdapter.InsertProduct("New Product", 1, 1, "12 tins per carton", 12.95m, 10, 0, 10, false));

// or to delete the product

productsAdapter.Delete(new_productID);

It's a handy using the DAL once you get to know the details of it. More next time.

Data Access Layer 2, SQL Query Structures

To Reorient from the conclusion of my last post, the current topic is:

1. Creating a DataTable in a Typed DataSet using the TableAdapter Configuration Wizard
2. Supplying a SQL Query string to build a useful DataAdapter class within the DataTable 
3. Working with the methods of the DataAdapter class to access and modify DB lists

I provided an example of a basic SQL Query resulting in a method, GetProducts(), that lists all the products in the database when called. To make it more useful, I need the ability to retrieve information based on specific properties. For this, we can add parameterized methods to the TableAdapter.

Creating parameterized methods for the Table Adapter

The first method created in the DAL is GetProducts(). Now I am going to add to that a GetProductsByCategoryID(categoryID) method. 

Right click the TableAdapter interface and select Add Query from the list. The SQL SELECT statement is similar to the last with one important difference:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued

FROM Products

WHERE CategoryID = @CategoryID

Color highligting added to emphasize the filtering string of the previous query. That is the only difference from the original method that gets the entire table.  The obvious question this begs is how to pass the scalar variable to the SQL query in an actual data event.  Two answers to this.

For quick gratification, you can supply a test parameter to a Preview Data test. Right click on the method in the TableAdapter to do this.

To actually use the TableAdapter method in a .net page, I'll provide a summary overview of the complete framework:

named instance object:

NorthwindTableAdapters.ProductsTableAdapter productAdapter = new NorthwindTableAdapters.ProductsTableAdapter();

dataTable object:

Northwind.ProductsDataTable products;

invoke method (returns a Typed DataSet) and assign to the instance object

products = productsAdapter.GetProductsByByCategoryID(1);

and return it row by row as html

foreach (Northwind.ProductsRow productsRow in products)

Response.Write("Product: " + productRow.ProductName + "
");

As regards an implementation in an .aspx and accompanying .cs, the deviations from a blank page are as follows:

the .aspx page

1. Create GridView .NET component and take note of assigned ID

the .cs page

1. on Page_Load, declare a new instance of ProductsTableAdapter as above
2. assign instance.GetProductsByCategoryID(value) to IDofGridView.DataSource
3. instance.DataBind();

That's it!  No data access-specific code required.

Next up, Inserting, Updating and Deleting Data

Data Access Layer Continued

The process of separating data from presentation makes use of

• A Data Access Layer, using Typed DataSets
• A Business Logic Layer (BLL) that enforces custom business rules and
• A Presentation Layer consisting of ASP.NET pages with a common layout

In the last post, I outlined my strategy for using a DAL in a current project. Following are details of the DataSets used in the DAL.

In preparing the foundation of a DAL, it should be clear how to do the following (so I'm not going to dwell on them):

• Establish a DB Connection
• Add a DataSet from the template list
• Run through the TableAdapter Configuration Wizard

What may be less obvious is the way to structure your hand-written SQL queries so that they conform to your business logic layer. Here is a basic example:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products

Which results in two methods, the ability to

• fill a DataTable - takes in a DataTable as a parameter and populates it based on the query
  
• return a DataTable - creates and fills the DataTable for you and returns it as the method's return value

At this point there is one Typed DataSet (Northwind) with a single DataTable (Products) and a strongly-typed DataAdapter class (NorthwindTableAdapters.ProductsTableAdapter) with a GetProducts() method.

For instance, I create a named instance of a NorthwindTableAdapters.ProductsTableAdapter (Northwind is the ID of my Typed DataSet and Products is the DataTable):

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();
Northwind.ProductsDataTable products;

products = productsAdapter.GetProducts();

foreach (Northwind.ProductsRow productRow in products)
Response.Write("Product: " + productRow.ProductName + "
");

This short but effective code will cycle through each record in the database table and create a list of all the names in the table.

Data Layer Strategy

I'm redesigining the data layer for my current client and am using Microsoft SQL Server Management Studio Express with Visual Studio 2008.

I began by diagramming the relationship of their products and reference information in their annual mail-out catalogs. Then we met to discuss the current product lineup and plans for future products. Following this, I produced some UML diagrams and used note cards with dry erase markers to plot the Unique IDs and Foreign Keys and modularized a RDBM system in preparation of the development process. I also began mocking up some basic UI screens for integration and to assist in the data visualization from their internal team perspective.

The API for their web resolves to two applications, a product inventory and a product photo gallery. Each has a role that defines and supports the other. Using my UML architecture and SQL queries, I developed a schema with the appropriate foundation to begin a DB system.

I started the admin implementation with some models in the .NET IDE. Creating GridViews and binding to the SQL DB, I tested the model with embedded data-specific logic in the presentation layer. The method tested was a combination of ADO.NET code in the ASP.NET code and SqlDataSource controls in the markup. While I was able to populate the data easily, the need to create new markup for every procedure was not an optimal solution.

A better approach for the client, in fact, for any client development project, is the abstraction of the data access logic into a separate layer, called the Data Access Layer, or DAL. Using Visual Studio's DataSet template, you can create predefined SELECT, INSERT, UPDATE and DELETE commands to streamline data binding. It frees up the developer to make better decisions in the implementation process.

Using the DAL allows strongly-typed members in the DataTable, an advantage I'll go into next time. For more information, see Designing Data Tier Components and Passing Data Through Tiers.

NVARCHAR versus VARCHAR

A succinct evaluation from ASP.NET Blogs

SQL Server provides both datatypes to store character information. For the most part the two datatypes are identical in how you would work with them within SQL Server or from an application. The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in your database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages.

SQL Wednesday

Boot up

IIS

Starts Automatically

SQLEXPRESS

Server Starts Manually

Visual Basic 2008

Associations made to SQLEXPRESS in Server Explorer

Initial Development: Code Behind Experiment

Experimenting with the VB concept of code behind

Using C# in an aspx.cs file referenced in the head of the client document

Testing out connections between forms, elements and IDs in C#

SQL TEST #1

Work with Direct Data Access, requires three steps

1. Create Connection object
   
2. Create Command object
3. Create Data Reader object

----------------

CONNECTION OBJECT

Step One:

Different ways to create a connection object.  Need to know:

1. Server Name, aka Data Source (e.g. localhost\SQLEXPRESS)
2. Database Name aka Initial Catalog (e.g. Pubs)
3. Security Type aka Integrated Security (e.g. SSPI)

In C# be sure to import the proper namespaces for the Data Provider you're using. It could be SQL, OLE DB, Oracle or ODBC. I'm using SQL, so the namespaces to import are:

The Data Provider Classes for SQL Server Data Provider are

• Connections - SqlConnection
• Command - SqlCommand
• DataReader - SqlDataReader
• DataAdapter - SqlDataAdapter

And the namespaces to import are

• using System.Data;
• using System.Data.SqlClient; 
  

Once these namespaces are imported, you can create an object of type SqlConnection

SqlConnection myConnection = new SqlConnection();

myConnection.ConnectionString = "Data Source=localhost\SQLEXPRESS;" + "Initial Catalog=Pubs;Integrated Security=SSPI";

This code would normally go in the code behind aka the aspx.cs file of the operative file. Since many different operative files will likely use the same connection string, a better approach is to centralize the connection string for easy universal reference. This could be done as a class member variable. Even better, put it in a configuration file.  

The configuration file is an xml file called web.config.  It sits at the root of the project and can be accesed globally. Thus it is a good place for the connection string info.

I'm placing it in the section of the web.config file.

Retrieve the connection string by name using OO notation after adding namespace System.Web.Configuration:

string connectionString =  WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;

-----------------

COMMAND OBJECT

To actually retrieve data requires

• A SQL statement that selects the information you want
• A Command object that executes the SQL statement
• A DataReader or DataSet object to access the retrieved records

Here's a method

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText = "SELECT * FROM Authors ORDER BY au_lname ";

--------------

DATA READER

to create a DataReader, you use the ExecuteReader() method of the command object:

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

move forward one line at a time using the myReader.Read(); method

with each row, you can add to an object this way:

1stNames.Items.Add(myReader["au_lname"] + ", " + myReader["au_fname"]);

to move to the next row, use the Read() method again. When finished, close the DataReader and Connection

myReader.Close();

myConnection.Close();

Done for now.

ADO.NET Part 1

First Step is connecting to the servers, both http and sql.

This is done in the Server Explorer. For a local connection with Management Studio Express, use server name:
localhost\SQLEXPRESS

then the test db I got from the Microsoft site:
pubs

and success. Tables are listed under Data Connections in the Server Explorer.
left click shows table definition, right click shows table data.

Using the command line results in plenty of Select, Update, Insert and Delete queries.

Creating ADO.NET DB with Visual Studio 2008

Yes, I'm using Visual Studio at the moment.

Though not outright embarrassing, defensiveness creeps in. I'm only using Microsoft for the client.

Besides, nothing wrong with some healthy competitive analysis. I'm learning how the other side thinks. Nah, just more defensiveness.

In any case, I have set up a good test environment for this. IIS running in place of Apache and SQL Management Studio Express in place of MySQL. Have to remember it's called see-qwel rather than my-Ess-Cue-Ell. It's a mental block.

Now to the next post where I chronicle the ADO.NET skulduggery in detail.

html eblast and mailchimp

Looking at helping current client to prepare some eblast templates for a marketing campaign leading up to a tradeshow in February 2009.

In past marketing campaigns, I've found mailchimp.com very useful. They automate the process of creating graphical templates so you don't have to.

One of the biggest hurdles in a mail campaign is meeting all the disparate mail client systems with the greatest possible compliance. A 100% success rate for html presentation is not possible, but companies like mailchimp insure near-optimal results by automating the creation process and placing all metatags up to current standards.

For instance, the template should not be too wide. I'm using their guidelines for a 600px width and their stylesheet system.

Examination of OOP Actionscript 3.0 Design Patterns

I'm looking into Design Patterns for ActionScript.

Long ago, using the authoring tool was the only way of cobbling together a project. That could get messy as version piled upon another. Now coding is the only way to design useful stuff. That gets positively unmanageable over time as versions encroach and gum up the works.

Thus, I am learning how to "keep it simple" by reading all about OOP principles in the O'Reilly book ActionScript 3.0 Design Patterns. I could be out playing badminton in the sun, but this will ultimately be a good use of my time. Read along if you like. I'm having a look at the Factory Method Pattern right now.