locked
SQL Best practices - Class to open connection? RRS feed

  • Question

  • User217028333 posted

    Hi all

    Ive been reading up on ASP.NET SQL connections and so far no one seems to be able to say the best way of doing this.

    I seem to have a lot of pages on my site which requires access to a database.

    At the moment i am opening the connection in the code behin, performing a task and then closing the connection (and reader)

    However this does not seem like a very good way of doing it to me for 2 reasons.

    Firstly, i have 2 connections, one for localhost when developing and one for live. At the moment i am just manually commenting out the connection string that i dont need.

    Secondly, i feel like im spending a lot of time writing out the SQL code. I do have both connection strings in the web.config and that has saved me a bit of time but i was wondering if anyone can think of a reason not to just put the whole thing into two classes.

    So i define the query as a string in page, pass that into a class method, it then opens the connection. I perform a task and then call a method to close the string again. In the opening method i could check if the url is local hst and if it is it connects to the dev connection.

    What do you think? Is this a bad idea?

    How would you do this?

    Thanks very much

    Dariune

    P.s. if you are feeling really helpful, and agree with my above solution, a small example would be greatly apreciated too. :)

    Saturday, September 3, 2011 7:43 AM

Answers

  • User3866881 posted

    Sorry but I haven't seen your acceptor of SqlDataReader,and it should be:

    SqlDataReader rd = GlobalHelper.OpenConn(strSQL);
    while(rd.Read())
    {
        ………………
    }

    rd.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 6, 2011 4:12 AM
  • User3866881 posted

    Hello dariune,

    Try to change to this:

    public class GlobalHelper
    {
    // Connects to the database using the passed string as a connectionstring
           
    public static SqlDataReader OpenConn(string SQLString)
           
    {

                   
    bool isLocal = HttpContext.Current.Request.IsLocal;
                   
    SqlConnection strConnection;

                   
    if (isLocal == true)
                   
    {
                            strConnection
    = new SqlConnection(ConfigurationManager.AppSettings["strLocalConnString"]);
                            strConnection
    .Open();
                   
    }
                   
    else{
                            strConnection
    = new SqlConnection(ConfigurationManager.AppSettings["strConnString"]);
                            strConnection
    .Open();
                   
    }

                   
    SqlDataReader myReader = null;

                   
    SqlCommand strSQL = new SqlCommand(SQLString, strConnection);
                    myReader
    = strSQL.ExecuteReader(CommandBehavior.CloseConnection);

                   
    return myReader;
                    
           
    }
    }

    When you use something like myReader.Close(), this will automatcially close the connection.

    Thx again

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 8, 2011 7:23 PM

