October 2008 - Posts

Sunday, October 26, 2008 9:34 PM

TwoSelect User Control, Moving Items between ListBox Controls - Part 2

As I promised in this post I am showing you how to implement TwoSelect Control, which I created in my Previous Post

You can download the Complete Source with implementation from here

In this post to get the Available Users and Added Users I am fetching from DataBase using LINQ to SQL, but you can also use either sql or Stored Procedure as per your convenience.

So the first step in Consuming this control in our code is by Creating the Tables, whose structure will look something like this

In the User Table I have stored all the User Information, in Group Table I have stored all the Group Details and in UserGroup Table I have related the User and Groups.

In the current project I have not given any screen to enter the User Details or Group Details, as this was out of scope of the current topics, so you can insert the User and Group directly in the Database Tables.

Secondly, Now coming to .NET Code, first I have created a LINQ to SQL and named this as UserGroupData.dbml, and added all the three tables into the designer.

After this I have added the Class to Handle the Data from LINQ to SQL and pass the data to Presentation Layer.

In the below given code snippet I am Retriving all the User who is already present in the Group, this function accepts the groupId as input parameter and return IDataReader to the Presentation Layer, so that we can pass the DataReader directly to the DataSourceAdded Property of the TwoSelect Control and Bind the Data to the Added Users ListBox.

Code Snippet


1:   /// <summary>
2:          /// Gets the group user.
3:          /// </summary>
4:          /// <param name="groupId">The group id.</param>
5:          /// <returns>IDataReader</returns>
6:          public IDataReader GetGroupUser(int groupId)
7:   {
8:   UserGroupDataDataContext data = new UserGroupDataDataContext();
9:   var user = from Grp in data.Groups
10:   join UsrGrp in data.UserGroups on Grp.GroupId equals UsrGrp.GroupId
11:   join Usr in data.Users on UsrGrp.UserId equals Usr.UserId
12:   where Grp.GroupId == groupId
13:   orderby Usr.UserId
14:   select new
15:                         {
16:   UserName = Usr.UserName,
17:   GroupName = Grp.GroupName,
18:   UserId = Usr.UserId
19:   };
20:   IDbCommand command = data.GetCommand(user);
21:   command.Connection = data.Connection;
22:   if(command.Connection.State == ConnectionState.Closed)
23:   command.Connection.Open();
24:   IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
25:   return reader;
26:   }

In the below given code snippet I am Retriving all the User who not present in the Group, this function accepts the groupId as input parameter and return IDataReader to the Presentation Layer, so that we can pass the DataReader directly to the DataSourceAvailable Property of the TwoSelect Control and Bind the Data to the Available Users ListBox.

Code Snippet


1:   /// <summary>
2:          /// Users not in the selected group.
3:          /// </summary>
4:          /// <param name="groupId">The group id.</param>
5:          /// <returns>IDataReader</returns>
6:          public IDataReader UserNotInGroup(int groupId)
7:   {
8:   UserGroupDataDataContext data = new UserGroupDataDataContext();
9:   var user = from Grp in data.Groups
10:   join UsrGrp in data.UserGroups on Grp.GroupId equals UsrGrp.GroupId
11:   join Usr in data.Users on UsrGrp.UserId equals Usr.UserId
12:   where Grp.GroupId != groupId
13:   orderby Usr.UserId
14:   select new
15:                         {
16:   UserName = Usr.UserName,
17:   GroupName = Grp.GroupName,
18:   UserId = Usr.UserId
19:   };
20:   IDbCommand command = data.GetCommand(user);
21:   command.Connection = data.Connection;
22:   if(command.Connection.State == ConnectionState.Closed)
23:   command.Connection.Open();
24:   IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
25:   return reader;
26:   }

The function below will get all the Group names and Bind the Group DropDown, to display all the Groups so that we can select the Groups whose user I want to manage.

Code Snippet


