Nearly every developer and architect would agree on the following, though relatively loose, definition of the business logic layer (BLL) of a software application: The BLL is the part of the software application that deals with the performance of business-related tasks. Code in the BLL operates on data that attempts to model entities in the problem domain—invoices, customers, orders, and the like. Operations in the BLL attempt to model business processes.

Under the hood of this largely accepted definition lie a number of key details that are left undefined and unspecified. Design patterns exist to help architects and code designers transform loose definitions into blueprints. In general, BLL design patterns have a slightly different focus. They model operations and data and often serve as the starting point for designing the BLL.

In this article, after a brief refresher on procedural and object-based patterns for organizing the BLL, I'll focus on one side of the problem—data transfer objects—that if not effectively addressed at the architecture level, may have a deep impact on the development of the project.

Procedural Patterns for BLL

When it comes to designing the BLL, you can start from the use-cases that have emerged during the analysis phase. Typically, you end up coding one method for each required interaction between the user and the system. Each interaction forms a logical transaction that includes all due steps—from collecting input to performing the task, and from database access to refreshing the user interface. This approach is referred to as the Transaction Script (TS) pattern.

In TS, you focus on the required actions and don't really build a conceptual model of the domain as the gravitational center of the application.

To move data around, you can use any container objects that may suit you. In the Microsoft .NET space, this mostly means using ADO.NET data containers such as DataSets and DataTables. These objects are a type of super-array object, with some search, indexing, and filtering capabilities. In addition, DataSets and DataTables can be easily serialized across tiers and even persisted locally to enable offline scenarios.

The TS pattern doesn't mandate a particular model for data representation (and doesn't prevent any either). Typically, operations are grouped as static methods in one or more entry-point classes. Alternatively, operations can be implemented as commands in a Command pattern approach. Organization of data access is left to the developer and typically results in chunks of ADO.NET code.

The TS pattern is fairly simple to set up; at the same time, it obviously doesn't scale that well, as the complexity of the application grows. In the .NET space, another pattern has gained wide acceptance over the years: the Table Module pattern. In a nutshell, the Table Module pattern suggests a database-centric vision of the BLL. It requires you to create a business component for each database table. Known as the table module class, the business component packages the data and behavior together.

In the Table Module pattern, the BLL is broken into a set of coarse-grained components, each representing an entire database table. Being strictly table-oriented, the Table Module pattern lends itself to using recordset-like data structures for passing data around. ADO.NET data containers or, better yet, customized and typed version of ADO.NET containers are the natural choice.

As the need for a more conceptual view of the problem domain arises, the BLL patterns that have worked for years in the .NET space need to evolve some more. Architects tend to build an entity/relationship model that represents the problem domain and then look at technologies like LINQ-to-SQL and Entity Framework as concrete tools to help.

Object-Based Patterns for BLL

The Table Module pattern is based on objects, but it's not really an object-based pattern for modeling the business logic. It does have objects, but they are objects representing tables, not objects representing the domain of the problem.

In an object-oriented design, the business logic identifies entities and expresses all of the allowed and required interactions between entities. In the end, the application is viewed as a set of interrelated and interoperating objects. The set of objects mapping to entities, plus some special objects performing calculations form the domain model. (In the Entity Framework, you express the domain model using the Entity Data Model [EDM].)

There are various levels of complexity in a domain model that suggest different patterns—typically the Active Record pattern or the Domain Model pattern. A good measure of this complexity is the gap between the entity model you have in mind and the relational data model you intend to create to store data. A simple domain model is one in which your entities map closely to tables in the data model. A not-so-simple model requires mapping to load and save domain objects to a relational database.

The Active Record pattern is an ideal choice when you need a simple domain model; otherwise, when it is preferable to devise entities and relationships regardless of any database notion, the Domain Model pattern is the way to go.

The Active Record pattern is similar to what you get from a LINQ-to-SQL object model (and the defaultgenerated model with the Entity Designer in the Entity Framework Version 1.0). Starting from an existing database, you create objects that map a row in a database table. The object will have one property for each table column of the same type and with the same constraints. The original formulation of the Active Record pattern recommends that each object makes itself responsible for its own persistence. This means that each entity class should include methods such as Save and Load. Neither LINQ-to-SQL nor Entity Framework does this though, as both delegate persistence to an integrated O/RM infrastructure that acts as the real data access layer, as shown in Figure 1.

Figure 1 A Layered Architecture – the Domain Model Pattern Used for the BLL

The Service Layer

In Figure 1, you see a logical section of the BLL named as the "service layer" sitting in between the presentation layer and the layer that takes care of persistence. In a nutshell, the service layer defines an interface for the presentation layer to trigger predefined system actions. The service layer decouples presentation and business logic and represents the façade for the presentation logic to call into the BLL. The service layer does its own job, regardless of how the business logic is organized internally.

As a .NET developer, you are quite familiar with event handlers in Web or Windows forms. The canonical Button1_Click method belongs to the presentation layer and expresses the system's behavior after the user has clicked a given button. The system's behavior—more exactly, the use case you're implementing—may require some interaction with BLL components. Typically, you need to instantiate the BLL component and then script it. The code necessary to script the component may be as simple as calling the constructor and perhaps one method. More often, though, such code is fairly rich with branches, and may need to call into multiple objects or wait for a response. Most developers refer to this code as application logic. Therefore, the service layer is the place in the BLL where you store application logic, while keeping it distinct and separate from domain logic. The domain logic is any logic you fold into the classes that represent domain entities.

In Figure 1, the service layer and domain model blocks are distinct pieces of the BLL, although they likely belong to different assemblies. The service layer knows the domain model and references the corresponding assembly. The service layer assembly, instead, is referenced from the presentation layer and represents the only point of contact between any presentation layer (be it Windows, Web, Silverlight, or mobile) and the BLL. Figure 2 shows the graph of references that connect the various actors. The service layer is a sort of mediator between the presentation layer and the rest of the BLL. As such, it keeps them neatly separated but loosely coupled so that they are perfectly able to communicate. In Figure 2, the presentation layer doesn't hold any reference to the domain model assembly. This is a key design choice for most layered solutions.

Figure 2 Graph of References Between Participant Actors

Introducing Data Transfer Objects

When you have a domain-based vision of the application, you can't help but look seriously into data transfer objects. No multitier solution based on LINQ to SQL or Entity Framework is immune from this design issue. The question is, how would you move data to and from the presentation layer? Put another way, should the presentation layer hold a reference to the domain model assembly? (In an Entity Framework scenario, the domain model assembly is just the DLL created out of the EDMX file.)

Ideally, the design should look like Figure 3, where made-to-measure objects are used to pass data from the presentation layer to the service layer, and back. These ad hoc container objects take the name of Data Transfer Objects (DTOs).

Figure 3 Communication Between Presentation Layer and Service Layer

A DTO is nothing more than a container class that exposes properties but no methods. A DTO is helpful whenever you need to group values in ad hoc structures for passing data around.

From a pure design perspective, DTOs are a solution really close to perfection. DTOs help to further decouple presentation from the service layer and the domain model. When DTOs are used, the presentation layer and the service layer share data contracts rather than classes. A data contract is essentially a neutral representation of the data that interacting components exchange. The data contract describes the data a component receives, but it is not a system-specific class, like an entity. At the end of the day, a data contract is a class, but it is more like a helper class specifically created for a particular service method.

A layer of DTOs isolates the domain model from the presentation, resulting in both loose coupling and optimized data transfer.

Other Benefits of DTOs

The adoption of data contracts adds a good deal of flexibility to the service layer and subsequently to the design of the entire application. For example, if DTOs are used, a change in the requirements that forces a move to a different amount of data doesn't have any impact on the service layer or even the domain. You modify the DTO class involved by adding a new property, but leave the overall interface of the service layer intact.

It should be noted that a change in the presentation likely means a change in one of the use cases and therefore in the application logic. Because the service layer renders the application logic, in this context a change in the service layer interface is still acceptable. However, in my experience, repeated edits to the service layer interface may lead to the wrong conclusion that changes in the domain objects—the entities—may save you further edits in the service layer. This doesn't happen in well-disciplined teams or when developers have a deep understanding of the separation of roles that exists between the domain model, the service layer, and DTOs.

As Figure 4 shows, when DTOs are employed, you also need a DTO adapter layer to adapt one or more entity objects to a different interface as required by the use case. In doing so, you actually implement the "Adapter" pattern—one of the classic and most popular design patterns. The Adapter pattern essentially converts the interface of one class into another interface that a client expects.

Figure 4 DTO Adapters in the BLL

With reference to Figure 4, the adapter layer is responsible for reading an incoming instance of the OperationRequestDTO class and for creating and populating fresh instances of OperationResponseDTO.

When requirements change and force changes in a DTO-based service layer, all you need to do is update the public data contract of the DTO and adjust the corresponding DTO adapter.

The decoupling benefits of DTOs don't end here. In addition, to happily surviving changes in the presentation, you can enter changes to the entities in the domain model without impacting any clients you may have.

Any realistic domain model contains relationships, such as Customer-to-Orders and Order-to-Customer, that form a double link between Customer and Order entities. With DTOs, you also work around the problem of managing circular references during the serialization of entity objects. DTOs can be created to carry a flat stream of values that, if needed, serialize just fine across any boundaries. (I'll return to this point in a moment.)

Drawbacks of DTOs

From a pure design perspective, DTOs are a real benefit, but is this theoretical point confirmed by practice, too? As in many architecture open points, the answer is, it depends.

Having hundreds of entities in the domain model is definitely a good reason for considering alternatives to a pure DTO-based approach. In large projects with so many entities, DTOs add a remarkable level of (extra) complexity and work to do. In short, a pure, 100% DTO solution is often just a 100 percent painful solution.

While normally the complexity added to a solution by DTOs is measured with the cardinality of the domain model, the real number of needed DTOs can be more reliably determined looking at the use cases and the implementation of the service layer. A good formula for estimating how many DTOs you need is to look at the number of methods in the service layer. The real number can be smaller if you are able to reuse some DTOs across multiple service layer calls, or higher if your DTOs group some data using complex types.

In summary, the only argument against using DTOs is the additional work required to write and manage the number of resulting DTO classes. It is not, however, a simple matter of a programmer's laziness. In large projects, decoupling presentation from the service layer costs you hundreds of new classes.

It should also be noted that a DTO is not simply a lightweight copy of every entity you may have. Suppose that two distinct use cases require you to return a collection of orders—say, GetOrdersByCountry and GetOrdersByCustomer. Quite likely, the information to put in the "order" is different. You probably need more (or less) details in GetOrdersByCustomer than in GetOrdersByCountry. This means that distinct DTOs are necessary. For this reason, hundreds of entities are certainly a quick measure of complexity, but the real number of DTOs can be determined only by looking at use cases.

If DTOs are not always optimal, what would be a viable alternate approach?

The only alternative to using DTOs is to reference the domain model assembly from within the presentation layer. In this way though, you establish a tight coupling between layers. And tightly coupled layers may be an even worse problem.

Referencing Entities Directly

A first, not-so-obvious condition to enable the link of entities directly from the presentation layer is that it is acceptable for the presentation layer to receive data in the format of entity objects. Sometimes the presentation needs data formatted in a particular manner. A DTO adapter layer exists to just massage data as required by the client. If you don't use DTOs though, the burden of formatting data properly must be moved onto the presentation layer. In fact, the wrong place in which to format data for user interface purposes is the domain model itself.

Realistically, you can do without DTOs only if the presentation layer and the service layer are co-located in the same process. In this case, you can easily reference the entity assembly from within both layers without dealing with thorny issues such as remoting and data serialization. This consideration leads to another good question: Where should you fit the service layer?

If the client is a Web page, the service layer is preferably local to the Web server that hosts the page. In ASP.NET applications, the presentation layer is all in code-behind classes and lives side by side with the service layer in the same AppDomain. In such a scenario, every communication between the presentation layer and the service layer occurs in-process and objects can be shared with no further worries. ASP.NET applications are a good scenario where you can try a solution that doesn't use the additional layer of DTOs.

Technology-wise, you can implement the service layer via plain .NET objects or via local Windows Communication Foundation (WCF) services. If the application is successful, you can easily increase scalability by relocating the service layer to a separate application server.

If the client is a desktop application, then the service layer is typically deployed to a different tier and accessed remotely from the client. As long as both the client and remote server share the same .NET platform, you can use remoting techniques (or, better, WCF services) to implement communication and still use native entity objects on both ends. The WCF infrastructure will take care of marshaling data across tiers and pump it into copies of native entities. Also, in this case you can arrange an architecture that doesn't use DTOs. Things change significantly if the client and server platforms are incompatible. In this case, you have no chances to link the native objects and invoke them from the client; subsequently, you are in a pure service-oriented scenario and using DTOs is the only possibility.

The Middle Way

DTOs are the subject of an important design choice that affects the implementation of any communication between the presentation and the back end of the system.

If you employ DTOs, you keep the system loosely coupled and open toward a variety of clients. DTOs are the ideal choice, if you can afford it. DTOs add a significant programming overhead to any real-world system. This doesn't mean that DTOs should not be used, but they lead to a proliferation of classes that can really prefigure a maintenance nightmare in projects with a few hundred entity objects and even more use cases.

If you are at the same time a provider and consumer of the service layer, and if you have full control over the presentation, there might be benefits in referencing the entity model assembly from the presentation. In this way, all methods in the service layer are allowed to use entity classes as the data contracts of their signatures. The impact on design and coding is clearly quite softer.

Whether to use DTOs or not is not a point easy to generalize. To be effective, the final decision should always be made looking at the particulars of the project. In the end, a mixed approach is probably what you'll be doing most of the time. Personally, I tend to use entities as much as I can. This happens not because I'm against purity and clean design, but for a simpler matter of pragmatism. With an entity model that accounts for only 10 entities and a few use cases, using DTOs all the way through doesn't pose any significant problem. And you get neat design and low coupling. However, with hundreds of entities and use cases, the real number of classes to write, maintain, and test ominously approaches the order of thousands. Any possible reduction of complexity that fulfills requirements is more than welcome.

As an architect, however, you should always be on the alert to recognize signs indicating that the distance between the entity model and what the presentation expects is significant or impossible to cover. In this case, you should take the safer (and cleaner) route of DTOs.

Mixed Approach

Today's layered applications reserve a section of the BLL to the service layer. The service layer (also referred to as the application layer) contains the application logic; that is, the business rules and procedures that are specific to the application but not to the domain. A system with multiple front ends will expose a single piece of domain logic through entity classes, but then each front end will have an additional business layer specific to the use cases it supports. This is what is referred to as the service (or application) layer.

Triggered from the UI, the application logic scripts the entities and services in the business logic. In the service layer, you implement the use cases and expose each sequence of steps through a coarse-grained method for the presentation to call.

In the design of the service layer, you might want to apply a few best practices, embrace service-orientation, and share data contracts instead of entity classes. While this approach is ideal in theory, it often clashes with the real world, as it ends up adding too much overhead in projects with hundreds of entities and use cases.

It turns out that a mixed approach that uses data contracts only when using classes is not possible, is often the more acceptable solution. But as an architect, you must not make this decision lightly. Violating good design rules is allowed, as long as you know what you're doing.

 

Source :  MSDN Magazine, Aug 2009

Signature
Comments Comments (0)
Permalink | Post RSS

I will be consuming the data which was returned by Data Access Layer in the form of XML, Dataset, DataTable, ArrayList and Custom (Data Transfer) Objects to populating my Business Object, which will be finally consumed by a User Interface and most likely a web page. 

In this example, I will use only one class, which will perform the CRUD operation on Customer Object by invoking method of Data Access Layer. Since I have added methods in data access layer to return Customer(s) data in form on XML, Dataset, DataTable, ArrayList and Custom (Data Transfer) Object. No matter in which form data is returned from data access layer, the business object will read that object and consume the data to make it ready for business object caller.

This Business Object will refer the assembly I previously created Vishwa.Example.Data in my earlier post Data Transfer Objects (DTO) - Data Access Layer .

Following are the steps
          Created a Class Library Project in Visual Studio 2005 using language VB.NET
           Project Name: Vishwa.Example.Business
          Class Name: CustomerBiz.vb
CustomerBiz.vb
' <summary>
'   Customer Business Object
' </summary>
' <remarks>
'   Design Pattern: Domain Model and Identity Field
' </remarks>
Option Explicit On
Option Strict On
Imports Vishwa.Example.Data
Imports Vishwa.Example.Data.DataAccess
 
Namespace Example.Business
 
    <Serializable()> _
    Public Class CustomerBiz
        Private _custID As Integer = 0
        Private _custName As String = String.Empty
        Private _custDOB As DateTime = DateTime.MinValue
        Private _custAddress As String = String.Empty
        Private _dateCreated As DateTime = DateTime.Now
        Private _dateModified As DateTime = DateTime.Now
 
#Region "Constructor"
        Public Sub New()
 
        End Sub
#End Region
 
#Region "Properties"
 
        Public Property CustID() As Integer
            Get
                Return _custID
            End Get
 
            Set(ByVal value As Int32)
                _custID = value
            End Set
        End Property
 
        Public Property CustName() As String
            Get
                Return _custName
            End Get
            Set(ByVal value As String)
                _custName = value
            End Set
        End Property
 
        Public Property CustDOB() As DateTime
            Get
                Return _custDOB
            End Get
            Set(ByVal value As DateTime)
                _custDOB = value
            End Set
        End Property
 
        Public Property CustAddress() As String
            Get
                Return _custAddress
            End Get
            Set(ByVal value As String)
                _custAddress = value
            End Set
        End Property
 
        Public Property DateCreated() As DateTime
            Get
                Return _dateCreated
            End Get
            Set(ByVal value As DateTime)
                _dateCreated = value
            End Set
        End Property
 
        Public Property DateModified() As DateTime
            Get
                Return _dateModified
            End Get
            Set(ByVal value As DateTime)
                _dateModified = value
            End Set
        End Property
 
        ' A Simple example of Business Logic to Get Customer Age
        ' Such logics are created in Business Logic/Object Layer    
 
        Public ReadOnly Property CustAge() As Long
            Get
                If _custDOB > DateTime.MinValue Then
                    Return DateDiff(DateInterval.Year, _custDOB, Now())
                Else
                    Return 0
                End If
            End Get
        End Property
#End Region
 
#Region "Customer Methods"
 
        Public Function AddBlankCustomer() As Integer
            Return DataProvider.InsertCustomer("unknown", DateTime.Now, "Unknown")
        End Function
 
        Public Overloads Function AddCustomer(ByVal custInfo As CustomerBiz) As Integer
            Return DataProvider.InsertCustomer(custInfo.CustName, custInfo.CustDOB, "Unknown")
        End Function
 
        Public Overloads Function AddCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
                                              ByVal custAddress As String) As Integer
            Return DataProvider.InsertCustomer(custName, custDOB, custAddress)
        End Function
 
        Public Overloads Function UpdateCustomer(ByVal custInfo As CustomerBiz) As Boolean
            Return DataProvider.UpdateCustomer(custInfo.CustID, custInfo.CustName, custInfo.CustDOB, custInfo.CustAddress)
        End Function
 
        Public Overloads Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
             ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
            Return DataProvider.UpdateCustomer(custID, custName, custDOB, custAddress)
        End Function
 
        Public Overloads Function DeleteCustomer(ByVal custID As Integer) As Boolean
            Return DataProvider.DeleteCustomer(custID)
        End Function
 
        Public Overloads Function DeleteCustomer(ByVal custInfo As CustomerBiz) As Boolean
            Return DataProvider.DeleteCustomer(custInfo.CustID)
        End Function
 
