Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

Archive for Data

January 21, 2014

SQL Server Dates

Filed under: Data @ 4:58 pm
One of the challenges of working with data is working with dates.

Three common date data types are:
  • Date
  • DateTime
  • DataTimeOffset

The first two are well known. The third one is newer and not yet widely used. For completeness, all three are detailed below.

Date is just that, a date with no time component.

· Format: YYYY-MM-DD

· Example: 1992-08-20

· Use it whenever you need a date without the time, such as a birth date.

DateTime is the date with a time.

· Format: YYYY-MM-DD hh:mm:ss[.nnn]

· Example: 2014-02-25 11:30:00

· Use it whenever you need a date with the time, such as an appointment date.

DateTimeOffset was new in SQL Server 2008. It represents a date and time with time zone awareness.

· Format: YYYY-MM-DD hh:mm:ss[.nnn] [+|-] hh:mm

· Example: 2014-02-25 11:28:22 -08:00

The -08 represents the time zone offset from UTC (Coordinated Universal Time). I’m on Pacific Standard Time right now, so this is my UTC.

· Use it whenever you need a date and time with knowledge of its time zone, such as a purchase date.

If you ever need to track transactions or other activities across time zones, the DateTimeOffset is the perfect choice.

For example, say a pair of woolen socks was purchased at 10AM in New York (10:00:00 -05:00). Sunscreen was purchased at 9 AM in San Francisco (9:00:00 -08:00). If the orders are processed in the sequence in which they were received, the New York order must be processed first.

Since the dates are time zone aware, operations such as sorting and less than / greater than will correctly handle the dates. No need to convert the dates to UTC.

Enjoy!
February 27, 2010

Binding to a ComboBox using a DataTable and Linq

If you retrieve data into a DataTable, it is easy to bind it to a ComboBox. And before Linq, you would filter the DataTable using a DataView. But with Linq, you have some easy to use features for filtering the contents of your ComboBox.

First, here is the basic code for binding a ComboBox to a DataTable.

In C#:

private DataTable dt = Customers.Retrieve();

ComboBox1.DataSource = dt;
ComboBox1.DisplayMember = "FullName";
ComboBox1.ValueMember = "CustomerId";

In VB:

Private dt As DataTable = Customers.Retrieve

ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "FullName"
ComboBox1.ValueMember = "CustomerId"

The Retrieve method on the Customers class retrieves a DataTable of customers. If you want to try this code, you can build a DataTable in code following the techniques covered here.

You can then set the DataSource to the DataTable, set the DisplayMember to the name of the field to display in the ComboBox, and set the ValueMember to the name of the field to use as the field value. This is most often the unique key.

The result looks like this:

image

With Linq you can add filtering criteria. So let’s add a second ComboBox that lists only the customers with a last name that starts with "B".

NOTE: Be sure to set a reference to System.Data.DataSetExtensions.

In C#:

var query = dt.AsEnumerable().Where(c=>
        c.Field<String>("LastName").StartsWith("B"));

ComboBox2.DataSource = query.AsDataView();
ComboBox2.DisplayMember = "FullName";
ComboBox2.ValueMember = "CustomerId";

In VB:

Dim query = dt.AsEnumerable.Where(Function(c) _
       c.Field(Of String)("LastName").StartsWith("B"))

ComboBox2.DataSource = query.AsDataView
ComboBox2.DisplayMember = "FullName"
ComboBox2.ValueMember = "CustomerId"

This code uses a Lambda expression to filter the DataTable to only those rows where the LastName starts with "B".

The AsEnumerable extension method is necessary to allow Linq/Lambda expressions to work with a DataTable. Any field in the DataTable is accessed using c.Field<T> Or c.Field(Of T) where T is the type of the field. In this example, the field is a string.

The second ComboBox is then bound to the query using the AsDataView extension method. This allows the binding to bind the result as a DataView.

The result looks like this.

image

But wait, there is more. Because of the way that binding works. adding rows to the DataTable will add rows to the first ComboBox that is bound to the DataTable. AND if the new row starts with the letter "B", it will add it to the second ComboBox as well.

In this example, code in the Add button does this:

In C#:

private void Button1_Click(object sender, EventArgs e)
{
    dt.Rows.Add(5, "Bond", "James", "Bond, James", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3);
}

In VB:

