# Saturday, March 30, 2013

My youngest son recently completed his senior season of high school basketball, ending my 8 consecutive years as a high school basketball parent.

To commemorate the occasion, I created a highlight video of his team's season.

Saturday, March 30, 2013 2:25:07 PM (GMT Standard Time, UTC+00:00)
# Monday, March 25, 2013
Monday, March 25, 2013 2:28:00 PM (GMT Standard Time, UTC+00:00)
# Tuesday, March 19, 2013

When building a new application, you can start coding at the front, you can start coding at the back, or you can approach development randomly and just start wherever. I encourage you to start at the front-end of your application.

I came to this approach a few years ago while working on a new feature of an existing application. In the past, I had always started with the database (probably because my first language was the data-centric FoxPro language).

The new feature required a new database table and several new web pages. My partner and I split up the work:  He would write the forms and business logic; while I would create the new table, stored procedures, and data access layer. I immediately jumped to work, creating stored procedures and C# methods. I created procedures and methods to add a row, update a row, delete a row, return a single row by its ID, and return all rows in the table. After a few hours, my colleague was ready to integrate his code with mine.  “Where is the method to return all rows matched by last name?” he asked me. This method did not yet exist, so I wrote a function to search by last name and return matching rows. A few minutes later, he asked me for another method I had not yet written, so I wrote that one. After a few hours, I realized I had written almost none of the methods my partner needed and he used very few of the methods I had written in advance.

A light went on in my head: My approach was very inefficient.

The next feature we added, we took a different approach. We didn’t write any stored procedures or data layer methods until we had written code that called these methods. We started with the user interface, which told us which business objects and business logic we need; then wrote those business object classes and business layer code. The business layer in turn taught us what data layer methods and stored procedures we needed to write.

This is the approach I have taken ever since that project. I start with the part of the application closest to the front for which I know the requirements. I use each application layer to define the interface of the layer that it calls. This leads me to write only the methods that I need and ensures that each component has an interface that makes sense to those calling it.

This is the same philosophy used by proponents of Test-Drive Development (TDD), who advocate writing a failing test as the first test.

Starting with the front of my application and working my way back to the database has helped to keep my application interfaces, clean, lean, and logical. It took me a few years to learn this, but my code has improved since I did.

Tuesday, March 19, 2013 2:03:00 PM (GMT Standard Time, UTC+00:00)
# Monday, March 18, 2013
# Sunday, March 17, 2013

Thank you to all who attended my presentations at the Orlando Code Camp this past weekend. The audiences were great and I really enjoyed the conference.

I had numerous requests for the materials, which are available below.

Sunday, March 17, 2013 11:00:00 PM (GMT Standard Time, UTC+00:00)
# Friday, March 15, 2013

OVERVIEW

