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
– Patrick Artner, Commented Jan 13, 2018 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
– Antonios Tsimourtos, Commented Jan 13, 2018 at 20:31
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
– Gian Paolo, Commented Jan 13, 2018 at 20:34
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
– Steve, Commented Jan 13, 2018 at 20:44

Steve · Accepted Answer · 2018-01-13 21:07:02Z

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.

Patrick Artner · Accepted Answer · 2018-01-13 21:01:05Z

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.

Collectives™ on Stack Overflow

c# .net How to use a stored procedure?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related