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
  3. id EmailID
  4. 1 [email protected]
  5. 2 [email protected]
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 [email protected]. Return the Left('[email protected]',(charindex('@','[email protected]'))-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 varchar) AS 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++) {
  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.
  12. if (msg.d == 'Available') {
  13. uname.removeClass("notavailablecss");
  14. uname.addClass("availablecss");
  15. document.getElementById("msgbox").innerHTML = "";
  17. msgbox.html('<img src="Images/a.png"> <font color="Green"> Available </font>');
  19. }
  20. else {
  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++) {
  28. document.getElementById("msgbox").innerHTML+= ("<br/>"+temp[i]+"<br/>");
  29. }
  30. }
  31. }
  32. });
  33. }
  34. else {
  36. uname.addClass("notavailablecss");
  37. msgbox.html('<font color="#cc0000">User Name must be more than 5 characters</font>');
  38. }
  41. }
  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);
  16. if (dt.Rows.Count == 0)
  17. {
  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();
  27. }
  28. }

  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.