Linq-SQL canonical editable datagridview sample code

[Andrus]

I'm looking for a WinForms Linq-SQL editable DataGridView sample which
performs insert, update, delete
on entity list returned from Linq-SQL query and saves changes to database.

Where to find such sample code ?

Andrus.

 

[Marc Gravell]

At the most basic level, simply set the DataSource to the result of a
table's GetNewBindingList()

Testing this with the ctx Log attached to Console.Out, it seems to be doing
INSERT, UPDATE and DELETE correctly.

Marc

 

[DSK Chakravarthy]

Firth Screen cast that i always recommend people is by Amanda Silver. This
screencast is avaliable from Channel9 of MSDN. This is the link,
http://channel9.msdn.com/ShowPost.aspx?PostID=335058#335058

HTH

 

[Andrus]

Marc,

At the most basic level, simply set the DataSource to the result of a
table's GetNewBindingList()

Thank you.
I need to edit table rows returned by query. GetNewBindingList() is not
available for query.
So I tried the following code:

Northwind db = CreateDB();
var q = from c in db.Customers
where c.City == "London"
select c;

var list = new BindingList<Customer>(q.ToList());
DataGridView grid = new DataGridView { Dock = DockStyle.Fill,
DataSource = list };
Form f = new Form { Controls = { grid } };
Application.Run(f);
db.SubmitChanges();

Is this best way ?
Log shows that insert and delete commands are not generated.
Update command is generated and works OK.
Should insert and delete work also or should I change this code?

Andrus.

 

[Marc Gravell]

I don't know if there is an easier way, but how about:


using System;
using System.Collections.Generic;
using System.Linq;
using ConsoleApplication3;
using System.ComponentModel;
using System.Data.Linq;
using System.Windows.Forms;



class Program
{
static void Main()
{
Application.EnableVisualStyles();
using (NWindDataContext ctx = new NWindDataContext())
{
ctx.Log = Console.Out;
TableList<Supplier> suppliers = new TableList<Supplier>(
ctx.Suppliers,
ctx.Suppliers.Where(s => s.Country == "UK")
);
Button btn = new Button {
Text = "Commit",
Dock = DockStyle.Top
};
btn.Click += delegate { ctx.SubmitChanges(); };

Application.Run
(
new Form {
Text = "LINQ-to-SQL / DataGridView demo",
Controls = {
btn,
new DataGridView
{
Dock = DockStyle.Fill,
DataSource = suppliers
}
}
}
);
}
}
}

public class TableList<T> : BindingList<T> where T : class
{
private readonly Table<T> table;
public TableList(Table<T> table, IEnumerable<T> data)
{
if (table == null) throw new ArgumentNullException("table");
this.table = table;
if (data != null)
{
RaiseListChangedEvents = false;
foreach (T row in data)
{
Add(row);
}
RaiseListChangedEvents = true;
}
}
protected override void RemoveItem(int index)
{
T row = this[index];
base.RemoveItem(index);
table.DeleteOnSubmit(row);
}
protected override object AddNewCore()
{
T row = base.AddNewCore() as T;
if (row != null)
{
table.InsertOnSubmit(row);
}
return row;
}
}

 

[Andrus]

Marc,

I don't know if there is an easier way, but how about:

Thank you very much. I have issue on implementing add row properly using
this.

User presses down arrow in last row in grid starting adding new row.
Then user changes its mind desiding that new row should not added and
presses up arrow.
DataGridView does not show this unfinished row anymore.

However entity remains in DataContext and is added to database on
SubmitChanges.
How to prevent this ghost entity addition ?

Andrus.

 

[Marc Gravell]

Probably you'd need to implement ICancelAddNew, but taking a peek,
BindingList<T> already does this, calling RemoveItem correctly...
hmmm... I'll investigate...

 

[Marc Gravell]

I've looked at this with LINQ-to-SQL, and I cannot reproduce the
issue; if I add and remove a row (in any combination of immediate and
delayed cancel), then no change is applied to the database.

So: are you using LINQ-to-SQL, or are you using DbLinq? If the latter,
then I suspect it has "issues" noticing this trivial change (trivial
meant in the literal sense - not intended to be patronising).

You might be able to write the list to track insertions itself, but
this should be the job of the data-context. Of course, if this
actually is LINQ-to-SQL, please let me know and I'll retry...

Marc

 

[Andrus]

Marc,

Probably you'd need to implement ICancelAddNew, but taking a peek,
BindingList<T> already does this, calling RemoveItem correctly...

I observed the following:

1. Pressing down arrow in last line of DataGridView calls AddNewCore() which
adds new entity to
DataContext.

2. Pressing Up Arrow doest *not* call RemoveItem. So added fake entity will
me saved on SubmitChanges().

I'm wondering how Linq-SQL performs this correctly since RemoveItem() is not
called.

I'm using DbLinq but this should not depend on Linq provider.

Andrus.

 

[Andrus]

Marc,

I've looked at this with LINQ-to-SQL, and I cannot reproduce the
issue; if I add and remove a row (in any combination of immediate and
delayed cancel), then no change is applied to the database.

I'm planning to fix this in the following way:

1. AddNewCode() assigns new entity to property only, will not add to
DataContext
2. Override ICancelAddNew EndNew() method and add new entity to DataContext
in this method.

Will this work OK ?

Andrus.

 

[Marc Gravell]

Probably - but you can't *fully* verify the cancel conditions (the
index etc) since they aren't available as protected... I added
Console.WriteLine to RemoveItem, and it seemed to be working fine...

Marc

 

[Andrus]

Marc,

Probably - but you can't *fully* verify the cancel conditions (the
index etc) since they aren't available as protected... I added
Console.WriteLine to RemoveItem, and it seemed to be working fine...

I found that this occurs only when I move up-down in my
CustomDataGridViewComBobox column.

Down arrow in this column invokes AddNewCore().
Up arrow does *not* invoke RemoveItem.
Pressing down arrow again causes InvalidOperationException in
base.AddNewCore()

It seems that my combobox column class blocks bindinglist RemoveItem call.
I checked my class overridden methods and it seems that I'm calling base
methods in most cases.
Which combobox columns method calls bindinglist RemoveItem() ?
I implemented combobox column using MSDN sample code.

Any idea how to debug / resolve this issue? Exception which I got is below.

Andrus.

System.InvalidOperationException was unhandled
Message="Operation is not valid due to the current state of the object."
Source="System.Windows.Forms"
StackTrace:
at
System.Windows.Forms.DataGridView.DataGridViewDataConnection.ProcessListChanged(ListChangedEventArgs
e)
at
System.Windows.Forms.DataGridView.DataGridViewDataConnection.currencyManager_ListChanged(Object
sender, ListChangedEventArgs e)
at
System.Windows.Forms.CurrencyManager.OnListChanged(ListChangedEventArgs e)
at System.Windows.Forms.CurrencyManager.List_ListChanged(Object
sender, ListChangedEventArgs e)
at
System.ComponentModel.BindingList`1.OnListChanged(ListChangedEventArgs e)
at System.ComponentModel.BindingList`1.InsertItem(Int32 index, T
item)
at System.Collections.ObjectModel.Collection`1.Add(T item)
at System.ComponentModel.BindingList`1.AddNewCore()
at MyAppl.TableList`1.AddNewCore() in I:\MyAppl\TableList.cs:line 92
at
System.ComponentModel.BindingList`1.System.ComponentModel.IBindingList.AddNew()
at System.Windows.Forms.CurrencyManager.AddNew()
at
System.Windows.Forms.DataGridView.DataGridViewDataConnection.AddNew()
at
System.Windows.Forms.DataGridView.DataGridViewDataConnection.OnNewRowNeeded()
at System.Windows.Forms.DataGridView.OnRowEnter(DataGridViewCell&
dataGridViewCell, Int32 columnIndex, Int32 rowIndex, Boolean
canCreateNewRow, Boolean validationFailureOccurred)
at System.Windows.Forms.DataGridView.SetCurrentCellAddressCore(Int32
columnIndex, Int32 rowIndex, Boolean setAnchorCellAddress, Boolean
validateCurrentCell, Boolean throughMouseClick)
at System.Windows.Forms.DataGridView.ProcessDownKeyInternal(Keys
keyData, Boolean& moved)
at System.Windows.Forms.DataGridView.ProcessDownKey(Keys keyData)
at
System.Windows.Forms.DataGridView.ProcessDataGridViewKey(KeyEventArgs e)
at System.Windows.Forms.DataGridView.ProcessKeyPreview(Message& m)
at System.Windows.Forms.Control.ProcessKeyPreview(Message& m)
at System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
at System.Windows.Forms.ComboBox.ChildWndProc(Message& m)
at
System.Windows.Forms.ComboBox.ComboBoxChildNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
msg)
at
....

 

[Marc Gravell]

Sorry - I don't think I'm going to be able to debug that just from a
stacktrace, and I'm not in a desparate hurry to pick through your full
bespoke code... sorry again...

Marc

 

[Andrus]

Marc,

I discovered that this occurs when all following conditions are met:

1. DataGridView contains single row
2. DataGridViewColumn column is DataGridViewComboBoxColumn
3. EditMode is EditOnEnter

In this case pressing up arrow in new row does *not* call BindingList
RemoveItem() method.

Is this .NET bug ? Any idea how to fix it ?

Andrus.

 

[Andrus]

Following code reproduces DataGridView crash on edit.
Press down arrow, up arrow, down arrow. Exception occurs.

Any idea how to fix ?

Andrus.

using System;
using System.Windows.Forms;
using System.ComponentModel;

class Supplier {
public string Id { get; set; }
}

class Form1 : Form {
[STAThread]
static void Main() {
Application.Run(new Form1());
}

public Form1() {
DataGridView grid = new DataGridView();
// if this line is commented out, all is OK:
grid.EditMode = DataGridViewEditMode.EditOnEnter;
ComboBoxColumn comboBoxColumn = new ComboBoxColumn();
ComboBoxCell ComboBoxCell = new ComboBoxCell();
comboBoxColumn.CellTemplate = ComboBoxCell;
grid.Columns.Add(comboBoxColumn);
BindingList<Supplier> l = new BindingList<Supplier>();
l.Add(new Supplier());
grid.DataSource = l;
Controls.Add(grid);
}

class ComboBoxColumn : DataGridViewComboBoxColumn { }

class ComboBoxCell : DataGridViewComboBoxCell {
public override Type EditType {
get {
return typeof(ComboBoxEditingControl);
}
}

}

class ComboBoxEditingControl : ComboBox, IDataGridViewEditingControl {
protected int rowIndex;
protected DataGridView dataGridView;
protected bool valueChanged = false;

protected override void OnTextChanged(EventArgs e) {
base.OnTextChanged(e);
NotifyDataGridViewOfValueChange();
}

protected virtual void NotifyDataGridViewOfValueChange() {
valueChanged = true;
if (dataGridView != null) {
dataGridView.NotifyCurrentCellDirty(true);
}
}

public Cursor EditingPanelCursor {
get {
return Cursors.IBeam;
}
}

public DataGridView EditingControlDataGridView {
get {
return dataGridView;
}
set {
dataGridView = value;
}
}

public object EditingControlFormattedValue {
set {
if (value.ToString() != Text) {
Text = value.ToString();
NotifyDataGridViewOfValueChange();
}
}

get {
return Text;
}
}

public object
GetEditingControlFormattedValue(DataGridViewDataErrorContexts
context) {
return Text;
}



public void PrepareEditingControlForEdit(bool selectAll) { }

public bool RepositionEditingControlOnValueChange {
get {
return false;
}
}

public int EditingControlRowIndex {
get {
return rowIndex;
}

set {
rowIndex = value;
}
}

public void ApplyCellStyleToEditingControl(DataGridViewCellStyle
dataGridViewCellStyle) {
DropDownStyle = ComboBoxStyle.DropDown;
}

public bool EditingControlWantsInputKey(Keys keyData, bool
dataGridViewWantsInputKey) {
return !dataGridViewWantsInputKey;
}

public bool EditingControlValueChanged {

get {
return valueChanged;
}
set {
valueChanged = value;
}
}
}
}

 

[Marc Gravell]

I will look later; I can't promise anything...

 

[Andrus]

Marc,

I will look later; I can't promise anything...

Thank you.

I noticed that if I comment out OnTextChanged() override, exception does not
occur.
However this code is from MSDN sample.

Andrus.

 

[Marc Gravell]

Can you (briefly) remind me what the purpose of this custom column is?
I'm reaching the conclusion that (sample or not) trying to implement
this from scratch is going to be hard; can you not just modify the
behavior of the existing control? For example - if you just want to
support up/down keys etc:

class ComboBoxEditingControl : DataGridViewComboBoxEditingControl
{
private void ChangeUpDown(bool up)
{
DataGridViewCell cell =
EditingControlDataGridView.CurrentCell;
if (cell == null) return;

int row = cell.RowIndex, col = cell.ColumnIndex;
if (up) row--; else row++;
if (row >= 0 && row < EditingControlDataGridView.RowCount
&& EditingControlDataGridView.EndEdit())
{
cell = EditingControlDataGridView.Rows[row].Cells[col];
EditingControlDataGridView.CurrentCell = cell;
}
}
public override bool EditingControlWantsInputKey(Keys keyData,
bool dataGridViewWantsInputKey)
{
switch (keyData)
{
case Keys.Up:
BeginInvoke((MethodInvoker)delegate {
ChangeUpDown(true);
});
return true;
case Keys.Down:
BeginInvoke((MethodInvoker)delegate {
ChangeUpDown(false);
});
return true;
case Keys.Enter:
BeginInvoke((MethodInvoker)delegate {
EditingControlDataGridView.EndEdit(); });
return true;
case Keys.Escape:
BeginInvoke((MethodInvoker)delegate {
EditingControlDataGridView.CancelEdit(); });
return true;
default:
return base.EditingControlWantsInputKey(keyData,
dataGridViewWantsInputKey);
}
}
}

 

[Andrus]

Marc,

Can you (briefly) remind me what the purpose of this custom column is?

Custom column is used to host virtual foreign key ComboBox.
There may be 50000 customers in customer table. So customer name combobox
data source should be populated dynamically.

I use subclassed DataGridViewComboBoxCell GetFormattedValue() event to
populate combobox datasource
on the fly by calling Combobox datasource bindinglist special
AddIfNotExists()
method:

protected override object GetFormattedValue(object value, int rowIndex, ref
DataGridViewCellStyle cellStyle,
TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter,
DataGridViewDataErrorContexts context) {

ComboBoxColumn comboBoxColumn = OwningColumn as ComboBoxColumn;

comboBoxColumn.PickList.AddIfNotExists(value);

return base.GetFormattedValue(value, rowIndex, ref cellStyle,
valueTypeConverter, formattedValueTypeConverter, context);

Custom combobox column implementation is required to allow grid to
host this combobox.
I havent way any other method to allow enter customers by name in grid.

I'm reaching the conclusion that (sample or not) trying to implement
this from scratch is going to be hard;

Probably DataGridView does not call ICancelAddNew.EndNew() method.
New is remains in uncommited state when AddNewCore() is called. AddNewCore()
throws Invalid Operation exception.
I think there must be simple one line fix which fixes this. Probably
something simple is missing or wrong in custom column implementation. Or is
it possible to call EndNew() method itself from this code ?

can you not just modify the behavior of the existing control?

Should I really add event hander to GetFormattedValue() method ? There are
also other methods which needs to be overridden.
Should I try to add event handlers into all places ? MSDN recomments
subclassing and overriding methods as preferred technique for this.

For example - if you just want to
support up/down keys etc:

The goal is to allow enter data using foreign keys when lookup table is big
and
resides in server.

Andrus.

 

[Marc Gravell]

Call me crazy, but a drop-down isn't the first choice I'd use for
this!

I don't know where the problem is; normally, EndNew/CancelNew are used
correctly, but in the code you posted RemoveItem indeed doesn't get
called. I dont' know why.