Team management Part - II (Add Users)

Team Management

1. Team Management part 1 (View users)
2. Team Management Part II (Add Users) [Current]

In my previous post I have posted how to view users using SQL server query with database table structure and as a view method I have shown the ways of genealogy view using normal HTML, CSS and Google organizational chart.

In this post I will show you how to add a new user under one user. The post covers

1. Full database table structure of users.
2. Add new user under another user.
3. Send confirmation mail.
4. Confirm users via mail id (Using GUID). 

Full database structure:

Previously we have shown only the normal descriptions like email, name, phone no. and member's parent Id. But here we will include another 2 fields.  One is IsConfirm(bit) and another is IsDelete(bit).

Database Design:

UserId (Primary Key)	Int (Identity(1,1)) Primaty Key
Email	NVarChar(255)
Name	NVarChar(255)
ParentId	Int
IsDelete	Bit
IsConfirm	Bit

IsDelete: Its a bit value to save whether the user is deleted or not. In real process not a single value is being deleted during the delete or update. Each and every information about any thing is keep in store. So we use this type of flag to ensure the arising confusions. If this user is being deleted then the IsDelete field will be False (0), else True (1).

Now you will be little bit confused about the Password section. Where I am storing the password for the user. Here is the answer.

We are using a another table to store the passwords for each user using the UserId.

Id	Int(Identity(1,1)) Primary Key
UserId	Int
Password	NvarChar(255)
Date	Date

Using this table we can trace the user's password and their previous password and we can also use this as recovery process.

IsConfirm: This flag is being used to check whether the user is a valid one or not. Here we are sending an email after user's registration. As user go back to his/her mail box and hit the link, he/ she will be an authenticate user by updating the flag from False to True.

Check the diagram closely and understand the process flow of a full add user process.

Step 1:
Take the all inputs from user by creating a form. Here we will take users name, password email and other details. At the time of choosing the parent you can do two things.

1.  Take the Id of current user (who is logged in). - Use the Session value to get the parent id
2.  Use a Drop Down List to select the user. - Use a Drop down by binding all the users and their id. Take the selected one as Parent. 

Take the parent id and insert those data into database table. Get the ID from there using
@@identity or SCOPE_IDENTITY()

and with the help of that id insert the password into password table. Here you have to maintain another table for the process of email verification.

I am sure you know how to send the mail with HTML body. If not then go through this link. Here I have described how to send mail via Gmail and GoDaddy.

To get the email verification I am using GUID. To know what is GUID and how to generate that go through ...




We have to link this generated GUID with our user id, and IsConfrim. So what to do? Lets create the table structure.

GUID	NVarChar(255)
TableName	NVarChar(255)
Field	NVarChar(255)
Value	NvarChar(255)
UserId	INT
IsActive	Bit

GUID: The actual code to be generated (unique id)

TableName: On which table it will be updated

Field: On which field the action will be done.

Value: What will be the value after updation of the field.

UserId: On which user it change will be applicable.
IsActive: Is this still usable or not. At first when the GUID is creating it is True and after use it will turn into False.

How to create a GUID? 

To create GUID write down the following code snippet

Guid obj = Guid.NewGuid(); 
Console.WriteLine("New Guid is " + obj.ToString());

this will return a new GUID and with this you can perform all the operations you want.

All you have to do now is have to send a mail along with this GUID. To mail this you can use any of the SMTP server. To send via Google check this or if you want to send via GoDaddy server check this one.  As the mail content will be a link, may be a page link with the generated GUID. Like this

http://yourdomain.com/confirmuser?id=your_guid

or

http://yourdomain.com/confirmuser/your_guid

or any thing you want. You can easily get the guid from the URL via Query string.

string guid  = Request.QueryString("id");

when ever you got the GUID you can easily check whether it is previously used or not?

Select  IsActive from <table_name> where GUID="<your GUID>"

It will return either True or False and from that we can get that we have to proceed or not with this user. 

If True then get the table name, user id,  field and the value using this guid.

Select  TableName, Value, UserId, Field from <Table_Name> where GUID = "<your GUID>"

Took this data into a DataTable (say DataTable dt) for future use. Now you have to do the trick with this DataTable. The update statement will something like this.

Update dt.Rows[0]["TableName"].ToString()  set dt.Rows[0]["Field"].ToString() ="+ dt.Rows[0]["Value"].ToString() +" where UserId = "dt.Rows[0]["UserId"].ToString()"

Its is like

Update tblUser set IsActive="True" where UserId="<userid>"

Now update the table and send User a confirmation mail. Before exiting update the Guid table's isActive with false against the GUID.

Now your user is ready to use the application. Enojoy.