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();
            }

        }
    }

}

Answer 1

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.

Answer 2

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.