C# Bind SQL Data to a Listview WPF MVVM

Question

i just started with WPF and the MVVM pattern. I want to access data from a SQL Server, The connection works so far, so thats not the problem. But currently my problem is, that first, I don't know what WPF Control is best to display the Data(ListView, DataGrid, ...?) Second, I just don't get the data displayed in my ListView.

Currently, I have my ViewModel, that looks like this:

namespace StaticIPConfiger.Modelle {

class VModel {

    public List<string> AlleKunden {
        get {
            List<String> customerData = new List<String>();
            DatabaseConnection connection = new DatabaseConnection();
            SqlCommand getCustomers = new SqlCommand("Select c_name,l_name,a_town,a_pcode,a_street from dbo.AllCustomers", connection.Connection);
            connection.Connection.Open();
            getCustomers.ExecuteNonQuery();
            SqlDataReader reader = getCustomers.ExecuteReader();
            if(reader.HasRows) { }
                while (reader.Read()) {
                    customerData.Add(reader.GetString(0));
                }
            connection.Connection.Close();
            return customerData;
        }
    }
}

The .xaml from the Page where I want to display the data, looks like this.

<Page x:Class="StaticIPConfiger.PageViewCustomers"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
  xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
  xmlns:local="clr-namespace:StaticIPConfiger"
  xmlns:localvm="clr-namespace:StaticIPConfiger.Modelle" 
  mc:Ignorable="d" 
  d:DesignHeight="300" d:DesignWidth="500"
  Title="PageViewCustomers">
<Page.Resources>
    <ObjectDataProvider x:Key="vm" ObjectType="{x:Type localvm:VModel}" />
</Page.Resources>

<Grid DataContext="{Binding}">
    <ListView Margin="8" Height="350" Width="500" 
         ItemsSource="{Binding}" DataContext="{Binding Path=AlleKunden}">
        <ListView.View>
            <GridView>
                <GridViewColumn DisplayMemberBinding="{Binding Path=c_name}" 
              Header="Name" Width="100"/>
                <GridViewColumn DisplayMemberBinding="{Binding Path=l_name}" 
              Header="Standort" Width="100"/>
                <GridViewColumn DisplayMemberBinding="{Binding Path=a_town}" 
              Header="Ort" Width="100"/>
                <GridViewColumn DisplayMemberBinding="{Binding Path=a_pcode}" 
              Header="PLZ" Width="100"/>
                <GridViewColumn DisplayMemberBinding="{Binding Path=a_street}" 
              Header="Straße" Width="100"/>
            </GridView>
        </ListView.View>
    </ListView>
</Grid>

So it displays the Headers, by doing the Binding : "Binding Path=a_town" I want to refer to the Columns in my View from the SQL Server.

I think, that the DataType of my Method "AlleKunden" is wrong, maybe I dont need a List but another Type?

Another question: I have a dataSet.xsd File in my Solution. Cant I instead access the dataSet instead of the Database? Or do I miss something here?

Thanks already!!

If you need any more code, or have questions, just tell me.

Oh, and a Happy new Year to everyone!!

EDIT: I have one more question for my next step. I want to add a new customer to my database. A customer can have one or more "locations" and every location has a different address. Now my problem is, that in the database these are 3 different tables. customer, location and address. Now if I insert a location, I need to insert a customer ID for that location, same with the address. An Address needs a location id in the database. I thought about first inserting the customer, then get the max(customerid) from the database to insert the location with the max(customerid) and next i have to insert the address by using the max(locationid). Is there a more easy way to accomplish this task, because this seems pretty confusing, and I dont really know how to accomplish it by using wpf and mvvm pattern..

mm8 · Accepted Answer · 2017-01-03 17:08:48Z

think, that the DataType of my Method "AlleKunden" is wrong, maybe I dont need a List but another Type?

Yes, if you want to be able to bind directly to the columns that are returned from the database you could return a DataTable from your method and bind to its DefaultView property:

class VModel
{
    public DataView AlleKunden
    {
        get
        {
            DataTable dt = new DataTable();
            DatabaseConnection connection = new DatabaseConnection();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("Select c_name,l_name,a_town,a_pcode,a_street from dbo.AllCustomers", connection.Connection);
            adapter.Fill(dt);
            connection.Connection.Close();
            return dt.DefaultView;
        }
    }
}

<ListView Margin="8" Height="350" Width="500" ItemsSource="{Binding AlleKunden}">
    <ListView.View>
        <GridView>
            <GridViewColumn DisplayMemberBinding="{Binding Path=c_name}" Header="Name" Width="100"/>
            <GridViewColumn DisplayMemberBinding="{Binding Path=l_name}" Header="Standort" Width="100"/>
            <GridViewColumn DisplayMemberBinding="{Binding Path=a_town}" Header="Ort" Width="100"/>
            <GridViewColumn DisplayMemberBinding="{Binding Path=a_pcode}" Header="PLZ" Width="100"/>
            <GridViewColumn DisplayMemberBinding="{Binding Path=a_street}" Header="Straße" Width="100"/>
        </GridView>
    </ListView.View>
</ListView>

The path of the bindings refer to a public property or a column of the item in the ItemsSource of the ListView. If you are binding to a List<string> the bindings won't work because the String class has no c_name, l_name, etc. properies. But the DataRowView of the DataView has.

But currently my problem is, that first, I don't know what WPF Control is best to display the Data(ListView, DataGrid, ...?)

You typically use a DataGrid over a ListView when you want the user to be able to edit the data. If you simply want to display the data in a read-only fashion, using a ListView is a good choice.

Thank you! I implemented your solution, but it still displays no data. When I execute the query in SQL Management Studio, it displays the rows. I also dont get any error message in Visual studio?

Make sure that you have set the DataContext of the ListView or its parent window to an instance of your VModel class, that your DatabaseConnection class works as expected and that you are using the correct connection string to the database. This should work provided that you supply the correct connection string:

VModel.cs:

namespace WpfApplication1
{
    public class VModel
    {
        public VModel()
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection("your connection string to the database..."))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("Select c_name, l_name, a_town, a_pcode, a_street from dbo.AllCustomers", connection);
                adapter.Fill(dt);
            }
            AlleKunden = dt.DefaultView;
        }

        public DataView AlleKunden { get; private set; }
    }
}

