ASP NET Web API and SQL Server

this is part four of asp.net Web API tutorial in this video we'll discuss retrieving data from sequel server using asp.net Web API and entity framework so here is what we want to do we are going to have the sequence of a database table and when we issued this URI localhost four slash API four slash employees we want to retrieve all these employees from the sequence of an employee's table and return them as you can see here in case if we ask for a specific employee notice in the URI right here we have employee ID as well so in this case we want to return that specific employee details as you can see here let's see how to achieve this using sequel server entity framework and asp.net Web API so let's flip to visual studio the first thing that we want to do is create our Web API project so I am going to click on file new project and then let's select web and asp.net web application let's name our project employee service and let's click OK now we want to create a web api project so on the screen I'm going to select Web API and now let's click OK which is going to take a few seconds to create the web API project for us the project is now successfully created the next thing that you want to do is create an employee's table within our sequel server database so let's flip to sequel server management studio here we have the script which creates the database with named employee DB within this database we're creating our employees table and then finally we have the script which is going to populate employees table with test data I have already executed this clip I'll have the script available on my blog in case you need it now let's flip to visual studio we're going to add another project or solution employee service this is going to be a class library project which is going to contain our area dot and entity data model which retrieves data for us from the sequence of a database table so let's right click on the solution new project and the type of project that we want to add is a class library project let's name this employee data access let's click OK this is going to add the class library project to a solution we don't need this class 1 dot CS which is auto-generated so I'm going to delete that from our project and to this employee data access project I'm going to add a do dotnet entity data model it's present on the data so let's select data and then I do dotted entity data model let's name this data model employee data model let's click Add now we want to generate our entities from the database so I'm going to select this first option entity framework designer from database and then let's click Next we want to create a new connection so I'm going to click this button new connection and now here we need to specify our server name we want to connect to our local server so we can either specify dot or local within parentheses like this and we are going to use Windows authentication to connect to sequel server and from this drop-down we are going to select our database in our case the database is employee dB and let's click this button to test the connection so test connection succeeded let's click OK and entity framework is going to save a connection string with his name employee DB entities with an app config file within our employee data access class library project so let's click Next now and such thing us whether we want to use entity framework 5 or 6 let's use entity framework 6 and let's click Next and this is going to connect to sequel server retrieve all tables views and store procedures we only have got one table within our database let's select that table employees and the model name space is going to be employee DB more let's leave that with the default and let's click finish so this is going to take a few seconds to generate the entity data model for us the entity data model is now successfully created notice within our employee data access project we have a configuration file apt or conflict within which we have a connection string with named employee DB entities that points to our database which contains the employees table we want to use this employee data access project within our Web API project so let's right click on the references folder add reference we want to add a project reference so let's select the projects tab within which we have our employee data access project let's select that and click OK notice it has added a reference to our employee data access project the next thing that we want to do is add a controller to our Web API project so let's right click on the controller's folder and select add controller notice here we have templates for both adding MVC and Web API controllers I'm going to select this template Web API - controller MT which is going to create an empty controller for us and then we'll add the required methods on an as-needed basis now it's asking for a name for the controller let's call this employee's controller now all it's going to do is create an empty controller for us that is a class with named employees controller that derives from the base API controller class now the namespace for our employee data access project is employee data access so let's use that namespace here and within our employees controller class let's have get method which responds to the get HTTP verb this method is going to return I enumerable of employee of so where is this employee object coming from we have not created that this is the generated entity by the ATO dotnet entity framework so area dot entity framework has generated this entity based on this employees table which has got these columns so if you look at this entity right here so when we right click on that and select go to definition notice we have properties that correspond to these columns in this employees table so we want to return an i enumerable of employee objects from our method so the name of the method is going to be get which is going to respond to the HTTP GET verb now we have to create an instance of our dbcontext class so within our employee data access project let's expand the EDM X file here and the notice here we have employee data model context dot CS so within this file we have employee DB entities class which inherits from DB context class so this is the class that manages connections to the database retrieves the entities for us so let's create an instance of this class so let's go to our employees controller class and here let's use a using statement so using employee DB entities let's call this entities equals new employee DB entities so let's use the entities object and this has got the employees object you know this is basically a collection property which is going to return us the list of all employees and on this let's call to list which is going to convert that to a list of employees now this is going to respond to the HTTP GET verb without an ID parameter now when we request for a specific employee we want to return just that employee so let's have another get method which is going to respond to the URI which has got an ID in it so let's pass ID parameter to it and we don't want to return the entire employees list we just want to return one employee so first of all let's change the return type to employee and from the collection we want first or default and then here we are going to use a lambda expression employee such that employee dot PI D equals the ID that we are passing to this method alright so let's go ahead and build our solution the build is succeeded as you can see in the status bar right here let's run our project by pressing ctrl f5 we are on the start page here now let's navigate to four slash API four slash employees notice we are getting an error message the error message says no connection string named employee DB entities could be found in the application config file now if you look at the app dot config file notice we have a connection string with named employee DB entities so why is it complaining about that then that's because entity framework is looking for a connection string with this name in web dot config file and if you look at the web config file notice in this file we don't have a connection string with named employee DB entities and that's the reason why we are getting this message so to fix this all we have to do is make a copy of this connection string within our web config file so let's copy and paste that in the connection strings section of our web config file let's save it and let's reissue this request notice now we get all the employees from the database table as expected now if we request for a specific employee we get that specific employee in our next video we'll discuss a concept called content negotiation in asp.net Web API thank you for listening and have a great day

Channel: kudvenkat

Views: 605,703

Rating: 4.8580813 out of 5

Keywords: asp.net web api entity framework example, connect web api to sql server database, using entity framework with web api, asp.net web api entity framework database first, web api and entity framework 6, entity framework class library connection string

Id: nMGlaiNBbNU

Length: 11min 17sec (677 seconds)

Published: Sun Sep 04 2016