Private Sub Button1_Click2(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button1.Click
    dt.Rows.Add(5, "Bond", "James", "Bond, James", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3)
End Sub

Click the button and the result is as follows:

image

No refreshing or re-executing binding code required. It just works!!

Use this technique any time you want to bind a ComboBox to a filtered set of data from a DataTable.

Enjoy!

Build a DataTable in Code

Filed under: C#,Data,VB.NET @ 6:49 pm

There may be times you need to build a DataTable using code instead of retrieving the data from a database.

This post provides the code for building a DataTable using VB or C# code.

In C#:

DataTable dt = new DataTable("Customers");
DataColumn dc;

dc = new DataColumn();
dc.DataType = typeof(int);
dc.ColumnName = "CustomerID";

dt.Columns.Add(dc);
dt.Columns.Add(new DataColumn("LastName"));
dt.Columns.Add(new DataColumn("FirstName"));
// Concatenation of first and last names
dt.Columns.Add(new DataColumn("FullName"));
dt.Columns.Add(new DataColumn("Address"));
dt.Columns.Add(new DataColumn("City"));
dt.Columns.Add(new DataColumn("State"));
dt.Columns.Add(new DataColumn("Zip"));
dt.Columns.Add(new DataColumn("Phone"));

dc = new DataColumn();
dc.DataType = typeof(DateTime);
dc.ColumnName = "LastPurchaseDate";
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = typeof(int);
dc.ColumnName = "CustomerType";
dt.Columns.Add(dc);

// Populate the table
dt.Rows.Add(2, "Baggins", "Bilbo", "Baggins, Bilbo", "Bagshot Row #1", "Hobbiton", "SH", "00001", "555-2222", DateTime.Parse("9/24/2008"), 1);
dt.Rows.Add(1, "Baggins", "Frodo", "Baggins, Frodo", "Bagshot Row #2", "Hobbiton", "SH", "00001", "555-1111", DateTime.Parse("9/14/2008"), 1);
dt.Rows.Add(6, "Bolger", "Fatty", "Bolger, Fatty", "ProudFeet Creek", "Hobbiton", "SH", "00001", "555-1111",  DateTime.Parse("9/14/2008"), 1); dt.Rows.Add(4, "Elessar", "Aragorn", "Elessar, Aragorn", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0000", DateTime.Parse("9/14/2008"), 4);
dt.Rows.Add(5, "Evenstar", "Arwin", "Evenstar, Arwin", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0001", DateTime.Parse("9/23/2008"), 4);
dt.Rows.Add(3, "Greyhame", "Gandalf", "Grayhame, Gandalf", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3);

In VB:

Dim dt As New DataTable("Customers")
Dim dc As DataColumn

dc = New DataColumn
With dc
    .DataType = GetType(Integer)
    .ColumnName = "CustomerID"
End With
dt.Columns.Add(dc)
dt.Columns.Add(New DataColumn("LastName"))
dt.Columns.Add(New DataColumn("FirstName"))
‘ Concatenation of first and last names
dt.Columns.Add(New DataColumn("FullName"))
dt.Columns.Add(New DataColumn("Address"))
dt.Columns.Add(New DataColumn("City"))
dt.Columns.Add(New DataColumn("State"))
dt.Columns.Add(New DataColumn("Zip"))
dt.Columns.Add(New DataColumn("Phone"))

dc = New DataColumn
With dc
    .DataType = GetType(Date)
    .ColumnName = "LastPurchaseDate"
End With
dt.Columns.Add(dc)

dc = New DataColumn
With dc
    .DataType = GetType(Integer)
    .ColumnName = "CustomerType"
End With
dt.Columns.Add(dc)

‘ Populate the table
dt.Rows.Add(2, "Baggins", "Bilbo", "Baggins, Bilbo", "Bagshot Row #1", "Hobbiton", "SH", "00001", "555-2222", #9/24/2008#, 1)
dt.Rows.Add(1, "Baggins", "Frodo", "Baggins, Frodo", "Bagshot Row #2", "Hobbiton", "SH", "00001", "555-1111", #9/23/2008#, 1)
dt.Rows.Add(6, "Bolger", "Fatty", "Bolger, Fatty", "ProudFeet Creek", "Hobbiton", "SH", "00001", "555-1111", #9/14/2008#, 1)
dt.Rows.Add(4, "Elessar", "Aragorn", "Elessar, Aragorn", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0000", #9/23/2008#, 4)
dt.Rows.Add(5, "Evenstar", "Arwin", "Evenstar, Arwin", "Citadel", "Minas Tirith", "Gondor", "00000", "555-0001", #9/23/2008#, 4)
dt.Rows.Add(3, "Greyhame", "Gandalf", "Grayhame, Gandalf", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3)

The code starts by creating a new DataTable. This one is called "Customer". The next set of code defines the columns. This example demonstrates how to build integer, string, and date columns.

Finally, the last portion of the code populates several rows in the DataTable.

The result looks like this:

image

Use this technique any time you need to build a DataTable in code.

Enjoy!

EDITED 4/17/10: Corrected an error in the C# code data.

September 25, 2009

DAL: Using a Data Provider Factory

Filed under: C#,Data,VB.NET @ 6:59 pm

Whether it be SQL Server, Access, Oracle, or mySql, most applications write to one kind of database. But what if your application requirements are such that you have to support multiple database types? Then the DbProviderFactory is for you.

For example, say you are building a product to sell to HR departments. Some of your customers want to leverage their existing SQL Server infrastructure and will only buy the product if it supports SQL Server. Other customers want your application to work in an all Oracle environment, still others would prefer mySql. So what do you do? Build multiple versions of the product? No need!

Part of ADO.NET, the DbProviderFactory allows you to have one set of code that works with any type of database that supports a data provider such as ODBC, OleDb, or SQL Server.

The code below takes a stored procedure and a set of parameters and returns a DataTable. You could easily adjust this code to take a sql string instead of a stored procedure or to return a DataReader instead of a DataTable.

NOTE: This post assumes you already have a stored procedure defined to select the required data from your database. For more information on stored procedures, see this link.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to import the System.Data,  System.Data.Common and System.Collections.Generic namespaces.

NOTE: If you plan to cut and paste this code, be sure to define two application settings: ARConnectionString to specify a connection string appropriate for your database and ARProviderName to specify an appropriate provider name for your database (such as System.Data.SqlClient or System.Data.OleDb)

In C#:

public static class Dac
{

public static DataTable ExecuteDataTable(string storedProcedureName,
                          Dictionary<string,object> paramDictionary) 
  {
    DataTable dt = new DataTable();

    // Get the data factory
    DbProviderFactory df = DbProviderFactories.
               GetFactory(Properties.Settings.Default.ARProviderName);

    // Open the connection
    using (DbConnection cnn = df.CreateConnection())
    {
        cnn.ConnectionString =
                      Properties.Settings.Default.ARConnectionString;
        cnn.Open();

        // Define the command
        using (DbCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;

            // Handle the parameters
            if (paramDictionary != null)
            {
                DbParameter param;
                foreach (string key in paramDictionary.Keys)
                {
                    param = cmd.CreateParameter();

                    // The parameter name depends on the provider
                    switch (df.GetType().Name)
                    {
                        case "SqlClientFactory":
                            param.ParameterName = "@" + key;
                            break;
                        case "OracleClientFactory":
                            param.ParameterName = ":" + key;
                            break;
                        case "OleDbFactory":
                        case "OdbcFactory":
                            param.ParameterName = "?";
                            break;
                        default:
                            break;
                    }
                    param.Value = paramDictionary[key];
                    cmd.Parameters.Add(param);
                }
            }

            // Define the data adapter and fill the dataset
            using (DbDataAdapter da = df.CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
        }
    }
    return dt;
}
}

In VB:

Public Class Dac

Public Shared Function ExecuteDataTable( _
     ByVal storedProcedureName As String, _ 
     ByVal paramDictionary As Dictionary(Of String, Object)) _
                                                     As DataTable

    Dim dt As New DataTable

    ‘ Get the data factory
    Dim df As DbProviderFactory = DbProviderFactories. _
                        GetFactory(My.Settings.ARProviderName)

    ‘ Open the connection
    Using cnn As DbConnection = df.CreateConnection
        cnn.ConnectionString = My.Settings.ARConnectionString
        cnn.Open()

        ‘ Define the command
        Using cmd As DbCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedProcedureName

            ‘ Handle the parameters
            If paramDictionary IsNot Nothing Then
                Dim param As DbParameter
                For Each key As String In paramDictionary.Keys
                    param = cmd.CreateParameter()
                    ‘ The parameter name depends on the provider
                    Select Case df.GetType.Name
                        Case "SqlClientFactory"
                            param.ParameterName = "@" & key
                        Case "OracleClientFactory"
                            param.ParameterName = ":" & key
                        Case "OleDbFactory", "OdbcFactory"
                            param.ParameterName = "?"
                    End Select
                    param.Value = paramDictionary(key)
                    cmd.Parameters.Add(param)
                Next
            End If

            ‘ Define the data adapter and fill the dataset
            Using da As DbDataAdapter = df.CreateDataAdapter
                da.SelectCommand = cmd
                da.Fill(dt)

            End Using
        End Using
    End Using

    Return dt
End Function
End Class

The ExecuteDataTable function is in my Dac (data access component) class and defined to be public and static (shared in VB). It is public so it can be called from any other code. It is static/shared because it does not use or retain any state. That allows the function to be called without creating a instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code returns a DataTable, it assumes that the stored procedure contains one Select statement. Here is an example of a SQL Server stored procedure:

CREATE PROCEDURE dbo.CustomerRetrieveAll
AS
    SELECT    
                CustomerId,
                LastName,
                FirstName
    FROM       
                Customer
    ORDER BY
                LastName + ‘, ‘ + FirstName

NOTE: The structure and syntax of stored procedures in other database platforms may differ.

The second parameter is a Dictionary that defines any parameters required by the stored procedure. The dictionary key is the name of the parameter and the value is the value of the parameter. In the CustomerRetrieveAll example, there are no parameters. But your stored procedure may have parameters to filter the data.

The first line of code defines a variable for the DataTable. It is used as the return value from the function.

The next statement sets up the data provider factory. The GetFactory method returns an appropriate strongly typed data factory based on a string value representing the provider name. For SQL Server, this value is "System.Data.SqlClient", for an OleDb database, it is "System.Data.OleDb". Once you create a data factory, you can use its methods to define other data access objects.

In this example, the name of the provider is stored in a configuration file using the Settings feature in Visual Studio. (See this for information on settings in C# and this for information on settings in VB.) This allows you to change the configuration file to change the database provider used by the application. You will need to replace the property setting with a setting or provider name appropriate for your database.

You can then use the data factory to create a connection object of the appropriate type as shown in the first Using statement.

The code then defines the connection string. In this example, the connection string is also stored in the configuration file using the Settings feature in Visual Studio. You will need to replace the connection string in the example with a setting or connection string appropriate for your database.

The connection is then opened.The connection is automatically closed at the end of the Using block.

The second Using statement creates the command. In this case, the command is a stored procedure and the CommandText property of the command defines the stored procedure name.

If parameters were passed in, the parameters are added to the command’s Parameters. The format of name of the parameter is dependent on the provider, so a switch/select statement is used to set the appropriate name.

The third Using statement defines a DataAdapter, again using the data factory. The Fill method of the DataAdapter fills the DataTable with the data returned from the stored procedure.

Since the code uses the Using statement, all of the objects are appropriately closed and disposed.

The resulting filled DataTable is then returned.

As an example, this is how you call this method.

In C#:

DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveAll", 
                                            null);

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveAll", _
                                            nothing)

If the stored procedure had parameters, you would call it like this:

In C#:

Dictionary<string, object> paramDictionary =
                    new Dictionary<string, object>();
paramDictionary.Add("CustomerId", custId);
DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveById",
                                            paramDictionary);

In VB:

Dim paramDictionary As New Dictionary(Of String, Object)
paramDictionary.Add("CustomerId", custId)
Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveById", _
                                                  paramDictionary)

EDITED 9/28/09:

The code did not handle DbParameters correctly in the first draft of this post. This error was corrected in both the ExecuteDataTable method and in the calling examples.

EDITED 12/23/09:

When I made the above edit, I neglected to update the C# function signature. Corrected it above.

Enjoy!

August 25, 2009

Update a Microsoft Access Database

Filed under: C#,Data,VB.NET @ 7:29 pm

Despite the fact that there is a free version of SQL Server called SQL Server Express, there are still applications that require using a Microsoft Access database. But since these are dwindling in number, there are few articles or posts devoted to accessing Access.

This post (and my prior post that demonstrates how to retrieve Access data) attempt to rectify that issue.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to set a reference to System.Data.OleDb.

In C#:

string update = "Update Customer Set Title = ? Where PersonId = ?";
string cnnString =
     
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;";

using (var cnn = new OleDbConnection(cnnString))
{
    cnn.Open();

    using (var cmd = new OleDbCommand(update, cnn))
     {
        // Add the parameters
        cmd.Parameters.AddWithValue("Title", "President");
        cmd.Parameters.AddWithValue("PersonId", 1);

        // Execute the command
        cmd.ExecuteNonQuery();
    }
}

In VB:

Dim update As String = _
    "Update Customer Set Title = ? Where PersonId = ?"
Dim cnnString As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"

Using cnn As New OleDbConnection(cnnString)
    cnn.Open()

    Using cmd As New OleDbCommand(update, cnn)
        ‘ Add the parameters
        cmd.Parameters.AddWithValue("Title", "President")
        cmd.Parameters.AddWithValue("PersonId", 1)

        ‘ Execute the command
        cmd.ExecuteNonQuery()
    End Using
End Using

The code begins by declaring several variables. The first variable is the Update statement. Change it to update the desired fields from the desired table. Use "?" as placeholders for command parameters.

The connection string uses the Jet OleDb data provider. Change the connection string Data Source property to the directory and name of your Access database file. If you provide no directory (like in this example), it will assume that the file is located in the same directory as the executing application.

The first using statement defines the connection. The connection is then opened. The connection is automatically closed at the end of the using block.

The second using statement creates the command using the defined query and opened connection. Use the AddWithValue method of the command parameter’s collection to add the parameter names and values. There should be an AddWithValue method call for each "?" placeholder in the Update statement.

Finally, execute the command using the ExecuteNonQuery method.

Enjoy!

Retrieve a DataTable using Microsoft Access

Filed under: C#,Data,VB.NET @ 7:20 pm

Despite the fact that there is a free version of SQL Server called SQL Server Express, there are still applications that require using a Microsoft Access database. But since these are dwindling in number, there are few articles or posts devoted to accessing Access.

This post (and my next post that demonstrates how to update Access data)attempt to rectify that issue.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to set a reference to System.Data.OleDb.

In C#:

string query = "Select * from Customer";
string cnnString =
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;";
DataTable dt  = new DataTable("Customer");

using (var cnn = new OleDbConnection(cnnString))
{
    cnn.Open();

    using (var da = new OleDbDataAdapter())
    { 
        da.SelectCommand = new OleDbCommand(
            query, cnn);

        // Populate the DataTable
        da.Fill(dt);
    }
}

In VB:

Dim query As String = "Select * from Customer"
Dim cnnString As String = _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"
Dim dt As DataTable = New DataTable("Customer")

Using cnn As New OleDbConnection(cnnString)
    cnn.Open()

    Using da As New OleDbDataAdapter()

        da.SelectCommand = New OleDbCommand( _
            query, cnn)

        ‘ Populate the DataTable
        da.Fill(dt)
    End Using
End Using

The code begins by declaring several variables. The first variable is the Select statement. Change it to select the desired fields from the desired table.

NOTE: Even though Select * was shown in this example, good programming practices dictates specifying field names in the Select clause and not using the Select * syntax.

The connection string uses the Jet OleDb data provider. Change the connection string Data Source property to the directory and name of your Access database file. If you provide no directory (like in this example), it will assume that the file is located in the same directory as the executing application.

The code then creates an instance of the DataTable, giving it a name of Customer. Rename the table as appropriate for your application.

The first using statement defines the connection. The connection is then opened. The connection is automatically closed at the end of the using block.

The second using statement defines a data adapter. The SelectCommand property of the data adapter is set to the defined query and opened connection. The data adapter is then used to fill the DataTable.

Use the technique defined here to display the resulting DataTable.

Enjoy!

Reading Fixed Length Files

Filed under: C#,Data,Text Files,VB.NET @ 3:34 pm

There may be times that you need to read fixed length files into your application. For example, you obtain output from a legacy system or other application in a fixed length text file format, and you need to read and use that data in your application.

NOTE: For more information on fixed length files, see this link.

.NET provides several techniques for reading text files. This post focuses on how to read a fixed length text file into a DataTable.

You may find it very useful to read your text file into a DataTable, whether or not you plan to use a database. Reading a text file into a DataTable not only saves you a significant amount of string manipulation coding, it also makes it easy to access the imported data from within your application.

For example, you can use binding to bind the resulting DataTable to a grid or other controls. You can use Linq to DataTables like in this example to manipulate the resulting data. All of the features of the DataTable are then available to you.

BIG NOTE: Many developers have ignored this technique because one look at the code and the developer assumed it is somehow associated with a database, it is NOT. This is referring to in-memory DataTable objects.

For this example, the text file appears as follows:

000001  Baggins             Bilbo     20090811
000002  Baggins             Frodo     20090801
000003  Gamgee              Samwise   20090820
000004  Cotton              Rosie     20090821

Notice several things about this file:

  1. The columns are a fixed width.
  2. There is no header row that provides the column names. You could add column headers here if desired.

The first step in reading the file is to define a schema.ini file that defines the column widths. The file must follow these specifications:

  • The file must be called schema.ini.
  • The file must exist in the same directory as the text file.
  • The file must be in ANSI format. (See the note at the bottom of this post for information on saving a file to ANSI format.)

The contents of the schema.ini file for the example above is shown below:

[testFixed.txt]
ColNameHeader=False
Format=FixedLength
DateTimeFormat=yyyymmdd
Col1=CustomerId Text Width 6
Col2=LastName Text Width 22
Col3=FirstName Text Width 10
Col4=LastUpdateDate DateTime Width 8

The first line of the file is always the name of the associated text file enclosed in square brackets ([ ]).

The next set of lines define basic attributes of the text file:

  • ColNameHeader: In this case, there is no column header in the text file, so this property is set to false. The system will assume that the first line of the text file is the header unless you specify otherwise.
  • Format: In this case, the format is FixedLength. The system will assume comma delimited unless you specify otherwise.
  • DateTimeFormat: If you have a date in your file, you can specify the format here.

The last set of lines defines each column in the text file. The format of these lines are as follows:

Colx=ColumnName ColumnType Width ColumnWidth

See this link for more information on the contents of the schema.ini file.

You can then read the file using the following code.

In C#:

string fileName = "testFixed.txt";
string dirName = Path.GetDirectoryName(Application.ExecutablePath);
DataTable dt;

using (OleDbConnection cn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
            "Data Source=" + dirName + ";" +
            "Extended Properties=\"Text;\""))
{
    // Open the connection
    cn.Open();

    // Set up the adapter
    using (OleDbDataAdapter adapter =
        new OleDbDataAdapter("SELECT * FROM " + fileName, cn))
    {
        dt = new DataTable("Customer");
        adapter.Fill(dt);
    }
}

In VB:

Dim fileName As String = "testCSV.txt"
Dim dirName As String = _
            Path.GetDirectoryName(Application.ExecutablePath)
Dim dt As DataTable

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
            "Data Source=" & dirName & ";" & _
            "Extended Properties=""Text;""")
    ‘ Open the connection
    cn.Open()

    ‘ Set up the adapter
    Using adapter As New OleDbDataAdapter( _
            "SELECT * FROM " & fileName, cn)
        dt = New DataTable("Customer")
        adapter.Fill(dt)
    End Using
End Using

This code starts by declaring variables to hold the text file name, directory containing the file and the resulting DataTable.

This technique only works with a standard set of file name extensions (see the NOTE at the end of this post). The file can reside in any directory. In this example, the file resides in the same directory where the application is executed. But this is not a requirement.

The first using statement in the example code sets up the connection string for connecting to the directory. It sets the Provider property to use the Microsoft.Jet.OleDb provider. The Data Source property defines the directory containing the text file. The Extended Properties define that the file will be Text ("Text"). The Extended Properties must be within quotes, so double-quotes (VB) or slash quote (C#) are used to escape the quotes.

If a schema.ini file exists in the directory defined as the data source and has a bracketed entry with the text file name, that .ini file is used to determine any other extended properties. So no other extended properties are defined in the connection string itself.

The code then opens the connection, thereby opening the file and the associated schema.ini file. Since this code is in a using statement, the files are automatically closed at the end of the using block.

The second using statement sets of the DataAdapter by defining a Select statement and the open connection. The Select statement selects all of the information from a specific file as defined by the fileName variable.

The code then creates the DataTable, giving the table a name. In this example, the table name is "Customer".

Finally, it uses the Fill method of the TableAdapter to read the data from the text file into the DataTable.

Using the technique detailed here, you can view the resulting DataTable. The column headings were defined by the header in the text file. If you don’t have a header, the columns will be giving a default name.

image

Note how the date in the above screen shot appears as a standard date column.

You can then access the data in the table as you access any other DataTable. For example:

In C#:

foreach (DataRow dr in dt.Rows)
{
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}",
                dr["CustomerId"],
                dr["LastName"],
                dr["FirstName"],
                dr["LastUpdateDate"]);

}

In VB:

For Each dr As DataRow In dt.Rows
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}", _
                dr("CustomerId"), _
                dr("LastName"), _
                dr("FirstName"), _
                dr("LastUpdateDate"))
Next

NOTE:

By default, this technique only works with .txt, .csv, .tab, and .asc file extensions. If your file name has a different extension, you can either change the extension in your code before reading the file, or you can update the Extensions key in following registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

NOTE:

By default, this technique assumes you are working with ANSI text files. If that is not the case, you can update the CharacterSet key in the same registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Though this is not recommended.

VERY IMPORTANT NOTE:

If you test this sample code by creating a text file with Visual Studio, the resulting text file will be in UTF-8 format. You need to save the file into ANSI format. The easiest way I found to do this is detailed below.

Adding a Text File to your Project:

  1. Right-click on your project in Visual Studio.
  2. Select Add | New Item from the context menu.
  3. Pick Text File from the available templates and click Add.
  4. Type in the data for the test file or paste in the text from the example at the top of this post.
  5. Save the file within Visual Studio. This creates a UTF-8 formatted file.
  6. If you plan to use the directory of the executing application, set the Copy to Output Directory to Copy always in the properties window for the file.

Converting the resulting UTF-8 file to ANSI format:

  1. Right-click on the file and select Open With
  2. Select Notepad.
  3. Select File | Save As.
  4. Set the Encoding to ANSI and click Save.
Enjoy!

Reading Comma Delimited Files

Filed under: C#,Data,Text Files,VB.NET @ 2:14 pm

There may be times that you need to read comma separated value (CSV) files into your application. For example, you obtain output from a legacy system or other application in a comma delimited text file format, and you need to read and use that data in your application.

NOTE: For more information on delimited files, see this link.

.NET provides several techniques for reading text files. This post focuses on how to read a comma delimited text file into a DataTable.

You may find it very useful to read your text file into a DataTable, whether or not you plan to use a database. Reading a text file into a DataTable not only saves you a significant amount of string manipulation coding, it also makes it easy to access the imported data from within your application.

For example, you can use binding to bind the resulting DataTable to a grid or other controls. You can use Linq to DataTables like in this example to manipulate the resulting data. All of the features of the DataTable are then available to you.

BIG NOTE: Many developers have ignored this technique because one look at the code and the developer assumed it is somehow associated with a database, it is NOT. This is referring to in-memory DataTable objects.

For this example, the text file appears as follows:

CustomerId, LastName, FirstName, LastUpdateDate
1,  Baggins, Bilbo, 20090811 
2,  Baggins, Frodo, 20090801 
3,  Gamgee,  Samwise, 20090820 
4,  Cotton,  Rosie, 20090821

Notice several things about this file:

  1. It is a comma separated value (CSV) file.
  2. The first line provides the column names. This is optional.

You can read this text file into a DataTable using OleDb as follows.

In C#:

string fileName = "testCSV.txt";
string dirName = Path.GetDirectoryName(Application.ExecutablePath);
DataTable dt;

using (OleDbConnection cn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
            "Data Source=" + dirName + ";" +
            "Extended Properties=\"Text;HDR=Yes;FMT=Delimited\""))
{
    // Open the connection
    cn.Open();

    // Set up the adapter
    using (OleDbDataAdapter adapter =
        new OleDbDataAdapter("SELECT * FROM " + fileName, cn))
    {
        dt = new DataTable("Customer");
        adapter.Fill(dt);
    }
}

