467,920 Members | 1,293 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,920 developers. It's quick & easy.

C# Data Access Best Practice

I have a general question on best practice regarding data access. I have
the code below, a static method defined in a class that I use in a data
layer dll. The method takes a string as its parameter, connects to the
database , executes the string (in this case SQL) in the form of a
SqlDataReader and then returns the SqlDataReader.

Here's the method...

public static SqlDataReader SQLServerExecuteSQL(string SQLstr)
{
SqlConnection myConnection;
SqlCommand myCommand;
SqlDataReader dr;
String connectionString =
ReportUIClassLibrary.UtilityObjects.GetConnectionS tring();
myConnection = new SqlConnection(connectionString);
myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand(SQLstr, myConnection);
dr = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
return dr;
myConnection.Close();
}

As a note ReportUIClassLibrary.UtilityObjects.GetConnectionS tring() is a
method that reads all of the connect information from a configuration
file. I then use this method in an ASP.NET as so. In the example below I
am assigning properties of a listbox...

string DBID = Request.QueryString["dbid"];
string SQLstr;
SQLstr = "SELECT * FROM MRSReports where DBID = '" + DBID + "'";
lbGetReport.DataSource =
ReportUIClassLibrary.DBObjects.SQLServerExecuteSQL (SQLstr);
lbGetReport.DataTextField = "ReportName";
lbGetReport.DataValueField = "ReportID";
lbGetReport.DataBind();
lbGetReport.Dispose();

What Iím worried about is a couple of things. Firstly am I leaving an
open connection to my database using this technique - I'm not sure if
calling the dispose on the listbox is going to be good enough. Secondly
and more importantly is this really good practice? Does anyone have any
experiences to share about what is best practice when designing code
that has to frequently open and close database connections? What about
Microsoft Data Access Application Block, has anyone had experience with
using that component and could comment on whether it was a pleasant
experience?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #1
  • viewed: 3364
Share:
2 Replies

"Joe Bloggs" <bo********@netscape.net> wrote in message
news:Oy*************@TK2MSFTNGP11.phx.gbl...
I have a general question on best practice regarding data access. I have
the code below, a static method defined in a class that I use in a data
layer dll. The method takes a string as its parameter, connects to the
database , executes the string (in this case SQL) in the form of a
SqlDataReader and then returns the SqlDataReader.

Here's the method...

public static SqlDataReader SQLServerExecuteSQL(string SQLstr)
{
SqlConnection myConnection;
SqlCommand myCommand;
SqlDataReader dr;
String connectionString =
ReportUIClassLibrary.UtilityObjects.GetConnectionS tring();
myConnection = new SqlConnection(connectionString);
myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand(SQLstr, myConnection);
dr = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
return dr;
myConnection.Close();
}

As a note ReportUIClassLibrary
.. . .
What I'm worried about is a couple of things. Firstly am I leaving an
open connection to my database using this technique - I'm not sure if
calling the dispose on the listbox is going to be good enough. Secondly
and more importantly is this really good practice?


IMO, no. It is not good practice to return DataReaders. It's just too easy
to leak connections. If you always open and close connections and
DataReaders locally it's easy to verify that your code is correct. If you
return a DataReader, you must chase down the client code to verify that
you're not leaking connections.

Also when you return a DataReader from a library you have failed to hide the
complexities of data access from the calling code and have introduced
operational requirements which are not obvious from the interface design.
How do you properly admonish the client developer to close the DataReader
before letting it go out of scope?

I would always spend the extra memory and return DataSets. If you feel you
must use a DataReader, then use it locally inside a using block.

David
Nov 16 '05 #2
Also, any code after the return statement never executes.. i.e.
connection.close is being called after return...

It's generally best practices to use parameters when querying the database,
this helps by:
a) Preventing SQL injection attacks
b) Performance (makes it easier for the optimiser to cache queries.)

Just my 2c...

"David Browne" wrote:

"Joe Bloggs" <bo********@netscape.net> wrote in message
news:Oy*************@TK2MSFTNGP11.phx.gbl...
I have a general question on best practice regarding data access. I have
the code below, a static method defined in a class that I use in a data
layer dll. The method takes a string as its parameter, connects to the
database , executes the string (in this case SQL) in the form of a
SqlDataReader and then returns the SqlDataReader.

Here's the method...

public static SqlDataReader SQLServerExecuteSQL(string SQLstr)
{
SqlConnection myConnection;
SqlCommand myCommand;
SqlDataReader dr;
String connectionString =
ReportUIClassLibrary.UtilityObjects.GetConnectionS tring();
myConnection = new SqlConnection(connectionString);
myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand(SQLstr, myConnection);
dr = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
return dr;
myConnection.Close();
}

As a note ReportUIClassLibrary


.. . .
What I'm worried about is a couple of things. Firstly am I leaving an
open connection to my database using this technique - I'm not sure if
calling the dispose on the listbox is going to be good enough. Secondly
and more importantly is this really good practice?


IMO, no. It is not good practice to return DataReaders. It's just too easy
to leak connections. If you always open and close connections and
DataReaders locally it's easy to verify that your code is correct. If you
return a DataReader, you must chase down the client code to verify that
you're not leaking connections.

Also when you return a DataReader from a library you have failed to hide the
complexities of data access from the calling code and have introduced
operational requirements which are not obvious from the interface design.
How do you properly admonish the client developer to close the DataReader
before letting it go out of scope?

I would always spend the extra memory and return DataSets. If you feel you
must use a DataReader, then use it locally inside a using block.

David

Nov 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bill Willyerd | last post: by
41 posts views Thread by laimis | last post: by
4 posts views Thread by Paul Aspinall | last post: by
1 post views Thread by Paul Aspinall | last post: by
7 posts views Thread by Arpan | last post: by
13 posts views Thread by G | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.