Stored procedure works when executed in query builder, but doesn't work from C# code

Question

Please help me.

I want to create a dynamic search query in a stored procedure. The piece of code follows the query that I've written and run in the query builder but does not run when used from the C # code

My table name is info with 3 columns, id int, name nvarchar(50), family nvarchar(50).

SELECT Id, [name ], family, description
FROM info
WHERE (@n IS NULL) AND (@f IS NULL) OR
      (@n IS NULL) AND (family = @f) OR
      ([name ] = @n) AND (@f IS NULL) OR
      ([name ] = @n) AND (family = @f)

And by "Does not run" do you mean you get an exception? If so, what's the exception? — Simon Wilson, Jul 23 '18 at 11:39
This question doesn't work, i read one the other day and it worked, but this one doesn't work.... See what I've done here? i havent given you any indication why or what i don't understand or what the problem may be — TheGeneral, Jul 23 '18 at 11:49
OT personally, when I use combinations of ANDs and ORs in a condition, I use extra brackets to make *sure* of the precedence. I do not want to remember whether AND goes before OR or the other way around. — Hans Keﬆing, Jul 23 '18 at 11:51
As an aside, the `WHERE` can be shortened to `WHERE ((@n IS NULL) OR ([name ] = @n)) AND ((@f IS NULL) OR ([family] = @f))`. You've used every selector twice. If you kept doing the same thing, you'd end up with 8 lines (with 3 checks each) if a third filter was passed. — Flater, Jul 23 '18 at 11:58
@simon wilson when i just send @n to this procedure visual told me that you should send @f too — eng.peyman, Jul 23 '18 at 12:31
@Flater your right but when i write your suggestion the query buldder chenge it to that i show to you — eng.peyman, Jul 23 '18 at 12:35
@eng.peyman if you got an exception, then it would be very helpful to add details (Message, stacktrace, any InnerException) about that to your question (not as comment, but [edit]). — Hans Keﬆing, Jul 24 '18 at 10:13

Tom Dee · Answer 1 · 2018-07-23T12:19:14.343

1

I've wrote below a fairly generic template on how to read data from a stored procedure. You just need to add the connection string and procedure name.

using (var conn = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand("ProcedureName", conn) { 
                           CommandType = CommandType.StoredProcedure }) 
    {
       conn.Open();
       cmd.Parameters.Add(new SqlParameter("@f", variableOfF ?? DbNull.Value);
       cmd.Parameters.Add(new SqlParameter("@n", variableOfN ?? DbNull.Value);

       using (SqlDataReader reader = cmd.ExecuteReader())
       {
           while (reader.Read())
           {
               int id = Convert.ToInt32(reader["id"]);
               string name = reader["name"].ToString();
               string family = reader["family"].ToString();
               string description = reader["description"].ToString();

               // do whatever you need to with the variables
           }
       }
    }
}

edited Jul 23 '18 at 12:19

answered Jul 23 '18 at 11:57

Tom Dee

2,516
4
17
25

1

note that you should use `new SqlParameter("@f", variableOfF ?? DbNull.Value)` because `null` is an expected (non-erroneous) value. – Flater Jul 23 '18 at 11:59
Is there a reason that you are using Convert and ToString instead of simple casting? – Cetin Basoz Jul 23 '18 at 12:08
@CetinBasoz I just think it adds to the readability. An interesting post on this is: https://stackoverflow.com/a/15395832/8363456 – Tom Dee Jul 23 '18 at 12:18
IMHO it renders readability. And I don't understand what part of that link you wanted me to read? There I couldn't see anything that is controversial to what I said. Probably you didn't understand that I am talking about explicit casting instead of using methods. – Cetin Basoz Jul 23 '18 at 12:25

score 0 · Answer 2 · answered Jul 24 '18 at 10:54

0

Finally, the problem is solved and the solution is as follows: Sufficient in the input parameters of the query put the value of the input parameters null

i wish help someone else

answered Jul 24 '18 at 10:54

eng.peyman

19
1

Stored procedure works when executed in query builder, but doesn't work from C# code

2 Answers2