C #의 저장 프로 시저에서 반환 값 가져 오기

C #의 저장 프로 시저에서 반환 값 가져 오기

---

다음 쿼리가 있습니다.

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

이것은 완벽하게 잘 컴파일됩니다.

C #에서는이 쿼리를 실행하고 반환 값을 얻고 싶습니다.

내 코드는 다음과 같습니다.

  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;

참고 : 코드를 짧게 유지하기 위해 예외 처리가 중단되었습니다. 마지막 줄에 도달 할 때마다 null이 반환됩니다. 이 코드의 논리 오류는 무엇입니까?

감사

---

Mehrdad는 몇 가지 좋은 점을 지적했지만 내가 주목 한 가장 중요한 것은 쿼리를 실행하지 않는다는 것입니다 ...

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

---

retval.Direction = ParameterDirection.Output;

ParameterDirection.ReturnValue출력 매개 변수가 아닌 프로 시저의 "반환 값"에 사용되어야합니다. SQL RETURN문에서 반환 된 값을 가져옵니다 (매개 변수 이름이 @RETURN_VALUE).

RETURN @b당신 대신에SET @b = something

그런데 반환 값 매개 변수는 항상 int문자열 이 아닙니다 .

---

반환 값에 많은 문제가 있었기 때문에 결국에는 항목을 선택했습니다.

해결책은 마지막에 결과를 선택하고 함수에 쿼리 결과를 반환하는 것입니다.

제 경우에는 존재 확인을하고있었습니다.

IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE @RoleName = RoleName)) 
    SELECT 1
ELSE
    SELECT 0

그때

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "RoleExists";
    return (int) cmd.ExecuteScalar()
}

int 대신 문자열 값으로 동일한 작업을 수행 할 수 있어야합니다.

---

이 구축되어 조엘의 와 메흐 다드의 답변 : 당신은 결코의 매개 변수, 바인딩하고 retval받는 사람을 sqlcommand. 당신은

sqlcomm.Parameters.Add(retval);

명령을 실행하고 있는지 확인하려면

sqlcomm.ExecuteNonQuery();

2 개의 반환 값 문자열 ( returnValue및 retunvalue) 이있는 이유도 잘 모르겠습니다 .

---

당신은 당신의 SQL이 잘 컴파일된다고 말했지만, 나는 다음과 같이 얻는다 : 스칼라 변수 "@Password"를 선언해야한다.

또한 저장 프로 시저에서 varchar (@b)를 반환하려고하지만 SQL Server 저장 프로시 저는 정수만 반환 할 수 있습니다.

절차를 실행할 때 오류가 발생합니다.

'varchar 값'x '를 데이터 유형 int로 변환 할 때 변환이 실패했습니다.'

---

select 문없이 저장 프로 시저에서 값을 반환 할 때. 값을 얻으려면 "ParameterDirection.ReturnValue"및 "ExecuteScalar"명령을 사용해야합니다.

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

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);
        }

위의 c # 함수를 사용하여 SP "IsEmailExists"의 반환 값을 얻습니다.

---

이 SP는 매우 이상해 보입니다. @b에 전달되는 내용은 수정하지 않습니다. 그리고 SP의 어디에도 @b에 아무것도 할당하지 않습니다. @Password가 정의되어 있지 않으므로이 SP는 전혀 작동하지 않습니다.

실제로 @Password를 반환하거나 SET @b = (SELECT ...)

SP를 다음과 같이 수정하면 훨씬 간단 해집니다 (OUTPUT 매개 변수 없음).

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

그런 다음 코드에서 cmd.ExecuteScalar를 사용하고 결과를받을 수 있습니다.

---

이것이 도움이 될 수 있습니다.

데이터베이스 스크립트 :

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

애플리케이션 구성 파일 :

<?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>

데이터 액세스 계층 (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();


        }


    }
}

비즈니스 로직 계층 (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;

        }

    }
}

구현 :

BLL.BLL.InsertUser("@lastid",new SqlParameter("neuname","Ded"),
                 new SqlParameter("neupassword","Moro$ilka"),
                 new SqlParameter("neuposition","Moroz")
                 );

---

여기에는 여러 가지 문제가 있습니다.

1. 불가능합니다. varchar를 반환하려고합니다. 저장 프로 시저 반환 값은 정수 식만 될 수 있습니다. 공식 RETURN 문서를 참조하십시오 : https://msdn.microsoft.com/en-us/library/ms174998.aspx .
2. 당신 sqlcomm은 처형되지 않았습니다. sqlcomm.ExecuteNonQuery();명령을 실행 하려면 호출 해야합니다.

---

다음은 OUTPUT 매개 변수를 사용하는 솔루션입니다. 이것은 다음으로 테스트되었습니다.

• 윈도우 서버 2012
• .NET v4.0.30319
• C # 4.0

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;
}

---

반환 값과 출력 변수의 개념을 혼합했습니다. 1- 출력 변수 :

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();

---

이 문제를 해결해야 할 두 가지가 있습니다. 먼저 출력 (return 아님) 매개 변수에 값을 저장하도록 저장 프로 시저를 설정합니다.

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

이것은 @b에 대한 암호이며 반환 매개 변수로 가져옵니다. 그런 다음 C #에서 가져 오려면 다음을 수행하십시오.

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();
     }

---

사용할 때

cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

그런 다음 저장 프로 시저가

return @RETURN_VALUE;

저장 프로 시저의 끝에.

---

당신이 통과해야 가정 Username및 Password에 저장 프로 시저 및 로그인이 성공 여부를 알 수 여부와 어떤 오류가 발생했는지 확인 저장 프로 시저 .

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;
}

연결 문자열 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>

그리고 여기 에 참조 용 저장 프로 시저 가 있습니다.

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

---

이 코드 줄은 SQL Server에서 Store StoredProcedure 반환 값을 반환합니다.

cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;                
cmd.ExecuteNonQuery();

Atfer 쿼리 실행 값이 SP에서 반환됩니다.

id = (int)cmd.Parameters["@id"].Value;

참고URL : https://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp