c# execute SqlCommand with Parameters in "using" code block

Question

I am attempting to execute an SQL Command through a 'using' code block, but can't seem to get it to work with Parameters. I get the error: 'Parameters does not exist in the current context', does anyone have a possible solution for this problem? Heres My code:

DataTable dt = new DataTable();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
using (var cmd = new SqlCommand(" SELECT FName" +
                                        " FROM EmployeeTable " +
                                        " WHERE EmployeeId = @empId",
                                        con)
                                        {
                                            Parameters.Add(new SqlParameter("@empId",empId))
                                        })
        {
            try
            {
                   con.open();
                   dt.Load(cmd.ExecuteReader());
            }
            catch(Exception ex)
            {
                 //(snip) Log Exceptions
            }
        }
        return dt;

@Corak I do log the exception in my actual code, but I didn't see that snippet of code being relevant to the problem at hand so I didn't bother putting it in — Divide100, Aug 19 '13 at 15:16

Scott Chamberlain · Accepted Answer · 2013-08-19T15:00:17.990

4

Don't use constructor initialization for this.

DataTable dt = new DataTable();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
using (var cmd = new SqlCommand(" SELECT FName" +
                                        " FROM EmployeeTable " +
                                        " WHERE EmployeeId = @empId",
                                        con))
{
    cmd.Parameters.Add(new SqlParameter("@empId",empId));
    try
    {
           con.open();
           dt.Load(cmd.ExecuteReader());
    }
    catch(Exception) //BAD BAD BAD!!! Why are you doing this!
    {
    }
}
return dt;

Also why are you catching all exceptions and throwing them away, this is a horrible thing to. If you have a exception you think is going to be common first see if you can refactor your code to check the for the input values that would cause it and not have it thrown at all (perhaps even throw an ArgumentException of your own), and if you can't do that then check for that specific exception, not every possible one.

edited Aug 19 '13 at 15:00

answered Aug 19 '13 at 14:53

Scott Chamberlain

124,994
33
282
431

I had it set to SqlException in my actual code, I just wrote that out quickly – Divide100 Aug 19 '13 at 14:59
I plan on forwarding the error to an error page and logging it into the DB later on, but right now I haven't done much with the exceptions – Divide100 Aug 19 '13 at 15:03
Then at least change your catch to `catch(SqlException ex)` and put in `throw new NotImplmentedException("Error logging not added", ex);` in the body. That way if the exception does happen and you forget to update your code you can still be notified that it happened. – Scott Chamberlain Aug 19 '13 at 15:05
Right now I display the error in a div when its thrown: errorDiv.InnerText = "Error: " + e.Message; – Divide100 Aug 19 '13 at 15:06
2

In the future, when you ask questions here just add something like `//(Snip) log the execption` to your question if you are actually logging the error or you will get everyone jumping up and down on you for throwing away your exception. That error is common in new programmres so a lot of people go out of the way to break that bad habit before it starts if they see it. By showing you do logging but did not include it in the question people won't bother you about it (I recommend editing your original question too) – Scott Chamberlain Aug 19 '13 at 15:09
@Divide100 Are you still having your problem? If someone provided you with a solution that worked please [mark the answer as accepted](http://stackoverflow.com/help/someone-answers). – Scott Chamberlain Aug 19 '13 at 16:34

score 2 · Answer 2 · answered Aug 19 '13 at 14:53

2

It is impossible to use object initializer for anything other than property assignment, so you should rewrite your code like this (only relevant part here):

...
using (var cmd = new SqlCommand(" SELECT FName" +
                                        " FROM EmployeeTable " +
                                        " WHERE EmployeeId = @empId",
                                        con))
{
    cmd.Parameters.Add(new SqlParameter("@empId", empId));
...

answered Aug 19 '13 at 14:53

Andrei

55,890
9
87
108

Ok, so you can't specify it within the initialization of the using block? – Divide100 Aug 19 '13 at 14:54
1

@Divide100, expressions like `Collection.Add` - no. – Andrei Aug 19 '13 at 14:56

score 0 · Answer 3 · answered Aug 19 '13 at 14:56

0

Your second using statement closes before the try catch. Therefore once the try catch is reached the using block has already been closed. The parameters are therefore in the incorrect scope.

answered Aug 19 '13 at 14:56

Christopher Trevor

26
4

Oh yes, I see now. My bad. . .been a long day. – Christopher Trevor Aug 19 '13 at 15:24

c# execute SqlCommand with Parameters in "using" code block

3 Answers3

Linked

Related