Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
In this video we will discuss how to check if username exists in database using asp.net web service and jQuery ajax.
As we start to type in the the username textbox and when the number of characters in the textbox is equal to or greater than 3, then we want to issue an ajax call to check in the database table if that username is already in use.
If the username is already taken, then we want to display a message stating username already in use.
If the username is not taken yet, then we want to display a message stating username available.
Step 1 : Create SQL Server table and insert test data
Create table tblRegistration
(
ID int primary key identity,
Username nvarchar(100)
)
Go
Insert into tblRegistration values (‘Pameela’)
Insert into tblRegistration values (‘SaraBaker’)
Go
Step 2 : Create a stored procedure to check if username is in use. This stored procedure returns 1 if the user name is already taken, else 0.
Create procedure spUserNameExists
@UserName nvarchar(100)
as
Begin
Declare @Count int
Select @Count = Count(UserName)
from tblRegistration
where UserName = @UserName
If @Count ] 0
Select 1 as UserNameExists
Else
Select 0 as UserNameExists
End
Step 3 : Create new asp.net web application project. Name it Demo.
Step 4 : Include a connection string in the web.config file to your database.
Step 5 : Add a class file to the project. Name it Registration.cs. Copy and paste the following code.
namespace Demo
{
public class Registration
{
public string UserName { get; set; }
public bool UserNameInUse { get; set; }
}
}
Step 6 : Add a new WebService (ASMX). Name it RegistrationService.asmx. Copy and paste the following code.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace Demo
{
[WebService(Namespace = ”
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class RegistrationService : System.Web.Services.WebService
{
[WebMethod]
public void UserNameExists(string userName)
{
bool userNameInUse = false;
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spUserNameExists”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = “@UserName”,
Value = userName
});
con.Open();
userNameInUse = Convert.ToBoolean(cmd.ExecuteScalar());
}
Registration regsitration = new Registration();
regsitration.UserName = userName;
regsitration.UserNameInUse = userNameInUse;
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(regsitration));
}
}
}
Step 7 : jQuery code
$(document).ready(function () {
$(‘#txtUserName’).keyup(function () {
var userName = $(this).val();
if (userName.length ]= 3) {
$.ajax({
url: ‘RegistrationService.asmx/UserNameExists’,
method: ‘post’,
data: { userName: userName },
dataType: ‘json’,
success: function (data) {
var divElement = $(‘#divOutput’);
if (data.UserNameInUse) {
divElement.text(data.UserName + ‘ already in use’);
divElement.css(‘color’, ‘red’);
}
else {
divElement.text(data.UserName + ‘ available’)
divElement.css(‘color’, ‘green’);
}
},
error: function (err) {
alert(err);
}
});
}
});
});
