-1

I tried to find my answer on google as always but I found only a partial answer to my question.

My question is: while creating a connection to a database, what is the more preferable way to make it: without or with the USING.

For now, as I start in C# (but I have some skill in PHP), I learned online with the USING method like below:

string conString = Properties.Settings.Default.mattDatabaseConnectionString;

using (SqlCeConnection con = new SqlCeConnection(conString))
{
    using (SqlCeCommand query = new SqlCeCommand("SELECT * FROM customers", con))
    {
        SqlCeDataReader reader = query.ExecuteReader();
    }
}

On google, I found that the use of the USING prevent from forget to close the ExecuteReader and dispose the SqlCeCommand. But you can also make it without like below (example took from msdn):

SqlCeConnection conn = null;
        SqlCeCommand cmd = null;
        SqlCeDataReader rdr = null;
        try
        {
            // Open the connection and create a SQL command
            //
            conn = new SqlCeConnection("Data Source = AdventureWorks.sdf");
            conn.Open();

            cmd = new SqlCeCommand("SELECT * FROM DimEmployee", conn);

            rdr = cmd.ExecuteReader();

            // Iterate through the results
            //
            while (rdr.Read())
            {
                int employeeID = rdr.GetInt32(0);   // or: rdr["EmployeeKey"];
                string lastName = rdr.GetString(5); // or: rdr["FirstName"];
            }

            // Always dispose data readers and commands as soon as practicable
            //
            rdr.Close();
            cmd.Dispose();
        }
        finally
        {
            // Close the connection when no longer needed
            //
            conn.Close();
        }

I know the 2 methods above are equivalent but whats is the best method for skilled programmers and why. Probably the second for less characters??

And is the SqlCeEonnection conn = null; and the 2 others really necessary??

Thanks guys

Chris Dunaway
  • 10,974
  • 4
  • 36
  • 48
MindKind
  • 49
  • 1
  • 12
  • Use whatever you find more readable. In my opinion `using` is less verbose and doesn't raise questions like _"is sqlceconnection conn = null necessary"_. – Tim Schmelter Aug 31 '14 at 20:32
  • You should check `conn` for null before closing it to make these 2 methods equivalent – Sergey Berezovskiy Aug 31 '14 at 20:34
  • I'm surprised no one has mentioned the [IDisposable](http://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx) interface, the `Dispose()` method, or garbage collection yet. Granted, the question was asked about a database connection (et al), but it's as much about .Net programming as it is about Sql Server or coding style. – Dave Mason Aug 31 '14 at 21:47

2 Answers2

1

Using isn't 'necessary', but it's a good idea because it'll save you some issues.

Yes if you remember to close in the finally then you're going to be alight, but it's less verbose to just put it within using. It also makes for cleaner code in my opinion (as you're limiting the scope of the variable to within the scope of the connection)

Haedrian
  • 4,240
  • 2
  • 32
  • 53
  • My opinion is the code will look more clean without the USING. Do you personally use the USING or not? – MindKind Aug 31 '14 at 20:37
  • I personally do use the Using. But yeah it's mostly a stylistic choice. If you prefer using try/finally blocks then it's your decision. It's that tiny bit more verbose. – Haedrian Aug 31 '14 at 20:38
  • @Matt how do you define cleanness of code? More lines = more clean? `using` is very compact and self-descriptive syntax to dispose resources – Sergey Berezovskiy Aug 31 '14 at 20:38
  • @SergeyBerezovskiy Less code mean the code is more light.. more clean. no? – MindKind Aug 31 '14 at 20:39
  • @Haedrian perfect. thanks. I think I will continue use the USING to be sure to not have issues. – MindKind Aug 31 '14 at 20:41
  • 1
    @Matt exactly. Less code = less code to read and understand, less code to maintain. `using` gives much less code then `try..finally` – Sergey Berezovskiy Aug 31 '14 at 20:41
  • @SergeyBerezovskiy ok yes I understand your point of view. The use of try-finally make more lines, but make the connection code more clean. Use of the USING make less lines but make the connection code harder to read. .. heh :P for me right now thats really a missing issues case. I think i'll go for the use of USING. – MindKind Aug 31 '14 at 20:44
1

using is the cleanest way to make sure your connection is disposed, and I like to write nested using statements in this way:

using (SqlCeConnection con = new SqlCeConnection(conString))
using (SqlCeCommand query = new SqlCeCommand("SELECT * FROM customers", con))
{
    con.Open();
    using(SqlCeDataReader reader = query.ExecuteReader())
    {
        //...
    }
}

The try-catch is equivalent as you mentioned.

The only exception, of course, is when you don't want to dispose the connection right away. For instance, you might need to use the data reader outside the using block, in which case you shouldn't use a using because it will dispose the connection when you exit it, and the data reader will throw an exception when you try to use it while the underlying connection is closed.

Eren Ersönmez
  • 38,383
  • 7
  • 71
  • 92