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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With