C# com Storeprocedure

Question

Good evening guys I’m making a system for college and I’m trying to do with Procedure more is not recording on the bench I think there’s something wrong but I don’t think what it is I tried to use the CM.Parameters.Addwithvalue and the CM.Parameters.Add more didn’t work. follows the bank code

    CREATE PROCEDURE uspDeptoPermissaoInserir 

@desc_deptoPerm varchar(20),
@dtCad_deptoPerm datetime,
@obs_deptoPerm varchar(max),
@cFunc_menu char(1),
@cFunc_incluir char(1),
@cFunc_alterar char(1),
@cHorario_menu char(1),
@cHorario_incluir char(1),
@cHorario_alterar char(1),
@cUser_menu char(1),
@cUser_incluir char(1),
@cUser_alterar char(1),
@histSal_menu char(1),
@histSal_incluir char(1),
@histSal_alterar char(1),
@alterSenha_menu char(1),
@alterSenha_incluir char(1),
@alterSenha_alterar char(1),
@cDepto_menu char(1),
@cDepto_incluir char(1),
@cDepto_alterar char(1),
@cCadVinc_menu char(1),
@cCadVinc_incluir char(1),
@cCadVinc_alterar char(1),
@cTipoFolha_menu char(1),
@cTipoFolha_incluir char(1),
@cTipoFolha_alterar char(1),
@cTribSind_menu char(1),
@cTribSind_incluir char(1),
@cTribSind_alterar char(1),
@cCaged_menu char(1),
@cCaged_incluir char(1),
@cCaged_alterar char(1),
@cFerias_menu char(1),
@cFerias_incluir char(1),
@cFerias_alterar char(1),
@contaBanco_menu char(1),
@contaBanco_incluir char(1),
@contaBanco_Alterar char(1),
@vincEmp_menu char(1),
@vincEmp_incluir char(1),
@vincEmp_alterar char(1)

AS 
BEGIN

INSERT INTO tbDepartamentoPerm
(
desc_deptoPerm,
dtCad_deptoPerm,
obs_deptoPerm, 
cFunc_menu,
cFunc_incluir,
cFunc_alterar,
cHorario_menu,
cHorario_incluir,
cHorario_alterar,
cUser_menu,
cUser_incluir,
cUser_alterar,
histSal_menu,
histSal_incluir,
histSal_alterar,
alterSenha_menu,
alterSenha_incluir,
alterSenha_alterar,
cDepto_menu,
cDepto_incluir,
cDepto_alterar,
cCadVinc_menu,
cCadVinc_incluir, 
cCadVinc_alterar, 
cTipoFolha_menu,
cTipoFolha_incluir,
cTipoFolha_alterar, 
cTribSind_menu, 
cTribSind_incluir, 
cTribSind_alterar,
cCaged_menu, 
cCaged_incluir, 
cCaged_alterar, 
cFerias_menu, 
cFerias_incluir,
cFerias_alterar, 
contaBanco_menu,
contaBanco_incluir, 
contaBanco_Alterar, 
vincEmp_menu, 
vincEmp_incluir, 
vincEmp_alterar
)
VALUES
(
@desc_deptoPerm,
@dtCad_deptoPerm,
@obs_deptoPerm, 
@cFunc_menu,
@cFunc_incluir,
@cFunc_alterar,
@cHorario_menu,
@cHorario_incluir,
@cHorario_alterar,
@cUser_menu,
@cUser_incluir,
@cUser_alterar,
@histSal_menu,
@histSal_incluir,
@histSal_alterar,
@alterSenha_menu,
@alterSenha_incluir,
@alterSenha_alterar,
@cDepto_menu,
@cDepto_incluir,
@cDepto_alterar,
@cCadVinc_menu,
@cCadVinc_incluir, 
@cCadVinc_alterar, 
@cTipoFolha_menu,
@cTipoFolha_incluir,
@cTipoFolha_alterar, 
@cTribSind_menu, 
@cTribSind_incluir, 
@cTribSind_alterar,
@cCaged_menu, 
@cCaged_incluir, 
@cCaged_alterar, 
@cFerias_menu, 
@cFerias_incluir,
@cFerias_alterar, 
@contaBanco_menu,
@contaBanco_incluir, 
@contaBanco_Alterar, 
@vincEmp_menu, 
@vincEmp_incluir, 
@vincEmp_alterar
)

SELECT @@IDENTITY AS Retorno

END

now follow the code of my modified application:

    public int IncluirDeptoPermissao(DeptoPermissaoDTO objDepto)
    {
        using (SqlConnection CON = new SqlConnection())
        {
            CON.ConnectionString = Properties.Settings.Default.bdHtSisCadFunc;

            using (var CM = new SqlCommand("uspDeptoPermissaoInserir", CON)
            {
                CommandType = CommandType.StoredProcedure
            }) { 
            CON.Open();

            CM.CommandType = CommandType.StoredProcedure;


            CM.Connection = CON;

            CM.Parameters.Add("desc_deptoPerm", SqlDbType.VarChar).Value = objDepto.DescDepto;
            ........               
            CM.Parameters.Add("vincEmp_alterar", SqlDbType.VarChar).Value = objDepto.VincEmpAlterar;


            int qtd = CM.ExecuteNonQuery();

            return qtd;

        }
    }

Answer 1

The problem is in the call of the trial...

Don’t use the exec when you want to call your application’s procedures...

Make only the Procedure call:

var connectionString = Properties.Settings.Default.bdHtSisCadFunc;
var retId = 0;

using (SqlConnection CON = new SqlConnection(connectionString))
using (var command = new SqlCommand("uspDeptoPermissaoInserir", conn) { 
                           CommandType = CommandType.StoredProcedure }) { 
        command.Parameters.Add("desc_deptoPerm", SqlDbType.VarChar).Value = objDepto.DescDepto;
        ...
        command.Parameters.Add("vincEmp_alterar", SqlDbType.VarChar).Value = objDepto.VincEmpAlterar;



        retId = command.ExecuteNonQuery();
        }
        return retId;
    }