How to Get Client IP Address in ASP.NET Core

I was working on this ASP.NET Core 3 project for a client, and he asked me to capture the current client IP address. So, I thought it would be beneficial I write a quick post to share with you how I was able to get this done.

I remember when working in classic ASP.NET, in order to get the user IP address we would simply access the request of the current HTTP context or directly accessing the request. However, things changed in ASP.NET Core a little. Therefore, let’s explore how we can get the current user’s IP address in an ASP.NET Core 3 MVC application.

Ways to get IP in Classic ASP.NET

  • Example HttpContext.Current.Request.UserHostAddress

           var ip =  HttpContext.Current.Request.UserHostAddress; 
         
  • Example Request.UserHostAddress

          var ip = Request.UserHostAddress;  
         

Create a New ASP.NET Core Web Project

We are going to create a new ASP.NET Core 3 WEB application and use that as our demonstration. I am using Visual Studio 2019 for this demo.

  • Create a .NET Core 3.x Web application and click next.
  • On the next popup, provide a name for the application. let’s call it “ClientIPAdressDemo”. Then, click create.
  • Select the “Web Application (Model-View-Controller)” template as shown below and click create.
  • Once the project is done created and Visual studio restores the nuget packages, simply run the application. And you should see a “Welcome” displayed in browser as shown below.

Way to get client IP address in ASP.NET Core

Now that we have a working solution, let’s inject the HttpContextAccessor instance in the ConfigureServices method from the Startup.cs class. You maybe have to add this reference using Microsoft.AspNetCore.Http.

 
  public void ConfigureServices(IServiceCollection services)
  {
    services.AddControllersWithViews();
    services.AddSingleton<IActionContextAccessor, ActionContextAccessor>();
  }

Next, we’ll update the HomeController to inject the IActionContextAccessor in the constructor. But first let’s declare a variable.

 private readonly IActionContextAccessor _accessor;

Now, let’s inject the IActionContextAccessor into the controller’s constructor with DI.

  public HomeController(ILogger logger, IActionContextAccessor accessor)
  {
    _logger = logger;
    _accessor = accessor;
  }

Finally, we can retrieve the IP address from the accessor variable.

public IActionResult Index()
{
  var ip = _accessor.ActionContext.HttpContext.Connection.RemoteIpAddress.ToString();
  return View();
}

Just a minor note…
In case you don’t want to use DI, you can always access the IP information directly in the controller as followed.

var ip =HttpContext.Connection.RemoteIpAddress.ToString()

Interesting Features about JSON.stringify

If you’ve been working with JavaScript in the past, you probably used this cool JSON.stringify method to convert some type of JavaScript value into its JSON string representation. Such method can be used with simple data types like strings and numbers. We probably have used this daily, but unaware of the extra feature available to use in JSON.stringify method. Therefore, in this post we’re going to explore some basic features we can use instantly in our next JavaScript code. To follow along, check out this demo link.

The basic

var cities= ['Miami', 'Orlando', 'Los Angeles', 'New York', 'Chicago'];
var json = JSON.stringify(cities); 

console.log(json);
//["Miami","Orlando","Los Angeles","New York","Chicago"]

If you look closely to the comment in the code snippet above, the array is printed out with no spaces and the single quotes are updated to double quotes.

JavaScript Object Conversion

In some cases you might be working with a JavaScript Object and want to convert it into JSON.

const person = {
  firstName: 'Paul',
  lastName: 'Johnson',
  age: 50,
  jobTitle: 'Manager'
}
var json = JSON.stringify(person);

console.log(json);
//{"firstName":"Paul","lastName":"Johnson","age":50,"jobTitle":"Manager"}

Again, as you can see the object is converted into a string and each property inside the object is shown as well as its value.

Working with Unsupported DataTypes

Not all data types supported by JavaScript can be converted in JSON. Types such as Strings, numbers, booleans, null, arrays, and objects are supported. However, date objects, regular expressions, undefined, and functions are not. How JSON.stringify deals with unsupported data types may differ if they are contained in an object or an array.
In case you have a complex object that can’t be serialized, it will be converted into an empty object or omitted. So beware of that when working with JSON.stringify. Here’s a quick snippet below showing a function and a complex credit object. Let’s see how that goes.

const book = {
  title: 'JSON Stringify Example',
  description: 'Providing great example on how to use JSON stringify',
  year: '2020',
  isOverdue: function() {
    // code here
  },
  credits: new Map([
    ['Jacque Roma'],
    ['Darline Raroule']
  ])
}
var json = JSON.stringify(book);

//{"title":"JSON Stringify Example","description":"Providing great example on how to use JSON stringify","year":"2020","credits":{}}

As you can see above in the output comment the credit object is indeed empty and isOverdue is removed.

Working with the replacer parameter in JSON.stringify

According to this Mozilla postthe replacer parameter can be either a function or an array. This function receives the actual key and value on which the JSON.stringify method is operating on. You are able to change the representation of the value with the function’s return value.
Let’s look at a quick example below on how to use the replacer as a function.

function replacer(key, value) {
  if (value < 1000) {
    return undefined;
  }
  return value;
}

var reviews = {
  books: 5000,
  cars: 3000,
  house: 999,
  boat: 2000
};
json = JSON.stringify(reviews, replacer);
console.log(json);
//{"books":5000,"cars":3000,"boat":2000}

After running the script above, because of the replacer function house is now printed out in the output. This is one way to remove unwanted values from the result.

However, when replacer is an array, the array's values will dictate what returned in the resulting JSON string. Here's a quick example below.

var reviews = {
  books: 5000,
  cars: 3000,
  house: 999,
  boat: 2000
};
json = JSON.stringify(reviews, ['books', 'house','boat'] );
console.log(json);
//{"books":5000,"house":999,"boat":2000}

Use of the space argument

JSON stringify has a third argument which you can use to control the spacing in the final string.

  • Use a number as the third argument ==> will cause each level in the stringification to be indented with this number of space characters up to 10.
    var reviews = {
      books: 5000,
      cars: 3000,
      house: 999,
      boat: 2000
    };
    json = JSON.stringify(reviews, ['books', 'house','boat'], 2);
    console.log(json);
    
    //example result below
    //{
    //  "books": 5000,
    //  "house": 999,
    //  "boat": 2000
    //}
    
  • Use a string as the third argument ==> will cause each level to be indented by this string (or the first ten characters of it). We'll use the same example above.
    var reviews = {
      books: 5000,
      cars: 3000,
      house: 999,
      boat: 2000
    };
    json = JSON.stringify(reviews, ['books', 'house','boat'], 'xx');
    console.log(json);
    
    //example result below
    //{
    //xx"books": 5000,
    //xx"house": 999,
    //xx"boat": 2000
    //}
    

To conclude, I would advise that you try it out for yourself and if this new to look more into the documentation.

Use SQLite in .NET Core 3 With Entity Framework Core

As you might know, ASP.NET is able to support many different types of Database providers. Today, I decided to test the use of SQLite in ASP.NET Core 3 with Entity Framework Core. For a quick demonstration, I’ll create a simple console application. You can follow along.

Create Sample Console Project

Create a .NET Core 3.x console application in Visual Studio 2019.

Next Dialog, give the project a name.

Install Entity Framework Core

When working with EF Core, you will want to install the correct package for the Entity Framework Core database provider you want to target in your project. Since this demo project is regarding SQLite, I am going to install the EF Core that supports SQLite.

Add NuGet package reference

Install-Package Microsoft.EntityFrameworkCore.Sqlite

Now that we have SQLite Support added, let’s add a simple model to the project to use a test.
Right-Click the project name, Add, and Class to add the new model as shown below.


Replace content of the employee model class with the following snippet.

namespace SQLiteAspNetCoreDemo
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    }
}

Create DBContext

Now we need to create the DBContext. Add a new class file and name it SQLiteDBContext.cs and add following code.

using Microsoft.EntityFrameworkCore;

namespace SQLiteAspNetCoreDemo
{
    public class SQLiteDBContext : DbContext
    {
        public DbSet Employees { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=sqlitedemo.db");
    }
}

The code above contains a basic DbSet which is used to create the Employees table.
OnConfiguring() method is used for configuring the DBContext. Here the option is set to use Sqlite database and also setting a connection string or data source to connect.

Now, we’re ready to test things out. Let’s try to add some employee data. Open Program.cs and add the following code.

The moment of truth. Before we can try to run the project we’ll need to run migration first using the following commands in Package Manager Console.
First we’ll need to add Microsoft.EntityFrameworkCore.Tools to allow us to create and apply migration.

Install-Package Microsoft.EntityFrameworkCore.Tools

Next, we need to add migration. Since this is the first migration we’re running we’ll call it InitialCreate.

Add-Migration InitialCreate

A new file will be created in the project with the following content

using Microsoft.EntityFrameworkCore.Migrations;

namespace SQLiteAspNetCoreDemo.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    Id = table.Column(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    FirstName = table.Column(nullable: true),
                    LastName = table.Column(nullable: true),
                    Age = table.Column(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Employees");
        }
    }
}

Update-Database

After running the update command above, we can see the result in the output windows as shown below.

Update the program

We can start testing things out and see if it works. Find the Program.cs file and replace its content with the following.
In the code below, we are creating a new employee object and save it in the database.
Next, we read and get the first employee from the database. Of course, it’s a new database therefore, we’re expecting just one entry in the database. Then, we’re updating the employee first name and the age. Last, we’re deleting that employee.

using System;
using System.Linq;

namespace SQLiteAspNetCoreDemo
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var db = new SQLiteDBContext())
            {
                // Create
                Console.WriteLine("Add New Employee: ");
                db.Employees.Add(new Employee { FirstName = "John", LastName = "Doe", Age = 55 });
                db.SaveChanges();

                Console.WriteLine("Employee has been added sucessfully.");

                // Read
                Console.WriteLine("Querying table for that employee.");
                var employee = db.Employees
                    .OrderBy(b => b.Id)
                    .First();

                Console.WriteLine("The employee found: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                // Update
                Console.WriteLine("Updating the employee first name and age.");

                employee.FirstName = "Louis";
                employee.Age = 90;

                Console.WriteLine("Newly updated employee is: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                db.SaveChanges();

                // Delete
                Console.WriteLine("Delete the employee.");

                db.Remove(employee);
                db.SaveChanges();
            }
        }
    }
}

Test SQLite in ASP.NET Core 3

Finally, the project was run successfully. Below is the output. In case you run into issue with the SQLite table not found, check this post which might help.

How To Add Swagger To Your Web API In ASP.Net Core 3

What is Swagger?
According to Swagger.io, it is basically an open-source software framework with a large ecosystem of tooling that helps developers design, build, document, and consume RESTful web services. For more reading on Swagger check out there Tools Documentation.

Let’s build a WEB API for this demo

In order for me to be able to show how to add swagger, I will need to implement a simple WEB API in ASP.NET Core. If you would like to follow along, here’s a link to the demo project on GitHub

In this post, you learn how to:

  • Create a web API project.
  • Add a model class and a database context.
  • Scaffold a controller with CRUD methods.
  • Add Swagger

Prerequisites

Create Web API Project

From the File menu, select New > Project.
Select the ASP.NET Core Web Application template and click Next.

Give the project a name or if you would like to follow along, name the project WebApiSwaggerMoviesDemo and click Create.

In the Create a new ASP.NET Core Web Application dialog, confirm that .NET Core and ASP.NET Core 3.0 are selected. Select the API template and click Create.

By default a new API called “WeatherForecast” is created for you.

Test the WeatherForecast API

Before I add to this project, I wanted to test it first to make sure everything is good to go. As, you can see I get a list of temperatures below in JSON format.

Add New Movie API

Now that I know the application is working as expected. I need to add a new Model folder to house the movie model.

In Solution Explorer, right-click the project. Select Add > New Folder. Name the folder Models.

Right-click the Models folder and select Add > Class. Name the class Movie and select Add. Therefore, a new file called Movie.cs is added in the Models folder.

Replace the template code with the following code:

using System;

namespace WebApiSwaggerMoviesDemo.Models
{
    public class Movie
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public string Classification { get; set; }
        public DateTime? ReleaseDate { get; set; }
    }
}

Configure the DB Context

Right-click the Models folder and select Add > Class. Name the class MovieContext and click Add. Then, make sure to replace the content of the file with the following piece of code.

using Microsoft.EntityFrameworkCore;

namespace WebApiSwaggerMoviesDemo.Models
{
    public class MovieContext: DbContext
    {
        public MovieContext(DbContextOptions options)
             : base(options)
        {
        
        }
        public DbSet Movies { get; set; }
    }
}

While developing this demo, I did not have Microsoft.EntityFrameworkCore installed, so I had to get it install via NuGet Package Manager.

Register the DBContext

As you already know ASP.NET Core is designed from the start to support Dependency Injection, therefore I need to register the Database context with the IOC container. Something to note here is that I am also specifying to use In-memory database. The name is also specified.

Replace startup.cs file with the following code below.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using WebApiSwaggerMoviesDemo.Models;
using Microsoft.EntityFrameworkCore;

namespace WebApiSwaggerMoviesDemo
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
           
services.AddDbContext(opt => { opt.UseInMemoryDatabase("MovieList"); });
services.AddControllers(); } // This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.UseHttpsRedirection(); app.UseRouting(); app.UseAuthorization(); app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); } } }

Add Movies Controller

In order to add the movie controller, I used the scaffold feature as followed.
Right-click the Controllers folder.

Select Add > New Scaffolded Item.

Select API Controller with actions, using Entity Framework, and then select Add.

In the Add API Controller with actions, using Entity Framework dialog:

Pick Movie (WebApiSwaggerMoviesDemo.Models) in the Model class.
Next,select MovieContext (WebApiSwaggerMoviesDemo.Models) in the Data context class.
Then, click Add.

Install Swagger

I have everything I need in the Web API. It’s time to install Swagger. So, I will use NuGet Package Manager to add SwashBuckle

Install-Package Swashbuckle.AspNetCore -Version 5.0.0-rc4

Once the package has been installed, navigate to the project folder and then open startup.cs and then add the middleware as you see in the below file.

       public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext(opt =>
            {
                opt.UseInMemoryDatabase("MovieList");
            });
            services.AddControllers();
 
services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "Swagger Movies Demo", Version = "v1" }); });
}

And last, you’ll need to enable Swagger and Swagger UI by updating the configure method as followed.

       public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();
            app.UseSwagger();
app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/v1/swagger.json", "Swagger Movies Demo V1"); });
app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); }

Start a new debugging session (F5) and navigate to this url http://localhost:[PORT_NUMBER]/swagger. Then, you should see Swagger UI help pages for your APIs.

All in all, let’s add a few movies to test the API. Expand the post ribbon.

After adding a few movies, let use the GET to pull all the movies.

How to Send Asp.net Core Email With Sendgrid API

I remember a few months back I wrote a quick post on how I managed to send email using MailKit in one of the projects I was on. Well, as you know there are many different services out there making thing simple to send e-mails from application. Since, I like to test things out first before actually implementing it in a real application, I decided to give Sendgrid a try. Therefore, in this post I will walk you through the steps I took to send emails using the Sendgrid API. Are you ready? Let’s do it!!! Find demo project on GitHub here.

I need to create a brand new application for this post. I am currently using Visual Studio 2019 for this project but feel free to use any other version you may have.

Project Setup

  • First, create a new ASP.NET Core application with the following name “SendgridEmailInAspNetCore”
    “File” –> “New” –> “Project”
    Create new project in Visual Studio 2019
  • A “New Project” dialog will open where you can configure your project such as giving a name and location.
    Configure new project in Visual Studio 2019
  • Make sure the project build properly.
    Now that we have the project setup we need to add the Sendgrid Package using the NuGet Package manager tool.
    Open NuGet free and open-source package manager
    Search and Find the Sendgrid Library in NuGet

Add services folder

  • Let’s add a new folder to the project and called it “Services”. This folder will contain the interface that will be used to send the e-mail.
  • Next we need to add the interface called “IEmailSender.cs”
        
    
        using System.Collections.Generic;
        using System.Threading.Tasks;
    
         namespace SendgridEmailInAspNetCore.Services
         {
           public interface IEmailSender
           {
              Task SendEmailAsync(List<string> emails, string subject, string message);
           }
         }
    
    
  • Add an Implementation class called “EmailSender.cs.
    
    using Microsoft.Extensions.Options;
    using SendGrid;
    using SendGrid.Helpers.Mail;
    using System;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    
    namespace SendgridEmailInAspNetCore.Services
    {
        public class EmailSender:IEmailSender
        {
            public EmailSender(IOptions<EmailAuthOptions> optionsAccessor)
            {
                Options = optionsAccessor.Value;
            }
    
            public EmailAuthOptions Options { get; } //set only via Secret Manager
    
            public Task SendEmailAsync(List<string> emails, string subject, string message)
            {
              
                return Execute(Environment.GetEnvironmentVariable("SENDEMAILDEMO_ENVIRONMENT_SENDGRID_KEY"), subject, message, emails);
            }
    
            public Task Execute(string apiKey, string subject, string message, List<string> emails)
            {
                var client = new SendGridClient(apiKey);
                var msg = new SendGridMessage()
                {
                    From = new EmailAddress("noreply@domain.com", "Bekenty Jean Baptiste"),
                    Subject = subject,
                    PlainTextContent = message,
                    HtmlContent = message
                };
    
                foreach (var email in emails)
                {
                    msg.AddTo(new EmailAddress(email));
                }
    
                Task response = client.SendEmailAsync(msg);
                return response;
            }
        }
    }
    
    
  • Next, we’ll add a new class in that same services folder called “EmailAuthOptions.cs” This will only contain the email secrets from sendgrid.

    namespace SendgridEmailInAspNetCore.Services
    {
        public class EmailAuthOptions
        {
            public string SendGridUser { get; set; }
            public string SendGridKey { get; set; }
        }
    }
    

Register the services

  • Find the Startup.cs file, and the ConfigureServices method. Then, add the following two lines.

            services.Configure(Configuration);
            services.AddTransient<IEmailSender, EmailSender>();
    

Setup the Environment Variable

  • Right click on the project and go to the properties. Then, click the debug tab and add a new environment variable with the following key “SENDEMAILDEMO_ENVIRONMENT_SENDGRID_KEY”. set the value to what you have gotten from Sendgrid.
    Way to configure sendgrid api key

Build E-mail Interace to capture message from user

  • Let’s add a new model called “EmailModel.cs” with the following properties.
    using System.ComponentModel.DataAnnotations;
    
    namespace SendgridEmailInAspNetCore.Models
    {
        public class EmailModel
        {
            [Required]
            [EmailAddress]
            public string Email { get; set; }
            [Required]
            public string Subject { get; set; }
            [Required]
            public string Message { get; set; }
        }
    }
    
  • Now, let’s add a new view called “SendEmail.cshtml” in the HOME folder with the following content.
    
    @model SendgridEmailInAspNetCore.Models.EmailModel
    
    @{
        ViewData["Title"] = "SendEmail";
        Layout = "~/Views/Shared/_Layout.cshtml";
    }
    
    <h2>Send Email With Sendgrid in Asp.Net Core</h2>
    
    <hr />
    <div class="row">
        <div class="col-md-4">
            <form asp-action="SendEmail">
                <div asp-validation-summary="ModelOnly" class="text-danger"></div>
                <div class="form-group">
                    <label asp-for="Email" class="control-label"></label>
                    <input asp-for="Email" class="form-control" />
                    <span asp-validation-for="Email" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label asp-for="Subject" class="control-label"></label>
                    <input asp-for="Subject" class="form-control" />
                    <span asp-validation-for="Subject" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label asp-for="Message" class="control-label"></label>
                    <textarea asp-for="Message" class="form-control" cols="100"></textarea>
                    <span asp-validation-for="Message" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <input type="submit" value="Send" class="btn btn-default" />
                </div>
            </form>
        </div>
    </div>
    
    @section Scripts {
        @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
    }
    
           
  • In order to be able to send the email in the controller we’ll need to inject the email service via the constructor so that we can have access to send the emails.
    start the start of the home controller we need to add the follow code snippet.

            
    private readonly IEmailSender _emailSender;
    
     public HomeController(IEmailSender emailSender)
     {
       _emailSender = emailSender;
     }
    
  • Now, we’re ready to add the email action methods to the controller.
        
    public IActionResult SendEmail()
    {
      return View();
    }
    public async Task SendEmail(EmailModel model)
    {
     if (ModelState.IsValid)
     {
       var emails = new List();
       emails.Add(model.Email);
       await _emailSender.SendEmailAsync(emails, model.Subject, model.Message);
     }
     return View(model);
    }
    

Final Asp.net Core Email With Sendgrid

  • Now that we have everything in place let’s run the application and Test it out.
    Voila, the application loaded with no issues. Let’s navigate to the SendEmail page. Try to send email without entering anything, you should notice the validations.
    Testing the Asp.net Core Email With Sendgrid with empty inputs
  • Now, enter a valid email, subject, and a message, then click the send button.
    Send a valid email with Asp.net Core Email With Sendgrid
  • The moment of truth. Bingo, I got the e-mail in my inbox as shown below.
    Verify that the email was sent
    Verify that the email was received.

Delete Duplicate Entries In A Sql Server Table

Couple days ago I wrote a quick post demonstrating how to find duplicate records in a SQL server table. A colleague of mine read the post and asked me to show how to delete the duplicates  from the table. Therefore, I thought it would make sense I put this post together on how to delete duplicate entries in a table.

What does it take to remove duplicates from a table?

  • First, we need to validate the data to see the duplicates we are deleting.
    WITH student_cte AS (
        SELECT 
            First_Name, 
    		Last_Name,
    		Phone, 
            ROW_NUMBER() OVER(
                PARTITION BY 
                    First_Name, 
                    Last_Name, 
                    Phone
                ORDER BY 
                    First_Name, 
                    Last_Name, 
                    Phone
            ) rn
         FROM dbo.Students
    )
    select * from student_cte;
    
  • The picture below shows the records that are duplicates with the row count.
  • Now, in order to delete these record, I am going to show the best way to handle using that same CTE query above with a slight modification. I have deleted any instance with rn > 1.
    WITH student_cte AS (
        SELECT 
            First_Name, 
    		Last_Name,
    		Phone, 
            ROW_NUMBER() OVER(
                PARTITION BY 
                    First_Name, 
                    Last_Name, 
                    Phone
                ORDER BY 
                    First_Name, 
                    Last_Name, 
                    Phone
            ) rn
         FROM dbo.Students
    )
    DELETE FROM student_cte WHERE rn >1;
    
  • After running the query above, the duplicated records are now gone.

In this post, I showed how you can get rid of duplicate rows from a table in SQL Server. I hope after reading this article you will be able to use these tips. If you have questions or feedback, please share it with me. I’d like to know what you’re thinking. Please post your feedback, question, or comments about this article.

How To Get Sendgrid API Key To Send E-mails

In Sendgrid there are 3 different types of API keys that you can generate to use in your application. Which one to use depends on your requirements need. Therefore, in this post I am going to show how to setup a simple restricted API key to be used on my next e-mail demo.