In VB:

Dim fileName As String = "testCSV.txt"
Dim dirName As String = _
            Path.GetDirectoryName(Application.ExecutablePath)
Dim dt As DataTable

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
            "Data Source=" & dirName & ";" & _
            "Extended Properties=""Text;HDR=Yes;FMT=Delimited""")
    ‘ Open the connection
    cn.Open()

    ‘ Set up the adapter
    Using adapter As New OleDbDataAdapter( _
            "SELECT * FROM " & fileName, cn)
        dt = New DataTable("Customer")
        adapter.Fill(dt)
    End Using
End Using

This code starts by declaring variables to hold the text file name, directory containing the file and the resulting DataTable.

This technique only works with a standard set of file name extensions (see the NOTE at the end of this post). The file can reside in any directory. In this example, the file resides in the same directory where the application is executed. But this is not a requirement.

The first using statement in the example code sets up the connection to the directory. It sets the Provider property to use the Microsoft.Jet.OleDb provider. The Data Source property defines the directory containing the text file. The Extended Properties define that the file will be Text ("Text"), it has a header (HDR=Yes), and it is in a delimited file format (FRM=Delimited). The Extended Properties must be within quotes, so double-quotes (VB) or slash quote (C#) are used to escape the included quotes.

The code then opens the connection, thereby opening the file. Since this code is in a using statement, the file is automatically closed at the end of the using block.

The second using statement sets of the DataAdapter by defining a Select statement and the open connection. The Select statement selects all of the information from a specific file as defined by the fileName variable.

The code then creates the DataTable, giving the table a name. In this example, the table name is "Customer".

Finally, it uses the Fill method of the TableAdapter to read the data from the text file into the DataTable.

Using the technique detailed here, you can view the resulting DataTable. The column headings were defined by the header in the text file. If you don’t have a header, the columns will be giving a default name.

image

You can then access the data in the table as you access any other DataTable. For example:

In C#:

foreach (DataRow dr in dt.Rows)
{
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}",
                dr["CustomerId"],
                dr["LastName"],
                dr["FirstName"],
                dr["LastUpdateDate"]);

}

