Live Chat Icon For mobile
Live Chat Icon

How do I use stored procedures in Blazor application?

Platform: Blazor| Category: General

Stored procedures are pieces of reusable code that you can save in a database data dictionary. They help you extract, edit, and remove data from a database without writing the code to do so again and again. It also saves your time, lessens your workload, and increases your productivity.

Follow these steps to use stored procedures in a Blazor application:

  1. Create a new Blazor application and add the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer NuGet packages using the NuGet package manager.

  2. Create a class named Empclass.cs in the Data folder and define the properties that are already in the database table.
    [Empclass.cs]

    public class EmpClass
    {
       [Key]
       public int Empid { get; set; }
       public string Empname { get; set; }
       public int Salary { get; set; }
    }

  3. Add the virtual property for the DbSet from Empclass to display records in the database under the Data folder.
    [ApplicationDbContext.cs]

    public class ApplicationDbContext : DbContext
    {
       public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
       {}
       public virtual DbSet<EmpClass> DisplayRecords { get; set; }
    }

  4. Connect the SQL Server database to the Blazor application by choosing (View –> Server Explorer). Right-click the Data Connections and choose Add connection in Visual Studio. Add your server name and DB name to connect the database to the Blazor application.

    The database is now connected to the Blazor application.

  5. Add the connection strings configurations to the appsetting.json file.
    [appsetting.json]

    {
      "ConnectionStrings": {
        "Myconnection": "Data Source={{Server_Name}};Initial Catalog={{DataBase_Name}};Integrated Security=True"
      },
      "Logging": {
        // . . .
      },
      "AllowedHosts": "*"
    }

  6. Create an Empservices class in the Services folder and define the FromSqlRaw extension method to execute a stored procedure.
    [Empservices.cs]

    using System.Linq;
    using {{Your_App_Name}}.Data;
    using Microsoft.EntityFrameworkCore;
    public class Empservices
        {
            protected readonly ApplicationDbContext _dbcontext;
     
            public Empservices(ApplicationDbContext _db)
            {
                _dbcontext = _db;
            }
     
            public EmpClass[] GetEmpDetails()
            {
                EmpClass[] empobj;
                // Here "exec Employee" is a stored procedure of SQL Server Database.
                empobj = _dbcontext.DisplayRecords.FromSqlRaw("exec Employee").ToArray();
                return empobj;
            }
        }

  7. Add the DbContext configuration to the Startup.cs file.
    [Startup.cs]

    using Microsoft.EntityFrameworkCore;
    using {{Your_App_Name}}.Services;
    public class Startup
    {
      public void ConfigureServices(IServiceCollection services)
       {
          services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("Myconnection")));
          services.AddScoped<Empservices>();
       }
       // . . .
    }

  8. Now call the GetEmpDetails() method in the Index.razor file and the table to display the database records.
    [Index.razor]

    @page "/"
    @using {{Your_App_Name}}.Data
    @using {{Your_App_Name}}.Services
    @inherits OwningComponentBase<Empservices>
     
    <table border="1">
        <tr>
            <th>Empid</th>
            <th>Empname</th>
            <th>Salary</th>
        </tr>
        @foreach(var emp in empdetails)
        {
         <tr>
             <td>@emp.Empid</td>
             <td>@emp.Empname</td>
             <td>@emp.Salary</td>
         </tr>
        }
    </table>
     
    @code {
        EmpClass[] empdetails;
        protected override void OnInitialized()
        {
            // Here Service property is inherited from OwningComponentBase.
            empdetails = Service.GetEmpDetails();
        }
    }

  9. Press Ctrl + F5 to run the application and see the output in the following image.

Share with

Share on twitter
Share on facebook
Share on linkedin

Related FAQs

Couldn't find the FAQs you're looking for?

Please submit your question and answer.