How to create ASP.NET Core Web API with Azure MySQL Server.

Introduction

In this article, we are going to discuss Asp.net Core Web Api.


We are going to cover:

  1. What is API?
  2. What is Web API?
  3. Why Web Api required?
  4. How to create Asp.net Core web API using .Net 3.1

Prerequisites

  1. .NET 3.1
  2. Visual Studio 2019

What is an API?

Basically, an API is a set of routines, protocols, and tools developers use to build software applications and to specify how software components should interact. In simple terms, an API is a messenger that sends a request to a provider you want data from and then delivers the response back to you. Think of an API as an intermediary that allows two programs to talk to each other.


Why is Web API required?

The user wants to access the application from different devices like mobile, browser, Google devices, etc. In this case, Web API can be useful.Different devices request to Web API and Web API will respond in JSON format. Most of the devices are able to understand JSON output. 

Let’s see the below web Api Architecture diagram,


Web API is mostly used for CRED (Create, Read, EDIT, DELETE) operations. It  follows HTTP verbs for these operations.


  • HTTP GET – Read Operation
  • HTTP POST – Create Operation
  • HTTP PUT – Update Operation
  • HTTP DELETE – Delete Operation

How to create an Asp.Net core web API?


Follow the below steps to create your first Web API project,

1.       Open Visual Studio 2019 and create a new project.

 

2. Select the “Asp.Net Core Web API” template and click on Next.

3.      Provide Project name and location.

4.    Select Target Framework and click on Create button.


5.       Once project is create basic view in solution explore.

6.   Next we need to create two folder with names Controllers and Models.

7.       Need to install dependency from Genet packages.

MySql.Data.dll

Microsoft.AspNetCore.Mvc.NewtonsoftJson.dll – version 3.1.10

8.       To connect Mysql we need added server name, user name and password in appsettings.json.

{

  "ConnectionStrings": {

    "EmployeeAppcon": "Data Source=*****.mysql.database.azure.com;initial catalog=”DateBaseName”;User id=******; Pwd=******"

  },

    "Logging": {

      "LogLevel": {

        "Default": "Information",

        "Microsoft": "Warning",

        "Microsoft.Hosting.Lifetime": "Information"

      }

    },

    "AllowedHosts": "*"

  }

 

9.       We need update Startup.cs with below code.

using Microsoft.AspNetCore.Authentication;

using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting;

using Microsoft.AspNetCore.Http;

using Microsoft.Extensions.Configuration;

using Microsoft.Extensions.DependencyInjection;

using Microsoft.Extensions.Hosting;

using Newtonsoft.Json.Serialization;

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Linq;

using System.Threading.Tasks;

 

namespace createwebapiusingmysql

{

    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.

        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940

        public void ConfigureServices(IServiceCollection services)

        {

            // Need to add cors for webapi

            services.AddCors(c =>

            {

                c.AddPolicy("AllowOrgin", options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

            });

 

          

            services.AddControllersWithViews().AddNewtonsoftJson(options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore)

                .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());

            // We need added controllers

            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)

        {

            //enable Cors

            app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

            if (env.IsDevelopment())

            {

                app.UseDeveloperExceptionPage();

            }

 

            app.UseRouting();

 

            app.UseEndpoints(endpoints =>

            {

                app.UseEndpoints(endpoints =>

                {

                    endpoints.MapControllers();

                });

            });

        }

    }

}

 

10.    In the model folder, create a Product’s class to storage set and get property.

 



 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

 

namespace createwebapiusingmysql.Models

{

    public class Products

    {

        public int categoryId { get; set; }

 

        public int ProductId { get; set; }

 

        public string ProductName { get; set; }

 

        public string ProductDescription { get; set; }

 

        public string ProductManual { get; set; }

 

        public string ProductSpecification { get; set; }

 

        public string ProductWarranty { get; set; }

 

        public string ProductFeatures { get; set; }

 

        public string ProductModel { get; set; }

 

        public string ProductCompany { get; set; }

 

        public string ProductPreview { get; set; }

 

        public string ProductAuthor { get; set; }

        public string createdBy { get; set; }

    }

}

 

