locked
why need con.open() when we use using block? without con.open() is binding records when use inside "using" block. RRS feed

  • Question

  • User892410294 posted

    Hello,

    without con.open() also it is working. getting records from database and fill into database.

    they why need con.open() like below

    using (SqlConnection con = new SqlConnection(_ConnectionString))
    {

    con.Open();
    SqlCommand cmd1 = new SqlCommand("SELECT * FROM testtable WHERE name='" + UserId + "' AND password='" + UserPassword + "'", con);
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    da1.Fill(dt1);

    }

    help needed.

    Thursday, August 13, 2015 3:19 AM

Answers

  • User-1716253493 posted

    Afaik, dataadapter no need to open/close connection

    https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

    NoteNote

    The code shown in this example does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fillopened the connection, it also closes the connection when Fill is finished. This can simplify your code when you deal with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Openmethod of the Connection, performing the operations against the data source, and then calling the Close method of the Connection. You should try to keep connections to the data source open as briefly as possible to free resources for use by other client applications.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2015 4:47 AM
  • User71929859 posted

    In your code you are not specifically calling any method which executes queries (Like ExecuteNonQuery, ExecuteScalar etc.). In your case, you don't need to open the connection. It does not have anything to do with the using block. using block is only responsible for closing the connection, not opening. Even if you take it out from the using block and write it like below, it will work

    SqlConnection con = new SqlConnection(_ConnectionString))
    
    SqlCommand cmd1 = new SqlCommand("SELECT * FROM testtable WHERE name='" + UserId + "' AND password='" + UserPassword + "'", con);
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    da1.Fill(dt1);
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2015 11:00 PM
  • User-821857111 posted

    From the page: https://msdn.microsoft.com/en-us/library/377a8x4t(v=vs.110).aspx

    The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.
     You can either call conn.Dispose() or instantiate the connection in a using block to ensure it is marked for disposal. Note that if you explicitly call conn.Dispose(), you also need to use a try-catch block to handle any potential exceptions that might be raised when the data access operation is being performed. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 17, 2015 12:07 PM

All replies

  • User-770998187 posted

    the using statement will close the connection once you are done with that. So it is important to open if it is closed, otherwise you will have to check connection state everytime.

    Thursday, August 13, 2015 3:32 AM
  • User-1716253493 posted

    Afaik, dataadapter no need to open/close connection

    https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

    NoteNote

    The code shown in this example does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fillopened the connection, it also closes the connection when Fill is finished. This can simplify your code when you deal with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Openmethod of the Connection, performing the operations against the data source, and then calling the Close method of the Connection. You should try to keep connections to the data source open as briefly as possible to free resources for use by other client applications.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2015 4:47 AM
  • User71929859 posted

    In your code you are not specifically calling any method which executes queries (Like ExecuteNonQuery, ExecuteScalar etc.). In your case, you don't need to open the connection. It does not have anything to do with the using block. using block is only responsible for closing the connection, not opening. Even if you take it out from the using block and write it like below, it will work

    SqlConnection con = new SqlConnection(_ConnectionString))
    
    SqlCommand cmd1 = new SqlCommand("SELECT * FROM testtable WHERE name='" + UserId + "' AND password='" + UserPassword + "'", con);
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    da1.Fill(dt1);
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2015 11:00 PM
  • User892410294 posted

    you are right. But then if i did  not use 'USING' , the con object will not dispose. 

    Monday, August 17, 2015 9:44 AM
  • User-821857111 posted

    From the page: https://msdn.microsoft.com/en-us/library/377a8x4t(v=vs.110).aspx

    The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.
     You can either call conn.Dispose() or instantiate the connection in a using block to ensure it is marked for disposal. Note that if you explicitly call conn.Dispose(), you also need to use a try-catch block to handle any potential exceptions that might be raised when the data access operation is being performed. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 17, 2015 12:07 PM