1:   /// <summary>
2:          /// Gets all group.
3:          /// </summary>
4:          /// <returns>IDataReader</returns>
5:          public IDataReader GetAllGroup()
6:   {
7:   UserGroupDataDataContext data = new UserGroupDataDataContext();
8:   var groups = from Grp in data.Groups 
9:   orderby Grp.GroupName 
10:   select new 
11:   { 
12:   GroupId = Grp.GroupId, 
13:   GroupName = Grp.GroupName 
14:   };
15:   IDbCommand command = data.GetCommand(groups);
16:   command.Connection = data.Connection;
17:   if (command.Connection.State == ConnectionState.Closed)
18:   command.Connection.Open();
19:   IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

20:   return reader;
21:   }

This function will update the DataBase once we have selected the Users in a particular Group.

Code Snippet


1:   /// <summary>
2:          /// Saves the group user.
3:          /// </summary>
4:          /// <param name="group">The group.</param>
5:          public void SaveGroupUser(List<UserGroup> usrGroup)
6:   {
7:   UserGroupDataDataContext data = new UserGroupDataDataContext();

8:   //first delete all the existing user data from User Group
9:              foreach (UserGroup match in usrGroup)
10:   {
11:   var deleteUserGroup = from userGroups in data.UserGroups
12:   where userGroups.UserId == match.UserId
13:   select userGroups;
14:   foreach (var userGroup in deleteUserGroup)
15:   data.UserGroups.DeleteOnSubmit(userGroup);

16:   data.SubmitChanges();
17:   }

18:   //MISSING : Logic to implement Save new Data related to User and Group
19:             
20:   }

The same things you can do without using the LINQ to SQL either by writing the Stored Procedure or using the SQL Queries.

I am giving below the sample stored procedure which can replace the function GetGroupUser and GetUserNoInGroup

GetGroupUser

   1:  SET ANSI_NULLS ON

   2:  GO

   3:  SET QUOTED_IDENTIFIER ON

   4:  GO

   5:  CREATE PROCEDURE [dbo].[GetGroupUser]

6:

   7:      (

   8:      @GroupId int

   9:      )

10:

  11:  AS

  12:      SET NOCOUNT ON

13:

  14:      SELECT

  15:          Usr.UserName,

  16:          Grp.GroupName,

  17:          Usr.UserId

  18:      From

  19:          GROUPS Grp

  20:      INNER JOIN

  21:          UserGroup UsrGrp

  22:      ON

  23:          Grp.GroupId = UsrGrp.GroupId

  24:      INNER JOIN USERS Usr ON

  25:          UsrGrp.UserId = Usr.UserId

  26:      WHERE

  27:          Grp.GroupId=@GroupId

  28:      ORDER BY Usr.UserId

29:

  30:      RETURN

GetUserNotInGroup .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

   1:  SET ANSI_NULLS ON

   2:  GO

   3:  SET QUOTED_IDENTIFIER ON

   4:  GO

   5:  ALTER PROCEDURE [dbo].[UserNotInGroup]

6:

   7:      (

   8:      @GroupId int

   9:      )

10:

  11:  AS

  12:      SET NOCOUNT ON

  13:      SELECT

  14:          Usr.UserName,

  15:          Usr.UserId,

  16:          Grp.GroupName

  17:      From

  18:          GROUPS Grp

  19:      INNER JOIN

  20:          UserGroup UsrGrp

  21:      ON

  22:          Grp.GroupId = UsrGrp.GroupId

  23:      INNER JOIN USERS Usr ON

  24:          UsrGrp.UserId = Usr.UserId

  25:      WHERE

  26:          Grp.GroupId!=@GroupId

  27:      ORDER BY Usr.UserId

28:

  29:      RETURN

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

Third, I have included the UserControl in my Project, and added in Default.asp page simply by Drag and Drop.

Also I have added one DropDown Control to get the List of all the Groups and Added a Save Button to Save the Changes back to the database.

After designing the Page will look something similar to this

Now in Code Behind I tried to implement only the logic which is related to the TwoSelect User controls,

Code Snippet


