How can I list with GridView avoiding duplicates?

0

Good morning, my problem is as follows. I'm making a system in which I have to add Containers, and those containers have materials in turn. The data when being registered are stored in a database. Choose to create a "material" table that contains the material id and its name. The materials are sand, soda, lime and several. I also have the "envase" table that contains the container fields. Then choose a table EnvaseMaterial that has the package code as FK , the id of the material and the percentage used. Now. The problem is that if I'm going to list in GridView it's obviously going to be duplicated as you see in the image. What I need is to look like this: Envase, Precio, Peso, Arena, Sosa, Cal, Varios , the value of each material being YOUR PERCENTAGE . You could create the field arena, cal, sosa, varios in the table envaseMaterial and assign the percentages in the code. But the option does not seem feasible since if I add a new material that I would do? I shot the whole table to create a new field? It can not be like this. I would also be forgetting the "material" table that contained id and material name.

code:

public void CargarEnvasesAMostrar()
        {

            var e = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = e;
            using (SqlConnection conn = new SqlConnection(e))
            {
                try
                {
                    con.Open();
                    SqlCommand command =
                        new SqlCommand(
                            "SELECT distinct t.nombre,t.precioEnvase,t.peso,ma.nombreMaterial,e.porcentajeUtilizado FROM tipoEnvase t JOIN envaseMaterial e ON t.codigo=e.codigo join material ma on e.idMaterial = ma.idMaterial",
                            conn);
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        gridEnvases.DataSource = dataSet;
                        gridEnvases.DataBind();
                    }
                }
                catch (SqlException ex)
                {

                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

GridView in code behind

            

            <asp:BoundField DataField="nombre" HeaderText="Envase"/>
            <asp:BoundField DataField="precioEnvase" HeaderText="Precio"/>
            <asp:BoundField DataField="peso" HeaderText="Peso"/>
            <asp:BoundField DataField="nombreMaterial" HeaderText="Material"/>
            <asp:BoundField DataField="porcentajeUtilizado" HeaderText="%"/>


        </columns>



          <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
         <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
         <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
         <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
         <sortedascendingcellstyle backcolor="#F7F7F7" />
         <sortedascendingheaderstyle backcolor="#4B4B4B" />
         <sorteddescendingcellstyle backcolor="#E5E5E5" />
         <sorteddescendingheaderstyle backcolor="#242121" />
     </asp:GridView>
    
asked by Agustín Morelle 29.06.2017 в 21:23
source

1 answer

0

I would solve it by modifying the query by transposing rows to columns. I leave a link where they show good bun examples using pivot and dynamic sql in sql server:

PIVOT - SQL Server

    
answered by 14.07.2017 в 23:55