To start, let’s have a look at our desired gridview filtering controls, starting with the AjaxControlToolkit AutoCompleteExtender deep-text search textBox discussed in this post:

This is a gridview with search and filtering controls in the header

As discussed, the search textbox searches multiple fields in our entity to help the user find the desired record:

Looking for all trips in October

Ordinarily, the problem you encounter when trying to put search/filter controls in a HeaderTemplate is that you can’t set your DataSource’s whereParameter ControlID to a control on a gridview, because they’re not in the same scope (NamingContainer), you’ll get an error like this:

System.InvalidOperationException: Could not find control ‘yourControlID’ in ControlParameter ‘yourParam’

If you’ve ever done a view source on an aspx page, you’ll notice that a control you made like this in Visual Studio:

<asp:HiddenField ID="txtTripId_hidden" runat="server" />

Looks something like this after it’s rendered in the browser:

<input type="hidden" id="ctl00__yourForm_txtTripId_hidden" />

Basically, any asp.net control that’s nested inside a NamingContainer (gridviewrow, content, etc) is going to have a unique ID generated at runtime.  That’s why you’re able to set the id of multiple textboxes to the same value if they’re on different templates of a gridviewrow.  If your hiddenfield is in a different container than your datasource, the datasource can’t “see” your hiddenfield.

We’re going to discuss two different approaches to this problem, and both build on what I outlined in the post on retrieving key/value pairs from an AutoCompleteItem.

Method 1 – Putting a hiddenField on your form and using the EntityDataSource’s WhereParameter:

Note the javascript in that post, because we’re going to be adding to it for this method.  I’m not going to re-type it all here, but you can stick the following snippet into the “autoCompleteItemSelected” method and be on your way:

//the underscore is a token in the id of the rendered control:
var splitSourceId = source.get_id().split('_');

if(splitSourceId.length > 2) {
     //this is just based on the namingConventions,
     //    try alert(splitSourceId) to see why we do this:
     var strippedSourceId = splitSourceId[0] + '__' + splitSourceId[2] + '_' + _
           splitSourceId[splitSourceId.length-1] + '_hidden';

     //try to find the hiddenField that's outside the gridview:
     var outerHidden = document.getElementById(strippedSourceId);
     if(outerHidden)
          outerHidden.value = eventArgs.get_value();
}

The nice thing about this is, you add it to your shared javascript function, and it doesn’t interfere with anything. I was using the function on 3 other pages before I added this, and it’s inconsequential if you’re not trying to use it.

So what we’ve just done there is set a root-level control (hiddenField)’s value to our Id that we want to filter on. Now we can reference the hiddenField in our EntityDataSource and be good to go! For this trip gridview, our dataSource will look like so:

<asp:EntityDataSourceID="entTripSource" runat="server"
 ConnectionString="name=BusLineEntities"
 DefaultContainerName="BusLineEntities"
 EntitySetName="ExecutiveTrips"
 AutoGenerateWhereClause="true">
 <WhereParameters>
 <asp:ControlParameter ControlID="txtTripSearch_hidden"
Type="Int32" Name="Id" />
 </WhereParameters>
</asp:EntityDataSource>

You might notice it’s not posting back like you’d expect. You need to cause the postback at some point, which I’ve done by using the TextChanged event of our input textbox (this makes it immediate when the user selects an item from the autoComplete dropdown, no “apply filter” button is needed).

<asp:TextBox ID="txtPassenger_input" runat="server"
        AutoPostBack="true" OnTextChanged="someMethod" Width="200px" />

That works very well for when you only want to search. In some cases, though, you want even more functionality, like filtering on a range of values, that’s where method 2 comes in.

Method 2:

Coming soon…

In this example, we’re going to be searching our Bus Line database for executive travel.  Our imaginary sub-system tracks business trips made by the CEO, CIO, CTO, and CFO.

If we have a gridview with all the business trips for a given date range, there could be many pages to look through to find the trip you’re looking for.  It’s only nice to allow our users to search the gridview based on any number of criteria.

