Return a message from a stored procedure to C# app

Question

I have a stored procedure that adds a user and at each permission I add, I want to start building a success message.

My stored procedure runs fine but how do I get that success message back into a message dialog in my app?

I want to display the below @text in a messagebox in my C# app.

DECLARE @text NVARCHAR(1000)
SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
SELECT @text

This is my call in my C# app:

    public DataTable CreateOrDropUser(string dataBase, string procedure, SqlParameter[] parameters)
    {
       try
       {
          if (dataBase.Length > 0) { procedure = dataBase + ".." + procedure; } //Set procedure to DBNAME..ProcedureName

          SqlCommand cmd1 = new SqlCommand(procedure, con);
          cmd1.CommandType = CommandType.StoredProcedure;

          foreach (SqlParameter p in parameters)
          {
              if (p != null)
              {
                  cmd1.Parameters.Add(p);
              }
          }

          con.Open();
          DataTable dt = new DataTable();
          SqlDataAdapter da = new SqlDataAdapter(cmd1);
          da.Fill(dt);
          con.Close();

          MessageBox.Show("Success"); //This should display the @text variable in my proc

          return dt;      
     }
     catch (Exception ex)
     {
        try
        {
           if (con.State == ConnectionState.Open)
           {
              con.Close();
           }
        }
        catch
        {
           MessageBox.Show("Could not connect to database. Check settings. " + ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        MessageBox.Show(ex.Message);
        return null;
      }      
    }

My Stored proc, Just focus on sections by all the prints, that's the text i'm adding:

ALTER PROCEDURE [dbo].[AdminDevUserCreate]
    @SQLLoginName varchar(50),
    @SQLLoginPass varchar(50) 
AS


DECLARE @text NVARCHAR(1000)OUTPUT  

--PRINT 'Create SQL Login'
SET @text = 'Create SQL Login ' + @SQLLoginName 
-- USE [Master]

EXEC(' USE [master] CREATE LOGIN [' + @SQLLoginName + '] WITH PASSWORD=''' + @SQLLoginPass + ''', DEFAULT_DATABASE=[TestAudit], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF')

--PRINT 'Add Server Roles'
SET @text += + CHAR(13)+CHAR(10) + 'Add Server Roles'
--Add Server roles
    EXEC master..sp_addsrvrolemember @loginame = @SQLLoginName, @rolename = N'bulkadmin'
    EXEC master..sp_addsrvrolemember @loginame = @SQLLoginName, @rolename = N'processadmin'
    EXEC master..sp_addsrvrolemember @loginame = @SQLLoginName, @rolename = N'securityadmin'   
--PRINT 'Allow SQL Agent Job Manage'
SET @text += + CHAR(13)+CHAR(10) + 'Allow SQL Agent Job Manage'
--USE [MSDB]
EXEC ('msdb..sp_addrolemember ''SQLAgentOperatorRole'', ''' + @SQLLoginName + '''')

--PRINT 'Allow Trace'
SET @text += + CHAR(13)+CHAR(10) + 'Allow Trace'
--Allow trace (SQL Profiler)
--USE [MASTER]
EXEC (' USE [MASTER] GRANT ALTER TRACE TO ' + @SQLLoginName )

--PRINT 'Prevent admin proc changes '
SET @text += + CHAR(13)+CHAR(10) + 'Prevent admin proc changes '
    EXEC ('USE [TestAudit] DENY ALTER ON [TestAudit].[dbo].[Admin] TO ' + @SQLLoginName) --Prevents changes to Admin function
--PRINT 'Prevent database trigger changes'
SET @text += + CHAR(13)+CHAR(10) + 'Prevent database trigger changes'
    EXEC ('USE [TestAudit] DENY ALTER ANY DATABASE DDL TRIGGER TO ' + @SQLLoginName) --Prevents modify of [SchemaAuditTrigger]

PRINT @text
Select @text

Try `MessageBox.Show(dt.Rows[0][0].ToString());` – KV Prajapati Jun 29 '12 at 10:52 — KV Prajapati, Jun 29 '12 at 10:52

Manatherin · Accepted Answer · 2012-06-29T12:17:38.927

Your best bet is to use a output parameter.

In your stored procedure add the parameter @text nvarchar(1000) OUTPUT then in your code add an extra parameter with the name @text and set the parameter direction to output.

then just add the line SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.' in your stored procedure

Edit: My answer is if you don't want this to affect your current query, if i misinterpreted your question please let me know. Also to get the value, after you execute the query you can get the value from the @name parameter using .Value

Edit 2: Example Code Should look something like

//Add these lines
SqlParameter text = new SqlParameter("@name", SqlDbType.NVarChar);
text.Direction = ParameterDirection.Output;
cmd1.Parameters.Add(text);

con.Open();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd1);
da.Fill(dt);
con.Close();

//Change this line
MessageBox.Show(text.Value); //This should display the @text variable in my proc

if you need help with the stored procedure please post it and i'll give a example with that too

Edit 3: Quick example Tested with a quick example. The C# code:

        using (SqlConnection connection = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=TestDB;Integrated Security=True"))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "Test";

                SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000);
                text.Direction = ParameterDirection.Output;
                command.Parameters.Add(text);

                using (DataTable dt = new DataTable())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        da.Fill(dt);
                    }
                }

                Trace.WriteLine(text.Value);

                connection.Close();
            }
        }

The Stored Procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Test
    @Text Nvarchar(1000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @Text = 'test'
END
GO

Worked fine for me if you want to check it against yours for differences

Edit 4: In your stored procedure the @text needs to be a parameter so instead of

ALTER PROCEDURE [dbo].[AdminDevUserCreate]
    @SQLLoginName varchar(50),
    @SQLLoginPass varchar(50) 
AS

DECLARE @text NVARCHAR(1000)OUTPUT

make it

ALTER PROCEDURE [dbo].[AdminDevUserCreate]
    @SQLLoginName varchar(50),
    @SQLLoginPass varchar(50),
    @text NVARCHAR(1000) OUTPUT 
AS

also when creating the SqlParameter use

SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000);

which should get rid of the size issue as you are telling it that the parameter is NVARCHAR(1000)

the line

PRINT @text
Select @text

shouldn't be needed

How would i add that parameter directed to output? I added @text nvarchar(1000) OUTPUT, now how to i get my messagebox to display it? — Pomster, Jun 29 '12 at 10:58
@Pommy can't remember if it's done differently with SqlAdapters but i think the code posted should work, please let me know if it doesn't — Manatherin, Jun 29 '12 at 11:03
I got this message, Object reference not set to an instance of an object. — Pomster, Jun 29 '12 at 11:04
@Pommy Also you should look at wrapping your SqlConnection, SqlCommand, DataTable, SqlDataAdapter, etc. in using statements to protect against memory leaks (see http://msdn.microsoft.com/en-us/library/yh598w02.aspx) — Manatherin, Jun 29 '12 at 11:04
I added the messagebox as follows MessageBox.Show("Success" + text.Value); and my message was just success. — Pomster, Jun 29 '12 at 11:06
@Pommy Seems to work for me, 2 things to check, 1 change the declaration to `SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000);` 2 are you calling the `cmd1.Parameters.Add(text);` — Manatherin, Jun 29 '12 at 11:17
I keep getting this message show: String[2]: the Size Property has an invalid size of0. — Pomster, Jun 29 '12 at 12:05
Thanks so much for all you time and help :) i have it working now :D — Pomster, Jun 29 '12 at 12:20

Remus Rusanu · Answer 2 · 2012-06-29T11:59:18.790

Informational messages can be retrieved from SqlConnection.InfoMessage event. They can be raised in T-SQL code using RAISERROR with severity 0-9.

 public DataTable CreateOrDropUser(string dataBase, string procedure, SqlParameter[] parameters)
    {
        SqlconnecitonStringBuilder scsb = new SqlConnectionStringBuilder (connstring);
        scsb.Database = database;
        using (SqlConnection con = new SqlConnection (scsb.ConnectionString))
        {
            StringBuilder sb = new StringBuilder ();
            con.Open ();

            SqlCommand cmd1 = new SqlCommand(procedure, con);
            cmd1.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter p in parameters)
            {
                if (p != null)
                {
                    cmd1.Parameters.Add(p);
                }
            }

            conn.InfoMessage += (args => 
            {
              sb.AppendLine (args.Message);
            });
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd1);
            da.Fill(dt);
            MessageBox.Show(sb);
            return dt;      
        }
    }

And in T-SQL:

RAISERROR(N'This is line %d', 0,0,1);
RAISERROR(N'This is line %d', 0,0,2);

score 2 · Answer 3 · answered Jun 29 '12 at 10:52

If you are using MVC then there is an excellent sample here. I have used it in multiple projects and found it fantastic.

Also you should really be wrapping your SqlCommand and SqlConnection object in using statements to stop leaky connections.

Note: this can be adapted to WebForms without too much trouble.

score 0 · Answer 4 · answered Jun 29 '12 at 10:53

0

MessageBox.Show(dt.Rows[0][0].Tostring());

or

Use a output parameter

answered Jun 29 '12 at 10:53

Asif

2,657
19
25

Return a message from a stored procedure to C# app

4 Answers4

Linked