Executing a SQL Server stored procedure from C#

Question

I have problems executing my stored procedure in C# console application and I don't know what the problem is. Could you please take a look?

string path="";

StringBuilder sb = new StringBuilder();
StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:\n");

try
{ 
    SqlConnection conn = new SqlConnection("Data Source=DESKTOP-M3IMRLE\\SQLEXPRESS; Initial Catalog = db2; Integrated security=true");

    Console.WriteLine("Enter path : ");
    path = Console.ReadLine();
    conn.Open();

    SqlCommand cmd = new SqlCommand();

    SqlCommand command = new SqlCommand("EXECUTE main.mainproc @path='" + path + "'", conn);

    if(command!=null)
    { 
        Console.WriteLine("JSON loaded");
    }  

    conn.Close();
}
catch(SqlException ex)
{
    sqlErrorMessages.AppendFormat("Message: {0}\n", ex.Message);
}

First thing I notice, you never execute your command. You also declare cmd and sb without utilizing them, they can probably be removed. You should probably declare the command's CommandText to be the name of your Stored Procedure, and set the CommandType to CommandType.StoredProcedure, then add the path value as a SqlParameter to the command. — Jonathon Chase
– Jonathon Chase, Commented May 24, 2018 at 22:47

marc_s · Accepted Answer · 2018-05-25 04:30:14Z

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)

Glen Thomas · Accepted Answer · 2018-05-24 22:47:42Z

0

You haven't called ExecuteNonQuery on your SqlCommand

command.ExecuteNonQuery()

answered May 24, 2018 at 22:47

Glen Thomas

10.8k5 gold badges40 silver badges71 bronze badges

Comments

hiren65 · Accepted Answer · 2022-07-22 04:43:17Z

0

 public DataSet GetDataSet(SqlConnection connection, string storedProcName, params SqlParameter[] parameters)
    {
        var command = new SqlCommand(storedProcName, connection) { CommandType = CommandType.StoredProcedure };
        command.Parameters.AddRange(parameters);

        var result = new DataSet();
        var dataAdapter = new SqlDataAdapter(command);
        dataAdapter.Fill(result);

        return result;
    }

answered Jul 22, 2022 at 4:43

hiren65

111 bronze badge

1 Comment

ahuemmer Over a year ago

Please don't post code-only answers but add a little textual explanation about how and why your approach works and what makes it different from the other answers given. You may also have a look at our "How to write a good answer" entry.

Collectives™ on Stack Overflow

Executing a SQL Server stored procedure from C#

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related