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

Answer 1

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)