Migrating away from VB6, but to where?

[Lenggries]

Hey all, in my spare time at my job I've been brainstorming where to take our flagship application down the road. In a nutshell, this app is:
- a desktop app
- written in VB6
- uses MS Access databases
- minor web connectivity (license and version checking, and FTP uploading).
- input consists of reading files containing private data
- performs financial computations
- produces lots of charts, tables, and reports
- has been under revisions for close to 40 years (aka at this point, much of the code is a patchwork)

For many reasons, including the fact that new finanial regulations may cripple our application, we're looking at different ways to migrate and augment this application to meet demands for the future.

Personally, I was raised as an OO programmer on C-style languages, and had to adapt to older ways to maintain this program. There are a lot of issues I have with it, and am interested in taking it in a much more modern direction. However, I am undecided in which direction to take it, and so would love some advice or helpful suggestions as I do my research.

Some other info about our resources:
- We have a full MSDN subscription, and so are much more interested in MS technologies (aka .NET instead of Java)
- We are interested in moving this to the net as an online app
- We may want to have both on and offline functionality (that is an issue with a lot of unknowns at this point)
- We clearly want to migrate to a different type of persistence
- We presently only have 2 in-house developers
- There is some debate about using VB or C#: the other programmer has only ever used VB, but I at least feel it is easier to find new talent more familiar with C-style languages, which would help us down the road.
- Many of the key algorithms are embarrassingly parallelizable, so we definitely want to try to take advantage of this opportunity for improved efficiency

I originally envisioned something along the line of a MVC setup where two views are created... one of ASP.NET and one of a desktop app. Then I looked at Silverlight once I read about the out-of-browser features. In any case, I have next to no experience with any of these technologies, and would love to hear of any suggestions or recommendations you all might have about where I should focus my research on.

Thanks in advance!

[GaryMazzone]

Personnaly for me

If staying in desktop then ether C# or VB as the front end with an SQL Server backend (enterprise edition).

If working with the web the ASP.Net with either of the above for coding. I would again use SQL Server as the backend.

[Jenner]

I echo Mazz on this one. If your one developer is familiar with VB.NET; and the code is written in VB6, then I'd use VB.NET. It's not hard for a C# programmer to pick up VB.NET; possibly easier than a VB.NET programmer going to C# because the language is more verbose. Microsoft .NET is just that ".NET". VB and C# are simply two ways to look at the same thing. Any .NET programmer worth their salt should be able to switch from one to the other without much difficulty. It's not like the assemblies are different between the two.

You definitely want a better database as well and I'd look at the Microsoft SQL family. If you need something small, non-server based, there's SQL Server Compact. Otherwise, any version of full blown SQL Server would work well. The SQL syntax is identical between the two as well, so there's no tweaks there.

One possibility you may want to look into is using Windows Presentation Foundation (WPF) for all your GUI. It's very fast, very modern looking, natively supported by Vista and Windows 7 and echoing the GUI to Silverlight is a no-brainer. The downside is your programmer needs to be familiar with WPF which uses XAML as it's base. It's a bit of a leap (one I'm personally still struggling with) but from what I understand extremely worth it.

[Shaggy Hiker]

I would also add that I would be looking at 2010 right from the start since it adds lots of parallel items.

EDIT: Oh, and let me add that "Written in VB6" and "under revision for close to 40 years" only go together if you are talking in geologic scales (where 40 years is "close to" the age of VB). I assume that it has migrated from language to language in that time.

[RobDog888]

I would highly recommend using Silverlight 4.0.

In browser or out of browser with the same code, its a setting
Utilizes WPF graphics for high performance
WCF web services
Access or SQL server backends etc
VB or C# code behind


Basically if you wanted a desktop app and an oline web app you can do it with the same code base. You may want to have extra features in one version or the other but the vast majority would be common.

[JuggaloBrotha]

I would say go with .Net and either SQL Compact or the full blown SQL Server as the backend.

I still use Access databases (which work just fine in .Net) but only for desktop apps where I need the portability, any online apps hitting a database, Access is out of the question.

[GaryMazzone]

I would stay a way from the CE version for the reason the OP stated in post 1. He will need the security features found only in the full version of SQL Server (and I think the Enterprise version is really required) for the financial issues.

[Shaggy Hiker]

