.NET Core Web API Curd Service Based On SQL Store Procedure

Introduction

 
This article explains a cross-platform .NET core web API service based on SQL store procedure, the function is multiple times are reusability and the back end process must be easy to handle large-sized projects. There are multiple ways to provide a web API service. The SQL store procedure is used to create the view table's column joins. Multiple conditions are used to provide different services, and this web service is highly secure to provide service to multiple platforms. For example, building a simple mobile contact .NET core Web API service based on a SQL store procedure service.
 

SQL Database Process

 
Step 1 - SQL Database Creation
 
The database name is MobileContact
  1. CREATE DATABASE MobileContact;  
Step 2 -Table Creation
 
The Table name is Contact
  1. CREATE TABLE Contact (  
  2. ID int NOT NULL,      
  3. Username nchar(10),        
  4. Mobile nchar(10)   
  5. PRIMARY KEY (ID)   
  6. );   
Step 3 - Table Row Insert Records 
 
Inserting Sample Records
  1. insert into Contact(Username,Mobile) Values('Barath','12345');  
  2.   
  3. insert into Contact(Username,Mobile) Values('Kumar','678910');  
Step 4 - Creating an Insert Procedure
 
The insert Procedure name is procInsert.
  1. Create PROCEDURE  procInsert  @Username nchar(10), @Mobile nchar(10)  
  2. AS      
  3.      Insert into Contact(Username,Mobile) values(@Username,@Mobile)      
  4. go      
Step 5 - Creating Update Procedure
 
The update procedure name is procUpdate
  1. Create PROCEDURE  procUpdate @ID nchar(10), @Username nchar(10), @Mobile nchar(10)  
  2. AS      
  3.      Update Contact set Username=@Username, Mobile=@Mobile where ID=@ID      
  4. go       
Step 6 - Creating  Delete Procedure
 
The Delete procedure name is procDelete.
  1. Create PROCEDURE  procDelete @ID nchar(10)    
  2. AS        
  3.     Delete from Contact where ID=@ID    
  4. go   
Step 7 - Creating Select Procedure.
 
The select procedure name is  procselect.
  1. Create PROCEDURE  procselect   
  2. AS      
  3.     Select * from Contact   
  4. go   
 Step 8 - Creating Random Select Procedure.
 
The Random Select Procedure name is procgetselect
  1. Create PROCEDURE  procgetselect @ID nchar(10)  
  2. AS      
  3.     Select * from Contact where ID=@ID  
  4. go         

.NET Core Web API Service With Visual Studio 2017 .NET Core version 2.0 and 2.1

 
Step 1 - Creating a new project
 
Go to File and select the new project.
 
 
Step 2 - The web .NET core project
 
Select the ASP.NET Core Web Application. The project name is CorewebapiService
 
 
 
Step 3 - Solution Explorer
 
Solution Explorer is taking a few minutes because of NuGet DLL updating purposes. After creating a new folder model then creates a DataLayer.cs class file.
 
 
Step 4 - Details of DataLayer.cs Class
 
Declare the Class Headers.  
  1. using System.Data;  
  2. using System.Data.SqlClient;  
SQL database connection.
  1. public static string sqlDataSource = "Data Source=.;Initial Catalog=MobileContact;Integrated Security=True";  
SQL Store procedure Call function: Full Select
  1. public DataTable GetMobileContact()  
  2.         {  
  3.             DataTable dt = new DataTable();  
  4.             try  
  5.             {  
  6.                 SqlDataReader dr;  
  7.                 using (SqlConnection con = new SqlConnection(sqlDataSource))  
  8.                 {  
  9.                     using (SqlCommand cmd = new SqlCommand("procselect", con))  
  10.                     {  
  11.                         cmd.CommandType = CommandType.StoredProcedure;          
  12.                         con.Open();  
  13.                         dr = cmd.ExecuteReader();  
  14.                         dt.Load(dr);  
  15.                         con.Close();  
  16.                     }  
  17.                 }  
  18.             }  
  19.             catch (Exception ex)  
  20.             {  
  21.                 ex.ToString();  
  22.             }  
  23.             return dt;  
  24.         }  
