softlogger Latest Articles ::DTS-SSIS

Manipulating and Massaging Data in Excel

Microsoft Excel 2007 (nee Excel 12) — 2007-11-12T00:00:00

Today’s author: Chad Rothschiller, a program manager on the Excel team. Chad is going to discuss using formulas to 'clean up' data in Excel.

Overview

Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

Example Data

Here’s the data discussed in this example exercise, feel free to copy/paste it into a spreadsheet and work along through the steps:

SSN,Last Name,Middle Name,First Name,DOB,Date of Letter,E-mail Address,Home Phone,Address,City,State,Postal Code
123456789,freehafer    ,drew   ,nancy,19700101,11/1/2007,NancyF@northwindtraders.com,1235550102,123 1st   Avenue,Seattle ,wa,09999
123456789,cencini        ,a   ,andrew,19700101,11/1/2007,AndrewC@northwindtraders.com,1235550102,123 2nd Avenue,Bellevue,wa,09999
123456789,kotas      ,bollen,jan,19700101,11/1/2007,JanK@northwindtraders.com,1235550102,123 3rd Avenue,Redmond ,wa,09999
123456789,sergienko      ,,mariya,19700101,11/1/2007,MariyaS@northwindtraders.com,1235550102,123     4th Avenue,Kirkland,wa,09999
123456789,thorpe     ,j ,steven,19700101,11/1/2007,steven@northwindtraders.com,1235550102,123 5th Avenue,Seattle,wa,09999
123456789,neipper    ,john,michael,19700101,11/1/2007,MichaelN@northwindtraders.com,1235550102,123 6th Avenue,Redmond,wa,09999
123456789,zare       ,b ,robert,19700101,11/1/2007,RobertZ@northwindtraders.com,1235550102,123 7th Avenue,Seattle,wa,09999
123456789,giussani       ,,laura,19700101,11/1/2007,LauraG@northwindtraders.com,1235550102,123 8th Avenue,Redmond,wa,09999
123456789,hellung-larsen      ,marie,anne,19700101,11/1/2007,AnneHL@northwindtraders.com,1235550102,123 9th Avenue,Seattle   ,wa,09999

Notice a few things about the data:

SSN isn’t formatted, it’s just a raw number
Last Name is all lower case and contains extra spaces
Some people don’t have middle names at all, some are abbreviated, some are spelled out in full
Date of Birth (DOB) is in YYYYMMDD format, while Date of Letter is in MM/D/YYYY format
E-Mail Address contains both upper and lower case characters
Home Phone isn’t formatted
Address has extra spaces
State is all lower case
Postal Code has a leading zero that shouldn’t be dropped

Here’s what needs to be done to the data to get it ready for processing:

Not lose the leading zero when bringing this data into Excel
Format the SSN with dashes in the proper place
Remove extra spaces from the name fields and Address field
Get the names all in one field, in the form of “Last Name, First Name Middle Initial.”, with proper casing
Convert DOB to MM/DD/YYYY format
Make E-Mail Address values be all lower case
Format the Home Phone field according to the standard US phone number format (NNN) NNN-NNNN
Upper case the State values
Sort the data according to these sort keys: State / City / Last Name / First Name / Middle Name (not middle initial)

Step 1: Import the data and don’t accidentally drop the leading zeros!

To Excel, the Postal Code values look like numbers, and so that’s the default way it will treat them when loading this file. (Unfortunately Excel can’t read the header “Postal Code” and switch it to a text field based on the semantic meaning of the data.)

Fortunately there is a way to override this default, using the Text Import Wizard:

File | Open the text file
Text Import Wizard – Step 1 of 3: Choose “Delimited”, then click Next
Text Import Wizard – Step 2 of 3: Choose “Comma” as the delimiter, then click Next (note that other delimiters can be selected, and even a custom one can be specified if the data uses a character not listed in the UI)
Text Import Wizard – Step 3 of 3: Scroll over to the Postal Code field, select it, and set “Column data format” to Text, then click Finish

Best fit the columns (select those columns, double click on the line between column headers), and here’s what the result should look like:

Now that the data has landed in Excel without dropping critical information, next up is to work it into the format needed for reporting and uploading.

Step 2: Formatting SSN

The SSN values need to be converted to the form NNN-NN-NNNN. There are two ways to accomplish this.

