CODEWITHSUNDEEP
c#sqlsql-server
finsters
finsters

Reputation: 177

Executing a SQL Server stored procedure from C#

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

Upvotes: 4

Views: 21238

Answers (3)

hiren65
hiren65

Reputation: 11

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

Upvotes: 0

Steve
Steve

Reputation: 216243

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)

Upvotes: 9

Glen Thomas
Glen Thomas

Reputation: 10744

You haven't called ExecuteNonQuery on your SqlCommand

command.ExecuteNonQuery()

Upvotes: 0

Related Questions