dotnet windows forms databinding: I am evaluating LINQ to be used in an upcoming project.
I would like to bind a LINQ collection to a DataGridView in WindowsForm.
I used a BindingSource object and got the result displayed all right.
But how to propagate the updates back to the database?

Hi Jakob,

Based on my understanding, in your application you load data from DB into a
DataSet and use LINQ to do some query on top of this DataSet. Then you bind
the result of the LINQ query through a BindingSource component to a
DataGridView on the form. Your question is how to save updates back to DB
when the user has changed data in the DataGridView. If I'm off base, please
feel free to let me know.

After you get the result of the LINQ query, you can use the CopyToDataTable
method to move the data to a new DataTable, which is then used for binding.

When the data operations are finished, merge the new DataTable back into
the source DataTable. Then you could use a DataAdapter to update the
changes back to DB.

The following is a sample.

// Assume GetNorthwind() produces a Dataset with tables "Customers" (id,
// name, state) and "Orders" (id, customer_id, date)

Dataset nw = GetNorthwind();
DataTable customers = nw.Tables["Customers"];

// Filter: obtain DataRow objects for customers in Washington
var q = from row in customers.AsEnumerable()
where row.Field<string>("state") == "WA"
select row;

DataTable boundTable = q.CopyToDataTable();
dataGridView1.DataSource = boundTable;
// perform operations
customers.Merge(boundTable);
// update changes back to DB using a DataAdapter
...

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

I'm assuming you mean LINQ-to-SQL, and that you aren't using a custom
projection.

Such objects are tracked by the DataContext; if you're context is still
alive, you should just be able to call yourContext.SubmitChanges();
?

Marc

I have no decided design yet, I am just trying to use LINQ the best way.
I thought LINQ could be used to map data all the way from database to GUI.
In your example you use dataset as the foundation and just use LINQ to do
some manipulation of the data.

Does this mean LINQ produced collections should not be used to databind
direct?
Do I still need to have a mixture of collections and datasets in my
application?
For complex situations I tend to collections beacuse they are much more
flexible.

To have the collections generated straight from SQL with a LINQ-to-SQL layer
sounded like a great idea. But if I need to uphold a parallell structure in
datasets the advantage quickly disappears.

What are your recommendations?
I have seen lots of simple examples where LINQ is used to manipulate data.

The example I assumed is a detached data collection shown in a DataGridView.

Hi Jakob,

Thank you for your prompt response!

[quoted text, click to view]
GUI. In your example you use dataset as the foundation and just use LINQ to
do some manipulation of the data.

The feature of LINQ that you understand is called LINQ to SQL.

From the view of LINQ to Objects, LINQ to XML or LINQ to DataSets, LINQ in
and of itself is just a query mechanism. Updating an external data source
is the job of the application programmer. e.g. with LINQ to DataSets, you
can use a TableAdapter, which is external to the LINQ infrastructure, to
update a back-end database.

LINQ to SQL goes beyond this by incorporating update semantics directly
into the DataContext object, the object which serves as a data source to
LINQ to SQL. LINQ also allows for heterogeneous data sources and this can
limit and/or complicate updatability.

LINQ to SQL is possible to any back-end database that supports the
IQueryable interface. Currently, this is only the System.Data.SqlClient
managed provider, though we are working with other major database and
Provider vendors to LINQ-enable their providers.

[quoted text, click to view]
direct?

The result of a LINQ query can be bound to a WinForm or Web Form control
directly. The reason why I call the CopyToDataTable method in my sample to
move data to a new DataTable is that the query is NOT executed at all when
the query is defined. In fact, the execution occurs when the
CopyToDataTable method is called.

This is called deferred execution, which is the default in LINQ. We can
force early executioin by using the .ToList or .ToArray method to copy the
results to a collection or array.

[quoted text, click to view]
quickly disappears.

When we execute a LINQ to SQL query, the following steps take place:

1. The compiler converts the LINQ query into an expression tree.
2. The LINQ to SQL run-time constructs SQL from the expression tree.
3. A new instance of the XYZ class is generated and populated for each row
returned.
4. The DataContext tracks the original state of each object. When you add,
remove, or modify and object, and then call DataContext.SubmitChanges, it
generates SQL statements to send the changes to the back-end database.

If you have a hierarchical class structure, such as Customers, Orders, and
Order Details, the DataContext class submits the changes in a way to avoid
violating referential integrity.

The results of a query can be an entity class or it can be a projection. A
projection is an anonymous class that is created by the compiler to contain
a custom set of data.

Note that objects of anonymous type are not tracked by the DataContext and
therefore changes made to projected results cannot be saved to the back-end
database without custom application code.

If you want to be able to save the changes, always Select the entity
directly.

[quoted text, click to view]

In your scenario, I suggest that you use LINQ to SQL to retrieve data from
DB and then bind the result of the query to a DataGridView control. After
the user changes the values in the DataGridView, you call the SubmitChanges
method to save the changes back to the DB.

For more information on LINQ to SQL, please refer to the following MSDN
document:

'LINQ to SQL: .NET Language-Integrated Query for Relational Data'
http://msdn2.microsoft.com/en-us/library/bb425822.aspx

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Thanks for your extensive answer!!
It did clarify the picture.

I also read parts of the article but I still have one missing piece in my
understanding.
If I use LINQ-to-SQL and bind resulting entity classes/collections to my
forms in controls like DataGridView, how should I send the updates back?

With datasets I instantiate a dataset adapter and call its Update method
with the dataset which means all will be sorted out. The updates are all
flagged within the dataset.

But in LINQ-to-SQL I guess it is the datacontext that keeps track of the
updates? Does this mean I should keep the same datacontext alive during the
whole lifetime of my application? This gives me a potential risk. I edit
values in one form that I don't want to send to the database, then I edit
values in another form that I do want to save. Finally I call SubmitChanges

I tried the approach with a global singleton datacontext object.
It works but I am a bit hesitant as I don't have full control as to what
objects are actually submitted to database when I call SubmitChanges.

A possible scenario is this: I have a form with Customers. For each customer
there is a button that will open all orders of that customer. I edit a couple
of customers and then open the subform for one of them and edit some orders.
In the subform I want to implement a Cancel button. How do I do that?

If I kill the current datacontext object and create a new one, I will in
effect throw away all queued changes. But then I will loose all changes in
the Customer form too, which would probably surprise the user ....

I would feel a bit more safe if I could find a mechanism to just save one
kind of objects at the time. Second best would be if I could clear specified
types from the ChangeSet before I saved.

Am I on the wrong track?
LINQ-to-SQL seemed so interesting on the first look, but maybe I am

You can always obtain the item you want (Dispose() and throw away the
original context) - work with the object for a while (update properties
etc); now when you want to save, create a new context and use Attach (on the
relevant table) to tell this context about your snapshot object. Now
SubmitChanges() on the new context.

Cancel would just throw the object away.

Any use?

Marc

Thanks Marc for your answer.
Your suggestion sounds very promising!!!
I didn't find the Attach method before, but it might be the missing piece in
my puzzle.

When I read the documentation I found that a timestamp column was
recommended so I added one to my table and then tried the following.

private void btnLoad_Click(object sender, EventArgs e)
{
MyDataContext dc = new MyDataContext();
BindingSource bs = new BindingSource();
bs.DataSource = from p in dc.TextTypes
orderby p.TextTypeName
select p;
myDataGridView.DataSource = bs;
dc.Dispose();
}

private void btnLoad_Click(object sender, EventArgs e)
{
BindingSource bs = (BindingSource)myDataGridView.DataSource;
IEnumerable<TextType> textTypes = (IEnumerable<TextType>)bs.DataSource;
MyDataContext dc = new MyDataContext();
dc.TextTypes.AttachAll(textTypes, true);
}
The AttachAll method threw a NotSupportedException with the following message:
"Cannot access a disposed object.
Object name: 'DataContext accessed after Dispose.'."

Question 1: Does the collection maintain some kind of connection to the
DataContext that makes it necessary to keep it alive?


I then removed the Dispose call which somehow kept the context alive.
But then I received another NotSupportedException:
"An attempt has been made to Attach or Add an entity that is not new,
perhaps having been loaded from another DataContext. This is not supported."

Question 2: How should Attach/AttachAll be used?
I thought the idea was to attach entities that were not created in the

Thanks for your extensive answer.
I think I am slowly grasping the concept of the LINQ-to-SQL object.

Probably it is best not to force the concept to do what it is not intended
to. I guess the problem is to get rid of old thinking from datasets and
realize that this is something totally different.

A global datacontext is still too dangerous, and Attach method will not
really work in my scenario. My compromise is rather to use different
datacontext objects in limited situations. A form with data will probably be
a good candidate for a datacontext that is alive as long as the form is, and
will then very conveniently serve as an automatic update mechanism. This
means I can still get rid of all the data mapping.

Hi Jakob,

The Attach/AttachAll method is usually used in the scenario where the entity
objects are serialized to client over a network and detached from their data
context and the clients require to update or delete data.

Note that only call the Attach methods on new or deserialized entities. The
only way for an entity to be detached from its original data context is for
it to be serialized. If you try to attach an undetached entity to a new data
context, and that entity still has deferred loaders from its previous data
context, LINQ to SQL will thrown an exception.

For more information on how to use the Attach/AttachAll method, please refer
to the following MSDN document:
'Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL)'
http://msdn2.microsoft.com/en-us/library/bb546187.aspx

As for your original question of how to cancel current editing, LINQ to SQL
doesn't provide such a function so far. A workaround is to implement the
IEditableObject interface on the entity class. The following is a sample:

public class Customer : IEditableObject
{
struct CustomerData
{
internal string id;
internal string firstName;
internal string lastName;
}

private CustomerData custData;
private CustomerData backupData;
private bool inTxn = false;

// Implements IEditableObject
void IEditableObject.BeginEdit()
{
Console.WriteLine("Start BeginEdit");
if (!inTxn)
{
this.backupData = custData;
inTxn = true;
Console.WriteLine("BeginEdit - " +
this.backupData.lastName);
}
Console.WriteLine("End BeginEdit");
}

void IEditableObject.CancelEdit()
{
Console.WriteLine("Start CancelEdit");
if (inTxn)
{
this.custData = backupData;
inTxn = false;
Console.WriteLine("CancelEdit - " + this.custData.lastName);
}
Console.WriteLine("End CancelEdit");
}

void IEditableObject.EndEdit()
{
Console.WriteLine("Start EndEdit" + this.custData.id +
this.custData.lastName);
if (inTxn)
{
backupData = new CustomerData();
inTxn = false;
Console.WriteLine("Done EndEdit - " + this.custData.id +
this.custData.lastName);
}
Console.WriteLine("End EndEdit");
}

public Customer(string ID)
: base()
{
this.custData = new CustomerData();
this.custData.id = ID;
this.custData.firstName = "";
this.custData.lastName = "";
}

public string ID
{
get
{
return this.custData.id;
}
}

public string FirstName
{
get
{
return this.custData.firstName;
}
set
{
this.custData.firstName = value;
}
}

public string LastName
{
get
{
return this.custData.lastName;
}
set
{
this.custData.lastName = value;
}
}
}

Suppose that you bind the LINQ query result to a BindingSource named "bs",
then you can call the CancelCurrentEdit method to cancel the current editing
as follows:
this.BindingContext[bs].CancelCurrentEdit();

Your other questions:
[quoted text, click to view]

Call the SubmitChanges method on the DataContext object.

[quoted text, click to view]

Yes, it is.

[quoted text, click to view]

No, what you want is not available so far.
When you make the call of SubmitChanges, the DataContext tries to translate
your changes into equivalent SQL commands. You can use your own custom logic
to override these actions, but the order of submission is orchestrated by a
service of the DataContext known as the change processor.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.