I have a repetitious regenerate process that I would like to automate.

I do the following things thru the GUI
File/New Project

     fill out the  create new project dialog (with the same entries every time) 
    uncheck Custom Properties checkbox
     click Create button 

Then on the stored procedure selector I click the checkbox to select all the procs click Select Rows Using a Filter - looking for 'dt_' click Toggle Checkboxes of selected rows click Select Rows Using a Filter - looking for 'list' I change the number of resultsets to 2 or more and press set click ok ctl-shift-T Check checkbox to select all the entities mapped on tables Click add to project

ctl-shift-V Check checkbox to select all the entities mapped on views Click add to project

add all retrieval and action stored procs

click project/generate select 'adapter scenario (full/safe) etc....' select the template set remove the last '.' from the Root namespace START GENERATOR BUTTON SAVE PROJECT EXIT

Is there a way to automate this through the command line generator ?

Thanks Dave

You can refresh from the command line, and generate from the command line. I don't see why you would need to recreate the project every time. Could you elaborate on that abit?

It's just a local standard to always start clean so there are no side effects.

But if I do a refresh from command line, will a new stored proc that has 'list' in the name be set to '2 or more' resultsets? If it will, then that would be ok.

IowaDave wrote:

It's just a local standard to always start clean so there are no side effects.

But if I do a refresh from command line, will a new stored proc that has 'list' in the name be set to '2 or more' resultsets? If it will, then that would be ok.

Not automatically, if proc-resultset retrieval is failing on that proc.

You can also grab the sourcecode and after the refresh takes place, check the object model in the project object (traversing the objects in the project) and check if everything is as you want it to be.

Not automatically, if proc-resultset retrieval is failing on that proc.]

We standardized on "all retrieval procs will return a dataset" and "retrieval procs name ends with the string 'list' " so even if the proc-resultset retrieval works, I want to force it to 2 or more. The reason we did this was because the proc-resultset retrieval does fail enough that we did not want to have to figure out each time we generated whether we get one or more resultsets from a proc, and as new procs are added, we could easily tell by the name that it should be a retrieval proc.

you can do that easily in the commandline refresher code. After the project has been refreshed, walk the proc calls and check the # of resultsets, if the name ends with list, set the # of resultsets to 2, then call Save on the project object. It's very straight forward. Please consult the SDK reference manual for the object model in the Project object.

We are at 2004.2 on the runtime - I assume I need to update to 2005.1 ? Are there any migration concerns?

Compilation errors from the command line generator :

C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(152): 'SD.LLBLGen.Pro.ApplicationCore.ApplicationConfiguration' does not contain a definition for 'AdditionalTemplateSetsFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(12 : 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalGeneratorConfigFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(132): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalGeneratorConfigFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(137): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalGeneratorConfigFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(123): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalGeneratorConfigFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(156): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalTemplateSetFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(161): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalTemplateSetFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(165): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalTemplateSetFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(170): 'SD.LLBLGen.Pro.ApplicationCore.Project' does not contain a definition for 'AdditionalTemplateSetFolder' C:\Program Files\Solutions Design\CommandLineGenerator\Generator\Startup.cs(140): No overload for method 'LoadAllGeneratorConfigFiles' takes '1' arguments

I could mail you the source of commandline refresher 1.0.2004.2 and generator 1.0.2004.2, though it's recommended you upgrade to 1.0.2005.1. There aren't upgrade concerns: it in general is a matter of regenerating the code and you're done. Please download the documentation of 1.0.2005.1 and check the 'migrating your code' section for details.

I may as well go ahead with the 2005.1 .

Thanks for your help. Dave

Otis wrote:

you can do that easily in the commandline refresher code. After the project has been refreshed, walk the proc calls and check the # of resultsets, if the name ends with list, set the # of resultsets to 2, then call Save on the project object. It's very straight forward. Please consult the SDK reference manual for the object model in the Project object.

after this line: e.Start(projectToRefresh, configurationSettings, preferences, password, overwritePassword, catalogsToRefresh, showErrorLog);

I added:

            foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.ActionSPCalls)
            {
                if (spcd.Name.ToUpper().EndsWith("LIST"))
                {
                    spcd.AmountResultsets = 2;
                }
            }
            foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.RetrievalSPCalls)
            {
                if (spcd.Name.ToUpper().EndsWith("LIST"))
                {
                    if(spcd.AmountResultsets < 2)
                        spcd.AmountResultsets = 2;
                }
            }

I get the following error at runtime:

C:\Program Files\Solutions Design\Refresher\Startup.cs(176): Property or indexer 'SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition.AmountResultsets' cannot be assigned to -- it is read only C:\Program Files\Solutions Design\Refresher\Startup.cs(16 Property or indexer 'SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition.AmountResultsets' cannot be assigned to -- it is read only

Am I trying to change the wrong property?

Dave

The property on the related StoredProcedure object has to be set. so SPCallDefinition.TargetStoredProcedure.AmountResultSets

Otis wrote:

The property on the related StoredProcedure object has to be set. so SPCallDefinition.TargetStoredProcedure.AmountResultSets

That took care of that problem. Thanks

In testing:

I added a stored proc with a select * from table so when I run the refresh, I would expect this to show up in the collection projectToRefresh.RetrievalSPCalls correct? I may be confused about what the refresher is supposed to do, or there is more I need to code so new sprocs get selected? In the readme.txt, is the paragraph about the preferences.xml file only for pre 1.0.2005.1? Is there more doc about the use of the refresher?

Thanks

IowaDave wrote:

Otis wrote:

The property on the related StoredProcedure object has to be set. so SPCallDefinition.TargetStoredProcedure.AmountResultSets

That took care of that problem. Thanks

In testing:

I added a stored proc with a select * from table so when I run the refresh, I would expect this to show up in the collection projectToRefresh.RetrievalSPCalls correct?

SqlServer doesn't store proc resultset info, so this has to be determined and the only way to do that is through SET FMTONLY statements with executing the proc (I use ado.net's wrapper methods to do this). This is safe, but can fail if the proc contains temptables, Sqlserver then throws an error and the proc is added to the actionprocedures.

New procedures are added to the project if the preference 'AddNewElementsAfterRefresh' is set to true.

I may be confused about what the refresher is supposed to do, or there is more I need to code so new sprocs get selected?

The refresher does: - read in new catalog information in a new catalog object - migrate current project to new catalog object - remove the old catalog object.

this is all done in the applicationcore assembly, so all that's done in the commandline tool is call the right methods in the applicationcore.

In the readme.txt, is the paragraph about the preferences.xml file only for pre 1.0.2005.1? Is there more doc about the use of the refresher? Thanks

The preferences for llblgen pro are stored in that preferences.xml file. So to be able to use the settings there, the file has to be present on the machine you run the commandline refresher on.

If you open the preferences dialog in llblgen pro, and check under the catalog refresher section, those are the settings the command line refresher works with. As it's designed to run unattended, there's no userinteraction, so it's key to specify the settings there as you want them.

Thanks for your help. I didn't get the connection before.

One last thing that I'm not sure about. When I create a new project, the views are add as entities, which is what I want, but after running the refresher, there is a Typed View created for each view in the database. This was unexpected, and I don't see a preference to prevent this.

I choose AddNewViewsAsEntitiesAfterRefresh=true in the preferences.

Thanks.

Do you have the first item set to true, AddNewElementsAfterRefresh? This will add everything.

bclubb wrote:

Do you have the first item set to true, AddNewElementsAfterRefresh? This will add everything.

Yes when AddNewElementsAfterRefresh is true I get all the views added as typed views If I say false, they don't get added (desired) but other new objects such as Stored Procs don't get added either.

Is there a way to make this more granular? I don't want the views re-added as typed views but I want new tables and views added as entities AND I want new sprocs added. I guess if the Typed Views are created it's not the end of the world. Just seems unnecessary.

Thanks.

Another thing that is problematic is the following exception:

    Collection was modified; enumeration operation may not execute.

