I have the following query:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output
AS
SET @Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
RETURN @b
GO
This compiles perfectly fine. I want to execute this query and get the return value. My code is below:
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
Note: Exception handling cut to keep the code short. Everytime I get to the last line, null is returned. What's the logic error with this code?
This question is related to
c#
sql
sql-server
For .net core 3.0 and dapper:
If your stored procedure returns this:
select ID, FILE_NAME from dbo.FileStorage where ID = (select max(ID) from dbo.FileStorage);
Then in c#:
var data = (_dbConnection.Query<FileUploadQueryResponse>
("dbo.insertFile", whateverParameters, commandType: CommandType.StoredProcedure)).ToList();
var storedFileName = data[0].FILE_NAME;
var id = data[0].ID;
As you can see, you can define a simple class to help with retrieving the actual values from dapper's default return structure (which I found impossible to work with):
public class FileUploadQueryResponse
{
public string ID { get; set; }
public string FILE_NAME { get; set; }
}
I was having tons of trouble with the return value, so I ended up just selecting stuff at the end.
The solution was just to select the result at the end and return the query result in your functinon.
In my case I was doing an exists check:
IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE @RoleName = RoleName))
SELECT 1
ELSE
SELECT 0
Then
using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RoleExists";
return (int) cmd.ExecuteScalar()
}
You should be able to do the same thing with a string value instead of an int.
This Line of code returns Store StoredProcedure returned value from SQL Server
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
Atfer Execution of query value will returned from SP
id = (int)cmd.Parameters["@id"].Value;
Mehrdad makes some good points, but the main thing I noticed is that you never run the query...
SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
You say your SQL compiles fine, but I get: Must declare the scalar variable "@Password".
Also you are trying to return a varchar (@b) from your stored procedure, but SQL Server stored procedures can only return integers.
When you run the procedure you are going to get the error:
'Conversion failed when converting the varchar value 'x' to data type int.'
There are two things to fix about this. First set up the stored procedure to store the value in the output ( not return ) parameter.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output
AS
SET @b =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
RETURN
GO
This will but the password into @b and you will get it as a return parameter. Then to get it in your C# do this:
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar, 50);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = new SqlParameter("@b", SqlDbType.VarChar, 50);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.Parameters.Add(retval);
sqlcomm.ExecuteNonQuery();
SqlConn.Close();
string retunvalue = retval.Value.ToString();
}
retval.Direction = ParameterDirection.Output;
ParameterDirection.ReturnValue should be used for the "return value" of the procedure, not output parameters. It gets the value returned by the SQL RETURN statement (with the parameter named @RETURN_VALUE).
Instead of RETURN @b you should SET @b = something
By the way, return value parameter is always int, not string.
This is building on Joel's and Mehrdad's answers: you're never binding the parameter of the retval to the sqlcommand. You need a
sqlcomm.Parameters.Add(retval);
and to make sure you're running the command
sqlcomm.ExecuteNonQuery();
I'm also not sure why you have 2 return value strings (returnValue and retunvalue).
This SP looks very strange. It does not modify what is passed to @b. And nowhere in the SP you assign anything to @b. And @Password is not defined, so this SP will not work at all.
I would guess you actually want to return @Password, or to have SET @b = (SELECT...)
Much simpler will be if you modify your SP to (note, no OUTPUT parameter):
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Validate] @a varchar(50)
AS
SELECT TOP 1 Password FROM dbo.tblUser WHERE Login = @a
Then, your code can use cmd.ExecuteScalar, and receive the result.
When you use
cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
you must then ensure your stored procedure has
return @RETURN_VALUE;
at the end of the stored procedure.
Suppose you need to pass Username and Password to Stored Procedure and know whether login is successful or not and check if any error has occurred in Stored Procedure.
public bool IsLoginSuccess(string userName, string password)
{
try
{
SqlConnection SQLCon = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnector"].ConnectionString);
SqlCommand sqlcomm = new SqlCommand();
SQLCon.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.CommandText = "spLoginCheck"; // Stored Procedure name
sqlcomm.Parameters.AddWithValue("@Username", userName); // Input parameters
sqlcomm.Parameters.AddWithValue("@Password", password); // Input parameters
// Your output parameter in Stored Procedure
var returnParam1 = new SqlParameter
{
ParameterName = "@LoginStatus",
Direction = ParameterDirection.Output,
Size = 1
};
sqlcomm.Parameters.Add(returnParam1);
// Your output parameter in Stored Procedure
var returnParam2 = new SqlParameter
{
ParameterName = "@Error",
Direction = ParameterDirection.Output,
Size = 1000
};
sqlcomm.Parameters.Add(returnParam2);
sqlcomm.ExecuteNonQuery();
string error = (string)sqlcomm.Parameters["@Error"].Value;
string retunvalue = (string)sqlcomm.Parameters["@LoginStatus"].Value;
}
catch (Exception ex)
{
}
return false;
}
Your connection string in Web.Config
<connectionStrings>
<add name="SqlConnector"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Databasename;User id=yourusername;Password=yourpassword"
providerName="System.Data.SqlClient" />
</connectionStrings>
And here is the Stored Procedure for reference
CREATE PROCEDURE spLoginCheck
@Username Varchar(100),
@Password Varchar(100) ,
@LoginStatus char(1) = null output,
@Error Varchar(1000) output
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN
SET @Error = 'None'
SET @LoginStatus = ''
IF EXISTS(SELECT TOP 1 * FROM EMP_MASTER WHERE EMPNAME=@Username AND EMPPASSWORD=@Password)
BEGIN
SET @LoginStatus='Y'
END
ELSE
BEGIN
SET @LoginStatus='N'
END
END
END TRY
BEGIN CATCH
BEGIN
SET @Error = ERROR_MESSAGE()
END
END CATCH
END
GO
When we return a value from Stored procedure without select statement. We need to use "ParameterDirection.ReturnValue" and "ExecuteScalar" command to get the value.
CREATE PROCEDURE IsEmailExists
@Email NVARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(SELECT Email FROM Users where Email = @Email)
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END
in C#
GetOutputParaByCommand("IsEmailExists")
public int GetOutputParaByCommand(string Command)
{
object identity = 0;
try
{
mobj_SqlCommand.CommandText = Command;
SqlParameter SQP = new SqlParameter("returnVal", SqlDbType.Int);
SQP.Direction = ParameterDirection.ReturnValue;
mobj_SqlCommand.Parameters.Add(SQP);
mobj_SqlCommand.Connection = mobj_SqlConnection;
mobj_SqlCommand.ExecuteScalar();
identity = Convert.ToInt32(SQP.Value);
CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
}
return Convert.ToInt32(identity);
}
We get the returned value of SP "IsEmailExists" using above c# function.
The value you are trying to get is not a return value but an output parameter. You need to change parametere direction to Output.
SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.Output;
command.ExecuteNonquery();
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
There are multiple problems here:
sqlcomm was never executed. You have to call
sqlcomm.ExecuteNonQuery(); in order to execute your command.Here is a solution using OUTPUT parameters. This was tested with:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) OUTPUT
AS
BEGIN
DECLARE @b AS varchar(50) = (SELECT Password FROM dbo.tblUser WHERE Login = @a)
SELECT @b;
END
SqlConnection SqlConn = ...
var sqlcomm = new SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter output = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
ouput.Direction = ParameterDirection.Output;
sqlcomm.ExecuteNonQuery(); // This line was missing
returnValue = output.Value.ToString();
// ... the rest of code
} catch (SqlException ex) {
throw ex;
}
May be this will help.
Database script:
USE [edata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertNewUser](
@neuname NVARCHAR(255),
@neupassword NVARCHAR(255),
@neuposition NVARCHAR(255)
)
AS
BEGIN
BEGIN TRY
DECLARE @check INT;
SET @check = (SELECT count(eid) FROM eusers WHERE euname = @neuname);
IF(@check = 0)
INSERT INTO eusers(euname,eupassword,eposition)
VALUES(@neuname,@neupassword,@neuposition);
DECLARE @lastid INT;
SET @lastid = @@IDENTITY;
RETURN @lastid;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() as errline,
ERROR_MESSAGE() as errmessage,
ERROR_SEVERITY() as errsevirity
END CATCH
END
Application configuration file:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="conStr" value="Data Source=User\SQLEXPRESS;Initial Catalog=edata;Integrated Security=True"/>
</appSettings>
</configuration>
Data Access Layer (DAL):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class DAL
{
public static SqlConnection conn;
static DAL()
{
conn = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());
conn.Open();
}
}
}
Business Logic Layer(BLL):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DAL;
namespace BLL
{
public static class BLL
{
public static int InsertUser(string lastid, params SqlParameter[] coll)
{
int lastInserted = 0;
try
{
SqlCommand comm = new SqlCommand();
comm.Connection = DAL.DAL.conn;
foreach (var param in coll)
{
comm.Parameters.Add(param);
}
SqlParameter lastID = new SqlParameter();
lastID.ParameterName = lastid;
lastID.SqlDbType = SqlDbType.Int;
lastID.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(lastID);
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "InsertNewUser";
comm.ExecuteNonQuery();
lastInserted = (int)comm.Parameters[lastid].Value;
}
catch (SqlException ex)
{
}
finally {
if (DAL.DAL.conn.State != ConnectionState.Closed) {
DAL.DAL.conn.Close();
}
}
return lastInserted;
}
}
}
Implementation :
BLL.BLL.InsertUser("@lastid",new SqlParameter("neuname","Ded"),
new SqlParameter("neupassword","Moro$ilka"),
new SqlParameter("neuposition","Moroz")
);
You have mixed up the concept of the Return Value and Output variable. 1- Output Variable:
Database----->:
create proc MySP
@a varchar(50),
@b varchar(50) output
AS
SET @Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
C# ----->:
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;//This is optional because Input is the default
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter outputval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
outputval .Direction = ParameterDirection.Output//NOT ReturnValue;
string outputvalue = sqlcomm.Parameters["@b"].Value.ToString();
Source: Stackoverflow.com