check if data already present in db

Question

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

Sachin Singh · Answer

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" ); } } }); });

Nitin Sontakke · Answer

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!

Nitin Sontakke · Answer

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.

Jaydeep Patil · Answer

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!

Preetham Thangamma · Answer

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)); }

Sachin Singh · Answer

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;

Preetham Thangamma · Answer

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

Preetham Thangamma · Answer

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