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.

Introduction

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.