There are times when you would like to filter a Lookup based on the selected value of another Lookup, but those entities do not have a direct relationship to each other. For instance, I had a situation where there was a Work Item Tracking entity that had Lookup fields for both a Project and Work Item Type entity. Based on what the user selected for the Project and the Work Item Type’s “Default” value, the Work Item Type Lookup needed to be filtered.

However, there is no direct relationship between the two; Work Item Types are associated with Projects via a Program entity. So, I basically needed to create a view for the Work Item Type Lookup that only showed records with a Default of “Yes” and also joined to Projects through Programs. The problem I ran into, though, was that I mistakenly thought that I would need to set things up so the view would join from the Work Item Type to the Programs, then to the Projects where the Project was equal to the currently-selected Project, then back to the Programs, then finally back to the Work Item Type, where I could filter by Default.

That would’ve been fine if the result set was pulled from the Work Item Type join at the end, but it’s pulled from the original Work Item Type reference. So, a less convoluted approach was needed: I had to filter by Default, but also join from Work Item Type to Programs, then to Projects where the Project was equal to the currently-selected Project. Throwing it together in a Personal View, it looked like this:

From there, it was a matter of grabbing the FetchXML so I could use it in an OnLoad JavaScript function to dynamically replace the “projectid” value and name:

<?xml version="1.0"?>
-<fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0">
	-<entity name="workitem">
		<attribute name="workitemid"/> <attribute name="name"/> <attribute name="createdon"/> <order descending="false" attribute="name"/> -<filter type="and">
			<condition attribute="default" value="1" operator="eq"/>
		</filter> -<link-entity name="program" alias="aa" to="programworkitemid" from="programid">
			-<link-entity name="project_program" to="programid" from="programid" intersect="true" visible="false">
				-<link-entity name="project" alias="ab" to="projectid" from="projectid">
					-<filter type="and">
						<condition attribute="projectid" value="{AB6FCB73-6F1C-E111-B786-005056BC4F66}" operator="eq" uitype="project" uiname="Test 4"/>
					</filter>
				</link-entity>
			</link-entity>
		</link-entity>
	</entity>
</fetch>

Regarding the specifics of actually creating the custom view, it’s basically just building the FetchXML string, getting the Lookup control, then using “addCustomView” to set it, but all of that is covered in a great article at Create a custom filtered lookup view in runtime – CRM 2011.

This entry was posted on Friday, December 9th, 2011 at %I:%M %p and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

There’s a change we’re making to one of our projects that relies on a couple new XML attributes, however, we already have data in our development system that we want to keep. While the XML would be updated when the project saves it, nobody wants to manually open and click through the 2000+ forms we’ve generated over the past couple years, so I ended up creating a sproc that would do it for us.

My first thought was that I could simply cast the value from the XML column as a VARCHAR(MAX), fire off a couple global REPLACE commands, and be done with it, but things are never that easy. While one of the attributes we were adding could be inserted with the same value for all nodes, the other holds the ID of the tab the control the node represents is rendered on. This means I needed to figure out a way to select individual nodes and insert the attribute with the correct value, which means REPLACE was out of the question.

I settled on loading a cursor with the node ID’s and their corresponding tab ID’s, then using XQuery to modify individual nodes with the correct values. It’s not the quickest solution, but it did exactly what I needed; I could pick out a specific node, check if the attribute already existed, then add/modify as needed:

	DECLARE @xml XML
	DECLARE @Query VARCHAR(MAX)
	DECLARE @FVId INT
	DECLARE @FFId INT
	DECLARE @FVSId INT

	SELECT	 @xml = FIData
			,@FVId= FVId
	FROM FI
	WHERE FFId = @FFId

	DECLARE fvff_cursor CURSOR FOR
	SELECT fvff.FFId, fvff.FVSId
	FROM fvff
	INNER JOIN fvs ON fvs.FVSId = fvff.FVSId
	INNER JOIN fvt ON fvt.FVTId = fvs.FVTId
	WHERE fvt.FVId= @FVId
	AND fvff.Active = 1

	OPEN fvff_cursor

	FETCH NEXT FROM fvff_cursor
	INTO @FFId, @FVSId

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @xml.exist('/formData/FFValues/FF[@FFId=xs:string(sql:variable("@FFId")) and @FVSId]') = 0
		BEGIN
			SET @xml.modify('insert attribute FVSId{ xs:string(sql:variable("@FVSId")) } into (//formData/FFValues/FF[@FFId=xs:string(sql:variable("@FFId"))])[1]')
		END

		IF @xml.exist('/formData/FFValues/FF[@FFId=xs:string(sql:variable("@FFId")) and @IsEnabled]') = 0
		BEGIN
			SET @xml.modify('insert attribute IsEnabled{ "true" } into (//formData/FFValues/FF[@FFId=xs:string(sql:variable("@FFId"))])[1]')
		END
		ELSE
		BEGIN
			SET @xml.modify('replace value of (//formData/FFValues/FF[@FFId=xs:string(sql:variable("@FFId"))]/@IsEnabled[.="false"])[1] with "true"')
		END

		FETCH NEXT FROM fvff_cursor
		INTO @FFId, @FVSId
	END

	CLOSE fvff_cursor
	DEALLOCATE fvff_cursor

	UPDATE FI
	SET FIData = @xml
	WHERE FIId = @FIId