SQL Server Express is still a possibility, but the fuller version would be better.

[GaryMazzone]

Not if you need all the bells and whistles for security. Some of that stuff is only avail in the Enterprise version

[Lenggries]

Thank you all for your responses.

GaryMazzone: Just trying to read between the lines... are you suggesting that we put the business logic itself into the database? Also, the primary security concerns, other than things like passwords, is that the input sometimes contains personal data that we don't need, so my goal for that is to have the client scrub the data before it reaches our servers. That would excise what is by far the largest potential liability.

Shaggy Hiker: Yes, the program was written entirely in one form of basic or another over it's lifetime. Much of the code was rewritten during the migration to windows 15 years ago, but the algorithms and the very inefficient database design remained unchanged for the most part. And yes, we're going to use VS2010 (unless this gets put off again until VS2525 is released first ;)).

RobDog888: Right now Silverlight is on the top of my list, but I'm still waiting for it to be released before I start testing it out further. I do have a few concerns, however. Do you (or anyone else reading this) know of a book or website on Silverlight 4 that is particularly helpful and well-organized?

Thanks again for all your help

[GaryMazzone]

Yes... basicly I would move all logic from the application to Stored procedures in the database.

[FunkyDexter]

Yes... basicly I would move all logic from the application to Stored procedures in the databaseNNNNnnnnoooooooooooooooooooooo!!!

Sorry, Gary:bigyello:. I should start by saying that Gary is not wrong, just that I disagree with him.