Change the way the data is displayed, but keep the underlying values as numbers, or
Insert hyphens into the data

Change the display via cell formatting

Select the range A2:A10 where the data is stored
Bring up the Format Cells dialog (CTRL + 1)
On the Number tab, click on the Custom category
In the Type field, enter this string: 000-00-0000
Click OK

Here’s what the end result looks like:

Note that the underlying value is “123456789” (look in the formula bar to verify this), while the cell is formatted to display the hyphens.

Inserting hyphens

A formula is needed to accomplish this. This example will use the convention of creating a new table of data from the original, where the formulas are in cells off to the right of the original data.

The formula for this will use the MID() function to pull out the first 3 numbers of the SSN. Then it will use the “&” to concatenate that with a hyphen, use the MID() function to grab the middle two numbers, use “&” again to insert another hyphen, and finally use MID() again to tack on the last 4 numbers of the SSN.

Copy/Paste the headers from A1:L1 into N1:Y1 (to not get lost)
In N2, enter this formula: =MID(A2,1,3)&"-"&MID(A2,4,2)&"-"&MID(A2,6,4)
Fill that formula down across all the rows of data (N2:N10 for this example)

Here’s what the result looks like:

To prove it, select the column of SSNs, Copy / Paste Special… Values and examine the contents of the cells. The hyphens are in the right places.

Step 3: Fixing up names

There are several problems to be addressed with the names:

They’re not in proper casing
They contain extra spaces
Only the middle initial is desired

The “&” operator will be used to concatenate the value in Last Name with a comma and space; the “&” operator will be used to add the value from First Name and to add a space to that result; the LEFT() function along with the “&” operator will be used to create and add a middle initial from Middle Name. The last two steps will be to wrap those functions with a TRIM() function to remove extra spaces, and wrap the final result with a PROPER() function to set the letter casing correctly.

For this part, the headers “Last Name”, “Middle Name”, and “First Name” that were created in O1:Q1 aren’t needed. Delete columns P & Q, and rename “Last Name” in O1 to “Name”. The formula will go in column O, using the original Name columns as input.
In O2 insert this function: =PROPER(TRIM(TRIM(B2)&", "&D2&" "&LEFT(C2,1)))

Here’s what the result looks like:

Note: while extra spaces were in the original values, even if they weren’t the TRIM() function would need to be used in this case because for people without Middle Name values listed, the concatenation part of the formula introduces an extra space. That’s also the reason why the TRIM() function should be used on the final result as opposed to deeper in the formula, i.e. on First Name, Last Name, and Middle Name individually.

Note: Extra spaces may not be the only undesirable characters in the data. The CLEAN() function can be used to remove non-printable characters. See Appendix 1 for a more thorough discussion of removing undesirable characters from the data.

Step 4: Convert DOB to an actual date

The DOB values are not recognized by Excel as dates. These need to be converted to MM/DD/YYYY format. Using the DATE() function and MID() function will produce the desired results. The YEAR, MONTH, and DAY portions of the date will be extracted from the DOB field using MID(), and the result is converted to a date using the DATE() function:

In P2 enter this formula: =DATE(MID(E2,1,4),MID(E2,5,2),MID(E2,7,2))
Fill that formula down across the rows of data (P2:P10 in this case)

Here’s what the result looks like:

Step 5: Fill in Date of Letter

No changes are needed to the Date of Letter field, so either copy / paste those values in, or use a formula to refer to them so that the table created is complete.

Step 6: lower case E-Mail Address

For consistency sake, the email addresses should all be lower case, since they aren’t case-sensitive anyhow. This will be accomplished using the LOWER() function.

In cell R2, enter this formula: =LOWER(G2)
Fill that formula down across the rows of data

Here’s what the result looks like:

Step 7: Format Home Phone

The Home Phone values are hard to read, and not in the correct format. They need to be fixed up to match the (NNN) NNN-NNNN format.

Just like SSN, this can be accomplished through cell formatting or inserting the proper characters in the right places.

Change the display via cell formatting

Select the range H2:H10 where the data is stored
Bring up the Format Cells dialog (CTRL + 1)
On the Number tab, click on the Custom category
In the Type field, enter this string: (000) 000-0000
Click OK

Here’s what the end result looks like:

Inserting parenthesis & hyphens

A formula needs to be used to accomplish this. The formula will use MID() and concatenate operations to build up the correct value.

