History

One of the challenges that developers face is bringing data from storage engines such as SQL server into object-oriented programming models.  Most developers address this challenge by writing complex data access code to move data between their applications and the database.  However, writing such code requires an understanding of the database, so that you can access data either from the raw tables, from views or from stored procedures.

Entity/relationship modeling is not a new technology, it was introduced 30 years ago in the 70’s.  Using entity/relationship models, programmers create a conceptual model of the data and write their data access code against that model, while an additional layer provides a bridge between the conceptual model and the actual data store.  The Entity Framework is designed based on this technology by implementing the entity relationship model in the form of the Entity Data Model.

Introduction

The Entity Framework provides a technology to map conceptual entities (standard .NET classes) to the data store schema of your database (tables and views).  This allows an application developer to work with the conceptual entities without having to be concerned about the underlying data model.  The Entity Framework provides a platform that raises the level of abstraction from the logical relational level to the conceptual level which enables developers to work with data at a greater level of abstraction.  The Entity Framework uses an Entity Data Model (EDM).  The Entity Data Model consists of the storage schema, the conceptual schema, the mapping schema and the entity classes.

Storage Schema

The storage schema describes, in an XML definition, the schema of the data store.  This is the exact schema of your data store.  The storage schema definition can be generated directly from the data store.  The Storage schema describes the schema of a database table as an entity type.  The storage schema also describes relationships between tables as an association.

Conceptual Schema

The conceptual schema describes the conceptual entities as entity types and is defined in XML.  This is created to describe the schema of the conceptual entities.  Conceptual entity types are simply .NET classes.  The conceptual schema also describes relationships between entity types as associations.  These relationships are references in the conceptual schema when an entity contains instances of another entity.

Mapping Schema

The mapping schema definition is the glue that binds the conceptual model and the data store model.  This XML definition contains information on how the conceptual entities, functions and associations are mapped to the storage schema.  The associations we define in the conceptual schema is mapped to the relationships that we define in the storage schema.

Summary

Conceptual entities designed in the development life cycle can later be mapped to data stores. This allows an application developer to work with an object oriented model, and a database developer to work with a relational model, and at the end those two models are combined using the Mapping Schema.  For more information visit  Entity Framework Quickstart.

As you may (or may not) know, a while back Microsoft purchased some charting functionality from Dundas, to be included as part of Reporting Services for SQL Server 2008.  Well, now they have packaged up this functionality as a control for Visual Studio 2008!  This is a fantastic add-on, as we now have the power, flexibility, and visual excellence of the old Dundas chart control as a free Visual Studio 2008 add-on!

Scott Guthrie has an excellent blog post discussing it, which can be found here.

Scott specifcially talks about ASP.NET, and a few of the commenters on his blog ask the obvious question: Can this be used for Winforms apps as well?  I went ahead and installed the add-on, and the new Chart control can indeed be dropped into Winforms apps as well as ASP.NET!

Lastly, I got curious as to whether or not this control could be used from Visual Studio 2005.  It doesn’t show up in the toolbox automatically, but it can be added and does indeed work in VS2005!

Safe and Easy Data Reader

Several years ago, I was responsible for maintaining an application built on top of Rockford Lhotka’s CSLA.NET framework. It is a nice framework, not too hard to use, and provides a large number of useful features.

When starting a new project today, I don’t usually choose to use CSLA. I developed my own pseudo-framework that more closely represents the architecture and features I commonly need and use.

One of the good idea’s I’ve taken away from my CSLA experience, is a safe data reader.

What is a safe data reader? It’s simply a custom implementation of the IDataReader interface.

The 3 changes from a default IDataReader implemenation are…
1 - Customized IDataReader methods to gracefully handle NULL values or type-conversion errors safely.
2 - Additional methods to use for working with Nullable values (GetNullableDateTime, GetNullableDecimal, GetNullableInt32, etc).
3 - Overloads to the GetXXX methods to accept the string field name as a parameter. This is easier and safer than the GetXXX methods that accept the field’s ordinal position in the data reader.

Public Class SafeDataReader
    Implements IDataReader

    Private _dr As IDataReader

    Public Sub New(ByVal dr As IDataReader)
        _dr = dr
    End Sub

    Public Sub Close() Implements System.Data.IDataReader.Close
        _dr.Close()
    End Sub

    Public ReadOnly Property Depth() As Integer Implements System.Data.IDataReader.Depth
        Get
            Return _dr.Depth
        End Get
    End Property

    Public Function GetSchemaTable() As System.Data.DataTable Implements System.Data.IDataReader.GetSchemaTable
        Return _dr.GetSchemaTable
    End Function

    Public ReadOnly Property IsClosed() As Boolean Implements System.Data.IDataReader.IsClosed
        Get
            Return _dr.IsClosed
        End Get
    End Property

    Public Function NextResult() As Boolean Implements System.Data.IDataReader.NextResult
        Return _dr.NextResult
    End Function

    Public Function Read() As Boolean Implements System.Data.IDataReader.Read
        Return _dr.Read
    End Function

    Public ReadOnly Property RecordsAffected() As Integer Implements System.Data.IDataReader.RecordsAffected
        Get
            Return _dr.RecordsAffected
        End Get
    End Property

    Public ReadOnly Property FieldCount() As Integer Implements System.Data.IDataRecord.FieldCount
        Get
            Return _dr.FieldCount
        End Get
    End Property

    Public Function GetBoolean(ByVal i As Integer) As Boolean Implements System.Data.IDataRecord.GetBoolean
        If _dr.IsDBNull(i) Then
            Return False
        End If
        Return _dr.GetBoolean(i)
    End Function

    Public Function GetByte(ByVal i As Integer) As Byte Implements System.Data.IDataRecord.GetByte
        If _dr.IsDBNull(i) Then
            Return 0 'Could be Byte.MinValue
        End If
        Return _dr.GetByte(i)
    End Function

    Public Function GetBytes(ByVal i As Integer, ByVal fieldOffset As Long, ByVal buffer() As Byte, ByVal bufferoffset As Integer, ByVal length As Integer) As Long Implements System.Data.IDataRecord.GetBytes
        If _dr.IsDBNull(i) Then
            Return 0 'Could be Long.MinValue
        End If
        Return _dr.GetBytes(i, fieldOffset, buffer, bufferoffset, length)
    End Function

    Public Function GetChar(ByVal i As Integer) As Char Implements System.Data.IDataRecord.GetChar
        If _dr.IsDBNull(i) Then
            Return Char.MinValue
        End If
        Return _dr.GetChar(i)
    End Function

    Public Function GetChars(ByVal i As Integer, ByVal fieldoffset As Long, ByVal buffer() As Char, ByVal bufferoffset As Integer, ByVal length As Integer) As Long Implements System.Data.IDataRecord.GetChars
        If _dr.IsDBNull(i) Then
            Return 0
        End If
        Return _dr.GetChars(i, fieldoffset, buffer, bufferoffset, length)
    End Function

    Public Function GetData(ByVal i As Integer) As System.Data.IDataReader Implements System.Data.IDataRecord.GetData
        Return _dr.GetData(i)
    End Function

    Public Function GetDataTypeName(ByVal i As Integer) As String Implements System.Data.IDataRecord.GetDataTypeName
        Return _dr.GetDataTypeName(i)
    End Function

    Public Function GetDateTime(ByVal i As Integer) As Date Implements System.Data.IDataRecord.GetDateTime
        If _dr.IsDBNull(i) Then
            Return DateTime.MinValue
        End If
        Return _dr.GetDateTime(i)
    End Function

    Public Function GetDecimal(ByVal i As Integer) As Decimal Implements System.Data.IDataRecord.GetDecimal
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Decimal.MinValue
        End If
        Return _dr.GetDecimal(i)
    End Function

    Public Function GetDouble(ByVal i As Integer) As Double Implements System.Data.IDataRecord.GetDouble
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Double.MinValue
        End If
        Return _dr.GetDouble(i)
    End Function

    Public Function GetFieldType(ByVal i As Integer) As System.Type Implements System.Data.IDataRecord.GetFieldType
        Return _dr.GetFieldType(i)
    End Function

    Public Function GetFloat(ByVal i As Integer) As Single Implements System.Data.IDataRecord.GetFloat
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Single.MinValue
        End If
        Return _dr.GetFloat(i)
    End Function

    Public Function GetGuid(ByVal i As Integer) As System.Guid Implements System.Data.IDataRecord.GetGuid
        If _dr.IsDBNull(i) Then
            Return Guid.Empty
        End If
        Return _dr.GetGuid(i)
    End Function

    Public Function GetInt16(ByVal i As Integer) As Short Implements System.Data.IDataRecord.GetInt16
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Short.MinValue
        End If
        Return _dr.GetInt16(i)
    End Function

    Public Function GetInt32(ByVal i As Integer) As Integer Implements System.Data.IDataRecord.GetInt32
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Int32.MinValue
        End If
        Return _dr.GetInt32(i)
    End Function

    Public Function GetInt64(ByVal i As Integer) As Long Implements System.Data.IDataRecord.GetInt64
        If _dr.IsDBNull(i) Then
            Return 0 'Could use Long.MinValue
        End If
        Return _dr.GetInt64(i)
    End Function

    Public Function GetName(ByVal i As Integer) As String Implements System.Data.IDataRecord.GetName
        Return _dr.GetName(i)
    End Function

    Public Function GetOrdinal(ByVal name As String) As Integer Implements System.Data.IDataRecord.GetOrdinal
        Return _dr.GetOrdinal(name)
    End Function

    Public Function GetString(ByVal i As Integer) As String Implements System.Data.IDataRecord.GetString
        If _dr.IsDBNull(i) Then
            Return String.Empty
        End If
        Return _dr.GetString(i)
    End Function

    Public Function GetValue(ByVal i As Integer) As Object Implements System.Data.IDataRecord.GetValue
        Return _dr.GetValue(i)
    End Function

    Public Function GetValues(ByVal values() As Object) As Integer Implements System.Data.IDataRecord.GetValues
        Return _dr.GetValues(values)
    End Function

    Public Function IsDBNull(ByVal i As Integer) As Boolean Implements System.Data.IDataRecord.IsDBNull
        Return _dr.IsDBNull(i)
    End Function

    Default Public Overloads ReadOnly Property Item(ByVal i As Integer) As Object Implements System.Data.IDataRecord.Item
        Get
            If _dr.IsDBNull(i) Then
                Return Nothing
            End If
            Return _dr.Item(i)
        End Get
    End Property

    Default Public Overloads ReadOnly Property Item(ByVal name As String) As Object Implements System.Data.IDataRecord.Item
        Get
            If _dr.IsDBNull(_dr.GetOrdinal(name)) Then
                Return Nothing
            End If
            Return _dr.Item(name)
        End Get
    End Property