Types of API KEYS Available

  • Full Access which allows the API key to access GET, PATCH, PUT, DELETE and POST endpoints for all parts of your account, excluding billing and Email Address Validation.
  • Restricted Access, this is a customized type where you can set levels of access for all parts of your account, excluding billing and Email Address Validation.
  • Billing Access which allows the API key to access billing endpoints for the account.

Steps to create new Sendgrid API Key

  • First, you need to head over to Sendgrid.com and create an account. Once you log into the application, it will take you to the dashboard area. Click on Setting on the left pane to expand it.
  • Next, click on the “API KEY” as shown below. That will open the page to allow you to create a new API KEY.
  • Now, click on the “Create API Key” button at the top right corner, it will open the 3 types of API Key to select and the different options you need to setup.
  • For the purpose of this tutorial, I am going to set this key for sending email only.
  • Once you’re done you will get a key. The API KEY generated and displayed to you just once. So be sure to copy and save it somewhere. After that only the subset key is displayed.

Last, some notes to keep in mind.

Sendgrid only allows 100 keys per account.

Once you delete a key, it can no longer be used to access SendGrid’s services.

 

How to create a simple Todo list Using Jquery

Yes, you heard it, a simple doto list using Jquery. We all know the famous hello world applications when we’re first learning a language. Now, it’s the todo app. In this post I am going to create a simple web todo application using HTML5, CSS3 and Javascript(jQuery).
You can develop this todo application in any text editor and debug in any browser. I’m using Plunker to code this due to its simplicity. You can follow along with using this Plunker demo link

Todo list Using Jquery – Project Setup

First things first, we need to set up our project. Let’s add a new index.html file to our plunker project as shown below.

  
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <title></title>
    <link data-require="bootstrap@3.3.6" data-semver="4.1.3" rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" />
    <script data-require="bootstrap@3.3.6" data-semver="4.1.3" src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="style.css" />
    <script data-require="jquery" data-semver="3.2.1" src="https://cdn.jsdelivr.net/npm/jquery@3.2.1/dist/jquery.min.js"></script>
    <script src="script.js"></script>
  </head>

  <body>
    <div class="col-md-12">
      <h1>Simple Todo Demo!</h1>
      <p>Click on row to delete newly added entry.</p>
      <div data-role="content">
        <ul class="list-group" id="taskListSection">
          <li class="list-group-item active">Todos</li>
          <li class="list-group-item ">1. Example of Existing task</li>
          <li class="list-group-item ">2. Example of Existing task</li>
        </ul>
        <br />
        <fieldset class="ui-grid-a">
          <div class="ui-block-a">
            <textarea placeholder="Enter task" id="taskInputText" cols="50" maxlength="128"></textarea>
          </div>
          <div class="ui-block-b">
            <input type="button" class="btn btn-success" value="Submit" id="taskBtn" />
          </div>
        </fieldset>
      </div>
    </div>
  </body>

</html>
  

Nothing too fancy in that html code. We have a heading and paragraph to let user know to click newly created task. Next is an unordered list group from bootstrap 4 showing 2 items to start with. Think of those two items as existing data from a database. Next is a simple textarea to allow user to type new task entry to add to the list. Last, we have a button which will fire the add operation when user click on it.

Add script to handle add and delete tasks


// Add your javascript here
$(function(){
  $("h1").animate({
    "margin-left": "100px"
  }, "slow");
  

$('#taskBtn').click(function() {
  
    var newTask = $('#taskInputText').val();

    if(newTask !== '') {
      
        var count = $("#taskListSection").children().length;
        
        $('#taskListSection').append('<li class="list-group-item deletetask bg-success">' + count + '. '+ newTask + '</li>');
       
        $('#taskInputText').val('');
        
        deleteTasks();
        
        setTimeout(function(){
          $('#taskListSection li.bg-success').removeClass('bg-success');
        },1000); 
      
    } else {
        alert('Come on, you\'re better than that');   
    }
});  
});

function deleteTasks(){
 $('.deletetask').click(function(){
    $(this).remove();
}); 

}

