Executing a Stored Procedure from a WCF Service Method

Here is an example of a method that is used within a WCF Service to execute a Stored Procedure and parse the return value (a success/failure string) to a local variable which can be manipulated to display a friendly success or failure message.

public String fSave (string aParm1, string aParm2, 
                             string aParm3, Int32 aParm4)
{
    SqlConnection lSQLConn = null;
    SqlCommand lSQLCmd = new SqlCommand();
    string lsResponse = "";
    string connStr = "";
 
    connStr = 
    ConfigurationManager.ConnectionStrings["MyConnStr"].ConnectionString;
 
    try
    {
        // create and open a connection object
        lSQLConn = new SqlConnection(connStr);
        lSQLConn.Open();
        //The CommandType must be StoredProcedure if we are using an ExecuteScalar
        lSQLCmd.CommandType = CommandType.StoredProcedure;
        lSQLCmd.CommandText = "sp_YourSPName"; 
        lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
        lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
        lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
        lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
               
        lSQLCmd.Connection = lSQLConn;
        //Executes the SP and returns the single select output to a variable
        lsResponse = Convert.ToString(lSQLCmd.ExecuteScalar());
    }
    catch (Exception Exc)
    {
        return "Error: " + Exc.Message;                
    }
    finally
    {
        lSQLCmd.Dispose();
        lSQLConn.Close();
    }
 
    if (String.IsNullOrEmpty(lsResponse))
    {
        return "Error: Unspecified problem while adding task.";                
    }
    return lsResponse;
}

Now, let's review the above method. There are a few interesting things:
Once the connection is open, the SQLCommand has to be defined as a CommandType = CommandType.StoredProcedure. This signals the type of action we need to execute.
Next, we define the Stored Procedure to be executed by defining the CommandText="sp_YourSPName".
To get the return value, we set the string variable to the results of a SQL Command, ExecuteScalar().

But wait. What should we do if our Stored Procedure returns a result set rather than a single value?
Well, you need to make a slight change to the method, we need to add a DataAdapter and a DataSet to parse the result.
See below:

SqlConnection lSQLConn = null;
SqlCommand lSQLCmd = new SqlCommand();
//Declare a DataAdapter and a DataSet
SqlDataAdapter lDA = new SqlDataAdapter();
DataSet lDS = new DataSet();
 
//...Execution section
 
// create and open a connection object
lSQLConn = new SqlConnection(connStr);
lSQLConn.Open();
//The CommandType must be StoredProcedure if we are using an ExecuteScalar
lSQLCmd.CommandType = CommandType.StoredProcedure;
lSQLCmd.CommandText = "sp_YourSPName"; 
lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
 
lSQLCmd.Connection = lSQLConn;
//Fill the DataAdapter with a SelectCommand
lDA.SelectCommand = lSQLCmd;
lDA.Fill(lDS);

Hope this is helpful,
Will CodeProject