I am trying to connect my ASP.NET Core 6 application to SQL Server, but I get an error on the connection string. Does anyone have experience with .NET 6 that could help?

This is my code in my class (book.cs). I get the ERROR where I commented in the code. And yes I have the right string in the app.json file

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; }

    public List<Book> GetBooks(string connectionString)
    {
        List<Book> bookList = new List<Book>();
        SqlConnection con = new SqlConnection (connectionString);
        string selectSQL = "select BookId, Title, Isbn, PublisherName, AuthorName, CategoryName from GetBookData";
        con.Open(); // Here i get my error

        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();
            }
        }
        return bookList;
    }

CodePudding user response：

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. https://learn.microsoft.com/en-us/dotnet/core/extensions/options

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

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

    public string ConnectionString { get; set; }
}

public class Book
{
    private readonly ConnectionStrings connectionStringsOptions;

    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; }

    public Book(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(); // Here i get my error

        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();
            }
        }
        return bookList;
    }
}

Make sure your Startup.cs is doing that on the ConfigureServices method:

services.Configure<ConnectionStrings>(Configuration);

CodePudding user response：

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;
        }