How to call code from SQL in C#

Question

I have two classes in which the teachers have merged their final projects into one, one class is software engineering and another one is data bases. The thing is that for SE i have to develop a desktop/smartphone app and for DB i have to develop every DB related stuff for that app.

But i have to keep both things separated, i mean i have to keep C# code away from SQL code so i can't do queries or any stuff using selection strings and such, i just have to call stored procedures with said queries from code.

Any idea how could i do that? To summarize i just want to call any code or procedure that i write in sql and store it's values in a variable,object or array.

As i said i cannot use:

string selectstr = "SELECT * FROM students;"

and execute that query, i have to write that in sql and call it from C# and store the values returned.

Create your stored procedure and then call it from c# as you would do with an normal sql statement. If you have to return more than one value let your stored procedure write into some "result" table and read the result from there. — user743414, May 07 '14 at 07:51
possible duplicate of [How to execute a stored procedure within C# program](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — Tarec, May 07 '14 at 07:52
Excuse me? What exactly is unclear about the question?? If it was closed for being a duplicate, fine, but "unclear what you're asking"?? — Thorsten Dittmar, May 07 '14 at 08:05
You've put too much irrelevant informations, and phrases like `so i can't do queries or any stuff using selection strings and such` are hard to understand. Although I'd agree it's not a reason to close for being unclear, I'd point that question's a bit needless since you're asking about separating sql statements from app implementation while suggesting stored procedures at the same time. So it looks like you don't need an answer for "any idea?" question. — Tarec, May 07 '14 at 08:20
Sorry if my question was unclear or misleading in any way, i tried to give as much information as i could given that english is not my main language. Anyway, thanks for correcting me. — HardCodeStuds, May 07 '14 at 08:56

score 1 · Accepted Answer · answered May 07 '14 at 08:00

Stored procedures are called like any other SQL command in C#:

using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@myParameter1", value);
    ...

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
       ...
    }
}

The "magic" bit is to set the command type correctly ;-)

How to call code from SQL in C#

1 Answers1