best way to develop complex data driven applications?

[brigadier90]

Hello all,

i've been learning visual basic for a while now however still find a few things confusing which i hoped someone can clear up for me.

I am developing a complex data driven software in vb using mysql as a datasource. now i have found a software called mysql Connector wich allows me to connect to vb and drag and drop tables onto a form to create controls which is a great tool. I have also read in a book that such a method is only useful for creating simple applications and prototyping more complex ones. Does that mean that creating data driven apps through code using ado.net classes (mysqlConnection, MySQLCommand etc) is better? also is it the ony way i can make it dynamic?

[Max Peck]

Personally I've always found using bound controls to be pretty restrictive. They can be handy if you have a tabular form that simply scrolls through an existing data set and allows you to add or delete records, however if you want to do anything more complex than that you'll find that there's no substitute to writing your SQL code yourself and coding your form to behave the way you need it to.

The technology does provide some shortcuts but when it comes to what you're calling "complex data driven software" you just aren't going to find too many "cook book" methods that will enable you to produce a really intelligent piece of software. Templating web pages and the like is one thing but templating applications is an entirely different animal.

I'm developing a product right now that could be considered a complex data-driven application. I have a handful of forms that are simple enough that I could use a bound control (grid) on them - but that's it. The remaining forms dealing with the database have to make decisions on whether the input data is valid, whether or not the record being edited already exists and several other things that a bound control just would not be able to do.

Once again it's all about using the right tool to do the job. "Complicated data driven" applications just aren't simple to develop if there is *any* business intelligence needed in the application. Don't let any advertising hype convince you otherwise. Even if such tools do exist that claim to let you "drag and drop" to make your application the part they're sometimes not going to tell you (at least until after you cough up the dough to buy their tool) is that as a developer you're going to have to "wire up" the configuration in some way.

What I suggest is that you develop your own library of data access primitives that access your database in the way you want to do it. For example, I have a set of about a half-dozen primitives that cover just about all aspects of data-access that I do to a connected DB. That way throughout my application I do everything the same way. If I discover a bug underlying the data-access layer or I want to enhance the thing then all sections of the application benefit from the added intelligence. Once I've learned how the various controls I use work, then "binding" them to the data doesn't really save me that much time any more.

Hope I didn't go into too much here ... just my 2-cents.

-Max

[brigadier90]

Thanks for your great input. One more question please: If I proceeed using the ado.net classes what would the best way be to create a live system (dynamic) i.e. when one window is updated another promptly reflects that update (e.g. a profit and loss summary window that gets updated through other windows). Would calling a fucntion every x seconds to retreive and refresh the screen be better or is there any other way it could be done automatically on update or keyup?

Thanks Again.

[takkone]

I have a set of about a half-dozen primitives that cover just about all aspects of data-access that I do to a connected DB.

Mr. Peck, I could not agree more with your advice. I too bind nearly all of my controls at runtime. The occational lookup list or dropdown box being the exception. I dont want to hijack this thread but id like to learn more about the approach i quoted above. I too reuse some basic functions like set/get connection string or see if table exists, etc... But what are other examples that are part your "primatives"?

Another question: I tend to put all my SQL code inline within my VB code. Well not exactly, I mean I do create a module within my solution that has subs and functions, some scope friend some scope private, for all my database selects, inserts, and updates. I use sqlclient class and commandtype as commandtext. I see a lot of other programmers use stored procedures instead. Do you see any negative aspect to my approach? I am a development team of one and prefer to keep everything i side my vb solution rather than calling a stored proc that lives outside my app. Occationally I will use a view by creating it on the SQL server first if it has a lot of complex joins or case statement and no parameters are required.

If i overstepped my bounds here just let me know and i will start a new thread with this question.

[techgnome]

" I use sqlclient class and commandtype as commandtext. I see a lot of other programmers use stored procedures instead. Do you see any negative aspect to my approach? " -- one word: Maintenance... if you have to make a change to a query, your approach results in the app being re-compiled and deployed. with a stored proc, all that needs to be rolled out is a script to update the sproc.

-tg

[brigadier90]

Thanks for your great input. One more question please: If I proceeed using the ado.net classes what would the best way be to create a live system (dynamic) i.e. when one window is updated another promptly reflects that update (e.g. a profit and loss summary window that gets updated through other windows). Would calling a fucntion every x seconds to retreive and refresh the screen be better or is there any other way it could be done automatically on update or keyup?

Thanks Again.


Srry Just Bumping this since no one answered me yet

[techgnome]

it depends on the architecture of the system... there's two ways - using a "pull" method, where the windows poll the database (or what ever storage means you use) to see if there is data available. There is also a "push" method, where windows can "subscribe" to be notified of changes. Then when there is a change, a message is pushed out to any interested subscriber. Even at this point, there's two ways to handle that. The info can be pushed to the subscribing form, or a message is sent to the form, and the form still has to pull the data from the storage. I've got a post somewhere that shows how to send messages between forms... let me see if I can find it.

-tg

[techgnome]

Found it! - http://www.vbforums.com/showpost.php...78&postcount=4 ... it's simple and I don't remember what kind of documentation (if any is in it). I should probably grab a copy of the code and write a tutorial around it.

-tg

[brigadier90]

Thank you very much for taking the time to reply, you've been a great help.

[Max Peck]

Thanks for your great input. One more question please: If I proceeed using the ado.net classes what would the best way be to create a live system (dynamic) i.e. when one window is updated another promptly reflects that update (e.g. a profit and loss summary window that gets updated through other windows). Would calling a fucntion every x seconds to retreive and refresh the screen be better or is there any other way it could be done automatically on update or keyup?