For example, with a single textbox, we can allow the user to search by trip name, the person’s name, the person’s title, trip start date, trip destination, airline, etc.

Let’s look first at our sample executive travel entity and a gridview to display our trips (obviously this is not normalized, etc…):

An example of our Trip entity

Our trips span 3 pages

So you know what the code is doing when you get to that point, I’ll go ahead and post all of the AutoCompleteExtender dropdown screenshots now.  Keep in mind this is one textbox wired to one AutoCompleteExtender calling one method of one webservice.  There are no tricky code-arounds or messy overloads.

The idea is that the user can type in anything they know about a trip…who’s on it, when it is, where it’s going, etc, and then choose the desired trip from the dropdown.

Here we're searching on trip number

Searching by person title (returning all where title == CEO)

Searching where Airline like("Airtran Airways")

Where State like("Nevad%")

In this one, our CEO is named Steve Jobs, and our CIO is named Steve Wozniak

Looking for all trips in October (remember the date thing, we'll come back to that later)

…and so on.  I took more screenshots, but by now it should be obvious that we can search an entitySet on any number of fields (essentially a deep-text search), and return the corresponding Id (PK) of the entity.  If you recall my previous post on returning key/value pairs from an AutoCompleteItem, we can gloss over that part of it and just strip the web service down to the functionality this article addresses:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetTripList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* trips from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> TripList = new List<string>();

    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<ExecutiveTrip> tripsQuery =
              from t in myEnts.ExecutiveTrips
                    where t.TripName.Contains(prefixText)
                              || t.PersonName.Contains(prefixText)
                              || t.PersonTitle.Contains(prefixText)
                              || t.DestinationCity.Contains(prefixText)
                              || t.DestinationState.Contains(prefixText)
                              || t.Airline.Contains(prefixText)
                    orderby t.TripName, t.StartDate
                    select t;

      //add the values to the list and include id
      foreach (ExecutiveTrip t in tripsQuery) {
              TripList.Add(AjaxControlToolkit.AutoCompleteExtender. _
                CreateAutoCompleteItem(t.TripName, t.Id.ToString()));
         }
   }
return TripList.ToArray();
}//end GetTripList

As you can see above, all we really need to do is chain together as many logical OR || operators and statements. That should be pretty plain, so I’ll skip ahead to a situation that can be slightly confusing…

Recall above we wanted to search by date in our web service.  In this particular case, the date is in our trip name, but you might not always be so lucky.  Assuming your date field is a System.DateTime datatype, it seems logical to try something along the lines of:

where Convert.ToDateTime(t.StartDate).ToLongDateString().Contains(prefixText)

Unfortunately, that won’t work.  You’re not able to do the conversion inside of your query.  While there are probably better ways to do this, my approach was fairly simple and can be used on any datatype.  Where required, I make a SQL view that has all of the fields I’m going to want to search on, and I make all but the primary key varchar fields (either by cast or convert).  This allows us to do a string search on DateTimes, Int32s, etc.  In case you need the conversion, a SQL DateTime or SmallDateTime value can be converted to a string in your view’s SELECT statement like so:

convert(varchar, getdate(), 100)
       output: Sep 11 2009 9:56PM

convert(varchar, getdate(), 101)
       output: 9/11/2009

convert(varchar, getdate(), 102)
       output: 2009.09.11

convert(varchar, getdate(), 106)
       output: 11 Sep 2009

convert(varchar, getdate(), 107)
       output: Sep 11, 2009

Which one you choose really just depends on how you want it to be searched (I’d go with 107, personally).

That’s all for now, my next post will explain the two different ways you can use the return value to databind your EntityDataSource on the trip Id.

In this post, we’re going to look at modifying the AjaxControlToolkit to allow you to format the displayed items in an AutoCompleteExtender dropdown with html.

I first found out about this from Rob White, aka ILIVEWITHIAN on The Old Sewing Factory.  Credit goes to him, this is just an example of how I did it in C#.

As discussed, you need to get the source for the toolkit here.  Make sure you get the source and not just the precompiled dll, we’re going to be extending it.

The first change will be adding the following method overload to your AutoCompleteExtender.cs file:

public static string CreateAutoCompleteItem _
                        (string text, string value, string markup)
{
      return new JavaScriptSerializer().Serialize _
                                    (new Pair(text, new Pair(value, markup)));
}

For the sake of keeping this simple, I won’t post the whole function that goes in AutoCompleteBehavior.js, but you can read about it at Rob’s blog, or go here to download the file (link taken from Rob White at The Old Sewing Factory).

Once you’ve got the toolkit extended, the fun begins.  In this example, our bus line company wants to add/edit passengers.  Because employees of the bus line are already in our personnel database, we don’t want to duplicate information in our passengers table.  All the same, we don’t want just anyone to be able to edit employee personnel records.  At first, it makes sense to just exclude employees in our web service, but then how would a user know if the person they’re typing in already exists?  The answer: format employees in the dropdown!  We’ll be showing all employees in red, and regular passengers/customers in black.

We’ll start by querying our entity set just like in the previous post on this topic:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetPassengerList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* passengers from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> Passengers = new List<string>();
    //query the passenger entity:
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<Passenger> passengersQuery =
              from p in myEnts.Passengers
                    where p.DisplayName.Contains(prefixText)
                    orderby p.LastName
                    select p;
      string theHTML; //this will be what the user sees

      //add the values to the list and include id
      foreach (Passenger p in passengersQuery) {
         if(p.IsEmployee == false) { //person is an external customer
              Passengers.Add(AjaxControlToolkit.AutoCompleteExtender. _
                CreateAutoCompleteItem(p.DisplayName, p.Id.ToString()));
                 }
         else { //person is an employee, display in red
             theHTML = "<span style='color: red;'>" + p.DisplayName + "</span>";
             Passengers.Add(AjaxControlToolkit.AutoCompleteExtender. _
               CreateAutoCompleteItem(p.DisplayName, p.Id.ToString(), theHTML));
                }
         }
   }
return Passengers.ToArray();
}//end GetPassengerList

When it’s all done, you’ll get output like this:

If you really want to get fancy, you can do something like Rob did and display the employee’s photo, an icon, etc., the possibilities are endless. I found this simple span to display employees in red was exactly what my client needed to see if the person they were typing in existed in *one* of their tables, while being able to distinguish between external and internal customers.

Next up, if the name they typed in wasn’t in the list, we’ll give them a one-click insert. Also, allowing user to select an employee (red) to view their info, but disable edit, is a trivial bit of code on top of this.

In this article, we’re going to be using an entityDataSource tied to a detailsView to insert a new “person” entity into our database.  When the new entity is inserted, we want to use the new server-generated PK (identity) on our form, so we’ll be sending it back from the stored proc.

First things first, let’s look at our sample entity. This is a person in our Bus Line’s passenger database:

Simple person entity

To insert a new entity from our detailsView, we want to map an Insert stored proc, which looks like this:

CREATE PROCEDURE SP_BUSLINE_PERSON_INSERT
  @LAST_NAME varchar(25),
  @FIRST_NAME varchar(25),
  @DISPLAY_NAME varchar(65)
AS
BEGIN
  DECLARE @NEW_IDENTITY int;

  INSERT INTO BUSLINE_PERSONNEL(LAST_NAME, FIRST_NAME, DISPLAY_NAME)
  VALUES (@LAST_NAME, @FIRST_NAME, @DISPLAY_NAME)

  SET @NEW_IDENTITY = @@identity;
  SELECT @NEW_IDENTITY "PERSON_ID"
END

Note that we’re not setting an “OUTPUT” parameter, but rather SELECTing a value.

Now that our SP is created, we need to map the insert function on our entity:

