check if data already present in db

Preetham Thangamma

Good Morning,

Please do let me know how to check if the data is already present in the db while creating another record using ASP.Net Core6

Thank you

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaydeep Patil4y ago
Hi Preetam,

As per my understanding if you use dapper and something like that then you can use simple SqlCommand and put the query to count no of record with perticular type you want ti insert and then check that count using if condition.

HaPpy Learning!
+2
Preetham Thangamma4y ago
hello Sachin Singh,

i did use the linq but it is passing null value..following is my code:
User customer = _context.User.Where(x => x.Email == Email).SingleOrDefault();
if (customer != null)
{
ViewBag.Message = "Exist";
return View();
}
else
{
User user = vm.Users;
_context.Add(user);
UserProgram userProgram = vm.UserPrograms;
userProgram.CreatedOn = user.CreatedOn;
await _context.SaveChangesAsync();
userProgram.UserId = user.Id;
_context.Add(userProgram);
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Create));

}

+2
Sachin Singh4y ago
why can't you use jquery in core 6?
If you are talking about EF Core then you can easily replace the SP with Linq as
var res= _context.Users.Any(x=>x.Email==Email);
return res;
+2
Preetham Thangamma4y ago
Hello Sachin Singh,

Thank you so much for the reply but this code is of no use for me as i am working in ASP.Net core 6...can u please let me know how do it in ASP.Net core 6

Thank you
+2
Sachin Singh4y ago
step 1.
"txtEmail" type="text" />
<span id="message">span>
step 2.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CheckEmailAvailability]
      @Email NVARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
      IF NOT EXISTS(SELECT UserName FROM Users
                    WHERE Email = @Email)
      BEGIN
            SELECT 'TRUE'
      END
      ELSE
      BEGIN
            SELECT 'FALSE'
      END
END
GO
step 3.
public IActionResult CheckEmail(string email)
{
    bool status = false;
    string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("CheckEmailAvailability", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Email", email.Trim());
            conn.Open();
            status = Convert.ToBoolean(cmd.ExecuteScalar());
            conn.Close();
        }
    }

return Json(new {isValid= status});

}
step 4.
$("#txtEmail").blur(function(){

var email = $("#txtEmail").val();
        $.ajax({
            type: "POST",
            url: "/ControllerName/CheckEmail",
            data: '{email: "' + email + '" }',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                var message = $("#message");
                if (response.isValid) {
                    //Email available.
                    message.css("color", "green");
                    message.html("Email is available");
                }
                else {
                    //Email not available.
                    message.css("color", "red");
                    message.html("Email is NOT available");
                }
            }
        });
});
+3
Nitin Sontakke4y ago
Understood! This is exactly what I was looking for. As you see, it is almost always a good ideal to give as munch information as you can so that the best possible solution can be provided. Anyway.

Here all your email ids are held in a database.

You are better of writing a stored procedure, for example, usp_emailAddressExists, to which you can pass the email address as a parameter. This will return 1 if email address exists or 0 if not.

Or a plain and simple select statement, if you don't want this level of sofistication, complexity.

Hope it helps!
+3
Preetham Thangamma4y ago
Hello Nitin Sontakke Sir,

My question is if i enter an email id in the textbox and click create button,it should pop a message that "the email id already exists" if the email id is alraedy is alraedy saved in the database...please do let me know how to do that!!

Thank you
+2
Nitin Sontakke4y ago
To begin with, personally, I won't expect / know business layer / middleware to have this knowledge.

The question is what is your criteria of determining if record already exists, this matters a lot.

Typically all data entities expect to have a unique primary key which is numeric. If such is the case, then any corrosponding property of a model class will have 0 value. This is one way.

Another approach is to have a single stored procedure on db side responsible for insert and update as well.

This procedure can have your custom criteria of record existance checking and taking action accordingly.

Hope you understand.

Any questions, please do let me know.
+3