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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros