Level of Difficulty: Intermediate – Senior.
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:
- Create the project
- Scaffold the database
- Delete the default template content
- 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:
- Create a reusable “Execute Command” method
- Create an ASP.NET Core Web API project
- Create a solution (.sln) file
- 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:
- Install the libraries
- Compile the command and connection string
- 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:
- Get the context name
- Get the model
- Compile the controller from the template
- 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.
The JPanda 
I have recently used the dotnet aspnet-codegenerator to scaffold controllers which now allows me to deprecate the “Controller Template”. For those of you who may be interested, check out the official MSDN docs around this: https://docs.microsoft.com/en-us/aspnet/core/fundamentals/tools/dotnet-aspnet-codegenerator?view=aspnetcore-3.1
LikeLike