Again, a simple javascript file containing animation for the heading of the page upon loading. Then, I have a click event listener for when user wants to add a new task to the list. A one second delay is added after the new entry is added to the list and row is highlighted. Then, it is unhighlited after the one second is expired.
A delete function is in there as well to remove the item from the list. Very basic.

Find Duplicate Entries In A Sql Server Table Using CTE

In my last post, I showed how to find duplicate entries in a sql server table using group by which was pretty simple to pull off. I thought it would be a good idea to find the list of all the duplicate rows using CTE for those that are fan of it. We will be using the same table from our previous post on finding duplicate rows which you can read and follow.

What is CTE

A CTE which is short for Common Table Expression, is a temporary result set that you can reference within another query statement. They are used to simplify large queries.

Find duplicate rows CTE

   WITH student_cte AS(
        SELECT  First_Name, 
                Last_Name,
                Phone,  
                COUNT(*) Occurrences
        FROM dbo.students
        GROUP BY
                First_Name, 
                Last_Name,
	        Phone
       HAVING COUNT(*) > 1
  )
  SELECT	st.Id,
		st.First_Name, 
		st.Last_Name,
		st.Phone  		
 FROM dbo.students st
 INNER JOIN student_cte 
  ON student_cte.First_Name = st.First_Name
  AND student_cte.Last_Name = st.Last_Name;

Find duplicate entries result

Find duplicate entries using cte as query

Quick Way To Find Duplicate Rows In A Sql Server Table

If you are dealing with duplicate rows in a SQL server database, and you’re not sure how to find them, don’t panic. I had to deal with a similar situation recently.
Two weeks ago, I was working on a project where I had to import some data from a legacy system into a whole new table. After the data had been loaded and a report was generated with that data, we’ve noticed there were some duplicate entries. Therefore, I had to quickly figure out how to put together a quick query to find duplicate rows and removed them from the table.

In this post I will show simple steps to take in case you are facing the same issue. In order to get this post, I am going to create a new table and populate it with some seed data.

Create Database

DROP TABLE IF EXISTS Students;
CREATE TABLE Students (
    Id INT IDENTITY(1, 1), 
    First_Name  nvarchar(100), 
    Last_Name  nvarchar(100), 
    Phone nvarchar(12),
    PRIMARY KEY(id)
);

Populate the table with data

Now that we have our table added, I will proceed to populate it with some data for our demonstration.

   INSERT INTO dbo.Students
        (first_name,last_name,phone) 
   VALUES
        ('Mary','Allan','407-455-1414'),
	('John','Doe','407-445-5414'),
        ('Chirac','Paul','407-455-1814'),
	('John','Doe','407-445-5414'),
        ('Mela','Jil','407-787-6767'),
	('John','Doe','407-445-5414'),
        ('Mary','Allan','407-455-1414'),
	('John','Doe','407-445-5414'),
        ('Mary','Allan','407-455-1414'),
	('Chris','Tai','407-555-7777'),
	('Albert','Allan','321-455-9977'),
	('Jean','Doe','407-445-5414');

Ensure the data was inserted properly

Okay, we have the data we need in the table, let’s run a quick query to see if it match our criteria, in order words have some duplicates. Let’s run a simple select statement to retrieve the full data-set in the the table.

   SELECT Id, First_Name, Last_Name,Phone 
   FROM dbo.students
   ORDER BY First_Name, Last_Name desc;

Query result to find duplicate rows in table

Find duplicate rows in the table

As a matter of fact, there are many ways to find and return the duplicates. I ended up using group by to get my result, If you end up using some other method, please share it in the comment below.

 SELECT  First_Name, 
          Last_Name,
          Phone,  
          COUNT(*) Occurrences
  FROM dbo.students
  GROUP BY
    First_Name, 
    Last_Name,
    Phone
  HAVING 
    COUNT(*) > 1;


Last, below you can see the result of the duplicated rows found and the number of time.
Find duplicate rows in sql server table