Category Archives: WCF

Working with SalesForce and Dynamics CRM data in Visual Studio 11 Beta

Microsoft Visual Studio 11 beta and Windows 8 Consumer Previews were made available on Feb 29th 2012. They will be valid through to June 2012.

The Xpert360 development team took the beta for a spin with the latest versions of the Xpert360 Lightning Series product builds: WCF Ria Service data source extensions for LightSwitch and .NET 4 that connect to salesforce and Dynamics CRM Online instances.

VS11 Beta Premium Applying: LightSwitch Beta Core

VS11 Beta Premium Applying: LightSwitch Beta Core

After the VS11 install a quick rebuild of the data extensions VSIX in VS2010 pulled in the latest software versioning as shown below:

Clip for LightSwitch Data Extension vsixmanifest

Clip for LightSwitch Data Extension vsixmanifest

The new VSIX files now prompt for the version of Visual Studio if not already installed and within ten minutes of the VS11 install we are building our first Visual Studio LightSwitch 11 application to interact with our CRM test systems.

LightSwitch project templates in VS11 Beta

LightSwitch project templates in VS11 Beta

Then we create new data connections with the Xpert360 Lightning Data Extensions.

LightSwitch designer - Choose a WCF Ria Service

LightSwitch designer - Choose a WCF Ria Service!

… and we move on and choose some of the CRM entities exposed by the service.

LightSwitch designer - Select Data Source Objects

LightSwitch designer - Select Data Source Objects from Dynamics CRM Online

The chosen entities appear against the data source in the LightSwitch designer and can be explored and manipulated as usual. Notice the automatically available entity relationships between the salesforce entities.

LightSwitch designer - SalesForce Opportunity Entity

LightSwitch designer - SalesForce Opportunity Entity with chosen subset fo relationships

A few more clicks to build a list detail screen…

LightSwitch designer - Xpert360 Lightning Data Extensions

LightSwitch designer - Xpert360 Lightning Data Extensions

Ten minutes later we have our first CRM data from salesforce and Dynamics CRM Online.

LightSwitch - SalesForce Opportunities List Details

LightSwitch - SalesForce Opportunities List Details at your disposal

Looking around in the VS11 IDE the default theme has faded to grey, perhaps its vying for an Oscar with “The Artist”. Here are the two LightSwitch project views available in solution explorer:

LightSwitch designer - Logical viewLightSwitch designer - Logical view

LightSwitch designer - Logical and File views

If you are embarking on custom UI controls with Silverlight 5 then here are the available project templates in VS11:

VS11 Beta Silverlight project templates

VS11 Beta Silverlight project templates

We are also eagerly awaiting the VS11 LightSwitch Extensibility Toolkit which has been indicated to be ready in a few weeks by Beth Massi:

The Xpert360 Lightning Series data extensions will unleash the true power of LightSwitch onto your salesforce CRM and Dynamics CRM Online data very soon. They are currently undergoing private beta testing which will now be extended to include the VS11 Beta as this platfrom has a go-live license.

If you somehow missed all the anouncements here are the links:

Any feedback or enquiries welcome at mailto:info@xpert360.com

Advertisements

Using Table Value Functions to Replace ‘Get’ Stored Procedures?

I learnt so many useful techniques, tricks and pointers at this SQLBits conference in York: tally tables, common table expressions (CTEs), cross applies, query tuning techniques, parallelism, data visualisation, to name but a few. However, the most useful technique I learnt was divulged in the first couple of hours during the training session given by Rob Farley.

In amongst the banter, jokes and excursions, a la Geordie comedian Ross Noble, was a complete gem which will make a huge difference to our existing and future projects.

Put simply, never use stored procedures for simple retrieves, but replace these with Inline Table-Value Functions (TVF).

Given our love of stored procedures, this is a difficult pill to swallow at first, but the benefit can be seen through the following example…

Suppose we have written a stored procedure that returns a dataset to the business layer of the application. In our case, we will have delivered the dataset via a WCF service.

Our stored procedure might be something like:
CREATE PROCEDURE dbo.usp_MyTable_Get (@FilterParms…)
AS
BEGIN
SELECT T.Column1, T.Column2, T.Column3, LT.Column2, …
FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable AS LT ON LT.IDCol = T.Column4
WHERE T.ColumnN = @FilterParm1

END

Now suppose that the application development team ask for an additional column to be included in the returned dataset. This may be because the dataset returns a foreign key identifier and the developers need to see the lookup description in the returned dataset. To return the lookup value we need to include an additional join on the lookup table.

Before going ahead, we’d confirm that the developer hasn’t or can’t get the lookup description from content held in memory; we’d explain that we’ll need to create a new stored procedure just for this situation, or, we’ll need to include it in the existing one which will (unnecessarily) adversely impact performance for instances where the service is used but the additional column is not required.

We have seen this situation many times and it is frustrating that an optimum one-size-fits-all solution hasn’t presented itself – until now…

