I would say configurations in .NET are annoying stuff but, let's skip that ;)

The usage of options is done after some DI logic. So basically, your problem probably is in how you are trying to get the value from appsettings.{ENV}.json .

You haven't attached this part of the code but when you are calling your method from the Book.cs class, you should do something like that:

new Book().GetBooks(_configuration.GetConnectionString("connStrKey"));

BTW this is an anti-pattern and I strongly recommend you start using the Options Pattern to inject options and settings into your code.

So, your code should be changed to something like that:

public class ConnectionStrings
{
    public ConnectionStrings()
    {
        ConnectionString = string.Empty;
    }

    public string ConnectionString { get; set; }
}

public class BooksRepository
{
    private readonly ConnectionStrings connectionStringsOptions;

    public BooksRepository(IOptions<ConnectionStrings> connectionOptions)
    {
        connectionStringsOptions = connectionOptions.Value;
    }

    public List<Book> GetBooks()
    {
        List<Book> bookList = new List<Book>();
        SqlConnection con = new SqlConnection(connectionStringsOptions.ConnectionString);
        string selectSQL = "select BookId, Title, Isbn, PublisherName, AuthorName, CategoryName from GetBookData";
        con.Open();

        SqlCommand cmd = new SqlCommand(selectSQL, con);

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr != null)
        {
            while (dr.Read())
            {
                bookList.Add(new Book
                {
                    BookId = Convert.ToInt32(dr["BookId"]),
                    Pagecount = Convert.ToInt32(dr["pagecount"]),
                    Title = dr["Title"].ToString(),
                    TypeName = dr["TypeName"].ToString(),
                    AutherLastName = dr["AutherLastName"].ToString(),
                    AutherFirstName = dr["AutherFirstName"].ToString()
                });
            }
        }
        return bookList;
    }
}

public class Book
{
    public int BookId { get; set; }
    public int Pagecount { get; set; }
    public string Title { get; set; }
    public string TypeName { get; set; }
    public string AutherFirstName { get; set; }
    public string AutherLastName { get; set; }
}

Since you are using .net 6, update your Program.cs class to add the connection strings as DI Options adding the following line before the var app = builder. Build(); statement.

builder.Services.Configure<ConnectionStrings>(builder.Configuration.GetSection("ConnectionStrings"));

Example of appsettings.json section

{
  "ConnectionStrings": {
    "ConnectionString": "localhost:1433"
  }
}

I eventually came to the answer. Had to change the query in SQL and put part of the code into a try/catch block and it worked.

string ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=RFID_Library;Integrated Security=True; TrustServerCertificate=True";
        string errormsg = null;
        List<Book> bookList = new List<Book>();
        try
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            string selectSQL = "select BookId, Title, pagecount, AutherFirstName, AutherLastName, TypeName from GetBookData";
            con.Open();
            SqlCommand cmd = new SqlCommand(selectSQL, con);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr != null)
            {
                while (dr.Read())
                {
                    Book book = new Book();
                    book.BookId = Convert.ToInt32(dr["BookId"]);
                    book.Pagecount = Convert.ToInt32(dr["pagecount"]);
                    book.Title = dr["Title"].ToString();
                    book.TypeName = dr["TypeName"].ToString();
                    book.AutherLastName = dr["AutherLastName"].ToString();
                    book.AutherFirstName = dr["AutherFirstName"].ToString();

                    bookList.Add(book);
                }
            }
            return bookList;

        }
        catch (Exception)
        {

            throw;
        }