1:   public partial class _Default : System.Web.UI.Page
2:      {
3:   UserData data = new UserData();

4:   private int GetGroupId
5:   {
6:   get { return Convert.ToInt32(ddlGroup.SelectedValue); }
7:   }
8:   /// <summary>
9:          /// Handles the Load event of the Page control.
10:          /// </summary>
11:          /// <param name="sender">The source of the event.</param>
12:          /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
13:          protected void Page_Load(object sender, EventArgs e)
14:   {
15:   if (!IsPostBack)
16:   {
17:   ddlGroup.DataSource = data.GetAllGroup();
18:   ddlGroup.DataTextField = "GroupName";
19:   ddlGroup.DataValueField = "GroupId";
20:   ddlGroup.DataBind();
21:   ListItem item = new ListItem();
22:   item.Text = "Please Select";
23:   item.Value = "0";
24:   ddlGroup.Items.Insert(0,item);
25:   }
26:   }

27:   protected void ddlGroup_SelectedIndexChanged(object sender, EventArgs e)
28:   {
29:   //Bind the User Control Available List Box
30:              uclTwoSelect.DataSourceAvailable = data.GetGroupUser(this.GetGroupId);
31:   uclTwoSelect.DataTextFieldAvailable = "UserName";
32:   uclTwoSelect.DataValueFieldAvailable = "UserId";

33:   //Bind the User Control User Already Added List Box
34:              uclTwoSelect.DataSourceAdded = data.UserNotInGroup(this.GetGroupId);
35:   uclTwoSelect.DataTextFieldAdded = "UserName";
36:   uclTwoSelect.DataValueFieldAdded = "UserId";

37:   uclTwoSelect.BindControl();
38:   }

39:   /// <summary>
40:          /// Saves the User associated with the Groups.
41:          /// </summary>
42:          /// <param name="sender">The source of the event.</param>
43:          /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
44:          protected void btnSave_Click(object sender, EventArgs e)
45:   {
46:   UserGroup user = new UserGroup();
47:   List<UserGroup> group = new List<UserGroup>();
48:   ListItemCollection userGroup = uclTwoSelect.AddedItems;
49:   foreach (ListItem item in userGroup)
50:   {
51:   user.GroupId = Convert.ToInt32(ddlGroup.SelectedValue);
52:   user.UserId = Convert.ToInt32(item.Value);

53:   group.Add(user);
54:   }

55:   data.SaveGroupUser(group);
56:   }
57:   }

You can see in the code above I am binding the DropDown List of the Group and on SelectedIndexChanged even of the DropDown I am Binding the twoSelect Controls Data, with the DataSourceAvailable and DataSourceAdded Properties.

This is just the one of the implementation you can download the Control and its implementation code from here and Enhance the Control.

References

http://www.west-wind.com/WebLog/posts/141435.aspx

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listbox.aspx

http://msdn.microsoft.com/en-us/library/fb3w5b53(VS.85).aspx

Thanks

Brij Mohan

Posted by bmdayal | with no comments

Sunday, October 26, 2008 9:32 PM

TwoSelect User Control, Moving Items between ListBox Controls - Part 1

This post will show you how to create TwoSelect User control, basically this control can be used in the scenario where you want to add Users to selected Group. Left side of the ListBox will contain the users which is not present in the group and right side of the ListBox contains the list of users already present in the group.

I have created this as a very Generic control which can be used in other scenarios other then this also. You can customize the Display Text, button captions, some behaviours of the ListBox of this Control.

Also you need to provide the DataSource for the ListBoxes as IDataReader.

This control is built in Visual Studio 2008 with Framework 3.5 sp1, but this can be used in Visual Studio 2005 or Framework 2.0 also.

Following are the list of properties which are exposed in this control with their Types

   1:  public ListItemCollection AddedItems

2:

   3:  public ListItemCollection AvailableItems

4:

   5:  public string AvailableItemText

6:

   7:  public string AddedItemsText

8:

   9:  public string AddAllItemsButtonText

10:

  11:  public string AddSelectedItemsButtonText

12:

  13:  public string RemoveSelectedItemButtonText

14:

  15:  public string RemoveAllItemsButtonText

16:

  17:  public ListSelectionMode AvailableListSelectionMode

18:

  19:  public ListSelectionMode AddedItemsListSelectionMode

20:

  21:  public IDataReader  DataSourceAvailable

22:

  23:  public IDataReader DataSourceAdded

24:

  25:  public string DataTextFieldAvailable

26:

  27:  public string DataValueFieldAvailable

28:

  29:  public string DataTextFieldAdded

30:

  31:  public string DataValueFieldAdded

On click of the 'Add' Button, I am adding the items in the ArrayList and then from ArrayList I am moving from Available Items List Box to Added Items ListBox.

Add OnClick


1:  /// <summary>
2:  /// Add all the selected items from the Available Items to the Added Items
3:  /// </summary>
4:  /// <param name="sender">The source of the event.</param>
5:  /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
6:  protected void btnAdd_Click(object sender, EventArgs e)
7:   {
8:   if (lstAvailable.SelectedIndex >= 0)
9:   {
10:   for (int i = 0; i < lstAvailable.Items.Count; i++)
11:   {
12:   if (lstAvailable.Items.Selected)
13:   {
14:   if (!arlList.Contains(lstAvailable.Items))
15:   arlList.Add(lstAvailable.Items);
16:   }
17:   }
18:   for (int i = 0; i < arlList.Count; i++)
19:   {
20:   if (!lstAdded.Items.Contains((ListItem)arlList))
21:   lstAdded.Items.Add((ListItem)arlList);
22:   lstAvailable.Items.Remove((ListItem)arlList);
23:   }
24:   }
25:   }

On Click of 'Add All' I am first adding all the items from Available Items to Added Items ListBox, and then clearing all the items from the Available Items ListBox

Code Snippet


1:   /// <summary>
2:   /// Add all the items from the Available items to the Added Items
3:   /// </summary>
4:   /// <param name="sender">The source of the event.</param>
5:   /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
6:   protected void btnAddAll_Click(object sender, EventArgs e)
7:   {
8:   foreach (ListItem list in lstAvailable.Items)
9:   {
10:   lstAdded.Items.Add(list);
11:   }
12:   lstAvailable.Items.Clear();
13:   }

On click of the 'Remove' Button, I am adding the items in the ArrayList and then from ArrayList I am moving from Added Items List Box back to Available Items ListBox.

Code Snippet


1:   /// <summary>
2:   /// Moves the Selected items from the Added items to the Available items
3:  /// </summary>
4:  /// <param name="sender">The source of the event.</param>
5:  /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
6:  protected void btnRemove_Click(object sender, EventArgs e)
7:   {
8:   if (lstAdded.SelectedIndex >= 0)
9:   {
10:   for (int i = 0; i < lstAdded.Items.Count; i++)
11:   {
12:   if (lstAdded.Items.Selected)
13:   {
14:   if (!arlList.Contains(lstAdded.Items))
15:   arlList.Add(lstAdded.Items);
16:   }
17:   }
18:   for (int i = 0; i < arlList.Count; i++)
19:   {
20:   if (!lstAvailable.Items.Contains((ListItem)arlList))
21:   lstAvailable.Items.Add((ListItem)arlList);
22:   lstAdded.Items.Remove((ListItem)arlList);
23:   }
24:   }
25:   }

On Click of 'Remove All' I am first adding all the items from Added Items List Box to Available Items ListBox, and then clearing all the items from the Added Items ListBox.

Code Snippet


1:   /// <summary>
2:   /// Moves all the items from the Added items to the Available items
3:   /// </summary>
4:   /// <param name="sender">The source of the event.</param>
5:   /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
6:   protected void btnRemoveAll_Click(object sender, EventArgs e)
7:   {
8:   foreach (ListItem list in lstAdded.Items)
9:   {
10:   lstAvailable.Items.Add(list);
11:   }
12:   lstAdded.Items.Clear();
13:   }

In ASPX code I am adding just basic look and feel which will look like the one which is given in the screen shot at the beginning of the Post, but you can extend this control to customize the look and feel.

In this Post, I have given the source code of the Control only, in my next Post I will give the sample implementation of this control, using ASP.NET and LINQ.

bmdayal

This site

This Blog

Syndication

Sponsors

Search

Tags

Navigation

Archives