Post
Ask Question

Generate Dynamic Models in C# .NET 8 MVC from Database

Introduction

Creating models for database tables is important to populate data. But what if you are required to generate models dynamically instead of creating classes and properties for your model? In this case, you will need to write code that will create the models at runtime according to the table schema. In this article, we'll use C# .NET 8 MVC to create a dynamic model generator that can automatically generate models from your database table schema. 

What is dynamic model generation?

In traditional C# .NET MVC development, developers often spend a huge amount of time creating models that are the same as the structure of their database tables. This process involves defining properties for each column in the table, which can be time-consuming, error-prone, and not very scalable, especially for applications with large databases. Dynamic model generation is the process of creating models dynamically at runtime based on the schema of the database tables. Instead of manually writing model classes for each table, we can automate this process by querying the database for its schema and dynamically generating the corresponding C# classes. 

Creating dynamic models using C#.NET 8 Project

I will be explaining all the steps from scratch so if you have already created the project you can skip the project creation part. Now, let's see how to do this by creating a project.

Step 1. Prerequisites

  • .NET 8, since, I will proceed with.NET 8, it must be installed (If you are using another version, that's up to your requirement).

Step 2. Setting up the Project

  1. In the Visual Studio, click 'Create a new project' and click 'Next.'
  2. In templates, select 'ASP.NET Core Web App (Model-View-Controller)' and click 'Next'.
  3. In the 'Configure your new project' window, name your project, here I have named it 'DynamicModelGenerator' and click 'Next'.
  4. In the Additional Information window, in Framework, choose '.NET 8' and click 'Create'.
  5. Now, the project is created and the project structure looks like the below image.

Project Structure

Step 3. Install Packages

Now, let's install the required packages for our project. For our project, we will use Entity for performing database operations. So, let's install it.

  1. Click 'Project' and select 'Manage NuGet Packages'.
  2. In 'Browse', enter 'EntityFramework' in the search box.
  3. Select it and click 'Install'.
  4. A popup window will open, click, 'I accept'. 
  5. Entity Framework is now installed and looks like below.

Entity framework

Step 4. Configure the connection string

Now, let's add a connection string in the appsettings.json file. Replace the Server, Database, user id, and Password according to your database.

  "ConnectionStrings": {
    "DefaultConnection": "Server=******;Database=******;user id=******;Password=******;MultipleActiveResultSets=true;Encrypt=False;"
  },

Step 5. Create Index View

In the HomeController, we have an Index method, by default, it has some boilerplate code written. Let's change it according to our requirements. Here, I am changing some basic code that is needed for our implementation.

In Index.cshtml

@{
    ViewData["Title"] = "Home Page";
}

<div>
    <h2>Dynamic Model Generator</h2>
    <p>Generate dynamic models and use them in your application.</p>
    <p>Click <a href="/get-database-tables">here</a> to get all the tables present in your database.</p>
</div>

In the above, I have added a title to the page, a heading in the home page some info in <p> tag and <a> tag to provide a link on whose click, and a method having route 'get-database-tables' will be called. The view will look like below.

Home page

Step 6. Create ModelGenerator Controller

Separating your code into different controllers according to your requirements is a good practice. So, Create a controller and name it appropriately. I have named it 'ModelGeneratorController'. Your controller will look like below.

using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;

public class ModelGeneratorController : Controller
{
    //empty controller
}

In this controller, let's add a constructor and a read-only property.

private readonly string _connectionString;

public ModelGeneratorController(IConfiguration configuration)
{
    _connectionString = configuration.GetConnectionString("DefaultConnection");
}

In the above, I added a private read-only string named _connectionString to store the database connection string. Then, I created a constructor and in that, I assigned the connection string stored in our appsettings.json file to the _connectionString property.

Step 7. Create a method to get all database tables

Now, let's create a method to get all the tables present in our database.

[HttpGet]
[Route("get-database-tables")]
public IActionResult GetDatabaseTables()
{
    var tableNames = new List<string>();

    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
        var reader = command.ExecuteReader();

        while (reader.Read())
        {
            tableNames.Add(reader.GetString(0));
        }

        reader.Close();
    }

    return View(tableNames);
}

In the above code I created a method named GetDatabaseTables(). made it HTTP GET to handle the GET request, method and gave it the route 'get-database-tables', which we used in index.cshtml page.

In this method, I created a variable tableNames, a list of string values, which will be used to store the list of tables present in the database. In the next line, I created a connection with SQL Server and then opened the connection. In the next line, I created a SQL command (SqlCommand) to execute a SQL query against the database. The query selects table names from the INFORMATION_SCHEMA.TABLES view where the TABLE_TYPE is 'BASE TABLE'.

This ensures that only base tables (not views or system tables) are included in the result. In the next line, I executed the command using ExecuteReader() and iterated over the results using a while loop. For each row in the result set, it reads the value of the first column (table name) using the reader.GetString(0) and add it to the tableNames list. After reading all the data, we closed the data reader and the database connection to release resources. At last, I passed the table names to the View().

Step 8. Create a view to show the tables present in the database

To create a view, right-click on GetDatabaseTables() method, click, 'Add View', and name the view, I named it 'GetDatabaseTables.cshtml'. Your view file is created 

In GetDatabaseTables.cshtml

@model List<string>
@{
    ViewData["Title"] = "Database Tables";
}
<!DOCTYPE html>
<html>
<head>
    <title>@ViewData["Title"]</title>
    <style>
        table {
            width: 40%;
            border-collapse: collapse;
        }

        th, td {
            padding: 8px;
            border: 1px solid #ddd;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
        }

        tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        tr:hover {
            background-color: #ddd;
        }

        a {
            text-decoration: none;
            color: blue;
        }

            a:hover {
                text-decoration: underline;
            }
    </style>
</head>
<body>
    <div>
        <h1>Database Tables</h1>
        <table>
            <thead>
                <tr>
                    <th>Table Name</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var tableName in Model)
                {
                    <tr>
                        <td>@tableName</td>
                        <td><a href="@Url.Action("CreateDynamicModel", "ModelGenerator", new { tableName = tableName })">Create Dynamic Model</a></td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</body>
</html>

In the above code, I added a directive that specifies the type of model that this view expects. In our case, it expects a List<string>, which means it will receive a list of strings from the controller. Then, added a title to the page. In the body, I created a <div>, and inside that <div>, added a for each loop to iterate over the list of table names and display them in a tabular format with some CSS styling. This will look like below.

tables

Step 9. Create a method to create a dynamic model

 [HttpGet]
 [Route("create-dynamic-model")]
 public string CreateDynamicModel(string tableName)
 {
     StringBuilder classDefinition = new();

     classDefinition.AppendLine($"public class {tableName}Model");
     classDefinition.AppendLine("{");

     List<string> propertyDeclarations = GetPropertyDeclarationsForTable(tableName);

     foreach (var propertyDeclaration in propertyDeclarations)
     {
         classDefinition.AppendLine($"    {propertyDeclaration}");
     }

     classDefinition.AppendLine("}");

     return classDefinition.ToString();
 }

In the above method, I created a StringBuilder object and then started constructing a model class by getting property declarations for the table. and inside a for each loop, generated all property declarations. Once the class construction is completed, I returned the constructed class to the view.

Step 10. Create GetPropertyDeclarationsForTable() method

In the above method, we used GetPropertyDeclarationsForTable(), now let's create the method.

 private List<string> GetPropertyDeclarationsForTable(string tableName)
 {
     List<string> propertyDeclarations = [];

     using (var oldConnection = new SqlConnection(_connectionString))
     {
         oldConnection.Open();

         string sql = $"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'";
         using var cmd = new SqlCommand(sql, oldConnection);
         using SqlDataReader reader = cmd.ExecuteReader();

         while (reader.Read())
         {
             string columnName = reader["COLUMN_NAME"].ToString();
             string dataType = reader["DATA_TYPE"].ToString();
             bool isNullable = reader["IS_NULLABLE"].ToString() == "YES";

             string propertyDeclaration = $"public {GetCSharpType(dataType, isNullable)} {columnName} {{ get; set; }}";
             propertyDeclarations.Add(propertyDeclaration);
         }

         oldConnection.Close();
     }

     return propertyDeclarations;
 }

In the above method, I created a list of strings. In the next line, I established the connection and got the data using an SQL connection. In next line, in a while loop, i got the information about the columns and constructed property declaration, using that information. Next, added that declaration to the string list. After closing the connection, I returned the property declarations.

Step 11. Create GetCSharpType() method

In the above method, we used the GetCSharpType() method, so let's create it.

private static string GetCSharpType(string dataType, bool isNullable)
 {
     switch (dataType)
     {
         case "bigint":
             return isNullable ? "long?" : "long";
         case "int":
             return isNullable ? "int?" : "int";
         case "nvarchar":
         case "varchar":
             return "string";
         default:
             return "object";
     }
 }

In this method, I added a switch case in which according to database data type, I returned the appropriate C# data type corresponding to them.

Step 12. Create a view of the CreateDynamicModel page

To create a view, right-click on CreateDynamicModel () method, click, 'Add View', and name the view, I named it 'CreateDynamicModel .cshtml'. Your view file is created 

In CreateDynamicModel.cshtml

@model string

<!DOCTYPE html>
<html>
<head>
    <title>Dynamic Model Class</title>
</head>
<body>
    <h2>Dynamic Model Class</h2>
    <pre>
        @Html.Raw(Model)
    </pre>
</body>
</html>

In the above method, the string coming from the method is shown in the view inside <pre> tag. This will look like below.

model

Here, we have successfully created a model dynamically using C# MVC.

Conclusion

In this article, we learned how to dynamically generate models in C# .NET MVC based on database table schemas. By querying the database for table information and dynamically constructing C# classes, we can streamline the model creation process and improve scalability. This approach reduces manual effort, minimizes errors, and enhances the maintainability of applications with evolving database structures.


Similar Articles