Step 1: install sqlClient

1. Go to tools -> NutGet Package Manager -> Manage NutGet Manager Solution

2. install 'System.Data.sqlClient' , version 4.8.2

Step 1: add connection string in appsettings.json

                            "ConnectionStrings": {
                                "DefaultConnection": "Server=ACT002\\SQLEXPRESS;Database=EmployeeDB;uid=sa;password=vandiperiyar;Trusted_Connection=True;MultipleActiveResultSets=true;"
                            },

                            "Logging": {
                            "LogLevel": {
                                "Default": "Information",
                                "Microsoft": "Warning",
                                "Microsoft.Hosting.Lifetime": "Information"
                            }
                            },
                            "AllowedHosts": "*"
                            }


                        

Step2: use packages in controller

                            using System.Data;
                            using System.Data.SqlClient;
                            using Microsoft.Extensions.Configuration;

                        

Step3 : access connection string

1. Defind variable

                        private readonly IConfiguration _configuration;
                        

2. inject service into constructor

                        public DepartmentsController(IConfiguration configuration)
                        {
                            _configuration = configuration;
                        }
                        

3. get connection string

                        string sqlDataSource = _configuration.GetConnectionString("DefaultConnection");
                        

4. Prepare query

                        SqlCommand command = new SqlCommand(queryString, connection);
                        

5. create connection

                        using (SqlConnection connection =  new SqlConnection(sqlDataSource))
                        {
                        

6. execute query

                        SqlDataReader reader = command.ExecuteReader();
                        

Complete code

                        using Microsoft.AspNetCore.Http;
                        using Microsoft.AspNetCore.Mvc;
                        using System;
                        using System.Collections.Generic;
                        using System.Linq;
                        using System.Threading.Tasks;
                        using WebApplication5.Models;
                        using Newtonsoft.Json;
                        using System.Data;
                        using System.Data.SqlClient;
                        using Microsoft.Extensions.Configuration;

                        namespace WebApplication5.Controllers
                        {
                            [Route("[controller]")]
                            [ApiController]
                            public class DepartmentsController : ControllerBase
                            {

                                private readonly IConfiguration _configuration;

                                public DepartmentsController(IConfiguration configuration)
                                {
                                    _configuration = configuration;
                                }

                                // GET: /departments/ 
                                [HttpGet]
                                public string Index()
                                {
                                    string queryString =
                                            "SELECT * FROM Department";

                                    string sqlDataSource = _configuration.GetConnectionString("DefaultConnection");

                                    using (SqlConnection connection =  new SqlConnection(sqlDataSource))
                                    {
                                        SqlCommand command = new SqlCommand(queryString, connection);
                                        connection.Open();
                                        SqlDataReader reader = command.ExecuteReader();


                                        while (reader.Read())
                                        {
                                            ReadSingleRow((IDataRecord)reader);
                                        }

                                        // Call Close when done reading.
                                        reader.Close();

                                    }

                                    Department d = new Department();
                                    d.DepartmentId = 1;
                                    d.DepartmentName = "m";
                                    return JsonConvert.SerializeObject(d);
                                // return Ok(jsonResult);

                                }

                                private static void ReadSingleRow(IDataRecord dataRecord)
                                {
                                    Console.WriteLine(String.Format("{0}, {1}", dataRecord[0], dataRecord[1]));
                                }


                            }
                        }