# Tuesday, April 23, 2013
Tuesday, April 23, 2013 7:06:00 AM (GMT Daylight Time, UTC+01:00)
# Tuesday, April 16, 2013
Tuesday, April 16, 2013 6:53:00 AM (GMT Daylight Time, UTC+01:00)
# Tuesday, April 9, 2013
Tuesday, April 9, 2013 6:50:00 AM (GMT Daylight Time, UTC+01:00)
# Tuesday, April 2, 2013
Tuesday, April 2, 2013 6:44:00 AM (GMT Daylight Time, UTC+01:00)
# 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)