Bom dia, tenho uma stored procedure que executada individualmente funciona, porém quando a executo através do c#, ao passar pela linha
cmd.ExecuteNonQuery();
ela me retorna 0, sendo que deveria retornar o valor do id inserido.
SP
USE [CRM_Desenv]
GO
/****** Object: StoredProcedure [dbo].[SP_ScannerEmailInserir] Script Date: 02/05/2013 19:33:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_ScannerEmailInserir]
(
@T0013_NOME varchar(50),
@T0013_SERVIDOR varchar(50),
@T0013_CONTA varchar(50),
@T0013_USUARIO varchar(50),
@T0013_SENHA varchar(50),
@T0013_PORTA int,
@T0013_TSSL int,
@T0013_CRIARTICKET int,
@T0013_REPOSTA_DE varchar(50),
@T0013_REPOSTA_PARA varchar(50),
@T0013_REPPOSTA_CC varchar(50),
@T0013_REPOSTA_CCO varchar(50),
@T0013_REPOSTA_CORPO varchar(max),
@T0013_REPOSTA_STATUS int,
@T0013_REPOSTA_ASSUNTO varchar(50),
@T0013_ID bigint = null
)
AS
DECLARE @IDINSERIDA BIGINT
BEGIN TRY
IF(@T0013_ID IS NULL)/*Caso não encontre nenhum registro com o ID informado o sistema retorna o ID [0], neste caso incluir o bloco*/
BEGIN
PRINT('Incluir Registro')
INSERT INTO T0013_SCANNERDEEMAIL(
T0013_NOME,
T0013_SERVIDOR,
T0013_CONTA,
T0013_USUARIO,
T0013_SENHA,
T0013_PORTA,
T0013_TSSL,
T0013_CRIARTICKET,
T0013_REPOSTA_DE,
T0013_REPOSTA_PARA,
T0013_REPOSTA_CC,
T0013_REPOSTA_CCO,
T0013_REPOSTA_CORPO,
T0013_STATUS,
T0013_REPOSTA_ASSUNTO
)VALUES(
@T0013_NOME,
@T0013_SERVIDOR,
@T0013_CONTA,
@T0013_USUARIO,
@T0013_SENHA,
@T0013_PORTA,
@T0013_TSSL,
@T0013_CRIARTICKET,
@T0013_REPOSTA_DE,
@T0013_REPOSTA_PARA,
@T0013_REPPOSTA_CC,
@T0013_REPOSTA_CCO,
@T0013_REPOSTA_CORPO,
@T0013_REPOSTA_STATUS,
@T0013_REPOSTA_ASSUNTO
)
SET @IDINSERIDA = SCOPE_IDENTITY()
print (@IDINSERIDA)
SELECT @IDINSERIDA
RETURN @IDINSERIDA
END
ELSE
BEGIN
PRINT('Atualizar Registro')
UPDATE T0013_SCANNERDEEMAIL
SET
T0013_NOME = @T0013_NOME,
T0013_SERVIDOR = @T0013_SERVIDOR,
T0013_CONTA = @T0013_CONTA,
T0013_USUARIO = @T0013_USUARIO,
T0013_SENHA = @T0013_SENHA,
T0013_PORTA = @T0013_PORTA,
T0013_TSSL = @T0013_TSSL,
T0013_CRIARTICKET = @T0013_CRIARTICKET,
T0013_REPOSTA_DE = @T0013_REPOSTA_DE,
T0013_REPOSTA_PARA = @T0013_REPOSTA_PARA,
T0013_REPOSTA_CC = @T0013_REPPOSTA_CC,
T0013_REPOSTA_CCO = @T0013_REPOSTA_CCO,
T0013_REPOSTA_CORPO = @T0013_REPOSTA_CORPO,
T0013_STATUS = @T0013_REPOSTA_STATUS,
T0013_REPOSTA_ASSUNTO = @T0013_REPOSTA_ASSUNTO
WHERE T0013_ID = @T0013_ID
SET @IDINSERIDA = @T0013_ID
SELECT @IDINSERIDA
RETURN @IDINSERIDA
END
END TRY
BEGIN CATCH
print ''
print 'Ocorreu um erro!'
print 'Mensagem: ' + ERROR_MESSAGE()
print 'Procedure: ' + ERROR_PROCEDURE()
END CATCH
C#
public int Inserir(ScannerDeEmailController ScannerEmail)
{
string ConnSTR = ConfigurationManager.AppSettings["ConnectionString"];
ConnSTR = Cript.Decrypt(ConnSTR);
SqlConnection conn = new SqlConnection(ConnSTR);
string sCommand = "SP_ScannerEmailInserir";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sCommand;
cmd.CommandType = CommandType.StoredProcedure;
//parametros de entrada
cmd.Parameters.Add(new SqlParameter("@T0013_NOME", SqlDbType.Text)).Value = ScannerEmail.NOME;
cmd.Parameters.Add(new SqlParameter("@T0013_SERVIDOR", SqlDbType.Text)).Value = ScannerEmail.SERVIDOR;
cmd.Parameters.Add(new SqlParameter("@T0013_CONTA", SqlDbType.Text)).Value = ScannerEmail.CONTA;
cmd.Parameters.Add(new SqlParameter("@T0013_USUARIO", SqlDbType.Text)).Value = ScannerEmail.USUARIO;
cmd.Parameters.Add(new SqlParameter("@T0013_SENHA", SqlDbType.Text)).Value = ScannerEmail.SENHA;
cmd.Parameters.Add(new SqlParameter("@T0013_PORTA", SqlDbType.Int)).Value = ScannerEmail.PORTA;
if (ScannerEmail.TSSL == true) cmd.Parameters.Add(new SqlParameter("@T0013_TSSL", SqlDbType.Int)).Value = 1;
else cmd.Parameters.Add(new SqlParameter("@T0013_TSSL", SqlDbType.Int)).Value = 0;
cmd.Parameters.Add(new SqlParameter("@T0013_CRIARTICKET", SqlDbType.Int)).Value = ScannerEmail.CRIARTICKET;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_DE", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_DE;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_PARA", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_PARA;
cmd.Parameters.Add(new SqlParameter("@T0013_REPPOSTA_CC", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CC;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_CCO", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CCO;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_CORPO", SqlDbType.Text)).Value = ScannerEmail.REPOSTA_CORPO;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_STATUS", SqlDbType.Int)).Value = ScannerEmail.REPOSTA_STATUS;
cmd.Parameters.Add(new SqlParameter("@T0013_REPOSTA_ASSUNTO", SqlDbType.Int)).Value = ScannerEmail.REPOSTA_ASSUNTO;
if (ScannerEmail.ID != 0)
{
cmd.Parameters.Add(new SqlParameter("@T0013_ID", SqlDbType.BigInt)).Value = ScannerEmail.ID;
}
//parametros de saida
var parametroSaida = cmd.Parameters.Add("@IDINSERIDA", SqlDbType.Int);
parametroSaida.Direction = ParameterDirection.ReturnValue;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
return Convert.ToInt32(parametroSaida.Value);
}
O return Convert.ToInt32(parametroSaida.Value); sempre tem sido zero, alguem sabe me dizer o porque?