11.  Next create products controller in control folder to write get and post methods.

 



 



 

 

12.  Add below code productscontroll.cs

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Configuration;

using MySql.Data.MySqlClient;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Threading.Tasks;

 

namespace createwebapiusingmysql.Controllers

{

    [Route("api/[controller]")]

    [ApiController]

    public class ProductsController : Controller

    {

        private readonly IConfiguration _configuration;

        public ProductsController(IConfiguration configuration)

        {

            _configuration = configuration;

        }

 

        // Get All products from mysql table

        [HttpGet]

        public JsonResult Getproducts()

        {

            string query = @"select * from tableName;";

            DataTable table = new DataTable();

            string sqlDataSource = _configuration.GetConnectionString("EmployeeAppcon");

            MySqlDataReader myReader;

            using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))

            {

                mycon.Open();

                using (MySqlCommand mycommond = new MySqlCommand(query, mycon))

                {

                    myReader = mycommond.ExecuteReader();

                    table.Load(myReader);

 

                    myReader.Close();

                    mycon.Close();

                }

            }

            return new JsonResult(table);

        }

    }

}

 

13.  Next Need to create table in Mysql server in azure:

 

create table databaseName.products(

ProductId INT AUTO_INCREMENT PRIMARY KEY,

ProductName nvarchar(50),

categoryid int,

productDescription nvarchar(500),

productManual nvarchar(50),

productSpecification nvarchar(500),

productWarranty nvarchar(100),

productFeatures nvarchar(100),

productModel nvarchar(100),

productCompany nvarchar(50),

productPreview nvarchar(500),

productCreated date,

productAuthor nvarchar(100),

foreign key (categoryid)

references categories(categoryid)

);

 

14.   Final result in postman.

 

 



 

15.       Add new product in product table.

15.1   First we need to create stored procedures in MySQL database.



 

15.2    Create stored procedures with below MySql commands.

CREATE DEFINER=`ceeaadmin`@`%` PROCEDURE `Add_categorie_products`(

       fcategoryid varchar(50),

    fproductname varchar(50),

    fproductDescription varchar(500),

    fproductManual varchar(500),

    fproductSpecification varchar(100),

    fproductWarranty varchar(100),

    fproductFeatures varchar(100),

    fproductModel varchar(100),

    fproductCompany varchar(50),

    fproductPreview varchar(500),

    fproductAuthor varchar(50)

)

BEGIN

DECLARE ProductId  INT DEFAULT 0;

START TRANSACTION;

 

INSERT INTO restapi.Products(ProductName,categoryid,productDescription,productManual,productSpecification,productWarranty,

productFeatures,productModel,productCompany,productPreview,productCreated,productAuthor)

    VALUES(fproductname,fcategoryid,fproductDescription,fproductManual,fproductSpecification,fproductWarranty,

    fproductFeatures,fproductModel,fproductCompany,fproductPreview,curdate(),fproductAuthor);

    SET ProductId = LAST_INSERT_ID();

        COMMIT;

    SET ProductId := (select last_insert_id());

    select categoryName,ProductId from restapi.categories,restapi.products where categories.categoryid = products.categoryid AND products.ProductId=ProductId;

END

15.3    Please create [httppost] method and add below code in controller. 

// To Insert New record

        [HttpPost]

        public JsonResult Post(Products prd)

        {

            DataTable table = new DataTable();

            string sqlDataSource = _configuration.GetConnectionString("EmployeeAppcon");

            MySqlDataReader myReader;

            using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))

            {

                try

                {

// To Insert New record using storage process

 

                    using (MySqlCommand mycommond = new MySqlCommand("restapi.Add_categorie_products", mycon))

                    {

                        mycommond.CommandType = CommandType.StoredProcedure;

 

                        mycommond.Parameters.AddWithValue("@fcategoryid", prd.categoryId);

 

                        mycommond.Parameters.AddWithValue("@fproductname", prd.ProductName);

                        mycommond.Parameters.AddWithValue("@fproductDescription", prd.ProductDescription);

                        mycommond.Parameters.AddWithValue("@fproductManual", prd.ProductManual);

                        mycommond.Parameters.AddWithValue("@fproductSpecification", prd.ProductSpecification);

                        mycommond.Parameters.AddWithValue("@fproductWarranty", prd.ProductWarranty);

                        mycommond.Parameters.AddWithValue("@fproductFeatures", prd.ProductFeatures);

                        mycommond.Parameters.AddWithValue("@fproductModel", prd.ProductModel);

                        mycommond.Parameters.AddWithValue("@fproductCompany", prd.ProductCompany);

                        mycommond.Parameters.AddWithValue("@fproductPreview", prd.ProductPreview);

                        mycommond.Parameters.AddWithValue("@fproductAuthor", prd.ProductAuthor);

 

 

                        mycon.Open();

 

                        myReader = mycommond.ExecuteReader();

                        table.Load(myReader);

 

                        myReader.Close();

                        mycon.Close();

                    }

                }

                catch (Exception ex)

                {

                    string x = ex.Message.ToString();

                    string error = ex.StackTrace.ToString();

                }

 

            }

            return new JsonResult(table);

        }

15.4  Please use below json format to insert product using postman.

{

        "categoryid":"1",

        "ProductName": "Hammers",

        "productModel": "modelurl",

        "productAuthor": "F1255RDS2792",

        "productCompany": "BOSCH_TEST",

        "productDescription": "BOSCH",

        "productManual": "Washer is able to perform various drum actions or a combination of\ndifferent actions depending on th",

        "productSpecification": "686 mm(L) x 767 mm(P) x 982 mm(H)\n",

        "productWarranty": "Labor 2 Year / Parts 2 Year",

        "productFeatures": "Good to user",

        "productPreview": "imageurl"   

    }

 



16.       To update product using post man.

16.1    Add below code in product controller.

// To Update the record

        [HttpPut("{id}")]

        public JsonResult Put(int id, Products prd)

        {

            string query = @"update restapi.products set

                                ProductName = @ProductName,

                                ProductDescription = @ProductDescription,

                                productManual = @ProductManual,

                                ProductModel = @ProductModel,

                                ProductSpecification = @ProductSpecification,

                                ProductWarranty = @ProductWarranty,

                                productFeatures = @productFeatures,

                                productPreview =@productPreview,

                                ProductCompany = @ProductCompany,

                                ProductAuthor = @ProductAuthor

                                where ProductId =@ProductId;";

 

            DataTable table = new DataTable();

            string sqlDataSource = _configuration.GetConnectionString("EmployeeAppcon");

            MySqlDataReader myReader;

            try

            {

                using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))

                {

                    mycon.Open();

                    using (MySqlCommand mycommond = new MySqlCommand(query, mycon))

                    {

 

                        mycommond.Parameters.AddWithValue("@ProductId", id);

                        mycommond.Parameters.AddWithValue("@ProductName", prd.ProductName);

                        mycommond.Parameters.AddWithValue("@ProductDescription", prd.ProductDescription);

                        mycommond.Parameters.AddWithValue("@ProductManual", prd.ProductManual);

                        mycommond.Parameters.AddWithValue("@ProductModel", prd.ProductModel);

                        mycommond.Parameters.AddWithValue("@ProductSpecification", prd.ProductSpecification);

                        mycommond.Parameters.AddWithValue("@ProductWarranty", prd.ProductWarranty);

                        mycommond.Parameters.AddWithValue("@productFeatures", prd.ProductFeatures);

                        mycommond.Parameters.AddWithValue("@productPreview", prd.ProductPreview);

                        mycommond.Parameters.AddWithValue("@ProductCompany", prd.ProductCompany);

                        mycommond.Parameters.AddWithValue("@ProductAuthor", prd.ProductAuthor);

                        //mycon.Open();

                        myReader = mycommond.ExecuteReader();

                        table.Load(myReader);

 

                        myReader.Close();

                        mycon.Close();

                    }

                }

            }

            catch (Exception ex)

            {

                string x = ex.Message.ToString();

                string error = ex.StackTrace.ToString();

            }

            return new JsonResult("Product Updated Sucessfully");

        }


Reference links:

https://www.youtube.com/watch?v=Br8H-TuSSO8













Comments

Post a Comment

Popular posts from this blog

Create Data Package file to Import Or Export CRM records.

Image
  The Microsoft Dynamics CRM 2015 Software Development Kit (SDK) Migration tool is essential for exporting and importing configuration data. It is particularly useful for transferring metadata, customizations, and configurations between environments. Download Link: https://www.microsoft.com/en-in/download/details.aspx?id=44567 After downloading Microsoft Dynamics CRM 2015 Software Development Kit (SDK) unzip and Navigate to MicrosoftDynamicsCRM2015SDK\Tools\ConfigurationMigration. ·        To Open SDK tool MicrosoftDynamicsCRM2015SDK\Tools\ConfigurationMigration\ DataMigrationUtility.exe ·        First, we need to Create Schema. Select on Create Schema and click on continue. ·        It be asked to log in to your source CRM system, Select Display list of available organizations and give your username and passed and logging. ·        Please select your org...
Read more

Receive Incoming Message in Twilio using Power Automate

Image
  In this blog we will learn how to read when an incoming message received in Twilio using Power Automate. ·        Power Automate Flow creation. ·        Configuration power automate flow URL in Twilio.   Power Automate Flow creation. Open https://make.powerapps.com/   and create a new automated flow in Power Automate using the "When an HTTP request is received" trigger. To do this, search for "HTTP" and select the HTTP request trigger, as shown below. Give the required flow name and click on Create. Next add Compose compound and add below formal  to read input from above step. decodeUriComponent(string(triggerBody())) Next to read received message, from number and to number we need each initial variables in flow. From Number add below formal in value:  first(split(split(outputs('Compose'), 'From=')[1], '&')) To Number add below formal in value : first(split(split(ou...
Read more

Azure Function to Read Incoming Message in Twilio

Image
  Introduction In this tutorial, we will learn how to read incoming message in Twilio and respond to webhooks using Azure Functions. We will learn what is webhooks, how to set up an Azure Function, and then use Azure Function app to respond to a Twilio SMS webhook and create a record in dynamic 365 in case entity when new message received. Prerequisites: A Twilio account.  An Azure account with an active subscription. Dynamic365 subscription. What is webhook? Webhook is an HTTP request triggered by an event in a source system and sent to a destination system. To enable this, you provide the source system with a URL where it can send the HTTP request. A typical webhook setup involves the following: A destination system that needs to be notified about a specific event. A source system that sends a notification to the destination system when the event occurs. This system uses the provided webhook URL to make the HTTP request. In your a...
Read more

Open canvas app with Customize the command bar using command designer

Image
  This topic guides you through creating and editing modern commands using the command designer and Power Fx. Create a new model-driven app using modern app designer 1.      Sign into  Power Apps 2.      On the left navigation pane, select  Solutions  and then open or create a solution to contain the new model-driven app. 3.      Select  New  >  App  >  Model-driven app 4.      Select  Modern app designer , and then select  Create .    Enter a  Name  for your app, and then select  Create . More information:  Create a model-driven app that has an account table page 1.      On the left navigation pane, select  Solutions , and then open the solution containing the existing model-driven app. 2.      Select the model-driven app, and then select --  >  Edit ...
Read more

Execute CRUD operations within the Power Platform using a Canvas App

Image
  User story: When a new account is created, there is a requirement to execute a curd operation via Power Apps, involving updating an existing record and deleting the record from the accounts. If we want to few complete records in accounts entity on welcome screen: Navigate to vertical Gallery and select Items property should be Item=Accounts. To create a new record, one must click on the plus icon displayed on the screen and input the formula provided below, as illustrated in the following screen. This action will prompt a new screen to open for the creation of the new record.   Navigate ( CreateScreen ) ; ResetForm ( 'Account Create Form' ) After navigating to the creation screen, a new form will be displayed, and it is necessary to configure the data source to 'Account,' as shown in the screen below. And Items property = Defaults (Accounts) Next, upon clicking the submit button, we should save the data in the account entity and return to the main screen. ...
Read more