#Region " Custom methods"
    Public Function GetNullableDateTime(ByVal name As String) As Nullable(Of DateTime)
        If _dr(name) Is DBNull.Value Then
            Return Nothing
        End If
        Return Convert.ToDateTime(_dr(name))
    End Function

    Public Function GetNullableDecimal(ByVal name As String) As Nullable(Of Decimal)
        If _dr(name) Is DBNull.Value Then
            Return Nothing
        End If
        Return Convert.ToDecimal(_dr(name))
    End Function

    Public Function GetNullableInt32(ByVal name As String) As Nullable(Of Integer)
        If _dr(name) Is DBNull.Value Then
            Return Nothing
        End If
        Return Convert.ToInt32(_dr(name))
    End Function

    Public Function GetNullableBoolean(ByVal name As String) As Nullable(Of Boolean)
        If _dr(name) Is DBNull.Value Then
            Return Nothing
        End If
        Return Convert.ToBoolean(_dr(name))
    End Function

    Public Function GetDateTime(ByVal name As String) As DateTime
        Return GetDateTime(_dr.GetOrdinal(name))
    End Function

    Public Function GetDecimal(ByVal name As String) As Decimal
        Return GetDecimal(_dr.GetOrdinal(name))
    End Function

    Public Function GetInt16(ByVal name As String) As Int16
        Return GetInt16(_dr.GetOrdinal(name))
    End Function

    Public Function GetInt32(ByVal name As String) As Int32
        Return GetInt32(_dr.GetOrdinal(name))
    End Function

    Public Function GetInt64(ByVal name As String) As Int64
        Return GetInt64(_dr.GetOrdinal(name))
    End Function

    Public Function GetBoolean(ByVal name As String) As Boolean
        Return GetBoolean(_dr.GetOrdinal(name))
    End Function

    Public Function GetString(ByVal name As String) As String
        Return GetString(_dr.GetOrdinal(name))
    End Function

#End Region

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: free other state (managed objects).
            End If
            ' TODO: free your own state (unmanaged objects).
            ' TODO: set large fields to null.
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

