I like how this is split up, and I am currently trying to use it in a new web app that I am developing. But can someone please shed some more light on how this works.

So far everytime I need to return a datareader ect.. I need to create a xxxdetails.vb file. These files are just objects of what your bringing back from your stored procedure.

Is there a way I can use the BLL with out having to create a xxxDetails file everytime? There are times when I just want to return a datareader, but it's always closed unless I create 3 new files. Example the USER.VB, the UserDetails.vb file and then modify my USERSProvider and sqlclientprovider classes.

I hope I explained this well enough, and I hope someone can tell me if I should go on like this or change direction on how I use my DAL.

Thanks for any help, guidance or suggestions,
KSS

I know exactly what you are trying to do. You are trying to pass a DataReader up to the BLL, but when you go to read it, it’s closed.

This is because a DataReader can't actually "hold" anything. It's just a reader, and therefore requires an open connection. Once the connection is closed, the DataReader can’t read any data. You need to pass some sort of local in-memory object, like a DataTable or a DataSet. Then in the BLL, you’d iterate the rows and create your List(Of WhatEver).

Here would be an example of doing it with articles. (I’m eliminating the paging and caching code for simplicity):

In SqlArticlesProvider.vb
In Article.vb
_________________________________

Visit my blog at http://leedumond.com

Is there anyway I can get rid of the List(Of Article)?
If not does this need to be a separate .vb file?

It just seems that I'm having to do alot of work, everytime I need to return data. Everytime I want to return a new set of data or slightly modified I need a new BLL file, a new xxxDetails file and all that code that loops through the List(Of xxx) object.

The way the app is constructed, it's a three-layer application -- data, business, and presentation. In this example, Article is the business object that an ObjectDataSource in a page would bind to. So, if you want to display a list of articles (in a GridView for example), you would specify as a Select method for the ObjectDataSource a method in the business layer that returns a List(Of Article).

So your question is, can you get rid of the List(Of Article) and the accompanying Article class? Absolutely. It all depends on how simple you want to make it.

You could collapse the architecture into two tiers and just pass a DataReader directly to the page from the DAL, bypassing the BLL altogether. This would mean you wouldn't need the separate .vb class or to create a List(Of Article).

Or even simpler, you could just use the SqlDataSource and have a single layer. Then you wouldn't need to create any extra classes or methods at all.

Do you need an example of this?

_________________________________

Visit my blog at http://leedumond.com

I don't think I need any examples. If so I'll let you know in the future.

Thanks for your help it's greatly appriciated.

I thought this was a good idea to have a BLL. Good learning experiance but maybe not what I am needing, but since I am this far I'll try to use it. Make everything more object oriented I guess.

I'm just not used to the 3 layers, I'm more experianced with one DAL that connected and retuned your data to you.

Again thanks alot,
Keith.

Right. You are probably used to being able to populate UI controls with DataReaders -- which of course is relatively easy in a two-tier app, but as you've discovered, not so easy to do with three layers.

_________________________________

Visit my blog at http://leedumond.com

I have created this usersdetails.vb file that has properties for every field that my stored proc is retuning.

 Public Sub New(ByVal AspNetUserID As Guid, ByVal UserName As String, ByVal FirstName As String, ByVal MiddleName As String, _
               ByVal LastName As String, ByVal Phone1 As String, _
               ByVal Phone2 As String, ByVal Customer As Int32, ByVal IsAnonymous As Boolean, ByVal LastActivityDate As DateTime, _
               ByVal Email As String, ByVal IsApproved As Boolean, ByVal IsLockedOut As Boolean, _
               ByVal CreatedDate As DateTime, ByVal LastLoginDate As DateTime, ByVal LastLockoutDate As DateTime, _
               ByVal FailedPasswordAttemptCount As Int32, _
               ByVal FailedPasswordAnswerAttemptCount As Int32)

            Me.AspNetUserID = AspNetUserID 'guid
            Me.UserName = UserName
            Me.FirstName = FirstName
            Me.MiddleName = MiddleName
            Me.LastName = LastName
            Me.Phone1 = Phone1
            Me.Phone2 = Phone2
            Me.Customer = Customer
            Me.IsAnonymous = IsAnonymous
            Me.LastActivityDate = LastActivityDate
            Me.Email = Email
            Me.IsApproved = IsApproved
            Me.IsLockedOut = IsLockedOut
            Me.CreatedDate = CreatedDate
            Me.LastLoginDate = LastLoginDate
            Me.LastLockoutDate = LastLockoutDate
            Me.FailedPasswordAttemptCount = FailedPasswordAttemptCount
            Me.FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount

        End Sub

But in my provider when I get too the function for reading the data
I get an System.IndexOutOfRangeException.

 Protected Overridable Function GetUserFromReader( _
                ByVal reader As IDataReader) _
                As UsersDetails

'ASPNETUserID is a guid
            Return New UsersDetails( _
             reader("ASPNETUserID"), _
             reader("UserName").ToString(), _
             reader("FirstName").ToString(), _
             reader("MiddleName").ToString(), _
             reader("LastName").ToString(), _
             reader("Phone1").ToString(), _
             reader("Phone2").ToString(), _
             CInt(reader("Customer")), _
             CBool(reader("IsAnonymous")), _
             CDate(reader("LastActivityDate")), _
             reader("Email").ToString(), _
             CBool(reader("IsApproved")), _
             CBool(reader("IsLockedOut")), _
             CDate(reader("CreatedDate")), _
             CDate(reader("LastLoginDate")), _
             CDate(reader("LastLockoutDate")), _
             CInt(reader("FailedPasswordAttemptCount")), _
             CInt(reader("FailedPasswordAnswerAttemptCount")))

        End Function

I have gone through all the functions that deal with getting and reading the infor from the stored proc, and I am not missing any fields.
Does anyone know why I might be getting this exception?
Thanks,

kss - check for null values coming from the sproc, as this could cause issues if not coerced or set to default(T) for the field type.

in your DataAccess.cs, you could have something like this (c# version)

        internal static T CastTo<T>(object value)
        {
            return value != DBNull.Value ? (T)value : default(T);
        }

and in your provider class, you'd then have something along these lines:

        protected virtual RegionDetails GetRegionFromReader(IDataReader reader)
        {
            return new RegionDetails(
                DataAccess.CastTo<int>(reader["RegionID"]),
                DataAccess.CastTo<string>(reader["RegionDescription"]));
        }

hope this sheds a little extra light...

Sorry, but can you explain what you are doing with that code above.
Why would null values cause me problems?
Some of the fields in my query can be null.

A System.IndexOutOfRangeException can occur when the reader index you are trying to access doesn't exist in the reader. For example, you have

reader("ASPNETUserID")

but if the data column name in the reader is actually "AspNetUserID", you'd have a mismatch, and thus the index would be considered "out of range".



_________________________________

Visit my blog at http://leedumond.com