#Region "Get Customers From Custom Data Transfer Object"
        Public Function GetCustomer(ByVal custID As Integer) As CustomerBiz
            Dim custDTORec As New CustomerDTO
            Dim custBIZRec As New CustomerBiz
            custDTORec = DataProvider.GetCustomer(custID)
            If Not custDTORec Is Nothing Then
                custBIZRec = GetCustomerRow(custDTORec)
            End If
            Return custBIZRec
        End Function
 
        Public Function GetCustomers() As List(Of CustomerBiz)
            Dim custBIZRecs As New List(Of CustomerBiz)
            Dim custDTORecs As New List(Of CustomerDTO)
            custDTORecs = DataProvider.GetCustomers
 
            For Each custDTORec As CustomerDTO In custDTORecs
                Dim custBIZRec As CustomerBiz = GetCustomerRow(custDTORec)
                custBIZRecs.Add(custBIZRec)
            Next
            Return custBIZRecs
        End Function
 
#End Region
 
#Region "Consuming Get Methods of Other Data Trasfer Objects"
 
#Region "Get Customers From DataSet Object"
        Public Function GetCustomerFromDataSet(ByVal custID As Integer) As CustomerBiz
            Dim custDataSet As New DataSet("Customer")
            Dim custBIZRec As New CustomerBiz
            custDataSet = DataProvider.GetCustomerAsDataSet(custID)
            If Not custDataSet Is Nothing AndAlso custDataSet.Tables.Count = 1 Then
                custBIZRec = GetCustomerRow(custDataSet.Tables(0).Rows(0))
            End If
            Return custBIZRec
        End Function
 
        Public Function GetCustomersFromDataSet() As List(Of CustomerBiz)
            Dim custDataSet As New DataSet("Customers")
            Dim custBIZRecs As New List(Of CustomerBiz)
            custDataSet = DataProvider.GetCustomersAsDataSet()
 
            If Not custDataSet Is Nothing AndAlso custDataSet.Tables.Count = 1 Then
                For Each row As DataRow In custDataSet.Tables(0).Rows
                    Dim custBIZRec As CustomerBiz = GetCustomerRow(row)
                    custBIZRecs.Add(custBIZRec)
                Next
            End If
            Return custBIZRecs
        End Function
 
#End Region
 
#Region "Get Customers From DataTable Object"
        Public Function GetCustomerFromDataTable(ByVal custID As Integer) As CustomerBiz
            Dim custDataTable As New DataTable("Customer")
            Dim custBIZRec As New CustomerBiz
            custDataTable = DataProvider.GetCustomerAsDataTable(custID)
 
            If Not custDataTable Is Nothing AndAlso custDataTable.Rows.Count > 0 Then
                custBIZRec = GetCustomerRow(custDataTable.Rows(0))
            End If
            Return custBIZRec
        End Function
 
        Public Function GetCustomersFromDataTable() As List(Of CustomerBiz)
            Dim custDataTable As New DataTable("Customers")
            Dim custBIZRecs As New List(Of CustomerBiz)
            custDataTable = DataProvider.GetCustomersAsDataTable()
 
            If Not custDataTable Is Nothing AndAlso custDataTable.Rows.Count > 0 Then
                For Each row As DataRow In custDataTable.Rows
                    Dim custBIZRec As CustomerBiz = GetCustomerRow(row)
                    custBIZRecs.Add(custBIZRec)
                Next
            End If
            Return custBIZRecs
        End Function
#End Region
 
#Region "Get Customers From XML"
        Public Function GetCustomerFromXML(ByVal custID As Integer) As CustomerBiz
            Dim custBIZRecs As New List(Of CustomerBiz)
            Dim custBIZRec As New CustomerBiz
 
            Dim custXMLString As String = DataProvider.GetCustomersAsXMLString()
            If custXMLString.Length > 0 Then
                custBIZRecs = ReadCustomerXML(custXMLString)
                If Not custBIZRecs.Count > 0 Then custBIZRec = custBIZRecs(0)
            End If
 
            Return custBIZRec
        End Function
 
        Public Function GetCustomersFromXML() As List(Of CustomerBiz)
            Dim custBIZRecs As New List(Of CustomerBiz)
           Dim custXMLString As String = DataProvider.GetCustomersAsXMLString()
            If custXMLString.Length > 0 Then
                custBIZRecs = ReadCustomerXML(custXMLString)
            End If
            Return custBIZRecs
        End Function
 
#End Region
 
#Region "Get Customers From ArrayList Object"
        Public Function GetCustomerFromArrayList(ByVal custID As Integer) As CustomerBiz
            Dim custBIZRec As New CustomerBiz
            Dim custArrList As New ArrayList()
            custArrList = DataProvider.GetCustomerAsArrayList(custID)
 
            If Not custArrList Is Nothing AndAlso custArrList.Count > 0 Then
                custBIZRec = GetCustomerRow(custArrList)
            End If
            Return custBIZRec
       End Function
 
        Public Function GetCustomersFromArrayList() As List(Of CustomerBiz)
            Dim custBIZRecs As New List(Of CustomerBiz)
            Dim custArrList As New ArrayList()
            custArrList = DataProvider.GetCustomersAsArrayList()
 
            If Not custArrList Is Nothing AndAlso custArrList.Count > 0 Then
                For Each row As ArrayList In custArrList
                    Dim custBIZRec As CustomerBiz = GetCustomerRow(row)
                    custBIZRecs.Add(custBIZRec)
                Next
            End If
            Return custBIZRecs
        End Function
#End Region
 
#End Region
 
#Region "Common Methods to Transfer data from DTO to Business Object"
        Private Function GetCustomerRow(ByVal row As DataRow) As CustomerBiz
            Dim custBIZRec As New CustomerBiz
            If Not IsDBNull(row.Item("Cust_ID")) Then custBIZRec.CustID = CInt(row.Item("Cust_ID"))
            If Not IsDBNull(row.Item("Cust_DOB")) Then custBIZRec.CustDOB = CDate(row.Item("Cust_DOB"))
            If Not IsDBNull(row.Item("Cust_Name")) Then custBIZRec.CustName = row.Item("Cust_Name").ToString
            If Not IsDBNull(row.Item("Cust_Address")) Then custBIZRec.CustAddress = row.Item("Cust_Address").ToString
            If Not IsDBNull(row.Item("Date_Created")) Then custBIZRec.DateCreated = CDate(row.Item("Date_Created"))
            If Not IsDBNull(row.Item("Date_Modified")) Then custBIZRec.DateModified = CDate(row.Item("Date_Modified"))
            Return custBIZRec
        End Function
 
        Private Function GetCustomerRow(ByVal custDTORec As CustomerDTO) As CustomerBiz
            Dim custBIZRec As New CustomerBiz
            custBIZRec.CustID = custDTORec.CustID
            custBIZRec.CustName = custDTORec.CustName
            custBIZRec.CustDOB = custDTORec.CustDOB
            custBIZRec.CustAddress = custDTORec.CustAddress
            custBIZRec.DateCreated = custDTORec.DateCreated
            custBIZRec.DateModified = custDTORec.DateModified
            Return custBIZRec
        End Function
 
        Private Function GetCustomerRow(ByVal custArrList As ArrayList) As CustomerBiz
            Dim custBIZRec As New CustomerBiz
            If IsNumeric(custArrList.Item(0)) Then custBIZRec.CustID = CInt(custArrList.Item(0))
            If Not custArrList.Item(1) Is Nothing Then custBIZRec.CustName = custArrList.Item(1).ToString
            If IsDate(custArrList.Item(2)) Then custBIZRec.CustDOB = CDate(custArrList.Item(2))
            If Not custArrList.Item(3) Is Nothing Then custBIZRec.CustAddress = custArrList.Item(3).ToString
            If IsDate(custArrList.Item(4)) Then custBIZRec.DateCreated = CDate(custArrList.Item(4))
            If IsDate(custArrList.Item(5)) Then custBIZRec.DateModified = CDate(custArrList.Item(5))
            Return custBIZRec
        End Function
 
        Private Function ReadCustomerXML(ByVal xmlDocString As String) As List(Of CustomerBiz)
            Dim customers As New List(Of CustomerBiz)
            Dim status As Integer = 0
            Dim statusDescription As String = String.Empty
            Dim uniEncoding As New System.Text.ASCIIEncoding()
            Dim stringBytes As Byte() = uniEncoding.GetBytes(xmlDocString)
            Dim xmlDocSteam As New System.IO.MemoryStream(stringBytes, 0, stringBytes.Length)
 
            Using xmlDocReader As System.Xml.XmlReader = System.Xml.XmlReader.Create(xmlDocSteam)
                xmlDocReader.Read()
                xmlDocReader.MoveToContent()
 
                While xmlDocReader.Read()
                    If xmlDocReader.NodeType = System.Xml.XmlNodeType.Element And xmlDocReader.Name = "Customer" Then
                        Dim custRec As New CustomerBiz
                        While (xmlDocReader.Read() And xmlDocReader.Name <> "Customer")
                            If xmlDocReader.Name.Length > 0 Then
                                If xmlDocReader.Name = "Cust_ID" Then custRec.CustID = xmlDocReader.ReadElementContentAsInt("Cust_ID", "")
                                If xmlDocReader.Name = "Cust_Name" Then custRec.CustName = xmlDocReader.ReadElementContentAsString("Cust_Name", "")
                                If xmlDocReader.Name = "Cust_DOB" Then custRec.CustDOB = xmlDocReader.ReadElementContentAsDateTime("Cust_DOB", "")
                                If xmlDocReader.Name = "Cust_Address" Then custRec.CustAddress = xmlDocReader.ReadElementContentAsString("Cust_Address", "")
                                If xmlDocReader.Name = "Date_Created" Then custRec.DateCreated = xmlDocReader.ReadElementContentAsDateTime("Date_Created", "")
                                If xmlDocReader.Name = "Date_Modified" Then custRec.DateModified = xmlDocReader.ReadElementContentAsDateTime("Date_Modified", "")
                            End If
                        End While
                        customers.Add(custRec)
                    End If
                End While
            End Using
            xmlDocSteam.Close()
            Return customers
        End Function
 
#End Region
 
#End Region
 
    End Class
End Namespace
Compiled Assembly Name: Vishwa.Example.Business.dll
Now you can use the reference of the above assembly for customer object in your ASP.NET Web Page and test the functionality.
Signature
Comments Comments (0)
Permalink | Post RSS

When you separate your business layer from data access layer in two different assembly, you come across a situation as which data transfer method will best fit you. In this example, I went through various Data Transfer Objects(DTO) which are currently available in .NET such as XML, DataSet, DataTable, ArrayList and Custom Data Transfer Object . One DTO generally maps to one Business Object. Each option has its pros and cons, so depending on project one should choose as what best fits the need. In the following code I have tried to use all the four possibilities.

