How to Execute a Stored Procedure Within C# Program

Call a stored procedure with parameter in c#

It's pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd variable, and never use it. Here, however, you will use that instead of da.InsertCommand.

Also, use a using for all disposable objects, so that you are sure that they are disposed properly:

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

Executing a SQL Server stored procedure from C#

You could execute a stored procedure giving its name to the SqlCommand constructor and flagging the CommandType as a stored procedure.

Parameters are loaded in the Parameters collection of the command:

SqlCommand cmd = new SqlCommand("mainproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@path", SqlDbType.NVarChar).Value = path;
cmd.ExecuteNonQuery();

The final call to ExecuteNonQuery runs your stored procedure, but it is intended for procedures that runs an INSERT/UPDATE or DELETE commands, or in other words, commands that don't return data.

If your stored procedure is expected to return one or more records then you need more code:

....
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{
   // Get data from the first field of the current record assuming it is a string
   string data = reader[0].ToString();
}

The ExecuteReader method starts retrieving your data with the Read call and then continue until there are records to read. Inside the loop you can retrieve the fields data indexing the reader instance (and converting the object value to the appropriate type)

Execute Stored Procedure in .NET Core 3.1

To use your sproc as-is, you should probably hook up to the output parameter. Note, that this doesn't really use* EF Core for anything more than "something that generates a SqlConnection/command":

string user = "bob";
string password = Convert.ToBase64tring(Sha256Hash("secret", "mmmmsalty"));// you salt and hash your passwords, right??


using var cmd = _context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "[dbo].[LOGIN]";

//common
cmd.CommandType = CommandType.StoredProcedure;
if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();
cmd.Parameters.Add(new SqlParameter("@LOGIN", SqlDbType.VarChar) { Value = user });
cmd.Parameters.Add(new SqlParameter("@PASSWORD", SqlDbType.VarChar) { Value = password });
cmd.Parameters.Add(new SqlParameter("@EXIT", SqlDbType.Int) { Direction = ParameterDirection.Output });

cmd.ExecuteNonQuery();

var userExists = (int)cmd.Parameters["@EXIT"].Value == 1;

Remember to add necessary usings..

_{* (AFAIA, no method does; even methods that look like they use EF Core just grab its SQLBuilder and build a comamnd that calls the sproc)}

How to deploy stored procedure via C# code?

Workaround using dynamic SQL:

IF (OBJECT_ID('sp_InsertDevice', 'P') IS NOT NULL) 
    DROP PROCEDURE sp_InsertDevice 

EXEC(
'CREATE PROCEDURE sp_InsertDevice
    @serialNumber nvarchar(8),
    @modelName nvarchar(40),
    @userId int
AS
BEGIN
    INSERT INTO Device (SerialNumber, ModelName, UserID)
    VALUES (@serialNumber, @modelName, @userId)

    SELECT CAST(SCOPE_IDENTITY() AS INT);
END');

db<>fiddle demo

---

Unfortunately you will have to double every ' inside stored procedure. T-SQL does not support here-strings i T-SQL yet.

C# program does not execute Stored Procedure

You didn't actually execute your command, you need to add the following line to execute it:

cmd.ExecuteNonQuery();
cn.Close();

It is also highly recommended to use the Using statement nowadays:

The purpose of the using statement is to provide a simpler way to
specify when the unmanaged resource is needed by your program, and
when it is no longer needed.

Calling stored procedure from code-behind

Soo, based on what you really want then your script can look different. However, you should really consider where you want to put your logic. Do you want your stored procedure to insert data or do you want C# to do it? Otherwise here is an example where your stored procedure is used for inserting data:

NOTE

My date is propberly different due to i live in another country - Just change the date back to your desired result and dateformat.

NOTE2

Consider making your createdON variable in your stored procedure as a date instead of nvarchar also.

SQL Code

    --Build table for test inserts
CREATE TABLE  dbo.testtable (
Name nvarchar(50),
Createdon date
)

--Insert dummy values to sa_test
INSERT INTO sa_test
values('Thomas','2017-10-10'),
('Hans','2017-12-25')

--Edit stored procedure to not just select but also insert data based on @createdOn variable
create procedure sampleone (@createdOn nvarchar(200))
as
begin

insert into testtable (Name,createdon)
    select * 
    from sa_test
    where createdon= @createdOn
end

C# Code

 string conn = "Server=EGC25199;Initial Catalog=LegOgSpass;Integrated Security=True";
 string query = "sampleone";

 SqlConnection connDatabase = new SqlConnection(conn);
 connDatabase.Open();

 SqlCommand cmd = new SqlCommand(query, connDatabase);
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.Add("@createdOn", SqlDbType.Date).Value = "2017-10-10"; 
 cmd.ExecuteNonQuery();
 connDatabase.Close();

C# Code with datatable and print to console

 string conn = "Server=EGC25199;Initial Catalog=LegOgSpass;Integrated Security=True";
 string query = "sampleone";

        DataTable dt = new DataTable();
 SqlConnection connDatabase = new SqlConnection(conn);
 connDatabase.Open();

 SqlCommand cmd = new SqlCommand(query, connDatabase);
        using (var da = new SqlDataAdapter(cmd)) 
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@createdOn", SqlDbType.Date).Value = "2017-10-10"; //Adding the selected items to parameter @check
            cmd.ExecuteNonQuery();
            da.Fill(dt);


            foreach( DataRow row in dt.Rows)
            {
                Console.WriteLine();
                for (int x = 0; x < dt.Columns.Count; x++)
                {
                    Console.Write(row[x].ToString() + " ");
                }
            }
        }

        connDatabase.Close();

Result with console print

Result

My testtable is empty at first

sa_test table has 2 values i can select

When i then run my script my testtable looks like this:

---

Related Topics