DataGridView -> M$ SQL : I'm struggling to make it work.

[GriffMG]

Hi

I am right at the beginning of a conversion of an application from Clipper to C# against M$ SQL.

At first glance the DataGridView looked like a good way to help the users enter data in a spreadsheet like fashion - but it is driving me mad.

I have started with a one form, one table example - the table has just three columns; ID, Description & Manager - both ID and description have to be unique and manager must be completed. I thought this would be easy peasy.

If I use the dgv in 'spreadsheet' mode (using the NewRow) I don't get the underlying ID from the database, and I can't even seem to validate the description against the SQL back end.

Is it better to simply avoid using the whole NewRow thing and program forms that let the user complete record details in the traditional fashion - form by form, validate before save?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[jmeckley]

First, you won't get the ID from the database until you save the record to the database and reload the grid.
Second, the database should not be the first line of defense for data validation. validation is a function of the system. the database is a mechanism to persist data.

I don't have experience using the "spread sheet" mode so I can't speak to whether it's a solid feature or not. the winforms/wpf/webforms teams have been known to make some less-than-desirable design choices at times.

something else to consider. How would you make this work, if you didn't have a database. after all a database is just one way to get data. try getting the behavior you want using a in-memory collection. Then swap out the in memory collection for a database backed storage. (Or get wild and use an non-RDBMS persistent store )

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[GriffMG]

Thank you Jason,

Firstly, I'm stuck with C# and M$SQL for my back end, it wasn't my choice but there you go.

I *think* I'm trying to do what you suggest, I'm trying to get the code to validate user input against the back-end database as they enter it - I also want to save it immediately (as they come off the record is fine). I actually want to be 99.99% sure that an entry is valid before a safe is committed, as I have been used to doing with Clipper and VFP in the past - I don't mind if the database throws an occasional exception that I can handle gracefully.

My problem is that a) the DataGridView seems to be a bit unpredictable as to when it sends the update to the server and b) even when it does - the server has an identity for the record, but the dgv does not, so I can't easily validate new records against the ones on the server.

If I used an inmemory collection I am surely not going to be able to 'multiuser' the app very well am I?

Reloading the grid after each save is going to hit performance a bit too isn't it? Probably fine with the little 'library table' I'm starting with (maybe 10 or 20 records), but reloading thousands of records for some tables would be plain silly.

By 'spreadsheet mode' I only mean using a grid that users can type in the cells and use the 'NewRow' feature to rapidly add records - without using buttons, forms or key sequences.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[jmeckley]

Firstly, I'm stuck with C# and M$SQL for my back end, it wasn't my choice but there you go.
If I used an inmemory collection I am surely not going to be able to 'multiuser' the app very well am I?

the point is to get the behavior you want before you bring a database into the the mix. The UI should work regardless of where the data comes from (and is going). therefore if you can get it to work with an in-memory collection/textfile/json/document db, you could then swap out that implementation for one that talks to a database.

I don't mind if the database throws an occasional exception that I can handle gracefully

very true, but invalid data isn't exceptional, it's human nature. input should be validated before a db connection is even open. once validated, the input can be run through a series of verification checks to ensure the input is valid at that moment in time.
validated: required fields, date ranges, string lengths etc.
verified: customer name is unique, item is stock, the account has enough funds to make the transfer, etc.
Exceptions are the things you don't expect to happen, but could. the database is down, loss of network connectivity, dead locks, etc. these should be handled gracefully.

Reloading the grid after each save is going to hit performance a bit too isn't it?

not necessarily. if this is a multi-user system then the grid will need to be refreshed because another use may have modified the records in the table.

Probably fine with the little 'library table' I'm starting with (maybe 10 or 20 records), but reloading thousands of records for some tables would be plain silly.

your right loading 100's of records at anytime is silly. this is why databases have some form of paging mechanism. this way, not matter how many records are in the database the result set will only be, at most, 10-20-30 records. whatever page limit you set.

My problem is that a) the DataGridView seems to be a bit unpredictable as to when it sends the update to the server and b) even when it does - the server has an identity for the record, but the dgv does not, so I can't easily validate new records against the ones on the server.

don't blindly trust the UI control to communicate with the database. It shouldn't know anything about the database to begin with, but i digress. You want to control when the data is ready to be persisted. after persisting the changes, refresh the grid and the new record + PK id will be present.
PK (and FK) is a RDBMS concept, not an OOP concept. While PKs are required, then are not the only way to identify a object. you can override Equals and GetHasCode to control how objects are equal.

I haven't worked with VFP, but I know others who have. They also had difficulty transitioning to .Net. Letting go of what they knew in VFP and learning the paradigm of .Net. You may want to search the web and see if other VFP devs are talking about the idiosyncrasies of moving to .Net.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[GriffMG]

Thanks Jason,

I agree with most of your points, they echo what I've been trying to do - and you are right about it being a paradigm shift. I've got the hang of the OOPs bit, but I am struggling with not having all the current data at my finger tips... having to work with smaller sub-sets.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[clipper01]

griff , hi , just took a quick look at this site ; per some prior dialog on vfp section , am in same boat i.e. transfer to dotNet / Sql server etc . U very quickly find that what u could with VFP with extreme ease since about 1995 ( vfp 3.0 ) takes about 5 times as long in dotNet. My take would be this ;
1) sql server back-end , no-brainer
2) decide on winforms or wpf . M$ makes big deal about data-binding ease in wpf , but is right pain , u need to learn xaml , Blend as the designer surface ( even vs2010 , cider , is very poor ), debug is a nightmare ; ( IMHO ) I would stick with winforms ( as for SilverLight etc , don't make me laugh )
3) then u need to decide data architecture to fill dgv's handle data etc
a) use ADO.NET datasets
b) use entity forms ; main advantage that M$ touts , is it allows u to use LINQ , which almost gets u to the level of easy-code that we have in vfp since 1995 ( thank u M$ )
c) plain old SQL pass through

my guess would be (a) is best , tried/tested , zillions of google responses to any query etc

but at end , u have to totally re-think the way data to screen works , u no longer have the idea of "current record " , that is the huge paradigm shift I find.

I have not looked at any of the main commercial libraries like DevExpress etc , I'd be very intersted to know if any dotNet dev's tend to use these .

interested to follow progress on your conversion(s)

sean m

 

[jmeckley]

yes, controls offered by devexpress, componentone, telerik and the rest are very popular. However, these are all UI controls. they have nothing to do with how you get the data or where you send the data. just how to display data.

don't think in terms of a database, think of the data as a collection of objects. where the collection comes from or goes, just happens to be an RDBMS, but it doesn't have to be.

something else to consider. ado.net is, by default, disconnected. db connections/transactions should only be open for a short period of time. The idea of "current record" has no meaning in ado.net.

winforms vs. wpf:
I would learn wpf and xaml data binding. there is more power and flexibility with wpf. along with wpf you have the concept of Model View ViewModel (MVVM) WPF is a pain without it.
while you *can* use silverlight for web and desktop development I wouldn't. it's sole market right now is Windows mobile devices. it's not dead or dying, just a very restrictive market.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[clipper01]

thanks for update Jason , what is yr take on ado.net datasets vs entity framework , for regular "desk-top" apps ; u can do quite a bit of traversing/handling data with ado.net / or use LINQ more heavily ? I understand EF4 can handle POCO objects now which might be relevent to what u advise on using 'object' data ?.

coming from vfp world , main switch is lack of current record and the AWESOME grid that vfp has

 

[jmeckley]

I am a proponent of ORM. the more popular frameworks implementing the concepts of ORM are NHibernate, Castle.ActiveRecord, Enity Framework 4 and LLBL Gen Pro 3. I will pick Nhibernate every time if given the choice. it's the most mature and feature rich framework of them all. EF 4 is too little too late IMO. Base features all the ORMs are on par with each other. NHibernate excels when you then need to optimize the data access. There is also a rich community of extensions for NH which makes it much easier to extend.

as for linq. It's a useful tool, but it's not a cure-all like some people believe it to be. There are some common pitfalls with linq because of misconceptions or misunderstandings about what it should do.

a common scenario is using linq to query a database but filtering results in memory rather than on the database. in fact this is probably the single largest issue with linq. Like any other tool. you need to know how it works before you use it. and linq is so much more than just querying databases, but for some reason many people think this is the only reason for linq.

DataTable/Sets are good for reporting because more report writers handle tabular input.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[clipper01]

it's a big dilemna coming from the super-easy data handling of vfp. NH does seem to be the ORM of choice for a lot , but the learning curve seems very steep , overkill in many cases ?

Take yr point on EF , seldom gets good mentions. Going back to original question the user posted , to build a pretty bog-std desk-top app , maybe the best approach then is WPF + AD0.NET at least it minimises the learning curve/maximises the future dev routes ?

 

[jmeckley]

whether is winforms or wpf, orm or ado.net the core concepts are the same. WPF would be more relevant looking at the future of Windows Desktop development. It's good to understand ado.net as it's the basis for all data access frameworks. Ultimately though, I would recommend some form of higher abstraction over data access.

If your application is a simlpe "forms of data" UI then you may also want to consider MS LightSwitch.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[GriffMG]

I am stuck with C# and M$SQL, no chance of using anything else.

I only have 101,543 lines of code to rewrite...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[clipper01]

griff,all of the options that Jason mentioned are C# and M$SQL , huge issue for vfp-ers ( count me in ) is the horrendous loops u have to go through to do the things that were 1 / 2 lines of code in VFP. U are currently using winforms , the crucial choice that many dev coders have to make is to use WinForms or WPF ( Windows Presentation Foundation ) , both are forms-based GUI dev systems , but the way of data-binding is fundamentally different in WPF ; it is very like VFP , all happens automatically , once u figure out how to do it . so after the first project , the pay-back is huge

and like VFP , M$ has decided to stop new versions of WinForms ( but it will still be around for 10 years + , and it is very mature , loads of google help etc ) in favour of WPF

if you look at most of the VFP-dotNet conversion classes , u will see they are all going to WPF + some higher data abstract layer.

Prob is that all of that adds maybe 3 months learning curve ( IMHO), where u get going in WinForms in a few days .

so to convert your clipper app , it might makes sense to go as u are with winforms , may not be as 'powerful' as wpf , but will work for sure

plus , you can also host WPF forms in WinForms and vice versa , so this gives the option to dabble a bit in WPF as u go along

be very interested how the migration goes , have done a fair bit of dotNet dabbling , but short of a full conversion project

 

[GriffMG]

Hi

I did know that WPF was a flavour of .Net - but the client specified WinForms. They seem easy enough to live with.

I agree about the amount of coding - compared to clipper/vfp.

On the other hand, barring my niggle with the dgv, so many of the things I have developed as toolbox function in VFP are built-in to C# - I am sure I'll be productive in it soon... better be, only have eight months to finish the job (well seven now).

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.