This entry was posted on Friday, September 30th, 2011 at %I:%M %p and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Create the Silverlight Application

If you’ve read my two previous posts on the subject, (Part 1 and Part 2) you will know that I promised to do a part 3 to this series that will show how to wire up what we did in creating our N-Tier solution using WCF and Entity Framework to an actual application. In this case, we will create a Silverlight application that can consume our WCF services and display them on a page. This will not be as long as the other two posts, but it should give you a general idea of how this is to be done.

The first order of business is to create a new Silverlight application. In this case, we are going to add it to the solution that we created in the previous posts. It is called “BurnNotice”. Once again, if you do not know what Burn Notice is, my heard bleeds for you … check it out here … but it won’t affect your understanding of the example. To do this, right click on the solution itself and choose the option “Add > New Project” as shown in the screenshot.

The “Add New Project” dialog box will appear and you can select any kind of Silverlight application that you would like, but I generally like to choose the “Silverlight Navigation Application” as it comes pre-wired with some pages and navigation controls. You can make your own by adding additional controls in the same manner.

After selecting the type of Silverlight application that you want, you will be presented with the “New Silverlight Application” dialog. This will allow you to host the Silverlight application inside of a web application that Visual Studio will create for you. Assuming that you have not already added a web application to your solution, you can accept most of the default values. Of course you can name it whatever you want. My options were as follows:

After you have clicked the “OK” button, Visual Studio will add two new projects to your solution. The first of the two will be the Silverlight application from which you will connect to WCF Services created previously. The second will be the web application to host your Silverlight application so that it can be viewed in a browser. For the purposes of debugging, you will want to have the web application as the default project. If you selected the “Silverlight Navigation Project”, then you will also see a basic UI setup in the Design pane as shown in the following screenshot.

The web application project will have two pages designed to host your Silverlight application, one a web form and the other a standard html page like so:

The names of these two pages will be abnormally long as they include the name of the project along with “TestPage” added to the end. You don’t need to change them, but if you are going to be typing the address into a browser, it makes sense to change the name of the page to something more standard. I personally got rid of the html page altogether and then renamed the web form to “default.aspx” like this:

Add a Reference to the WCF Service

At this point we are almost home free. The last step to actually use the service that we created previously is to add a reference to the Silverlight project. In this case, instead of adding a standard reference, though, we want to add a reference to the service itself. To do this, right click on the project name and select “Add Service Reference…” as shown below:

This will bring up the “Add Service Reference” dialog box. In the Address box, enter the local URL of the service that you created, unless, of course, you have already deployed it somewhere in which case you can reference it there. After entering the URL, click on the “Go” button and any services discovered at the URL will be listed in the “Operations” box on the right. If none appear then either the URL you entered is incorrect or the service itself is not functioning properly as shown in the screenshot. You will also need to enter the Namespace which you want the reference to belong to. This can also be called whatever you want, but the default will have the word “service” in it so that later, while programming, you will know at a glance that any of the methods inside of it are from a service as opposed to another local project.

After adding the service, you will now see a new folder in your project called “Service References” and below it you will find the service that you have just created. It will be named whatever you called the namespace in the “Add Service Reference” dialog. It will look like so:

Call the service to populate a Silverlight control

At this point, the only task left is to call the service and use the data returned to populate a control. To do this, open one of the .xaml pages that were created when the Silverlight application was created. Since this is only an example it does not particularly matter which one. In my case, I will do it on the “Home.xaml” page. Once the page is opened in the design view, open the “Toolbox” on the left side of the page as shown in the screenshot. Under the sub heading “Common Silverlight Controls” you will see one called “DataGrid” (not to be confused with the “Grid” control which is used for layout purposes). Click on the DataGrid icon and drag it onto the designer surface.

This should end up giving you some code that looks basically like this:

<navigation:Page x:Class="BurnNotice.UI.Home"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:navigation="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation"
    mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480"
    Title="Home"
    Style="{StaticResource PageStyle}" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">

    <Grid x:Name="LayoutRoot">
        <ScrollViewer x:Name="PageScrollViewer" Style="{StaticResource PageScrollViewerStyle}">
            <StackPanel x:Name="ContentStackPanel">
                <TextBlock x:Name="HeaderText" Style="{StaticResource HeaderTextStyle}" Text="Home"/>
                <TextBlock x:Name="ContentText" Style="{StaticResource ContentTextStyle}" Text="Home page content"/>
                <sdk:DataGrid AutoGenerateColumns=True" Name="dgSpies" />
            </StackPanel>
        </ScrollViewer>
    </Grid>

</navigation:Page>

Right now, I am not going to go into a tutorial on .xaml files or how to acutally create and lay out a page using silverlight, but I do want to show how easy it now is to call the actual service and populate the grid. You will notice that I am just letting Silverlight autopopulate everything for the sake of simplicity. Obviously all of that can be customized. So, in the code behind to the .xaml page, all you need to do is set up some code in the “OnNavigatedTo” event that will call the service. All calls to a service like this are done asynchronously so once the call is done, you also need to set up a delegate that will be invoked when the results from the service are returned. This is very easy to do and the events are built into the service that we have already created. The code to do this looks like so:

namespace BurnNotice.UI
{
    public partial class Home : Page
    {
        public Home()
        {
            InitializeComponent();
        }

        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            BurnNoticeServiceClient client = new BurnNoticeServiceClient();
            client.Characters_GetAllCompleted += new EventHandler<Characters_GetAllCompletedEventArgs>(Characters_GetAll_Completed);
            client.Characters_GetAllAsync();
        }

        private void Characters_GetAll_Completed(object sender, Characters_GetAllCompletedEventArgs e)
        {
            dgSpies.ItemsSource = e.Result;
        }
    }
}

Depending on the speed, you might notice a bit of a lag time between initially calling the service and having the grid populated, but after it is called and the delegate executed … Voila, you have a populated datagrid on a silverlight page that looks like this (notice that Tricia Helfer is mentioned in the grid … Ahhh Tricia).

This entry was posted on Friday, September 30th, 2011 at %I:%M %p and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

While working on a project recently, I ended up needing to call a “Table-valued Function” in SQL Server. Had I been writing my process from scratch, I would probably not have chosen to use a Table-valued function, but I was asked to tap into previously existing code that was, so I didn’t have much choice. Initially, I didn’t think that this would be a problem, however, when I started trying to actually call a Table-valued Function like you would a stored procedure or a Scalar-valued Function, I ended up getting the following error:

This error is actually pretty self-explanatory. You can’t call a Table-valued function from ASP.NET. The problem was … I needed to call one. I tried all kinds of things. My last resort was going to be to create a new stored procedure (which I CAN call from ASP.NET) whose only function is to turn around and call the Table-valued function. This did not seem to be a very effective solution to me, if for no other reason than that it would require editing the new stored procedure every time the Table-value function changed. Those kinds of things are easy to get out of sync.

I did search for this error on line and I didn’t find much in the way of solutions. I finally found something that worked though and I thought that I would write a quick blog post about it. In essence, Table-valued Functions are called in the same way that one would run a query against a table. In other words, you use the function as if it were a table. So, if we want to select all the episodes from the BurnNotice Episodes table from season 2 where the rating is 5 stars, then our query would look like this:

SELECT * FROM episodes WHERE rating = 5 AND season = 2

NOTE: This would return a list of all episodes from season 2 as they are all awesome

However, if the rating of a given episode were an aggregate of a variety of factors and happened to include several values from different places, we could write a Table-valued Function called “EpisodeRatings” accepts two parameters (@Rating and @Season) that returns all the values that we need. Rather than an EXEC statement which we would use for a Scalar-valued Function, our call would look like this:

SELECT * FROM episoderatings(5, 2)

Knowing this difference is actually the key to solving the error that is the subject of this post. Rather than try to call the Table-valued Function as if it were a stored procedure, we will build a parameterized text string in our C# code. Using a parameterized string allows you to dynamically build the SQL Statement in your C# code, but it also uses strongly typed input parameters which would thwart attempts at SQL Injection attacks. I don’t think that this is the ideal solution, but for one of those time in which the project’s requirements or prior design constrain your options, it certainly works. So, below I have an example of some code that will build the SQL String, add in the parameters and then execute the Table-valued Function and return a data table.

public static DataTable ExecuteTableValueFunction(string database, List<SprocParameter> parameterList)
{

	StringBuilder sb = new StringBuilder();

	// Build the parameterized sql string
	sb.Append("SELECT * ");
	sb.Append("FROM episoderatings(");
	sb.Append("@Rating");
	sb.Append(",@Season");
	sb.Append(")");

	DataTable dt = new DataTable();
	Database db = DatabaseFactory.CreateDatabase("BurnNotice");
	DbCommand cmd = db.GetSqlStringCommand(sb.ToString());

	cmd.CommandType = CommandType.Text;
	cmd.CommandTimeout = timeout;

	foreach (SprocParameter param in parameterList) {
		if (param.Value == null) {
			db.AddInParameter(cmd, param.Name, param.DataType, DBNull.Value);
		} else {
			db.AddInParameter(cmd, param.Name, param.DataType, param.Value);
		}
	}

	db.CreateConnection().Open();
	dt.Load(db.ExecuteReader(cmd));
	db.CreateConnection().Close();

	return dt;
}

So, to conclude … I must say that I did not love solving this issue in this manner. I don’t like building SQL in my code using strings, still it is parameterized and will guard against SQL Injection attacks. In the end, I thought that it was a better option that creating extra procedures simply to call the table valued function. If you are having this error and have no other choice than to barrel forward using a table valued function, I hope that this post helps you solve the problem.

This entry was posted on Tuesday, August 9th, 2011 at %I:%M %p and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.