Email Name Suggestions Using Database (SQL Server)

Introduction

In my previous article Determine Whether a User Name is Available Using jQuery and Ajax determines whether or not a user exists in the table. In this article we are learning whether or not a username exists. If the user exists in the table then some other name is suggested like Gmail. We can check whether the user exists in the table using the front end (C# Language) or the back end. I always prefer the back end(database).
 
First of all we need a table for holding the customer login data.
  1. Select * from mytable  
  2.   
  3. id            EmailID  
  4. 1             Jogi@gmail.com
  5. 2             test@gmail.com   
In the previous article in create a query in the .cs file, this type of query creates a SQL Injection and SQL Injection is very bad for developers. So in this article we create a procedure for connecting with the database. The procedure has one parameter @EmailID  varchar(max). 
  1. if exists(select id from mytable where emailid=@EmailId)  //Check Email id already exists or not in the mytable.  
  1. create proc Proc_CheckUserAndSuggestEmail  
  2. (  
  3. @EmailId varchar(max)  // input Parameter
  4. )  
  5. as  
  6. begin  
  7. if exists(select id from mytable where emailid=@EmailId) 
  8. begin  
  9. select Email from fun_suggest(@EmailID)  //if already exists call this function with parameter
  10. end  
  11. end 
We need another function that returns the table. The function name is fun_suggest. The one parameter is @dataone.  
  1. SELECT @cslice= LEFT(@dataone,(CHARINDEX('@',@dataone)) - 1) --This line break the emailid like this type. suppose I enter the test@gmail.com. Return the Left('test@gmail.com',(charindex('@','test@gmail.com'))-1) "test" only.    
  1. ALTER function [dbo].[fun_suggest]    
  2. (@dataone varchar(max))    
  3. returns @temptable table(Email varchar(max))    
  4. as    
  5. begin    
  6. declare @cslice varchar(max)    
  7. declare @notsingle int=1 --set the value of Inner while loop    
  8. declare @five int =5 --set the value of outer while loop     
  9. declare @temp varchar(max--hold the value of simple    
  10. declare @charindexvalue int --check and hold the underscore(_) in the email id    
  11. declare @boolvalue int=1  --check and hold the value of help makeing the query    
  12. SELECT @cslice= LEFT(@dataone,(CHARINDEX('@',@dataone)) - 1)    
  13. while(@five>0)    --this while loop work five time only.  
  14. begin    
  15. select @charindexvalue=CHARINDEX('_',@dataone) --if '_' underscore is found in email id then return the number of
  16.  where is place other wise return the zero(number).   
  17. if(@charindexvalue=0)   --if @charindexvalue is zero then have not  _ in email id  
  18. begin    
  19. SELECT @temp=@cslice+'_'   -- So I add _ every email id if have not email id.  
  20. end    
  21. else    
  22. begin    
  23. SELECT @temp=@cslice  --if have already '_' in email id don't add another _.  
  24. end    
  25. select @five-=1    -- every time decrement one....  
  26. while(@notsingle>0) --this loop run have no idea which times run.   
  27. begin    
  28. if(@boolvalue!=1)    
  29. begin    
  30. select @temp=left(@temp,charindex('@',@temp)-1) --get the word before the @.    
  31. end    
  32. set @temp+=(select myrand from Get_Rand)   --Get_Rand view return the every time random number. view explain below.    
  33. if (CHARINDEX('@',@temp)=0)    
  34. begin    
  35. select @temp+=RIGHT(@dataone,(len(@dataone)-CHARINDEX('@',@dataone)+1)) --concatenate @gmail.com    
  36. end    
  37. if not exists(select id from mytable where Emailid=@temp)   --check automatic create email id exists or Not in the table  
  38. begin    
  39. if not exists( select email from @temptable where Email=@temp)  --check automatic create email is exists or not in the @temptable  
  40. begin    
  41. select @notsingle-=1;    
  42. end    
  43. end    
  44. set @boolvalue=0    
  45. end    
  46. set @notsingle=1    
  47. set @boolvalue=1    
  48. insert into @temptable values(@temp)  --insert the email id in @temptable  
  49. SELECT @temp=''    
  50. SELECT @temp=@cslice+'_'    
  51. end    
  52. return    
  53. end   
This view always returns a number in the range 1 to 9. 
  1. CREATE VIEW [dbo].[Get_RAND]  
  2. AS  
  3. SELECT CAST(CAST(RAND(CHECKSUM(NEWID())) * 9 AS int) + 1 AS varcharAS MyRAND  
Our back end work is complete. Now for the front end, in other words C# and jQuery. This project uses jQuery. We want to do it without a complete page refresh. I have the suggestion email id. So I create one function checkuser(). 
  1. type: "POST"  //aspx page have two state one is Get and another is Post state.  
  1. url: "Register.aspx/CheckUserName",  // page URL is Register.aspx and function name is checkUserName 
 msg.d.split(","); return the array of data. var temp to hold the array of data. If we want to get the data one by one we need a Loop. I decided to make a loop for getting the data one by  one.  
  1. var temp = msg.d.split(",");    
  2.                      document.getElementById("msgbox").innerHTML = "";    
  3.                   for (var i = 0; i < temp.length; i++) {    
  4.    
  5.                    document.getElementById("msgbox").innerHTML+= ("<br/>"+temp[i]+"<br/>");   
The following is the complete function of JavaScript. 
  1. function checkuser() {  
  2. var uname = $("#<%=txtUserName.UniqueID%>");  
  3.   if (uname.val().length > 5) {  
  4.             $.ajax({  
  5.                 type: "POST",  
  6.                 url: "Register.aspx/CheckUserName"// In this example most important thing is Testing is Static Method name like this:--- public static string Testing(string testing) and also parameter name is match other wise no error expection fire and no result accourate.  
  7.                 data: "{'args': '" + uname.val() + "'}",  
  8.                 contentType: "application/json; charset=utf-8",  
  9.                 dataType: "json",  
  10.                 success: function (msg) { //if success then this funcation call other wise some error message fire. code behind method reaten a String type value this value hold is msg.     
  11.   
  12.                     if (msg.d == 'Available') {  
  13.                         uname.removeClass("notavailablecss");  
  14.                         uname.addClass("availablecss");  
  15.                         document.getElementById("msgbox").innerHTML = "";  
  16.   
  17.                         msgbox.html('<img src="Images/a.png"> <font color="Green"> Available </font>');  
  18.   
  19.                     }  
  20.                     else {  
  21.                         
  22.                       uname.removeClass("availablecss");  
  23.                       uname.addClass("notavailablecss");  
  24.                       var temp = msg.d.split(",");  
  25.                       document.getElementById("msgbox").innerHTML = "";  
  26.                    for (var i = 0; i < temp.length; i++) {  
  27.   
  28.                     document.getElementById("msgbox").innerHTML+= ("<br/>"+temp[i]+"<br/>");  
  29.                    }  
  30.                     }  
  31.                 }  
  32.             });  
  33.         }  
  34.         else {  
  35.   
  36.             uname.addClass("notavailablecss");  
  37.        msgbox.html('<font color="#cc0000">User Name must be more than 5 characters</font>');  
  38.         }  
  39.   
  40.   
  41. }  
  42.   
  43. /script> 
And my last point is the function of a .cs file.
  1. [System.Web.Services.WebMethod]  
  2.    public static string CheckUserName(string args)  
  3.    {  
  4.        DataTable dt = new DataTable();  
  5.        string returnValue = string.Empty;  
  6.        SqlConnection sqlConn = new SqlConnection(@"Data Source=-----;Initial Catalog=----;Integrated Security=True");  
  7.        try  
  8.        {  
  9.            SqlCommand sqlCmd = new SqlCommand("Proc_CheckUserAndSuggestEmail", sqlConn);  
  10.            sqlCmd.CommandType = CommandType.StoredProcedure;  
  11.            sqlCmd.Parameters.AddWithValue("@EmailId", args);  
  12.            sqlConn.Open();  
  13.            SqlDataReader rd= sqlCmd.ExecuteReader();  
  14.           dt.Load(rd);  
  15.             
  16.           if (dt.Rows.Count == 0)  
  17.           {  
  18.   
  19.               returnValue = "Available";  
  20.           }  
  21.           else  
  22.           {  
  23.               for (int i = 0; i < dt.Rows.Count; i++)  
  24.               {  
  25.                   returnValue +=","+ dt.Rows[i]["email"].ToString();  
  26.                     
  27.               }  
  28.           }  
  29.                 
  30.          }  
  31.        catch  
  32.        {  
  33.            //Handle Error  
  34.        }  
  35.        finally  
  36.        {  
  37.            sqlConn.Close();  
  38.        }  
  39.          return returnValue;  
  40.    } 
To check whether or not the email id exists in the table I enter the id already in the table and get the suggestion of another five email ids.
 
type user name
 
Then enter again another email id. This email id does not exist in the table.  

user name

Final word

If you have any query then drop a comment in the comment box. You can download this project with the database query.