troubleshooting Question

Generic and efficient Data Access Layer? Is it possible?

Avatar of ShaymusBane2
ShaymusBane2 asked on
.NET ProgrammingASP.NET
5 Comments1 Solution292 ViewsLast Modified:
I am trying to create an efficient and generic Data Access Layer for my application using the Microsoft Enterprise Data Library, proper error checking, and the ‘using’ syntax to wherever necessary to ensure that all my resources are handled properly.  I’m hoping to then leverage this DAL through the object data source syntax in my aspx page, as well as within my business logic in the code behind.

Since this probably doesn’t make much sense as words, I’ll throw in some pseudo-code:

DAL.cs
--------------
// Function for generic datareader method
Private SqlDataReader GetDataReader (string sqlCommand) {
  SqlDatabase db = new SqlDatabase (connectionString);
  Try {
  Using (SqlDataReader reader = db.ExecuteReader(sqlCommand)) {
    // This part I’m fuzzy about…how do I return the results from here???
    //  I considered using another SqlDataReader object, but that seems redundant?
  }
  } catch (SqlException error) {
     Session[“Error”] = error.message;
     Server.Transfer (“~/error/error.aspx”, false);
  }
// ideally, I’d like to return the reader here…but if it’s contained in the ‘using block’
// I don’t know how to do so.  Would making a temp DataTable work?  What about
// Another SqlDataReader and assign it’s value to the result inside the ‘using’ block?
Return reader;
}

// Function to use the generic method
Public SqlDataReader GetAllEmployees() {
  Return GetDataReader(“SELECT * FROM EMPLOYEES ORDER BY LAST_NAME”);
}

displayEmployees.aspx
------------------------------
<asp:DropDownList id=”ddlEmployees” runat=”server” DataSourceID=”dsEmployees”></asp:DropDownList>
<asp:ObjectDataSource id=”dsEmployees” runat=”server” selectMethod=”GetAllEmployees” TypeName=”DAL”>

There are all manner of problems I’m having with this though.  The original DAL I have is similar to this, but everything is done with datasets.  Now that my DAL is several thousand lines long, I’m concerned with the performance hit of populating all my data objects by DataSets….

Soooo…am I overreacting, by trying to remove all the DataSet objects?  Or is this concern valid (as it seems the vast majority of opinions on the internet would suggest) and I need to figure out how to complete the above?

Further, would using DataTables as return objects and just forgetting about DataReaders be 'just as good'?

Thanks in advance...

Shaymus
ASKER CERTIFIED SOLUTION
dstanley9

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 5 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 5 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004