c# .net How to use a stored procedure?

Question

I am using Visual Studio 2017, created a C# .NET Web Service. I have created a function in my model and i want to make an SQL Insertion using stored procedures. The problem is that i get the error :

Could not find stored procedure 'createEmployee'

Do i have to define the procedure "createEmployee" somewhere else? Code below :

public string create()
{

    using (SqlConnection mConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
    {
        using (SqlCommand mCommand = new SqlCommand("createEmployee", mConnection))
        {

            mCommand.CommandType = CommandType.StoredProcedure;

            mCommand.Parameters.AddWithValue("@name", Name);
            mCommand.Parameters.AddWithValue("@surname", Surname);
            mCommand.Parameters.AddWithValue("@phone", Phone);
            mCommand.Parameters.AddWithValue("@gender", Gender);
            mCommand.Parameters.AddWithValue("@salary", Salary);
            mCommand.Parameters.AddWithValue("@country", Country);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                return "success";
            }
            catch (SqlException e)
            {
                return e.Message.ToString();
            }

        }
    }

}

you need to create the procedure on the database server - you did do that? — Patrick Artner, Jan 13 '18 at 20:30
@PatrickArtner This is my first C# .Net and i was confused about this. So in order this to work, will i have to execute an SQL "Create Procedure createEmployee" as well? — Antonios Tsimourtos, Jan 13 '18 at 20:31
[What is a stored procedure](https://stackoverflow.com/questions/459457/what-is-a-stored-procedure) — Steve, Jan 13 '18 at 20:32
I assume you already created the Sql Procedure in db, so no, you don't have to run any "create procedure" in your c# code. Maybe you need to qualify the name somehow? such "dbo.myDb.CreateEmploee". this can depend on connectionstring value — Gian Paolo, Jan 13 '18 at 20:34
Wait, it seems so static to me - what happens if i want to `update` (question is about insert though) just 1 column? How that procedure would look like? — Antonios Tsimourtos, Jan 13 '18 at 20:38
@GianPaolo What do you mean? Do i have to define something else, somewhere else, in order for the above code block to work? — Antonios Tsimourtos, Jan 13 '18 at 20:42
Storedprocedure are blocks of code written in the database and called (as you try to do) from your program. You need to create the storedprocedure on the database using the appropriate administrative tools (like Sql Server Management Studio) You can even create them directly from code. But only after they exist on the database you could call them from your code — Steve, Jan 13 '18 at 20:44
@Steve Got it. I thought i should only declare it in my code and not database as well. Do you have a good reference/tutorial for c# .net? I would appreciate it! — Antonios Tsimourtos, Jan 13 '18 at 20:50
For simple usage you could look at this question https://stackoverflow.com/questions/1383406/using-stored-procedures-in-c-sharp More in depth documents can be found on the Sql Server documentation — Steve, Jan 13 '18 at 20:55
Stored procedure will contain insert sql query. You need to create stored procedure in your SQL database. Refer how to create stored procedure in MSDN. https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure — SSD, Jan 13 '18 at 20:59

Steve · Accepted Answer · 2018-01-13T21:07:02.827

You need to start your Sql Server Management Studio, select your database and open a new query editor. Then you could paste the code that creates the StoredProcedure

CREATE PROCEDURE [dbo].[createEmployee]
    @name nvarchar(200),
    @surname nvarchar(200),
    @phone nvarchar(100),
    @gender nvarchar(1),
    @salary decimal(18,2),
    @country nvarchar(100)
AS
INSERT INTO employees (name, surname, phone, gender, salary, country) 
     VALUES (@name, @surname, @phone, @gender, @salary, @country)

This, of course is a bit arbitrary because I don't know neither the exact names of your columns neither the exact datatype and size of the text columns.

Note that while storedprocedure offers better performances and allows a DBA to optimize and secure the database access they often create an administrative burden that you should consider when adopting them for your solution.
For example, if your customers doesn't have a skilled IT team, you could get in trouble when you need to update the database just because you have added a new field to a table. If well written, code to manage CRUD operations with sql text embedded could be less troublesome to maintain.

score 1 · Answer 2 · answered Jan 13 '18 at 21:01

You can use Sql DDL to insert into your table, you dont need to use a stored procedure for that:(code approximately)

using (SqlConnection mConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
    {
        using (SqlCommand mCommand = new SqlCommand(@"

            INSERT INTO _input_your_tables_name_here 
                        (name, surname, phone, gender, salary, country) 
                 VALUES (@name, @surname, @phone, @gender, @country)", mConnection))

        {

            // mCommand.CommandType = CommandType.StoredProcedure;

            mCommand.Parameters.AddWithValue("@name", Name);
            mCommand.Parameters.AddWithValue("@surname", Surname);
            mCommand.Parameters.AddWithValue("@phone", Phone);
            mCommand.Parameters.AddWithValue("@gender", Gender);
            mCommand.Parameters.AddWithValue("@salary", Salary);
            mCommand.Parameters.AddWithValue("@country", Country);

            try
            {
                mConnection.Open(); // should net be needed, as using takes care of it
                mCommand.ExecuteNonQuery(); // should return 1 for 1 row created
                return "success";
            }
            catch (SqlException e)
            {
                return e.Message.ToString();
            } 
        }
    }

Having a procedure simplifies it, and if you ever adapt the SP to do other things as well you do not need to recompile your code unless you change new non-default parameters into your SP.

c# .net How to use a stored procedure?

2 Answers2