In this article, we will talk about, How to create ASP.NET Core Web API WITHOUT Entity Framework. We will connect to SQL Server from .Net Core without using Entity Framework and will use simply use the old way which uses SqlConnection for performing operation in the database.
If you are a newbie and started learning the concept of ASP.NET Core Web API then you have come to the right place, in this the article we will teach you to CRUD operations in asp .net Core Web API without using Entity Framework.
we have written this article for newbie programmers and anyone who wants to create custom a rest API with ADO .NET without using an entity framework.
we saw many posts on the Internet and we notice that all article is based on entity framework core, no one has given an example of ADO .NET that’s why in this post we decided to write a post on connecting to a database in ASP.NET Core without Entity Framework.
CRUD operations in asp net Core Web API without Entity Framework
Step Involve in the article
- Create ASP.NET Core Web API Project
- Create Database table
- Create Model class
- Create Controller and write logic
Step 1. Open visual 2022 and create an ASP.NET Core Web API Project
2. Create a Database table
Let’s create a table, we have created a table called TeamPlayers which is got, the Id, Name, Email, ContactNo, TeamName, and Address columns.
SQL Script:
CREATE TABLE [dbo].[TeamPlayers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[Email] [nvarchar](max) NOT NULL,
[ContactNo] [nvarchar](14) NULL,
[TeamName] [nvarchar](100) NOT NULL,
[Address] [nvarchar](100) NOT NULL,
CONSTRAINT [PK__Teacher__3214EC077B0B6A86] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TeamPlayers] ON
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (1, 'John Update', 'Atlanta Falcons', '8287589645', 'Atlanta Falcons', 'US')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (1002, 'Mark', 'Mark@gmail.com', '8287589645', 'Los Angeles Rams', 'US')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2002, 'Kemith', 'Germany', '33445666', 'Germany', 'Germany')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2003, 'Henry', 'France', '344565656', 'France', 'FR')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2004, 'Henry', 'France', '344565656', 'France', 'FR')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2005, 'Tommy', 'France', '454545454', 'France', 'Fr')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2006, 'ahsas', 'string', 'string', 'string', 'string')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2007, 'string', 'string', 'string', 'string', 'string')
GO
INSERT [dbo].[TeamPlayers] ([Id], [Name], [Email], [ContactNo], [TeamName], [Address]) VALUES (2008, 'Jammy', 'France', '354545454', 'France', 'FR')
GO
SET IDENTITY_INSERT [dbo].[TeamPlayers] OFF
GO
3. Right Click on solution and Add a folder named “Model”
and add a Model class inside that folder for request/response.
4. Create a Controller
Right-click on “Controller” folder =>add=>controller
Select API from the left side tab and then add API controller with read/write action.
Select API from the left side tab and then add API controller with read/write action.
It will create a controller with the following endpoint.
using Microsoft.AspNetCore.Mvc;
namespace RestAPI.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class TeamPlayerController : ControllerBase
{
// GET: api/<TeamPlayerController>
[HttpGet]
public IEnumerable<string> Get()
{
return new string[] { "value1", "value2" };
}
// GET api/<TeamPlayerController>/5
[HttpGet("{id}")]
public string Get(int id)
{
return "value";
}
// POST api/<TeamPlayerController>
[HttpPost]
public void Post([FromBody] string value)
{
}
// PUT api/<TeamPlayerController>/5
[HttpPut("{id}")]
public void Put(int id, [FromBody] string value)
{
}
// DELETE api/<TeamPlayerController>/5
[HttpDelete("{id}")]
public void Delete(int id)
{
}
}
}
Now,let’s write our custom logic for all each endpoint.
Below is the code example for the each ,api endpoint.
if you want to get the connection string from appsetting.json read this articel
using Microsoft.AspNetCore.Mvc;
using RestAPI.Model;
using System.Data.SqlClient;
namespace RestAPI.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class TeamPlayerController : ControllerBase
{
static string constr = "Data Source=SQLEXPRESS01;Initial Catalog=TestDb;User ID=sa;Password=sa@1234";
// GET: api/<TeamPlayerController>
[HttpGet]
public async Task<ActionResult<IEnumerable<TeamPlayerModel>>> Get()
{
List<TeamPlayerModel> teamPlayers = new List<TeamPlayerModel>();
try
{
string query = "SELECT * FROM TeamPlayers";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
await con.OpenAsync();
using (SqlDataReader sdr = await cmd.ExecuteReaderAsync())
{
while (await sdr.ReadAsync())
{
teamPlayers.Add(new TeamPlayerModel
{
Id = Convert.ToInt32(sdr["Id"]),
Name = Convert.ToString(sdr["Name"]),
Email = Convert.ToString(sdr["Email"]),
ContactNo = Convert.ToString(sdr["ContactNo"]),
TeamName = Convert.ToString(sdr["TeamName"]),
Address = Convert.ToString(sdr["Address"]),
});
}
}
await con.CloseAsync();
}
}
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex);
}
return Ok(teamPlayers);
}
// GET api/<TeamPlayerController>/5
[HttpGet("{id}")]
public async Task<ActionResult<TeamPlayerModel>> Get(int id)
{
TeamPlayerModel teamPlayer = new TeamPlayerModel();
try
{
string query = "SELECT * FROM TeamPlayers where Id=" + id;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
await con.OpenAsync();
using (SqlDataReader sdr = await cmd.ExecuteReaderAsync())
{
while (await sdr.ReadAsync())
{
teamPlayer = new TeamPlayerModel
{
Id = Convert.ToInt32(sdr["Id"]),
Name = Convert.ToString(sdr["Name"]),
Email = Convert.ToString(sdr["Email"]),
ContactNo = Convert.ToString(sdr["ContactNo"]),
TeamName = Convert.ToString(sdr["TeamName"]),
Address = Convert.ToString(sdr["Address"]),
};
}
}
await con.CloseAsync();
}
}
if (teamPlayer == null || teamPlayer.Id == 0)
{
//element not found
return NotFound();
}
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex);
}
return Ok(teamPlayer);
}
// POST api/<TeamPlayerController>
[HttpPost]
public async Task<ActionResult<TeamPlayerModel>> Post(TeamPlayerModel teamPlayer)
{
try
{
using (SqlConnection con = new SqlConnection(constr))
{
//inserting Patient data into database
string query = "insert into TeamPlayers values (@Name, @Email, @ContactNo,@TeamName,@Address)";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", teamPlayer.Name);
cmd.Parameters.AddWithValue("@Email", teamPlayer.TeamName);
cmd.Parameters.AddWithValue("@ContactNo", teamPlayer.ContactNo);
cmd.Parameters.AddWithValue("@TeamName", teamPlayer.TeamName);
cmd.Parameters.AddWithValue("@Address", teamPlayer.Address);
await con.OpenAsync();
int id = await cmd.ExecuteNonQueryAsync();
if (id > 0)
{
return Ok( teamPlayer);
}
await con.CloseAsync();
}
}
return BadRequest("Something went wrong while making entry in database!");
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex);
}
}
// PUT api/<TeamPlayerController>/5
[HttpPut("{id}")]
public async Task<ActionResult<TeamPlayerModel>> Put(int id, TeamPlayerModel teamPlayer)
{
try
{
if (id != teamPlayer.Id)
{
return BadRequest();
}
string query = "UPDATE TeamPlayers SET Name = @Name, Email = @Email,ContactNo=@ContactNo,TeamName=@TeamName,Address=@Address Where Id =@Id";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", teamPlayer.Name);
cmd.Parameters.AddWithValue("@Email", teamPlayer.TeamName);
cmd.Parameters.AddWithValue("@ContactNo", teamPlayer.ContactNo);
cmd.Parameters.AddWithValue("@TeamName", teamPlayer.TeamName);
cmd.Parameters.AddWithValue("@Address", teamPlayer.Address);
cmd.Parameters.AddWithValue("@Id", teamPlayer.Id);
await con.OpenAsync();
await cmd.ExecuteNonQueryAsync();
await con.CloseAsync();
return NoContent();
}
}
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex);
}
}
// DELETE api/<TeamPlayerController>/5
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{
try
{
using (SqlConnection con = new SqlConnection(constr))
{
string query = "Delete FROM TeamPlayers where Id='" + id + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
await con.OpenAsync();
await cmd.ExecuteNonQueryAsync();
await con.CloseAsync();
return NoContent();
}
}
}
catch(Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex);
}
}
}
}
if you want to set connection string in appsetting.json then read this articel
Now and let’s build and run your project.It will open below swagger screen for calling and testing the Api.
Testing Of EndPoints
Expand first endpoint=>try out=>execute
https://localhost:7104/api/TeamPlayer
It’ will return the all records in the table.
[
{
"id": 1,
"name": "John",
"email": "John@gmail.com",
"contactNo": "8287589645",
"teamName": "Atlanta Falcons",
"address": "US"
},
{
"id": 1002,
"name": "Mark",
"email": "Mark@gmail.com",
"contactNo": "8287589645",
"teamName": "Los Angeles Rams",
"address": "US"
}
]
Post Endpoint
Request
{
"id": 0,
"name": "Jammy",
"email": "Jammy@example.com",
"contactNo": "354545454",
"teamName": "France",
"address": "FR"
}
Put Endpoint
Request
{
"id": 1,
"name": "John Update",
"email": "John@gmail.com",
"contactNo": "8287589645",
"teamName": "Atlanta Falcons",
"address": "US"
}
For Getting Single Record
https://localhost:7104/api/TeamPlayer/1
Response body
{
"id": 1,
"name": "John Update",
"email": "Atlanta Falcons",
"contactNo": "8287589645",
"teamName": "Atlanta Falcons",
"address": "US"
}Thank you for reading article