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.
Think of the reasons that you may want to use a direct SQL query for your form:
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.
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:
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 “SalesLogixlxGridView”, 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.