Best resource for Online free Education
In this Video, 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 Videos.
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 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.
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 in 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 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 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 employees 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 return that employees information and hence the return type is Employee.
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 Video, I just show you how to retrieve the data from the SQL Server database using Entity Framework Database First approach and in our upcoming Videos, I will show you how to Perform the INSERT, UPDATE, and DELETE operations using ASP.NET Web API and SQL Server.