View.xaml:

<Window x:Class="WpfApplication1.MainWindow"
        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:WpfApplication1"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525">
    <Window.DataContext>
        <local:VModel />
    </Window.DataContext>
    <Grid>
        <ListView ItemsSource="{Binding AlleKunden}">
            <ListView.View>
                <GridView>
                    <GridViewColumn DisplayMemberBinding="{Binding Path=c_name}" Header="Name" Width="100"/>
                    <GridViewColumn DisplayMemberBinding="{Binding Path=l_name}" Header="Standort" Width="100"/>
                    <GridViewColumn DisplayMemberBinding="{Binding Path=a_town}" Header="Ort" Width="100"/>
                    <GridViewColumn DisplayMemberBinding="{Binding Path=a_pcode}" Header="PLZ" Width="100"/>
                    <GridViewColumn DisplayMemberBinding="{Binding Path=a_street}" Header="Straße" Width="100"/>
                </GridView>
            </ListView.View>
        </ListView>
    </Grid>
</Window>

Hey, thanks for your answer!! I now understand much better how Binding and Paths work. Thank you! I implemented your solution, but it still displays no data. When I execute the query in SQL Management Studio, it displays the rows. I also dont get any error message in Visual studio?
Sry, i forgot that! I have one more question, I edited my post. Do you have an approach for this?

Alex · Accepted Answer · 2017-01-03 16:34:43Z

2

Also in addition to @mm8 answer I would add that implementing the data access logic in the property getter is a very bad practice, especially when you bind those properties to UI:

AVOID throwing exceptions from property getters.

Property getters should be simple operations and should not have any preconditions. If a getter can throw an exception, it should probably be redesigned to be a method.

While data access is always a very sensible operation which may cause any number of exceptions.

So consider redesigning your AlleKunden in a way that the data access logic is moved somewhere else. E.g.:

public DataView AlleKunden
{
    get;
    private set;
}

// Use LoadData method anywhere in your ViewModel
private void LoadData()
{
    DataTable dt = new DataTable();
    try
    {
        DatabaseConnection connection = new DatabaseConnection();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("Select c_name,l_name,a_town,a_pcode,a_street from dbo.AllCustomers", connection.Connection);
        adapter.Fill(dt);
        connection.Connection.Close();
        AlleKunden = dt.DefaultView;
    }
    catch (/* ... */)
    {

    }
}

answered Jan 3, 2017 at 16:34

Alex

1,4519 silver badges18 bronze badges

1 Comment

Constantin M Over a year ago

Hey Alex, thanks for your advice, I never thought about that, but I will remember it in the future :) . Thanks for your code, I have a question: In the LoadData() Method, You assign the current DataView to AlleKunden. But when do I best call the LoadData() Method? Because if I dont call the LoadData() method, I wont get the new View..

Collectives™ on Stack Overflow

C# Bind SQL Data to a Listview WPF MVVM

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related