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 Retrieving 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.
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.
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.
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.
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 Grp20: INNER JOIN
21: UserGroup UsrGrp22: ON
23: Grp.GroupId = UsrGrp.GroupId24: INNER JOIN USERS Usr ON
25: UsrGrp.UserId = Usr.UserId26: WHERE
27: Grp.GroupId=@GroupId28: ORDER BY Usr.UserId
29: 30: RETURN
- GetUserNotInGroup
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 Grp19: INNER JOIN
20: UserGroup UsrGrp21: ON
22: Grp.GroupId = UsrGrp.GroupId23: INNER JOIN USERS Usr ON
24: UsrGrp.UserId = Usr.UserId25: WHERE
26: Grp.GroupId!=@GroupId27: ORDER BY Usr.UserId
28: 29: RETURN
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,
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.
Sorry for the Code Formatting Guys, but you can always refer my asp.net weblog for my neat and clear code formatting.
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
0 comments:
Post a Comment