Here we learn how to do setup for REST API project and perform CRUD Operation using C#
let's dive into process :
Step 1 : First open Visual Studio and Create an API project (I am using Visual Studio 2022 .)
Click on Create a new project :
Now Choose API Template and Click on Next Button :
Now Configure your project Name and project location :
Now you will see below Attached Screen and Click and Next Button :
your solution Explorer looks like below attached screen and you can first run your project it gives by default weatherForcastController :
When you will run your project , will open like below screen:
Step 2 : Add a Controller >> Right click on Controller folder >> Add>> New Item
Choose API Controller - Empty , give Appropriate name to your controller and click and Add Button :
your controller looks like below Screen :
Step 3 : Now Add a Model Folder and add a class named StudentRequest with properties representing student information such as studentId, studentName, emailId, gender, and dob :
- Right click on project >> Add>> New Folder
- Right click on New Folder >> Add >> New Item >>Class
Step 4 : Now add a folder ServiceLayer and inside add a Inteface and a class .
- Right click on project >>Add >> New Folder
- Right click on New Folder >>Add>>New Item>>Interface
- Right click on New Folder >> Add>>New Item>>Class
Service:
Step 5 : Now add a folder for repository layer (Data Access layer), And add a Inteface and a class .
- Right click on project >>Add >> New Folder
- Right click on New Folder >>Add>>New Item>>Interface
- Right click on New Folder >> Add>>New Item>>Class
Repositroy :
Step 6 : Now add a class for Injecting Dependencies .
- Right click on your project and a add class
- Add >> New Item >> Class
Step 7 : Now add your service and Repository in "InjectDependency class"
Step 8 : Now need to add this InjectDependency to Program.cs file as below given highlighted in red color :
Program.cs
Step 9 : Now Connect SQL Server with Visual Studio (Add ConnectionString to appsetting.json file)
- Open MS SQL Server >> And Copy Server Name (if your server Hosted then IP)
Now go to Visual Studio >> View >> Server Explorer >> Connect To Database >> And paste here your SQL Server Name or IP :
If SQL is locally then choose Authentication : Windows Authentication
Here will be displayed already created database names :
and choose database : Ex . my database is CodeWithSurya_DB And press ok button
Now SQL and Visual Studio is connected each other :
Click on Data Connections and press right click and choose properties and copy connectionString
Copy ConnectionString :
Step 10 : Now go to appsetting.json file and add a key ConnectionString and paste copied connectionString :
Now your project setup is Completed : Let's move to the Code :
Controller Code :
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using WebApplication1.Model;
using WebApplication1.ServiceLayer;
namespace WebApplication1.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class CrudOperationController : ControllerBase
{
private readonly ICRUDOperationSL ServiceLayer;
public CrudOperationController(ICRUDOperationSL sl)
{
ServiceLayer = sl;
}
[HttpPost]
[Route("InsertUpdateRecord")]
public async Task<IActionResult> InsertUpdateRecord(StudentRequest req)
{
return Ok(await this.ServiceLayer.InsertUpdateRecord(req));
}
[HttpGet]
[Route("GetStudnetData")]
public async Task<IActionResult> GetStudnetData(string studentId="0")
{
return Ok(await this.ServiceLayer.GetStudnetData(studentId));
}
[HttpDelete]
[Route("DeleteStudentData")]
public async Task<IActionResult> DeleteStudentData(string studentId )
{
return Ok(await this.ServiceLayer.DeleteStudentData(studentId));
}
}
}
Student class Code :
namespace WebApplication1.Model
{
public class StudentRequest
{
public int studentId { get; set; }
public string studentName { get; set; }
public string emailId { get; set; }
public string gender { get; set; }
public string dob { get; set; }
}
public class StudentResponse
{
public string status { get; set; }
public string message { get; set; }
public string data { get; set; }
}
public class GetStudentData
{
public int studentId { get; set; }
public string studentName { get; set; }
public string emailId { get; set; }
public string gender { get; set; }
public string dob { get; set; }
}
}
ICRUDOperationSL interface Code :
using WebApplication1.Model;
namespace WebApplication1.ServiceLayer
{
public interface ICRUDOperationSL
{
public Task<StudentResponse> InsertUpdateRecord(StudentRequest req);
public Task<List<GetStudentData>> GetStudnetData(string studentId);
public Task<StudentResponse> DeleteStudentData(string studentId);
}
}
CRUDOperationSL class Code :
using WebApplication1.Model;
using WebApplication1.RepositoryLayer;
namespace WebApplication1.ServiceLayer
{
public class CRUDOPerationSL :ICRUDOperationSL
{
private readonly ICRUDOperationRL repo;
public CRUDOPerationSL(ICRUDOperationRL _rp)
{
repo = _rp;
}
public async Task<StudentResponse> InsertUpdateRecord(StudentRequest req)
{
return await repo.InsertUpdateRecord(req);
}
public async Task<List<GetStudentData>> GetStudnetData(string studentId)
{
return await repo.GetStudnetData(studentId);
}
public async Task<StudentResponse> DeleteStudentData(string studentId)
{
return await repo.DeleteStudentData(studentId);
}
}
}
ICRUDOperationRL interface Code :
using WebApplication1.Model;
namespace WebApplication1.RepositoryLayer
{
public interface ICRUDOperationRL
{
public Task<StudentResponse> InsertUpdateRecord(StudentRequest req);
public Task<List<GetStudentData>> GetStudnetData(string studentId);
public Task<StudentResponse> DeleteStudentData(string studentId);
}
}
CRUDOperationRL class Code :
using Microsoft.Data.SqlClient;
using System.Data;
using WebApplication1.Model;
namespace WebApplication1.RepositoryLayer
{
public class CRUDOperationRL:ICRUDOperationRL
{
private readonly IConfiguration configuration;
public CRUDOperationRL(IConfiguration _configuration)
{
this.configuration = _configuration;
}
public async Task<StudentResponse> InsertUpdateRecord(StudentRequest req)
{
StudentResponse obj = new StudentResponse();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@StudentId", req.studentId);
cmd.Parameters.AddWithValue("@StudentName", req.studentName);
cmd.Parameters.AddWithValue("@Email", req.emailId);
cmd.Parameters.AddWithValue("@DOB", req.dob);
cmd.Parameters.AddWithValue("@Gender", req.gender);
DataTable dt = await ExecuteProcedure(cmd, "USP_InsertUpdateStudentData");
if (dt != null && dt.Rows.Count > 0)
{
obj.status = "200";
obj.message = dt.Rows[0]["Result"].ToString();
}
}
catch (Exception ex)
{
obj.status = "500";
obj.message = ex.Message;
}
return obj;
}
public async Task<List<GetStudentData>> GetStudnetData(string studentId)
{
List<GetStudentData> res = new List<GetStudentData>();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@StudentId", studentId);
DataTable dt = await ExecuteProcedure(cmd, "USP_ShowStudentDetails");
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow rw in dt.Rows)
{
GetStudentData obj = new GetStudentData();
obj.studentId = Convert.ToInt32(rw["StudentID"]);
obj.studentName = Convert.ToString(rw["StudentName"]);
obj.emailId= Convert.ToString(rw["Email"]);
obj.dob = Convert.ToString(rw["DOB"]);
obj.gender = Convert.ToString(rw["Gender"]);
res.Add(obj);
}
}
}
catch (Exception ex)
{
throw new Exception();
}
return res;
}
public async Task<StudentResponse> DeleteStudentData(string studentId)
{
StudentResponse obj = new StudentResponse();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@StudentID", studentId);
DataTable dt = await ExecuteProcedure(cmd, "USP_Delete_StudentDetails");
if (dt != null && dt.Rows.Count > 0)
{
obj.status = "200";
obj.message = dt.Rows[0]["Result"].ToString();
}
}
catch (Exception ex)
{
obj.status = "500";
obj.message = ex.Message;
}
return obj;
}
public async Task<DataTable> ExecuteProcedure(SqlCommand cmd, string ProcedureName)
{
try
{
string connectionString = configuration.GetConnectionString("DBString");
using (SqlConnection con = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
SqlDataAdapter dr = new SqlDataAdapter(cmd);
dr.Fill(dt);
return dt;
}
}
catch (Exception ex)
{
throw;
}
}
}
}
Program.cs Code :
using Microsoft.AspNetCore.Mvc.Razor;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
ConfigurationManager configuration = builder.Configuration;
builder.Services.AddControllers();
builder = new WebApplication1.InjectDependency().InjectDepency(builder);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddControllersWithViews()
.AddViewLocalization(LanguageViewLocationExpanderFormat.Suffix)
.AddDataAnnotationsLocalization();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
appsetting.json code :
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {// put here your connection string
"DBString": "Data Source=DESKTOP-SURYA\\SQLEXPRESS;Initial Catalog=CodeWithSurya_DB;Encrypt=false;Integrated
Security=True;Trust Server Certificate=True"
}
}
InjectDependency class Code :
using System;
using Microsoft.OpenApi.Models;
using WebApplication1.RepositoryLayer;
using WebApplication1.ServiceLayer;
namespace WebApplication1
{
public class InjectDependency
{
public WebApplicationBuilder InjectDepency(WebApplicationBuilder builder)
{
ConfigurationManager con = builder.Configuration;
builder.Services.AddSingleton(con);
builder.Services.AddScoped(typeof(ICRUDOperationSL), typeof(CRUDOPerationSL));
builder.Services.AddScoped(typeof(ICRUDOperationRL), typeof(CRUDOperationRL));
builder.Services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "CRUD_WEB_API", Version = "v1" });
});
return builder;
}
}
}
SQL Procedure and Table Code :
/*Create Table*/
create Table StudentDetails
(
StudentID Int Identity Constraint PK_StudentDetails_StudentID Primary Key(StudentID),
StudentName Varchar(200),
Email Varchar(50),
DOB DateTime,
Gender Varchar(20),
CreatedDate DateTime Constraint DF_StudentDetails_CreatedDate Default(GetDate()),
ModifiedBy Varchar(50),
ModifiedDate DateTime
)
Go
/*Procedure for data insert and update*/
Create proc USP_InsertUpdateStudentData
(
@StudentId Int,
@StudentName Varchar(200),
@Email Varchar(50),
@DOB DateTime,
@Gender Varchar(20)
)
As
Begin
Begin Try
Declare @result Varchar(Max)='',@Status Int
Begin Tran
IF(@StudentId<>0)
Begin
Update StudentDetails Set StudentName=@StudentName
,Email=@Email,DOB=@DOB,Gender=@Gender,
ModifiedDate=GETDATE() Where StudentID=@StudentId
Set @result='Data updated Successfully.!'
Set @Status=2
End
Else
Begin
Insert into StudentDetails(StudentName,Email,DOB,Gender,CreatedDate)
Values(@StudentName,@Email,@DOB,@Gender,GETDATE())
Set @result='Data inserted successfully.'
Set @Status=1
End
Commit Tran
End try
Begin Catch
Rollback Tran
Set @result=ERROR_MESSAGE()+' Error on Line No '+Cast(ERROR_LINE() as varchar(max));
End Catch
Select @result as [Result],@Status as [Status]
End
/*Procedure for show data and fetch particular data for update*/
Go
Create Proc USP_ShowStudentDetails
(
@StudentID Int
)
As
Begin
Declare @Query Nvarchar(Max)='',@Cond Varchar(Max)=''
If @StudentID<>0 Set @Cond=' And SD.StudentID=@StudentID'
Set @Query='Select SD.StudentID
,SD.StudentName,SD.Email
,Convert(varchar(10),sd.DOB,101) as [DOB]
,Sd.Gender
,Convert(varchar(10),sd.CreatedDate,101) as [CreatedDate]
,Convert(varchar(10),sd.ModifiedDate,101) as [ModifiedDate]
From StudentDetails SD Where 1=1'+@Cond
Exec Sys.Sp_ExecuteSql
@Stmt=@Query,
@Param=N'
@StudentID Int',
@StudentID=@StudentID;
End
/*procedure for delete data*/
Go
Create Proc USP_Delete_StudentDetails
(
@studentId Int
)
As
Begin
Declare @result Varchar(Max)='',@Status Int=0
Begin Try
Begin Tran
If Not Exists(Select StudentID from StudentDetails Where StudentID=@StudentID)
Begin
Set @result='Id does not Exists.!'
End
Else
Begin
Delete from StudentDetails Where StudentID=@StudentID
Set @Status=1
Set @result='Data deleted successfully.'
End
Commit Tran
End Try
Begin Catch
Set @result=ERROR_MESSAGE()+' Error On Line No :'+Cast(ERROR_LINE() As Varchar)
End Catch
Select @result As [result],@Status As [Status]
End
If you face below attached error : then simply double click on your project >> change InvariantGlobalization "false" from "true" <InvariantGlobalization>true</InvariantGlobalization>
Set false :
Thanks for reading , if you have any query then simply drop a message our team will revert you ASAP .
Surya Prakash (Admin) Reply
Thank Pratima !!