Linq-SQL canonical editable datagridview sample code
Andrus
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
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
screencast is avaliable from Channel9 of MSDN. This is the link,
http://channel9.msdn.com/ShowPost.aspx?PostID=335058#335058
HTH
"Andrus" <kobru...@hot.ee> wrote in message
news:utYyH29k...@TK2MSFTNGP05.phx.gbl...
Andrus
> 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
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
>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
BindingList<T> already does this, calling RemoveItem correctly...
hmmm... I'll investigate...
Marc Gravell
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
>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
> 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
index etc) since they aren't available as protected... I added
Console.WriteLine to RemoveItem, and it seemed to be working fine...
Marc
Andrus
> 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
stacktrace, and I'm not in a desparate hurry to pick through your full
bespoke code... sorry again...
Marc
Andrus
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
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
Andrus
>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
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
> 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
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.
Andrus
> Call me crazy, but a drop-down isn't the first choice I'd use for
> this!
What control I must use in grid ?
I need also show some butotn for active cell so user can can open picklist
using mouse.
Combobox has all required ui elements.
> 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.
Should I check boolean field and issue CancelNew before calling AddNewCore()
if I detect that
previous new row is not committed ?
Andrus.
Marc Gravell
search dialog?
> I need also show some butotn for active cell so user can can open picklist
> using mouse.
> Combobox has all required ui elements.
Fair enough...
> Should I check boolean [snip]
I simply don't know. Sorry.
Marc
Andrus
>Maybe a link label, and handle the even by presenting a sensible
>search dialog?
There must be possibility to enter customer name from keyboard directly to
list.
So this requires to create separate linklabel column in grid. Is this
reasonable ?
> I simply don't know. Sorry.
It seems that AddNewCore() calls ListChanged() event which confuses grid. I
fixed this by using
RaiseListChangedEvents = false;
row = base.AddNewCore() as T;
RaiseListChangedEvents = true;
Hope this is OK.
Andrus.
Andrus
in previous message
WPF: Custom control and resorce files in a different assembly.
I was unable to run this solution:
Error 1 Metadata file
'C:\CustomExpanderControl\TestHarnes\CustomExpander\bin\Release\CustomControls.dll'
could not be found TestHarnes
Error 2 Cannot find the type 'res:CustomResources'. Note that type names are
case sensitive. Line 22 Position 27.
C:\CustomExpanderControl\TestHarnes\CustomExpander\Controls\TestExpander.xaml
22 27 CustomControls
This attachment seems not related to your question.
I'm using Marc code sample with one fix without issues.
I do'nt remember which issue I described in post you referenced.
Andrus.
"Ashley Childs" <ashley...@hotmail.com> wrote in message
news:e503AeE4...@TK2MSFTNGP04.phx.gbl...
> Andrus,
>
> I have come the issue you outlined in this log and I would say it makes
> the experience of developeing LINQ with DataGridViews very painful.
> Please can you tell me if you managed to resplve this issue with you
> last post and if so in which event did you place your code?