将消息从存储过程返回到 C# 应用程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11260418/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return a message from a stored procedure to C# app
提问by Pomster
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.
我想在我的 C# 应用程序的消息框中显示以下 @text。
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:
这是我在 C# 应用程序中的调用：
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
采纳答案by Manatherin
Your best bet is to use a output parameter.
最好的办法是使用输出参数。
In your stored procedure add the parameter @text nvarchar(1000) OUTPUTthen in your code add an extra parameter with the name @textand set the parameter direction to output.
在您的存储过程中添加参数，@text nvarchar(1000) OUTPUT然后在您的代码中添加一个带有名称的额外参数@text并将参数方向设置为output.
then just add the line SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'in your stored procedure
然后只需SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'在您的存储过程中添加该行
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 @nameparameter using .Value
编辑：我的回答是，如果您不希望这影响您当前的查询，如果我误解了您的问题，请告诉我。同样要获取值，在执行查询后，您可以@name使用 .Value从参数中获取值
Edit 2: Example CodeShould look something like
编辑 2：示例代码应该看起来像
//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 exampleTested with a quick example. The C# code:
编辑 3：快速示例使用快速示例进行测试。C# 代码：
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
编辑 4：在您的存储过程中，@text 需要是一个参数，而不是
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 时使用
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)
当您告诉它参数是 NVARCHAR(1000)
the line
线
PRINT @text
Select @text
shouldn't be needed
不应该需要
回答by Sudhakar B
Your data table dt has your text output. you can For eg
您的数据表 dt 有您的文本输出。你可以例如
MessageBox.Show("" + dt.Rows[0][0]);
回答by Kane
If you are using MVC then there is an excellent sample here. I have used it in multiple projects and found it fantastic.
如果您正在使用MVC则是一个很好的样本这里。我在多个项目中使用过它，发现它很棒。
Also you should really be wrapping your SqlCommandand SqlConnectionobject in using statements to stop leaky connections.
此外，您真的应该将您的SqlCommand和SqlConnection对象包装在 using 语句中以阻止泄漏连接。
Note: this can be adapted to WebForms without too much trouble.
注意：这可以适应 WebForms 而不会太麻烦。
回答by Asif
MessageBox.Show(dt.Rows[0][0].Tostring());
or
或者
Use a output parameter
使用输出参数
回答by Remus Rusanu
Informational messages can be retrieved from SqlConnection.InfoMessageevent. They can be raised in T-SQL code using RAISERRORwith severity 0-9.
可以从SqlConnection.InfoMessage事件中检索信息性消息。它们可以在 T-SQL 代码中使用RAISERROR严重性 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:
在 T-SQL 中：
RAISERROR(N'This is line %d', 0,0,1);
RAISERROR(N'This is line %d', 0,0,2);
