How to fill a DataGrid with records obtained from a database?

You can try to do the following, get the tables within your dataset

DataTableCollection collection = ds.Tables;

After that you get the DataTable that contains the data:

DataTable table = collection[0];

even there you have a DataTable that we will use later.

The example that I will post will not be with all your data but I know it will be understood so that you can reproduce it.

Your datagrid must be edited to be able to enter data from the code, since you added columns by default you must add a binding to all your columns:

<DataGrid x:Name="dataGridUsuarios" CanUserAddRows="True" HorizontalAlignment="Left" Margin="64,70,0,0" VerticalAlignment="Top" Height="210" Width="364">
    <DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding Path=nombre}" ClipboardContentBinding="{x:Null}" Header="nombre" HeaderStringFormat="Nombre" Width="100"/>
        <DataGridTextColumn Binding="{Binding Path=apellido}" ClipboardContentBinding="{x:Null}" Header="apellido" HeaderStringFormat="Apellido" Width="100"/>
        <DataGridTextColumn Binding="{Binding Path=sexo}" ClipboardContentBinding="{x:Null}" Header="sexo" HeaderStringFormat="Sexo" Width="80"/>
        <DataGridTextColumn Binding="{Binding Path=edad}" ClipboardContentBinding="{x:Null}" Header="edad" HeaderStringFormat="Edad" Width="70"/>
    </DataGrid.Columns>
</DataGrid>

The parts as Binding="{Binding Path=nombre}" are important here because thanks to these data will be imported.

After that you must create a class to add your rows with all the data you are showing, in my example I will only work with four: name, last name, sex, age.

Then:

 public class Modelo
 {
     public string nombre { get; set; }
     public string apellido { get; set; }
     public string sexo { get; set; }
     public string edad { get; set; }
 }

Then imagine that my datatable table , previously mentioned has those data, what we should do now is to go through that datatable to add those contents to our datagrid.

foreach (DataRow row in table.Rows)
{
    var data = new Modelo { nombre = row["Nombre"].ToString(), apellido = row["Apellidos"].ToString(), sexo = row["Sexo"].ToString(), edad = row["Edad"].ToString() };
    dataGridUsuarios.Items.Add(data);
}

where the row["Nombre"] are values of your query, you will have ones in this way:

row["idUsuario"]
row["Nombre"]
row["ApellidoP"]
row["ApellidoM"]// y asi con todos tus datos.

After that I would have to give you a result similar to this one.

Also keep in mind that the Path = name of your xaml file must have the same format as your model.

Also, about the id that you want to put, you can do it as you did with the other columns, but since this should not be visible, you can define it with Visibility="Hidden" . Ex:

<DataGridTextColumn Binding="{Binding Path=id}" ClipboardContentBinding="{x:Null}" Header="id" HeaderStringFormat="id" Visibility="Hidden"/>

I have already done a test in WPF with the datagrid, if you already have your data model, of which you only want to show only some in your datagrid you can do the following and use an anonymous type with the data that you need to show and in base them to configure the columns of your datagrid as the headers and hide by saying the column of the ID so that you get it when you need to do something with the selected element.

I have this model:

public class Producto
{
    [Key]
    public int ProductoID { get; set; }    

    [Required, MaxLength(50, ErrorMessage = "Se han excedido el numero de caracteres {0}"), MinLength(5, ErrorMessage = "Nombre de producto demasiado corto")]
    public string NombreProducto { get; set; }

    public decimal PrecioVenta { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime FechaCreado { get; set; } = DateTime.Now;

    [DataType(DataType.DateTime)]
    public DateTime FechaModificado { get; set; } = DateTime.Now;
}

And I want in my datagrid the ID, ProductName and Price Sale, I do the following:

    private void Window_Loaded(object sender, RoutedEventArgs e)
    {
        using (var db = new LaundryDB())
        {
            //Obtengo todos mis productos de mi BD 
            var productos = from p in db.Productos.ToList()
                            select new { p.ProductoID, p.NombreProducto, p.PrecioVenta}; //selecciono con tipo anonimo solo los campos que me interesan

            //La lista de productos queda con 3 campos que me interesa
            //ProductoID - Index(0) 
            //NombreProducto Index(1)
            //PrecioVenta - Index(2) 

            //Asigno mi lista de productos como origen de datos del datagrid
            dgvProductos.ItemsSource = productos;

            //Oculto el campo ProductoID, que no deseo mostrar  
            dgvProductos.Columns[0].Visibility = Visibility.Hidden; //Index 0 - ProductoID

            //Modifico cabeceras de columnas
            dgvProductos.Columns[1].Header = "Nombre del producto o servicio"; //Index 1 - NombreProducto
            dgvProductos.Columns[2].Header = "Precio de venta"; //Index 2 - PrecioVenta
        }
    }

And this is the result, I do not know why an additional blank column comes out, but since you manage WPF you have to know what happens or someone else could help with that part

How about again, with this code you will obtain the ID of your user that is selected from your datagrid, I hope it is useful, do it in the SelectionChanged event of your DG, in my example what I do is put the ID of the product selected in a textbox, located above my datagrid, greetings.

    private void dgvProductos_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {            
        var ID = (dgvProductos.SelectedItem as Producto).ProductoID;
        txtID.Text = ID.ToString();
    }

It puts you columns after the columns that you predefined because the names of those columns do not match.

To solve it just modify your query to match the title of each column of the Datagridview:

String consulta = "select idUsuario,Nombre,ApellidoP AS 'Apellido Paterno',ApellidoM AS 'Apellido materno',Sexo,Telefono,Edad,Puesto from usuarios;"

I assume that the idUser column is hidden in your Datagridview, otherwise you would have to remove it from the query. A greeting.

Hello, what was happening to me was exactly the same with a DataGridView, in which I added the columns that I want to show in my application and the ones I want to hide as the ID to use it as you think, I solved that as commented above, you have to match the name of the column of my DGV with the one you have in your BD, and also in the Data property, in DataPropertyName put the same name of the data you are bringing so that it does not duplicate the column and it appears only once and with the data obtained from your query, that way was my solution and hopefully it is the same operation in WPF with the DataGrid, greetings and I leave you a capture

Let me verify if in WPF it is the same operation, since I have not developed in it, more than WinForms, and I pass the information to you.