如何从存储过程中获取SQL字符串结果并将其保存在c# Windows应用程序字符串变量中
本文关键字:字符串 存在 保存 Windows 变量 应用程序 结果 存储 存储过程 过程中 SQL | 更新日期: 2023-09-27 18:13:43
我有以下存储过程:
ALTER PROCEDURE [dbo].[ProcedureName]
@date NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @result nvarchar(500) -- this one should return string.
DECLARE @variable1 NVARCHAR(50)
set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from dbo.Table2))
DECLARE @variable2 NVARCHAR(50)
update dbo.Table1 set columnX = 1 where column1 not in (select column1 from dbo.Table2)
set @variable2 = @@ROWCOUNT
等等…它大概有200行脚本,至少有10-12个变量
之后我想得到这样的结果
'Hello,' +
'Some Text here' +
@date +': ' +
'Explaining text for variable1- ' + @variable1 +
'Updated rows from variable2 - ' + @variable2 +
'some other select count - ' + @variable3 +
'some other update rowcount - '+ @variable4
......
到现在为止,我能够得到这个与PRINT语句,但不能把它作为变量在我的c#代码,它是这样的:
public void Execute_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to execute the program?", "Confirm Start", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.No)
{
string connectionString = GetConnectionString(usernamePicker.Text, passwordPicker.Text);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.ProcedureName", connection))
{
connection.Open();
cmd.CommandText = "dbo.ProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = dateTimePicker1.Text;
SqlParameter result = cmd.Parameters.Add("@result", SqlDbType.VarChar);
result.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteScalar();
var resultout = (string)cmd.Parameters["@result"].Value;
connection.Close();
TextMessage.Text = dateTimePicker1.Text;
}
}
}
}
所有我得到的结果是0或NULL或等。我试图从SQL返回值与PRINT, return, SET, OUTPUT .......但似乎什么都不起作用。然而,从c#到SQL中获取变量似乎像儿童工作。什么好主意吗?
如果您想返回连接字符串作为输出,那么在过程结束时只需写入select @result
。请确保在此语句之前已将其连接起来。
这将返回你可以在c#代码中使用的字符串
将存储过程更改为:
ALTER PROCEDURE [dbo].[ProcedureName]
@date NVARCHAR(50),
@variable1 NVARCHAR(50) output,
@variable2 NVARCHAR(50) output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @result nvarchar(500) -- this one should return string.
set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from dbo.Table2))
update dbo.Table1 set columnX = 1 where column1 not in (select column1 from dbo.Table2)
set @variable2 = @@ROWCOUNT
并像这样修改代码:
SqlParameter result1 = cmd.Parameters.Add("@variable1", SqlDbType.VarChar);
result1.Direction = ParameterDirection.ReturnValue;
SqlParameter result2 = cmd.Parameters.Add("@variable2", SqlDbType.VarChar);
result2.Direction = ParameterDirection.ReturnValue;
假设我执行了这个过程
ALTER PROCEDURE [dbo].[ProcedureName]
@date NVARCHAR(50),
@result nvarchar(500) output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @variable1 NVARCHAR(50)
set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from Table2))
set @result = @variable1 + ' some text '
我想要"@result"从过程输出文本,并得到它与c#