If a sproc is in the actionprocedures collection, and I change the amountresultsets to 2 then llblgen moves it to the retrievalprocedures collection, and the exception is thrown.

IowaDave wrote:

Another thing that is problematic is the following exception:

    Collection was modified; enumeration operation may not execute.

If a sproc is in the actionprocedures collection, and I change the amountresultsets to 2 then llblgen moves it to the retrievalprocedures collection, and the exception is thrown.

If I recall correctly, that's an old bug, do you use the latest version?

Otis wrote:

If I recall correctly, that's an old bug, do you use the latest version?

1.0.2005.1 is the version. CommandLineRefresher 1.0.2005.1.sln The readme.txt was last modified 9/16/2005

The build date of the designer I mean (which is the build date of the applicationcore assembly) It's in the about box

Otis wrote:

The build date of the designer I mean (which is the build date of the applicationcore assembly) It's in the about box

Version 1.0.2005.1 Final Released on March 2nd, 2006

IowaDave wrote:

Otis wrote:

The build date of the designer I mean (which is the build date of the applicationcore assembly) It's in the about box

Version 1.0.2005.1 Final Released on March 2nd, 2006

Hmm. OK, that's indeed a build which should work. Could you paste the code which changes the resultset, and also describe a bit where you've placed that code so I can see if it perhaps crashes because it runs into other problems, or that you might want to use a different routine?

Otis wrote:

Hmm. OK, that's indeed a build which should work. Could you paste the code which changes the resultset, and also describe a bit where you've placed that code so I can see if it perhaps crashes because it runs into other problems, or that you might want to use a different routine?

In startup.cs here's your code:

            Engine e = new Engine();
    e.Start(projectToRefresh, configurationSettings, preferences, password,  overwritePassword, catalogsToRefresh, showErrorLog);

Then my code :

foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.ActionSPCalls)
                {
                    if (spcd.Name.ToUpper().EndsWith("LIST"))
                    {
                        spcd.TargetStoredProcedure.AmountResultsets = 2;
                    }
                }
                foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.RetrievalSPCalls)
                {
                    if (spcd.Name.ToUpper().EndsWith("LIST"))

                    {
                        if(spcd.TargetStoredProcedure.AmountResultsets < 2)
                            spcd.TargetStoredProcedure.AmountResultsets = 2;
                    }
                }

then your code:

    // save project.
        projectToRefresh.IsChanged=false;

Sorry about the formatting!

Thanks! I've cleaned up the colors a bit with code tags (which now understand spaces ). I'll let you know tomorrow morning what might be the cause of it.

IowaDave wrote:

Otis wrote:

Hmm. OK, that's indeed a build which should work. Could you paste the code which changes the resultset, and also describe a bit where you've placed that code so I can see if it perhaps crashes because it runs into other problems, or that you might want to use a different routine?

In startup.cs here's your code:

            Engine e = new Engine();
    e.Start(projectToRefresh, configurationSettings, preferences, password,  overwritePassword, catalogsToRefresh, showErrorLog);

Then my code :

foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.ActionSPCalls)
                {
                    if (spcd.Name.ToUpper().EndsWith("LIST"))
                    {
                        spcd.TargetStoredProcedure.AmountResultsets = 2;
                    }
                }
                foreach(SD.LLBLGen.Pro.ApplicationCore.StoredProcedures.SPCallDefinition spcd in projectToRefresh.RetrievalSPCalls)
                {
                    if (spcd.Name.ToUpper().EndsWith("LIST"))

                    {
                        if(spcd.TargetStoredProcedure.AmountResultsets < 2)
                            spcd.TargetStoredProcedure.AmountResultsets = 2;
                    }
                }

then your code:

    // save project.
        projectToRefresh.IsChanged=false;

Sorry about the formatting!

Ok, you foreach over the RetrievalSPCalls collection. When you set a proc's properties, it fires change events. Also it might be removed from the list and added to the ActionProcs (or vice versa). this means that the collection you foreach over is modified and you get the exception.

You should do: create an arraylist, and foreach over the procs collection to examine, and add the procs to process to the arraylist. then foreach over the arraylist and process the procs.