Thanks Again.

If you're going to have two different forms on the screen at the same time and want them both updated, the forms are going to have to be non-modal (obviously) so they can exist as a separate thread. The 2nd form can be updated from the first while onscreen if you just access the appropriate control in the Controls collection on the 2nd form. (That would probably be the most real-time way to do it). The 2nd form could also implement a timer which when fired can access the underlying database if you want to do it that way. Both methods would work.

-Max

[techgnome]

I'd prefer to broadcast a message instead... when you start getting forms accessing controls on other forms, you start to run into problems (I really, really, really wish they would get rid of default form instances once and for all) ... because the forms then become dependent upon each other (it may be one-way, but still, there's a direct coupling which can be - and should be imho - avoided).

This also can then remove any inaccuracies which may (or arguably may not) result from using a timer. It also means you remain in control on when you make updates to the window and aren't needlessly pulling data that hasn't changed.

The data entry form should save the data, then broadcast a message that says "I've been updated!" ... and any form that then listens for that message can then retrieve the information. Or even the data entry form says "I've been updated, and here's the new data!" ... again the subscribing forms can then take the data and do what it needs to do with it to update itself.

Granted, a lot of this is personal preference too... timers are easy (almost too easy sometimes) but can cause weird things to happen if you aren't paying attention. Especially when you're debugging... If you only need to worry about one or two forms, it's simple enough to use a timer... but if you need to add more forms to the mix... keep in mind, that for every timer running, that's a thread that needs to be be running and it can add up in CPU costs over time.

-tg

[Max Peck]

Mr. Peck, I could not agree more with your advice. I too bind nearly all of my controls at runtime. The occational lookup list or dropdown box being the exception. I dont want to hijack this thread but id like to learn more about the approach i quoted above. I too reuse some basic functions like set/get connection string or see if table exists, etc... But what are other examples that are part your "primatives"?

I spent a lot of time developing a very concise set of library functions that allow me to connect to a DB, execute queries against it, etc. I.E. in my code if I want a list of employees sorted by their ID's. (This in abbreviated C#)

using System.Data.SqlClient;
using SSDUtility;  // My library

var DB = new Database();
if (DB.Open("DBName")) {
    var rdr = DB.ExecuteQuery("SELECT * FROM Employees ORDER BY ID");
    while (rdr.Read()) {
         // do something.
    }
    rdr.Close();
    DB.Close();
}

"rdr" coerces to a SQLDataReader object returned by ExecuteQuery()

The library has about a dozen simple primitives like that which deal with the connection string and a bunch of other details which I no longer have to concern myself with once I've set it all up. I'm an old dog at this - I don't like having to do stuff over and over again. I'd rather build black boxes to do the work and then forget how they work while I figure out something else! Since developing my DB library I haven't dealt with a connection string since last year when I wrote the thing. ;-)

Another question: I tend to put all my SQL code inline within my VB code. Well not exactly, I mean I do create a module within my solution that has subs and functions, some scope friend some scope private, for all my database selects, inserts, and updates. I use sqlclient class and commandtype as commandtext. I see a lot of other programmers use stored procedures instead. Do you see any negative aspect to my approach? I am a development team of one and prefer to keep everything i side my vb solution rather than calling a stored proc that lives outside my app. Occationally I will use a view by creating it on the SQL server first if it has a lot of complex joins or case statement and no parameters are required.

To me, stored-procedures and views are something best used sparingly. I think I developed that view because at the company I'm presently working with these young kids have a tendency to try to do things just because it's "cool". Someone in the group (about 6 years ago I guess) got all wound up on how "cool" stored procs, triggers and all that stuff is. Granted, they can be cool, but I sure as hell would rather be debugging a DLL procedure rather than SQL code when it comes to business logic. Some of the stored procs in our system have SQL statements that are 200 lines long! A well written VB (or C#) routine breaking up that SQL into a half-dozen simple queries inside a loop would have been much easier to maintain and arguably just as fast.

There's nothing wrong with doing your SQL inline in your VB code. As far as I'm concerned it's really a matter of personal preference. Where do you want your business logic to live, in the DB itself? That's fine. It's your DB. There's no ONE right way to do anything in this craft.

-Max

[Max Peck]

I'd prefer to broadcast a message instead...

Yeah, that's fine too. Whatever flavor tickles the palette. As I said before, there's no one right way to do this. However one thing I will say ... simpler is better, less is MORE.

-CB

[techgnome]

RE SProcs - yes... in an ideal world, business logic wouldn't exist in the DB at all and stored procs would be simple CRUD statements... unfortunately I don't live in Utopia and I've kind of gotten attached to those paychecks they keep sending me... I'm jsut saying, I hear ya, but at the same time, if I need to update 100's of rows... I'm more likely to want to do that in a set-based transaction in SQL than looping through rows in a datatable... although LINQ and EF could very well change my mind on that... now... to get the base products people to change their minds....

-tg

[brigadier90]

Thanks again for all the input guys,

I just want to ask one more thing, because in my software i have multiple users, now both methods( timers and sending messages ) sound good, however i need a system that not only reflects changed on the desired form of my screen, but all users' screens who have an instance of the form to be updated opened in front of them.

could these methods do that? or one can and the other cant? i just thought id make sure before trying anything.
Also another question, if i use the timer method, would it affect the system if a window is performing a task every second? make it slower in anyway? make it crash?

Thank you in advance