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







Luis GalindoPosted Aug 11, 2020, 8:52 PM
Hi, why do you return an empty Datatable after insert and update methods in DataLayer.cs??
Hamid KhanPosted Aug 4, 2020, 1:13 PM
Good explanation with SP example in dotnet core.....................