Please be aware of the fact that XML is the most flexible but slowest in performance among all. On the other side Custom Objects are least flexible, take extra time to develop but are best performing among all. Using DataSet  and DataTable comes in the middle as they are easy to develop and relatively better in performance with some overhead but relationships between data tables can be easily made and also it has inbuilt capability for sorting, filtering etc.  Custom Objects has minimum overhead, best performance, no boxing or un-boxing but you have to write your own code for adding all additional flexibility which comes by default with DataSet. In my personal opinion, if you are going to develop small to medium size project and quick delivery is the first priority, going with DataSet will be the best option, but you are going to build an Enterprise Application with thousands of concurrent user, then consider developing Custom Objects. I had an opportunity to attend recent Microsoft Seminar on VS 2008, in which they talked about upcoming ADO.NET Entity Framework , which looks like a promising future for designing and developing Custom Entities quickly and easily along with LINQ capability - most likely it will be available with VS 2008 release.  To undertstand the details on pros and cons on business entity represented in various ways, you can check out the following link -  http://msdn2.microsoft.com/en-us/library/ms978496.aspx . Following is a simplified example to implement the same. ArrayList object falls in between DataSet and Custom Object, since it is an array, so it performs faster but due to boxing and unboxing it comes on 2nd in the performance race.
This example will implement various data transfer objects such as XML, Dataset, DataTable, ArrayList and Custom (Data Transfer) Object. Custom Object for Data Transfer refers to a class primarily made for Set and Get operation with mapping field of database table. This layer will be compiled as a separate assembly so Data Access can be truly separated from Business Object Layer. However, Business Object Layer will be referring this assembly to call these methods. DataTable represents one table, if you wish to get data from multiple table and its relationship, this option will not work.
The main benefit of this approach is if you need to make any changes to Data Access Layer or Business Layer, you will be touching only that layer unless you are changing the signature of existing method. Thus, it helps you to separate your Data Access Layer from Business Layer for maintainability and reusability prospective.
Data Access Layer only deals with Data Normalization, where as in Business Layer,  Business Object deals with Behavior Normalization.Thus, the data access layer will return the result in a relational fashion where as Business Object will apply the logic to make it behave as it is needed by business rules.
In this example, I am considering possibility of two data sources – XML or SQL Server. You can choose DataProvider by setting a value SQL or XML in web.config. If you are using SQL Server, provide the connection string, if XML then provide XMLDataFilePath  in web.config. If you do not configure these values, the data access layer will choose XML as data provider by default and your C:\ drive for the location of Customer.XML data file.
For SQL script and stored procedures you can check out my previous post Developing 3 Tier Application in .NET 2.0 – Part 1 . I am using exactly same objects for this example too.
Following are the steps
1.       Created a Class Library Project in Visual Studio 2005 using language VB.NET
a.       Project Name: Vishwa.Example.Data
 
2.       Added following four class files
a.       CustomerDTO.vb – Custom Data Transfer Object Class
b.      SQLProvider.vb – A Class containing SQL Server CRUD operations
c.       XMLProvider.vb – A Class containing XML file CRUD operations
d.      DataAccess.vb – A Class which acts as a factory and main interface for Business Layer through a Property which determines the corresponding Data Provider based on config setting.
Note: You must be thinking, too many classes for just CRUD operations for such a small table. But in order to keep things logically separated and clean and to implement different types of data transfer objects using different data provider model, it makes sense to create it. So here we go with code.
Web.Config
<appSettings>
            <add key="DataProvider" value="SQL"/> <!-- You can choose either SQL or XML as Data Provider, In case of SQL, you must have a valid connection string defined -->
            <add key="XMLDataFilePath" value="C:\Temp"/> <!--Path for XML Data File if DataProvider is XML-->
</appSettings>
     
<connectionStrings>
            <add name="ExampleDB" connectionString="Data Source=localhost;Database=Example;User ID=<UserID>;Password=<Password>;" providerName="System.Data.SqlClient"/>
</connectionStrings>
 
CustomerDTO.VB
 
Option Strict On
Option Explicit On
 