In S2, enter this formula: ="("&MID(H2,1,3)&") "&MID(H2,4,3)&"-"&MID(H2,7,4)
Fill that formula down across all the rows of data (S2:S10 for this example)

Here’s what the result looks like:

Step 8: Trim extra spaces from Address

Use the TRIM() function as done on the name fields to remove extra spaces. This one is easy, so go ahead and do it and move to the next step!

Step 9: Add City values to the new table

Since a whole new table is being created to replace the original data, the values from City need to be brought over, even though there’s nothing wrong with them. If you have a huge data set you might not be able to inspect them all by hand, so in that case you’ll probably still want to use TRIM() and CLEAN() on those values, just in case.

Step 10: Make all State value upper case

The State field contains lower case values, and all the characters for this field need to be upper case. Both PROPER() and LOWER() have already been used to clean up the data, so using UPPER() to convert the lower case characters to uppercase ones is easy.

In V2 enter this formula: =UPPER(K2)
Fill down that formula across all the rows of data (V2:V10 for this example)

Here’s what the result looks like:

Step 11: Fill in Postal Code

To complete the table, copy/paste the Postal Code values from L2:L10 to W2:W10.

Step 12: Finalize the values

Now creating the new table is completed, with the data in the format required. The sheet should be finalized by removing formulas, keeping only the values, and then deleting the original data.

Note: It might be useful to keep the formulas around on a template saved separately, so they don’t have to be built up over and over as different sets of data are processed.

Select the entire range of newly massaged data (N1:W10)
Copy / Paste Special… Values / OK (This step is to ensure the cells contain data values and not formulas. Since the formulas are referencing the original data, The original data can’t be deleted until the formulas are converted into values)
Now select the columns A:M, which include the range of original data (A1:L10) plus the extra space column, and delete those entire columns.

Now the newly cleaned data is the only data set in the sheet. Nice work!

Bonus Discussion: Sorting Text using more than 3 keys

It may be that someone needs to sort data sets using more than 3 keys for the sort. As you might now, Excel 2003 only supports 3 levels of sort keys. Excel 2007 supports a whole lot more, so this exercise is trivial using Excel 2007. Here’s how it could be done using Excel 2003. Assume the original data set needs to be sorted by State / City / Last Name / First Name / Middle Name, consider this similar data set:

A new column can be created (say, in column F), using text concatenation to string together the sort keys. In F2, enter this formula: =E2&D2&A2&C2&B2, and give it a header, like “SortKey”.

Now, sorting on that single column is the same as sorting on those keys individually. Of course, ascending/descending order can’t be specified on each key using this method, so Excel 2007 is still better!

Appendix 1: Cleaning Text

Trim means

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the non-breaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this non-breaking space character.

Clean means

Removes all non-printable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.

Advanced Clean for Unicode Characters using SUBSTITUTE & CHAR

Sometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character set values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.

The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the non-breaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this non-breaking space character.

The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.

To do this task, use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII characters for which the TRIM and CLEAN functions were designed.

Use LINQ with WPF : Styles and DataTemplates in code

Calvin Hsias WebLog — 2007-11-12T00:00:00

Using Windows Presentation Foundation (WPF) and LINQ is easy in code. We'll create a query and display it in a WPF ListBox

Using XAML is harder: the ObjectDataProvider class works with non-anonymous types.

Start Visual Studio 2008 (or 2005 with Visual Studio 2005 extensions for .NET Framework 3.0. Linq is only in 2008)

Choose File->New Project->Visual Basic->WPF Application

You can use the WPF Forms designer, or you can write your code in a program.

Double click the form designer to bring up the Window1.xaml.vb file. Replace the contents with the code below.

Class Window1

Private Sub Window1_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded

Me.Width = 800

Me.Height = 800

Dim Query = From proc In System.Diagnostics.Process.GetProcesses _

Select proc.Id, proc.ProcessName, ThreadCount = proc.Threads.Count, proc.MainWindowTitle

Dim MyListBox As New ListBox

MyListBox.ItemsSource = Query

' lb.ItemsSource = New String() {"one", "two"} ' or a simple array

Me.Content = MyListBox

End Sub

End Class

Hit F5, and now you have a ListBox on a form which displays multiple rows like so:

{ Id = 2312, ProcessName = notepad, ThreadCount =1, MainWindowTitle = t.txt – Notepad, }

Let's make the display a little more friendly by using a DataTemplate.

Add this code before the End Sub:

Dim dt As New DataTemplate

Dim factSP = New FrameworkElementFactory(GetType(StackPanel))

dt.VisualTree = factSP

factSP.SetValue(StackPanel.OrientationProperty, Orientation.Horizontal)

Dim factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding("Id"))

factTb.SetValue(TextBlock.WidthProperty, 40.0)

factTb.SetValue(TextBlock.FontWeightProperty, FontWeights.Bold)

factSP.AppendChild(factTb)

factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding("ProcessName"))

factTb.SetValue(TextBlock.FontStyleProperty, FontStyles.Italic)

factTb.SetValue(TextBlock.WidthProperty, 80.0)

factSP.AppendChild(factTb)

factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding("ThreadCount"))

factTb.SetValue(TextBlock.WidthProperty, 30.0)

factSP.AppendChild(factTb)

factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding("MainWindowTitle"))

factSP.AppendChild(factTb)

MyListBox.ItemTemplate = dt

This code uses a Data Template to map data names with UI elements.

Because there are multiple rows, the template declaratively specifies how the data is presented.

In this case, the multiple rows of the query mapped to multiple ListBoxItems in the ListBox. Each ListBoxItem displays as a factory generated Horizontal StackPanel. The StackPanel contains a factory generated TextBlock for each field. WPF allows all sorts of controls inside other controls, so we could have a movie or even another listbox inside the listbox item.

Some of the TextBlocks have their width or FontStyle set. However, this code is hardcoded for the field names and data types of the query. Let's make it more generic. We can use refection to get the data types and field names of the anonymous type created by the query:

Dim QueryAnonType = _

Query.GetType().GetInterface(GetType(IEnumerable(Of )).FullName).GetGenericArguments()(0)

For Each mem In QueryAnonType.GetMembers

If mem.MemberType = Reflection.MemberTypes.Property Then

Select Case CType(mem, Reflection.PropertyInfo).PropertyType.Name

Case "Int32"

Dim factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding(mem.Name))

factTb.SetValue(TextBlock.WidthProperty, 40.0)

factTb.SetValue(TextBlock.FontWeightProperty, FontWeights.Bold)

factSP.AppendChild(factTb)

Case "String"

Dim factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding(mem.Name))

factTb.SetValue(TextBlock.WidthProperty, 130.0)

factTb.SetValue(TextBlock.FontStyleProperty, FontStyles.Italic)

factTb.SetValue(TextBlock.ForegroundProperty, Brushes.CornflowerBlue)

factSP.AppendChild(factTb)

End Select

End If

Putting it all together, adding some style triggers for fun, the entire code sample is below.

Next time, we'll add headers that sort the columns when clicked and some

Class Window1

Private Sub Window1_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded

Me.Width = 800

Me.Height = 800

Dim Query = From proc In System.Diagnostics.Process.GetProcesses _

Select proc.Id, proc.ProcessName, ThreadCount = proc.Threads.Count, proc.MainWindowTitle

Dim MyListBox As New ListBox

MyListBox.ItemsSource = Query

' lb.ItemsSource = New String() {"one", "two"} ' or a simple array

Me.Content = MyListBox

Dim dt As New DataTemplate

Dim factSP = New FrameworkElementFactory(GetType(StackPanel))

dt.VisualTree = factSP

factSP.SetValue(StackPanel.OrientationProperty, Orientation.Horizontal)

Dim QueryAnonType = _

Query.GetType().GetInterface(GetType(IEnumerable(Of )).FullName).GetGenericArguments()(0)

For Each mem In QueryAnonType.GetMembers

If mem.MemberType = Reflection.MemberTypes.Property Then

Select Case CType(mem, Reflection.PropertyInfo).PropertyType.Name

Case "Int32"

Dim factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding(mem.Name))

factTb.SetValue(TextBlock.WidthProperty, 40.0)

factTb.SetValue(TextBlock.FontWeightProperty, FontWeights.Bold)

factSP.AppendChild(factTb)

Case "String"

Dim factTb = New FrameworkElementFactory(GetType(TextBlock))

factTb.SetBinding(TextBlock.TextProperty, New Binding(mem.Name))

