.NET Core 6 SQL Server connection without Entity Framework

Question

I am new to .NET Core. I have defined the connection string in appsettings.json like this:

"ConnectionStrings": {
    "TestBD": "Server=localhost;Database=Test;Trusted_Connection=True;MultipleActiveResultSets=true"
}

I am not using Entity Framework. I need to connect to the database using this connection string from the Program.cs file.

Any help is really appreciated. Thanks

Answer 1

You refer the following sample code to use ADO.NET in Asp.net 6 program.cs:

//required using Microsoft.Data.SqlClient;
app.MapGet("/movies", () =>
{
    var movies = new List<Movie>();
    //to get the connection string 
    var _config = app.Services.GetRequiredService<IConfiguration>();
    var connectionstring = _config.GetConnectionString("DefaultConnection");
    //build the sqlconnection and execute the sql command
    using (SqlConnection conn = new SqlConnection(connectionstring))
    {
        conn.Open();  
        string commandtext = "select MovieId, Title, Genre from Movie";

        SqlCommand cmd = new SqlCommand(commandtext, conn); 

        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            var movie = new Movie()
            {
                MovieId = Convert.ToInt32(reader["MovieId"]),
                Title = reader["Title"].ToString(),
                Genre = reader["Genre"].ToString()
            };

            movies.Add(movie);
        } 
    }
    return movies;
});

The result like this: