query and change procedures for DAO C#layer

Question

inclusion, alteration, achievement

Various procedures have been created for inclusion, amendment, consultation, data collection, etc. The procedures were created in SQL Server 2014. I need to access these procedures via database connection using C# in Visual Studio. A DAO layer has been created, and all methods for access to these procedures have been declared. The methods are empty... My question is about connection structuring, commands and adding parameters in code.

For example, for the Stp_ins_mvtocontasadtodevol I made the following code:

        SqlConnection sqlConnection1 = new SqlConnection("ConnectionString");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;

        cmd.CommandText = "STP_INS_MvtoContasAdtoDevol";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = sqlConnection1;

        cmd.Parameters.Add("MAD_ENC_Id", Integer).Value = "0";
        cmd.Parameters.Add("MAD_Meio_Recebto", varchar(30)).Value = "0";
        cmd.Parameters.Add("MAD_Adquirente", varchar(30)).Value = "0";
        cmd.Parameters.Add("MAD_TIP_ID_Dev", Integer).Value = "0";
        cmd.Parameters.Add("MAD_FIL_Id", Integer).Value = "0";
        cmd.Parameters.Add("MAD_TIP_Sigla", Char(3)).Value = "0";
        cmd.Parameters.Add("MAD_DthMovto", SmallDateTime).Value = "0";
        cmd.Parameters.Add("MAD_VlrMovto", Decimal(14,2)).Value = "0";
        cmd.Parameters.Add("MAD_QtdeParcelas", Integer).Value = "0";
        cmd.Parameters.Add("MAD_Obs", varchar(500)).Value = "0";

        sqlConnection1.Open();
        reader = cmd.ExecuteReader();
        sqlConnection1.Close();

I put 0. someone could help me because I also need to make reference to objects filling this data? ps.: this would be structure for inclusion, do not know if it is right. how would look for query, change etc?

Answer 1

Mariana, To isolate the DAO layer implements the only handling methods follows one of a dll type project where has a connection method adds parameters, performs manipulation to call the procedures, also executes example query :

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.Properties;

namespace DAL
{
  public class Acesso{

   private SqlConnection Conexao()
    {
        return new SqlConnection(Settings.Default.stringCoxecao);
    }


    private SqlParameterCollection sqlParameterCollection = new SqlCommand().Parameters;

    public void LimparParametros()
    {
        sqlParameterCollection.Clear();
    }

    public void AdicionarParametos(string nomeParametro, object valorParametro)
    {
        sqlParameterCollection.Add(new SqlParameter(nomeParametro,valorParametro));
    }

    public object ExecutarManipulacao(CommandType commandType, string StoreProcedureOuTexto)
    {

        try
        {
            SqlConnection sqlConnection = Conexao();
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandType = commandType;
            sqlCommand.CommandText = StoreProcedureOuTexto;
            sqlCommand.CommandTimeout = 7200;

            foreach (SqlParameter sqlParameter in sqlParameterCollection)
            {
                sqlCommand.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.Value));
            }
            return sqlCommand.ExecuteScalar();

        }
        catch (Exception ex)
        {

            throw new Exception(ex.Message);
        }

    }

    public DataTable ExecutaConsulta(CommandType commandType, string StoradeProcedureOuTText)
    {
        try
        {
            SqlConnection sqlConnection = Conexao();
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandType = commandType;
            sqlCommand.CommandText = StoradeProcedureOuTText;
            sqlCommand.CommandTimeout = 7200;

            foreach (SqlParameter sqlParameter in sqlParameterCollection)
            {
                sqlCommand.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.Value));
            }

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);

            return  dataTable;
        }
        catch (Exception ex )
        {

            throw new  Exception(ex.Message);
        }
    }
}
}

In the project I created another layer called DTO(transference object) and a layer called BLL. As the project was simple did a customer registration at DTO had the customer class and customer collection customer class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DTO
{
   public  class Cliente
{
    public int IdCliente { get; set; }
    public string Nome { get; set; }
    public DateTime DataNascimento { get; set; }
    public Boolean Sexo { get; set; }
    public decimal LimiteDeCompra { get; set; }
}
}

Class clientecolecao

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

 namespace DTO
 {
     public class ClienteColecao : List<Cliente>
    {
    }
 }

After that in the BLL layer used this two layers follows code of the BLL layer:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

using DAL;
using DTO;

namespace BLL
{
    public class ClienteBLL
{
    Acesso acesso = new Acesso();

      public string Inserir(Cliente cliente)
      {
         try
        {
            acesso.LimparParametros();
            acesso.AdicionarParametos("@Nome", cliente.Nome);
              acesso.AdicionarParametos("DataNascimento",cliente.DataNascimento);
            acesso.AdicionarParametos("@Sexo", cliente.Sexo);
            acesso.AdicionarParametos("LimiteCompra", cliente.LimiteDeCompra);
            string idCliente = acesso.ExecutarManipulacao(CommandType.StoredProcedure, "spInserir").ToString();

            return idCliente;

        }
        catch (Exception exception)
        {

            return exception.Message;
        }

    }

    public string Alterar(Cliente cliente)
    {
        try
        {
            acesso.LimparParametros();
            acesso.AdicionarParametos("@ID", cliente.IdCliente);
            acesso.AdicionarParametos("@Nome", cliente.Nome);
            acesso.AdicionarParametos("@DataNascimento", cliente.DataNascimento);
            acesso.AdicionarParametos("@Sexo", cliente.Sexo);
            acesso.AdicionarParametos("@LimiteCompra", cliente.LimiteDeCompra);

            string Idcliente = acesso.ExecutarManipulacao(CommandType.StoredProcedure, "spAlterar").ToString();

            return Idcliente;
        }
        catch (Exception excepcion)
        {

            return excepcion.Message;
        }

    }

    public string Excluir(Cliente cliente)
    {

        try
        {
            acesso.LimparParametros();
            acesso.AdicionarParametos("@ID", cliente.IdCliente);
            string IdCliente = acesso.ExecutarManipulacao(CommandType.StoredProcedure, "spExcluir").ToString();

            return IdCliente;
        }
        catch (Exception ex)
        {

           return ex.Message;
        }

    }

    public ClienteColecao ConsultaPorNome(string nome)
    {

        try
        {
            ClienteColecao clientCollect = new ClienteColecao();

            acesso.LimparParametros();
            acesso.AdicionarParametos("@Nome", nome);
            DataTable dataTableCli = acesso.ExecutaConsulta(CommandType.StoredProcedure, "spConsultaNome");

            foreach (DataRow linha in dataTableCli.Rows)
            {
                Cliente cliente = new Cliente();
                cliente.IdCliente = Convert.ToInt32(linha["Id"]);
                cliente.Nome = Convert.ToString(linha["Nome"]);
                cliente.DataNascimento = Convert.ToDateTime(linha["DataNascimento"]);
                cliente.Sexo = Convert.ToBoolean(linha["Sexo"]);
                cliente.LimiteDeCompra = Convert.ToDecimal(linha["LimiteCompra"]);

                clientCollect.Add(cliente);
            }

            return clientCollect;

        }
        catch (Exception ex)
        {

            throw new Exception("Não foi possivel consultar o cliente por nome. Detalhes:  "+ ex.Message);
        }

    }

    public ClienteColecao ConsultaPorId(int id)
    {
        try
        {

            ClienteColecao clientCollect = new ClienteColecao();

            acesso.LimparParametros();
            acesso.AdicionarParametos("@ID", id);
            DataTable dataTableCli = acesso.ExecutaConsulta(CommandType.StoredProcedure, "spConsultaNome");

            foreach (DataRow linha in dataTableCli.Rows)
            {
                Cliente cliente = new Cliente();
                cliente.IdCliente = Convert.ToInt32(linha["Id"]);
                cliente.Nome = Convert.ToString(linha["Nome"]);
                cliente.DataNascimento = Convert.ToDateTime(linha["DataNascimento"]);
                cliente.Sexo = Convert.ToBoolean(linha["Sexo"]);
                cliente.LimiteDeCompra = Convert.ToDecimal(linha["LimiteCompra"]);

                clientCollect.Add(cliente);
            }

            return clientCollect;
        }
        catch (Exception ex)
        {

            throw new Exception("Não foi possivel consultar o cliente por nome. Detalhes:  " + ex.Message);
        }
    }
}

}

If you have any questions about how much code you put in the comments!

Answer 2

That kind of Architecture BOLOVO separating the application by "levels" as view/business/date is very bad and outdated. It has been used a lot, but professionals have seen that it is an approach with more disadvantages than advantages.

Your question was about what to call StoredProcedures passing values, everything straight. Well, you can simplify this part of the code well:

Read the documentation of System.Data.Sqlclient.Sqlparameter: You can do something simple like cmd.Parameters.Add(new SqlParameter("MAD_ENC_Id", value)); that will work.