factTb.SetValue(TextBlock.WidthProperty, 130.0)

factTb.SetValue(TextBlock.FontStyleProperty, FontStyles.Italic)

factTb.SetValue(TextBlock.ForegroundProperty, Brushes.CornflowerBlue)

factSP.AppendChild(factTb)

End Select

End If

MyListBox.ItemTemplate = dt

Dim MyListboxStyle As New Style(GetType(ListBoxItem))

Dim tr = New Trigger

tr.Property = ListBoxItem.IsSelectedProperty

tr.Value = True

tr.Setters.Add(New Setter(ListBoxItem.ForegroundProperty, Brushes.Bisque))

MyListboxStyle.Triggers.Add(tr)

tr = New Trigger

tr.Property = ListBoxItem.IsMouseOverProperty

tr.Value = True

tr.Setters.Add(New Setter(ListBoxItem.ForegroundProperty, Brushes.Red))

tr = New Trigger

tr.Property = ListBoxItem.IsMouseOverProperty

tr.Value = True

tr.Setters.Add(New Setter(ListBoxItem.BackgroundProperty, Brushes.Aquamarine))

MyListboxStyle.Setters.Add(New Setter(ListBoxItem.ForegroundProperty, Brushes.Black))

MyListboxStyle.Triggers.Add(tr)

MyListBox.ItemContainerStyle = MyListboxStyle

End Sub

End Class

</Code Sample>

Datareader to Datatable Convertor

VB & .NET Blogs @ vbCity.com — 2007-10-17T00:00:00

On forum today I saw an interesting post where I had advised the ORPO that a datareader could be binded to combobox, having worked on ASP.NET for the past months now its easier than falling over as you can use the 'DropDownList' object. However, for the Windows Combobox object ( regardless of web based development semantics ) you cannot do this as it will pipe up with the friendly...

"Complex DataBinding accepts as a data souce either an IList or an IListSource"

Anyhoo - fix provided kinda went around the houses somewhat and I thought it might be an idea to show my DataReader to DataTable converter class. Basically, the base DbDataAdapter class exposes a 'Fill' routine that can allow the user to fill an instance of a datatable from a datareader instance. By inheriting this class and exposing this routine, you've got an easy converter object that makes binding to a combobox easy without having to a) create a datatable from scratch or b) creating an array (or other object) to contain the data to bind to.

Note: this code is for .NET 1.*, however, in .NET you might want to strongly type the second overloaded 'FillFromReader' method 'datatables' parameter, e.g. from ArrayList to 'Collections.Generic.List(Of DataTable).

Here's the implementation code...

Imports System.Data
Imports System.Data.Common

Public Class DRToTableConverter

Inherits Data.Common.DbDataAdapter

#Region "Comments"

' Converts a datareader to a datatable

#End Region

Public Overloads Function FillFromReader(ByVal dataTableToFill As DataTable, ByVal dataReader As IDataReader) As Integer

' Fills the datatable array with all results from the datareader
Return MyBase.Fill(DataTableToFill, DataReader)

End Function

Public Overloads Function FillFromReader(ByVal dataTables As ArrayList, ByVal dataReader As IDataReader) As Integer

' Fills the datatable array with all results from the datareader
Dim dt As DataTable
Dim noOfRowsAffected As Integer

' Loop through the datareader reading each resultset into a table object
Do

' Create a datatable
dt = New DataTable

' Perform the fill
noOfRowsAffected += MyBase.Fill(dt, dataReader)

' Add to the return collection
dataTables.Add(dt)

Loop While dataReader.NextResult

Return noOfRowsAffected

End Function

#Region "Overridden without implementation"

Protected Overrides Sub OnRowUpdated(ByVal value As System.Data.Common.RowUpdatedEventArgs)

' NOT IMPLEMENTED

End Sub

Protected Overrides Sub OnRowUpdating(ByVal value As System.Data.Common.RowUpdatingEventArgs)

' NOT IMPLEMENTED

End Sub

Protected Overrides Function CreateRowUpdatedEvent(ByVal dataRow As System.Data.DataRow, ByVal command As System.Data.IDbCommand, ByVal statementType As System.Data.StatementType, ByVal tableMapping As System.Data.Common.DataTableMapping) As System.Data.Common.RowUpdatedEventArgs

Return Nothing

End Function

