J
Joe Bloggs
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.GetConnectionString();
myConnection = new SqlConnection(connectionString);
myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand(SQLstr, myConnection);
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
myConnection.Close();
}
As a note ReportUIClassLibrary.UtilityObjects.GetConnectionString() 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?
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.GetConnectionString();
myConnection = new SqlConnection(connectionString);
myConnection.Open();
//prepare sql statements
myCommand = new SqlCommand(SQLstr, myConnection);
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
myConnection.Close();
}
As a note ReportUIClassLibrary.UtilityObjects.GetConnectionString() 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?