SQL Store procedure Call function: Random Select 
  1. public DataTable GetMobileSelectContact(string ID)  
  2.        {  
  3.            DataTable dt = new DataTable();  
  4.            try  
  5.            {  
  6.                SqlDataReader dr;  
  7.                using (SqlConnection con = new SqlConnection(sqlDataSource))  
  8.                {  
  9.                    using (SqlCommand cmd = new SqlCommand("procgetselect", con))  
  10.                    {  
  11.                        cmd.CommandType = CommandType.StoredProcedure;  
  12.                        cmd.Parameters.AddWithValue("@ID",ID.ToString());  
  13.                        con.Open();  
  14.                        dr = cmd.ExecuteReader();  
  15.                        dt.Load(dr);  
  16.                        con.Close();  
  17.                    }  
  18.                }  
  19.            }  
  20.            catch (Exception ex)  
  21.            {  
  22.                ex.ToString();  
  23.            }  
  24.            return dt;  
  25.        }  
SQL Store procedure Call function: Delete 
  1. public DataTable GetMobileDeleteContact(string ID)  
  2.        {  
  3.            DataTable dt = new DataTable();  
  4.            try  
  5.            {             
  6.                using (SqlConnection con = new SqlConnection(sqlDataSource))  
  7.                {  
  8.                    using (SqlCommand cmd = new SqlCommand("procDelete", con))  
  9.                    {  
  10.                        cmd.CommandType = CommandType.StoredProcedure;  
  11.                        cmd.Parameters.AddWithValue("@ID", ID.ToString());  
  12.                        con.Open();  
  13.                        cmd.ExecuteNonQuery();                  
  14.                        con.Close();  
  15.                    }  
  16.                }  
  17.            }  
  18.            catch (Exception ex)  
  19.            {  
  20.                ex.ToString();  
  21.            }  
  22.            return dt;  
  23.        }  
SQL Store procedure Call function: Update
  1. public DataTable GetMobileUpdateContact(string ID,string Username, string Mobile)  
  2.        {  
  3.            DataTable dt = new DataTable();  
  4.            try  
  5.            {  
  6.                using (SqlConnection con = new SqlConnection(sqlDataSource))  
  7.                {  
  8.                    using (SqlCommand cmd = new SqlCommand("procUpdate", con))  
  9.                    {  
  10.                        cmd.CommandType = CommandType.StoredProcedure;  
  11.                        cmd.Parameters.AddWithValue("@ID", ID.ToString());  
  12.                        cmd.Parameters.AddWithValue("@Username", Username.ToString());  
  13.                        cmd.Parameters.AddWithValue("@Mobile", Mobile.ToString());  
  14.                        con.Open();  
  15.                        cmd.ExecuteNonQuery();  
  16.                        con.Close();  
  17.                    }  
  18.                }  
  19.            }  
  20.            catch (Exception ex)  
  21.            {  
  22.                ex.ToString();  
  23.            }  
  24.            return dt;  
  25.        }  
SQL Store procedure Call function: Insert
  1. public DataTable GetMobileInsertContact(string Username, string Mobile)  
  2.        {  
  3.            DataTable dt = new DataTable();  
  4.            try  
  5.            {  
  6.                using (SqlConnection con = new SqlConnection(sqlDataSource))  
  7.                {  
  8.                    using (SqlCommand cmd = new SqlCommand("procInsert", con))  
  9.                    {  
  10.                        cmd.CommandType = CommandType.StoredProcedure;                         
  11.                        cmd.Parameters.AddWithValue("@Username", Username.ToString());  
  12.                        cmd.Parameters.AddWithValue("@Mobile", Mobile.ToString());  
  13.                        con.Open();  
  14.                        cmd.ExecuteNonQuery();  
  15.                        con.Close();  
  16.                    }  
  17.                }  
  18.            }  
  19.            catch (Exception ex)  
  20.            {  
  21.                ex.ToString();  
  22.            }  
  23.            return dt;  
  24.        }  
