drop down on razor page

Question

I'm looking to populate drop down from sql server database using system.data.sqlclient and trying to call a stored procedure and return data in a list and have it appear on Razor Page.

Anyone have any good examples?

I'm not looking to use entity framework.

Answer 1

Hi @Joseph Kashishian,

Here is a whole working demo about how to use ado.net to get the data and fill the value into the dropdownlist.

Model

public class Test
{
    public int Id { get; set; }
    public string Name { get; set; }
}
public class OccupantDetail {     
    public int StaffID { get; set; } 
}

Page(Index.cshtml)

@page
@model IndexModel

<select asp-for="@Model.OccupantDetail.StaffID" asp-items='new SelectList(Model.StaffList, "Id", "Name")'></select> 

Backend(Index.cshtml.cs)

public class IndexModel : PageModel
{
     
    private readonly string _connectionString;

    public IndexModel(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }
     public List<Test> StaffList { get; set; }
     public OccupantDetail OccupantDetail { get; set; }

    public async Task OnGet()
    {
        StaffList = await GetTestAsync();
    }
    public async Task<List<Test>> GetTestAsync()
    {
        var records = new List<Test>();

        using (var connection = new SqlConnection(_connectionString))
        {
            using (var command = new SqlCommand("GetData", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                await connection.OpenAsync();
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var record = new Test
                        {
                            Id = (int)reader["Id"],
                            Name = (string)reader["Name"],
                            // Map other columns as necessary
                        };
                        records.Add(record);
                    }
                }
            }
        }

        return records;
    }
       
}

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
//......
}

Stored Procedure in SQL Server

create procedure GetData 
as
begin
 select * from dbo.Test
end

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Rena