Home

Lightswitch and Stored Procedure transactions

Saturday, 01 October 2011 11:57 Dave Silverlight - LightSwitch
Print PDF

Running stored procedures from Lightswitch which use transactions

You can see the Demo here, source code below.

 

You can run stored procedures in Lightswitch, but you have to wrap them in a RIA service and treat them as Query, i.e. the DomainService must return an IQueryable (or variation of).

This is not an article about how to run the Stored procedures (but you can download the source below to have a look), but more about how transactions are dealt with within those procedures and Lightswitch.

 

The following relates to SqlServer, but the same should apply to any DB flavour.

If you have a bunch of stored procedures you need to run, and they do CRUD operations, it's best practice to wrap those procedures in transactions and add error handling.

Essentially, you want to be able to Commit/Rollback these operations depending on the business logic in your procedure and also handle any exceptions that may have been encountered.

It's also good practice to keep a record of any messages that are written (i.e. debug output) in those procedures which can give some indication of what the procedure was doing before the error occurred.

I tend do put the following 3 out parameters on all Stored Procedures

@ErrorCode int out - Denotes what error was raised, could be user initiated or come from error_number()

@ErrorMessage nvarchar(max) out - Denotes the error message, could be user initiated or come from error_message()  

@Result nvarchar(max) out - Used to store a "running commentry" on what has happened ... so far

 I also have Try/Catch blocks and usually Raise a user defined exception in the Try block with a Severity greater than 10, which automatically gets propogated to the Catch block where I can set the messages and deal with any transactions (normally this means rolling back).

Because all my procedures use this logic, I have a pretty good "Trace" of what's happened in my procedures, this is especially true if Procedure A calls Procedure B which calls Procedure C etc.

Which brings me on to Transactions, in SqlServer they are handled in Begin Tran and End Tran blocks and can be nested, but only the first Transaction will actually persist or Rollback the data.

You can keep a track of the transaction count using @@TranCount which gives you, you got it, the transaction level count.

So, in SqlServer the Commits and Rollbacks are handled like this .....

Commit

If it is the first trasaction level i.e. @@Trancount = 1, the commit is performed and data is persisted.

If it is a nested transaction i.e. @@Trancount > 1, then committing simply decrements @@Trancount

This ensures that only the outer most transaction does the actual commit.
So lets say you have procedure A, which begins a transaction, @@Trancount now = 1, it then calls Procedure B which begins its own transaction @@Trancount now = 2
If Procedure B does a Commit, it will have no efffect (apart from decrementing @@Trancount to 1), as Procedure A may still issue a Rollback which would Rollback Procedure B too.

Rollback
A Rollback will always rollback to the outermost transaction, regardless of what level of transaction i.e. @@Trancount is set to 0.

General guidlines when dealing with transactions in stored procedures suggest that :-

"If a stored procedure does not initiate the outermost transaction, it should not issue a ROLLBACK."

The upshot is that a transaction should be rolled back at the same level at which it was started, so only the calling procedure that starts a transaction should ever roll back. If the client code started the transaction, none of the procedures should roll back, but raise an error to the client.

What this means is that, if you didn't start the transaction, you shouldn't end it, otherwise you'll get error ....

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

So, finally on to Lightswitch ...........
When you call a stored procedure from Lightswitch, it starts a new System.Transaction (I believe), so @@trancount in the procedure is already at 1 on first entry.

On commits, everything works fine, as the Procedure begins it's Transaction @@trancount = 2, when it commits @@trancount gets decremented back to 1 and the domainService then does it's commit and everyone is happy.

The problem lies with Rollbacks, guidlines suggest that if the Procedure needs to rollback and it did not initiate the starting transaction, then it shouldn't issue a rollback, but instead raise an error to the caller (along with trace info), in our case the client app, it's then upto the client app to do the Rollback.

That's where the problem is, Lightswitch started the transaction, so it's upto Lightswitch to roll it back.

 

I put together a test harness to make it easier to see what I mean, the source code is attached below.

There are ways to deal with this

  1. Rollback the transaction in the Procedure, catch the ensuing error in the DomainService (the error will be Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements), throw a new error from the DomainService, and then catch this error in the call to Load().
  2. Commit the transaction, seems wrong, but because we didn't start the transaction, commiting merely decrements @@trancount, WE MUST HOWEVER, RAISE AN ERROR and the severity has to be greater than 10, it's then upto the caller to decide whether to Commit or Rollback, as only the initiator(outer most transaction) should finish the transaction. So in this case, because we raise an error, Lghtswitch does not issue a commit.
  3. Raise Error, in the procedure with severity greater than 10, catch the ensuing error in the DomainService (the error will be Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements), throw a new error from the DomainService, and then catch this error in the call to Load(), Lightswitch will not commmit.

All work, 2. gets around the Transaction count error, but Rollback seems safer, so I favour that.

If anyone has a more elegant solution, I'd love to know.


If you want to try this at home :-

  • Grab the source code
  • Run the Stored Procedure sql in your DB, By default the DB used is Northwind, but you can change the connection string in web.config to your DB
  • Run the APP

  

Any comments always welcome

  

D


Attachments:
FileDescriptionFile sizeLast modified
Download this file (LSTestRiaProc.zip)LSTestRiaProc.zip 1298 Kb20/10/2011
Comments (8)
Last Updated ( Monday, 03 October 2011 20:53 )
 

Hosting LightSwitch Applications on Winhost

Monday, 09 May 2011 17:44 Dave Silverlight - LightSwitch
Print PDF

Hosting LightSwitch Applications on Winhost

This is a walkthrough on publishing LightSwitch Application to WinHost, it may even be valid for other hosting providers.

