DynamicData Many to Many FieldTemplate

• Download source - 2.34 KB

Introduction

In Dynamic Data, when you have a database with an n-to-n or many-to-many relationship like this...

... the default DynamicData setup will be three table edits, but wouldn't it be easier if in the Table1 edit, you get a multiple select input like so:

The attached zip file gives you that multiple select option for DynamicData items.

Using the Code

I've only implemented it for the manytomany table without extra fields (so only the two foreign keys themselves) and these have to be of type int in my example.

The dynamic data setup does a lot for you but the setup for many to many relations isn't really useful (the extra table instead of editing it in the correct table). So if you add the following UIHint attribute to your manytomany column...

[UIHint("MultipleSelect")]

... and put the multipleselect_edit source in the FieldTemplates folder, you get the MultipleSelect listbox for your many to many relations.

What I did was rely a bit heavily on reflection. To populate the listbox, I use the OnDataBinding and OnPreRender events.

protected override void OnDataBinding( EventArgs e )
{
	base.OnDataBinding(e);

	//find the foreignkeycolumn not pointing to the current table 
         //so the foreignforeign table			
	MetaForeignKeyColumn mfk = (MetaForeignKeyColumn)
		ChildrenColumn.ChildTable.Columns.Single<MetaColumn>
		(c => c is MetaForeignKeyColumn && c.Name != 
		this.Column.Table.EntityType.Name);
	if (mfk != null)
	{
		LinqDataSource lds = new LinqDataSource();
		lds.ContextTypeName = 
			ChildrenColumn.ChildTable.DataContextType.FullName;
		lds.TableName = mfk.ParentTable.Name;

		_ddlItems.DataSource = lds;
		_ddlItems.DataTextField = mfk.ParentTable.DisplayColumn.Name;
		_ddlItems.DataValueField = mfk.ParentTable.PrimaryKeyColumns[0].Name;
		_ddlItems.DataBind();

		//when we set the ListItem.Selected to true here it doesn't work, 
		//so it is deferred to the 
		//prerender and temporarily stored in a string list
		selectedValues.Clear();
		if (!IsPostBack)
		{
			IList a = FieldValue as IList;
			foreach (var b in a)
			{
				object val = ( (PropertyInfo)b.GetType().GetProperty
				(mfk.ForeignKeyNames[0]) ).GetValue(b, null);
				selectedValues.Add(val.ToString());
			}
		}
	}
}

protected override void OnPreRender( EventArgs e )
{
	foreach (string s in selectedValues)
	{
		if (_ddlItems.Items.FindByValue(s) != null)
		{
			_ddlItems.Items.FindByValue(s).Selected = true;
		}
	}
	base.OnPreRender(e);
}

For some reason, setting the Selected = true in the OnDataBinding will not work, so I store the selectedValues in a temp list and set the selected property in the PreRender method.

To save all this, I use the ExtractValues method with an IsPostback check. Don't know if this is the correct place but it seems to work. What the save method does is remove all entities for the ID that is being edited, and then add the items that are selected in the listbox.

protected override void ExtractValues( IOrderedDictionary dictionary )
{
	if (IsPostBack)
	{
		//find the foreignkey pointing to this table
		MetaForeignKeyColumn mfkThis = (MetaForeignKeyColumn)
			ChildrenColumn.ChildTable.Columns.Single<metacolumn />
			(c => c is MetaForeignKeyColumn && c.Name == 
			this.Column.Table.EntityType.Name);
		//find the foreignkey pointing to the other table
		MetaForeignKeyColumn mfk = (MetaForeignKeyColumn)
			ChildrenColumn.ChildTable.Columns.Single<metacolumn />
			(c => c is MetaForeignKeyColumn && c.Name != 
			this.Column.Table.EntityType.Name);
		if (mfk != null)
		{
			//get a new context for the updates of the many to many table
			Type t = Type.GetType(Table.DataContextType.FullName);
			System.Data.Linq.DataContext c = 
			 Activator.CreateInstance(t) as System.Data.Linq.DataContext;

			//get the manytomany table object
			ITable o = c.GetType().GetProperty
				(ChildrenColumn.ChildTable.Name).GetValue
				(c, null) as ITable;
			if (o != null)
			{
				//the current edited id
				int currentID = Convert.ToInt32
					(Request.QueryString
					[Table.PrimaryKeyColumns[0].Name]);
				//first delete all items
				foreach (ListItem li in _ddlItems.Items)
				{
					//create the entity to delete
					object entity = Activator.CreateInstance
							(o.ElementType);
					o.ElementType.GetProperty
						(mfkThis.ForeignKeyNames[0]).
						SetValue(entity, currentID, null);
					o.ElementType.GetProperty
						(mfk.ForeignKeyNames[0]).
						SetValue(entity, Convert.ToInt32
						(li.Value), null);
					//has to be attached to be deleted
					o.Attach(entity);
					o.DeleteOnSubmit(entity);
				}
				c.SubmitChanges();
				//then add selected items
				foreach(ListItem li in _ddlItems.Items)
				{
					if (li.Selected)
					{
						//create entity to add
						object entity = 
						  Activator.CreateInstance
						  (o.ElementType);
						o.ElementType.GetProperty
						  (mfkThis.ForeignKeyNames[0]).
						  SetValue(entity, currentID, 
						  null);
						o.ElementType.GetProperty
						  (mfk.ForeignKeyNames[0]).
						  SetValue(entity, 
                                                         Convert.ToInt32(li.Value), 
						  null);
						o.InsertOnSubmit(entity);
					}
				}
				c.SubmitChanges();
			}
		}
	}
}

As you can see, lots of reflection here but that makes it reusable for other types. I also submit the deletes and inserts separately as it didn't work in one go. The ID's of the primarykeys are converted to Int32 so for now only those work. But if you have other types, you can easily make a copy for that specific type if you want. Just change the conversion.