Step 5 - Details of ValuesController.cs
 
Declare the ValuesController Class Headers 
  1. using System.Collections.Generic;  
  2. using Microsoft.AspNetCore.Mvc;    
  3. using CorewebapiService.Model;    
  4. using System.Data;    
Call the Model folder DataLayer Class.
  1. DataLayer db = new DataLayer();  
Web Api Service Get all values: [HttpGet]
  1. [HttpGet]  
  2.  public ActionResult<IEnumerable<string>> Get()  
  3.  {             
  4.      DataTable dt = db.GetMobileContact();  
  5.      var result = new ObjectResult(dt);  
  6.      return result;  
  7.  }  
Web API Service Get selected values: [HttpGet("GetMobile")]
  1. [HttpGet("GetMobile")]  
  2. public ActionResult<IEnumerable<string>> Getset(string ID)  
  3. {  
  4.     DataTable dt = db.GetMobileSelectContact(ID.ToString());  
  5.     var result = new ObjectResult(dt);  
  6.     return result;       
  7. }  
Web API Service Delete: [HttpDelete]
  1. [HttpDelete]  
  2. public ActionResult<IEnumerable<string>> Delete(string ID)  
  3. {             
  4.     DataTable dt = db.GetMobileDeleteContact(ID.ToString());  
  5.     var result = new ObjectResult(dt);  
  6.     return result;  
  7. }  
Web API Service Update: [HttpPut("GetMobileUpdate")]
  1. [HttpPut("GetMobileUpdate")]  
  2. public ActionResult<IEnumerable<string>> Update(string ID,string Username,string Mobile)  
  3. {  
  4.     DataTable dt = db.GetMobileUpdateContact(ID.ToString(),Username.ToString(),Mobile.ToString());  
  5.     var result = new ObjectResult(dt);  
  6.     return result;  
  7. }  
Web API Service Insert: [HttpPost("GetMobileInsert")]
  1. [HttpPost("GetMobileInsert")]  
  2. public ActionResult<IEnumerable<string>> Insert(string Username, string Mobile)  
  3. {  
  4.     DataTable dt = db.GetMobileInsertContact(Username.ToString(), Mobile.ToString());  
  5.     var result = new ObjectResult(dt);  
  6.     return result;  
  7. }  
Step 6 - DLL Reference 
 
Add a reference or update or remove to install the NuGet DLL packages.
 
 

Postman - .NET Core Web API Service call functions

 
The postman is an easy way to parse the .NET Core Web API Service. 
 
[HttpGet], [HttpGet("GetMobile")], [HttpDelete], [HttpPut("GetMobileUpdate")]
, [HttpPost("GetMobileInsert")]
 
Step1 - [HttpGet] 
 
Select Drop Down GET and paste the URL to click the send button.
 
 
Step 2 - [HttpDelete]
  
Select Drop Down DELETE and Refer the below screenshots to parse the delete values.
 
 
Setp 3 - [HttpGet("GetMobile")]
 
Select Drop Down GET and Parse the selected ID values.
 
 
Step 4 - [HttpPost("GetMobileInsert")]
  
Select dropdown POST (INSERT) and Refer the below screenshots to parse the Insert values.
 
 
Step 5 - [HttpPut("GetMobileUpdate")]
  
Select Drop Down PUT (Update) and Refer the below screenshots to parse the Updated values.  
 
 

Summary

 
This .NET Core Web API Services is used to host multiple platforms. Apache Web Server supports hosting on Linux operating system, And IIS, Windows Server also is supported, etc. Try to implement it on Oracle Database, MySQL Database, etc... I hope this method helps you to SQL store procedure based .NET core web API service reset.