Retrieve data from database in asp.net web api

Video Tutorial:

Watch here

Introduction:

 Here I will explain how to retrieve data from database in Asp.net MVC.  Returned data will be in JSON and XML format as per user requirement. We simply pass id or value to the API and it return result.

Description:

In previous articles I will explain How to bind Country, State, City Dropdownlist in mvc using ajax,

How to show database values using Dataset in table with Edit and delete operation using Asp.net MVC,  How to bind drodoen in mvc using ajax, How to login with facebook in asp.net mvc, How to upload multiple image or files in asp.net mvc with Source Code.with relevant example

So now we start.

Step1.  Firstly we select project type as Web API in Asp.net MVC web project

Step2: We need to create table and store procedure for fetching data

Table:

CREATE TABLE [dbo].[tbl_registration] (

    [Sr_no]    INT            IDENTITY (1, 1) NOT NULL,

    [Email]    NVARCHAR (100) NULL,

    [Password] NVARCHAR (MAX) NULL,

    [Name]     VARCHAR (MAX)  NULL,

    [Address]  NVARCHAR (MAX) NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

Store procedure:

Create proc SpgetDetailbyid

@Sr_no int

as

begin

Select * from tbl_registration where Sr_no=@Sr_no

end

Step3: In This step we need to create class file (db.cs) for accessing data from databse

Step4: Write fallowing code under db.cs class file

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

       public DataSet GetRecordbyid(int id)

        {

            SqlCommand com = new SqlCommand("SpgetDetailbyid", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Sr_no", id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

Step5: Write fallowing code under ValueController.cs file

database_Access_Layer.db dblayer = new database_Access_Layer.db();

public DataSet Getreacord(int id)

        {

            DataSet ds = dblayer.GetRecordbyid(id);

            return ds;

        }

Step6:  Do fallowing changes in WebApiConfig.cs file under App_Start folder for return format as JSON

public static void Register(HttpConfiguration config)

        {

            config.Routes.MapHttpRoute(

            name: "DefaultApi",

            routeTemplate: "api/{controller}/{action}/{id}",

            defaults: new { id = RouteParameter.Optional }

        );

            var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(t => t.MediaType == "application/xml");

            config.Formatters.XmlFormatter.SupportedMediaTypes.Remove(appXmlType);

        }

Step7: Now run the project and it return fallowing output

 

Download Full Source Code:

Download here