1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL from C#, standards

Discussion in 'C#' started by nubsii, May 7, 2008.

  1. #1
    Hi there. I read my own code and caught myself with something basically like this:
    
            try
            {
                //query stuff
                return command.ExecuteNonQuery();
                sqlConn.Close();
            }
            catch
            {
                sqlConn.Close();
                return 0;
            }
    
    Code (markup):
    For being a bit of code that never failed, its incredibly stupid. The sqlconn cant close if i've returned out of the function.. and the catch returns 0 which is the same thing the command would've returned if it didnt actually do anything.

    So instead I'd like to offer a slightly abstract model for how all queries should look FOR CRITIQUE (the kinda thing you'd find in someone tutorial about sql) and then pose a question: What sort of clever checks do you do in your own code using the data returned from ExecuteNonQuery() and the various other Executes()?

    
    string connectionString = "your connection string";
    SqlConnection conn = new SqlConnection(connectionString);
    try
    {
      sqlConn.Open();
      SqlCommand cmd = sqlConn.Createcommand();
      //if parameters are to be used.. they would appear here and look like this...
      cmd.Parameters.AddWithValue(stuff,stuff) //rather than Parameters.Add(new SqlParameter(stuff,stuff))
      cmd.CommandText = "your query"
      cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
       //write it
    }
    finally
    {
      sqlConn.Close();
    }
    
    Code (markup):
    How does that look for as an abstract shape for all SQL queries run from C#?

    Also lets say it returned something, perhaps a value indicating success, perhaps data. Where would you put the Return <value> in? Do SqlReaders need to be explicitly closed?
    SEMrush
     
    nubsii, May 7, 2008 IP
    SEMrush
  2. nubsii

    nubsii Peon

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I would like to append to the above that my primary concern when returning data out of the function is the status of the SqlConnection. If I return data in the midst of my try {}, will the my finally{} get to close the SqlConnection? Or will I have exited that block of code and left my SqlConnection open?

    I just now read that closing a DataReader will close a SqlConnection as well. Is that the missing piece to my puzzle?

    Pseudocode:
    Open dataReader,
    Get data and save it into an object,
    Close dataReader, (and therefore the SqlConnection as well)
    return the object
     
    nubsii, May 7, 2008 IP
  3. dgxshiny

    dgxshiny Greenhorn

    Messages:
    65
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    23
    #3
    The finally will execute under any circumstance. Fail or success, return in the try or not.

    Think of it as this: try this, if that does not work do this (catch). After that, finally do this.
     
    dgxshiny, May 7, 2008 IP
  4. dgxshiny

    dgxshiny Greenhorn

    Messages:
    65
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    23
    #4
    Also, closing a datareader will not close the sql connection, if anything, it is the other way around. Closing the connection closes the reader.
     
    dgxshiny, May 7, 2008 IP
  5. nubsii

    nubsii Peon

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Oh I see, I had made the false assumption that a return would leave that code abruptly. I tested the usage of finally while having returns in both the try{} and the catch{} and it does indeed occur no matter what. When I put a return in the finally{} I had a compile error (which makes sense, or else I would be able to return twice, once in the try/catch and once again in the finally).

    This leaves me with a few final questions before I implement a data access model which hopefully I can avoid changing for a long time...

    1) Is there any way that my finally will try to close a sqlConnection which isnt open (and is that problematic)? Maybe if the theres an issue with the SQL server an exception will get thrown, my code will enter the catch{} and then the finally{} where it attempts to close a connection which never opened. Would robust code look something like the following?
    
    //pseudocode...believe it or not!
    finally
    {
       if the sql connection is open  //Does this even exist? sqlConnection.State or something?
          close it
    }
    
    Code (markup):
    2) What about using Using(sqlConnection) {} ? (lol @ that sentence) If returning out of a try/catch allows the finally to execute, then I assume that leaving the scope of Using(sqlConnection) {} via a return statement will close/dispose of the sqlConnection just the same.
    I was browsing another forum when i stumbled upon a post pretty much just like this one... The model that gained the most favor looked like this:
    
    //pseudocode as usual...
    using (SqlConnection)
    {
       using (SqlCommand)
       {
           using (DataReader)
           {
           }
       }
    }
    
    Code (markup):
    What do you think about that? And then maybe wrap that whole thing into a try/catch, with no finally?
     
    nubsii, May 8, 2008 IP
  6. dgxshiny

    dgxshiny Greenhorn

    Messages:
    65
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    23
    #6
    I have never had the issue come up of trying to close a closed connection, but I do believe that you would get an exception from that. I do have a CloseConnection() function that does what you described. However, I do not check to see if the ConnectionState is Open, I check to see if it is not Closed. There are a few other states that it could possibly be in besides open and closed.

    I usually do not employ the Using statement, I used it at a job that used it as their standard before but I am not familiar enough to answer that question.
     
    dgxshiny, May 9, 2008 IP