Data Adapters

[JoeZim]

I've setup my application where I have a separate System.Data.SqlClient.SqlDataAdapter for each SQL Server Stored Procedure, all filling datasets at various points throughout the application. At the moment, I have 29 Adapters and one SQLConnection, all in Global.asax.

In the Global.asax.vb, I have the following:

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        Application("conn") = Me.SqlConnection1.ConnectionString
End Sub

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        Session("GroupCreditList") = Me.GroupCreditList.SelectCommand
        Session("GroupCreditData") = Me.GroupCreditData.SelectCommand
        Session("CustomerData") = Me.CustomerData.SelectCommand
        Session("CustomerList") = Me.CustomerList.SelectCommand
        Session("FacilityData") = Me.FacilityData.SelectCommand
        Session("FacilityType") = Me.FacilityType.SelectCommand
        Session("RegionDesc") = Me.RegionDesc.SelectCommand
        Session("SegmentDesc") = Me.SegmentDesc.SelectCommand
        Session("Booleans") = Me.Booleans.SelectCommand
        Session("CardType") = Me.CardType.SelectCommand
        Session("CollatData") = Me.CollatData.SelectCommand
        Session("CollateralType") = Me.CollateralType.SelectCommand
        Session("CollatCheck") = Me.CollatCheck.SelectCommand
        Session("CollatMaxCust") = Me.CollatMaxCust.SelectCommand
        Session("PreparerList") = Me.PreparerList.SelectCommand
        Session("RMList") = Me.RMList.SelectCommand
        Session("GridCollateral") = Me.GridCollateral.SelectCommand
        Session("GridFacility") = Me.GridFacility.SelectCommand
        Session("Leq") = Me.Leq.SelectCommand
        Session("GuarClass") = Me.GuarClass.SelectCommand
        Session("NonGovGuarType") = Me.NonGovGuarType.SelectCommand
        Session("GovGuarType") = Me.GovGuarType.SelectCommand
        Session("Percent") = Me.Percent.SelectCommand
        Session("GuarData") = Me.GuarData.SelectCommand
        Session("GuarFacName") = Me.GuarFacName.SelectCommand
        Session("GuarCustName") = Me.GuarCustName.SelectCommand
        Session("GridChecked") = Me.GridChecked.SelectCommand
        Session("gcMax") = Me.gcMax.SelectCommand
        Session("LoginList") = Me.LoginList.SelectCommand
End Sub

Everything seems to work fine, but I am still in development and have not tested this much for more than a couple sessions hitting the app at one point in time.

If all goes well, this could have the potential for about 200-300 users, so I was hoping to get some feedback on how I've got this setup. Is creating so many DataAdapters and session variables going to cause problems as the number of users increases?

I'm new to this type of design and application development, so any comments/suggestions would be appreciated.

Thanks!

 

[BoulderBum]

I'd say the approach lacks transparency/maintainability and is a bit inefficient. For 200-300 users, it won't overwhelm the server, but nevertheless, I wouldn't recommend your implementation.

A better approach would be to create a data access layer (DAL) and have any code that needs the data call your DAL. It would not only make the code leaner and easier to understand, but be a lot more flexible.

For instance, whereas you now have commands you probably end up calling from client code using your session variables:

[b]'In Page[/b]
Dim con AS SqlConnection = new SqlConnection( Application("conn") )

Dim cmd AS SqlCommand = Session("FacilityData")
cmd.Connection = con

Dim dataSet AS DataSet = new DataSet()
Dim apapter AS SqlDataAdpater( cmd )

con.Open()

apadter.Fill( dataSet )

con.Close()

'etc.

...you can slash the lines of code required, reduce the consumption of server resources and make the system more maintainable by creating a class to deliver data to the application.

For instance, if we have a class "Facility" that exposes a method "GetData" then the entirity of your client code would look like this instead the multiple line of code above:

[b]'In Page[/b]
DataSet facilityData = Facility.GetData()

...and since you're sheltered from implementation details with such an approach (the complexity is hidden behind a function call), your system is in a lot better shape if you, say, change your RDBMS or decide to use data caching to improve performance.

This is because changes to the implementation of the system only require changing the code in Facility.GetData() instead of in the numerous places you'd hand-code data access code otherwise.

 

[JoeZim]

Thanks for your suggestion BoulderBum, I had a feeling this was not very efficient, from a maintenance standpoint. You are correct, I have a the following in my pages, all over the place:

ds = New DataSet
Dim GroupCreditData As New System.Data.SqlClient.SqlDataAdapter(Session("GroupCreditData"))
GroupCreditData.SelectCommand.Parameters(1).Value = Session.Item("GroupName")
GroupCreditData.Fill(ds, "GroupCreditData")
Dim myView As DataView = ds.Tables("GroupCreditData").DefaultView

I also have adapters populating DropDownLists inside datagrids, which then have edit/update/delete/insert capabilities. Basically, are you saying these should all be moved outside the page code-behind into a separate class file, where the pages call and get data from one central location?

In this scenario, when a user opens the application to begin updating, this separate DAL would simply pull from the database upon request (from a given page), and could also feed/update the database, when requested. I will have to start reading up on the DAL concept. Hopefully it will not be too difficult to setup with my current design.

Thanks for you thoughts......

 

[IT4EVR]

From a performance standpoint, I think you are much better off opening datareaders from a command object, getting your data and closing.

Opening up that many data adapters and putting them into session variables is going to consume a lot of unnecessary memory.

Is there a reason you are opening up so many result sets at one time?

 

[BoulderBum]

are you saying these should all be moved outside the page code-behind into a separate class file, where the pages call and get data from one central location?

Yeah, that will make things a bit more manageable.

There are several different strategies you can employ to create a good DAL. You can use CodeGen to write the code for you (CodeSmith, for example), use the Data Access Application Block from Microsoft, use an O/R mapper or even home-cook a solution that will precisely meet your needs. Whatever you do it's always good to abstract the data access logic outside of the actual pages.