''' Design Pattern: Data Transfer Objects.
''' </summary>
''' <remarks>
''' Data Transfer Objects transfers data between processes, but without behavior, thus no properties or methods needed.
''' </remarks>
 
Namespace Example.Data
    <Serializable()> _
    Public Class CustomerDTO
 
        Public CustID As Integer = 0
        Public CustName As String = String.Empty
        Public CustDOB As DateTime = DateTime.MinValue
        Public CustAddress As String = String.Empty
        Public DateCreated As DateTime = DateTime.Now
        Public DateModified As DateTime = DateTime.Now
 
#Region "Constructor"
        Public Sub New()
 
        End Sub
#End Region
 
    End Class
End Namespace
 
DataAccess.vb: The following code is also a simplest example of  Factory Pattern, however it can be modified  for Abstract Factory pattern for best uses.
 
Option Strict On
Option Explicit On
Imports System.Web.Configuration
 
' Design Pattern : Factory
' Purpose: Providers Appropriate Provider based on Data Provider Type
 
Namespace Example.Data
   
    Public MustInherit Class DataAccess
 
#Region "Constructor"
        Public Sub New()
 
        End Sub
#End Region
 
        Private Shared _provider As DataAccess= Nothing
 
        Public Shared ReadOnly Property DataProvider() As DataAccess
            Get
                    If DataProviderType = "SQL" Then
                        Return New SQLProvider
                    Else
                        Return New XMLProvider
                    End If
             End Get
        End Property
 
#Region "Customer Methods"
        Public MustOverride Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
        Public MustOverride Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
        Public MustOverride Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
        Public MustOverride Function GetCustomer(ByVal custID As Int32) As DTOCustomer
        Public MustOverride Function GetCustomers() As List(Of DTOCustomer)
 
        Public MustOverride Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
                                              ByVal custAddress As String) As Integer
        Public MustOverride Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
                                             ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
        Public MustOverride Function DeleteCustomer(ByVal custID As Integer) As Boolean
 
        Public MustOverride Function GetCustomerAsXMLString(ByVal custID As Int32) As String
        Public MustOverride Function GetCustomersAsXMLString() As String
 
        Public MustOverride Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
        Public MustOverride Function GetCustomersAsDataSet() As DataSet
 
        Public MustOverride Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
        Public MustOverride Function GetCustomersAsDataTable() As DataTable
 
        Public MustOverride Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
        Public MustOverride Function GetCustomersAsArrayList() As ArrayList
#End Region
 
#Region "Common Property"
 
        Protected Shared ReadOnly Property ConnectionString() As String
            Get
                Try
                    Return WebConfigurationManager.ConnectionStrings.Item("ExampleDB").ConnectionString
                Catch exc As Exception
                    Return ""
                End Try
            End Get
        End Property
 
 
        Protected Shared ReadOnly Property XMLDataFile() As String
            Get
                Try
                    Return WebConfigurationManager.AppSettings.Item("XMLDataFilePath") & "\Customer.xml"
                Catch exc As Exception
                    Return "C:\Customer.xml"
                End Try
            End Get
        End Property
 
 
        Protected Shared ReadOnly Property DataProviderType() As String
            Get
                Try
                    Return WebConfigurationManager.AppSettings.Item("DataProvider").ToUpper
                Catch exc As Exception
                    Return "XML"
                End Try
            End Get
        End Property
#End Region
 
    End Class
End Namespace
 
SQLProvider.VB
Option Strict On
Option Explicit On
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Web.Configuration
 
 
' Purpose: SQL Server Data Provider
 
Namespace Example.Data
 
    Friend Class SQLProvider
        Inherits DataAccess
 
#Region "Constructor"
        Public Sub New()
 
        End Sub
#End Region
 
#Region "CRUD Operation Using Various Data Trasfer Objects"
 
#Region "Custom Data Transfer Object"
 
        Public Overrides Function GetCustomer(ByVal custID As Int32) As DTOCustomer
            Dim record As New DTOCustomer
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                        .Connection.Open()
                    End With
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
                        Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
                        Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
                        Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
                        Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
                        Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
 
                        If reader.Read Then
                            If Not reader.IsDBNull(custIDOrdinal) Then record.CustID = reader.GetInt32(custIDOrdinal)
                            If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
                            If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
                            If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
                            If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
                            If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
                        End If
                    End Using
                End Using
            End Using
            Return record
        End Function
 
        Public Overrides Function GetCustomers() As List(Of DTOCustomer)
            Dim records As New List(Of DTOCustomer)
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
                        .Connection.Open()
                    End With
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
                        Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
                        Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
                        Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
                        Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
                        Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
 
                        Do While reader.Read
                            Dim record As New DTOCustomer
                            If Not reader.IsDBNull(custIDOrdinal) Then record.CustID = reader.GetInt32(custIDOrdinal)
                            If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
                            If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
                            If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
                            If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
                            If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
                            records.Add(record)
                        Loop
                    End Using
                End Using
            End Using
            Return records
        End Function
 
        Public Overrides Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
            Return InsertCustomer(custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
        End Function
 
        Public Overrides Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
            Return UpdateCustomer(custRecord.CustID, custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
        End Function
 
        Public Overrides Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
            Return DeleteCustomer(custRecord.CustID)
        End Function
#End Region
 
#Region "Insert/Update/Delete Methods"
        Public Overrides Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
                                              ByVal custAddress As String) As Integer
            Dim returnValue As Integer = 0
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
 
                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                        .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
                        .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
                        .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
                        .Connection.Open()
                        .ExecuteNonQuery()
                        returnValue = CInt(command.Parameters("@ReturnID").Value)
                    End With
                End Using
            End Using
            Return returnValue
        End Function
 
        Public Overrides Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
                                              ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
            Dim returnValue As Integer = 0
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
 
                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                        .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
                        .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
                        .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
 
                        .Connection.Open()
                        .ExecuteNonQuery()
                        returnValue = CInt(command.Parameters("@ReturnID").Value)
                    End With
                End Using
            End Using
            Return (returnValue > 0)
        End Function
 
        Public Overrides Function DeleteCustomer(ByVal custID As Integer) As Boolean
            Dim returnValue As Integer = 0
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
 
                        .Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                        .Connection.Open()
                        .ExecuteNonQuery()
                        returnValue = CInt(command.Parameters("@ReturnID").Value)
                    End With
                End Using
            End Using
            Return (returnValue > 0)
        End Function
 
#End Region
 
#Region "Implementing Two Methods with Other Data Trasfer Objects"
 
#Region "XML String"
        Public Overrides Function GetCustomerAsXMLString(ByVal custID As Int32) As String
 
            Return GetCustomerAsDataSet(custID).GetXml
        End Function
 
        Public Overrides Function GetCustomersAsXMLString() As String
            Return GetCustomersAsDataSet().GetXml
        End Function
#End Region
 
#Region "DataSet Object"
 
        Public Overrides Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
            Dim resultDataSet As New DataSet("Customers")
            Using connection As New SqlConnection(ConnectionString)
                Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
                    With dataAdapter
                        .SelectCommand.CommandType = CommandType.StoredProcedure
                        .SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                        .Fill(resultDataSet, "Customer")
                    End With
                End Using
            End Using
            Return resultDataSet
        End Function
 
        Public Overrides Function GetCustomersAsDataSet() As DataSet
            Dim resultDataSet As New DataSet("Customers")
            Using connection As New SqlConnection(ConnectionString)
                Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomers", connection)
                    With dataAdapter
                        .SelectCommand.CommandType = CommandType.StoredProcedure
                        .Fill(resultDataSet, "Customer")
                    End With
                End Using
            End Using
            Return resultDataSet
        End Function
#End Region
 
#Region "DataTable Object"
        Public Overrides Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
            Dim recordDataTable As New DataTable("Customer")
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                    command.CommandType = CommandType.StoredProcedure
                    command.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    command.Connection.Open()
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        recordDataTable.Load(reader)
                    End Using
                End Using
            End Using
            Return recordDataTable
        End Function
 
        Public Overrides Function GetCustomersAsDataTable() As DataTable
            Dim recordDataTable As New DataTable("Customers")
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
                    command.CommandType = CommandType.StoredProcedure
                    command.Connection.Open()
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        recordDataTable.Load(reader)
                    End Using
                End Using
            End Using
            Return recordDataTable
        End Function
#End Region
 
#Region "ArrayList Object"
        Public Overrides Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
            Dim arrCustomer As New ArrayList()
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                        .Connection.Open()
                    End With
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
                        Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
                        Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
                        Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
                        Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
                        Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
 
                        If reader.Read Then
                            arrCustomer.Add(reader.Item(custIDOrdinal))
                            arrCustomer.Add(reader.Item(custNameOrdinal))
                            arrCustomer.Add(reader.Item(custDOBOrdinal))
                            arrCustomer.Add(reader.Item(custAddressOrdinal))
                            arrCustomer.Add(reader.Item(custDateCreatedOrdinal))
                            arrCustomer.Add(reader.Item(custDateModifiedOrdinal))
                        End If
                    End Using
                End Using
            End Using
            arrCustomer.TrimToSize()
            Return arrCustomer
        End Function
 
        Public Overrides Function GetCustomersAsArrayList() As ArrayList
            Dim arrCustomers As New ArrayList()
            Using connection As New SqlConnection(ConnectionString)
                Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
                    With command
                        .CommandType = CommandType.StoredProcedure
                        .Connection.Open()
                    End With
 
                    Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                        Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
                        Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
                        Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
                        Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
                        Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
                        Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
 
                        Do While reader.Read
                            Dim arrCustomer As New ArrayList
                            arrCustomer.Add(reader.Item(custIDOrdinal))
                            arrCustomer.Add(reader.Item(custNameOrdinal))
                            arrCustomer.Add(reader.Item(custDOBOrdinal))
                            arrCustomer.Add(reader.Item(custAddressOrdinal))
                            arrCustomer.Add(reader.Item(custDateCreatedOrdinal))
                            arrCustomer.Add(reader.Item(custDateModifiedOrdinal))
                            arrCustomer.TrimToSize()
                            arrCustomers.Add(arrCustomer)
                        Loop
                    End Using
                End Using
            End Using
            arrCustomers.TrimToSize()
            Return arrCustomers
        End Function
 
#End Region
 
#End Region
 
#End Region
 
    End Class
End Namespace
 
 
XMLProvider.vb
Option Strict On
Option Explicit On
Imports System.Data
Imports System.IO
Imports System.Xml
 
' Author : Vishwa
' Purpose: XML Data Provider
 
Namespace Example.Data
    Friend Class XMLProvider
        Inherits DataAccess
 
        Public Sub New()
 
        End Sub
#Region "CRUD Operation Using Various Data Trasfer Objects"
 
#Region "Custom Data Transfer Object"
        Public Overrides Function GetCustomer(ByVal ID As Integer) As DTOCustomer
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
            Dim custRecord As DTOCustomer = Nothing
 
            If custDataSet.Tables.Count > 0 Then
                Dim dataRow As DataRow()
                dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & ID.ToString & "'")
 
                'Assumption : there will be only one record for given ID
                If dataRow.Length > 0 Then
                    custRecord = New DTOCustomer
                    custRecord.CustID = CInt(dataRow(0).Item("Cust_ID"))
                    custRecord.CustName = dataRow(0).Item("Cust_Name").ToString
                    custRecord.CustDOB = CDate(dataRow(0).Item("Cust_DOB"))
                    custRecord.CustAddress = dataRow(0).Item("Cust_Address").ToString
                    custRecord.DateCreated = CDate(dataRow(0).Item("Date_Created"))
                    custRecord.DateModified = CDate(dataRow(0).Item("Date_Modified"))
                End If
 
                '--Alternative Way to Search a Customer in DataSet
                'Dim custDataView As DataView = custDataSet.Tables(0).DefaultView
                'custDataView.RowFilter = "ID=" & ID.ToString
                'If custDataView.Count > 0 Then
                '    custRecord = New Customer
                '    custRecord.CustID = CInt(custDataView.Item(0).Item("ID"))
                '    custRecord.CustName = custDataView.Item(0).Item("Name").ToString
                '    custRecord.CustDOB = CDate(custDataView.Item(0).Item("DOB"))
                '    custRecord.CustAddress = custDataView.Item(0).Item("Address").ToString
                '    custRecord.DateCreated = CDate(custDataView.Item(0).Item("DateCreated"))
                '    custRecord.DateModified = CDate(custDataView.Item(0).Item("DateModified"))
                'End If
            End If
 
 
            Return custRecord
        End Function
 
        Public Overrides Function GetCustomers() As List(Of DTOCustomer)
            Dim custDataSet As New DataSet("Customers")
            Dim custRecord As DTOCustomer = Nothing
            Dim customers As New List(Of DTOCustomer)
 
            If File.Exists(XMLDataFile) Then
                custDataSet.ReadXml(XMLDataFile)
                For i As Integer = 0 To custDataSet.Tables(0).Rows.Count - 1
                    Dim dataRow As DataRow = custDataSet.Tables(0).Rows(i)
                    custRecord = New DTOCustomer
                    custRecord.CustID = CInt(dataRow.Item("Cust_ID"))
                    custRecord.CustName = dataRow.Item("Cust_Name").ToString
                    custRecord.CustDOB = CDate(dataRow.Item("Cust_DOB"))
                    custRecord.CustAddress = dataRow.Item("Cust_Address").ToString
                    custRecord.DateCreated = CDate(dataRow.Item("Date_Created"))
                    custRecord.DateModified = CDate(dataRow.Item("Date_Modified"))
                    customers.Add(custRecord)
                Next
            End If
            Return customers
        End Function
 
        Public Overrides Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
            Return InsertCustomer(custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
        End Function
 
        Public Overrides Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
            Return UpdateCustomer(custRecord.CustID, custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
        End Function
 
        Public Overrides Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
            Return DeleteCustomer(custRecord.CustID)
        End Function
#End Region
 
#Region "Insert/Update/Delete Methods"
        Public Overrides Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
                                              ByVal custAddress As String) As Integer
 
            Dim custDataSet As New DataSet("Customers")
            Dim returnValue As Integer = 0
            Dim custTable As DataTable = Nothing
            Dim row As DataRow
 
            If File.Exists(XMLDataFile) Then
                custDataSet.ReadXml(XMLDataFile)
                If custDataSet.Tables.Count > 0 Then custTable = custDataSet.Tables(0)
            Else
                custTable = GetCustomerTable()
                custDataSet.Tables.Add(custTable)
            End If
 
            row = custTable.NewRow()
            returnValue = GetNextUniqueID()
            row.Item("Cust_ID") = returnValue 'CustID
            row.Item("Cust_Name") = custName
            row.Item("Cust_Address") = custAddress
            row.Item("Cust_DOB") = XmlConvert.ToString(custDOB, XmlDateTimeSerializationMode.Local)
            row.Item("Date_Created") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
            row.Item("Date_Modified") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
            custDataSet.Tables(0).Rows.Add(row)
            custDataSet.WriteXml(XMLDataFile)
 
            Return returnValue
        End Function
 
        Public Overrides Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
                                              ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
            Dim returnValue As Boolean = False
            Dim custTable As DataTable = Nothing
 
            If custDataSet.Tables.Count > 0 Then
                Dim dataRow As DataRow()
                dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
 
                'Assumption : there will be only one record for given ID
                If dataRow.Length > 0 Then
                    dataRow(0).Item("Cust_Name") = custName
                    dataRow(0).Item("Cust_Address") = custAddress
                    dataRow(0).Item("Cust_DOB") = XmlConvert.ToString(custDOB, XmlDateTimeSerializationMode.Local)
                    dataRow(0).Item("Date_Modified") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
                    returnValue = True
                End If
                custDataSet.WriteXml(XMLDataFile)
            End If
            Return returnValue
        End Function
 
        Public Overrides Function DeleteCustomer(ByVal ID As Integer) As Boolean
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
            Dim returnValue As Boolean = False
            Dim custTable As DataTable = Nothing
 
            If custDataSet.Tables.Count > 0 Then
                Dim dataRow As DataRow()
                dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & ID.ToString & "'")
                'Assumption : there will be only one record for given ID
                If dataRow.Length > 0 Then
                    dataRow(0).Delete()
                    returnValue = True
                End If
                custDataSet.WriteXml(XMLDataFile)
            End If
            Return returnValue
        End Function
#End Region
 
#Region "Implementing Two Methods with Other Data Trasfer Objects"
 
#Region "XML String"
        Public Overrides Function GetCustomerAsXMLString(ByVal custID As Int32) As String
            Return GetCustomerAsDataSet(custID).GetXml
        End Function
 
        Public Overrides Function GetCustomersAsXMLString() As String
            Return GetCustomersAsDataSet().GetXml
        End Function
#End Region
 
#Region "DataSet Object"
 
        Public Overrides Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
            Dim resultDataSet As New DataSet("Customers")
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
            Dim resultDataTable As DataTable = GetCustomerTable()
 
            If custDataSet.Tables.Count > 0 Then
                Dim dataRow As DataRow()
                dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
                If dataRow.Length > 0 Then
                    resultDataTable.Rows.Add(dataRow(0))
                    resultDataTable.AcceptChanges()
                    resultDataSet.Tables.Add(resultDataTable)
                End If
            End If
            Return resultDataSet
        End Function
 
        Public Overrides Function GetCustomersAsDataSet() As DataSet
            Dim resultDataSet As New DataSet("Customers")
            If File.Exists(XMLDataFile) Then resultDataSet.ReadXml(XMLDataFile)
            Return resultDataSet
        End Function
 
#End Region
 
#Region "DataTable Object"
        Public Overrides Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
            Dim recordDataTable As New DataTable("Customer")
            recordDataTable = GetCustomerAsDataSet(custID).Tables(0)
            Return recordDataTable
        End Function
 
        Public Overrides Function GetCustomersAsDataTable() As DataTable
            Dim recordDataTable As New DataTable("Customer")
            recordDataTable = GetCustomersAsDataSet().Tables(0)
            Return recordDataTable
        End Function
#End Region
 
#Region "ArrayList Object"
        Public Overrides Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
            Dim arrCustomer As New ArrayList()
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
 
            If custDataSet.Tables.Count > 0 Then
                Dim row As DataRow()
                row = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
 
                'Assumption : there will be only one record for given ID
                If row.Length > 0 Then
                    arrCustomer.Add(row(0).Item("Cust_ID"))
                    arrCustomer.Add(row(0).Item("Cust_Name"))
                    arrCustomer.Add(row(0).Item("Cust_DOB"))
                    arrCustomer.Add(row(0).Item("Cust_Address"))
                    arrCustomer.Add(row(0).Item("Date_Created"))
                    arrCustomer.Add(row(0).Item("Date_Modified"))
                End If
            End If
 
            arrCustomer.TrimToSize()
            Return arrCustomer
        End Function
 
        Public Overrides Function GetCustomersAsArrayList() As ArrayList
            Dim arrCustomers As New ArrayList()
            Dim custDataSet As DataSet = GetCustomersAsDataSet()
 
            If custDataSet.Tables.Count > 0 Then
                For Each row As DataRow In custDataSet.Tables(0).Rows
                    Dim arrCustomer As New ArrayList
                    arrCustomer.Add(row.Item("Cust_ID"))
                    arrCustomer.Add(row.Item("Cust_Name"))
                    arrCustomer.Add(row.Item("Cust_DOB"))
                    arrCustomer.Add(row.Item("Cust_Address"))
                    arrCustomer.Add(row.Item("Date_Created"))
                    arrCustomer.Add(row.Item("Date_Modified"))
                    arrCustomer.TrimToSize()
                    arrCustomers.Add(arrCustomer)
                Next
            End If
            arrCustomers.TrimToSize()
            Return arrCustomers
        End Function
 
#End Region
 
#Region "Private Shared Methods"
 
        Private Shared Function GetCustomerTable() As DataTable
            Dim customersTable As DataTable = New DataTable("Customer")
            customersTable.Columns.Add("Cust_ID", GetType(System.Int32))
            customersTable.Columns.Add("Cust_Name", GetType(System.String))
            customersTable.Columns.Add("Cust_DOB", GetType(System.DateTime))
            customersTable.Columns.Add("Cust_Address", GetType(System.String))
            customersTable.Columns.Add("Date_Created", GetType(System.DateTime))
            customersTable.Columns.Add("Date_Modified", GetType(System.DateTime))
            Return customersTable
        End Function
 
        Private Shared Function GetNextUniqueID() As Integer
            Dim uniqueID As Guid = Guid.NewGuid()
            Dim bArr As Byte() = uniqueID.ToByteArray()
            Dim autoNum As Integer = BitConverter.ToInt32(bArr, 0)
            If autoNum < 0 Then autoNum = autoNum * -1
            Return autoNum
        End Function
 
#End Region
 
#End Region
 
#End Region
 
    End Class
End Namespace
 
 
Compiled Assembly Name: Vishwa.Example.Data.dll
In next post Data Transfer Objects (DTO) – Business Layer, I will be referring this assembly to consume these methods.
Signature
Comments Comments (0)
Permalink | Post RSS

The following example will demonstrate as how to freeze or fix one or more column in a GridView. I have used stylesheet to achieve this result. At present this solution only works in IE 7.0. The data source is a xml file containing product sales result.

This example freezes the first column “Product Name” and allows scrolling GridView horizontally to see monthly sales for each product.

XML Data Format: Sample File

 <?xmlversion="1.0" encoding="utf-8" ?>
<ProductSales>
<Product ID="1" Name="Product 1" Year ="2007">
    <Jan>123,200</Jan>
    <Feb>333,300</Feb>
    <Mar>332,400</Mar>
    <Apr>222,200</Apr>
    <May>444,250</May>
    <Jun>234,300</Jun>
    <Jul>229,233</Jul>
    <Aug>111,333</Aug>
    <Sep>212,646</Sep>
    <Oct>423,266</Oct>
    <Nov>987,332</Nov>
    <Dec>282,234</Dec>
</Product>
</ProductSales>

  Page Name: FreezeColumnGridView.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="FreezeColumnGridView.aspx.vb" Inherits="FreezeColumnGridView" %>

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Product Sales</title>
   
 <%-- Ideally you can use the following link to have stylesheet as a separate file
    <link href="App_Themes/StyleSheet.css" rel="stylesheet" type="text/css" /> --%>
   
    <style type="text/css">
       
    body
    {
          font-family: Arial,Verdana, Helvetica;
          background-color:White;  
    }
H2    {    
          font-family: Arial,Verdana, Helvetica;
          font-size:    18px;
          font-weight:bold;  
      }
.OddColumn
    {
        font-family: Arial,Verdana, Helvetica;
        font-size: 14px;
        font-weight: normal;
        border-style: solid;
        border-width: 1px;
        border-color: #999966;
        width:60px;   
    }
.EvenColumn
    {
        font-family: Arial,Verdana, Helvetica;
        font-size: 14px;
        font-weight: normal;
        border-style: solid;
        border-width: 1px;
        border-color: #999966;
        width:60px;
        background-color:Silver;       
    }
.StaticColumn
      {    
          font-family: Arial,Verdana, Helvetica;
        font-size: 14px;
        font-weight: normal;   
          border: none;
          position: relative;
      }
     
#GridViewContainer
    {
          overflow: scroll;  
          margin-bottom: 14px;     
          width:600px;
    }
.GridViewHeader
      {
            font-family: Arial,Verdana, Helvetica;
            font-size: 14px;       
            font-weight:bold;
            color: black;          
        background-color:#aaaadd;             
    }
.GridViewRow
      {
            font-family: Arial,Verdana, Helvetica;
            font-size: 14px;       
            color: black;                
        background-color:#ccccff;                  
    }
   
.GridViewAltRow
      {
            font-family: Arial,Verdana, Helvetica;         
            font-size: 14px;
            color: black;          
            background-color:white;
    }
   
    </style>
   
</head>
<body>
    <form id="frmTestGridView" runat="server">
        <h2>Freeze column in GridView</h2>
        <div id="GridViewContainer">                          
               <asp:GridView ID="gvwSales" runat="server" DataKeyNames="ID"
                EmptyDataText="No record found." AutoGenerateColumns="false" AllowPaging="False">                       
                       <RowStyle CssClass="GridViewRow" />
                       <AlternatingRowStyle CssClass="GridViewAltRow" />
                      <HeaderStyle CssClass="GridViewHeader" />
                        <Columns>                                                             
                               <asp:BoundField DataField="Name" HeaderText="Product Name" ReadOnly="true" ItemStyle-CssClass="StaticColumn" HeaderStyle-CssClass="StaticColumn"
                                                     HeaderStyle-Font-Bold="true" ItemStyle-Wrap="false" HeaderStyle-Wrap="false"/>                   
                               <asp:BoundField DataField="Jan" HeaderText="Jan" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true" />                   
                               <asp:BoundField DataField="Feb" HeaderText="Feb" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Mar" HeaderText="Mar" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Apr" HeaderText="Apr" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                    
                               <asp:BoundField DataField="May" HeaderText="May" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Jun" HeaderText="Jun" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Jul" HeaderText="Jul" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Aug" HeaderText="Aug" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Sep" HeaderText="Sep" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Oct" HeaderText="Oct" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Nov" HeaderText="Nov" ReadOnly="true" ItemStyle-CssClass="EvenColumn" HeaderStyle-CssClass="EvenColumn" HeaderStyle-Font-Bold="true"/>                   
                               <asp:BoundField DataField="Dec" HeaderText="Dec" ReadOnly="true" ItemStyle-CssClass="OddColumn" HeaderStyle-CssClass="OddColumn" HeaderStyle-Font-Bold="true"/>                                                                               
                         </Columns>
                    </asp:GridView> 
           </div>   
    </form>
</body>
</html>
 
 

  Code Behind : FreezeColumnGridView.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

 

            If Not Page.IsPostBack Then

                Dim salesData As New System.Data.DataSet

                salesData.ReadXml("C:\Example\Vishwa.Example.WebSite\App_Data\ProductSales.xml")

                Me.gvwSales.DataSource = salesData

                Me.gvwSales.DataBind()

            End If

        End Sub

 

  Output at the runtime:

Signature
Comments Comments (15)
Permalink | Post RSS

It is very important to evaluate the most efficient way of passing data between layers. Many people recommend using ArrayList, some Generic List. But which one is the most efficient way to use? I thought to perform a speed test between Generic List and ArrayList in VS2005. Some people blindly accept that ArrayList is the fastest way. But is it true? Let’s have a real test by reading and writing hundred thousand records using each type and see the outcome. Knowing the fact that Collection, Data Table and XML is slower than these two but how much? Let’s put them to test too and compare the result.

 Following Windows From returns the result 

 

 

 From the above data, Generic List is a clear winner. The only downside is you have to write extra code for that. However, ArrayList takes the second place and relatively good with respect to Collection, Data Table or XML. So the decision is based on how efficient you want? You have to also realize the fact that in ArrayList, you will have 2 issues .First, you have to always retrieve data based on index (does not clearly tells you which column you are reading unless you know the mapping ). Second, you pay the penalty of Boxing/Unboxing, because every element in the ArrayList is an object, you will have to convert them to their actual data type. The benefit is you have to write less code and still achieve the better performance. 

 Following link gives more information and clear understanding on non generic and generic type and where to use what

http://blogs.msdn.com/kcwalina/archive/2005/09/23/Collections.aspx

Another important thing you must consider under 64 bit Operating System. Following is an article you can read.

http://blogs.msdn.com/joshwil/archive/2004/04/13/112598.aspx

Some of you asked as where is the code. I am dropping the code here, you can checkout on your own

Public Class FormSpeedTest

    Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click

        btnRun.Enabled = False

        Call WriteArrayListData()

        Call WriteCustomerObjectData()

        Call WriteDataTableData()

        btnRun.Enabled = True

    End Sub

    Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click

        btnRead.Enabled = False

        Call ReadArrayListData()

        Call ReadCustomerObjectData()

        Call ReadFromCustomerTable()

        Call ReadFromCustomerTableOptimization1()

        Call ReadFromCustomerTableOptimization2()

        'Call ReadXMLFromCustomerTable()

        btnRead.Enabled = True

    End Sub

    '/***********************ARRAY LIST Functionality ****************************************/

    Private Function WriteToArrayListByValue(ByVal custID As Int32, ByVal custName As String, ByVal custSex As Boolean, _

            ByVal custSalary As Decimal, ByVal custMaritalStatus As Char, ByVal custDOB As DateTime) As Object

        Dim arrCustomer As New ArrayList()

        arrCustomer.Add(custID)

        arrCustomer.Add(custName)

        arrCustomer.Add(custSex)

        arrCustomer.Add(custSalary)

        arrCustomer.Add(custMaritalStatus)

        arrCustomer.Add(custDOB)

        Return arrCustomer

    End Function

    Private Function WriteToArrayListFromArrayList(ByVal arrCust As ArrayList) As Object

        Dim arrCustomer As New ArrayList()

        arrCustomer.Add(arrCust(0))

        arrCustomer.Add(arrCust(1))

        arrCustomer.Add(arrCust(2))

        arrCustomer.Add(arrCust(3))

        arrCustomer.Add(arrCust(4))

        arrCustomer.Add(arrCust(5))

        Return arrCustomer

    End Function

    Private Sub WriteArrayListData()

        Dim stpWatch1 As New Diagnostics.Stopwatch

        Dim customerList As New ArrayList

        Dim customerObj As New List(Of SpeedTestCustomer)

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

        stpWatch1.Start()

        For i As Integer = 0 To 1000000

            customerList.Add(WriteToArrayListByValue(i, "Customer Name " & i.ToString, s, l * i, m, d))

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch1.Stop()

        lblArrayListWriteMsg.Text = "Loading an ArrayList Object with " & customerList.Count.ToString & " Items. Total Duration : " & (stpWatch1.ElapsedMilliseconds / 1000).ToString & " Seconds."

 

    End Sub

    Private Sub ReadArrayListData()

        Dim stpWatch1 As New Diagnostics.Stopwatch

        Dim customerList As New ArrayList

        Dim custX As New ArrayList

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

        '/*************LOAD FIRST ********************************/

        For i As Integer = 0 To 1000000

            customerList.Add(WriteToArrayListByValue(i, "Customer Name " & i.ToString, s, 1 * i, m, d))

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

        stpWatch1.Start()

        For i As Integer = 0 To customerList.Count - 1

            custX.Add(WriteToArrayListFromArrayList(CType(customerList(i), ArrayList)))

        Next

        stpWatch1.Start()

        lblArrayListReadMsg.Text = "Reading an ArrayList Object with " & customerList.Count.ToString & " Items. Total Duration : " & (stpWatch1.ElapsedMilliseconds / 1000).ToString & " Seconds."

 

    End Sub

    '/***********************Data Table Functionality ****************************************/

 

    Private Function CreateCustomerTable() As DataTable

        Dim dt As New DataTable("Customer")

        Dim dc1 As New DataColumn("CustId", GetType(Integer))

        Dim dc2 As New DataColumn("Name", GetType(String))

        Dim dc3 As New DataColumn("Sex", GetType(Boolean))

        Dim dc4 As New DataColumn("Salary", GetType(Decimal))

        Dim dc5 As New DataColumn("MaritalStatus", GetType(Char))

        Dim dc6 As New DataColumn("BirthDate", GetType(DateTime))

 

        dt.Columns.Add(dc1)

        dt.Columns.Add(dc2)

        dt.Columns.Add(dc3)

        dt.Columns.Add(dc4)

        dt.Columns.Add(dc5)

        dt.Columns.Add(dc6)

 

        Return dt

    End Function

    Private Sub WriteDataTableData()

        Dim stpWatch3 As New Diagnostics.Stopwatch

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

        stpWatch3.Start()

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

 

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

 

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch3.Stop()

        lblTableWriteMsg.Text = "Loading a Data Table Object with " & custTable.Rows.Count.ToString & " Items. Total Duration :" & (stpWatch3.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    Private Sub ReadFromCustomerTable()

        Dim stpWatch4 As New Diagnostics.Stopwatch

 

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

 

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

 

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

 

            If s Then s = False Else s = True

            If m = CChar("S") Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

 

        Dim custTableNew As New DataTable

        custTableNew = CreateCustomerTable()

        stpWatch4.Start()

        For i As Integer = 0 To custTable.Rows.Count - 1

            Dim dr As DataRow = custTableNew.NewRow

            dr("CustID") = CInt(custTable.Rows(i).Item(0))

            dr("Name") = custTable.Rows(i).Item(1).ToString

            dr("Sex") = CBool(custTable.Rows(i).Item(2))

            dr("Salary") = CDec(custTable.Rows(i).Item(3))

            dr("MaritalStatus") = CChar(custTable.Rows(i).Item(4))

            dr("BirthDate") = CDate(custTable.Rows(i).Item(5))

            custTableNew.Rows.Add(dr)

        Next

        stpWatch4.Stop()

        lblTableReadMsg.Text = "Reading a Data Table Object with " & custTableNew.Rows.Count.ToString & " Items. Total Duration :" & (stpWatch4.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    Private Sub ReadFromCustomerTableOptimization2()

        Dim stpWatch6 As New Diagnostics.Stopwatch

 

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

 

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

 

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

 

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

 

        Dim custTableNew As New DataTable

        custTableNew = CreateCustomerTable()

        stpWatch6.Start()

 

        If custTable.Rows.Count > 0 Then

            Dim custIDColumn As DataColumn = custTable.Columns("CustID")

            Dim custNameColumn As DataColumn = custTable.Columns("Name")

            Dim custSexColumn As DataColumn = custTable.Columns("Sex")

            Dim custSalaryColumn As DataColumn = custTable.Columns("Salary")

            Dim custMaritalStatusColumn As DataColumn = custTable.Columns("MaritalStatus")

            Dim custBirthDateColumn As DataColumn = custTable.Columns("BirthDate")

 

            For Each rowOrder As DataRow In custTable.Rows

                Dim dr As DataRow = custTableNew.NewRow

                dr("CustID") = CInt(rowOrder.Item(custIDColumn))

                dr("Name") = rowOrder.Item(custNameColumn).ToString

                dr("Sex") = CBool(rowOrder.Item(custSexColumn))

                dr("Salary") = CDec(rowOrder.Item(custSalaryColumn))

                dr("MaritalStatus") = CChar(rowOrder.Item(custMaritalStatusColumn))

                dr("BirthDate") = CDate(rowOrder.Item(custBirthDateColumn))

                custTableNew.Rows.Add(dr)

            Next

        End If

        stpWatch6.Stop()

        lblTableOpt2ReadMsg.Text = "Reading a Data Table Object 2nd Optimization with " & custTableNew.Rows.Count.ToString & " Items. Total Duration :" & (stpWatch6.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    Private Sub ReadFromCustomerTableOptimization1()

        Dim stpWatch5 As New Diagnostics.Stopwatch

 

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

 

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

 

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

 

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

 

        Dim custTableNew As New DataTable

        custTableNew = CreateCustomerTable()

        stpWatch5.Start()

 

        Using reader As DataTableReader = custTable.CreateDataReader

            Dim custIDOrdNo As Integer = reader.GetOrdinal("CustID")

            Dim custNameOrdNo As Integer = reader.GetOrdinal("Name")

            Dim custSex As Integer = reader.GetOrdinal("Sex")

            Dim custSalaryOrdNo As Integer = reader.GetOrdinal("Salary")

            Dim custMaritalStatusOrdNo As Integer = reader.GetOrdinal("MaritalStatus")

            Dim custBirthDateOrdNo As Integer = reader.GetOrdinal("BirthDate")

 

            Do While reader.Read

                Dim dr As DataRow = custTableNew.NewRow

                dr("CustID") = reader.GetInt32(custIDOrdNo)

                dr("Name") = reader.GetString(custNameOrdNo)

                dr("Sex") = reader.GetBoolean(custSex)

                dr("Salary") = reader.GetDecimal(custSalaryOrdNo)

                dr("MaritalStatus") = reader.GetChar(custMaritalStatusOrdNo)

                dr("BirthDate") = reader.GetDateTime(custBirthDateOrdNo)

                custTableNew.Rows.Add(dr)

            Loop

        End Using

 

        stpWatch5.Stop()

        lblTableOpt1ReadMsg.Text = "Reading a Data Table Object 1st Optimization with " & custTableNew.Rows.Count.ToString & " Items. Total Duration :" & (stpWatch5.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    '/***********Customer Object Data Funcationality ******************/

 

    Private Sub WriteCustomerObjectData()

        Dim stpWatch2 As New Diagnostics.Stopwatch

        Dim customerList As New ArrayList

        Dim customerObj As New List(Of SpeedTestCustomer)

 

        Dim s As Boolean = False

        Dim l As Decimal = 1

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

        stpWatch2.Start()

        For i As Integer = 0 To 1000000

            Dim x As New SpeedTestCustomer(i, "Customer Name " & i.ToString, s, 1 * i, m, d)

            customerObj.Add(x)

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch2.Stop()

        lblCustomerWriteMsg.Text = "Loading a Custom Business Object with " & customerObj.Count.ToString & " Items. Total Duration :" & (stpWatch2.ElapsedMilliseconds / 1000).ToString & " Seconds."

 

    End Sub

 

    Private Sub ReadCustomerObjectData()

        Dim stpWatch2 As New Diagnostics.Stopwatch

        Dim customerObj As New List(Of SpeedTestCustomer)

        Dim custY As New List(Of SpeedTestCustomer)

 

        Dim s As Boolean = False

        Dim l As Decimal = 1

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

        '/*************LOAD FIRST ********************************/

        For i As Integer = 0 To 1000000

            Dim x As New SpeedTestCustomer(i, "Customer Name " & i.ToString, s, 1 * i, m, d)

            customerObj.Add(x)

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

        '/**********************************READ NOW **********************/

        stpWatch2.Start()

        For i As Integer = 0 To customerObj.Count - 1

            custY.Add(customerObj(i))

        Next

        stpWatch2.Stop()

        lblCustomerReadMsg.Text = "Reading a Custom Business Object with " & customerObj.Count.ToString & " Items. Total Duration :" & (stpWatch2.ElapsedMilliseconds / 1000).ToString & " Seconds."

 

    End Sub

    Private Sub ReadXMLFromCustomerTable()

        Dim stpWatch9 As New Diagnostics.Stopwatch

        Dim custTable As New DataTable

        Dim custArrayList As New ArrayList

        custTable = CreateCustomerTable()

 

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

 

 

        For i As Integer = 0 To 100000

            Dim dr As DataRow = custTable.NewRow

 

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

 

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

 

        Dim strXML As String = ConvertDataTableToXML(custTable)

        stpWatch9.Start()

        Dim xmlDoc As Xml.XmlDocument = New Xml.XmlDocument()

        xmlDoc.LoadXml(strXML)

 

        For Each xmlNode As Xml.XmlNode In xmlDoc.SelectSingleNode("/Customer")

            Dim localArrayList As New ArrayList()

            localArrayList.Add(xmlNode.Item("CustId").InnerText)

            localArrayList.Add(xmlNode.Item("Name").InnerText)

            localArrayList.Add(xmlNode.Item("Sex").InnerText)

            localArrayList.Add(xmlNode.Item("Salary").InnerText)

            localArrayList.Add(xmlNode.Item("MaritalStatus").InnerText)

            localArrayList.Add(xmlNode.Item("BirthDate").InnerText)

            custArrayList.Add(localArrayList)

        Next

 

        stpWatch9.Stop()

       lblXMLReadMsg.Text = "Reading a XML Object into an ArrayList with " & custArrayList.Count.ToString & " Items. Total Duration :" & (stpWatch9.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

 

    Private Function ConvertDataTableToXML(ByVal dTable As DataTable) As String

        Dim dt As DataTable = dTable

        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder

        sb.Append(("<" _

                        + (dt.TableName + ">")))

        For Each row As DataRow In dt.Rows

            sb.Append("<Item>")

            Dim i As Integer = 0

            Do While (i < dt.Columns.Count)

                sb.Append(("<" _

                                + (dt.Columns(i).ColumnName + (">" _

                                + (row(i).ToString + ("</" _

                                + (dt.Columns(i).ColumnName + ">")))))))

                i = (i + 1)

            Loop

            sb.Append("</Item>")

        Next

        sb.Append(("</" _

                        + (dt.TableName + ">")))

        Return sb.ToString

    End Function

 

 

End Class

 

 

Public Class SpeedTestCustomer

 

    Private _custID As Int32

    Private _custName As String

    Private _custSex As Boolean

    Private _custSalary As Decimal

    Private _custMaritalStatus As Char

    Private _custDOB As DateTime

 

    Public Property CustID() As Int32

        Get

            Return _custID

        End Get

        Set(ByVal value As Int32)

            _custID = value

        End Set

    End Property

 

    Public Property Name() As String

        Get

            Return _custName

        End Get

        Set(ByVal value As String)

            _custName = value

        End Set

    End Property

 

    Public Property Sex() As Boolean

        Get

            Return _custSex

        End Get

        Set(ByVal value As Boolean)

            _custSex = value

        End Set

    End Property

 

    Public Property Salary() As Decimal

        Get

            Return _custSalary

        End Get

        Set(ByVal value As Decimal)

            _custSalary = value

        End Set

    End Property

 

    Public Property MaritalStatus() As Char

        Get

            Return _custMaritalStatus

        End Get

        Set(ByVal value As Char)

            _custMaritalStatus = value

        End Set

    End Property

 

    Public Property DateOfBirth() As DateTime

        Get

            Return _custDOB

        End Get

        Set(ByVal value As DateTime)

            _custDOB = value

        End Set

    End Property

 

#Region " Constructors"

    Public Sub New(ByVal custID As Int32, ByVal custName As String, ByVal custSex As Boolean, _

        ByVal custSalary As Decimal, ByVal custMaritalStatus As Char, ByVal custDOB As DateTime)

        Me.CustID = custID

        Me.Name = custName

        Me.Sex = custSex

        Me.Salary = custSalary

        Me.MaritalStatus = custMaritalStatus

        Me.DateOfBirth = custDOB

    End Sub

#End Region

End Class

 

 

Signature
Comments Comments (3)
Permalink | Post RSS

This article will first talk about top 5 best practices you should use while accessing database and then demonstrate how to properly use SqlConnection, SqlCommand and SqlDataAdapter objects while working with data using stored procedure or inline SQL for optimal performance. 
 

5 Best Practices

  1. Use the Appropriate Data-Access Object – Always use the DataReader's streaming data access for read-only data retrieval operations. Use the DataSet object for data update operations only if you need to perform the updates in disconnected mode. Use the DataView object when you want to work with filtered views of a larger DataSet object. The DataView object provides many of the benefits of the DataSet object, but without as much overhead.
  2. Use Stored Procedures, Not Embedded T-SQL: Stored procedures execute much faster than T-SQL statements because they are precompiled on the database server and are reusable. Not only does T-SQL execute more slowly than a stored procedure, but you also can introduce parsing errors into the T-SQL statement, which in turn generates runtime errors. Always execute stored procedures, and use input parameters (preferably with strong typing). Also, avoid using the * wildcard in SQL queries; always specify the exact fields you want to extract.
  3. Use SQL Data Types With SQL Server: The .NET Framework and SQL Server use different data types that do not always convert with each other. In addition, the SqlDataReader class provides typed access methods that map retrieved field values into the appropriate structure automatically. Always use typed access methods when retrieving SQL Server data to avoid type-conversion errors.
  4. Use Connection Pooling: Always use connection pooling. The SQL Server managed provider supports connection pooling by default, with little effort required on your part. The most work you must do is modifying the connection string to override default settings for connection pooling parameters.
  5. Use Centralized Data-Access Functions: Always centralize your data-access functions in a dedicated class file. This lets you maintain your database code in one central location, which makes it easier to write and maintain. A data-access class implements wrapper functions for basic data-access operations, including executing a stored procedure and returning either a DataReader, DataSet, XmlReader, or no return value at all. These functions should be using good programming sense such as proper connection handing, garbage collection, exception handling etc. You can also use Microsoft Enterprise Library, now available on http://www.codeplex.com/entlib and version 3.0 is just ready for release. For more in-depth understanding, you can read “Understanding and Optimizing ADO.NET Performance”  power point presentation by Gert E.R. Drapers, Software Architect at Microsoft Corporation.

Following is an example of Centralized Data Access Class. Connections, exceptions and garbage collection handling are being done through “Using” Statement. These methods will report any exceptions back to the caller, which can be presented in more elegant way in Business Layer or Presentation Layer.  .NET 2.0 framework introduced "Using" keyword in VB.NET. Behind the scenes, the Visual Basic complier translates a Using block into Try…Finally block, exactly as you do manually and any exceptions will be reported back to caller, and then disposes the object with End Using statement. So database connection will be automatically closed once the SqlConnection object gets disposed by End Using Statement, hence no need to explicitly close the connection as it is already done for you in orderly manner.

'--------- Class: DataAccess -----------------------------------------------------------

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
 
''' <summary>
''' DataAccess Class
''' </summary>
''' <remarks></remarks>
Public Class DataAccess
  
#Region "Constructor"
    Private Sub New()
 
    End Sub
#End Region
 
Private Shared instance As New DataAccess
    ''' <summary>
    ''' GetCustomerDataSet1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure
    ''' and sql parameters to Return a DataSet. This option is good, if you
    ''' are returning multiple datatable in dataset.
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <returns>Customer's DataSet with table name Customer</returns>
    ''' <remarks>when Fill method is called,SqlDataAdpater Opens
    ''' and Closes Connection internally, so no need to open connection explicitly.
    ''' You can open the connection explicitly if you will be using the same connection
    ''' for multiple DataTables on same SQLDataAdpater
    ''' </remarks>
    Public Shared Function GetCustomerDataSet1(ByVal custID As Int32) As DataSet
        Dim resultDataSet As New DataSet("Customers")
        Using connection As New SqlConnection(ConnectionString)
            Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
                With dataAdapter
                    .SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
                    .SelectCommand.CommandTimeout = 60 ' you can change based on the need
                    .SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Fill(resultDataSet, "Customer")
                End With
            End Using
        End Using
        Return resultDataSet
    End Function
 
    ''' <summary>
    ''' GetCustomerDataSet2 - Use of SqlDataAdpater, SqlConnection and SqlCommand along with Stored procedure
    ''' and sql parameters to Return a DataSet. This option is good, if you
    ''' are returning multiple datatable in a dataset.
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <returns>Customer's DataSet with table name Customer</returns>
    ''' <remarks>when Fill method is called,SqlDataAdpater Opens
    ''' and Closes Connection internally, so no need to open connection explicitly.
    ''' You can open the connection explicitly if you will be using the same connection
    ''' for multiple DataTables on same SQLDataAdpater
    ''' </remarks>
    Public Shared Function GetCustomerDataSet2(ByVal custID As Int32) As DataSet
        Dim dataAdapter As New SqlDataAdapter
        Dim resultDataSet As New DataSet("Customers")
 
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
                    .CommandTimeout = 60 ' you can change based on the need
                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    dataAdapter.SelectCommand = command
                    dataAdapter.Fill(resultDataSet, "Customer")
                End With
                dataAdapter = Nothing
            End Using
        End Using
        Return resultDataSet
    End Function
 
    ''' <summary>
    ''' GetCustomerDataTable1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure and sql parameters
    ''' to Return a DataTable,If you are expecting data from only one table, datatable is best option.
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <returns>Customer's DataTable with table name Customer</returns>
    ''' <remarks>SqlDataAdpater Opens and Closes Connection internally when Fill method is called
    ''' </remarks>
    Public Shared Function GetCustomerDataTable1(ByVal custID As Int32) As DataTable
        Dim resultDataTable As New DataTable("Customer")
        Using connection As New SqlConnection(ConnectionString)
            Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
                With dataAdapter
                    .SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
                    .SelectCommand.CommandTimeout = 60 ' you can change based on the need
                    .SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Fill(resultDataTable)
                End With
            End Using
        End Using
        Return resultDataTable
    End Function
 
    ''' <summary>
    ''' GetCustomerDataTable2 - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure
    ''' and sql parameters to Return a DataTable, If you are expecting data from only one
    ''' table, datatable is best option.
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <returns>Customer's DataTable with table name Customer</returns>
    ''' <remarks>SqlDataReader requires an open connection
    ''' </remarks>
    Public Shared Function GetCustomerDataTable2(ByVal custID As Int32) As DataTable
        Dim resultDataTable As New DataTable("Customer")
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60
                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Connection.Open()
                    Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)
                       resultDataTable.Load(reader)
                    End Using
                End With
            End Using
        End Using
        Return resultDataTable
    End Function
 
    ''' <summary>
    ''' GetCustomer - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure
    ''' with Sql Parameter to Return a Customer record. This examples reads the data in most efficient way
    ''' based on ordinal number rather column name. It also avoids boxing/unboxing which happends during
    ''' reading data on coulmn name and converting into proper datatype.
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <returns>Customer</returns>
    ''' <remarks>You should always read data based on ordinal number and avoid boxing/unboxing.</remarks>
    Public Shared Function GetCustomer(ByVal custID As Int32) As DTOCustomer
        Dim record As New DTOCustomer
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60 ' you can change based on the need
                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Connection.Open()
                End With
 
                Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                    Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
                    Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
                    Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
                    Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
                    Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
                    Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
 
                    If reader.Read Then
                        'Option -1 **** Based on Column ordinal number - More efficient code
                        record.CustID = reader.GetInt32(custIDOrdinal) ' Cust ID is Primary Key so it can not be NULL
                        If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
                        If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
                        If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
                        If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
                        If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
 
                        'Option -2 *****Based on column name - Less efficient code THUS COMMENTED
                        'record.CustID = CInt(reader.Item("Cust_ID"))
                        'record.CustName = reader.Item("Cust_Name").ToString
                        'If Not IsDBNull(reader.Item("Cust_DOB")) Then record.CustDOB = CDate(reader.Item("Cust_DOB"))
                        'record.CustAddress = reader.Item("Cust_Address").ToString
                        'If Not IsDBNull(reader.Item("Date_Created")) Then record.DateCreated = CDate(reader.Item("Date_Created"))
                        'If Not IsDBNull(reader.Item("Date_Modified")) Then record.DateModified = CDate(reader.Item("Date_Modified"))
                    End If
                End Using
            End Using
        End Using
        Return record
    End Function
 
    ''' <summary>
    ''' InsertTestData - Use of Inline SQL Statement with parameterized Query for Improved performance
    ''' and importantce of Prepare method of SqlCommand Object
    ''' </summary>
    ''' <param name="custName"></param>
    ''' <param name="custDOB"></param>
    ''' <param name="custAddress"></param>
    ''' <returns>No of rows affected</returns>
    ''' <remarks></remarks>
    Public Shared Function InsertTestData(ByVal custName As String, ByVal custDOB As DateTime, ByVal custAddress As String) As Int32
        Dim sqlString As String = "INSERT dbo.tbl_Customer(Cust_Name,Cust_DOB,Cust_Address) VALUES (@Name,@DOB,@Address)"
        Using conConnection As New SqlConnection(ConnectionString)
            Using cmdCommand As New SqlCommand(sqlString, conConnection)
                With cmdCommand
                    .CommandType = CommandType.Text
                    .CommandTimeout = 60 ' you can change based on the need
                    .Parameters.Add(New SqlParameter("@Name", SqlDbType.VarChar, 50)).Value = custName
                    .Parameters.Add(New SqlParameter("@DOB", SqlDbType.DateTime)).Value = custDOB
                    .Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 100)).Value = custAddress
                    .Connection.Open()
                    .Prepare() ' Important for the performance of inline SQL
                    Return .ExecuteNonQuery()
                End With
            End Using
        End Using
    End Function
 
    ''' <summary>
    ''' InsertCustomer - Inserts a new customer
    ''' </summary>
    ''' <param name="custName"></param>
    ''' <param name="custDOB"></param>
    ''' <param name="custAddress"></param>
    ''' <returns>Auto Generated Cust ID</returns>
    ''' <remarks>It also use the Return Value Paramater</remarks>
    Public Shared Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
                                          ByVal custAddress As String) As Int32
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60 ' The value you can change based on the need
                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
                    .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
                    .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
                    .Connection.Open()
                    .ExecuteNonQuery()
                    Return CInt(.Parameters("@Return").Value)
                End With
            End Using
        End Using
    End Function
 
    ''' <summary>
    ''' UpdateCustomer - Updates a Customer Record based on Cust ID
    ''' </summary>
    ''' <param name="custID"></param>
    ''' <param name="custName"></param>
    ''' <param name="custAddress"></param>
    ''' <returns>If Updated Successully then passed Cust ID else -1 if failed</returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateCustomer(ByVal custID As Int32, ByVal custName As String, ByVal custDOB As DateTime, _
                                          ByVal custAddress As String) As Int32
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60 ' The value you can change based on the need
                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
                    .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
                    .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
                    .Connection.Open()
                    .ExecuteNonQuery()
                    Return CInt(.Parameters("@Return").Value)
                End With
            End Using
        End Using
    End Function
 
    ''' <summary>
    ''' DeleteCustomer - Deletes a Customer record on Cust ID
    ''' </summary>
    ''' <param name="custID">integer</param>
    ''' <returns>If Deleted Successully then passed Cust ID else -1 if failed</returns>
    ''' <remarks></remarks>
    Public Shared Function DeleteCustomer(ByVal custID As Int32) As Int32
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)
                With command
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60 ' The value you can change based on the need
                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
                    .Connection.Open()
                    .ExecuteNonQuery()
                    Return CInt(.Parameters("@Return").Value) ' If fails then -1 else passed cust id
                End With
            End Using
        End Using
    End Function
 
    ' The following 5 methods you should use only in exceptional case
    ''' <summary>
    ''' GetResultAsDataReader- A generic method to return a SqlDataReader to calling method.
    ''' </summary>
    ''' <param name="sqlString"></param>
    ''' <returns>SqlDataReader</returns>
    ''' <remarks>It is very important to close SqlDataReader after its use in calling method</remarks>
    Public Shared Function GetResultAsDataReader(ByVal sqlString As String) As SqlDataReader
        Dim connection As New SqlConnection(ConnectionString)
        Using command As New SqlCommand(sqlString, connection)
            With command
                .CommandType = CommandType.Text
                .CommandTimeout = 60
                .Connection.Open()
                Return .ExecuteReader(CommandBehavior.CloseConnection)
            End With
        End Using
    End Function
 
    ''' <summary>
    ''' GetResultAsDataTable - A generic method to return a datatable on a SQL String
    ''' </summary>
    ''' <param name="sqlString"></param>
    ''' <returns>DataTable</returns>
    ''' <remarks>SQL Statement must be returning one result set.
    ''' Inline SQL Statements are slower then Stored Procedure
    ''' You should avoid using this unless you have exceptional case
    ''' </remarks>
    Public Shared Function GetResultAsDataTable(ByVal sqlString As String) As DataTable
        Dim resultTable As New DataTable
        Using connection As New SqlConnection(ConnectionString)
            Using command As New SqlCommand(sqlString, connection)
                With command
                    .CommandType = CommandType.Text
                    .CommandTimeout = 60
                    .Connection.Open()
                    Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)
                        If reader.HasRows Then resultTable.Load(reader)
                    End Using
                End With
            End Using
        End Using
        Return resultTable
    End Function
 
    ''' <summary>
    ''' GetResultAsDataSet - A Generic Method to return a dataset on a SQL String
    ''' </summary>
    ''' <param name="sqlString"></param>
    ''' <returns>DataSet</returns>
    ''' <remarks>Inline SQL Statements are slower then Stored Procedure
    ''' Inline SQL Statements are slower then Stored Procedure
    ''' You should avoid using this unless you have exceptional case
    ''' </remarks>
    Public Shared Function GetResultAsDataSet(ByVal sqlString As String) As DataSet
        Dim resultDataSet As New DataSet
        Using connection As New SqlConnection(ConnectionString)
            Using dataAdapter As New SqlDataAdapter(sqlString, connection)
                With dataAdapter
                    .SelectCommand.CommandType = CommandType.Text
                    .SelectCommand.CommandTimeout = 60
                    .Fill(resultDataSet)
                End With
                Return resultDataSet
            End Using
        End Using
    End Function
 
    ''' <summary>
    ''' RunSQLStatement - A generic method to execute a sql statement
    ''' </summary>
    ''' <param name="sqlString"></param>
    ''' <returns>No of rows affected</returns>
    ''' <remarks>Inline SQL Statements are slower then Stored Procedure
    ''' You should avoid using this unless you have exceptional case
    ''' </remarks>
    Public Shared Function RunSQLStatement(ByVal sqlString As String) As Int32
        Using connection As New SqlConnection(ConnectionString)
            Using dataAdapter As New SqlDataAdapter(sqlString, connection)
                With dataAdapter
                    .SelectCommand.CommandType = CommandType.Text
                    .SelectCommand.CommandTimeout = 60
                    .SelectCommand.Connection.Open()
                    Return (.SelectCommand.ExecuteNonQuery())
                End With
            End Using
        End Using
    End Function
 
    ''' <summary>
    ''' ConnectionString - A generic Property to get connection string
    ''' </summary>
    ''' <value>none</value>
    ''' <returns>connection string</returns>
    ''' <remarks>One place to make any change in connection string call</remarks>
    Public Shared ReadOnly Property ConnectionString() As String
        Get
            Try
                Return WebConfigurationManager.ConnectionStrings.Item("ExampleDB").ConnectionString
                'In ASP.NET Use ConfigurationManager instead of WebConfigurationManager
            Catch exc As Exception
                Return ""
            End Try
        End Get
    End Property
 
    ''' <summary>
    ''' CloseConnection- A generic method to close SqlConnection
    ''' </summary>
    ''' <param name="connection">SqlConnection</param>
    ''' <remarks></remarks>
    Public Shared Sub CloseConnection(ByRef connection As SqlConnection)
        If Not connection Is Nothing AndAlso connection.State = ConnectionState.Open Then connection.Close()
        connection = Nothing
    End Sub
 
    ''' <summary>
    ''' CloseDataReader - A generic method to close SqlDataReaer
    ''' </summary>
    ''' <param name="reader">SqlDataReaer</param>
    ''' <remarks></remarks>
    Public Shared Sub CloseDataReader(ByRef reader As SqlClient.SqlDataReader)
        If Not reader Is Nothing AndAlso Not reader.IsClosed Then reader.Close()
        reader = Nothing
    End Sub
 
End Class
 
'-----Class: DTOCustomer ------------------------------
Option Strict On
Option Explicit On
 
Public Class DTOCustomer
 
    Private _custID As Integer = 0
    Private _custName As String = String.Empty
    Private _custDOB As DateTime = DateTime.MinValue
    Private _custAddress As String = String.Empty
    Private _dateCreated As DateTime = DateTime.Now
    Private _dateModified As DateTime = DateTime.Now
 
#Region "Constructor"
    Public Sub New()
 
    End Sub
#End Region
 
#Region "Properties"
 
    Public Property CustID() As Integer
        Get
            Return _custID
        End Get
 
        Set(ByVal value As Int32)
            _custID = value
        End Set
    End Property
 
    Public Property CustName() As String
        Get
            Return _custName
        End Get
        Set(ByVal value As String)
            _custName = value
        End Set
    End Property
 
    Public Property CustDOB() As DateTime
        Get
            Return _custDOB
        End Get
        Set(ByVal value As DateTime)
            _custDOB = value
        End Set
    End Property
 
    Public Property CustAddress() As String
        Get
            Return _custAddress
        End Get
        Set(ByVal value As String)
            _custAddress = value
        End Set
    End Property
 
    Public Property DateCreated() As DateTime
        Get
            Return _dateCreated
        End Get
        Set(ByVal value As DateTime)
            _dateCreated = value
        End Set
    End Property
 
    Public Property DateModified() As DateTime
        Get
            Return _dateModified
        End Get
        Set(ByVal value As DateTime)
            _dateModified = value
        End Set
    End Property
 
#End Region
 
End Class
 
‘---Uses of Above Method and Class
 
Private Sub GetCustomerDataSet(ByVal custID As Int32)
        Dim dstRecord As New DataSet
        Try
            dstRecord = DataAccess.GetCustomerDataSet1(custID)
            'dstRecord = DataAccess.GetCustomerDataSet2(custID)
            Call ReadFromCustomerDataTable(dstRecord.Tables("Customer"))
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            dstRecord = Nothing
        End Try
    End Sub
 
    Private Sub GetCustomerDataTable(ByVal custID As Int32)
        Dim record As New DataTable
        Try
            record = DataAccess.GetCustomerDataTable1(custID)
            'record = DataAccess.GetCustomerDataTable2(custID)
            Call ReadFromCustomerDataTable(record)
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            record = Nothing
        End Try
    End Sub
 
    Private Sub GetCustomer(ByVal custID As Int32)
        Dim record As New DTOCustomer
        Try
            record = DataAccess.GetCustomer(custID)
            If Not record Is Nothing Then
                Me.txtCustName.Text = record.CustName
                Me.txtCustDOB.Text = record.CustDOB
                Me.txtCustAdd.Text = record.CustAddress
            Else
                Me.txtCustName.Text = ""
                Me.txtCustDOB.Text = ""
                Me.txtCustAdd.Text = ""
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            record = Nothing
        End Try
    End Sub
 
    Private Sub ReadCustomerFromDataReader(ByVal custID As Integer)
        Dim reader As SqlClient.SqlDataReader = Nothing
        Try
            reader = DataAccess.GetResultAsDataReader("dbo.Usp_GetCustomer " & custID.ToString)
            Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
            Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
            Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
 
            If reader.Read Then
                If Not reader.IsDBNull(custNameOrdinal) Then Me.txtCustName.Text = reader.GetString(custNameOrdinal)
                If Not reader.IsDBNull(custAddressOrdinal) Then Me.txtCustAdd.Text = reader.GetString(custAddressOrdinal)
                If Not reader.IsDBNull(custDOBOrdinal) Then Me.txtCustDOB.Text = reader.GetDateTime(custDOBOrdinal).ToShortDateString
            Else
                Me.txtCustName.Text = ""
                Me.txtCustDOB.Text = ""
                Me.txtCustAdd.Text = ""
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            DataAccess.CloseDataReader(reader)
        End Try
    End Sub
 
    ''' <summary>
    ''' Different options to read data from data table
    ''' </summary>
    ''' <param name="custDataTable"></param>
    ''' <remarks>If you have only one row, Option 1 is Ok</remarks>
    Private Sub ReadFromCustomerDataTable(ByVal custDataTable As DataTable)
        Me.txtCustName.Text = ""
        Me.txtCustDOB.Text = ""
        Me.txtCustAdd.Text = ""
 
        ''--Option 1 - Slowest -- but easy to code
        If custDataTable.Rows.Count > 0 Then
            If Not custDataTable.Rows(0).IsNull("Cust_Name") Then Me.txtCustName.Text = custDataTable.Rows(0).Item("Cust_Name").ToString
            If Not custDataTable.Rows(0).IsNull("Cust_DOB") Then Me.txtCustDOB.Text = CDate(custDataTable.Rows(0).Item("Cust_DOB")).ToShortDateString
            If Not custDataTable.Rows(0).IsNull("Cust_Address") Then Me.txtCustAdd.Text = custDataTable.Rows(0).Item("Cust_Address").ToString
 
        End If
 
        '--Option 2 - Good --
        'If custDataTable.Rows.Count > 0 Then
        '    Dim custNameDataColumn As DataColumn = custDataTable.Columns("Cust_Name")
        '    Dim custDOBDataColumn As DataColumn = custDataTable.Columns("Cust_DOB")
        '    Dim custAddDataColumn As DataColumn = custDataTable.Columns("Cust_Address")
        '    For Each rowOrder As DataRow In custDataTable.Rows
        '        If Not rowOrder.IsNull(custNameDataColumn) Then Me.txtCustName.Text = rowOrder.Item(custNameDataColumn).ToString
        '        If Not rowOrder.IsNull(custDOBDataColumn) Then Me.txtCustDOB.Text = CDate(rowOrder.Item(custDOBDataColumn)).ToShortDateString
        '        If Not rowOrder.IsNull(custAddDataColumn) Then Me.txtCustAdd.Text = rowOrder.Item(custAddDataColumn).ToString
        '    Next
        'End If
 
        '--Option 3 - Best
        'Using reader As DataTableReader = custDataTable.CreateDataReader
        '    Dim custNameOrdNo As Integer = reader.GetOrdinal("Cust_Name")
        '    Dim custDOBOrdNo As Integer = reader.GetOrdinal("Cust_DOB")
        '    Dim custAddOrdNo As Integer = reader.GetOrdinal("Cust_Address")
        '    If reader.Read Then
        '        If Not reader.IsDBNull(custNameOrdNo) Then Me.txtCustName.Text = reader.GetString(custNameOrdNo)
        '        If Not reader.IsDBNull(custDOBOrdNo) Then Me.txtCustDOB.Text = reader.GetDateTime(custDOBOrdNo).ToShortDateString
        '        If Not reader.IsDBNull(custAddOrdNo) Then Me.txtCustAdd.Text = reader.GetString(custAddOrdNo)
        '    End If
        'End Using
    End Sub
Signature
Comments Comments (2)
Permalink | Post RSS

In this example, a Gridview is generated at run time with customer list with bound field columns. It also creates a HTML table dynamically. Most of the properties can be easily controlled at run time. I am using Customer Business Object which I created for my earlier posts.

  Page: CustomerDynamicGridView.Aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CustomerDynamicGridView.aspx.vb" Inherits="CustomerDynamicGridView" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Dynamic Grid View</title>
</head>
<body>
    <form id="frmCustomer" runat="server">
    <div style="text-align:center">
        <asp:PlaceHolder ID="plhCustomer" runat="server">
        </asp:PlaceHolder>    
    </div>   
    </form>
</body>
</html>

 

Option Explicit On

Option Strict On

 

Imports Vishwa.Example.Business

 

''' <summary>

