ASP.NET Web API And SQL Server

Overview - In this article, we will see how to Retrieve data from SQL server using Web API and Entity Framework.

So, let’s start.

Introduction

First, create a Blank Web API project as,

File->New->Project.



Select ASP.NET web application Project and give a Desirable name and click Ok as



Click on Web API in template selection window.



It will create a blank Web API project. Now, in SQL Server, I have a table called Employee Data which has 5 fields as Id, FirstName, LastName, Gender, and Salary.



Now, what we want - If the request is http://localhost:portnumber/api/employees, it should display the list of employees, and if we type any Id in a particular URL, it should display those details.

Let’s see how.

Now, flip to Visual Studio. You can see, we have successfully created our empty Web API project.



In this solution, we will add web service for employees which will contain our ADO.NET Entity Data Model that retrieves the data from SQL Server.

So, right click on the Solution ->Add->New Project.



Next, click on Class Library (the type of project we want to access).



We don’t need the auto generated class file which is class1.cs. So, kindly delete that file.




And to this EmployeeDataAccess project, add a ADO.NET Entity Data Model.

Right click on the Project ->Add->New Item.



Now, go to Data tab, click on ADO.NeT Entity Data Model, and name it as EmployeeDataModel.



Now, click on Add. The subsequent screen will be the following. 

 

Since we want to generate entities from Database, we will select the first option. Click next



Now, we will create a new connection with SQL server, and refer it to our Database as well.

As you can see, the necessary connections are made. Now, test the connection.



Here, Entity Framework is going to see a connection string with the name TESTEntities within App.config file, in our EmployeeDataAccess Class library project. So, click Next now.

Its asks for the EF version we want to use. Let's select EF 6, and click Next.



 Select the Table from tables list and click on Finish.



You can see that the EmployeeDataModel is created successfully.

Now in that project, click on App.Config file. You will get the following screen.



I have highlighted the Connection String section. Our connection string here is TESTEntities which contains the EmployeeData Table.

 

We want to use this Class Library in our Web API Project.

Right Click on the References folder - > Add Reference



We want to add a project reference, so select the project tab and in that, select EmployeeDataAccess Class Library project. Then, click OK.





We have successfully added a reference to our main Employee Project. The next step is to add a Controller to our Web API Project. So, right click on the Controller folder and click on Add Controller.



When you click on Controllers, it will give options for adding templates for MVC and Web API Controllers. We will select "Web API 2 Controller – Empty".



Click on Add and give desired name as EmployeesController. It will create a Controller.

 

Now, we will add a namespace EmployeeDataAccess as our class library name is “EmployeeDataAccess”. Now, in our Controller class, we will add a GET method which will respond to GET HTTP verb. This will return IEnumerable Object.

public class EmployeesController : ApiController
{
   public IEnumerable<employeesData>
}

Now, the question is - "Where is this Employee Data that has come from this Employee.?". In our SQL server, our table name is employeeData. So, right click on that and go to definition, You will see the following.

We have these properties that correspond to these columns in our employeeData table. Now, we will write a GET method which will respond to the HTTP GET verb.

 

The next step is to create an instance of our DBContext class. So, we have EmployeeDatamodel.edmx file in which we have EmployeeDataModel.Context.cs file, and we have TESTEntities which inherits from DbContext class.



So, this is the class TESTEntities that manages the database and retrieves entities for us. So, in our Controller class, let’s use TESTEntities. It will return the list of employees as,


Now, this is going to respond to the HTTP GET Verb with an ID Parameter. When we request for specific employee, we want to return that employee.

 

Let’s add another GET method which will return that employee details only.


First, we changed the return type as we want to return a single employee and pass id in the GET method parameter. And, we want to return the first or the default value . We used lambda expression here in each employee ID, which will be equal to the id which we had used in our GET parameter.

 

Now, build the solution and run the app . You will get this output.



Now, navigate to api/employees. You will get the following.



As the error says, "No connection string named TESTEntities  found in config file".

 

Notice that we have a connection string in our App.config as TESTEnties but the name TESTEntities is not present in our web.config file as Entity Framework looks for web.config file.

 

Now, in our web.config file, we don’t have a connection string with TESTEntities and that’s the reason why we are getting this error. To fix this copy that connection string from app.config file and paste in our web.config file. Save it and reload the app,

Now type /api/employees



We have gotten all the employeeDetails from the Database. Now, when request for specific employee, we get -



Conclusion

So, this was all about ASP.NET Web API with SQL Server. Hope this article was helpful!!