2

I am using the following code to run a stored procedure given some input from user. The stored procedure updates some tables if the input was valid and there isn’t going to be a duplicate record.
If the input is invalid or that record already exists, stored procedure returns a value of the OUT parameter that is assigned to variable message. How can I display the value of message on the screen? Messabebox would be ideal, but couldn’t find where should call it from. 

partial void MyStorProc_Inserting(MyStorProcOperation entity)
{
    using (SqlConnection connection = new SqlConnection())
    {
        string connMyDB = this.DataWorkspace.MyDB.Details.Name;
        connection.ConnectionString = ConfigurationManager.ConnectionStrings[connMyDB].ConnectionString;

        string proc = "[MyDB].[dbo].[szMyStorProc]";
        using (SqlCommand command = new SqlCommand(proc, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@Uname", entity.Uname));
            command.Parameters.Add(new SqlParameter("@BU", entity.BU));

            SqlParameter output = new SqlParameter();
            output.ParameterName = "@ResultDescription";
            output.SqlDbType = System.Data.SqlDbType.VarChar;
            output.Direction = ParameterDirection.Output;
            output.Size = 256;
            command.Parameters.Add(output);

            connection.Open();
            command.ExecuteNonQuery();

            string message = output.Value.ToString();
        }
    }

    this.Details.DiscardChanges();
}

stored procedure goes somewhat like this:

CREATE PROCEDURE [dbo].[szMyStorProc] 
-- Add the parameters for the stored procedure here
@Uname varchar(50) = '', 
@BU varchar(50) = '',
@ResultDescription varchar(256)= ''  OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @EmployeeID INT
DECLARE @BUID INT
SELECT @EmployeeID = ID FROM tEmployee WHERE Uname = @Uname
IF @EmployeeID IS NULL 
BEGIN
    SELECT @ResultDescription = 'User name not found'
    PRINT @ResultDescription
END
ELSE
        ... 
        ...
    END
GO
Mo Patel
  • 2,321
  • 4
  • 22
  • 37
Alice
  • 21
  • 1
  • If anything I'd modify your `MyStorProc_Inserting` to return the status from the stored proc. That will lead to cleaner code by separating the UI from the worker stuff. – Peter M Nov 19 '13 at 23:12
  • @PeterM that's a good idea will need to do that! – Alice Nov 19 '13 at 23:26
  • found a good starting point here: [Passing Result Parameters from a Stored Procedure at the end](http://blogs.msdn.com/b/eric_erhardt/archive/2012/04/17/executing-a-simple-stored-procedure-in-lightswitch.aspx) `code`entity.ResultDescription = message; – Alice Nov 19 '13 at 23:31
  • Anyone got an example of the code on getting the output param back, I tried setting the return value to string and returning it, but get error saying a partial method must return void. @Alice, where exactly would I put this statement you suggested? – mark1234 Mar 20 '14 at 14:12

0 Answers0