tmergel
- edited
Today Nick Galler joins us once again to share some tips on using direct SQL in web grids. Great stuff!
Back to Basics - Using Direct SQL in Web Grids
By: Nicolas Galler, Strategic Sales Systems
If there is one skill common to all SalesLogix developer (and most SalesLogix consultants as well) I think it would have to be SQL. It has been a basic building block for customizations for a long time and is still a required tool for troubleshooting, imports and most enhancements. Even the less technically inclined consultants will often be able to express a problem via a SQL query, sometimes this is even true of SalesLogix users. Therefore I was thrilled to find out that there is still an easy way to utilize this knowledge on the new web client and wanted to share it with all interested.
But first a quick recap of the databinding situation on the web client.
- At the top, and most commonly used, we have the SalesLogix entity model, supported by NHibernate. It is a very powerful tool to access the relational data from within the code and support advanced features such as transactions, caching, and lazy loading. With these advantages come a few downsides: first of all, it is rather alien to the current SalesLogix developers, secondly, it can carry a significant overhead, especially for complex queries that are going to load many entities, and lastly, for complex queries, it can yield some very procedural, hard to read code.
- Next up we have the mashup model, this is a very neat way to design your queries against the entity model. When querying the entity model this is done with HQL, a language similar in syntax to SQL, though non-trivial queries such as Union or Case statement are harder to express. This can also be used to query different types of data source such as an RSS feed, so it is very powerful, yet I feel this still has a few significant downsides: first of all the mashups have to be defined separate to the form, and are not categorized, so they may get hard to manage if you use them a lot, but more importantly, HQL is not SQL, so you won’t be able to leverage your SQL skills, use SQL Management Studio to design the query, etc. They also have to be explicitly deployed, like everything that is built in the Application Architect, though that is usually not a huge problem since you can easily test them within the Application Architect. It is a great tool and it will only get better with time.
- Finally the approach that I want to present here relies on a basic ASP.NET functionality to query the database directly. You create a datasource, associate a SQL query with it, define whatever parameters are needed to run it, and finally associate it with the grid or list that needs to be bound to the result. It is very simple, one might even say primitive, yet it effectively gives you the full power of SQL, leveraging your existing skills and allowing for design of the query using the tools we are all familiar with. Of course it also comes with a few downsides, and I definitely want to touch on those first.
When to use (or NOT use) a SQL data source
Think of the reasons that you may want to use a direct SQL query for your form:
- Perhaps you have an existing SQL view or stored procedure that you need to access
- You need to display a complex relationship on a grid (this can almost always be done within the NHibernate framework as well but as stated above you can quickly end up with 100 lines of procedural code, hard to test, design and maintain)
- You have a query accessing many tables and need to optimize the joins
- You need to access data from another database (in this case of course you will have to tweak the connection string accordingly)
- You need to bind a 3rd-party control – most of those will work with these standard ASP.NET datasources but may have problems with the native SalesLogix datasources
- Or maybe you just have an existing query that you would like to use as is. The query works, it has been tested, it is easy to verify the results in SQL Studio, why mess with it? Microwaves are convenient but we all like to come home to a warm dinner from time to time.
Yet despite the convenience of SQL, this approach is NOT my default approach when binding a grid. The reason is that the NHibernate framework has some very significant advantages that make it preferable in the general case.
- First of all, do NOT use direct SQL to do modification of data (updates, inserts or deletes). This will not mesh well with the NHibernate relationships – and you will also be fighting against both the transaction and the caching mechanisms
- As it only works in custom smart parts it implies the usual drawbacks (refer to my previous article for an extensive discussion on that subject!) This is a solution very specific to ASP.NET and if (or when) the Application Architect is able to generate other types of projects, it will have to be ported
- Be careful not to cram too much logic into the SQL – it may be easier to maintain as a business method on the entity side, especially if you will need it from more than 1 form
- Caching – this can greatly improve performance because NHibernate will often be able to return the objects directly from memory, without needing to hit the SQL database at all!
- If you are creating a generic control you have to be extra careful not to use SQL syntax that might be specific to Oracle, or SQL server. Normally this is something that NHibernate would take care of for you
- Finally, because SQL works directly against the database, you can’t use it for transient records. Think about a screen like “Add Product” – SQL does not work here, because the products won’t actually be saved to the database until the user clicks OK.
However for the case of a grid on a tab with a complex query, I think that the SQL data source can save you a great deal of time and end up with more efficient code. This is a pragmatic approach – you know there are some drawbacks, and hope that they are offset by the savings.
A practical example
As an example I am going to redo a tab that I created on the network client. It is an association grid at the contact level that lets users see both contacts and accounts associated with the contact. It uses the standard ASSOCIATION table but unlike the standard Associations tab, the FROMID and TOID fields don’t have to be of the same type. We want to present associations in both directions, with the corresponding relationship attribute (FORWARDRELATION / BACKRELATION). It was a relatively easy customization on the LAN client – it just requires the use of a custom query on the datagrid for the union of both directions – and using the SQL data source I found it even easier on the web client. To spice things up a bit I added 2 requirements:
- A cascading “Address” grid, where the user can select a row from the association grid and see the addresses associated with the contact or account selected
- A filter dropdown where the user can select a filter for the relation (FORWARDRELATION or BACKRELATION, according to whether the current record is on the “TOID” or “FROMID” side)
We could achieve all that with the entity model of course, maybe add a GetAssociations business rule on the contact that would scan the association table using a EntityFactory criteria and return some sort of DTO object that we could use in the grid, but there might be quite a bit of code involved. I think there is a good case for a practical, SQL-minded approach.
This will make use of a custom smart part but unlike in my previous article, I am going to mostly use the Visual Studio designer here, except for the very beginning (setting up the basic layout) and the very end (polishing up the CSS).
First open Visual Studio and create a new user control. I put mine under SmartParts/Contacts and called it Associations.ascx. In retrospect that may not have been the best choice since it creates a bit of confusion with the stock Associations tab but it still works fine. I find it easier to do the basic layout in the source view but feel free to use the designer:
Switch to the designer view and drag a grid view into the bottom left cell, then a SqlDataSource at the bottom. Then use the “Configure DataSource” wizard on the SqlDataSource – here you will have to define the connection to the database, just go ahead and use the SQL server info since we will have to change it at runtime anyway, make sure you pick the “OleDb” version of the driver though (under the “Data Provider” selection).
After a few “Next” click you will be prompted on whether you wish to use a custom SQL statement, or have the wizard build it for you. Since we already know SQL here I think it is safe to just use a custom SQL statement 
This is my SQL, nothing too fancy, I would say level 2 or 3. Note I am using some dummy contact ids and hard coded the relation type for now, we will replace them with parameters later on:
Finally we need to add a little bit of code on the control. This is to ensure that the connection string is obtained from the web site configuration. Thankfully, this is provided by the ever useful DataService. Switch to the code view (F7) and add this inside of the default Page_Load method:
You can now add the view as a smart part in AA, redeploy and see the view in the client. Obviously there is not much to it right now:
Let’s go ahead and add the parameters so that it displays only the records related to the current contact. Select your association data source go to the properties (bottom left corner of Visual Studio, by default – note that they are usually not shown if you have the debugger running) and go to the “SelectQuery”. Replace the dummy contact ids with question marks, like so:
Then under parameters click on “Add Parameter” twice. You don’t need to set the value here, but rename them as you add them because you will need the names later:
Finally in your code view add the following under Page_Load (you can just add it right after the connection string code that was added earlier):
Remember if “EntityPage” is not detected automatically you can use the Visual Studio “auto-fix” shortcut (Ctrl+. by default) to add the correct Using line.
I believe this is what this would look like in VB, by the way:
Now check out your form in the web client – remember that you do not need to deploy or build in App Architect at this time. Make sure the grid refreshes when you use the arrows from the group navigation control, too.
We need a dropdown for our relation type selection. Honestly we could probably use a picklist here but since we are having fun let’s say we need to account for values that are no longer in the picklist and want to obtain the values via a “SELECT DISTINCT”. Go ahead and drag a new SqlDataSource and a DropDownList to the designer. Configure the SqlDataSource as above (I renamed it to dsRelationTypes, I am slightly picky about my names) and the following SQL for example:
Then hook them up, as you can see it’s quite straightforward, just click on the small arrow button next to the control and click “Choose Data Source” (while you are at it click “Enable AutoPostBack”):
Remember to set up the connection string, the same way it was done for the associations source, in the Page_Load method – this is quite important because if you forget this step the site may work well in development but fail on production:
Back in the Select Query of the associations data source, again replace the hard-coded “Friend” relation type with question marks, and click on add parameter twice. Reorder the parameters to ensure they are in the same order as in the query, and change the source to “Control” then select your dropdown:
That’s it! Again you can check it out on the web site and make sure everything works. It still looks pretty bad – we will fix up the style to match the SalesLogix grids at the end.
Finally, we can add our address grid. I will speed things up a bit here since the article is getting long already! Add the entity id (something like ISNULL(c.contactid,a.accountid) as ENTITYID) to both parts of the UNION query, drop a grid view into the lower right corner, and a new SqlDataSource. You can configure a “SELECT … FROM ADDRESS WHERE ENTITYID=…” type query on the data source and associate it with the grid, similar to what was done at the beginning of the article. Then add a hidden field called txtSelectedId to the form and associate it with a parameter on your new datasource:
And remember to set up the connection string for it in the Page_Load method.
Select the associations grid, make sure the “DataKeyNames” property is set to “entityid” and add a “Select” column:
Next, a bit of code to bind the 2 grids – add this in Page_Load:
And this method to the form (if you used auto completion when adding the previous line, Visual Studio will generate most of this for you):
Voila – filters and cascading grid:
OK, the grids are still very ugly – let’s fix them up. For this part I admit that I switch to the HTML view and paste the code!
Basically it is a replacement of “asp:GridView” with “SalesLogix
lxGridView”, and a few extra attributes – I replace this:
Same thing with the address grid. Sorry, I know it is a bit of a soup, but it is the same style used in all grids of the client, and unfortunately these are not set by default. You can also use the SlxGridView from the get go but it has a few quirks so I find it easier to start with a “vanilla” ASP.NET grid.
Here is the result:
I won’t go over the insert form because it is a bit beside the point, however I did include it in the sample bundle you are welcome to download at http://blog.nicocrm.com/wp-content/uploads/2009/12/SqlDataSource Sample.zip. Obviously I am leaving a few things out – the first record should be selected by default, there should be a delete button somewhere, and probably we’d like the user to be able to just click on a row to select it instead of having to click the “Select” link – but as a sample of SqlDataSource I think it is quite enough. By the way with very minor modification this smart part could be made generic so that it would work on both the contact and the account screen.
Conclusion
I have sometimes seen comments on the forum struggling to fit a complex query in the entity framework. It is good to know that one must not always turn one’s back on trusty old SQL! At the same time, do keep in mind the few caveats stated at the beginning of the article before automatically jumping on SQL for every grid on the application, as tempting as it may be.
