question

Dmitriy-2994 avatar image
0 Votes"
Dmitriy-2994 asked ·

How to combine three tables as a data source for Binding in a DataGrid?

I am creating an WPF app with MVVM. I have three tables as sources of my data which I need to display in the DataGrid through the ItemsSource="{Binding}". How and where do I combine this three tables so I could use the combined data set for my binding?

To add more details.

In the DataGrid I need to display the list of all books with column1 as book title (table Titles) and column2 as all authors of the book (table Authors) so I am thinking I need to perform a natural join operation of the table Titles on table AuthorISBN on table Authors. This part I get, but in where and how, I don't.

Any insight would be greatly appreciated.

85519-struct2.png 85518-struct1.png


dotnet-csharpdotnet-wpf-xaml
struct1.png (6.0 KiB)
struct2.png (7.7 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterFleischer-3316 avatar image
1 Vote"
PeterFleischer-3316 answered ·

Hi,
try following demo:

XAML:

 <Window x:Class="WpfApp1.Window038"
         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:WpfApp038"
         mc:Ignorable="d"
         Title="Dmitriy-2994 Datagrid embedded tables" Height="450" Width="800">
   <Window.DataContext>
     <local:ViewModel/>
   </Window.DataContext>
   <Grid>
     <DataGrid ItemsSource="{Binding View}"/>
   </Grid>
 </Window>

And classes:

 using System.Collections.ObjectModel;
 using System.ComponentModel;
 using System.Linq;
 using System.Windows;
 using System.Windows.Data;
    
 namespace WpfApp038
 {
   public class ViewModel
   {
     public ViewModel() =>cvs.Source = GetData();   
    
     ObservableCollection<Data> colData = new ObservableCollection<Data>();
     CollectionViewSource cvs = new CollectionViewSource();
     public ICollectionView View { get => cvs.View; }
     private ObservableCollection<Data> GetData()
     {
       var colISBN = LoadAuthorISBN();
       var colAuthors = LoadAuthors();
       var colTitles = LoadTitles();
       var query = from isbn in colISBN
                   join author in colAuthors on isbn.AuthorID equals author.AuthorID
                   join title in colTitles on isbn.ISBN equals title.AuthorISBN
                   select new Data() { Author = $"{author.FirstName} {author.LastName}", ISBN = isbn.ISBN, Title = title.Title };
       return new ObservableCollection<Data>(query);
     }
    
     private ObservableCollection<AuthorISBN> LoadAuthorISBN()
     {
       ObservableCollection<AuthorISBN > col = new ObservableCollection<AuthorISBN>();
       for (int i = 1; i < 10; i++) col.Add(new AuthorISBN() { AuthorID = i, ISBN = $"ISBN {i}" });
       return col;
     }
    
     private ObservableCollection<Authors> LoadAuthors()
     {
       ObservableCollection<Authors> col = new ObservableCollection<Authors>();
       for (int i = 1; i < 10; i++) col.Add(new Authors() { AuthorID = i,FirstName = $"FirstName{i}", LastName = $"LastName{i}" });
       return col;
     }
    
     private ObservableCollection<Titles>  LoadTitles()
     {
       ObservableCollection<Titles> col = new ObservableCollection<Titles>();
       for (int i = 1; i < 10; i++) col.Add(new Titles() { AuthorISBN = $"ISBN {i}", Title=$"Title {i}" });
       return col;
     }
   }
    
   public class Data
   {
     public string Title { get; set; }
     public string ISBN { get; set; }
     public string Author { get; set; }
    
   }
    
   internal class AuthorISBN
   {
     internal int AuthorID { get; set; }
     internal string ISBN { get; set; }
   }
   internal class Authors
   {
     internal int AuthorID { get; set; }
     internal string FirstName { get; set; }
     internal string LastName { get; set; }
   }
   internal class Titles
   {
     internal string AuthorISBN { get; set; }
     internal string Title { get; set; }
   }
 }

Result:

85775-x.png



x.png (45.2 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Very cool. Thank you for your solution. I shall learn from it and improve my skills.

0 Votes 0 ·
DaisyTian-MSFT avatar image
1 Vote"
DaisyTian-MSFT answered ·

I make a demo based on your description as below:

 public class ViewModel
     {
         public ObservableCollection<Model> models { get; set; } = new ObservableCollection<Model>();
         public ViewModel()
         {
             string connsql = @"Data Source = (localdb)\ProjectsV13; Initial Catalog = *******.......";
             try
             {
                 using (SqlConnection conn = new SqlConnection())
                 {
                     conn.ConnectionString = connsql;
                     conn.Open();
                     String sql = "select T.Title,T.ISBN,A.FirstName,A.LastName from dbo.Titles as T inner join dbo.AuthorISBN as AI on T.ISBN = AI.ISBN inner join dbo.Author as A on AI.AuthorID = A.AuthorID";
                     SqlCommand cmd = new SqlCommand(sql, conn);
                     SqlDataReader dr = cmd.ExecuteReader();
                     while (dr.Read())
                     {
                         Model model = new Model();
                         model.FirstName = dr["FirstName"].ToString();
                         model.LastName = dr["LastName"].ToString();
                         model.Title = dr["Title"].ToString();
                         models.Add(model);
                     }
                     conn.Close();
                 }
             }
             catch (Exception ex)
             {
                 Debug.WriteLine(ex.Message.ToString());
             }
         }
    
     }
     public class Model
     {
         public string Title { get; set; }
         public string FirstName { get; set; }
         public string LastName { get; set; }
     }

The Xaml is:

       <Window.DataContext>
             <local:ViewModel></local:ViewModel>
         </Window.DataContext>
         <Grid>
             <DataGrid ItemsSource="{Binding models}"></DataGrid>
         </Grid>

The result picture is:
85672-capture.png

Did I miss any info for your requirement? If I did, please point out.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


capture.png (4.7 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Very nice solution. The only problem is, the classes (tables) with data are already sitting inside of my solution (there was a pre-request from using the Scaffold-DbContext to create the classes), so there is no need to create a connection to the external database. Never the less, your code is right on point when it to comes to the primary functionality.

0 Votes 0 ·
DuaneArnold-0443 avatar image
1 Vote"
DuaneArnold-0443 answered ·

@Dmitriy-2994

IMHO, what you need to send is a custom class/object with properties in a List<T> the List<T> is blindable.

You can do a join sure, but you should learn how to use a datareader.

The link below should help you understand how to do the join and then select the column names via the datareader. It doesn't matter that it talking MS SQL server when you are using Access, becuase the use of T-SQL and using a ADO.NET datareader are the same even if using Access.

http://www.mikepope.com/blog/AddComment.aspx?blogid=1718

It's VB but you should be able to understand it and convert it to c#.

https://www.tek-tips.com/faqs.cfm?fid=3727

In this example, you can see how you can access the query results using column names via the datareader.

https://www.c-sharpcorner.com/uploadfile/mahesh/datareader-in-ado-net/

You can create a custom class/object a container object.

https://gunnarpeipman.com/csharp-automatic-properties/

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/auto-implemented-properties

https://www.c-sharpcorner.com/UploadFile/mahesh/create-a-list-of-objects-in-C-Sharp/

instance a new list that's strong typed to the customx.cs

customxlist List<customx> = new List<customx>();

while in reader loop..

var custx = new customx()

populate the custx public properties from the datareader columns, which would be the selected columns you picked in the join of tables.

add the custx to the customxlis

end of loop..

bind the customxlist to the control.

HTH




· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for you input. I will try to go through the materials you have supplied and learn from them.

0 Votes 0 ·