Populate DataGrid Using SQL Server database

Question

All I'm trying to do is populate a DataGrid with items from a local SQL Server database called Inventory. I'm not getting any errors, but my table is blank.

FYI: the Inventory database has a table called Item which has columns ItemNumber, Name, Cost, and QuantityOnHand. The DataGrid name is called dgInventory. Any help would be appreciated.

Here's my .xaml code:

<UserControl x:Class="FinalAssignment.Views.InventoryView"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:FinalAssignment.Views"
        mc:Ignorable="d">
    <Grid>
        <DataGrid Name="dgInventory" AutoGenerateColumns="False">
            <DataGrid.Columns>
                <DataGridTextColumn Header="Item Number" Binding="{Binding ItemNumber}" />
                <DataGridTextColumn Header="Name" Binding="{Binding Name}" />
                <DataGridTextColumn Header="Cost" Binding="{Binding Cost}" />
                <DataGridTextColumn Header="Quantity on Hand" Binding="{Binding QuantityOnHand}" />
            </DataGrid.Columns>
            <DataGrid.RowDetailsTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Details}" Margin="10" />
                </DataTemplate>
            </DataGrid.RowDetailsTemplate>
        </DataGrid>
    </Grid>
</UserControl>

Here's my .cs code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;

namespace FinalAssignment.Views
{
    /// <summary>
    /// Interaction logic for InventoryView.xaml
    /// </summary>
    public partial class InventoryView : UserControl
    {
        public InventoryView()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string strConnection = "Server=ASHLEY-PC\\SQLEXPRESS;Database=Inventory;Trusted_Connection=true";

            SqlConnection con = new SqlConnection(strConnection);

            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = con;
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = "select ItemNumber, Name, Cost, QuantityOnHand from Item";
            SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

            DataTable dt = ((DataView)dgInventory.ItemsSource).ToTable();
            sqlDataAdap.Fill(dt);
        }
    }
}

sujith karivelil · Accepted Answer · 2016-08-17 04:06:05Z

3

You have not assign the DataSource for the Grid in the current code; it is not necessary to Create headers for the Grid It will automatically assigned from the binding table. Consider the following snippets

<DataGrid Name="dgInventory" ItemsSource="{Binding}" AutoGenerateColumns="False">

And the back-end code will be :

sqlCmd.CommandText = "select ItemNumber as Item Number, Name, Cost, QuantityOnHand as Quantity on Hand from Item";
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
sqlDataAdap.Fill(dt);
dgInventory.ItemsSource = dt.DefaultView;

edited Aug 17, 2016 at 4:06

answered Aug 17, 2016 at 3:39

sujith karivelil

29.1k7 gold badges60 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user6477547 Over a year ago

Thank you! Instead of gridEmployees, do you mean dgInventory?

sujith karivelil Over a year ago

Yes, I have updated the post, take a look.. Hope that it will solve your issue

user6477547 Over a year ago

Hmmm. Still not working. I'm guessing it has something to do with my strConnection. My server name is ASHLEY-PC\SQLEXPRESS. :(

sujith karivelil Over a year ago

It will definitely works if the datatable is populated correctly. Could you please check whether the query populating the datatable

Umair Farooq Over a year ago

Instead of dgInventory.DataContext, don't you think dgInventory.ItemsSource should be used ?

|

CodeDada · Accepted Answer · 2016-08-17 09:37:20Z

You can specify ItemsSource binding for data grid in your xaml like this instead of doing in code behind .cs and assign Loaded attribute to your Form1_Load method

    <Grid>
<DataGrid Name="dgInventory" AutoGenerateColumns="False"  ItemsSource="{Binding Path=MyDataBinding}" Loaded="Form1_Load" >
        <DataGrid.Columns>
                    <DataGridTextColumn Header="Item Number" Binding="{Binding ItemNumber}" />
                    <DataGridTextColumn Header="Name" Binding="{Binding Name}" />
                    <DataGridTextColumn Header="Cost" Binding="{Binding Cost}" />
                    <DataGridTextColumn Header="Quantity on Hand" Binding="{Binding QuantityOnHand}" />
                </DataGrid.Columns>
                <DataGrid.RowDetailsTemplate>
                    <DataTemplate>
                        <TextBlock Text="{Binding Details}" Margin="10" />
                    </DataTemplate>
                </DataGrid.RowDetailsTemplate>
                </DataGrid>
            </Grid>

and in Xaml.cs should look like this

private void Form1_Load(object sender, EventArgs e)
        {
            string strConnection = "Server=ASHLEY-PC\\SQLEXPRESS;Database=Inventory;Trusted_Connection=true";

            SqlConnection con = new SqlConnection(strConnection);

            string query = "select ItemNumber, Name, Cost, QuantityOnHand from Item";
            SqlDataAdapter sqlDataAdap = new SqlDataAdapter(query,con );

            DataSet _Bind = new DataSet();
           _Adapter.Fill(_Bind, "MyDataBinding");

           dgInventory.DataContext = _Bind;

           con.Close();
        }

Hope that helps !

Collectives™ on Stack Overflow

Populate DataGrid Using SQL Server database

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related