List 3 Layers c # SQL SERVER Window Form

0

Good morning. I'm doing a form that is to register a user I'm doing it in the c # language with 3 layers and SQL SERVER database.

In my Registration form I have this aspect as the image:

As you can see in my form I have 5 combobox. The problem is that I want that combobox to be shown dynamically with a procedure.

This is the procedure I use to show my procedure

Create procedure listarviaincidencia

@opt int,

as

if @opt =1

begin

SELECT DerivarA =   REPLICATE('0',2 - LTRIM(RTRIM(LEN(DerivarID)))) + LTRIM(RTRIM(CAST(DerivarID AS CHAR(2)))) + ' - ' +
                                UPPER(CAST(DerivarA AS VARCHAR(150)))
        FROM TAteDerivarA
        ORDER BY DerivarID

end

This is the code of my layers:

Data Layer:

public class Cls_Conexion
{
    SqlConnection conexion = new SqlConnection("Data Source=192.168.105.150\SQL2012;Initial Catalog=DBProAuxQP;User Id=sa;Password=DBServ@14;");

    //Metodo para abrir la conexion

    void abrir_conexion()
    {
        if (conexion.State == ConnectionState.Closed)
        {
            conexion.Open();
        }
    }

    //Cerrar la Conexion 

    void cerrar_conexion()
    {
        if (conexion.State == ConnectionState.Open)
        {
            conexion.Close();
        }
    }

    //Metodo para ejecutar los Procedimientos  (Insert,delete,Update)

    public void Ejecutar_sp(String NombreSp, List<Cls_Parametro> lst)
    {
        SqlCommand  cmd;

        try
        {
            abrir_conexion();
            cmd = new SqlCommand(NombreSp,conexion);
            cmd.CommandType = CommandType.StoredProcedure;
            if (lst != null)
            {
                for (int i = 0; i < lst.Count; i++)
                { 
                    //Verificar el Tipo de Entrada
                    if (lst[i].Direccion == ParameterDirection.Input)
                    {
                        cmd.Parameters.AddWithValue(lst[i].Nombre, lst[i].Valor);
                    }

                    //Verificando el Tipo de Salida
                    if (lst[i].Direccion == ParameterDirection.Output)
                    {
                        cmd.Parameters.Add(lst[i].Nombre, lst[i].TipoDato, lst[i].Tamaño).Direction = ParameterDirection.Output;
                    }

                }
                cmd.ExecuteNonQuery();

                //Recuperando los valores de Salida

                for (int i = 0; i < lst.Count; i++)
                {
                    if (cmd.Parameters[i].Direction == ParameterDirection.Output)
                    {
                        lst[i].Valor = cmd.Parameters[i].Value.ToString();
                    }
                }
            }


        }
        catch (Exception ex)
        {
            throw ex;
        }
        cerrar_conexion();
    }

    public DataTable Listado(String Nombresp, List<Cls_Parametro> lst)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da;
        try
        {
            da = new SqlDataAdapter(Nombresp, conexion);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            if (lst != null)
            { 
                for(int i=0; i<lst.Count;i++)
                {
                    da.SelectCommand.Parameters.AddWithValue(lst[i].Nombre, lst[i].Valor);
                }
            }
            da.Fill(dt);

        }
        catch (Exception ex)
        {
            throw ex;
        }
        return dt;
    }
}

My business layer:

public class Cls_RegistrarIncidencia
{



    /* Variables para Combobox ViaIncidencia */

    public int VI_Ope { get; set; }
    public string VI_Buscar { get; set; }

    Cls_Conexion M = new Cls_Conexion();



    public DataTable ListadoViaIncidencia()
    {

        List<Cls_Parametro> lst = new List<Cls_Parametro>();
        try
        {
            return M.Listado("PE_Ate_InformacionAtencion_New", lst);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }
}

My Layer Presentation in the LOAD where I want that combobox to store the data

private void Form1_Load(object sender, EventArgs e)
    {

       RI.VI_Ope = 6;
        RI.VI_Buscar = "";



     DataTable dt =  RI.ListadoViaIncidencia();
     CboViaIncidencia.DataSource = dt;
    }

Show me this error:

But when I execute I get an error in the dt. I would like you to help me I am new to c # I do not know if this is the right way to do it in 3 layers.

    
asked by PieroDev 04.03.2017 в 17:54
source

2 answers

1

You are missing a small piece of code in your business layer:

RI.VI_Ope = 6;
RI.VI_Buscar = "";
DataTable dt =  RI.ListadoViaIncidencia();
CboViaIncidencia.DataSource = dt;
CboViaIncidencia.DisplayMember = "nombre_columna_mostrar";
CboViaIncidencia.ValueMember = "nombre_columna_Valor";
    
answered by 06.03.2017 в 22:30
0

You should do the following, in my case I will use a list.

public class Datos {
     public int nDato { get; set; }
     public string aDato { get; set; }
}

List<Datos> datos = new List<Datos>();
datos.Add(new Datos { nDato = 1, aDato = "Valor 1" });
datos.Add(new Datos { nDato = 2, aDato = "Valor 2" });
datos.Add(new Datos { nDato = 3, aDato = "Valor 3" });
comboBox1.DisplayMember = "aDato"; // valor que se mostrara en la dista desplegable
// si observas, tiene que ser el mismo nombre del dato que quiero mostrar en la lista
comboBox1.ValueMember = "nDato"; // y este seria el valor que tiene el elemento
comboBox1.DataSource = datos;  // en tu caso es un datatable, funciona igual con la lista
  • Note, you tell me that you only have one data in your DataTable, so how will the value to be shown only use the DisplayMember and the ValueMember will be empty. What is the ValueMember for? It helps if you want to know what value of the selected element of your combo and load cascading combos or save the value of that element in the database.

Greetings.

    
answered by 04.03.2017 в 18:45