The solution is to replace the stored procedure with an inline TVF which includes every possible join for foreign key lookups and every possible output column, and then build up SQL SELECTs on this TVF with the required columns relevant to each individual situation, letting the Query Optimizer exclude joined tables where necessary.
So, the above stored procedure is replaced with:

WCF Service
public List GetMyTable(dtoRequiredColumns, dtoParameters, dtoFilter)
{
List dtoOut = new List()
string strSQL = ""

foreach (col in dtoRequiredColumns)
{
if (col.IsRequired) strSQL += ", " + col.Name
}

strSQL = "SELECT" + strSQL.Substring(2) + " FROM dbo.tvfMyTableGet"

// add any WHERE and ORDER BY clause

SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(strSQL, SQLConnection)
DataTable dtResult = new DataTable()
SqlDataAdapter.Fill(dtResult)

foreach (DataRow drRow in dtResult.Rows)
{
// add row to dtoOut
}

SqlDataAdapter.Dispose()

return dtoOut
}

and the TVF definition is something like the following:

CREATE FUNCTION dbo.tvfMyTable
RETURNS TABLE
RETURN
SELECT T.every column…,
LT1.everycolumn…,
LT2.everycolumn…,
LT3.everycolumn…,

FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable1 AS LT1
ON LT1.IDColumn = T.ForeignKeyID1
INNER JOIN dbo.MyLookupTable2 AS LT2
ON LT1.IDColumn = T.ForeignKeyID2
INNER JOIN dbo.MyLookupTable3 AS LT3
ON LT1.IDColumn = T.ForeignKeyID3

GO
So the function includes every column in MyTable, and is joined to every table that provides a lookup for foreign keys.

And here’s the clever thing, at run-time the optimizer looks at which columns are included in the SELECTed column list and excludes those JOINs on tables that are not required for that particular query. In addition, query plans are cached (however, note that different length string parameters will generate additional cached plans).

The objections might go something like – underlying data is made more secure by placing these behind a stored procedure ‘firewall’. Of course, just as with stored procedures, we can grant execute permission to the function and not the underlying tables or views. And, this looks like dynamic SQL, which is bad! But, we are not allowing any injection to take place here – the listed columns are defined within the dtoRequiredColumns object and full control of the ‘dynamic SQL’ is maintained.

Here at last is the one-size-fits-all solution, so thanks go to Rob Farley for this.

Read more from Rob Farley’s Blog here .

My Experience using Entity Framework for WCF/SQL Server Layered Application

This post is a little late coming , as I first starting using Entity Framework 4 when it was on CTP 1 at the start of the year. I used it up to CTP2 in March/April 2010.  Forgive me if there have been changes since and please do visit the MSDN site here and do read up on it (I read Julia Lermans book and her blog here)

First let me start by saying EF4 is a great product. It has the potential to not only generate code and classes for communication with database but also now has the ability to generate the database schema based on your code. This is great for developers who are not confident with databases or T-SQL, or do not have a lot of time to create a working system.

My background is primarily as a sql dba and developer, perhaps this is why I had a few problems with EF.

I have always believed that a good database layer is the best starting point for a good application design.  I realise there are times when this is not always possible …

However, I like using stored procedure when writing real applications.

  1. They have the ability to protect the application from schema changes making it easier to change code without redeploying your front end.
  2. They can protect the database from unscrupulous client developers who like writing badly performing inline sql (you know who you are …!)
  3. They generally have the capability to perform well (of course they have to also be written well!)
  4. They can be used to protect from direct table access and permissions can be applied both externally and programatically within the procedure.
  5. They can enforce atomic updates to your data across multiple tables.
  6. Most importantly for me.  They can remove a lot of complexity from the application and can allow you to create a normalised data schema, but protect the developers from the complexity of multi-tabled updates.

So why is this a problem I hear your ask.  Entity framework can be used with stored procedures can’t they?

Well yes and no …

Of course you can map your insert/update/delete portion of the entity to your stored procedures.  But they have to be written in a certain way and they must return key values in a select statement (no output parameters allowed here).

No out of the box support for the Get data process being a stored procedure, this needs to be a table or a view, unless you like editing the xml files.  I found this side of the entity framework to be incredibly painful, in terms of setting an exact entity that could use a view for the Get and stored procedures for the rest.  Let alone when you want to add a column and have to start all over again.

However what I did find useful in the entity framework was the ability to knock up a working demo system in an incredibly short amount of time. I had a WPF/WCF/SQL 2008 system working with a single table process in a matter of days.  But what I found was that I couldn’t take it any further without wasting a lot of time remapping schema changes to the entities.  I also decided to take a look at some of the generated SQL statements from the entity sql and linq sql. It wasn’t the worst I’ve seen but boy was it verbose !

My conclusion is … use the EF if you want create quick proof of concepts or prototypes but when it comes to writing real database applications stick to the stored procedures and real T-SQL, your DBA will thank you for it when he has to take it over and support it.  Microsoft seem to be approaching the whole code generation tools from the front end.  What I really need is the reverse, I want a really good database design, with standard stored procedures for data updates and tool to help me connect it to a front end without me writing all the code!

Please try it for yourself and do read the material at the start of the post  – make up your own mind!