load datagridview according to the parameter (stored procedure) [closed]

Question

load datagridview according to the parameter (stored procedure) [closed]

Navigation

#1 by (0 votes)

0

Hello good afternoon to all, along with greeting them again bothering with a doubt: I have to fill a datagridview with a stored procedure of mysql, which makes a select and by means of a where limits the query:

CREATE PROCEDURE 'spMedicamentosPorPaciente' (in textobusqueda varchar (45))
BEGIN
SELECT * FROM medicamentos_paciente WHERE idPaciente = textobusqueda  LIMIT 150;
END

my project is in layers, in the data and business layer I have a method that allows me to fill the grid without problems:

public DataTable mostrarTabla()
    {
        DataTable dtProfesionales = new DataTable("profesional");

        MySqlConnection con = new MySqlConnection();

        try
        {
            con.ConnectionString = conexion.cadenaConexion;

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "spMostrarProfesionales";
            cmd.CommandType = CommandType.StoredProcedure;

            MySqlDataAdapter adaptador = new MySqlDataAdapter(cmd);
            adaptador.Fill(dtProfesionales);

        }
        catch (Exception ex)
        {

            dtProfesionales = null;
        }
        return dtProfesionales;

    }

and the code in business:

public static DataTable mostrar()
    {
        return new datosProfesional().mostrarTabla();
    }

the issue is that I do not know how to load the datatable show with the stored procedure that has the parameter, I'm trying with this code:

public DataTable mostrarTabla(datosMedicamentosPaciente medpaciente)
    {
        DataTable dtMedicamentosPaciente = new DataTable("medicamentos_paciente");

        MySqlConnection con = new MySqlConnection();

        try
        {
            con.ConnectionString = conexion.cadenaConexion;

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "spMedicamentosPorPaciente";
            cmd.CommandType = CommandType.StoredProcedure;

            //DECLARAMOS LOS PARAMETROS QUE SON IGUALES A LOS QUE TENEMOS EN EL SP
            MySqlParameter parIdMedPaciente = new MySqlParameter();
            parIdMedPaciente.ParameterName = "@textobusqueda";
            parIdMedPaciente.MySqlDbType = MySqlDbType.VarChar;
            parIdMedPaciente.Value = medpaciente.IdMedicamentoPaciente;
            cmd.Parameters.Add(parIdMedPaciente);

            MySqlDataAdapter adaptador = new MySqlDataAdapter(cmd);
            adaptador.Fill(dtMedicamentosPaciente);

        }
        catch (Exception ex)
        {

            dtMedicamentosPaciente = null;
        }
        return dtMedicamentosPaciente;

    }

in the form I have this function to load the grid:

private void cargarGrilla() 
{ 
    this.dgvMedicamentos.DataSource = negocioMedicamentos.mostrar(this.txtCodPaciente.Text); 
    //this.ocultarColumnas(); 
    //lblTotalRegistros.Text = "Total de Registros: " + Convert.ToString(dgvBoxes.Rows.Count); 
}

but it throws me the following errors:

Error 3 No overload for the 'show' method takes '1'   arguments. XXXXXXX \ CapaPresentacion \ frmMedicamentosPaciente.cs 36 47 CapaPresentacion

Error 4 No overload for the 'showTable' method takes '1'   arguments XXXXXX \ CapaNegocios \ negocioMedicamentosPaciente.cs 47 20 CapaNegocios

Greetings to all and thanks

c#

asked by Nicolas Ezequiel Almonacid 23.07.2018 в 21:09

source

1 answer

Set characters within an input Instagram api does not return full results

score 0 · Accepted Answer

In the data layer, as a general method to return a datatable with two parameters

namespace Data
{
    public class Connection
    {
        public SqlConnection connectionString = 
        new SqlConnection("Server = XXXXXXX, 1433; Initial Catalog = XXXXX; Persist Security Info=False;User ID = jorgeluis; Password=XXX@XXXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout = 30;");

        public void Connect()
        {
            try
            {
                connectionString.Open();
            }
            catch (Exception ex)
            {

            }
        }
        public void Disconnect()
        {
            try
            {
                connectionString.Close();
            }
            catch (Exception ex)
            {
                // Acomodas tu mensaje de error en logs, directo al usuario o como prefieras
            }
        }
    }
}

namespace Data
{
    public class ReadGeneralData
    {
        public DataTable GetDataTable(string storedProcedure, string nameColumn1, Int16 id1)
        {
            DataTable dataTable             = new DataTable();
            Connection connection           = new Connection();
            SqlCommand sqlCommand           = new SqlCommand();
            SqlDataAdapter sqlDataAdapter   = new SqlDataAdapter();
            sqlCommand.CommandText          = storedProcedure;
            sqlCommand.CommandType          = CommandType.StoredProcedure;
            sqlCommand.Connection           = connection.connectionString;
            sqlDataAdapter.SelectCommand    = sqlCommand;

            SqlParameter parameter1     = new SqlParameter();
            parameter1.SqlDbType        = SqlDbType.SmallInt;
            parameter1.ParameterName    = nameColumn1;
            parameter1.Value            = id1;

            sqlCommand.Parameters.Add(parameter1);
            connection.Connect();
            sqlDataAdapter.Fill(dataTable);
            connection.Disconnect();

            return dataTable;
        }
    }
}

In the presentation layer

    ReadGeneralData readGeneralData = new ReadGeneralData();
    grd.DataSource = 
    readGeneralData.GetDataTable("TU_procedimiento_Almacenado", "nameColumn1", 1));
    // 1 Es un valor referencial, ya ves que valor le pasas, recuerda ajustarlo al tipo de datos 
    // que se declare en la capa de datos
    grd.DataBind(); // En caso de que sea web