When working with reports that deal with any item over a period of time, ie Sales By Month, developers get stuck with writing stored procedures that will return a pivot table result. This sql coding is not only complex, but also a pain to debug (if you are not the one who orginally wrote it). The following code will allow you to select your normal set of data and when you pull it back into your application as a datatable, you could pass your table to the following routine, and it will return a summarized datatable that has been pivot’d.

 Public Shared Function InvertTable(ByVal Table As DataTable, ByVal XAxisColumn As String, ByVal YAxisColumn As String, ByVal ValueColumn As String, ByVal EmptyCellValue As String) As DataTable
        'Create a DataTable to Return
        Dim returnTable As New DataTable()

        If XAxisColumn = "" Then
            XAxisColumn = Table.Columns(0).ColumnName
        End If

        'Add a Column at the beginning of the table
        returnTable.Columns.Add(YAxisColumn)

        'Read all DISTINCT values from columnX Column in the provided DataTale
        Dim columnXValues As New List(Of String)()

        For Each dr As DataRow In Table.Rows

            Dim columnXTemp As String = dr(XAxisColumn).ToString()
            If Not columnXValues.Contains(columnXTemp) Then
                'Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                columnXValues.Add(columnXTemp)
                returnTable.Columns.Add(columnXTemp)
            End If
        Next

        'Verify if Y and Z Axis columns re provided
        If YAxisColumn  "" AndAlso ValueColumn  "" Then
            'Read DISTINCT Values for Y Axis Column
            Dim columnYValues As New List(Of String)()

            For Each dr As DataRow In Table.Rows
                If Not columnYValues.Contains(dr(YAxisColumn).ToString()) Then
                    columnYValues.Add(dr(YAxisColumn).ToString())
                End If
            Next

            'Loop all Column Y Distinct Value
            For Each columnYValue As String In columnYValues
                'Creates a new Row
                Dim drReturn As DataRow = returnTable.NewRow()
                drReturn(0) = columnYValue
                'foreach column Y value, The rows are selected distincted
                Dim rows As DataRow() = Table.[Select](YAxisColumn & “=’” & columnYValue & “‘”)

                ‘Read each row to fill the DataTable
                For Each dr As DataRow In rows
                    Dim rowColumnTitle As String = dr(XAxisColumn).ToString()

                    ‘Read each column to fill the DataTable
                    For Each dc As DataColumn In returnTable.Columns
                        If dc.ColumnName = rowColumnTitle Then

                            Try
                                drReturn(rowColumnTitle) = Convert.ToDecimal(drReturn(rowColumnTitle)) + Convert.ToDecimal(dr(ValueColumn))
                            Catch
                                drReturn(rowColumnTitle) = dr(ValueColumn)
                            End Try

                        End If
                    Next
                Next

                returnTable.Rows.Add(drReturn)
            Next

        Else
            Throw New Exception(”The columns to perform inversion are not provided”)
        End If

        ‘if a nullValue is provided, fill the datable with it
        If EmptyCellValue  “” Then
            For Each dr As DataRow In returnTable.Rows
                For Each dc As DataColumn In returnTable.Columns
                    If dr(dc.ColumnName).ToString() = “” Then
                        dr(dc.ColumnName) = EmptyCellValue
                    End If
                Next
            Next
        End If

        Return returnTable
    End Function

Granted the code in itself doesn’t look very friendly either, but copy paste and it will make creating pivot tables extremely simple.

Variables:
Table - Datatable you are wanting to invert
XAxisColumn - This is the column in Table that you want to summerize by. For example, if you are doing a parts sale by month, this would be your PartNumber column
YAxisColumn - This is the column that you are spanning. For example, if you are doing a parts sales by month, this would be your Months
ValueColumn - This is the column to be summarized. For example, if you are doing a parts sales by month this would be your quantity.
EmptyCellValue - If there is a null value it will replace the null value with this value

Many times I find myself doing Windows Service applications for clients, but don’t want to have to manually setup the app.config for the Windows Service, and depending on the clients level of computers, explaining how to modify the app.config might not be an option. Since Windows Services do not have forms to do configuration you’ll have to create a seperate application just for configuration. With the other application it will read/write to its app.config by default. This is not what you are wanting either, since the service won’t see your configurations app.config file.

Read Another Application App.Config (appSettings Section)

Public Shared Function Read(ByVal ApplicationConfigPath As String) As Dictionary(Of String, String)
        Dim fileMap As New ExeConfigurationFileMap()

        fileMap.ExeConfigFilename = ApplicationConfigPath

        Dim Config As System.Configuration.Configuration = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
        Dim appSettings As AppSettingsSection = Config.AppSettings
        Dim Settings As KeyValueConfigurationCollection = appSettings.Settings
        Dim AllSettings As New Dictionary(Of String, String)

        For Each Key As String In Settings.AllKeys
            AllSettings.Add(Key, Settings.Item(Key).Value)
        Next

        Return AllSettings
    End Function

Write New Key/Value Pair To Another App.Config (appSettings Section)

Dim fileMap As New ExeConfigurationFileMap()

        fileMap.ExeConfigFilename = ApplicationConfigPath

        Dim Config As System.Configuration.Configuration = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
        Dim appSettings As AppSettingsSection = Config.AppSettings
        Dim Settings As KeyValueConfigurationCollection = appSettings.Settings

        Settings.Add(Key, Value)
        Config.Save()

Update Key/Value Pair In Another App.Config (appSettings Section)

Dim fileMap As New ExeConfigurationFileMap()

        fileMap.ExeConfigFilename = ApplicationConfigPath

        Dim Config As System.Configuration.Configuration = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
        Dim appSettings As AppSettingsSection = Config.AppSettings
        Dim Settings As KeyValueConfigurationElement = appSettings.Settings(Key)

        Settings.Value = value
        Config.Save()

        ConfigurationManager.RefreshSection("appSettings")