Microsoft Excel data can be treated like many other data sources from within a .NET application: we can connect to an Excel data source and we can query it using Structured Query Language (SQL). We don't even need to launch Excel or even have Excel installed in order to do so. All we need is the appropriate drivers.
This is good news if we need to read Excel data on a server, such as a web server. It’s possible to user Office Interop code to launch Excel and manipulate a spreadsheet, but Microsoft advises against doing so (http://support.microsoft.com/kb/257757).

SAMPLE

Here is sample code for querying Excel data and loading it into an ADO.NET DataTable

public static DataTable GetExcelData(string fullPath)
{
    var connectionString = string.Format
        (
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'",
        fullPath
        );
    var adapter = new OleDbDataAdapter
        (
        "SELECT * FROM [Sheet1$]", 
        connectionString
        );
    var ds = new DataSet();
    adapter.Fill(ds, "ExcelData");
    DataTable excelTable = ds.Tables["ExcelData"];
    return excelTable;
}

The Connection String contains the driver and the full path to the Excel file. The “HDR” in the connection string indicates that the first row of the spreadsheet contains column headers. These headers will become the column names in our ADO.NET data table.
We can call this code from a web page to read an Excel file uploaded with the ASP.NET FileUpload control. The code is shown below.

protected void ReadExcelButton_Click(object sender, EventArgs e) 
{ 
    if (ExcelUploaderValidator.IsValid) 
    { 
        string fileName = ""; 
        string filePath = Server.MapPath("Upload"); 
        var fullPath = ""; 
        //var fullPath = @"C:\Test\ReadExcel\ReadExcel\bin\Debug" + @"\" + fileName; 
        if (ExcelUploader.HasFile) 
        { 
            var rand = new Random(); 
            fileName = String.Format("{0:000000}-{1}", rand.Next(0, 999999), ExcelUploader.FileName); 
            fullPath = filePath + @"\" + fileName; 
            ExcelUploader.SaveAs(fullPath); 
            DataTable excelTable = ExcelUtils.GetExcelData(fullPath); 
            ExcelGrid.DataSource = excelTable; 
            ExcelGrid.DataBind(); 
        } 
    } 
} 

In this example, we save the uploaded file to a folder on the server; then, call our function, pointing to that file, so we can pass it in as part of the connection string.
For completeness, here is the web page markup in this sample:

<div> 
    <br /> 
    <asp:FileUpload ID="ExcelUploader" runat="server"  /> 
    <asp:RegularExpressionValidator 
        runat="server" 
        ID="ExcelUploaderValidator" 
        ControlToValidate="ExcelUploader" 
        ErrorMessage="Only Excel files or CSV files are allowed" 
        ValidationExpression="^.*\.(xls|XLS|xlsx|XLSX|csv|CSV)$"> 
    </asp:RegularExpressionValidator> 
    <br /> 
</div> 
<asp:Button ID="ReadExcelButton" runat="server" onclick="ReadExcelButton_Click" 
    Text="Read Excel Document" /> 
<asp:GridView ID="ExcelGrid" runat="server"> 
</asp:GridView> 

The query assumes that the workbook we are reading contains a worksheet named “Sheet1” and it reads all the data in that worksheet, copying it into a DataTable.

LIMITATIONS

The Excel data driver only works in 32-bit .NET projects, so you will need to configure the project properties to run in 32-bit mode.

CONCLUSION

In this article, we demonstrated a simple way to read data from an Excel document and copy it into a DataTable for further manipulation and processing.

Friday, March 15, 2013 2:29:32 PM (GMT Standard Time, UTC+00:00)
# Wednesday, March 13, 2013
Wednesday, March 13, 2013 5:14:15 PM (GMT Standard Time, UTC+00:00)
# Thursday, March 7, 2013

Last week, I attended my third Microsoft Global MVP Summit. The Summit is a conference held in the Seattle area open to Microsoft MVPs.

I signed a Non-Disclosure Agreement that prevents me from talking about most of the conference content; but I can tell you about my experience and my impressions.

By far, the best part of the MVP Summit is the chance to meet so many smart people. Many of them I know by reputation before I meet them. Every year I joke that I plan to be the dumbest guy in every room at this conference. And every year, the joke is very close to reality. There are some amazing people here - those with deep knowledge of a technology; those who have built amazing products or open-source projects; those who have written books and blogs that I've read; those who produce podcasts that I listen to regularly; and those who have a story to tell about how they use technology to solve real problems. I love meeting and talking with all these people.

The sessions are good, but, other than REDACTEDREDACTEDREDACTED, I didn't hear about a whole lot of new stuff.

There are some regular events in the evening and I took advantage of those. A party at Ted Neward's house attracted a who's who of technologists and the annual Party With Palermo (hosted by Jeff Palermo of Austin, TX) always attracts a great crowd. I attended a reception for first-time MVPs, even those this was my third summit, because:

  1. It was hosted by INETA and I am on the Board of Directors
  2. It was organized by my friend Joe Guadagno, who did an amazing job
  3. INETA presented a Lifetime Achievement award to Russ Fustino at the event and I wanted to be present to congratulate Russ.

The day after the Global MVP Summit was the ASP.NET Insiders Summit organized by Scott Hanselman. I was excited to attend this conference because I was invited to join the Insiders only a few weeks ago. (I think I was the newest member at the time of the Summit). These sessions were really informative. We got a look at new and proposed language, framework, and IDE features. We also had a chance to provide feedback to the product team; and to see several open-source web frameworks. Another NDA prevents me from revealing too many details of what I saw there, but I really learned a lot from this extra day. I plan to attend the ASP.NET Summit again next year.

Two days before the MVP Summit, a group of attendees and a few other volunteers traveled to the Northwest Harvest Food Bank in Seattle to help pack fruit for needy families in the area. This was a great opportunity to meet people, have fun, and do some good.

The week was exhausting but well worth the trip. If Microsoft will have me, expect to see me at the 2014 Global MVP Summit. And I still expect to be the dumbest guy in each room.

Thursday, March 7, 2013 1:08:02 AM (GMT Standard Time, UTC+00:00)
# Monday, March 4, 2013
Monday, March 4, 2013 5:53:08 AM (GMT Standard Time, UTC+00:00)