Running a Stored Proc

 Hi All,

I have a stored procedure that I want to run (from a Winform) , that does not return a record set of any description and is not mapped to any entities. It runs 3 other stored procedures that grab details from linked servers (in SQL Server 2005) and returns details to the database. This procedure is run on a schedule every night, but if, for any reason, that fails, I want to be able to run it manually from the front end.

Am I right in thinking that currently, I cannot run this sort of procedure via Lightspeed ?

On another note, is there an easy way to search the blog ? I seem to remeber there was a entry on running stored procs some time back.

Cheers

Paul

 Just found the screen cast on Stored Procs, but would it be possible to have some sort of available index for these, rather than having to trawl back through the Blog ? Ta.

http://www.mindscape.co.nz/products/LightSpeed/screencasts.aspx

The central index of screencasts :-) I'll leave Ivan to answer your actual question.

 - JD

You can run some scalar stored procedures via the Calculate(ProcedureQuery) method.  (The procedure must still return a value, and Calculate may not support some scenarios -- I'd advise giving it a quick try with the required sproc before betting your life on it.)  There is no designer support for this though.

Cheers for the lightning replies guys !

I'll give it a whirl with Calculate and see what happens. I can always run it through a SqlCommand object if it doesn't play nicely.

 OK, I have a test query running as below, but I'm getting a timeout error (System.Data.SqlClient.SqlException - Timeout expired) I searched through help and can't find where to set the timeout to a greater length. Is it on the uow somewhere or the ProcedureQuery or do I need to set directly in the SQL objects somewhere ??

ProcedureQuery qry = new ProcedureQuery("STP_ImportProcedure_Run",
                                new ProcedureParameter("ImportHistoryTypeID", 1,ParameterDirection.Input,DbType.Int32,0),
                                new ProcedureParameter("LocID", 11, ParameterDirection.Input, DbType.Int32, 0),
                                new ProcedureParameter("ISOStartDate", "20090301", ParameterDirection.Input, DbType.String, 8),
                                new ProcedureParameter("ISOEndDate", "20090301", ParameterDirection.Input, DbType.String, 8));

            ModelUnitOfWork.Instance.Calculate(qry);

You can configure the timeout for SQL Server connections on the connection string.

Check out this site for a reference: http://www.connectionstrings.com/Articles/Show/all-sql-server-connection-string-keywords

Specifically, the 4th row down - "Connect Timeout"

Let us know if that helps.

 - JD

 Ah, OK. I was looking for an override to the connection timeout property specific to that query in a similar manner to ADO. I am using a single connection and uow for the lifetime of the app at the moment, so if I change the connection timeout, it will affect all queries, where as I only want it for this one. I will look into creating a different connection and uow for this particular query as it can take a couple of minutes. I also want to look at how to run it asynchronously so the user can cancel if it;s taking too long.

Cheers

Paul

 Sooo, I don't suppose someone can point me to the right place in the documentation to start looking at how to run asynchronous queries ? Is it possible using Calculate ? It's going to be really basic to start with - I just want to run the query and show a progress type bar so that the user can cancel the query if it seems to be taking too long. Is there some sort of Beginxxx Endxxx pair somewhere or should I be looking into the BackgroundWorker class (if so ,any pointers to articles on the web to read ??)

Cheers

Paul

LightSpeed has no built-in support for running queries asynchronously.  Use an asynchronous delegate or (probably easier in your environment) the BackgroundWorker component.

Not sure about tutorials on the Web, though MSDN and CodeProject are bound to have about a gazillion articles on BackgroundWorker: check the Great Gazoogle.  Did you get hold of a copy of Sells & Weinhardt?  If so, they cover this in chapter 18, "Multithreaded User Interfaces," in particular the section entitled "Simplified Multithreading" on page 750.

 Thanks for that Ivan. Have read up about BackgroundWorker and that seems to be the easiest thing to use at this stage.

Hi All,

I can't seem to get the timeout to work at all.Have had a most frustating morning !!

I've tried "Connection Timeout=0" and "Connect Timeout =0" and "Connection Timeout =60" but whatever I do it times out after 30 seconds. This post seems to suggest that the problem may be for the individual query

http://stackoverflow.com/questions/27472/timeout-not-being-honoured-in-connection-string

Is it possible that the encapsulation of calling a stored proc in Lightspeed is overriding the timeout with a default of 30 seconds ? Are there any plans to expose the command timeout ?

Here's the code :

In the appconfig

<lightSpeedContexts>
    <add name="default"
         connectionStringName="MainConnection"
         dataProvider="SqlServer2005"
         pluralizeTableNames="False"
         identityMethod="IdentityColumn"/>
    <add name="NoTimeOut"
         connectionStringName="NoTimeoutConnection"
         dataProvider="SqlServer2005"
         pluralizeTableNames="False"
         identityMethod="IdentityColumn"/>
  </lightSpeedContexts>

  <connectionStrings>
    <add name="MainConnection"
         connectionString="Data Source=REMSOFT_DELL\SQL2005;Initial Catalog=AoteaCentral;Integrated Security=True;Pooling=False;Connect Timeout =0"/>
    <add name="NoTimeoutConnection"
       connectionString="Data Source=REMSOFT_DELL\SQL2005;Initial Catalog=AoteaCentral;Integrated Security=True;Pooling=False;Connection Timeout =0"/>
  </connectionStrings>

the unit of work code (note have tried both connections "default" and "NoTimeOut" woth the same result") :

 public static ModelUnitOfWork Instance
        {
            get
            {
                if (_instance == null)
                {
                    _context = new LightSpeedContext<ModelUnitOfWork>("default");
                    _instance = _context.CreateUnitOfWork();
                }
                return _instance;
            }
        }

and here's the procedure call (note that it works if I set parameters that will get the query done in under 30 seconds)

ProcedureQuery qry = new ProcedureQuery("STP_ImportProcedure_Run",
                               new ProcedureParameter("ImportHistoryTypeID", 1, ParameterDirection.Input, DbType.Int32, 0),
                               new ProcedureParameter("LocID", 17, ParameterDirection.Input, DbType.Int32, 0),
                               new ProcedureParameter("ISOStartDate", "20090308", ParameterDirection.Input, DbType.String, 8),
                               new ProcedureParameter("ISOEndDate", "20090310", ParameterDirection.Input, DbType.String, 8));
            ModelUnitOfWork.Instance.Calculate(qry);

I think you're right -- we're not actually overriding the timeout, it's just that (as per the thread you cite) the command timeout seems to be independent of the connection timeout, and we're not doing anything to modify the command timeout away from its 30-second default.

We'll look into exposing this for you.

 Great, thanks Ivan - I'm not going mad then {:o)

 Pressed return before I finished....

Having the command timeout exposed also simplifies things with regards to connections. i.e. I won't have to create different connections (and different code to create units of work on that connection) just to have a different maximum timeout. In some cases I want a never ending timeout (which I will wrap in an asynchronous call), in others the standard 30 seconds is OK in in other scnarios, I want a couple of minutes.

Righty ho, this will be in nightlies dated 21 Mar 2009 and above.  You can set the timeout through code (LightSpeedContext.CommandTimeout) or configuration (commandTimeout).  The default is the database provider's command timeout.  The setting affects all database interactions -- queries, updates, sprocs, etc.

If you change the timeout while the app is running, commands use the timeout that was in effect when they were issued.  This may require a bit of care in a multithreaded environment.  For example, suppose you set the timeout to a long time on the UI thread, then kick off a BackgroundWorker which will run a long query in the background, then in the UI thread reset the timeout to the default.  The UI thread might reset the timeout before the BackgroundWorker has had the chance to begin executing the command.  And this will not always be 100% predictable because LightSpeed doesn't tell you exactly when it has assigned the timeout and it is therefore safe to reset it -- the command timeout isn't assigned until sometime after the code has entered the Find or Calculate method.  (In practice this "sometime after" will usually be a few microseconds, so we don't expect this to be an issue as long as you exercise a bit of caution.  Just don't kick off the BackgroundWorker and then immediately reset the timeout.  If it does foul you up then let us know.)

 Jeez, you guys are quick.

Many thanks for that.Yet again confirms my decision in going with Lightspeed.