asked on 2/27/2007

Generic and efficient Data Access Layer? Is it possible?

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

.NET Programming ASP.NET

Last Comment

dstanley9

2/28/2007

ASKER CERTIFIED SOLUTION

dstanley9

2/27/2007

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ShaymusBane2

2/28/2007

ASKER

Good to know...that being said, using DataTables / Datasets, how far off is my pseudo code, with regards to clean up and efficiency? Would something more like this work? Are there any glaring 'bugs' or connections that need to be closed? Does the SqlDatabase need to be cleaned up in the finally? Anything else I'm missing here?

DAL.cs
--------------------

private DataTable GetDataTable(string sqlCommand) {
DataTable results = null;
SqlDatabase db = new SqlDatabase (connectionString);
Try {
Using (DataSet ds = db.ExecuteDataSet(sqlCommand)) {
result = ds.Tables[0];
}
} catch (SqlException error) {
Session[“Error”] = error.message;
Server.Transfer (“~/error/error.aspx”, false);
}
return result;
}

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

dstanley9

2/28/2007

Your USNIGN block is incorrect. DataSets do not implement IDisposable, so the USING block does nothing. I assume you want the using block around your SqlDatabase class:

private DataTable GetDataTable(string sqlCommand) {
DataTable results = null;
Using (SqlDatabase db = new SqlDatabase (connectionString)){
Try {
DataSet ds = db.ExecuteDataSet(sqlCommand)
result = ds.Tables[0];
}
} catch (SqlException error) {
Session[“Error”] = error.message;
Server.Transfer (“~/error/error.aspx”, false);
}
}
return result;
}

Other than that, assuming your SqlDatabase class closes the connection when it is disposed, you should be fine.

ShaymusBane2

2/28/2007

ASKER

Thanks for the advice.

For what it's worth, SqlDatabase does not implement IDisposable (at least not according to the compile error I get), but DataSet DOES implement IDisposable...at least I'm assuming as such, since it does compile with the Dataset contained within the using block.

Thanks again dstanley9

dstanley9

2/28/2007

You are correct. It does implement Dispose(), but it does nothing with the underlying connection. In fact, you don't _want_ to dispose it because you are returning one of the underlying tables. You just have to trust that SqlDatabase is closing the connection properly (assuming it's a black-box component)