1_
Read last name of the first person in the table
C# BasicsEntities m_context = new BasicsEntities(); .. IQueryable<person> personsQuery = m_context.CreateObjectSet<person>(); string lastName = personsQuery.First().lastName;
2_
Query/join data from Persons and Departments tables
C# IQueryable<person> personsWithDepartmentsQuery = m_context.CreateObjectSet<person>().Include("department"); string deptName = personsWithDepartmentsQuery.First().department.name;
3_
Use LINQ syntax to retrive data from Persons table
C# IQueryable query = from p in m_context.persons select p;
4_
Display list of last names in Listbox
C# using System.Data.Objects; // System.Data.Entity.dll ... IQueryable query = from p in m_context.persons select p; listBox1.DisplayMember = "LastName"; listBox1.DataSource = ((ObjectQuery) query).Execute(MergeOption.AppendOnly);
5_
Add person
C# person person1 = new person { firstName = "Zbynek", lastName = "Cernin", department_id = 1 }; m_context.persons.AddObject(person1); m_context.SaveChanges();
6_
Delete last person
C# m_context.persons.DeleteObject(m_context.persons.Last()); m_context.SaveChanges();
7_
Read Persons table into DataGridView and save changes made by user - EF6
C#
// load data
IQueryable query = from pers in m_context.persons
select pers;
dataGridView1.DataSource = ((ObjectQuery)query).Execute(MergeOption.AppendOnly);
// or based on you data model
// dataGridView1.DataSource = ((System.Data.Entity.Infrastructure.DbQuery<Person>>)query).ToList();
//Save changes after edit
dataGridView1.EndEdit();
m_context.SaveChanges();
8_
Delete multiple selected rows DataGridView - EF6
C# // EF 6 // code for a button event handler // delete rows in database foreach (var row in dataGridView1.SelectedRows) { object o = ((DataGridViewRow)row).Cells[ID_INDEX].Value; int id = Convert.ToInt32(o); // (int)o throws InvalidCastException _dbContext.Persons.Remove(_dbContext.ElementExtern.Where(el => el.Id == id).First()); } _dbContext.SaveChanges(); // delete rows in Grid for (int i = dataGridView1.Rows.Count - 1; i >= 0; i--) { if (dataGridView1.Rows[i].Selected) { dataGridView1.Rows.RemoveAt(i); // dataGridView1.DataSource must be bound to BindingSource in order to allow RemoveAt() to run } }
9_
Load, edit and save data from 2 tables (Persons, Department) in one DataGridView - EF6
In order to achieve this you need to create a helper class that will hold references to original entities
loaded by entity framework and properties for the columns that will be shown in the DataGridView
C# public class QueryData { public Person Person; public Department Department; public long Id { get { return Person.Id; } } public string Name { get { return Person.Name; } set { Person.Name = value; } } public string DepartmentName { get { return Department.Name; } set { Department.Name = value; } } }Then it is possible to read data and display it in the DataGridView
C# var query = from p in _dbContext.Persons join d in _dbContext.Department on p.DepartmentId equals d.Id into d1 from d2 in d1.DefaultIfEmpty() select new QueryData { Person = p, Department = d2 }; BindingSource bs = new BindingSource(); // without BindingSource it is not possible to call: dataGridView1.Rows.RemoveAt(i); bs.DataSource = query.ToList(); dataGridView1.DataSource = bs; dataGridView1.Refresh(); }
10_
Use combo box for enumerated values in DataGridView
You need a class that represents internal data (e.g. value in DB) and text presented to the user
C# public class Gender { public int Code { get; set;} public string Name { get; set; } }Create list manually or read it from DB (e.g .from a table)
C# List>Gender< genders = new List>Gender<(); genders.Add(new ElementType() { TypeCode = "0", Name = "Female" }); genders.Add(new ElementType() { TypeCode = "1", Name = "Male" });Create combo box column type
C# DataGridViewComboBoxColumn columnType = new DataGridViewComboBoxColumn(); columnType.DataPropertyName = "Gender"; // name of the public property in the class that is datasource for the DataGridView (QueryData in the example above) columnType.DataSource = genders; columnType.ValueMember = "TypeCode"; columnType.DisplayMember = "Name";Assign it to the DataGridView
C# dataGridView1.Columns.Insert(2, columnType);