When you buy this book you support this site! - Thank You for your support!

Oct 17

Written by: Michael Washington
10/17/2019 4:00 PM  RssIcon

image

In this article we will create a SQL Database Administration Module for Oqtane, the web application framework that runs in Microsoft Blazor.

 

Getting Started

image

See the article Creating a Hello World Module For Blazor Oqtane for instructions on getting started with Microsoft’s Blazor and Oqtane.

 

The Module

image

The module requires that you are logged in as the Host account to work.

 

image

The user can paste any SQL statement or query in the SQL Command box and click the Execute SQL button to see the response.

In this example we are creating a table called ToDoItem.

 

image

We can issue any commands such as an insert statement into the table we just created.

 

image

We can also issue select queries.

The results will show up in a table under SQL Response.

 

Creating The Module

image

Using Visual Studio, the first step is to add a new class called SQLAdminInfo.cs to the Oqtane.Shared project using the following code:

 

using System;
using System.Collections.Generic;
using System.Text;
namespace Oqtane.Models
{
    public class SQLAdminInfo
    {
        public string Content { get; set; }
    }
}

 

 

image

Next, in the Client project, we create the main page (Index.razor) and the Service (SQLAdminService.cs) using the following code:

 

Index.razor

 

@using Oqtane.Modules
@using Oqtane.Shared
@using System.Net.Http
@using Oqtane.Modules.SQLAdmin.Services
@inherits ModuleBase
@inject NavigationManager NavigationManager
@inject HttpClient http
@inject SiteState sitestate
@if (@PageState.User != null)
{
    <p>Hello <b>@PageState.User.Username</b>!</p>
    <h4>SQL Command</h4>
    <textarea rows="4" cols="35"
              placeholder="Enter SQL Command"
              @bind="SqlTextCommand" />
    <h4>SQL Response</h4>
    <div>@((MarkupString)@SqlResponse)</div>
    <button class="btn btn-success"
            @onclick="ExecuteSQLCommand">
        Execute SQL
    </button>
}
else
{
    <p>Must be logged in as host in to use this module!</p>
}
@code {
    string SqlTextCommand = "Select * from Page";
    string SqlResponse = "";
    public async Task ExecuteSQLCommand()
    {
        SQLAdminService sQLAdminService = 
            new SQLAdminService(http, sitestate, NavigationManager);
        SQLAdminInfo paramSQLAdminInfo = new SQLAdminInfo();
        paramSQLAdminInfo.Content = SqlTextCommand;
        SQLAdminInfo sQLAdminInfo = 
            await sQLAdminService.RunSQL(paramSQLAdminInfo);
        SqlResponse = sQLAdminInfo.Content;
    }
}

 

 

SQLAdminService.cs

 

using System.Threading.Tasks;
using System.Net.Http;
using Microsoft.AspNetCore.Components;
using Oqtane.Services;
using Oqtane.Shared;
using Oqtane.Models;
namespace Oqtane.Modules.SQLAdmin.Services
{
    public class SQLAdminService : ServiceBase
    {
        private readonly HttpClient http;
        private readonly SiteState sitestate;
        private readonly NavigationManager NavigationManager;
        public SQLAdminService(
            HttpClient http, 
            SiteState sitestate, 
            NavigationManager NavigationManager)
        {
            this.http = http;
            this.sitestate = sitestate;
            this.NavigationManager = NavigationManager;
        }
        private string apiurl
        {
            get { 
                return CreateApiUrl(
                    sitestate.Alias, 
                    NavigationManager.Uri, 
                    "SQLAdmin"); 
            }
        }
        public async Task<SQLAdminInfo> 
            RunSQL(SQLAdminInfo paramSQLAdminInfo)
        {
            return 
                await 
                http.PostJsonAsync<SQLAdminInfo>(
                    apiurl, paramSQLAdminInfo);
        }
    }
}

 

image

The Service (SQLAdminService.cs) code calls the Controller (in the Server project), that receives the SQL commands, executes them in the database, and returns the response:

 

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Oqtane.Models;
using Oqtane.Repository;
using Oqtane.Services;
using Oqtane.Shared;
namespace Oqtane.Server.Modules.SQLAdmin.Controllers
{
    [Route("{site}/api/[controller]")]
    public class SQLAdminController : Controller
    {
        private Tenant tenant;
        public SQLAdminController(ITenantRepository Tenants)
        {
            // Oqtane supports multiple Tenants
            // Get the first Tenant
            tenant = Tenants.GetTenant(1);
        }
        // POST api/<controller>
        // Only allow Host to call this 
        [HttpPost]
        [Authorize(Roles = Constants.HostRole)]
        public SQLAdminInfo Post([FromBody] SQLAdminInfo paramSQLAdminInfo)
        {
            StringBuilder html = new StringBuilder();
            string connectionString = tenant.DBConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            SQLAdminInfo sQLAdminInfo = new SQLAdminInfo();
            try
            {
                using (connection)
                {
                    connection.Open();
                    if (paramSQLAdminInfo.Content.ToLower().StartsWith("select"))
                    {
                        #region SQL QUERY
                        SqlCommand command =
                            new SqlCommand(paramSQLAdminInfo.Content, connection);
                        var reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            html.Append("<table border = '1'>");
                            html.Append("<table>");
                            int index = 1;
                            while (reader.Read())
                            {
                                if (index == 1)
                                {
                                    html.Append("<tr>");
                                    for (int i = 0;
                                        i < reader.FieldCount;
                                        i++)
                                    {
                                        html.Append("<td>");
                                        html.Append(reader.GetName(i));
                                        html.Append("</td>");
                                    }
                                    html.Append("</tr>");
                                }
                                index++;
                                html.Append("<tr>");
                                for (int i = 0;
                                    i < reader.FieldCount;
                                    i++)
                                {
                                    html.Append("<td>");
                                    html.Append(reader.GetValue(i));
                                    html.Append("</td>");
                                }
                                html.Append("</tr>");
                            }
                            html.Append("</table>");
                            sQLAdminInfo.Content = html.ToString();
                        }
                        else
                        {
                            html.Append("<table border = '1'>");
                            html.Append("<table>");
                            html.Append("<tr><td>No rows</td></tr>");
                            html.Append("</table>");
                            sQLAdminInfo.Content = html.ToString();
                        }
                        reader.Close();
                        #endregion
                    }
                    else
                    {
                        #region SQL COMMAND
                        SqlCommand command =
                            new SqlCommand(paramSQLAdminInfo.Content, connection);
                        command.ExecuteNonQuery();
                        html.Append("<table border = '1'>");
                        html.Append("<table>");
                        html.Append("<tr><td>Command Executed!</td></tr>");
                        html.Append("</table>");
                        sQLAdminInfo.Content = html.ToString();
                        #endregion
                    }
                }
            }
            catch (System.Exception ex)
            {
                sQLAdminInfo.Content = ex.Message;
            }
            return sQLAdminInfo;
        }
    }
}

 

Note: See: Creating a Hello World Module For Blazor Oqtane for instructions on instantiating the module in the Oqtane framework.

 

Links

Creating a Hello World Module For Blazor Oqtane

Creating a Database Driven Module For Blazor Oqtane

www.oqtane.org

 

Blazor.net

Get started with Blazor

Build your first Blazor app

Steve Sanderson notes on Blazor Authentication and Authorization

 

Announcing Oqtane... a Modular Application Framework for Blazor!

Oqtane (GitHub)

Oqtane Custom Module Sample (GitHub)

Tags: Blazor , Oqtane
Categories:

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
Microsoft Visual Studio is a registered trademark of Microsoft Corporation / LightSwitch is a registered trademark of Microsoft Corporation