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

** This is OUTDATED – Will be updated soon **

image

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

** NOTE: At this time this is just “Sample Code” and does not implement proper security (because we are waiting for Authentication Blazor components from the ASP.NET Core team) **

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.Shared.Models.SQLAdmin.Models
{
    public class SQLAdminInfo
    {
        public string Content { get; set; }
    }
}

 

image

Next, we create the main page of the module using the following code:

 

@using Oqtane.Modules
@using Oqtane.Shared.Models.SQLAdmin.Models
@using Oqtane.Shared
@using Oqtane.Services
@using System.Net.Http
@inherits ModuleBase
@inject IUriHelper UriHelper
@inject HttpClient http
@if (@PageState.User != null && @PageState.User.IsSuperUser)
{
    <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>
}
@functions {
    private string apiurl;
    private string SqlTextCommand =
        "Select * from Page";
    private string SqlResponse =
        "";
    public async Task ExecuteSQLCommand()
    {
        ServiceBase sb = new ServiceBase();
        apiurl =
            sb.CreateApiUrl(
                UriHelper.GetAbsoluteUri(),
                "SQLAdmin");
        var response =
            await http.PostJsonAsync<SQLAdminInfo>(
                apiurl,
                SqlTextCommand);
        SqlResponse = response.Content;
    }
}

 

 

image

The main module code calls the controller, that receives the SQL commands, executes them in the database, and returns the response:

 

using System.Data.SqlClient;
using System.Text;
using Microsoft.AspNetCore.Mvc;
using Oqtane.Models;
using Oqtane.Repository;
using Oqtane.Shared.Models.SQLAdmin.Models;
namespace Oqtane.Server.Modules.SQLAdmin.Controllers
{
    [Route("{site}/api/[controller]")]
    public class SQLAdminController : Controller
    {
        private Tenant tenant;
        public SQLAdminController(ITenantRepository TenantRepository)
        {
            // Oqtane supports multiple Tenants
            // Get the current Tenant
            tenant = TenantRepository.GetTenant();
        }
        // POST api/<controller>
        [HttpPost]
        public SQLAdminInfo Post([FromBody] string SqlTextCommand)
        {
            // In a future version of Blazor we will have 
            // this.User.Identity populated so we can
            // use ** server side ** code such as this
            // to check that a user should have access 
            //if(this.User.Identity.IsAuthenticated)
            //{
            //    if(this.User.Identity.Name == "Host")
            //    {
            //        // Do a thing...
            //    }
            //}
            StringBuilder html = new StringBuilder();
            string connectionString = tenant.DBConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            SQLAdminInfo sQLAdminInfo = new SQLAdminInfo();
            try
            {
                using (connection)
                {
                    connection.Open();
                    if (SqlTextCommand.ToLower().StartsWith("select"))
                    {
                        #region SQL QUERY
                        SqlCommand command = 
                            new SqlCommand(SqlTextCommand, 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(SqlTextCommand, 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