Protected Overrides Function CreateRowUpdatingEvent(ByVal dataRow As System.Data.DataRow, ByVal command As System.Data.IDbCommand, ByVal statementType As System.Data.StatementType, ByVal tableMapping As System.Data.Common.DataTableMapping) As System.Data.Common.RowUpdatingEventArgs

Return Nothing

End Function

#End Region

End Class

And here's an example of using it

' Generate the connection object
Dim sqlDBConn As New SqlConnection("Network Library=DBMSSOCN;Data Source=10.0.0.10;Initial Catalog=MyDatabase;User ID=Username;Password=Password;")
Dim sqlComm As New SqlCommand("SELECT * FROM MyTable", sqlDBConn)

' Open the connection and execute
sqlDBConn.Open()
Dim dr As SqlDataReader = sqlComm.ExecuteReader

' Invoke the convertor object and create our empty datatable
Dim dtConverter As New DRToTableConverter
Dim dt As New DataTable
' Fill from the datareader
dtConverter.FillFromReader(dt, dr)
dtConverter = Nothing

' Set the source of the combobox
With Me.ComboBox1

.DataSource = dr
.ValueMember = "MyValueField"
.DisplayMember = "MyDisplayField"

End With

' Cleanup

HTH - M

Data Transformation pattern

A BULLs view of LIFE and TECHNOLOGY — 2006-09-08T00:00:00

Here's an interesting and informative article on implementation of a Transformation Interface that can serve as an intermediate layer between an application and a data store. This is particularly useful for our scenarios where we need to transform data between the web UI and persistent storage

http://www.atug.com/andypatterns/TI.htm

Enhancing TableAdapters

Dan Wahlins WebLog — 2006-08-31T00:00:00

I've been playing around and researching different ways to enhance TableAdapters when more custom functionality is needed. If you're not familiar with TableAdapters, they act as the glue between a data source and a strongly-typed DataSet/DataTable. You can create them visually using the Visual Studio .NET 2005 DataSet designer and add multiple queries to them that call SQL statements or stored procedures. You can even create new stored procedures right in the VS.NET DataSet designer wizard.

There are several reasons you might want to enhance a TableAdapter:

Support transactions (since these aren't exposed directly from the TableAdapter classes generated by VS.NET 2005)
Have more control over when a connection begins and ends
Perform custom functionality such as sorting after data is retrieved from the database (in cases where you want to override the default sort.
Many others....the sky's the limit!

Fortunately, .NET 2.0 introduces the concept of partial classes which means that more than one class with the same name (but marked with the "partial" keyword) can be stored in separate files. At compile time all of the "partial" classes (with the same name) across one or more files are combined together to create a single class. Because of this great feature you can easily enhance the functionality of a TableAdapter by simply adding a partial class with the same name as the TableAdapter class generated by VS.NET. At compile time, your class and the auto-generated TableAdapter class will be combined into one class that includes your custom enhancements. You could of course inherit from the TableAdapter class as well if you want to override some of its default functionality.

Sahil Malik has a great post about how to leverage partial classes to add transactional functionality and connection lifetime management to a TableAdapter. In his blog you'll see how you can easily create a partial class with the transactional and connection-oriented methods needed. Sometimes you might need to perform simple operations such as overriding the default sort of records retrieved from the database. This can easily be done by adding a partial class with your own custom method. A basic example of adding a custom sort method is shown below:

using System;
using System.Data;

namespace LANLDataTableAdapters
{
    public partial class TutorialTableAdapter : System.ComponentModel.Component
    {
        public virtual LANLData.TutorialRow[] GetAlphaSortedData()
        {
            //DB defines a SortOrder field. This method allows
            //us to override that default sort and sort alphabetically.
            LANLData.TutorialDataTable dt = this.GetData();
            LANLData.TutorialRow[] sortedRows =
                (LANLData.TutorialRow[])dt.Select("1=1", "Description");
            return sortedRows;
        }
    }
}

While it's certainly advisable to perform any sorts in the database if possible, this example provides a way to sort the rows by description after they've been retrieved. There are several ways to do the sort (Array.Sort or a DataView) but using the Select() method proved to be one of the easiest for what I needed (and required the least amount of code). By using partial classes you can easily add whatever enhancements you need to your TableAdapters.

Share this post: Email it! | bookmark it! | digg it! | reddit!