Level of Difficulty: Intermediate

So you’ve created a database and now you need to make it available for third party access without actually giving people a username and password to the database. You’re familiar with how C# works and how beneficial an ASP.NET Core Web API would be in developing this solution. You have a crazy deadline though and you start wishing that some of the ground work could be automated, allowing you to enter a few parameters and the rest is automagic!

Well here’s a solution that might help and it’s all written in Python!

What are the steps?

The steps that the script will be following are:

1. Create the project
2. Scaffold the database
3. Delete the default template content
4. Create the controllers

Deep Dive

Let’s dive deeper into the steps listed above.

Create The Project

The project can be created using the dotnet CLI thus being executable from a Python script. This does mean that the dotnet CLI would need to be installed. If it is not yet installed, you can install it from here.

The project creation step consists of a few steps:

1. Create a reusable “Execute Command” method
2. Create an ASP.NET Core Web API project
3. Create a solution (.sln) file
4. Add the Web API Project to the solution

Initialise Variables

The following library is to be imported and the variables initialised so that the script will work properly:

import os

project_path = r"{}\Projects".format(os.getcwd()) # points to a folder named projects created in the same directory as this script
project_name = "TestScriptWebAPI" # the name of the project (and file that groups the project/solution files together)

start_directory = os.getcwd() # the directory of the script
start_time = datetime.now() # the time that process started

Execute Command

This method allows for any command to be executed, provided that the command and the appropriate file path are provided.

def ExecuteCommand(command, file_path):
    
    # if the file path exists and is not empty, change to the directory else return False and "File path not valid"
    if file_path != None and os.path.exists(file_path):

        os.chdir(file_path)

    else:

        return False, "File path not valid" # False depicts that the command did not run successfully due to the invalid file path

    command_output = os.popen(command).read() # command is executed

    return True, command_output # True depicts that the command was executed successfully, however, it might not be the desired out put which is why the command_output is also returned

Create an ASP.NET Core Web API Project, Solution and Linkage

This method is used to create the project, the solution and the linkage between the two.

def CreateWebAPI(project_name, project_path):
    
    # create the solution path if it doesn't exist yet
    solution_path = r"{}\{}".format(project_path, project_name)
    
    if (os.path.exists(solution_path) == False):
        
        os.mkdir(solution_path)

    # this is the command that will be run in order to create a new project.  Customising the project before creation would occur here
    command = "dotnet.exe new webapi --name {} --force".format(project_name)

    result = ExecuteCommand(command, project_path)[0]
    
    if result:
        
        print("Project successfully created")
        
    else:
        
        print("Project not created")

    # this is the command that will be run in order to create a new sln.  Customising the project before creation would occur here
    command = "dotnet.exe new sln --name {} --force".format(project_name)

    result = ExecuteCommand(command, solution_path)[0]
    
    if result:
        
        print("Solution successfully created")
        
    else:
        
        print("Solution not created")
            
    # this is the command used to add the project to the solution
    csproj_path = r"{0}\{1}\{1}.csproj".format(project_path, project_name)
    command = 'dotnet.exe sln add "{}"'.format(csproj_path)
    solution_path = r"{}\{}".format(project_path, project_name)
    
    result = ExecuteCommand(command, solution_path)[0]
    
    if result:
        
        print("Project successfully added to solution")
        
    else:
        
        print("Project not added to solution")

Now that the project has been created and added to the solution, the appropriate libraries can be installed so that the database can be scaffolded.

Scaffold Database

The database would need to already be created and validation on the database would already need to have happened. The validation would include ensuring that all tables contain primary keys.

Scaffolding the database consists of the following steps:

1. Install the libraries
2. Compile the command and connection string
3. Scaffold the database

Install The Libraries

This method installs the desired packages.

def InstallPackages(solution_path):
    
    # Install SqlServer
    command = "dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)
    
    # Install EF Design
    command = "dotnet add package Microsoft.EntityFrameworkCore.Design -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)
    
    # Install EF Tools
    command = "dotnet add package Microsoft.EntityFrameworkCore.Tools -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)

This method compiles the command string from the database connection and tables.

def CompileCommandStrings(auth, server, database, tables, username, password):
    
    # the models are created in the Models folder
    # the context is created in the Data folder

    if auth == 'Windows':
        
        connection_string = text = '"ConnectionStrings": \n\t{"DefaultConnection": "Initial Catalog=' + database + ';Data Source=' + server + '; Trusted_Connection=true;"},\n"Logging"'
        command = 'dotnet.exe ef dbcontext scaffold "Initial Catalog={};Data Source={}; Trusted_Connection=true;" Microsoft.EntityFrameworkCore.SqlServer -o Models --context-dir Data'.format(database, server)

    if auth == 'SQL':

        connection_string = text = '"ConnectionStrings": {\n\t"DefaultConnection": "Password=' + password + ';Persist Security Info=True;User ID=' + username + ';Initial Catalog=' + database + ';Data Source=' + server + '"},\n"Logging"'
        command = 'dotnet.exe ef dbcontext scaffold "Password={};Persist Security Info=True;User ID={};Initial Catalog={};Data Source={}" Microsoft.EntityFrameworkCore.SqlServer -o Models --context-dir Data'.format(password, username, database, server)

    if tables != '':

        split_table = []

        tables.replace(' ','')

        if ',' in tables:

            split_table = tables.split(',')

        if ';' in tables:

            split_table = tables.split(';')

        for table in split_table:

            command += ' -t {}'.format(table)

    command += ' -f'
    
    return command, connection_string

This method is used to scaffold the database into the project.

def ScaffoldDatabase(auth, server, database, tables, username, password, project_path):
    
    solution_path = r"{}\{}".format(project_path, project_name)
    
    InstallPackages(solution_path)
    
    result = CompileCommandStrings(auth, server, database, tables, username, password)
    command = result[0]
    connection_string = result[1]
        
    command_result = ExecuteCommand(command, solution_path)
    
    print(command_result)

Delete Template Content

When spinning up a project, a WeatherForecast.cs model is created along with a controller. These default classes need to be deleted so that they don’t interfere with the project.

This method deletes the template model and controller files that have been created with the project:

def DeleteTemplateFiles(project_path, project_name):

    # delete the template WeatherForecast.cs Model class
    template_model = r"{}\{}\WeatherForecast.cs".format(project_path, project_name)

    if os.path.isfile(template_model):
        os.remove(template_model)

    # delete the template WeatherForecast.cs Controller class
    template_controller = r"{}\{}\Controllers\WeatherForecastController.cs".format(project_path, project_name)

    if os.path.isfile(template_controller):
        os.remove(template_controller)

Create The Controllers

Creating the controllers requires the following steps:

1. Get the context name
2. Get the model
3. Compile the controller from the template
4. Create the controllers

Get The Context Name

This method gets the context class name.

def GetContext(file_path):
    
    # the file path should be the path to where the context class was created    
    f = open(file_path, "r")

    context_name = None
    
    for line in f.readlines():

        if '_context' in str(line) and 'private readonly' in str(line):
            
            line = line.replace('  ', '')
            context_name = line.split(' ')[2]
            
    return context_name

Get The Model

This method gets the model class and returns the class name, attribute as well as the namespace.

def GetModel(file_path):

    # file path should depict the path to the Model folder
    f = open(file_path, "r")

    class_name = None
    attributes = []
    namespace = None

    # for each line in the model class, extract the class name, the attributes and the namespace
    for line in f.readlines():

        if 'namespace' in str(line):

            namespace = line.split(' ')[1].split('.')[0]

        if 'public' in str(line):

            line = line.replace('  ', '')

            split_line = line.split(' ')

            if split_line[2] == 'class':

                class_name = split_line[3].replace('\n','')

            else:

                attributes.append(split_line[2])
                                                
    return class_name, attributes, namespace

Compile The Controller From The Template

This method compiles the controller class from the controller template.

def CompileControllerTemplate(model, attributes, file_path, template_file_path, namespace, context_name, id):

    file = open(file_path, "w+")

    template_file = open(template_file_path, "r+")

    template = template_file.read()

    template = template.replace('FillNamespace', namespace)
    template = template.replace('FillContext', context_name)
    template = template.replace('FillModel', model)
    template = template.replace('fillModel', model[0].lower() + model[1:])
    template = template.replace('FillId', id)

    file.writelines(template)

    file.close()
    template_file.close()

Create Controllers

This method creates the controllers.

def CreateControllers(solution_path):

    # initialise the model_file_path
    model_file_path = r"{}\Models".format(solution_path)
    
    # for each model found in the model file, get the model info and use it to create the controller
    for file in os.listdir(model_file_path):

        if file != 'ErrorViewModel.cs':

            file_path = "{}\..\Data".format(model_file_path)

            for context in os.listdir(file_path):

                context_name = context.replace('.cs','')

            file_path = '{}\{}'.format(model_file_path, file)

            model_result = GetModel(file_path)

            model = model_result[0]
            attributes = model_result[1]
            id = attributes[0]
            namespace = model_result[2]            
            path = r'{}\Controllers'.format(solution_path)
            file_path = r"{}\{}Controller.cs".format(path, model)
                        
            template_file_path = r"{}\APIControllerTemplate.cs".format(start_directory)

            if os.path.exists(path) == False:

                os.mkdir(path)

            CompileControllerTemplate(model, attributes, file_path, template_file_path, namespace, context_name, id)

Now you can hit F5 and test your API functionality. Did you get stuck anywhere or do you have feedback? Please feel free to drop it below.

Future Enhancements

Future enhancements of this code include adding SQL validation prior to the web API project creation as well as the implementation of a design pattern through the use of templates, similar to the way it was done in order to create controllers.

The project code is available here.