I hate the 'all logic in stored procedures' model and the last two apps I've worked on have used it, much to my annoyance. There are benefits such as increased security, reduced network traffic and better performance due to cached execution plans (actually, I remain skeptical about that last one to this day and have a sneaky suspicion it's largely MS hype). Ease of roll out is also often touted as a benefit but, in my experience, a change in the DB almost always requires a change in the UI so I remain unconvinced of this one. That said SZLamany has championed and aproach where the UI is defined by the database which sounds very interesting so, if he's still around (don't think I've seen a post from him in ages), he might be able to expand on that. For me, though, the downsides far outweigh the upsides. Here's a few (and I believe they're the real killers):-
1. Want to port your app to MySQL or Oracle later? Tough. You can't. Your locked in. (This is where my aforementioned sneaky suspicion comes from). You might not believe that you'll ever want to port it but customers can sometimes make demands like this and even internal apps are sometimes saleable. Don't cut yourself off from future opportunities.
2. Stored Procedures are inherantly procedural (the clue's in the name) so you aint gonna be doing any object orientation and the business logic is probably the single area that would benefit from it most.
3. Stored Procedures don't readily decompose. You can decompose into smaller Sprocs and UDFs but you won't because it really doesn't lend itself to it and decomposition tends to ruin the performance (not neccessarily but it starts getting tricky). Instead you're going to end up writing monolithic Sprocs which are a nightmare to maintain.
4. Due to 3, unit testing really isn't an option because your units are too large. That's a huge loss in how robust you can make your development.
5. No unit testing really means no Test Driven Development so no move towards AGILE and similar modern techniques. Actually, I think this last one might be a lie and I'm trying to push our current development in this direction at the moment but it's proving problematic to say the least. Some of the new technologies in VS2010 are helping so I'm hopeful but a long way from having a complete aproach so far. I'm really still prototype an aproach at the moment but I'm having to make huge compromises with the principles of AGILE and there's a good chance I might just end up with a mish-mash of aproaches that doesn't help at all. Assuming I can resolve the issues I've got, believe me, I will be extremely happy to discover I'm wrong. Please, God, let me be wrong.

Personally, I've always want to implement a business logic layer as some kind of virtualised application running on the DB server and serving requests from a fairly thin client (I think I might allow basic validation in the client although this does increase the coupling to the BLL to some extent) sat on client PCs. That would give the reduced network traffic benefits of the SPROC aproach and alot of the security benefits as SQL Server itself would not need to be exposed to the network - just the virtualised BLL. I have to be honest, though, and say that I haven't had the opportunity to try this aproach out yet so there could be untold pitfalls I haven't considered.

[Jenner]

I've always hated any form of stored procedures for Dexter's reason #1. Until some form of "Standard SQL v2.0" comes out that standardizes the creation and implementation of stored procedures across all major database platforms, I doubt I'll ever use them for any reason.

I'm not up on the fancy lingo to have a clue what the other points are, but they sound pretty important to me too. :D

Personally, I'm skeptical about any utterance of "increased security" in the software world. Windows 7 for example is supposed to have "increased security" but there hasn't been any decrease in the number of new pieces of malware coming out each month. Whether I am sending "INSERT INTO Credits (Account, Amount) VALUES (@Acc, @Amt);" or "CREDITADD(@Acc, @Amt);" from a client to a database doesn't seem in my mind to offer any form of enhanced security.

At best it obfuscates the interface to the database and if one thing the history of security has taught us, obfuscation is the poorest form of security.

Now, the idea of a business logic layer running on the DB server itself does sound intriguing.

[GaryMazzone]

For inserts the claim of an increase in speed is definatly IMHO a lie. But for complicated selects I think that it is true. I would rather have a plan available for me then to have SQL server (or any orher DB Server for that point) have to calculate one every time I want to run it.

What about views for complicated selects? Do you think that there would be no benifit there? Or using Indexed views (SQL Server term) and Materialized Veiws (Oracle term for the same basic thing) not help in any way?

[RobDog888]

Thank you all for your responses.

GaryMazzone: Just trying to read between the lines... are you suggesting that we put the business logic itself into the database? Also, the primary security concerns, other than things like passwords, is that the input sometimes contains personal data that we don't need, so my goal for that is to have the client scrub the data before it reaches our servers. That would excise what is by far the largest potential liability.

Shaggy Hiker: Yes, the program was written entirely in one form of basic or another over it's lifetime. Much of the code was rewritten during the migration to windows 15 years ago, but the algorithms and the very inefficient database design remained unchanged for the most part. And yes, we're going to use VS2010 (unless this gets put off again until VS2525 is released first ;)).

RobDog888: Right now Silverlight is on the top of my list, but I'm still waiting for it to be released before I start testing it out further. I do have a few concerns, however. Do you (or anyone else reading this) know of a book or website on Silverlight 4 that is particularly helpful and well-organized?

Thanks again for all your help
You can start looking at SL 3 if you want a released version but to be honest, SL 4 is releasing in a matter of a couple weeks at most.

[FunkyDexter]

Indexed views are a thing of beauty and my comments should in no way be contrued as aplying to them:cool: I guess they do lock you into the DB to some extent but they'd be far easier to port to a different platform. To be honest, you're always locked in to some extent because even the basic sql you might issue from a client has subtle differences (Date handling, Case statements etc.) so complete separation is probably a myth. Embedding the entire BLL is a step change in the level of coupling though.

On complicated selects, the recieved wisdom is definitely that sprocs improve the performance due to cached execution plans etc. But I've also read a very few articles (including one in books on line) suggesting that this is, in fact, not the case at all (I'll see if I can dig out a link and post it back in a sec). I was left not knowing which to believe though I think I probably still come down on the side of Sprocs giving you a small benefit. I don't believe creation of the execution plan represents that big a portion of the total time to retrieve data though; disk hits and network traffic make up the lion's share. I'd be curious to do some formal performance testing but I've never got round to it.

edit> I couldn't find the original books on line article but here's a link to a blog that discusses it (http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server/). Scroll down to the 'The Performance Argument' Section. The whole article's worth a read and puts lots of pro and counter arguments although it's a bit old now (only applies up SQLServer 2000) so I don't know how much of it is still applicable.

[GaryMazzone]

I think you are locking in (on a db tech) once you start coding. If you use the SQL Server Client objects they give different Properties then some of the stuff in Oracle client (I don't think the DataReader in Oracle has the HasRows() property). So unless we go with the ODBC data client I think we start locking to a database tech right away.

Ok wrong info here :) or at least old info. A search for the Oracle DataReader on their web site says it now supports the HasRows() property

[FunkyDexter]

I think you are locking in (on a db tech) once you start coding.Yeah, I think you're probably right. The trick is to minimise the 'lock in' as much as possible. I suspect it's impossible to eliminate it entirely. Thinking about it, it's definitely impossible - at the very least you'd have a connection string to change.

It's just occured to me that this is probably the first time in my life I've argued that a lock in is a bad thing.:bigyello: