C # with Storeprocedure

Question

C # with Storeprocedure

Navigation

#1 by (1 votes)

0

Good evening guys I'm doing a system for college and I'm trying to do with the procedure but it's not recording in the bank I think there's something wrong but I do not think so. I tried to use CM.Parameters.AddWithValue and CM.Parameters .Add more did not work. follow 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 changed 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;

        }
    }

sql-server c# stored-procedures

asked by anonymous 28.03.2016 / 04:31

1 answer

How to Validate a Form with Hidden Fields Picker View - Display an ImageView according to the sum of values

score 1 · Accepted Answer

The problem is with the procedure call ...

Do not use exec when you want to call procedures from your application ...

Only call the procedure:

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