In VB:

For Each dr As DataRow In dt.Rows
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}", _
                dr("CustomerId"), _
                dr("LastName"), _
                dr("FirstName"), _
                dr("LastUpdateDate"))
Next

NOTE:

By default, this technique only works with .txt, .csv, .tab, and .asc file extensions. If your file name has a different extension, you can either change the extension in your code before reading the file, or you can update the Extensions key in following registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

NOTE:

By default, this technique assumes you are working with ANSI text files. If that is not the case, you can update the CharacterSet key in the same registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Though this is not recommended.

VERY IMPORTANT NOTE:

If you test this sample code by creating a text file with Visual Studio, the resulting text file will be in UTF-8 format. You need to save the file into ANSI format. The easiest way I found to do this is detailed below.

Adding a Text File to your Project:

  1. Right-click on your project in Visual Studio.
  2. Select Add | New Item from the context menu.
  3. Pick Text File from the available templates and click Add.
  4. Type in the data for the test file or paste in the text from the example at the top of this post.
  5. Save the file within Visual Studio. This creates a UTF-8 formatted file.
  6. If you plan to use the directory of the executing application, set the Copy to Output Directory to Copy always in the properties window for the file.

Converting the resulting UTF-8 file to ANSI format:

  1. Right-click on the file and select Open With
  2. Select Notepad.
  3. Select File | Save As.
  4. Set the Encoding to ANSI and click Save.
Enjoy!
July 23, 2009

Linq: Sorting a DataTable

Filed under: C#,Data,Lambda Expressions,LINQ,VB.NET @ 6:24 pm

Last night, my husband (who is also a .NET developer) asked me about sorting a DataTable by user-defined columns. (Yes, we are a pretty exciting couple!)

Most developers that work with DataTables know how to sort the DataTable using a DataView.Sort. If you are interested in that technique, you can view the msdn documentation here. This post is about sorting using Linq.

NOTE: Be sure to set a reference to System.Data.DatasetExtensions.

If you know ahead of time which columns of the DataTable should be sorted, you can use LINQ to DataTables like this.

In C#:

var query = from c in dt.AsEnumerable()
            orderby c.Field<DateTime?>("LastPurchaseDate"),
                    c.Field<string>("LastName") descending
            select c;
DataView dv   = query.AsDataView();

In VB:

Dim query = From c In dt.AsEnumerable _
            Order By c.Field(Of DateTime?)("LastPurchaseDate"), _
                     c.Field(Of String)("LastName") Descending
Dim dv As DataView = query.AsDataView

The dt variable represents the table you wish to sort. The AsEnumerable is an extension method on the DataTable that allows you to use Linq with it. The Order By clause takes any number of columns. You must specify the data type and then the column name or index. To sort descending, use the Descending keyword.

