Conversion from VFP -> C# (with SQL back-end)

[GriffMG]

I don't know if anyone is interested, but I thought I'd give you an update.

After watching most of Bob Tabor's course (or perhaps that should be his 'sales spiel') on C# I dived right in.

Initial progress was actually blinding. I reached my first milestone of being able to create a self-updating application that could find new versions of itself on the web or locally on a LAN, could search for SQL servers, locate appropriate databases and even create the first inklings of a new database a little ahead of schedule.

Then I ground to a complete halt.

A simple three column table and a DataGridView. C# equivalent of the VFP grid.

The client is very, very keen to have a spreadsheet-like interface wherever possible - C# has a nice tool, and it has a 'NewRow' feature at the bottom, just like Access does... but it behaves very strangely to a VFP programmer. It took me a long, long time to understand that you have to think 'disconnected' from the database. It's like the whole model is based on allowing errors and then letting the database reject things when you commit them, rather than validating them and then being 99.99% sure that your data will be successfully written.

That is a gross over simplification, but it is how I was working.

Last night I had a little ephithemy though, and found a way to get the DataGridView to work like VFPs - writing back to the SQL db on (what seems like) a record by record basis. The only snag is that you need two unique fields to do it, the regular ID primary key (which the DataGridView doesn't want to give you when you add new records) and another unique element to do validation against.

I've not tested it against significant datasets yet - it may prove woefully slow - but for my first table it is working ok - I think...



Regards

Griff
Keep [Smile]ing

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

 

[craigber]

You can't think in VFP terms when using .Net. Just like you can think in VFP terms when using Java, PHP, Ruby, Python, <insert your language here>.

You need to think in .Net terms. And, keep in mind this statement from Kevin McNeish, "Everything that's easy in VFP is hard in .Net. Everything that's hard in VFP is easy in .Net".

I'm haven't done VFP in years and I'm only doing C# now and I love it. Once you stop trying to do things the VFP way it's fantastic to work with.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[clipper01]

Griff , I agree with you , C# will drive u nuts to start with , but like Criag said , you have to NOT think vfp , DONT FIGHT C#

I have not yet had to re-build a vfp app to dotNet , so at this stage am just building a library of "techniques" for future use. A really good example that you mentioned in yr early post was the prob of an auto-inc value in memory having to handle if another user auto-incs the db in the meantime. Took a lot of searching but all you need to do is aadd a single key statement, to create a batch sql query , see the full snippet below , but it took a LOT of searching

cmd1.CommandText += "; SELECT SCOPE_IDENTITY() AS DepartmentID;";

the other thing I would strongly reccommenc is that you always use BindingSource rather than Dtatable as the data-source for grids etc.

To do that you also might need to use Entity Forms . That way you have a have a data-binding that is almost as easy as vfp.

Would not yet claimto be an expert in C# but I honestly think if you are not using Entity Form , Binding Source etc , you might as well tell your client to go back to VFP or walk away from project

Craig u are the expert , is that too extreme an advise ???


But having said all that , I am now also at the point where I love to work in C# , eventhough VFP will always be much more productive for any kind of data-bound apps



*** sample c# snippet to hansdle post-back of ato-inc values
** note the critical --- SELECT SCOPE_IDENTITY() AS DepartmentID;";

** and tthe critical --- builder.RefreshSchema();



string qry = @" SELECT DepartmentID,name,groupname,modifieddate from
humanResources.department";

mAdaptDept = new SqlDataAdapter(qry, gConnSqlDb);
mTableDept = new DataTable();
mAdaptDept.MissingSchemaAction = MissingSchemaAction.AddWithKey;

SqlCommandBuilder builder = new SqlCommandBuilder(mAdaptDept);
SqlCommand cmd1 = builder.GetInsertCommand();
cmd1.CommandText += "; SELECT SCOPE_IDENTITY() AS DepartmentID;";
cmd1.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
builder.RefreshSchema(); // refresh the T-SQL generated commands
mAdaptDept.InsertCommand = cmd1;

mAdaptDept.Fill(mTableDept);
mBindDept.DataSource = mTableDept;
grdDept.DataSource = mBindDept;

 

[craigber]

I always use Entity Framework against SQL Server if I can because it's so easy to use.

You should never get a primary key before saving the data. The key belongs to the database and until you've saved data, there is no key. This is not a .Net thing, it's a software development thing.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[clipper01]

yea , the primary key is sent back from the DB using these 2 lines

cmd1.CommandText += "; SELECT SCOPE_IDENTITY() AS DepartmentID;";
cmd1.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

then u can use that ID to link child record , very typical case e.g order header/details

 

[GriffMG]

Craigber and Clipper01,

Thank you for your ideas and support, maybe I'm just too set in my ways to enjoy c#->sql!

I actually understand what clipper01 is suggesting btw, and I think that if I hadn't developed my own workaround I would adopt that routine... but I have the grid updating the underlying record as and when I demand it... it's not quite as slick as I would like it to be, but it's workable...

Regards

Griff
Keep [Smile]ing

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

 

[clipper01]

glad to help , and again ref yr observation on difficulty with working with datagridview , would highly reccomend to ALWAYS use BindingSource as the datasource of a grid , NOT a datatable , see snippets below , it avoids all of the probs , try it !!

say you have a grid grdDept , and have built a table mTableDept to populate it

1) grdDept.DataSource=mTableDept , means you then are tied to using all the datagridview events etc

2) BindingSource mBindDept= new BindingSource();
mBindDept.DataSource = mTableDept;
grdDept.DataSource = mBindDept;

means you can use the BindingSource mBindDept methods,events for everything , very similar to vfp , you NEVER have to use any of the DGV methods/events

http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.aspx

p.s. your project is the first I have seen of a vfp-dotNet migration, all our experts are still running scared from this