Please enable Javascript for better experience...
Web API Tutorial C# [Part 5] - Fetching data from SQL Server into Web API
By Rahul Kumar Jha | Jan 9, 2020 | In Tutorials | Total Views [ 22371 ]
Taged In
(2 Like)

This is part 5 of Web API tutorial series. In this article you will learn to establish SQL connection into Web API and fetching record from table. In this tutorial, classic way has been demonstrated.


In previous articles, we learnt how to create and consume Web API and for demonstration purpose, we used hard coded dummy data. This is ok till we are demonstrating but in live projects, we play with real data which is stored in database or at differnt medium. In this tutorial, we will learn esblishing connection with SQL database and then later we will demonstrate fetching data from SQL. For that pleaes follow below steps or watch the video underneath.

Get ready with Database

Step 1) This is easy step. Create a database and name it "PRACCON" (You can take any name). Now create "Employee" table and insert few records into this.


Adding connection string in Web.config

Step 2) Once we are done with database, next task is to add connection string in web.config file in Web API application. Here use ConnectionString tag under Configuration tag and set name of conection and connection string.


Adding Code in Web API method

Step 3) Add below code in API methods. Here we are using classing way to set communcation with SQL.

  • We picked connection string from ConfigurationManager.
  • Added command string for both methods one by one, one is default to get all records from Employee table and other one is conditional based on id column.
  • Open connection.
  • Initialized SqlDataAdapter and provided command and connection to it.
  • Fill DataSet.
  • Iterate DataRow from DataSet.Table[0] (Datasetr has only one table so we took index 0, if you have multiple datatables then you can take index accordingly).



Test Web API

Step 4) Once you are done with this, you can test your Web API using MVC application.



Hope it helps you.

<< Web API Tutorial C# - Part 4: Testing Web API using Postman
Web API Tutorial C# [Part 6] - Using Entity Framework with Web API [Database First Approach] >>

Share this

About the Author

Rahul Kumar Jha
Rahul Kumar Jha
Founder, Developer

Public profile: user/profile/99900001

Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, ANGULAR, ANGULAR JS, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

User's Comments

Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID