locked
data binding in wpf RRS feed

  • Question

  • i have a wpf datagrid that needs to display the data of a certain table in sql- call it table a. the problem is that table a only holds the codes (foreign keys)to many diffrent tables.

    for ex: say table a has two columns codeworker and codejob and then there are two more tables called workers and jobs and they hold the names of the corresponding code from table a

    in my datagrid i need to be able to display the name from the other tables that is represented by the code in table a

    all of this needs to be done programatically and not in xaml?

    how can i do this? please reply specifically since i am a begginer in wpf.

    i hope i made myself clear enough. if not i will be happy to clarify further.

    thanks in advance.

    hopefull


    Thursday, November 17, 2011 10:07 AM

Answers

  • Hi,

    XAML Code

     <ListView x:Name="lstData"
              ItemsSource="{Binding}">
                <ListView.View>
                    <GridView>
                        <GridViewColumn Header="Name"
                          Width="Auto"
                            DisplayMemberBinding="{Binding Path=FirstName}" />
                     
                    </GridView>
                </ListView.View>
            </ListView>
    


    and code behind

    SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PCNAME;Initial Catalog=DBName;Integrated Security=True";
    
                conn.Open();
    
                SqlDataAdapter adapt = new SqlDataAdapter("select B.FirstName from wa.tblPatient A inner join wa.tblPerson B on(A.PersonId=B.PersonId)", conn);
                DataSet ds = new DataSet();
                adapt.Fill(ds);
    
                DataTable dt = ds.Tables[0];
    
                //a1.ItemsSource = dt.DefaultView;
                this.DataContext=this;
    
                // Create the GridView
                GridView gv = new GridView();
    
                // Create the GridView Columns
                foreach (DataColumn item in dt.Columns)
                {
                    GridViewColumn gvc = new GridViewColumn();
                    gvc.DisplayMemberBinding = new Binding(item.ColumnName);
                    gvc.Header = item.ColumnName;
                    gvc.Width = Double.NaN;
                    gv.Columns.Add(gvc);
                }
    
                // Setup the GridView Columns
                lstData.View = gv;
                // Display the Data
                lstData.DataContext = ds.Tables[0];
    

    Thanks,

    Rajnikant
     


    Friday, November 18, 2011 9:19 AM

All replies

  • Hi,

    I do not see any specifics of WPF programming in your requirement, because most of what you need to do has to be done either at the database level and/or at the Data Access Layer level.

    At the database level you can simply create a stored procedure or view where you can join your various tables to retrieve the various columns and once you get the various columns you require, you may fill them in objects in your DAL and simply bind to the UI DataGrid.

    If there is any more story to it, please elaborate and I would be happy to help you finalize an approach.


    Thanks,
    Abhinav
    Please mark as solution if this answer helped you
    Thursday, November 17, 2011 10:33 AM
  • Hi,

    Just write a sql query for column you want using joins, it will return a datatable, bind datatable's defaultview to datagrid.

    For Example

     SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PCName;Initial Catalog=DBName;Integrated Security=True";
    
                conn.Open();
    
                SqlDataAdapter adapt = new SqlDataAdapter("select B.FirstName from tblA A inner join tblB B on(A.PersonId=B.PersonId)", conn);
                DataSet ds = new DataSet();
                adapt.Fill(ds);
    
                DataTable dt = ds.Tables[0];
    
                a1.ItemsSource = dt.DefaultView;
    

     

     

    Thanks,

    Rajnikant


    Thursday, November 17, 2011 10:40 AM
  • > table a only holds the codes (foreign keys)to many diffrent tables. for ex: say table a has two columns codeworker and codejob and then there are two more tables called workers and jobs and they hold the names of the corresponding code from table a. in my datagrid i need to be able to display the name from the other tables that is represented by the code in table a.

     

    <Window x:Class="WpfApplication6.MainWindow"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            FontSize="16" Title="Test" Height="300" Width="500"
            FocusManager.FocusedElement="{Binding ElementName=mv}">
        <Grid>
            <Grid.ColumnDefinitions>
                <ColumnDefinition />
                <ColumnDefinition />
            </Grid.ColumnDefinitions>
            <Grid.Resources>
                <Style TargetType="DataGrid">
                    <Setter Property="VerticalGridLinesBrush" Value="Silver" />
                    <Setter Property="HorizontalGridLinesBrush" Value="Silver" />
                    <Setter Property="CanUserAddRows" Value="False" />
                    <Setter Property="ColumnWidth" Value="*" />
                    <Setter Property="AutoGenerateColumns" Value="False" />
                    <Setter Property="HeadersVisibility" Value="Column" />
                    <Setter Property="Background" Value="White" />
                </Style>
            </Grid.Resources>
            <DataGrid x:Name="mv" Grid.Column="0"
                ItemsSource="{Binding workers}" IsSynchronizedWithCurrentItem="True">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="workers" Binding="{Binding name}" Width="*" />
                </DataGrid.Columns>
            </DataGrid>
            <DataGrid Grid.Column="1"
                ItemsSource="{Binding ElementName=mv, Path=SelectedValue.workers_a}">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="jobs" Binding="{Binding job}" Width="*" />
                </DataGrid.Columns>
            </DataGrid>
        </Grid>
    </Window>
    
    using System.Data;
    using System.Windows;
    
    namespace WpfApplication6
    {
        public partial class MainWindow : Window
        {
            public MainWindow()
            {
                InitializeComponent();
                this.DataContext = this.LoadData();
            }
    
            object LoadData()
            {
                var a = new DataTable("a");
                a.Columns.Add("codeworker", typeof(int));
                a.Columns.Add("codejob", typeof(int));
    
                var w = new DataTable("workers");
                w.Columns.Add("id", typeof(int));
                w.Columns.Add("name", typeof(string));
    
                var j = new DataTable("jobs");
                j.Columns.Add("id", typeof(int));
                j.Columns.Add("name", typeof(string));
    
                var data = new DataSet();
                data.Tables.AddRange(new[] { a, w, j });
                data.Relations.Add("workers_a", w.Columns["id"], a.Columns["codeworker"]);
                data.Relations.Add("jobs_a", j.Columns["id"], a.Columns["codejob"]);
    
                // 'virtual' columns
                a.Columns.Add("worker", typeof(string), "parent(workers_a).name")
                    .ColumnMapping = MappingType.Hidden;
                a.Columns.Add("job", typeof(string), "parent(jobs_a).name")
                    .ColumnMapping = MappingType.Hidden;
    
                // test data
                for (int i = 0; i < 2; i++) w.Rows.Add(i, "worker" + i);
                for (int i = 0; i < 5; i++) j.Rows.Add(i, "job" + i);
                // worker0 jobs
                a.Rows.Add(0, 0); 
                a.Rows.Add(0, 1);
                a.Rows.Add(0, 2);
                // worker1 jobs
                a.Rows.Add(1, 2); 
                a.Rows.Add(1, 3);
                a.Rows.Add(1, 4);
                
                return data;
            }
        }
    }
    
    

    • Proposed as answer by Malobukv Thursday, November 17, 2011 11:31 AM
    • Unproposed as answer by hopefullProgrammer Friday, November 18, 2011 9:12 AM
    Thursday, November 17, 2011 11:21 AM
  • great answer, thanks. and thank you for answering so promptly.

    how would i afterwards update tablea with the new codeName if the user were to choose a diffrent name?

    this is very important

    thanks,

    hopefull

    Thursday, November 17, 2011 2:23 PM
  • > how would i afterwards update tablea with the new codeName if the user were to choose a diffrent name?

     
    simply change name of the table. and write:
    var a = new DataTable("newname");
    
    instead of var a = new DataTable("a");
    Thursday, November 17, 2011 2:56 PM
  • i am not using linq. i am using the solution suggested by Rajnikant

    SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=GNSEZ-PC-131;Initial Catalog=WADB;Integrated Security=True";

                conn.Open();

                SqlDataAdapter adapt = new SqlDataAdapter("select B.FirstName from tblA A inner join tblB B on(A.PersonId=B.PersonId)", conn);
                DataSet ds = new DataSet();
                adapt.Fill(ds);

                DataTable dt = ds.Tables[0];

                a1.ItemsSource = dt.DefaultView;

     so i still have the question how can i update the new code afterwards.

    also if i am binding the data to a listview what is the display member path?

    i will be very very happy if somebody can help me.

    Friday, November 18, 2011 9:08 AM
  • Hi,

    XAML Code

     <ListView x:Name="lstData"
              ItemsSource="{Binding}">
                <ListView.View>
                    <GridView>
                        <GridViewColumn Header="Name"
                          Width="Auto"
                            DisplayMemberBinding="{Binding Path=FirstName}" />
                     
                    </GridView>
                </ListView.View>
            </ListView>
    


    and code behind

    SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PCNAME;Initial Catalog=DBName;Integrated Security=True";
    
                conn.Open();
    
                SqlDataAdapter adapt = new SqlDataAdapter("select B.FirstName from wa.tblPatient A inner join wa.tblPerson B on(A.PersonId=B.PersonId)", conn);
                DataSet ds = new DataSet();
                adapt.Fill(ds);
    
                DataTable dt = ds.Tables[0];
    
                //a1.ItemsSource = dt.DefaultView;
                this.DataContext=this;
    
                // Create the GridView
                GridView gv = new GridView();
    
                // Create the GridView Columns
                foreach (DataColumn item in dt.Columns)
                {
                    GridViewColumn gvc = new GridViewColumn();
                    gvc.DisplayMemberBinding = new Binding(item.ColumnName);
                    gvc.Header = item.ColumnName;
                    gvc.Width = Double.NaN;
                    gv.Columns.Add(gvc);
                }
    
                // Setup the GridView Columns
                lstData.View = gv;
                // Display the Data
                lstData.DataContext = ds.Tables[0];
    

    Thanks,

    Rajnikant
     


    Friday, November 18, 2011 9:19 AM
  • > i am not using linq. 


    my code above does not use linq.


    > ... SqlDataAdapter adapt = new SqlDataAdapter("select B.FirstName from tblA A inner join tblB B on(A.PersonId=B.PersonId)", conn); ...


    so requirements has apparently changed. could you please elaborate:
    what fields are into the result table?

    Friday, November 18, 2011 9:41 AM
  • no, the requirments have not changed and they are as follows:

    i have one table in sql that holds codes- call this table TableCode. it has two column columnName and columnCodeJob. there is another table called  TableJobs that hold the corresponding values for the codes in TableCode. in my wpf UI I would like to display the names of all the workers and the NAME of their job. do to so i am using a join statement as purposed by rajnikant(thanks!). the only question i have left is:

    1)  how can i databind this info. to a ListBox after i have it in a dataTable - (rajnikant, i did not see how your solution can help me since a listBox does not have a property "DisplayMemberBind" and in this case i am not using a dataGrid)

    2) once the user chooses a diffrent job name for certain worker how can i update the TableCode with the new job code corresponding to the new job name that was chosen

    thank you all for you effort in trying to help me

    greatly appriciated,

    hopefull

    Saturday, November 19, 2011 4:56 PM
  • > if i am binding the data to a listview what is the display member path?

     
     
    in this case all you need is to use following xaml, instead of xaml in my previous posts above.
     
     
    <Window x:Class="WpfApplication6.MainWindow"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            FontSize="16" Title="Test" Height="300" Width="500"
            FocusManager.FocusedElement="{Binding ElementName=mv}">
        <Grid>
            <Grid.ColumnDefinitions>
                <ColumnDefinition />
                <ColumnDefinition />
            </Grid.ColumnDefinitions>
            <Grid.Resources>
                <Style x:Key="lvhs" TargetType="GridViewColumnHeader">
                    <Setter Property="Visibility" Value="Collapsed" />
                </Style>
            </Grid.Resources>
            <ListView ItemsSource="{Binding workers}" IsSynchronizedWithCurrentItem="True" x:Name="mv" Grid.Column="0">
                <ListView.View>
                    <GridView ColumnHeaderContainerStyle="{StaticResource lvhs}">
                        <GridView.Columns>
                            <GridViewColumn Header="workers" DisplayMemberBinding="{Binding name}" />
                        </GridView.Columns>
                    </GridView>
                </ListView.View>
            </ListView>
            <ListView ItemsSource="{Binding ElementName=mv, Path=SelectedValue.workers_a}" Grid.Column="1">
                <ListView.View>
                    <GridView ColumnHeaderContainerStyle="{StaticResource lvhs}">
                        <GridView.Columns>
                            <GridViewColumn Header="jobs" DisplayMemberBinding="{Binding job}" />
                        </GridView.Columns>
                    </GridView>
                </ListView.View>
            </ListView>
        </Grid>
    </Window>
    
    
      
    Saturday, November 19, 2011 6:05 PM
  • We are temporarily marking this as "Answer", if you have any concerns or new findings; please feel free to unmark this.

    Best regards.


    Annabella Luo[MSFT]
    MSDN Community Support | Feedback to us
    Friday, November 25, 2011 6:00 AM