Introduction: In this article I have explained how to check username or email availability from sql server database table using Asp.Net, jQuery and Ajax.
Description: While working on Asp.Net page we usually need to create a registration page to register users so that they can login to the website. On registration page there may be the fields like username, password, date of birth etc.
Username or email must me unique for each user. So developer has to validate them as soon as they are entered in respective textboxes so that user came to know whether the username or email entered is available or not.
There are many ways to implement this check. Here I am going to use jQuery to make ajax calls to the server to check whether username/email is available or already assigned to other user.
Implementation: Let’s create a test page (default.aspx) for demonstration purpose.
First of all create a table using the following script
CREATE TABLE tbUsers
(
UserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserName VARCHAR(50) NOT NULL,
Password VARCHAR(50) NOT NULL
)
Insert some dummy data for testing purpose using following script
INSERT INTO tbUsers (UserName, Password)
VALUES ('admin','admin@007#'),('sultan','sultan_777'),('Kabali','bali#999')
Check table data
SELECT * FROM tbUsers
Result:
UserId
|
UserName
|
Password
|
1
|
Admin
|
admin@007#
|
2
|
Sultan
|
sultan_777
|
3
|
Kabali
|
bali#999
|
Now create a stored procedure to check for username availability as:
CREATE PROCEDURE spCheckUserNameAvailability
(
@UserName VARCHAR(50)
)
AS
BEGIN
SELECT COUNT(*) FROM tbUsers WHERE UserName = @UserName
END
Now in web.config file create connection string as:
<connectionStrings>
<add name="sqlCon" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True"/>
</connectionStrings>
HTML source
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
.success {
background-color: #5cb85c;
font-size: 12px;
color: #ffffff;
padding: 3px 6px 3px 6px;
}
.failure {
background-color: #ed4e2a;
font-size: 12px;
color: #ffffff;
padding: 3px 6px 3px 6px;
}
</style>
<script src="http://code.jquery.com/jquery-1.11.3.js" type="text/javascript"></script>
<script type="text/javascript">
function checkUserName(txtUserName) {
$.ajax({
type: "POST",
async: true,
url: 'default.aspx/CheckUserNameAvailability',
data: '{username: "' + $(txtUserName).val().trim() + '" }',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
if (response.d != "0") {
$("#spnMsg").html('Username has already been taken');
$("#spnMsg").removeClass("success").addClass("failure");
$("#btnRegister").prop('disabled', true);
}
else {
$("#spnMsg").html('Available');
$("#spnMsg").removeClass("failure").addClass("success");
$("#btnRegister").prop('disabled', false);
}
}
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset style="width: 350px;">
<legend>Register</legend>
<table>
<tr>
<td>
<asp:TextBox ID="txtUserName" runat="server" placeholder="User Name"onchange="checkUserName(this)"></asp:TextBox>
<span id="spnMsg"></span></td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtPassword" runat="server" placeholder="Password" TextMode="Password"
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnRegister" runat="server" Text="Register" ClientIDMode="Static" /></td>
</tr>
</table>
</fieldset>
</div>
</form>
</body>
</html>
Asp.Net C# Code to check username availability
In .aspx.cs file create a method to check username availability as:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
[WebMethod]
public static int CheckUserNameAvailability(string username)
{
string conString = ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand("spCheckUserNameAvailability", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
conn.Open();
return (int)cmd.ExecuteScalar();
}
}
}
Asp.Net VB Code to check username availability
In .aspx.vb file create a method to check username availability as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
<WebMethod> _
Public Shared Function CheckUserNameAvailability(username As String) As Integer
Dim conString As String = ConfigurationManager.ConnectionStrings("sqlCon").ConnectionString
Using conn As New SqlConnection(conString)
Using cmd As New SqlCommand("spCheckUserNameAvailability", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@UserName", username)
conn.Open()
Return CInt(cmd.ExecuteScalar())
End Using
End Using
End Function
Explanation: As soon as username is entered in textbox the onchange event gets fired and username is passed to the checkUserName function which makes ajax call to the server side function “CheckUserNameAvailability” with the help of jquery.
To be able to call server side function using jquery ajax, the function must be defined as web service WebMethod and it should be public static in C# and public shared in Vb. Username passed as parameter to this function is then passed to stored procedure which counts how many similar username exists in database.
If the returned count is greater than 0 then that means same username or email already exists in table otherwise it is available.
No comments:
Post a Comment