''' Author : Vishwa@VishwaMohan.com

''' Date : 10/14/2007

''' Class: CustomerDynamicGridView

''' Purpose : To Generate a Dynamic Gridview with Custom Bound Column

''' </summary>

''' <remarks></remarks>

'''

 

Partial Class CustomerDynamicGridView

    Inherits System.Web.UI.Page

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then Call PopulateDynamicGrid()

    End Sub

 

    Private Sub PopulateDynamicGrid()

        Dim gvwGridView As New GridView

        Dim tblMain As New HtmlTable

        Dim trRow As New HtmlTableRow

        Dim tdCell As New HtmlTableCell

        Dim lblTitleText As New Label

        Dim bizCust As New BIZCustomer

 

        lblTitleText.Text = "Customer List"

        lblTitleText.Font.Bold = True

        lblTitleText.BackColor = Drawing.Color.Navy

        lblTitleText.ForeColor = Drawing.Color.White

 

        tdCell.Controls.Add(lblTitleText)

        tdCell.Height = "30px"

        trRow.Cells.Add(tdCell)

        tblMain.Rows.Add(trRow)

 

        With gvwGridView

            .HorizontalAlign = HorizontalAlign.Left

            .BackColor = Drawing.Color.FromName("#ccccff")

            .BorderColor = Drawing.Color.Black

            .CellPadding = 3

            .CellSpacing = 0

            .Font.Name = "Verdana"

            .Font.Size = 8

            .HeaderStyle.Font.Size = 10

            .HeaderStyle.BackColor = Drawing.Color.FromName("#aaaadd")

            .HeaderStyle.VerticalAlign = VerticalAlign.Bottom

            .AlternatingRowStyle.BackColor = Drawing.Color.White

            .HeaderStyle.Wrap = False

            .AutoGenerateColumns = False

            .EmptyDataText = "No Record Found."

            CustomizeThisGrid(gvwGridView)

            .DataSource = bizCust.GetCustomers()

            .DataBind()

        End With

 

        trRow = New HtmlTableRow

        tdCell = New HtmlTableCell

        tdCell.Align = "Left"

        tdCell.Controls.Add(gvwGridView)

        trRow.Cells.Add(tdCell)

        tblMain.Rows.Add(trRow)

        plhCustomer.Controls.Add(tblMain)

 

    End Sub

 

    Private Sub CustomizeThisGrid(ByRef myGridView As GridView)

        myGridView.Columns.Clear()

        Dim col0 As BoundField = New BoundField()

        Dim col1 As BoundField = New BoundField()

        Dim col2 As BoundField = New BoundField()

 

        col0.ItemStyle.HorizontalAlign = HorizontalAlign.Left

        col0.HeaderStyle.HorizontalAlign = HorizontalAlign.Left

        col0.HeaderText = "ID#"

        col0.DataField = "CustID"

        myGridView.Columns.Add(col0)

 

        col1.ItemStyle.HorizontalAlign = HorizontalAlign.Left

        col1.HeaderStyle.HorizontalAlign = HorizontalAlign.Left

        col1.HeaderText = "Name"

        col1.DataField = "CustName"

        myGridView.Columns.Add(col1)

 

        col2.ItemStyle.HorizontalAlign = HorizontalAlign.Left

        col2.HeaderStyle.HorizontalAlign = HorizontalAlign.Left

        col2.HeaderText = "Address"

        col2.DataField = "CustAddress"

 

        myGridView.Columns.Add(col2)

 

    End Sub

 End Class

 

 

Signature
Comments Comments (10)
Permalink | Post RSS

The following example will demonstrate as how to manage registered users, activate or deactivate them assign or remove role(s) in ASP.Net. My assumption is, you already have a page which allows users to register and now you would like to activate the user and assign a role so that they can login to your web site. I have created two page, which resides in Admin area of web site. You will be required to make some changes such as Master Page File, Namespace, Base Page and Content Place Holder ID etc. in order to properly compile and work with you project. The first page will allow you to search and find one or more registered users and second page will allow you to activate and assign a role.
 

Note: My User Profile contains additional fields such as First Name, Last Name, Address and Phone Number, if you are not using in your profile, you can remove it or change it as per your user profile.

 

Users.Aspx

 

<%@ Page Language="VB" MasterPageFile="~/WebSite.master"

    AutoEventWireup="false" CodeFile="Users.aspx.vb" Inherits="Admin_Users"

    title="VishwaMohan.Com | Admin | Manage Users" %>

 

<asp:Content ID="conContentManage" ContentPlaceHolderID="cphMainContent" Runat="Server">

     <div style="text-align:center">

     <table cellpadding="2" cellspacing="0" border="0" width="100%" style="text-align:center">

        <tr>

            <td id="content" width="100%" valign="top" height="100%" class="darkgray_row" style="text-align:center">      

                <table cellpadding="0" cellspacing="0" border="0" width="100%" style="text-align:center" >

                    <tr>

                        <td valign="top" style="text-align:center"><h3 class="lighgray_row" style="text-align:center">Account Management</h3>

                            <br />

                            <b>- Total registered users: <asp:Literal runat="server" ID="lblTotUsers" /><br />

                            - Users online now: <asp:Literal runat="server" ID="lblOnlineUsers" /></b>

                            <p>

                                Click one of the following link to display all users whose name begins with that letter:

                            </p>

                        </td>

                    </tr>

            <tr>

                <td style="text-align:center">

                   <asp:Repeater runat="server" ID="rptAlphabet" OnItemCommand="rptAlphabet_ItemCommand">

                      <ItemTemplate><asp:LinkButton ID="lnbLinkButton" runat="server" Text='<%# Container.DataItem %>'

                         CommandArgument='<%# Container.DataItem %>' />&nbsp;&nbsp;

                      </ItemTemplate>

                   </asp:Repeater>

                </td>

           </tr>

           <tr>

                <td style="text-align:center">

                    <br />

                    Otherwise use the controls below to search users by partial username or e-mail:

                    <br />

                </td>

            </tr>       

            <tr>

            <td height="50px;" style="text-align:center">

                <asp:DropDownList runat="server" ID="ddlSearchTypes">

                  <asp:ListItem Text="UserName" Selected="true" />

                  <asp:ListItem Text="E-mail" />

               </asp:DropDownList>

               Contains

               <asp:TextBox runat="server" ID="txtSearchText" />

               <asp:Button runat="server" ID="btnSearch" Text="Search" CssClass="button" OnClick="btnSearch_Click" />

                <br />

            </td>

            </tr>

            <tr>

                <td style="text-align:center">

               <asp:GridView ID="gvwUsers" runat="server" AutoGenerateColumns="false" DataKeyNames="UserName"

                  OnRowCreated="gvwUsers_RowCreated" Width="100%" PagerSettings-Mode="NumericFirstLast"

                  PageSize="10" >

                  <Columns>    

                     <asp:BoundField HeaderText="UserName" DataField="UserName" />

                     <asp:HyperLinkField HeaderText="E-mail" DataTextField="Email" DataNavigateUrlFormatString="mailto:{0}" DataNavigateUrlFields="Email" />

                     <asp:BoundField HeaderText="Created" DataField="CreationDate" DataFormatString="{0:MM/dd/yy h:mm tt}" />

                     <asp:BoundField HeaderText="Last activity" DataField="LastActivityDate" DataFormatString="{0:MM/dd/yy h:mm tt}" />

                     <asp:CheckBoxField HeaderText="Appr." DataField="IsApproved" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" />

                     <asp:HyperLinkField Text="<img src='../images/edit.gif' border='0' />" DataNavigateUrlFormatString="EditUser.aspx?UserName={0}" DataNavigateUrlFields="UserName" />

                     <asp:ButtonField CommandName="Delete" ButtonType="Image" ImageUrl="~/images/delete.gif" />

                  </Columns>

                  <EmptyDataTemplate><b>No users found for the specified criteria</b></EmptyDataTemplate>

               </asp:GridView>            

                </td>

           </tr>

        </table>

        </td>

      </tr>       

     </table>

 </div>

 </asp:Content>

 

Users.Aspx.vb

Option Explicit On

Option Strict On

Partial Class Admin_Users

    Inherits BasePage

    Private allUsers As MembershipUserCollection = Membership.GetAllUsers

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Me.IsPostBack Then

            lblTotUsers.Text = allUsers.Count.ToString

            lblOnlineUsers.Text = Membership.GetNumberOfUsersOnline.ToString

            Dim alphabet As String() = _

                "A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;All".Split(CChar(";"))

            rptAlphabet.DataSource = alphabet

            rptAlphabet.DataBind()

        End If

    End Sub

 

    Private Sub BindUsers(ByVal reloadAllUsers As Boolean)

        If reloadAllUsers Then

            allUsers = Membership.GetAllUsers

        End If

 

        Dim users As MembershipUserCollection = Nothing

 

        Dim searchText As String = ""

        If Not String.IsNullOrEmpty(gvwUsers.Attributes("SearchText")) Then

            searchText = gvwUsers.Attributes("SearchText")

        End If

 

        Dim searchByEmail As Boolean = False

        If Not String.IsNullOrEmpty(gvwUsers.Attributes("SearchByEmail")) Then

            searchByEmail = Boolean.Parse(gvwUsers.Attributes("SearchByEmail"))

        End If

 

        If searchText.Length > 0 Then

            If searchByEmail Then

                users = Membership.FindUsersByEmail(searchText)

            Else

                users = Membership.FindUsersByName(searchText)

            End If

        Else

            users = allUsers

        End If

 

        gvwUsers.DataSource = users

        gvwUsers.DataBind()

    End Sub

 

    Protected Sub rptAlphabet_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles rptAlphabet.ItemCommand

        gvwUsers.Attributes.Add("SearchByEmail", Boolean.FalseString)

 

        If e.CommandArgument.ToString.Length = 1 Then

            gvwUsers.Attributes.Add("SearchText", e.CommandArgument.ToString + "%")

            BindUsers(False)

        Else

            gvwUsers.Attributes.Add("SearchText", "")

            BindUsers(False)

        End If

    End Sub

 

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim searchByEmail As Boolean = (ddlSearchTypes.SelectedValue = "E-mail")

        gvwUsers.Attributes.Add("SearchText", "%" + txtSearchText.Text + "%")

        gvwUsers.Attributes.Add("SearchByEmail", searchByEmail.ToString)

        BindUsers(False)

    End Sub

 

    Protected Sub gvwUsers_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvwUsers.RowCreated

        If e.Row.RowType = DataControlRowType.DataRow Then

            Dim btn As ImageButton = CType(e.Row.Cells(6).Controls(0), ImageButton)

            btn.OnClientClick = "if (confirm('Are you sure you want to delete this user account?') == false) return false;"

        End If

    End Sub

    Protected Sub gvwUsers_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvwUsers.RowDeleting

        Try

            Dim userName As String = gvwUsers.DataKeys(e.RowIndex).Value.ToString

            ProfileManager.DeleteProfile(userName)

            Membership.DeleteUser(userName)

            BindUsers(True)

            lblTotUsers.Text = allUsers.Count.ToString

        Catch exc As Exception

            lblOnlineUsers.Text = exc.Message

        End Try

    End Sub

End Class

EditUser.Aspx

<%@ Page Language="VB" MasterPageFile="~/WebSite.master"

    AutoEventWireup="false" CodeFile="EditUser.aspx.vb" Inherits="Admin_EditUser"

    title="VishwaMohan.Com | Admin | Edit Users" %>

 

<asp:Content ID="conContentEditUser" ContentPlaceHolderID="cphMainContent" Runat="Server">

      <div style="text-align:center">

        <table cellpadding="2" cellspacing="0" border="0" width="100%" style="text-align:center">

        <tr>           

            <td id="content" width="100%" valign="top" height="100%" class="darkgray_row" align="left">      

                <table cellpadding="0" cellspacing="0" border="0" width="100%" style="text-align:center" >

                    <tr>

                        <td valign="top" colspan="2" style="text-align:center">

                                <h3 class="lighgray_row" style="text-align:center">Edit User Account</h3>

                        </td>

                     </tr>

                     <tr>         

                        <td width="200px" nowrap></td>             

                        <td>                      

                            <table cellpadding="2" border="0" width="100%" visible=false>

                            <tr>

                                <td >User Name:</td>

                                <td >

                                    <asp:Literal ID="lblUserName" runat="server"></asp:Literal></td>

                            </tr>

                            <tr>

                                <td >

                                    First Name:</td>

                                <td>

                                    <asp:Label ID="lblFirstName" runat="server"/></td>

                            </tr>

                            <tr>

                                <td >

                                    Last Name:</td>

                                <td>

                                    <asp:Label ID="lblLastName" runat="server"/></td>

                            </tr>

                            <tr>

                                <td colspan="2"><hr /></td>

                            </tr>

                            <tr>

                                <td >

                                    E-Mail:</td>

                                <td>

                                    <asp:HyperLink ID="lnkEmail" runat="server">[lnkEmail]</asp:HyperLink></td>

                            </tr>

                            <tr>

                                <td >

                                   Address:</td>

                                <td>

                                    <asp:Label ID="lblAddress" runat="server"/></td>

                            </tr>

                            <tr>

                                <td >

                                    Phone:</td>

                                <td>

                                    <asp:Label ID="lblPhone" runat="server"/></td>

                            </tr>

                            <tr>

                                <td colspan="2"><hr /></td>

                            </tr>

                            <tr>

                                <td >

                                    Registered:</td>

                                <td>

                                    <asp:Literal ID="lblRegistered" runat="server"></asp:Literal></td>

                            </tr>

                            <tr>

                                <td >

                                    Last Login:</td>

                                <td>

                                    <asp:Literal ID="lblLastLogin" runat="server"></asp:Literal></td>

                            </tr>

                            <tr>

                                <td >

                                    Last Activity</td>

                                <td>

                                    <asp:Literal ID="lblLastActivity" runat="server"></asp:Literal></td>

                            </tr>

                             <tr>

                                <td colspan="2"><hr /></td>

                            </tr>

                            <tr>

                                <td >

                                    Online Now:</td>

                                <td>

                                    <asp:CheckBox ID="chkOnlineNow" runat="server" Enabled="False" /></td>

                            </tr>

                            <tr>

                                <td >

                                    Approved:</td>

                                <td>

                                    <asp:CheckBox ID="chkApproved" runat="server" AutoPostBack="True" />&nbsp;&nbsp; If approved, make sure a role is assigned.</td>

                            </tr>

                            <tr>

                                <td >

                                    Locked Out:</td>

                                <td>

                                    <asp:CheckBox ID="chkLockedOut" runat="server" AutoPostBack="True" /></td>

                            </tr>

                        </table>

                     </td>

                </tr>

                <tr>

                    <td colspan="2" style="text-align:center">

                        <h4 class="lighgray_row" style="text-align:center">Edit user's roles</h4>

                        <br />  

                        <asp:CheckBoxList ID="chklRoles" runat="server" CellSpacing="4" RepeatColumns="5"/>

                    </td>

                 <tr>

                 <td class="sidebar"></td>                

                 <td>

                    <table cellpadding="2" width="100%" border="0">

                        <tr>

                            <td align="right">

                                <asp:Label ID="lblRolesFeedback" runat="server" Text="Roles updated successfully"

                                    Visible="False"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;

                                <asp:Button ID="btnUpdateRoles" runat="server" Text="Update" CssClass="button"/></td>

                        </tr>

                        <tr>

                            <td align="right">

                                Create new role:&nbsp;<asp:TextBox ID="txtNewRole" runat="server"></asp:TextBox>

                                <asp:RequiredFieldValidator ID="rfvRequireNewRole" runat="server" ControlToValidate="txtNewRole"

                                    ErrorMessage="Role name is required." SetFocusOnError="True" ValidationGroup="CreateRole"></asp:RequiredFieldValidator>

                                <asp:Button ID="btnCreateRole" runat="server" Text="Create" ValidationGroup="CreateRole" CssClass="button" /></td>

                        </tr>

                    </table>

                  </td>

                  </tr>

                  </table>  

            </td>

          </tr>           

        </table>

</div>

 

 

</asp:Content>

 

 

 

EditUser.Aspx.vb

Option Explicit On

Option Strict On

Imports System.Collections

Imports System.Collections.Generic

 

 

Partial Class Admin_EditUser

    Inherits BasePage

    Dim userName As String = ""

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Try

            userName = Me.Request.QueryString("UserName")

            lblRolesFeedback.Visible = False

 

            If Not Me.IsPostBack Then

                ' show the user's details

                If userName.Length > 0 Then

                    Dim user As MembershipUser = Membership.GetUser(userName)

                    Me.lblUserName.Text = user.UserName

                    Me.lnkEmail.Text = user.Email

                    Me.lnkEmail.NavigateUrl = "mailto:" & user.Email

                    Me.lblRegistered.Text = user.CreationDate.ToString("f")

                    Me.lblLastLogin.Text = user.LastLoginDate.ToString("f")

                    Me.lblLastActivity.Text = user.LastActivityDate.ToString("f")

                    Me.chkOnlineNow.Checked = user.IsOnline

                    Me.chkApproved.Checked = user.IsApproved

                    Me.chkLockedOut.Checked = user.IsLockedOut

                    Me.chkLockedOut.Enabled = user.IsLockedOut

 

                    Dim userProfile As ProfileCommon = Me.Profile

                    userProfile = Me.Profile.GetProfile(userName)

                    Me.lblFirstName.Text = userProfile.FirstName

                    Me.lblLastName.Text = userProfile.LastName

                    Me.lblAddress.Text = userProfile.Address

                    Me.lblPhone.Text = userProfile.Phone

 

                    BindRoles()

                End If

            End If

        Catch exc As Exception

            ' Do nothing

        Finally

        End Try

    End Sub

 

    Private Sub BindRoles()

        Me.chklRoles.DataSource = Roles.GetAllRoles

        Me.chklRoles.DataBind()

        For Each role As String In Roles.GetRolesForUser(userName)

            Me.chklRoles.Items.FindByText(role).Selected = True

        Next

    End Sub

    Protected Sub chkApproved_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkApproved.CheckedChanged

        Try

           

            Dim user As MembershipUser = Membership.GetUser(userName)

            Dim userEmail As String = user.Email.ToString()

            user.IsApproved = chkApproved.Checked

            Membership.UpdateUser(user)

            If chkApproved.Checked Then

                Dim emailMsg As New System.Net.Mail.MailMessage

                Dim smtpClient As New System.Net.Mail.SmtpClient()

 

                emailMsg.From = New System.Net.Mail.MailAddress(ConfigurationManager.AppSettings.Item("AdminUserEmail").ToString())

                emailMsg.Subject = "Your Account has been Approved."

                emailMsg.Body = "Hello " & userName & vbCrLf & Space(15) & "Your Account has been Approved." & vbCrLf & vbCrLf & "See you online!" & vbCrLf & "- Vishwa Mohan"

                emailMsg.To.Add(userEmail)

                smtpClient.Send(emailMsg)

 

            End If

            Me.lblRolesFeedback.Text = "Approval status updated successfully."

        Catch exc As Exception

            Me.lblRolesFeedback.Text = exc.Message

        Finally

            Me.lblRolesFeedback.Visible = True

        End Try

    End Sub

 

    Protected Sub chkLockedOut_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkLockedOut.CheckedChanged

        If Not chkLockedOut.Checked Then

            Dim user As MembershipUser = Membership.GetUser(userName)

            user.UnlockUser()

            Me.chkLockedOut.Enabled = False

        End If

    End Sub

 

    Protected Sub btnUpdateRoles_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateRoles.Click

        ' first remove the user from all roles...

        Dim currRoles() As String = Roles.GetRolesForUser(userName)

        If currRoles.Length > 0 Then

            Roles.RemoveUserFromRoles(userName, currRoles)

            Me.lblRolesFeedback.Text = "Role removed from the User."

        End If

 

        ' and then add the user to the selected roles

        Dim newRoles As New List(Of String)

        For Each item As ListItem In chklRoles.Items

            If item.Selected Then

                newRoles.Add(item.Text)

                Me.lblRolesFeedback.Text = "New Role Added to the User."

            End If

        Next

        Dim userNames() As String = {userName}

        Roles.AddUsersToRoles(userNames, newRoles.ToArray)

        Me.lblRolesFeedback.Visible = True

    End Sub

 

    Protected Sub btnCreateRole_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreateRole.Click

        If Not Roles.RoleExists(txtNewRole.Text.Trim) Then

            Roles.CreateRole(txtNewRole.Text.Trim)

            BindRoles()

        End If

    End Sub

End Class

 

Users.Aspx Page at Run Time

 

Signature
Comments Comments (10)
Permalink | Post RSS

About Me

Me Hello,my name is Vishwa Mohan Kumar.
I am a Software Architect. This blog is result of my experiments.

Flickr Photos

Calendar

<<  September 2010  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

Recent Comments

Comment RSS

Live Traffic Feed