CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Thomas
Thomas

Reputation: 13

Bind Data to Specific Columns of Datagridview - Entity Framework

I don't know if I should take all the data or just the one I need :) I am doing it for the first time. I want to bind this data to Specific columns

See select

var SQLquery = (from artikel in db.DHH_Lagerverwaltung_Artikel
join hersteller in db.DHH_Lagerverwaltung_Hersteller on artikel.ID_Hersteller equals hersteller.ID_Hersteller
join kategorie in db.DHH_Lagerverwaltung_Kategorie on artikel.ID_Kategorie equals kategorie.ID_Kategorie
join bestand in db.DHH_Lagerverwaltung_Bestand on artikel.ID_Artikelnummer equals bestand.ID_Artikelnummer
join fach in db.DHH_Lagerverwaltung_Fach on bestand.ID_Fach equals fach.ID_Fach
join stellplatz in db.DHH_Lagerverwaltung_Stellplatz on fach.ID_Stellplatz equals stellplatz.ID_Stellplatz
join ebene in db.DHH_Lagerverwaltung_Ebene on stellplatz.ID_Ebene equals ebene.ID_Ebene
join regal in db.DHH_Lagerverwaltung_Regal on ebene.ID_Regal equals regal.ID_Regal
join lager in db.DHH_Lagerverwaltung_Lager on regal.ID_Lager equals lager.ID_Lager
//where lager.Raum == ""
select new {
 ArtikelBezeichnung = artikel.Bezeichnung,
  ArtikelEAN = artikel.EAN,
  BestandsMenge = bestand.Menge,
  MinMenge = bestand.Menge,
  Lagerort = lager.Raum + regal.RegalNr + ebene.Ebene + stellplatz.Stellplatz + fach.Fach,
  Hersteller = hersteller.Name,
  Kategorie = kategorie.Name
});

Upvotes: 0

Views: 1230

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Do this one line of code, underneath the query:

dataGridViewX.DataSource = new BindingSource(SQLquery.ToList(), null);

The BindingSource can work with the List<anonymoustype> the query will create

Alternatively, because you're working with anonymous types you could also make an extension method to generate you a BindingList instead:

static class ListExtensions
{
    public static BindingList<T> ToBindingList<T>(this IList<T> source)
    {
        return new BindingList<T>(source);
    }
}

You can bind a datagridview to a bindingList:

dataGridViewX.DataSource = SQLquery.ToList().ToBindingList();

Binding through a BindingSource gives some advantages for filtering, sorting, accessing the current item etc. It also allows you to arrange hierarchical data structures. If you're going to user BindingSource you should perhaps consider NOT using anonymous types, because they're compiler generated POCO classes that you don't really have any reliable access to if you wanted to dig your bindingSource's .Current object out and cast it to something you work with.

If instead you made your class a fully defined one in your own code, then you have:

collection.Select(c => new Whatever(){ Id = c.Id, Name = c.Name });

But you can work with it better:

var x = myBindingSource.Current as Whatever;

If you use anonymous types it's that as Whatever cast that you can't easily do, and you'll end up stuck with myBindingsource.Current being an object, needing either some dynamic workaround (which is not optimal when really this is a design time known class type) or a bit of a hack where you declare another anonymous type with the same order and type of parameters and rely on the compiler making them the same thing when it creates the anonymous types

Upvotes: 1

Samuel B.
Samuel B.

Reputation: 446

Hi I created a small demo code. You just need to re-write your code and It should work. Hope it will helps you.

private class Data
    {
        public int? Id { get; set; }
        public DateTime? DateTimeShipped { get;set;}
        public DateTime? DontNeed1 { get; set; }
        public DateTime? DontNeed2 { get; set; }
        public bool? Ok { get; set; }

        public Data()
        {

        }
        public Data(int? id, DateTime? dateTime, bool? Ok, DateTime? DontNeed1, DateTime? DontNeed2)
        {
            this.Id = id;
            this.DateTimeShipped = dateTime;
            this.Ok = Ok;
            this.DontNeed1 = DontNeed1;
            this.DontNeed2 = DontNeed2;
        }
    }

Class Data holds values from linq select. Also there is Dictionary which have list of desired columns including their types. This Dictionary is used for creating columns which should be in DataGridView. 

Dictionary<string, Type> desiredCols = new Dictionary<string, Type>
{
     {"Id", typeof(int)},
     {"DateTimeShipped", typeof(DateTime)},
     { "Ok", typeof(bool)}
};

IEnumerable<Data> sqlList = (from data in dbContext.SomeTable
                  select new Data
                  {
                      Id = data.Id,
                      DateTimeShipped = data.DatumSuggestFrom,
                      Ok = data.Ok,
                      DontNeed1 = data.DatumSuggestFrom,
                      DontNeed2 = data.DatumSuggestTo
                  }).ToList();

var bindingList = new BindingList<Data>(sqlList.ToList());
foreach(var c in desiredCols)
{
    DataGridViewColumn col = new DataGridViewTextBoxColumn();
    col.Name = c.Key;
    col.HeaderText = c.Key;
    col.ValueType = c.Value;
    dataGridView1.Columns.Add(col);
 };

 foreach(var col in bindingList)
 {
     dataGridView1.Rows.Add(col.Id, col.DateTimeShipped, col.Ok);
 }

Upvotes: 0

Related Questions