Back to: ASP.NET Web API Tutorials For Beginners and Professionals
ASP.NET Web API using SQL Server
In this article, I am going to discuss ASP.NET Web API using SQL Server. So here we will create the ASP.NET Web API Service which will perform the CRUD operation on the SQL Server database. We are going to use this service as the base for understanding many of the ASP.NET Web API concepts that we are going to discuss in our upcoming articles.
The ASP.NET Web API EmployeeService that we are going to build will retrieve the data from the Employees database table. We will be using Entity Framework Database First Approach to retrieve data from the SQL server database. You can use any technology of your choice to retrieve data from the database. For example, you can even use raw ADO.NET.
Example: ASP.NET Web API using SQL Server
We are going to use the following Employees table in this demo to understand how to create ASP.NET Web API Application using the SQL Server database.
Please use the following SQL Script to create the WEBAPI_DB database and Employees table and populate the Employees table with sample data.
CREATE DATABASE WEBAPI_DB GO USE WEBAPI_DB GO CREATE TABLE Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO INSERT INTO Employees VALUES ('Pranaya', 'Rout', 'Male', 60000) INSERT INTO Employees VALUES ('Anurag', 'Mohanty', 'Male', 45000) INSERT INTO Employees VALUES ('Preety', 'Tiwari', 'Female', 45000) INSERT INTO Employees VALUES ('Sambit', 'Mohanty', 'Male', 70000) INSERT INTO Employees VALUES ('Shushanta', 'Jena', 'Male', 45000) INSERT INTO Employees VALUES ('Priyanka', 'Dewangan', 'Female', 30000) INSERT INTO Employees VALUES ('Sandeep', 'Kiran', 'Male', 45000) INSERT INTO Employees VALUES('Shudhansshu', 'Nayak', 'Male', 30000) INSERT INTO Employees VALUES ('Hina', 'Sharma', 'Female', 35000) INSERT INTO Employees VALUES ('Preetiranjan', 'Sahoo', 'Male', 80000) GO
Creating a new ASP.NET Web API Project
Open Visual Studio and select File – New – Project as shown below
From the “New Project” window, select the Web option under the “Visual C#” option which is under the “Installed” section. Again from the middle pane, you need to select the “ASP.NET Web Application” and name the project as “EmployeeService“. Finally, click on the “OK” button as shown in the below image.
Once you click on the OK button. A new dialog window will open with the Name “New ASP.NET Project” for selecting project Templates as per your requirement. From this dialog, we are going to choose the Web API project template, select the Authentication type as No Authentication and then click on the OK button as shown in the below image.
Once you click on the OK button, it will create the ASP.NET Web API Project. At this point, you should have the ASP.NET Web API project created with the following file and folder structure.
Adding ADO.NET Entity Data Model
We are adding ADO.NET Entity Data Model and using the Entity Framework Database First Approach we are going to communicate with the SQL Server database and will perform the CRUD Operations. The following are the steps to add ADO.NET Entity Data Model to ASP.NET Web API Application.
Right-click on the Models folder and then select Add – New Item from the context menu which will open the Add New Item window as shown below. From this “Add New Item” window select “Data” from the left pane which is inside the Visual C# and Installed section and then from the middle pane select ADO.NET Entity Data Model. In the Name text box, type EmployeeDataModel and then click on the Add button as shown in the below image.
Once you click on the Add button, it will open the Entity Data Model Wizard window. From this Entity Data Model Wizard, select the “EF Designer from database” option and click the Next button as shown in the below image. EF Designer from the database option is basically used to use the Database First Approach of Entity Framework.
Once you click on the Next button, it will open Choose Your Data Connection window. From this window, click on the “New Connection” button as shown in the below image to create a new connection that will be going to connect with SQL Server Database and the WEBAPI_DB database.
Once you click on the New Connection button it will open the Connection Properties window. On the “Connection Properties” window, set
- Data Source = Microsoft SQL Server (SqlClient) as we are going to interact with SQL Server database
- Server Name = provide the server (Machine name or IP address)
- Authentication = Select the authentication type (I am using Windows Authentication)
- Select or enter a database name = WEBAPI_DB
- Click on the Test Connection button to check if every the provided information is correct
Finally, click on the OK button as shown in the below image.
Once you click on the OK button it will navigate back to the Choose Your Data Connection wizard. Here Modify the Connection String name as EmployeeDBContext and click on the Next button as shown in the below image.
Once you click on the Next button, it will open Choose Your Entity Framework Version window. From this window, select Entity Framework 6.x and click on the Next button as shown in the below image.
Once you click on the Next button, it will open the “Choose Your Database Objects and Settings” window. From this window, select the “Employees” table, provide the model namespace as Models and click on the Finish button as shown in the below image.
Once you click on the Finish Button, the following EDMX file with the Employees model will generate.
The EDMX file is generated within the Models folder with the following structure.
Adding ASP.NET Web API Controller
Let us add an Empty Web API Controller with the name Employees in our Controllers folder. To do so, Right-click on the Controllers folder in the EmployeeService project and then select Add – Controller option from the context menu which opens the following Add Scaffold window. From this window, select the “Web API 2 Controller – Empty” option which will create an empty Web API Controller, and click on the “Add” button as shown in the below image.
Once you click on the Add button, it will open the following window. In this window, provide the Controller Name as EmployeesController and click on the Add button as shown in the below image.
Once you click on the Add button, it will create the Employees Web API controller within the Controllers folder in your project.
Creating ASP.NET Web API Services:
Now, let us create two services i.e. two action methods within the Employees controller. One service is going to return the list of employees and the other action method is going to return one employee’s information based on the employee id. To do so, copy and paste the following code in EmployeesController.cs class file. As you can see in the below code, we have created two action methods with the name Get. The first action method does not take any parameter and returns the list of employees while the second action method takes Employee id as a parameter and returns that employee’s information, and hence the return type is Employee.
using EmployeeService.Models; using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Net.Http; using System.Web.Http; namespace EmployeeService.Controllers { public class EmployeesController : ApiController { public IEnumerable<Employee> Get() { using (EmployeeDBContext dbContext = new EmployeeDBContext()) { return dbContext.Employees.ToList(); } } public Employee Get(int id) { using (EmployeeDBContext dbContext = new EmployeeDBContext()) { return dbContext.Employees.FirstOrDefault(e => e.ID == id); } } } }
At this point when you navigate to /api/employees, you should see all employees and when you navigate to /api/employees/1, you should see all the details of the employee whose Id=1. Here, in this article, I just show you how to retrieve the data from the SQL Server database using Entity Framework Database First approach and in our upcoming articles, I will show you how to Perform the INSERT, UPDATE, and DELETE operations using ASP.NET Web API and SQL Server.
In the next article, I am going to discuss Content Negotiation in ASP.NET Web API with Examples. Here, in this article, I try to explain ASP.NET Web API using SQL Server with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this Web API using the SQL Server article.
Simple… it worked..!
I think webapi filters are missed.. Could you please check once. If not please provide one best center.
Very soon, we will update filters in web api.
Thanks for share your knowledge… if you don’t mind would you discuss about QueryUtility in .net??
Hi, quite a good explanation.
please the code is not compiling cos the Employee controller class is missing a using directive or assembly reference.
How do i solve that?
thanks
Please provide webapi filters are missed
Saludos, al ejecutar me salió este mensaje:
Advertencia de seguridad.
La ejecucion de esta plantilla de texto puede dañar el equipo.
No la ejecute si la ha obtenido de una fuente que no es de confianza
no problem you run those code
Thanks so much
What if we wanted to pass a value that was not an ID field?
Great!
Thank you so much <3
Please provide webapi filters are missed