Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Nov 5 11:13:22 CST 2009
Hi John, If your "Process X", "Process Y",... calls to stored procedures do have constant parameters' values then I do not see (I can be missing them) clear reasons to use table-driven approach - I'd just have hardcoded ProcessX, ProcessY, ... calling C# wrapper classes' methods to execute SPs. And the latter wrapper classes can inherit from some generic base class(es) to keep executing context, collect statistics etc... As I noted previously WF (Windows Workflow Foundation) could be a good candidate for your subject area but WF has a steep learning curve so you will not be able to effectively apply it right now but with small dedicated classes, which you're writing right now (and delivering your custom solutions right now) you'll be able to relatively easy migrate to WF based solutions in the future. And if you will spend time working on generic custom table-driven solution you'll probably have to throw it away later when migrating to WF... Before you get comfortable with WF you can code you ProcessX, ProcessY, ... steps as "meta-process" text or xml files keeping fully qualified class names for your dedicated classes. And your "generic execution engine" will be just a simple code reading "meta-process" description, instantiating custom classes using .NET Reflection, calling their generic context/initialization methods, running SP by calling generic Run() method (parameters setting is inside your custom classes), then calling generic termination methods and iterating to the next step/finishing when "meta-process" file description ends. That could be it. Thank you. --Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, November 05, 2009 7:34 PM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] How do you run SQL Server processes Shamil, > Using table-driven process could be not quite right in C#/.Net world, which proposes many other options to implement generic solutions - I'd just start with "hardcoded" solutions using calls to a set of small "generic" That is precisely what I do right now, and what I will do for the near future. The table driven thing is simply that I write "processes" as discussed in this thread. A process might be: Export To Accuzip: "Export dbX TblY to files in DirectoryZ". However that breaks down onto a set of a handful of stored procedures, with passed in parameters. Now, the process "Export To Accuzip" is applied against 6 or 8 different databases every month, but ALSO against random databases which are orders. Orders come in every month and they are a whole nother discussion, but PART of an order is the process Export to Accuzip. DatabaseA exports files to its own directory, DatabaseB to its own directory, OrderAA to its own directory and so forth. But the EXPORT itself is a very standard process. So I am examining the concept of storing the process in a table, so that I can simply select (from a form: Process X: Use SP1, params use SP4, Params use SP9, Params Process Y: Use SP1, Params Use SP2, Params Use SP5, Params Process Z: Use... Table driven (to the extent possible). Then a supervisor application could execute a process (set of SPs), read out of a table, passing in only the database name and possibly a directory on disk. Everything else is selected and set up in advance. To see what a process is, just open a table, to modify the process, select different stored procedures. This stuff is VERY standard, with just a few things that change from DB to db (the database name and where files are going) however I do occasionally shufffle around the interior processes (indexes created, fields used etc. That stuff is internal to the SP and if I change that it is in the SP itself. Then that change is applied to every database from then on. I am not saying that this will work but as my understand and knowledge changes I am spending a LOT of time rewriting stuff. John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > Hi John, > > <<< > I am hoping to eventually make the entire process "table driven" where I can > store (in a table) the > name of the sp, the parameters in a child table etc. > Using table-driven process could be not quite right in C#/.Net world, which > proposes many other options to implement generic solutions - I'd just start > with "hardcoded" solutions using calls to a set of small "generic" > classes/methods executing custom (MS SQL) database processing, when that set > of methods/functions stabilizes I'd try to generalize/"generic-ize" it if > that will be needed at all... > > What is the purpose of your "going generic" for this sets of tasks/customer? > > Isn't it "just for fun" of generic programming? > Would that "going generic" make your programming more effective? > > Developing/debugging/testing generic custom "table-driven"/"other-metadata > media driven" solutions is always (much) more time consuming than > "hardcoded" ones, and supporting "table-driven" generic custom solutions, > especially in the context of constantly changing customer requirements also > promise to be rather time consuming - I mean generic > "table-"/metadata-driven solutions could only(?) pay back well in the case > they are used on many deployment sites by many people. I can be wrong, just > talking from my experience, which is not universal of course... > > I'd better propose to use metadata descriptions/specs (stored in db tables > or (XML) files), if you're so bored to write repetitive code, to generate > "hardcoded" solutions, then make quick fixes to this generated hardcode to > satisfy your customers' current requests, then (in free time) adjust code > generator, then when everything will stabilize (will that ever happen?) > develop truly generic "table-driven" solution. > > You can also consider using F# and Windows Workflow Foundation (WF)... > > The following presentation seems to be useful to get true picture of > "generic vs. specific" solutions trade-offs: > > http://www.infoq.com/presentations/Generic-Specific-Tradeoffs-Stefan-Tilkov > > > Thank you. > > -- > Shamil _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4576 (20091105) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru