How to Execute a SQL Server Stored Procedure from a C# Program?

The storage procedure is performed from the C# program

This article will explore how to perform storage procedures from the C# program. The storage procedure is a predefined database process, which can be used to perform specific database operations from the code.

Assuming the storage procedure called "test" in the SQL Server query window:

To perform this storage procedure from the C# program, please follow the steps below:

<code class="language-sql">CREATE PROCEDURE dbo.test AS
BEGIN
    DECLARE @command AS VARCHAR(1000), @i INT;
    SET @i = 0;
    WHILE @i < 10
    BEGIN
        SET @command = 'SELECT ' + CAST(@i AS VARCHAR(10));
        EXEC(@command);
        SET @i = @i + 1;
    END;
END;</code>

Introduce the necessary naming space:

Establish a connection with the database:

<code class="language-csharp">using System;
using System.Data;
using System.Data.SqlClient;</code>

Create a new SQLCOMMAND object and specify the storage procedure name:

<code class="language-csharp">SqlConnection conn = new SqlConnection("Server=(local);DataBase=master;Integrated Security=SSPI");
conn.Open();</code>

3. Use the ExecretenonQuery method to perform the storage procedure:

<code class="language-csharp">SqlCommand cmd = new SqlCommand("dbo.test", conn);
cmd.CommandType = CommandType.StoredProcedure;</code>

4. If necessary, deal with any abnormalities or verify the execution results.

Complete code:

<code class="language-csharp">int result = cmd.ExecuteNonQuery();</code>

5. Note: Unless the storage procedure is stored in a non -default architecture or database, the path of the storage script is not required in the connection string. In this case, you need to use a complete limited name to specify it (for example, [databasename]. [Schemaname]. [ProcedureName]).
This Revised Response Maintains The Image and Provides a More Concise and Clearer Expluting A Stored Procedure from C#. Procedure is also included and functional.

The above is the detailed content of How to Execute a SQL Server Stored Procedure from a C# Program?. For more information, please follow other related articles on the PHP Chinese website!