A nullable DateTime (represented by DateTime?) ensures that the code correctly handles any null values in the table.

In the above examples, the data is sorted first by LastPurchaseDate (ascending) and then by LastName (descending).

The resulting DataView can be bound to a grid or other control.

If you prefer Lambda expressions, you can do this same sort as follows:

In C#:

var query2 = dt.AsEnumerable()
          
.OrderBy(c=> c.Field<DateTime?>("LastPurchaseDate"))
           .ThenByDescending(c=> c.Field<string>("LastName"));
DataView dv2   = query.AsDataView();

In VB:

Dim query2 = dt.AsEnumerable _
    .OrderBy(Function(c) c.Field(Of DateTime?)("LastPurchaseDate")) _
    .ThenByDescending(Function(c) c.Field(Of String)("LastName"))
Dim dv2 As DataView = query.AsDataView

This code performs the same sort as the prior examples.

But neither of these techniques work well if you want the user to select any number of columns to use for the sort. In that case, you need something a little more full-featured.

The first step to building a more generalized sort is to build your own comparer class.

In C#:

private class RowComparer : IComparer<DataRow>
{
    public Dictionary<int, SortOrder> SortColumns { get; set; }

    public int Compare(System.Data.DataRow x, System.Data.DataRow y)
    {
        int returnValue  = 0;
        foreach (int key in SortColumns.Keys)
        {
            int compareResult ;

            // Check for nulls
            if (x.ItemArray[key] == DBNull.Value
                       && y.ItemArray[key] == DBNull.Value)
                compareResult = 0;
            else if (x.ItemArray[key] == DBNull.Value)
                compareResult = -1;
            else if (y.ItemArray[key] == DBNull.Value)
                compareResult = 1;
            else
            {
                // Execute the compare based on the column type
                if (x.Table.Columns[key].DataType.Name ==
                                            typeof(Decimal).Name)
                    compareResult =
                         Decimal.Compare((decimal)x.ItemArray[key],
                                         (decimal)y.ItemArray[key]);

                else if (x.Table.Columns[key].DataType.Name ==
                                            typeof(DateTime).Name)
                    compareResult =
                         DateTime.Compare((DateTime)x.ItemArray[key],
                                          (DateTime)y.ItemArray[key]);
                else
                    // Compare anything else as a string
                    compareResult =
                         String.Compare(x.ItemArray[key].ToString(),
                                        y.ItemArray[key].ToString());
            }

            if (compareResult != 0)
            {
                returnValue =
                   SortColumns[key] == SortOrder.Ascending ?
                                  compareResult: -compareResult;
                break;
            }
        }
        return returnValue;
    }
}

