Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Defining a storedprocedure
 

Pages: 1
LLBLGen Pro Runtime Framework
Defining a storedprocedure
Page:1/1 

  Print all messages in this thread  
Poster Message
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 09-Oct-2009 09:36:02.  
Hello,
I'm about to try to use a stored procedure (in response to a suggestion of Daelmo) in order to get info on table size. For this.. I found the storedprocedure sp_spaceused was available.
See here: http://msdn.microsoft.com/en-us/library/ms188776.aspx


I've read in the manual that step 1 is to define a stored procedure in the Designer.

If I right click on Retrieval Stored Procedure Calls, a popup menu shows, first option "Add stored procedure call" ... greyed out! Can't select it.

Am I missing something?

I'm using version 2.6 final, rel.date sept 12th 2008.

Thanx for your input!

Paul
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14529 posts
# Posted on: 09-Oct-2009 12:28:13.  
System objects including systemprocedures aren't read.
Please create your own SP, even if it's just a wrapper.


  Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 13-Oct-2009 09:14:42.  
Walaa,
thank you for the suggestion. Just did that and it works well.
In order to get the space used for each datatable, using the storedprocedure sp_spaceused, I did the following:


private DataTable SPSpaceUsedForEachTable()
        {
            // ForEach Table construction, using the EntityType enum.
            EntityType et = EntityType.Mapl_AdEntity;
            String[] tableNames = Enum.GetNames(et.GetType());

            SqlParameter[] sqlParms = new SqlParameter[2];
            String objName = String.Empty;

            for (Int32 i = 0; i < tableNames.Length; i++)
            {
                objName = tableNames[i].TrimEnd("Entity");
                sqlParms[0] = new SqlParameter("@objname", objName);
                sqlParms[0].IsNullable = true;

                sqlParms[1] = new SqlParameter("@updateusage", "true");
                sqlParms[1].IsNullable = true;


                DataTable tempTable = new DataTable();

                DbUtils.CallRetrievalStoredProcedure("sp_spaceused", sqlParms, tempTable, null);
                DataRow tempRow = tempTable.Rows[0];

                
                DataRow newRow = spaceUsedDataTable.NewRow();
                newRow.CopyValuesFrom(tempRow);
                newRow[6] = String.Format("{0} {1} {2} {3}", tempRow[2], tempRow[3], tempRow[4], tempRow[5]);
                spaceUsedDataTable.Rows.Add(newRow);
            }

            return spaceUsedDataTable;
        }
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14529 posts
# Posted on: 13-Oct-2009 09:33:14.  
You could have also created your own SP which wrapps sp_spaceused, then map it to LLBLGen retriwval procedures, so you can directly call the corresponding generated static method call.
Code:
RetrievalProcedures.MySpaceUsedSP(...);

Passing .NET types rather than creating and passing the SqlParameters yourself. As the generated SP method call do this for you.


  Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 13-Oct-2009 12:38:14.  
Yep, I see what you mean.
This way I could code everything in C#, without writing a SP.
Paul


ps. Could it not be an idea that LLBLGEN provides a wrapper for all system stored procedures? Perhaps as seperate DAL project?
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.