query and change procedures for DAO layer C #

2

include, change, get

Several procedures were created with the purpose of including, changing, querying, obtaining data, 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 was created, and all methods for accessing these procedures were declared. The methods are empty ... There is no doubt about structuring the connection, commands and adding parameters in code.

For example, for the procedure STP_INS_MvtoAddsDevEdol 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. Somebody could help me because I also need to refer to objects filling this data? ps .: this would be structure for inclusion, I do not know if it is right. how would it look for query, change etc?

    
asked by anonymous 14.07.2016 / 00:35

2 answers

2

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

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 the DTO (transfer object) and a layer called BLL. As the project was simple it made a customer registry in the DTO had the client class and client collection class client

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

Collecting client class

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

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

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

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 the code you post in the comments!

    
14.07.2016 / 01:23
1

This type of BOLOVO Architecture by separating the application by "levels" like view / business / data is very bad and outdated. It has been widely used, but professionals have already seen that it is an approach with more disadvantages than advantages.

Your question was how to call StoredProcedures passing values, all right. Well, you can make this part of the code very well:

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

    
14.07.2016 / 11:29