If you haven't already, check out Publishing a Silverlight Application on Winhost     

We'll be using the DB we setup there in this example too.

 

What you'll need

LightSwitch

IIS 7

Fidler

SQL Server Management Studio (optional)

 

First things first, create a DB on Winhost, if you haven't already done so and use the link above to setup the connection to it.

It may be a good idea to setup an Application start point too, so do that in the WinHost Panel.

Once you've got LightSwitch installed, open either Visual Studio 2010 or Visual Studio LightSwitch and create a new lightswitch project.

When the template has run, you are presented with the Application Designer Start Page with two options, "Create New Table" or "Attach to External DataSource", we want to do the latter, so click it. You can also right click on the Data Sources folder in the Solution Explorer and click on "Add Data Source".

In the Wizard, click "DataBase" then Next

 

 

Enter your Winhost DB connection details.

Click Test Connection, Click OK when all is done.

In the next screen a list of DB objects available to import is shown, open the Tables Tree and click on Categories (if you are following the example from the previous post, the Categories table should be the only thing listed).

Click Finish.

 

In the Application Designer, you now have the Category "Table" shown, we now need to create a Screen to show the data in the Category Table.

Right Click on the Screens folder in the Solution explorer and Click "Add Screen".

In the next Dialog click on "Editable Grid Screen" on the left and in the Screen Data dropdown on the right select the Categories table.

We can now Save All and Run.

Add a valid windows user if you are prompted.

Ok, so if all went well you should see your LightSwitch Application, connected to your WinHost DB and displaying the Categories, play around a bit.

 

Hosting on the Winhost server

Lets publish to the Winhost Server.

Check out the publishing section  here on the previous article, we're doing pretty much the same.

So right click the Application in Solution Explorer, Click "Publish"

In the first screen click "Web" and then Next.

CLick IIS Server and untick the checkbox, click next.

Click "Remotely Publish to server now", in Service Url enter "https://w04.winhost.com:8172/msdeploy.axd" in Site/Application enter "your_site.com/your_App_StartPoint", the username and password are your FTP username and password from winhost, enter them and click the "Allow untrusted certificate" checkbox.

Click Next.

Check the connection string, it should be ok, click next, next again, look at the summary and then publish.

Publish may take a few minutes ............

 

When it finishes, launch your app by entering the url in a browser, the url you published to i.e. www.your_site/your_app .

If all goes well you should see your LightSwitch App.

If not fire up Fidler and do the following, then try again.


Debugging

To show any server errors you need to set customErrors mode="Off" in web.config.



I normally just edit this manually from the file system (or you can edit it after it's beens published via your favourite ftp client .... FileZilla).

If you want to edit it in visual studio, go to Solution Explorer and Click on the Application, in the top menu of the Solution Explorer, click the FileView dropdown and change it to file view.

Now click the "Show All files" button, (two buttons to the right) and you should see a project called "server generated", this is where web.config lives.


Authentication

If you want to add authentication to your app, you'll need to set this up in the LightSwitch Application properties.

Right click on the Application Project and Click Properties, in the Designer, click on the "Access Control" tab.

By default the Authentication mode is set to "Do not enable authentication", set it to "Use Forms Authentication", this will now use the built in Asp.Net Membership authentication.

When you run your app locally, the authentication tables are stored locally on a file based Sql server db, you can see the connection string in web.config (" _IntrinsicData"), when you deploy your application, obviously you need to deploy these Authentication tables too, but how.

Well luckily for us, LightSwitch deployment does all that for us, we just need to tell it a couple of things.

 

So we've set our application up to use Authentication, so lets's publish it again.

Right click the application and select Publish.

We now have two more steps to configure in the wizard, so click on the "DataBase Connections" tab and enter the required details for your WinHost DB.

The first connection will be used to create the required ASP Authentication tables in your DB, setup the connection as earlier.

The second is the user connection to the DB and defaults to the same connection as above, leave it at that for now.

Click on the "Authentication" tab this is important and click "Yes Create the application Administrator", without creating this user, you will not be able to login!

This Admin user will be created in the table Aspnet_users which will be created for you in your DB on publishing.

Enter the required details, the password by default must be 7 characters or greater and conatin at least one non alpha-numeric character (you can set the password strength later when creating other users if you wish).

Click Publish .........................

 

Gotcha's

  • Make sure you set only one Authentication Scheme, this is done in IIS under Authentication (see previous post for details of how this can manifest itself), if you don't need authentication make sure only "Anonymous Authentication" is enabled, this is the default for a stock LightSwitch app.
  • If you get the following error it means you haven't set your trust level, or more importantly it hasn't been set for you, so you have to "pursuade" IIS to add it for you. 

Description: The application attempted to perform an operation not allowed by the security policy.  To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.]
Microsoft.LightSwitch.WebHost.Implementation.RedirectToHttpsModule.RequireEncryption() +0
Microsoft.LightSwitch.WebHost.Implementation.RedirectToHttpsModule.Init(HttpApplication application) +17
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +431
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +194
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +339
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +253

 

You will need to set your Applications Trust level, to do that either add to web.config or open up IIS 7 and click on your application, click on ".Net Trust Levels" and use the dropdown to set the trust level, if it's already set to full, change it to some other value, click apply, then change it back to Full Trust, click apply again, this will add the relevant trust level to the web.config for you.

You could also just add the following to web.config.

        <configuration> 
             
                           
<trust level="Full">
           
</trust>
         </configuration>
       

 

I hope this helps, let me know

 

TheDev

Add new comment
Last Updated ( Wednesday, 13 July 2011 12:56 )