build razor page with ado .net stored procedure

Question

sql server

Answer 1

Hi @walter piol

build razor page with ado .net stored procedure

You can refer to the following steps:

1. Create a Razor page project and install the System.Data.SqlClient package via Nuget (Right click the Razor page project, select the "Manage Nuget Package..." option, then browse this package and install it).
2. Create a stored procedure in the database and get all Events (the stored procedure name is : GetAllEvents).
3. In the appsettings.json file, add the SQL server database connection string. Like this:

1. Add a razor page: AdoTest.cshtml:

        @page
        @using System.Data
        @model RazorWebApp.Pages.AdoTestModel


        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>EventID</th>
                <th>EventName</th>
                <th>Status</th>
            </tr>
            @foreach (DataRow row in Model.Events.Tables[0].Rows)
            {
                <tr>
                    <td>@row["EventID"]</td>
                    <td>@row["EventName"]</td>
                    <td>@row["Status"]</td>
                </tr>
            }
        </table>

and the Ado.Test.cshtml.cs file like this:

        using Microsoft.AspNetCore.Mvc.RazorPages;
        using System.Data;
        using System.Data.SqlClient;

        namespace RazorWebApp.Pages
        {
            public class AdoTestModel : PageModel
            {
                //inject the IConfiguration, after that we can get the connection string via Configuration.
                private readonly IConfiguration configuration; 
                public AdoTestModel(IConfiguration Configuration)
                {
                    configuration=Configuration;
                }
                public DataSet Events { get; set; }
                public void OnGet()
                {
                    DataSet ds = new DataSet();
                    string constr = configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");

                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        string query = "exec GetAllEvents";
                        using (SqlCommand cmd = new SqlCommand(query))
                        {
                            cmd.Connection = con;
                            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                            {
                                sda.Fill(ds);
                            }
                        }
                    }
                    Events = ds;
                }
            }
        }

1. After that the result as below:

---

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,

Dillion