All replies

  • User1460320011 posted

    your idea is ok... can make the code more simple..

    Saturday, September 3, 2011 7:58 AM
  • User217028333 posted

    Oh really?

    How?

    Edit: Oh you mean what i said above would make the code simpler. I thought you meant there is a simpler way than my way. 


    Ok well unless anyone tells me otherwise i think that is how i will do it.

    Saturday, September 3, 2011 9:32 AM
  • User3866881 posted

    So i define the query as a string in page, pass that into a class method, it then opens the connection. I perform a task and then call a method to close the string again. In the opening method i could check if the url is local hst and if it is it connects to the dev connection.

    It sounds nice, but more accurately, would you mind showing us your detailled codes?

    Thx again

    Sunday, September 4, 2011 9:47 PM
  • User217028333 posted

    Hi

    Ive tried to do what i said above and i am having trouble.

    Below is my code, is anyone able to see where i have gone wrong?

    Thanks all

    I apreciate the help (As always :) )

    I have a global function ....

    public class GlobalHelper
    {
    // Connects to the database using the passed string as a connectionstring
    	public static SqlDataReader OpenConn(string SQLString)
    	{
    
    		bool isLocal = HttpContext.Current.Request.IsLocal;
    		SqlConnection strConnection;
    
    		if (isLocal == true)
    		{
    			strConnection = new SqlConnection(ConfigurationManager.AppSettings["strLocalConnString"]);
    			strConnection.Open();
    		}
    		else{
    			strConnection = new SqlConnection(ConfigurationManager.AppSettings["strConnString"]);
    			strConnection.Open();
    		}
    
    		SqlDataReader myReader = null;
    
    		SqlCommand strSQL = new SqlCommand(SQLString, strConnection);
    		myReader = strSQL.ExecuteReader();
    
    		return myReader;
    		
    	}
    }



    And then on the code behind of an actual page ...

    string strSQL = "my sqlcommand";
    
    		GlobalHelper.OpenConn(strSQL);
    
    		try
    		{
    			//SqlCommand strSQL = new SqlCommand("My SqlCommand", strConn);
    			//test = strSQL.CommandText.ToString();
    		//	myReader = strSQL.ExecuteReader();
    			
    			while (myReader.Read())
    			{
    				strTitle = myReader["Title"].ToString();
    				strContent = Server.HtmlDecode(myReader["Content"].ToString());
    				strPostedBy = GlobalHelper.GetStaffName(myReader["PostedBy"].ToString());
    				dteDateAdded = myReader["DateAdded"].ToString();
    				dteUpdated = myReader["DateUpdated"].ToString();
    				strImagePath = myReader["Avatar"].ToString();
    				frmGUID.Value = myReader["guid"].ToString();
    			}
    
    		}
    
    catch (Exception ex)
    		{
    			throw ex;
    		}
    
    strConnection.Close();
    		strConnection.Dispose();
    		myReader.Dispose();
    Monday, September 5, 2011 2:12 PM
  • User3866881 posted

    Ive tried to do what i said above and i am having trouble.

    What trouble have you got? Have you got anything wrong exception messages???

    Please list them for us to see...

    Thx again

    Monday, September 5, 2011 9:17 PM
  • User217028333 posted

    Oh yes, that would help wouldnt it lol.

    Sorry about that.

    The myReader has the "Cannot use in this context" error on the code behind page. (Im not infront of it but i think thats what it was)

    Tuesday, September 6, 2011 3:51 AM
  • User3866881 posted

    Sorry but I haven't seen your acceptor of SqlDataReader,and it should be:

    SqlDataReader rd = GlobalHelper.OpenConn(strSQL);
    while(rd.Read())
    {
        ………………
    }

    rd.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 6, 2011 4:12 AM
  • User217028333 posted

    Ooops i didnt realise i would have to do that.

    I thought i could return myReader and have it return the variable as well.

    I was obviously tired last night.

    Before we consider this one done, could i ask one more question.

    I cant close the strConn in the class because it isnt done being used yet.

    I cant close it in the page because it isnt directly being returned.

    How then, do i securely close the strConn?

    Tuesday, September 6, 2011 1:29 PM
  • User1460320011 posted

    put finally after catch

    try

    catch

    finally

    strConn.close()

    Tuesday, September 6, 2011 8:59 PM
  • User217028333 posted

    Hi

    Thanks for the reply

    Unfortunately that wont work because the Connection is declared and opened in the extranal class and therefore when i try to close it on the codebehind page it says it is out of contect.

    I was thinking of returning both the connection and the reader as an array? Is that the best option do you think?

    Any ideas?

    Edit: I tried to make it an array but because they are both different types i cant put them in an array to be returned and used on page.

    .NET does like to make things harder than it needs to be sometimes i find lol

    Wednesday, September 7, 2011 1:39 PM
  • User217028333 posted

    No one? There must be a way of closing a connection opened in a class.

    Thursday, September 8, 2011 4:21 PM
  • User3866881 posted

    Hello dariune,

    Try to change to this:

    public class GlobalHelper
    {
    // Connects to the database using the passed string as a connectionstring
           
    public static SqlDataReader OpenConn(string SQLString)
           
    {

                   
    bool isLocal = HttpContext.Current.Request.IsLocal;
                   
    SqlConnection strConnection;

                   
    if (isLocal == true)
                   
    {
                            strConnection
    = new SqlConnection(ConfigurationManager.AppSettings["strLocalConnString"]);
                            strConnection
    .Open();
                   
    }
                   
    else{
                            strConnection
    = new SqlConnection(ConfigurationManager.AppSettings["strConnString"]);
                            strConnection
    .Open();
                   
    }

                   
    SqlDataReader myReader = null;

                   
    SqlCommand strSQL = new SqlCommand(SQLString, strConnection);
                    myReader
    = strSQL.ExecuteReader(CommandBehavior.CloseConnection);

                   
    return myReader;
                    
           
    }
    }

    When you use something like myReader.Close(), this will automatcially close the connection.

    Thx again

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 8, 2011 7:23 PM
  • User217028333 posted

    Thats fantastic.

    I had no idea you could do that.

    Thanks mate :)

    Saturday, September 10, 2011 7:09 AM