In VB:

Public Class RowComparer
    Implements IComparer(Of DataRow)

    Private _sortColumns As Dictionary(Of Integer, SortOrder)
    Public Property SortColumns() As Dictionary(Of Integer, SortOrder)
        Get
            Return _sortColumns
        End Get
        Set(ByVal value As Dictionary(Of Integer, SortOrder))
            _sortColumns = value
        End Set
    End Property

    Public Function Compare(ByVal x As System.Data.DataRow, _
         ByVal y As System.Data.DataRow) As Integer _
         Implements System.Collections.Generic.IComparer( _
                            Of System.Data.DataRow).Compare
        Dim returnValue As Integer = 0
        For Each key As Integer In SortColumns.Keys
            Dim compareResult As Integer

            ‘ Handle DBNull.
            Dim xValue As Object = _
              If(x.Item(key) Is DBNull.Value, Nothing, x.Item(key))
            Dim yValue As Object = _
              If(y.Item(key) Is DBNull.Value, Nothing, y.Item(key))

            ‘ Execute the appropriate compare based on the column type
            Select Case x.Table.Columns(key).DataType.Name
                Case GetType(Decimal).Name
                    compareResult = _
                        Decimal.Compare(CType(xValue, Decimal), _
                                      
CType(yValue, Decimal))

                Case GetType(DateTime).Name
                    compareResult = _
                        DateTime.Compare(CType(xValue, DateTime), _
                                         CType(yValue, DateTime))

                Case Else
                    ‘ Compare anything else as a string
                    compareResult = _
                         String.Compare(x.Item(key).ToString, _
                                        y.Item(key).ToString)
            End Select

            If compareResult <> 0 Then
                returnValue = _
                  If(SortColumns(key) = SortOrder.Ascending, _
                          compareResult, -compareResult)
                Exit For
            End If
        Next
        Return returnValue

    End Function
End Class

This class defines a comparer to use when sorting a DataRow. The sortColumns property is a Dictionary that stores the index of the column to use as the sort, and a SortOrder to define whether to sort ascending or descending. Presumably, the values for this Dictionary were obtained from the user.

The Compare method does all of the work. It compares any two DataRows to determine how each row should be sorted against any other row. The method processes each of the sortColumns.

First, it checks for a DBNull. The DBNull checking is different in the C# code and VB code. The C# code checks for a DBNull and manually sets the result of the compare. The VB code simply sets the value to Nothing if it is DBNull.

The code performs a compare based on the type of column. This is necessary because the user would expect decimals to sort as numbers, not as strings. You can add any other data types here as you require. Any data types not specifically handled will be handled as a string.

Note that a Select/Case statement was used in VB, but if/else if was used in C#. This is because C# requires its switch/case statements to switch based on constant values, not variables.

