DataGridView & DAL

[markross]

Can some plaese give me a quick run down on how to set up a DataGridView using Data Access Layers, - I'v done asp.net using dal but not winforms.
I'm using a mdf database, and may use linq

Thanks
Mark

 

[cjard]

read the DW2 link in my sig. If you may use link, see the .NET 3 customised versions of the pages

 

[markross]

Sorry about the Delay

I read a little in the Walkthrough's, - thanks cjard.
I am just going to use Datasets via Table Adaptors

I notice that the SQL default values are not being picked up by the dataset - I have to manually make a change to each row with a default value in each DataTable in the DataSet.

I also had to change the DataGridView's EditMode to 'EditOnEnter' to get a good mouse click behaviour

I also added:
TableAdapter.Update(Dataset)
Dataset.AcceptChanges()
to the RowEnter of the DataGridView
(above is abbreviated)

Also, -1 (<0) are OK for the primary key, it means Autonumber passthrough to the sql database.

Note: I wanted the data to update to sql sooner than later for other uses to see it.

I will go with this model for now, It might do for the complete app. and if not, I may have a better idea of what I want down the track.


Thanks
Mark

 

[cjard]

I notice that the SQL default values are not being picked up by the dataset - I have to manually make a change to each row with a default value in each DataTable in the DataSet.

Indeed, because the DataTable schema is inferred from the SELECT query, not from any table definition (the select query can contain the results of functions or operations on fields so getting the tabledef wont help), and the only thing it really looks at is the primary key, whether you have selected all the columns of it and hence how to write the Ins Upd and Del queries for DML

It would be a neat addition to include defualt values but:
a) youd have to get MS to upgrade the dataset generator
b) if you want to use default values you would normally jsut pass NULL to the database.. so leaving the datset's default value of NULL would cause a NULL to be inserted, a default vlaue to be used, and the DAL automatically requiereis the database after the insert to check for values calculated by the db.. sooo, though the user wouldnt se ethe defult value before they saved, they would see it after.

REmember, datasets are not intended to replace databases and will not replicate all their fnctionality. You can, however, write some code if you want that will pyull a tabledef and turn it into XML that the dataset generator works off.. I do this often, with an app I wrote to turn fixed width data into XML compatible with a dataset. It also generates the full XML definition of the dataset, so all i do is describe my FWidth file, my app makes the dataset and the regular expresisons needed to transform the data and all i do is make a new dataset, then open it with XML Editor rather than DataSet Designer, paste the XML def in and voila.. the dataset suddenly becomes fully populated with all the tables that will be parsed out of the FW file.
The upshot is you can write a prog that parses the xml of the dataset, looks up the default and writes new XML back..

If you think it will be quicker than setting it manually or leaving it null, that is..

I also had to change the DataGrid's EditMode to 'EditOnEnter' to get a good mouse click behaviour

I dont quite know what a BAD mouse behaviour would be but I do have a hack I apply to some of my DataGridViews where a context menu exists.. On CellMouseDown, if the cell is within the currently selected range, do nothing, otherwise, set the current cell = to the cell that was mousedowned.. THis ensures a selcetion can be made, pointed to, right clicked etc.. but if the user just points to a cell they want to interact with, they dont have to left click it first before right clicking to operate on it.. Much more intuitive relative to windows de facto HCI (select 10 files, then right click a different one, 10 selections disappear and newly ricghtclicked file is selected and operated on)

I also added:
TableAdapter.Update(Dataset)
Dataset.AcceptChanges()
to the RowEnter of the DataGrid
(above is abbreviated)

Yeah, um.. I really wouldnt do that. You dont want to fire off an update query every time the user mouses around the grid. Why did you do that?

Also, -1 (<0) are OK for the primary key, it means Autonumber passthrough to the sql database.

Yes but see earlier point about requerying db.. the idea for local relationships is that you will use some field to maintain the relation but it will be updated on save. The relationship is preserved.

Note: I wanted the data to update to sql sooner than later for other uses to see it.

Well, ok.. but i find the optimistic concurrency model quite effective.. I operate in a financial processing company with a lot of customer service activity and its rare that their opreations overlap to the point where we need data to be updated to the earliest opportunity

I will go with this model for now, It might do for the complete app. and if not, I may have a better idea of what I want down the track.

No worries, Its what I did too.. staritng out, it's a bit awkward but it soon makes sense and is very well thought out for the most part

 

[markross]

Thanks for the info cjard
I have read it once, but will reread it a few time it get the understaning out of it.

I find that sending a <null> as a default value generates and error and setting the default in the dataset fixes it as well as immediately displays the default in the grid.

I have also change to using Row.Validated instead of RowEnter, works a lot better.
This form I’m working on (there may be a lot of work done with this form, the user may sit on this page all day, and I also want change to be seen on other machine quickly), I want a Row by Row update, but nearly all other forms will have a page update (which would be the default for VB)

I am fighting with passing SELECT parameters - this might be to do with a bug in the designer code of my form, it has now Brocken for the 15th time, and this time is Brocken good. I will do a new form.

One other question, Should the unwated pri-key column in the DataGrid be deleted or hilden (visiable=false)?

Note: I am using sql2005 express

 

[cjard]

I am fighting with passing SELECT parameters - this might be to do with a bug in the designer code of my form, it has now Brocken for the 15th time, and this time is Brocken good. I will do a new form.

One other question, Should the unwated pri-key column in the DataGrid be deleted or hilden (visiable=false)?

Note: I am using sql2005 express

Whats the problem with the parameterised query?

Unwanted columns in a DGV can be deleted if you like, so long as they are autogenerated for new rows. They arent needed for the link between DGV and DataTable, only DT <-> SqlsServer

 

[markross]

I added a new query to the DataTable which has a FillBy & GetData methods adding a WHERE parm=@parm in my sql,
I changed the TableAdapter.Fill(DS) to FillBy(DS, parm),

I'm not getting ..... I just run the program again to get more info for this post and it works fine.

Thanks Again cjard

 

[cjard]

If you can remember anything about the error, even just a notion or a general idea of its content, i might have seen it before

 

[markross]

There was no error message
when I used Fill, I got all the records, when I used FillBy, there was nothing.
I checked my parm with a msgbox and my parm was good.

I have just retested the app and this problem is still not recurring.
I do get an foreign-key error when I click save after editing the second data grid, look like I will have to update each one individually to get the update order correct.

I also notice the my dataset layout get screwed up a bit sometime, I lose parts of datatable, some tables are on top of others and some relationship head off in funny directions. does seam to break anything, just annoying.

I have remove the gv.rowvalidated used to update sql as it caused the datagride to go a bit spackie.

 

[cjard]

typically you update parent, then child or if you use a db like oracle, if it is all within one transaction then foreign keys are only checked at the end of the transaction so update whenever you like and the tran will only fail if orphans are present. also see the sticky in this forum by me "calling endedit causes relations to fail"

this doesnt bite datagrid often because it does a lot of endedit (i think) but it does bite other components

 

[markross]

Found that Bug, it happens when I don't click on save
I put TableAdapterManager.UpdateAll(DS) before the fill's and is fine.

see, GV-1 is a Master; GV-2 is Detail; GV-3 is conditional from the select row of GV-1 & GV-2 (GV-1 in inherited via binding, GV-2 via parm and if GV-2 is empty, return all values (Fill, not FillBy)).
the data model for this part of the database is a bit weird, as ussally, I am staring with the hardest part, but also the most important part, of the DataBase.

 

[cjard]

Are you still encountering a problem?

 

[markross]

I got it working OK but still having problem with a few things.

I am going to work on a few other forms and then come back to it. I need some play time.

I installed VS08 SP1beta and it fixed up the DataSet Designer (I had to debate with my self on installing a beta service pack, but I won in the end).