472,808 Members | 2,777 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,808 software developers and data experts.

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
2 3591

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bill Willyerd | last post by:
Hello All, I have been searching for a published document for Best Practices concerning access levels based on roles. Should developers have more than (if at all) select level access to...
41
by: laimis | last post by:
Hey guys, I just recently got introduced to data mappers (DTO mapper). So now I have a SqlHelper being used by DTOMapper and then business layer is using DTOMapper when it needs to persist...
8
by: Art | last post by:
Hi folks, I'm writing a traditional desktop app using VB.NET and am stumbling over what seems like a very basic question: My app does not need to be connected to a server or another computer....
3
by: Marc Gravell | last post by:
Kind of an open question on best-practice for smart-client design. I'd really appreciate anyones views (preferably with reasoning, but I'll take what I get...). Or if anybody has any useful links...
4
by: Paul Aspinall | last post by:
Hi I've previously constucted my .NET 1.1 data access, via a Data Access Layer (DAL), that was all in code. Now that I have access to the DataSet control in .NET 2.0, I'm looking for opinions...
1
by: Paul Aspinall | last post by:
Hi I've previously constucted my .NET 1.1 data access, via a Data Access Layer (DAL), that was all in code. Now that I have access to the DataSet control in .NET 2.0, I'm looking for opinions...
13
by: Alan Silver | last post by:
Hello, MSDN (amongst other places) is full of helpful advice on ways to do data access, but they all seem geared to wards enterprise applications. Maybe I'm in a minority, but I don't have those...
7
by: Arpan | last post by:
The .NET Framework 2.0 documentation states that An Object variable always holds a pointer to the data, never the data itself. Now w.r.t. the following ASP.NET code snippet, can someone please...
13
by: G | last post by:
Hello, Looking for opinions on a fairly simple task, new to ASP.net (C#) and want to make sure I do this as efficiently as possible. I have a web based form, and I need to run some SQL before...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.