Regardless of the datatype, the result of the type-specific compare method is:

  • -1: If the value of x is less than the value of y.
  • 1: if the value of x is great than the value of y.
  • 0: if the value of x and y are equal.

If the resulting value is not 0, the loop can exit because the comparison is complete. If the resulting value is 0, meaning the columns are equal, the loop continues and the next column in the set of sort columns is checked.

You then use this class as follows.

In C#:

Dictionary<int, SortOrder> sortColumns =
                    new Dictionary<int, SortOrder>();
sortColumns.Add(2,SortOrder.Ascending);
sortColumns.Add(1, SortOrder.Descending);

RowComparer comp = new RowComparer();
comp.SortColumns = sortColumns;

var query3 = dt.AsEnumerable().OrderBy(q => q, comp);
DataView dv3 = query3.AsDataView();

In VB:

Dim sortColumns As New Dictionary(Of Integer, SortOrder)
sortColumns.Add(2, SortOrder.Ascending)
sortColumns.Add(1, SortOrder.Descending)

Dim comp As New RowComparer
comp.SortColumns = sortColumns

Dim query3 = dt.AsEnumerable.OrderBy(Function(q) q, comp)
Dim dv3 As DataView = query3.AsDataView

The first set of code sets up the Dictionary of sort columns. The sort columns are hard-coded in this example, but presumably they would come from the user.

The code then creates an instance of the new RowComparer class and passes in the set of columns.

NOTE: You could define a parameterized constructor and pass in the sorted columns instead of using a property, if desired.

The Lambda expression to perform the sort is then greatly simplified. It just passes in the desired comparer.

That’s it. You now have the ability to sort your DataTable using any user-defined set of columns.

Enjoy!

Writing Data from a DataTable to Excel

Filed under: C#,Data,Microsoft Excel,VB.NET @ 3:24 pm

As with most things in Visual Studio, there are many ways to export data from your .NET application to an Excel spreadsheet. This post covers one straightforward technique.

First, set a reference to the desired version of the Microsoft Excel Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Excel.

Import the Microsoft.Office.Interop.Excel library in your code file. To keep the namespaces clear, consider using an alias.

In C#:

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

NOTE: C# also requires a reference to System.Reflection to use the Missing.Value field. This is required because C# 3.0 does not support optional parameters. Every parameter must have a value, so Missing.Value is used to provide a value for all unspecified optional parameters.

In VB:

Imports Excel = Microsoft.Office.Interop.Excel

The rest of the code is presented in VB and C# and then discussed below. It is provided in one big chunk to make it easier to copy/paste into your application.

In C#:

Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = "Customers";

// Process the DataTable

// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE

DataTable dt = Customers.RetrieveAsDataTable();

int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
    rowCount += 1;
    for (int i = 1; i < dt.Columns.Count+1; i++)
    {
        // Add the header the first time through
        if (rowCount==2)
        {
            oSheet.Cells[1, i] = dt.Columns[i – 1].ColumnName;
        }
        oSheet.Cells[rowCount, i] = dr[i – 1].ToString();
    }
}

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
              oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();

// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Excel.XlSaveAsAccessMode.xlExclusive,
    Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

In VB:

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

‘ Start Excel and get Application object.
oXL = New Excel.Application

‘ Set some properties
oXL.Visible = True
oXL.DisplayAlerts = False

‘ Get a new workbook.
oWB = oXL.Workbooks.Add

‘ Get the active sheet
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = "Customers"

‘ Process the DataTable
BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
Dim dt As Data.DataTable = Customers.RetrieveAsDataTable

‘ Create the data rows
Dim rowCount As Integer = 1
For Each dr As DataRow In dt.Rows
    rowCount += 1
    For i As Integer = 1 To dt.Columns.Count
        ‘ Add the header the first time through
        If rowCount = 2 Then
            oSheet.Cells(1, i) = dt.Columns(i – 1).ColumnName
        End If
        oSheet.Cells(rowCount, i) = dr.Item(i – 1).ToString
    Next
Next

‘ Resize the columns
oRange = oSheet.Range(oSheet.Cells(1, 1), _
          oSheet.Cells(rowCount, dt.Columns.Count))
oRange.EntireColumn.AutoFit()

‘ Save the sheet and close
oSheet = Nothing
oRange = Nothing
oWB.SaveAs("test.xls")
oWB.Close()
oWB = Nothing
oXL.Quit()

‘ Clean up
‘ NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

NOTE: The VB code code above is set up to run with Option Strict ON.

The code begins by declaring the variables used in the code. Notice that in both C# and in VB this makes use of the namespace alias as shown at the very beginning of this post.

The code then starts Excel and makes it visible. If you want to create the spreadsheet without making Excel visible to the user, you could set the Visible to false instead.

A workbook is added, the active sheet of the workbook is referenced, and the sheet is given a name.

The DataTable is then processed. You can use any DataTable here. The code loops through the rows of the DataTable and for each row it loops through the columns. The first time through the columns it adds column headings.

To make the spreadsheet easier to read, the columns are then resized to show the contents of the cells. If you have lots of data in a cell, you may want to skip this step.

The workbook is then saved. Since no directory was specified, Excel will save the file to your My Documents folder. Notice the massive number of parameters in the C# code when calling the SaveAs (and Close) methods. This is required because C# does not have optional parameters. So the code must fill in each and every parameter in the call. VB does support optional parameters, so does not need to set the extra parameters.

NOTE: C# is getting optional parameters in .NET 4.0 (VS 2010).

Finally, the code is cleaned up. Since Excel is accessed through COM interop, the double garbage collection code is added to ensure Excel is correctly closed.  See this link for more information on why this specific garbage collection code is recommended.

Enjoy!

Next Page »

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs