SqlCeResultset and Bindingsource

I have created an entry form by creating a resultset data source
(Categories) from a sql mobile database(Northwind), setting the resulset to
Details (data source view) and dragging the datasource the form.

Now I can easily accomplish navigation using the bindingsource but I don't
now how to accomplish the following.
If the user wants to add a record, I call the AddNew method of the
bindingsource. Now when the user enters a Category ID that already exists I
need to set the Bindingsource the corresponding record.

I can't use the Find method of the bindingsource since the sqlceresult
apparently doesn't support this. I tried using the seek method of the
sqlceresultset but then I have no idea how to update the bindingsource to
the correct record.

How can I accomplish this

thanx.
Mark

Smart Device Development13

You may want to try the following:

1. Use IndexOf() method on the BindingSource. This will return the index of the first item in the bound list.

2. Use the index information to reset the current item in the list by using Position property.

3. Retrieve the current item through the Current property, and the entire list can be retrieved through the List property.

4. Editing operations are supported on the current item through Current and the RemoveCurrent, EndEdit, CancelEdit and Add and AddNew methods.

Currency management is handled automatically for all underlying data source types.

Manav

Hi,

thanx but still no luck.

If I use the IndexOf() method with parameter null it returns -1.

Is there no way to update the BindingSource so that it will sync with the underlying resultset

There must be someway to accomplish this.

You need to read about how data binding works in .NET. In simple words UI Control is bound to a BindingSource which is bound to an underlying database via resultset or other ADO.NET class. Read about BindingSource here http://msdn2.microsoft.com/en-us/library/system.windows.forms.bindingsource(VS.80).aspx

i.e. UI Control (e.g. List Box) <--> BindingSource <--> Database

The currency management i.e. keeping the UI and dB in sync is done by the BindingSource so if you navigate to an item in UI e.g. List Box than internally the pointer (position) is moved to the correct dB record in the resultset.

What does this mean Use IndexOf() method to find the index of the record and when index is found than move to that index via position property. This will internally keep the List, BindingSource and dB in sync.

Don't use IndexOf() with null values it will return -1, instead use it with valid values else have logic to handle the -1 case.

Manav

Ok,

the problem then is since my Bindingsource is bound to a scqlceresultset.resultsetview how do I retrieve the record I need to pass to IndexOf()

Mark

BindingSource is also bound to your UI control so when user selects something in the UI, you can find that record in the BindingSource and the resultset as currency management is done for you.

If user enters a "category id" - you want to find it that category id exists - if it does you want to do something with that record else you want to add a new record. So, use IndexOf() method of the listbox in the UI and/or binding source to see if the record is in the dB else add new.

If my answers still don't make sense than please repost your question in detail - maybe I'm not clearly understanding your question. I'm hopeful that the problem is not as hard as it sounds

Manav

Hi Manav,

I agree it should be simpel..

Maybe this will shed some light on the situation.

categoriesResultSet is generated.(Northwind).
categoriesResulstSet set to Detail in the Data Sources then dragged onto a
form.

call Addnew, Then type exisiting CategoryID in Category ID Text Box. Now I
want to retrieve the corresponding record and position the BindingSource on
the corresponding record.

private void Form1_Load(object sender, EventArgs e)
{
categoriesResultSet = new DeviceApplication3.CategoriesResultsetResultSets.CategoriesResultSet();
categoriesResultSet.Bind(this.categoriesResultSetBindingSource);
}

private void btnAddnew_Click(object sender, EventArgs e)
{
categoriesResultSetBindingSource.AddNew();
}

private void category_IDTextBox_LostFocus(object sender, EventArgs e)
{
if (categoriesResultSetBindingSource.SupportsSearching == true)
{
// too bad, this whould have made life easy
MessageBox.Show("Hurray !");
}

// try seek, but doesn't work
if (categoriesResultSet.Seek(System.Data.SqlServerCe.DbSeekOptions.FirstEqual, new object[] { category_IDTextBox.Text }))
{
categoriesResultSet.Read();
categoriesResultSetBindingSource.DataSource = categoriesResultSet.ResultSetView;
}

// this also doesn't work. Presumably pass the record as parameter, but how to get record
int index = categoriesResultSetBindingSource.IndexOf(category_IDTextBox.Text);
if (index > 0)
{
categoriesResultSetBindingSource.Position = index;
}
}

Mark.

I don't have any conclusive answer for you. I tried your application and came to realize that - an item can be searched inside the binding source based on the index or as an object. Since category id does not necessarily maps to the index and/or the object value it is very tricky to find the correct record. You can however scroll through the entire binding source but thats ineffecient. Here is a scenario that you may want to try. Instead of dragging the details view, drag the table as is and this will create a datagrid onto the design surface. Debug the program and click on records randomly - such a record selection within the grid will require the currency manager to select the record and reposition the recordset. This will give you how internally binding source, record selection and currency management works. Please do post the results onto this forum. I'm off for next 3-4 days and hope to check back your findings.

Good luck

Manav

That is not normal usage pattern for RS. You can not search RS (as it contains no data), SQL Mobile does that as you change your query and execute another RS.

It looks like DataSet is more suitable for what you're trying to do. In this case what usually considered DataSet’s disadvantage (loading all records into memory) becomes an advantage and allows you to accomplish what you’re trying to accomplish easily.

Also, since you’re binding to ListBox, RS will retrieve all records anyway so there are no performance benefits compared to DataSet (which would also retrieve all records returned by a query). The only list control which won’t force RS to retrieve all records is DataGrid. Simple controls are also fine.

Ok,

For the record, I'm not using a Listbox control in this form, but anyway, I decided to abandon the Bindingsource approach. Instead I'm using the resultset and update the controls myself.

thanx for all your time.

regards,
Mark

Sure, you can do that. Too bad you won't get any performance benefits (as you would have to retrieve data to populate controls manually anyway) and it would increase amount of work you have to do. Basically you’re trying to recreate DataSet scenario manually with control as storage.

Also keep in mind BindingSource has noting to do with it. It's a proxy class and it can be removed completely if needed - you can bind directly to ResultSet or DataSet. BindingSource offers some extra services and allows you to swap result sets on multiple controls by setting it in a single place, but it does not actually process any data.

I am still using the resultset, I only don't bind my controls via the bindingsource. This way I can use the seek functionality of the resultset on the scenario I described earlier and keep the performance.

That would indeed work as long as you're not populating any list controls on (located on any form) from that RS.