Insert Function Mapping for our Person entity

So, now that our insert is all set up (we’re going to ignore the fact that you’re currently required to map stored procs for all functions if you map for one), let’s take a look at the entity data source that feeds our detailsView:

<asp:EntityDataSourceID="entPersonSource" runat="server"
 ConnectionString="name=BusLineEntities"
 DefaultContainerName="BusLineEntities"
 EntitySetName="People" EnableInsert="True"
OnInserted="eps_Inserted"
 Where="it.Id = @PERSON_ID)">
 <WhereParameters>
 <asp:ControlParameter ControlID="txtPerson_hidden"
Type="Int32" Name="PERSON_ID" />
 </WhereParameters>
</asp:EntityDataSource>

(Note you could have also set AutoGenerateWhereClause=”True” and renamed your whereParameter to “Id”)

For the record, we’re using a hiddenField as our ParameterSource because the user is typing into an autoComplete textbox that searches our People entitySet. If the name is found, the user selects it and can edit the details. If the name isn’t found, the name is put on our Details View for the user and they can choose to insert the new record. Obviously, there won’t be a person_id for the datasource to bind to before the entity is inserted, hence our needing the return value to databind our detailsView after insert.

So now let’s have a look at this detailsView (simplified):

<asp:DetailsView ID="dvPerson" runat="server"
 DataSourceID="entPersonSource" DataKeyNames="Id">
<Fields>
<asp:TemplateField HeaderText="Display Name: ">
   <InsertItemTemplate>
     <asp:TextBox ID="txtDName" runat="server" Text='<%# Bind("DisplayName") %>' />
   </InsertItemTemplate>
   <ItemTemplate>
     <asp:Label ID="lblDName" runat="server" Text='<%# Eval("DisplayName") %>' />
   </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name: ">
   <InsertItemTemplate>
       <asp:TextBox ID="txtLName" runat="server" Text='<%# Bind("LastName") %>' />
   </InsertItemTemplate>
   <ItemTemplate>
       <asp:Label ID="lblLName" runat="server" Text='<%# Eval("LastName") %>' />
   </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="First Name: ">
   <InsertItemTemplate>
     <asp:TextBox ID="txtFName" runat="server" Text='<%# Bind("FirstName") %>' />
   </InsertItemTemplate>
   <ItemTemplate>
     <asp:Label ID="lblFName" runat="server" Text='<%# Eval("FirstName") %>' />
   </ItemTemplate>
</asp:TemplateField>
   <asp:TemplateField ShowHeader="False">
   <InsertItemTemplate>
     <asp:ImageButton ID="btnInsert" CommandName="Insert"
        runat="server" ImageUrl="add.png" />
   </InsertItemTemplate>
   <ItemTemplate>
      <asp:ImageButton ID="btnEdit" CommandName="Edit"
        CommandArgument='<%# Eval("Id") %>' runat="server" ImageUrl="edit.png" />
   </ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView> 

Now, since we’re supposed to be binding the detailsView to the entityDataSource where person_id = the person_id in the hidden field, the details view will disappear after an insert, which is why we put the following in our code-behind:

protected void eps_Inserted(object sender, EntityDataSourceChangedEventArgs e)
        {
            Person p = (Person)e.Entity;
            txtPerson_hidden.Value = p.Id.ToString();
        }

You can now insert an entity using a stored proc and immediately use the return value. And notice that we didn’t have to go to the code-behind for our insert command on the detailsView (the same is true for update and delete, you just have to set your databindings correctly in the markup).

In my last post, I showed you how to use javascript and asp hiddenField controls to save values selected in an AJAX AutoCompleteExtender.

In this article, we’ll take a closer look at the actual web service being called. The “GetPassengerList” method queries our Passengers entity set (ado.net Entity Framework) to return autocomplete options as a user types a passenger name into the textbox.

The code is pretty straightforward, and presented here:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetPassengerList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* passengers from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> Passengers = new List<string>();
    //query the passenger entity:
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<Passenger> passengersQuery =
              from p in myEnts.Passengers
                    where p.DisplayName.Contains(prefixText)
                    orderby p.LastName
                    select p;
      //add the values to the list and include id
      foreach (Passenger p in passengersQuery)
         Passengers.Add(AjaxControlToolkit.AutoCompleteExtender. _
                     CreateAutoCompleteItem(p.DisplayName, p.Id.ToString()));
     }
      return Passengers.ToArray();
}//end GetPassengerList

Note that we’re creating objects of type “AutoCompleteItem,” which enables us to have the dataText and an associated dataValue, much like we were using a databound dropDownList.

Now, if we had information we wanted to display in our autocomplete in addition to the person’s DisplayName, we could simply include the navigation path in our query and then append the desired value to our string:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetPassengerList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* passengers from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> Passengers = new List<string>();
    //query the passenger entity:
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<Passenger> passengersQuery =
              from p in myEnts.Passengers.Include("States")
                    where p.DisplayName.Contains(prefixText)
                    orderby p.LastName
                    select p;
      //add the values to the list and include id
      foreach (Passenger p in passengersQuery)
         Passengers.Add(AjaxControlToolkit.AutoCompleteExtender. _
                      CreateAutoCompleteItem(p.DisplayName + _
                               " (" + p.States.Abbrev + ")", p.Id.ToString()));
     }
      return Passengers.ToArray();
}//end GetPassengerList

And that’s it. The last sample assumes a NavigationPath of “States” leads to a State entity, and we want to display the user’s home state abbreviation next to their name in our autoCompleteExtender.

In this post, we’ll look at using the AJAX Control Toolkit’s AutoCompleteExtender, coupled with a very simple javacsript call, to save keys/values to a hidden field on your form.  The best part of this is that the javascript function can be used for any type of value (for example, person_id, course_id, etc) you’ll need to refer to during data manipulation.

To start, let’s look at the webservice we’ll be using for our AutoCompleteExtender.  In this example, we want a textbox that will allow the user to start typing and it will auto-complete with passengers that have ridden our imaginary bus line.  The service, PassengerLookup.asmx, will look like this:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetPassengerList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* passengers from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> Passengers = new List<string>();
    //query the passenger entity:
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<Passenger> passengersQuery =
              from p in myEnts.Passengers
                    where p.DisplayName.Contains(prefixText)
                    orderby p.LastName
                    select p;
      //add the values to the list and include id
      foreach (Passenger p in passengersQuery)
         Passengers.Add(AjaxControlToolkit.AutoCompleteExtender. _
                     CreateAutoCompleteItem(p.DisplayName, p.Id.ToString()));
     }
      return Passengers.ToArray();
}//end GetPassengerList

Notice that we’re returning an AutoCompleteItem here, and not just an array of strings…that’s how we’ll access our value (in this case, the person.Id). I’ll write a more in-depth article on that soon.

Okay, so now that our webservice is good to go, let’s have a look at the aspx controls no our page.  For each extended textbox, we’ll want to have three controls (plus our watermark, optionally), and pay close attention to the naming conventions:

<asp:TextBox ID="txtPassenger_input" runat="server" Width="200px" />

<cc2:TextBoxWatermarkExtender ID="txtPassenger_TextBoxWatermarkExtender"
 runat="server" TargetControlID="txtPassenger_input"
 WatermarkCssClass="watermarkTextBox" WatermarkText="Last, First M.">
</cc2:TextBoxWatermarkExtender>

<cc2:AutoCompleteExtender ID="txtPassenger" runat="server"
 TargetControlID="txtPassenger_input" ServiceMethod="GetPassengerList"
 ServicePath="~/Services/PassengerLookup.asmx"
 MinimumPrefixLength="3" CompletionSetCount="20"
 CompletionInterval="100" CompletionListCssClass="PassengerAutoCompleteFlyout"
 CompletionListItemCssClass="PassengerAutoCompleteItem"
 CompletionListHighlightedItemCssClass="PassengerAutoCompleteHighlightItem"
 OnClientItemSelected="autoCompleteItemSelected">
</cc2:AutoCompleteExtender>

<asp:HiddenField ID="txtPassenger_hidden" runat="server" />

Note that the IDs of our controls are all based on the ID of the AutoCompleteExtender. The reason for that will become apparent when you look at the javascript call:

function autoCompleteItemSelected(source, eventArgs) {
 /******************************************************************
 * this function will save the value (personID, etc) of the item
 *    selected in an autcomplete to an associated hidden field
 *******************************************************************/

 /*  Naming conventions: (required for this to work)
 the textBox being extended:     ID="txtVar_input"
 the autocompleteExtender:       ID="txtVar"
 the hidden field to save to:    ID="txtVar_hidden"

 //get the hidden field associated with this autoCompleteExtender:*/
 var assocHiddenField = document.getElementById(source.get_id() + '_hidden');

 //set the value of the hidden field to the pair's value (personID, etc):
 assocHiddenField.value = eventArgs.get_value();

} //end function autoCompleteItemSelected()

And that’s all there is to it! The javascript function can be put in the head of your master page and re-used anywhere you want to retrieve values. To make it even more functional, we can include our extended textbox controls in a data control (gridview, repeater, etc), and bind with the following code:

<asp:HiddenField runat="server" ID="txt_hidden" Value='<%# Bind("Person.Id") %>' />

Now the gridview update will work on your entityDataSource with no additional code!

In my last post, I showed you how to use an entity datasource to query many-to-many relationships and display them on a gridview.

Now, we’ll go over the same scenario, but we’ll be using our gridviews to insert/delete records in our bridge table.

For review, we’re dealing with a database structure that looks like this:

This is how a many to many looks in your database, but not in your edmx

Conceptually, it looks like the below (the bridge table is represented as a many-to-many ( *:* ) relationship between our two entities:

This is how a many-to-many relationship looks in your data model

And once we’ve bound our gridviews (as in the last article), we should be looking at something like this:

the menu and the pantry

I’m going to assume a general understanding of gridview rowcommand events, but if anyone needs further explanation, feel free to ask.

The first action we’ll look at is the insert.  As you can see in the screenshot above, the user can click the arrow on the pantry to add a given item to the current menu.  The code will look like this for an insert:

protected void gvPantry_RowCommand(object sender, GridViewCommandEventArgs e)
  {
  /**************************************************************************
  * this function handles the row commands on the pantry (adds item to menu)
  * ************************************************************************/

  //"if" required to avoid raising this on ddlPantyFilter postback
  if (e.CommandName.ToString() == "AddItemToMenu")
  {
    Int32 mealID = Convert.ToInt32(((ImageButton)gvMeals.SelectedRow. _
                             FindControl("btnSelectMeal")).CommandArgument);
    Int32 pantryItemID = Convert.ToInt32(e.CommandArgument);
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      Meal thisMeal = myEnts.Meals.FirstOrDefault(m => m.Id == mealID);
      PantryItem thisPI = myEnts.PantryItems. _
                            FirstOrDefault(p => p.Id == pantryItemID);

      thisMeal.PantryItems.Add(thisPI);
      myEnts.SaveChanges();
    }
    gvFoodOnMenu.DataBind();
    gvPantry.DataBind();
  }
  } //end void gvPantry_RowCommand()

Fairly straightforward, but now let’s say you need to do a delete. It’s not much different, really, and the code will look something like this:

protected void gvFoodOnMenu_RowCommand(object sender, GridViewCommandEventArgs e)
  {
  /****************************************************************************
  * this function handles the row commands on the menu (removes item from menu)
  * **************************************************************************/

    Int32 mealID = Convert.ToInt32(((ImageButton)gvMeals.SelectedRow. _
                             FindControl("btnSelectMeal")).CommandArgument);
    Int32 pantryItemID = Convert.ToInt32(e.CommandArgument);
    using (BusLineEntities myEnts = new BusLineEntities())
    {
      Meal thisMeal = myEnts.Meals.FirstOrDefault(m => m.Id == mealID);
      PantryItem thisPI = myEnts.PantryItems. _
                            FirstOrDefault(p => p.Id == pantryItemID);

      thisMeal.PantryItems.Attach(thisPI);
      thisMeal.PantryItems.Remove(thisPI);
      myEnts.SaveChanges();
    }
    gvFoodOnMenu.DataBind();
    gvPantry.DataBind();
  } //end void gvPantry_RowCommand()

With that, in this post, I’ll show you how to use an EntityDataSource to query a many-to-many relationship (sometimes called a swing table or bridge table), for display on a gridview.

First thing’s first, you probably already noticed when importing your database to the entity model that your swing table (consisting of two foreign keys) didn’t show up. Well, it did, but not like you thought it would. Just in case you haven’t gotten that far, we’ll do a quick review…

Using an example of a system you’re creating for a bus line, let’s say you have two tables…a T_PANTRY and T_MEALS. T_PANTRY has all of you pantry items (food items that can be served to passengers), and T_MEALS has all of the meals served on your bus line. You have a third table, T_MENUS, that consists of the MEAL_ID (pk from T_MEALS), and PANTRY_ITEM_ID (pk from T_PANTRY). You might expect to see something like the following in your entity data model (edmx):

This is a many-to-many in your database, but not in your edmx

What you’ll actually see in your edmx can be a bit confusing at first, and looks like this:

This is how a many-to-many relationship looks in your data model

So, you’ve got a gridview that shows you all of the meals on a given trip, and you want to give the user the ability to select a meal. When they select a meal, they can see two smaller gridviews, one with all pantry items currently on the meal (T_MENUS), and one with all pantry items NOT currently on the meal. Your entity datasource would look something like this for the menu:

(note that you need to have dataKeyNames set up properly on your “MealsOnTrip” gridview for the where parameter to work when a meal is selected.)

Now, for your pantry gridview, you only want to show pantry items that aren’t currently on the menu.  To make things even smoother, we’ve added a dropdownlist to filter the pantry by mealtype.  The entitydatasource looks like so:

<asp:EntityDataSourceID="PantryDataSource" runat="server"
 ConnectionString="name=BusLineEntities"
 DefaultContainerName="BusLineEntities"
 EntitySetName ="PantryItems"
 Where="NOT EXISTS(SELECT VALUE m FROM it.Meals AS m WHERE m.ID = @MealId)
        AND it.MealType LIKE(@MealType)">
 <WhereParameters>
 <asp:ControlParameter ControlID="MealsOnTripGridView"
Type="Int32" Name="MealId" />
<asp:ControlParameter ControlID="ddlPantryFilter"
Type="String" Name="MealType" Property="SelectedValue" />
 </WhereParameters>
</asp:EntityDataSource>

So you have an idea what we’re talking about, here…when a meal is selected, the menu and pantry are visible, something like this:

the menu and the pantry

Okay, so now you know how to query a many-to-many relationship using an entityDataSource for display on a gridview.  The next article will show you how to insert/delete, using the same example we’re working with here.

If you have any questions or suggestions, feel free to leave a comment.

Normal GAC View

On the machine where the desired assembly is located, you can change the windows assembly to directory view by running the following command from command line:

Unregister SHFusion.dll

GAC in Directory View

You can now view the assembly folder like any other directory, just copy the desired DLL to another location and then re-set the view by running the following command:

Re-registering SHFusion.dll

Now, to install a DLL in the GAC on another machine, locate the gacutil.exe (should be in the location below, if not, change the command as needed) and run the following commands:

for example, gacutil.exe /if c:\dllCopyDir\testDLL.dll

the “/if” switch overwrites an existing DLL (if you wish, you can also use “/i” to only install if not existing)