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
CREATE DATABASE MobileContact;
Step 2 -Table Creation
The Table name is Contact.
CREATE TABLE Contact (
ID int NOT NULL,
Username nchar(10),
Mobile nchar(10)
PRIMARY KEY (ID)
);
Step 3 - Table Row Insert Records
Inserting Sample Records
insert into Contact(Username,Mobile) Values('Barath','12345');
insert into Contact(Username,Mobile) Values('Kumar','678910');
Step 4 - Creating an Insert Procedure
The insert Procedure name is procInsert.
Create PROCEDURE procInsert @Username nchar(10), @Mobile nchar(10)
AS
Insert into Contact(Username,Mobile) values(@Username,@Mobile)
go
Step 5 - Creating Update Procedure
The update procedure name is procUpdate.
Create PROCEDURE procUpdate @ID nchar(10), @Username nchar(10), @Mobile nchar(10)
AS
Update Contact set Username=@Username, Mobile=@Mobile where ID=@ID
go
Step 6 - Creating Delete Procedure
The Delete procedure name is procDelete.
Create PROCEDURE procDelete @ID nchar(10)
AS
Delete from Contact where ID=@ID
go
Step 7 - Creating Select Procedure.
The select procedure name is procselect.
Create PROCEDURE procselect
AS
Select * from Contact
go
Step 8 - Creating Random Select Procedure.
The Random Select Procedure name is procgetselect
Create PROCEDURE procgetselect @ID nchar(10)
AS
Select * from Contact where ID=@ID
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.
using System.Data;
using System.Data.SqlClient;
SQL database connection.
public static string sqlDataSource = "Data Source=.;Initial Catalog=MobileContact;Integrated Security=True";
SQL Store procedure Call function: Full Select
public DataTable GetMobileContact()
{
DataTable dt = new DataTable();
try
{
SqlDataReader dr;
using (SqlConnection con = new SqlConnection(sqlDataSource))
{
using (SqlCommand cmd = new SqlCommand("procselect", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
}
}
}
catch (Exception ex)
{
ex.ToString();
}
return dt;
}
SQL Store procedure Call function: Random Select
public DataTable GetMobileSelectContact(string ID)
{
DataTable dt = new DataTable();
try
{
SqlDataReader dr;
using (SqlConnection con = new SqlConnection(sqlDataSource))
{
using (SqlCommand cmd = new SqlCommand("procgetselect", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID",ID.ToString());
con.Open();
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
}
}
}
catch (Exception ex)
{
ex.ToString();
}
return dt;
}
SQL Store procedure Call function: Delete
public DataTable GetMobileDeleteContact(string ID)
{
try
{
using (SqlConnection con = new SqlConnection(sqlDataSource))
{
using (SqlCommand cmd = new SqlCommand("procDelete", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", ID.ToString());
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (Exception ex)
{
ex.ToString();
}
}
SQL Store procedure Call function: Update
public DataTable GetMobileUpdateContact(string ID,string Username, string Mobile)
{
try
{
using (SqlConnection con = new SqlConnection(sqlDataSource))
{
using (SqlCommand cmd = new SqlCommand("procUpdate", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", ID.ToString());
cmd.Parameters.AddWithValue("@Username", Username.ToString());
cmd.Parameters.AddWithValue("@Mobile", Mobile.ToString());
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (Exception ex)
{
ex.ToString();
}
}
SQL Store procedure Call function: Insert
public DataTable GetMobileInsertContact(string Username, string Mobile)
{
try
{
using (SqlConnection con = new SqlConnection(sqlDataSource))
{
using (SqlCommand cmd = new SqlCommand("procInsert", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", Username.ToString());
cmd.Parameters.AddWithValue("@Mobile", Mobile.ToString());
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
catch (Exception ex)
{
ex.ToString();
}
}
Step 5 - Details of ValuesController.cs
Declare the ValuesController Class Headers
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using CorewebapiService.Model;
using System.Data;
Call the Model folder DataLayer Class.
DataLayer db = new DataLayer();
Web Api Service Get all values: [HttpGet]
[HttpGet]
public ActionResult<IEnumerable<string>> Get()
{
DataTable dt = db.GetMobileContact();
var result = new ObjectResult(dt);
return result;
}
Web API Service Get selected values: [HttpGet("GetMobile")]
[HttpGet("GetMobile")]
public ActionResult<IEnumerable<string>> Getset(string ID)
{
DataTable dt = db.GetMobileSelectContact(ID.ToString());
var result = new ObjectResult(dt);
return result;
}
Web API Service Delete: [HttpDelete]
[HttpDelete]
public ActionResult<IEnumerable<string>> Delete(string ID)
{
DataTable dt = db.GetMobileDeleteContact(ID.ToString());
var result = new ObjectResult(dt);
return result;
}
Web API Service Update: [HttpPut("GetMobileUpdate")]
[HttpPut("GetMobileUpdate")]
public ActionResult<IEnumerable<string>> Update(string ID,string Username,string Mobile)
{
DataTable dt = db.GetMobileUpdateContact(ID.ToString(),Username.ToString(),Mobile.ToString());
var result = new ObjectResult(dt);
return result;
}
Web API Service Insert: [HttpPost("GetMobileInsert")]
[HttpPost("GetMobileInsert")]
public ActionResult<IEnumerable<string>> Insert(string Username, string Mobile)
{
DataTable dt = db.GetMobileInsertContact(Username.ToString(), Mobile.ToString());
var result = new ObjectResult(dt);
return result;
}
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.