Populating a WPF listbox with items from an SQL (SDF) database

Question

Hi!

I have been searching on how to do this for a very long time, and I have not managed to get a straight answer on the subject, so hopefully one of you StackOverflow users will be able to help me here. I have a WPF ListBox named CategoryList and a SDF database called ProgramsList.sdf (with two tables called CategoryList and ProgramsList). What I wish my program to do is get the category names from the CategoryList table and list them in the ListBox control called CategoryList.

Here's the code that I tried, but it only caused my program to crash.

    SqlConnection myConnection = new SqlConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "ProgramsList.sdf");
    SqlDataReader myReader = null;

    myConnection.Open();
    CategoryList.Items.Clear();
    SqlDataReader dr = new SqlCommand("SELECT Name FROM CategoryList ORDER BY Name DESC", myConnection).ExecuteReader();

    while (myReader.Read())
    {
        CategoryList.Items.Add(dr.GetInt32(0));
    }
    myConnection.Close();

Can anyone help me? Thanks in advance!

Answer 1

I'd try something like this:

var myConnection = new SqlConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "ProgramsList.sdf");
var cmd = new SqlCommand("SELECT Name FROM CategoryList ORDER BY Name DESC", myConnection);

myConnection.Open();
CategoryList.Items.Clear();

var sda = new SqlDataAdapter(cmd);
var ds = new DataSet();
sda.Fill(ds);

CategoryList.ItemsSource = ds.Tables["CategoryList"];

myConnection.Close(); 

Note, that you will need to setup the correct bindings in your CategoryList object, likely via some XAML like this:

<ListBox>
    <ListBox.Resources>
        <DataTemplate x:Key="DataTemplateItem">
            <Grid Height="Auto" Width="Auto">
                <TextBlock x:Name="Name" Text="{Binding Name}" />
            </Grid>
        </DataTemplate>
    </ListBox.Resources>
</ListBox>

Answer 2

Perhaps you mean: ....

CategoryList.Items.Add(dr.GetString(0));

....

Answer 3

A much better way is to bind your list to an object you create. That way you can specify properties for DisplayMemberPath (what you see) and SelectedValuePath (your programs internal value).

Here is your main XAML code. Note than the click method of the button will display the currently selected value of the ComboBox. That is going to make things easy later on. Hopefully this is not overkill but it shows a few principles that make WPF easy.

namespace WPFListBoxSample {

public partial class Window1 : Window

{

    WPFListBoxModel model = new WPFListBoxModel();

    public Window1()
    {
        InitializeComponent();
        this.Loaded += new RoutedEventHandler(Window1_Loaded);
    }

    void Window1_Loaded(object sender, RoutedEventArgs e)
    {
        GetData();
        this.DataContext = model;
    }

    public void GetData()
    {
        //SqlConnection myConnection = new SqlConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "ProgramsList.sdf");
        SqlConnectionStringBuilder str = new SqlConnectionStringBuilder();
        str.DataSource="192.168.1.27";
        str.InitialCatalog="NorthWnd";
        str.UserID="sa";
        str.Password="xyz";
        SqlConnection myConnection = new SqlConnection(str.ConnectionString);

        SqlDataReader myReader = null;

        myConnection.Open();
        SqlDataReader dr = new SqlCommand("SELECT CategoryId, CategoryName FROM Categories ORDER BY CategoryName DESC", myConnection).ExecuteReader();

        while (dr.Read())
        {
            model.Categories.Add(new Category { Id = dr.GetInt32(0), CategoryName = dr.GetString(1) });
        }
        myConnection.Close();
    }

    private void myButton_Click(object sender, RoutedEventArgs e)
    {
        if (this.myCombo.SelectedValue != null)
            MessageBox.Show("You selected product: " + this.myCombo.SelectedValue);
        else
            MessageBox.Show("No product selected");
    }
}

}

The XAML

    <Grid>
    <StackPanel>
        <ComboBox x:Name="myCombo" ItemsSource="{Binding Categories}" DisplayMemberPath="CategoryName"  SelectedValuePath="Id" />
        <Button x:Name="myButton" Content="Show Product" Click="myButton_Click"/>
    </StackPanel>
</Grid>

Your own object for representing a Category

namespace WPFListBoxSample
{
    public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
    }
}

Note the {get; set;}'s

Finally a little bit of glue that makes a lot of things easy is putting all your data in a model and binding to the model. This is the way to work WPF.

using System.Collections.Generic;
namespace WPFListBoxSample
{
    public class WPFListBoxModel
    {
        private IList<Category> _categories;
        public IList<Category> Categories
        {
            get
            {
                if (_categories == null)
                    _categories = new List<Category>();
                return _categories; }
            set { _categories = value; }
        }
    }
}