locked
Microsoft.ApplicationBlocks.Data RRS feed

  • Question

  • User313433139 posted
    do we need to close our datareader or my sqlconnection after using the SqlHelper object ? thanx !
    Friday, July 16, 2004 1:24 AM

All replies

  • User-1638510185 posted
    I think that it depends on the way that you utilize SqlHelper. A "help" file is distributed with this Application Block which outlines the different scenarios. As an alternative, you could step thru the SqlHelper routines in debug mode.
    Sunday, July 25, 2004 5:18 PM
  • User241098857 posted
    I close my datareaders but I don't see how you can close the sqlconnection in a statement like this: Public Shared Function GetQuoteNumbers(ByVal tableName As String, ByVal dateFrom As DateTime, ByVal dateTo As DateTime) As ArrayList Dim myArray As New ArrayList() Dim r1 As SqlClient.SqlDataReader r1 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("connString"), CommandType.StoredProcedure, _ "QuoteApproval_Reports_Get_Approved_QuoteNo_ByDate", _ New SqlParameter("@dtTo", dateTo)) While r1.Read myArray.Add(r1("QuoteNo")) End While r1.Close() Return myArray End Function as there is no connection object when you use a connection string.
    Monday, August 30, 2004 11:07 AM
  • User-1449169821 posted
    Take a look at the SqlHelper source, there's a flag for internally and externally managed connections, the Helper WILL close conntections when you use Connection strings...it just wraps the connection like so for DataSets: using (SqlConnection connection = new SqlConnection(connectionString)) { } and if (connectionOwnership == SqlConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } For a DataReader...so when you close the Reader, the connection will close...
    Friday, September 24, 2004 6:15 AM
  • User-1449169821 posted
    Take a look at the SqlHelper source, there's a flag for internally and externally managed connections, the Helper WILL close conntections when you use Connection strings...it just wraps the connection like so for DataSets: using (SqlConnection connection = new SqlConnection(connectionString)) { } and if (connectionOwnership == SqlConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } For a DataReader...so when you close the Reader, the connection will close...
    Friday, September 24, 2004 6:16 AM
  • User313433139 posted
    Ok now that i use the SqlHelper i got a major problem. Im building a Web CMS and i got a to many connection open. does the object handle that problem for me or i have to close my connection everytime i use Sqlhelper ? Here some exemples of what i did. Because i have so much done so far i dont want to copy paste my close command for all my Factory object.. thanx public void UpdateTeam(TeamInfos ti) { SqlHelper.ExecuteNonQuery(conn,"UpdateTeam", ti.TeamID, ti.LeagueID,ti.TeamName,ti.Color,ti.DivisionID,ti.CityName,ti.Overall,ti.Scoring,ti.Defense,ti.Checking,ti.Powerplay,ti.Goaltending,ti.IsActive, ti.TeamLogo, ti.TeamBanner, ti.TeamBackground); } public TeamInfos GetTeam(int teamID, int leagueID) { SqlDataReader read = SqlHelper.ExecuteReader(conn, "GetTeam", teamID, leagueID); TeamInfos ti = null; while(read.Read()) { ti = new TeamInfos(); ti.Checking = int.Parse(read["Checking"].ToString()); ti.CityName = read["CityName"].ToString(); ti.Color = read["Color"].ToString(); ti.Defense = int.Parse(read["Defense"].ToString()); ti.DivisionID = int.Parse(read["DivisionID"].ToString()); ti.Goaltending = int.Parse(read["Goaltending"].ToString()); ti.IsActive = bool.Parse(read["IsActive"].ToString()); ti.LeagueID = int.Parse(read["LeagueID"].ToString()); ti.Overall = int.Parse(read["Overall"].ToString()); ti.Powerplay = int.Parse(read["PowerPlay"].ToString()); ti.Scoring = int.Parse(read["Scoring"].ToString()); ti.TeamID = int.Parse(read["TeamID"].ToString()); ti.TeamName = read["TeamName"].ToString(); ti.TeamBackground = read["TeamBkg"].ToString(); ti.TeamBanner = read["TeamBanner"].ToString(); ti.TeamLogo = read["TeamLogo"].ToString(); ti.CategoryID = int.Parse(read["TeamCategoryID"].ToString()); ti.CategoryName = read["TeamCategoryName"].ToString(); } return ti; } public void DeleteTeamHistoryStats(int historyID, int leagueID) { SqlHelper.ExecuteNonQuery(conn, "DeleteTeamHistoryStats", historyID, leagueID); }
    Thursday, December 30, 2004 12:38 AM
  • User-1449169821 posted
    Hi, when you use the Connection object (as opposed to a connection string), you're required to manage your own connections.It's not obvious from your code if conn is a string or connection obhect. In addition I'd wrap that SqlDataReader in a using{} block to ensure it disposes correctly as is you may get an orphaned connnection if your command bugs out - and you certainly would since you don't close or dispose the SqlDataReader after use. My preferred method when using the DAAB though is to pass a connection string, not an object.
    Thursday, December 30, 2004 6:50 PM
  • User313433139 posted
    ************* Edited by moderator Adec *************** Inserted missing < code></ code> tags (without the spaces inside). Always include such tags when including code in your postings. Don't force the moderators to do this for you. Many readers disregard postings without the code tags. ************************************************** Here what i do inside of my Factory object when i use diffirent functions. I got max pool connection been used. How can i fix that.. thanx !! SqlConnection _conn = null; private SqlConnection conn { get { _conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); return _conn; } } public void DeleteTab(int tabID, int leagueID) { SqlHelper.ExecuteNonQuery(conn, "DeleteTab", tabID, leagueID); conn.Close(); }
    Thursday, December 30, 2004 8:38 PM