This project is read-only.

Import sp and functions

Jan 13, 2010 at 6:47 AM
Edited Jan 13, 2010 at 6:49 AM

nHydrate Team,

We could import ms sql db tables successfully.  Can we import existing sp and function?  These objects are essential for appl. development.

And also suggest how best we can wire sp. result set with table rows in DAL.  For eg:, Stored proc validates imput params and returns top 10 sales by month.  Sales Person (table) info might be required on sp result set rec. query,  for given sales person id.  



Jan 14, 2010 at 4:30 AM

We are actually looking into this.I will get back to you in a few days.

Jan 16, 2010 at 8:13 PM

Please download the latest version. The new import wizard will allow you to import existing views and stored procedures. Keep in mind that the stored procedures have no columns when imported. You will need to add these manually. The parameters will be imported but columns will not since you can return any (or many) sets of data from a stored procedure.

Jan 19, 2010 at 10:57 AM
Edited Jan 19, 2010 at 11:01 AM

Thanks for the updates.

Downloaded ver3.6.  Imported simple sp with one input param that retuns rows.  For eg:, SP returns candidatename, primaryskillname, secondaryskillname, otherskillname from candidate and skill tables by joining primary,secondary,other skill ids of candidate table to skill table's skill id.

Imported sp 'HrspgetcandidateskillsSelect' displays candidateid param.  Added 4 columns candidate_name, primaryskill_name, secondaryskill_name & otherskill_name. Error displayed that atleast one col. should be generated set to true on verification.  So changed candidate_name's Generated property to true.

Created new project to test 'HrspgetcandidateskillsSelect''.  Error "The stored procedure 'gen_HrspgetcandidateskillsSelect' doesn't exist." is displayed.  Changed 'HrspgetcandidateskillsSelect'' Generated property to false.  Verified and generated.  Tried to test the code but    HrspgetcandidateskillsCollection  not visible.

Note:  We had to copy Widgetsphere.Core.dll explicitly to project's bin folder.  Hope this is not solved issue(? or  is it required setting?)


Jan 19, 2010 at 1:33 PM

Yea, I know what this is. When you import the SP you are really only importing the SQL text and parameters. The name when generated has the "gen_" prefix. All generated files do. We need to come up with a scheme for importing. I know you are trying to use existing procedures and do not want the underscore mapping for name and the "gen_" prefix for object names. I will need to discuss this with other and come up with a general purpose solutions. If you generate the install project and execute it the proper SP objects will be added to your database and called appropriately then, since these "gen_" objects will be present. I assume you did not gen the installer and run it. You will need to do this in any case because all the CRUD layer is based on SP calls and they actual SPs need to be installed in the database.

Jan 20, 2010 at 6:34 AM

I selected "DAL & Database Installer/Updater" and executed 'generate'.  Later create test (startup) proj in same c# solution. added reference to nhydrate <company>.<proj> project.  Set the connection string. Created instance of 'Storedprocedures.<spname>collection.Runselect(@params)'.  Got the above mentioned error.

Later generated only 'DAL'. with imported sp's Generate property true.  Got same error.

Thinking that (correct my understanding about Generation ) sp is not created using nhydrate but already exists in DB, so set the sp's generate property to false.  Executed Generate both with 'DAL & db installer/updater' and just'DAL' options.  now <importedspname>.collection is not listed.




Jun 8, 2010 at 3:28 PM

Hope I'm not hijacking this thread, but, what is the point of manually creating columns for a stored procedure? I want a generic result set returned in most cases such as a dataset. I often have complex statements in my SP's as well as returning multiple result sets. I would assume as most generators do, that the genreated code would focus on the required parameters and not manually mapping columns to an embedded sql statement. I certainly don't have a problem with it. This is my first time using nHydrate over Subsonic and this feature is a pain.