C# / SQL: How to get easy/automatic mapping of a Database table to Class?

Question

I don't have much experience of C# & Database. My chef engineer has built tables in a database. In C# code he has built a class for each table with all the information that is already in the database table (see code extract). I am wondering if this is good practice and if there isn't a proper and easier way to have table information in one place. Are there easy to use tools that can create a table class out of a table definition? Is something built in Visual Studio? Also he programmed stored procedures for each table to access or store one row. So if there would be a future change in the schema (one row of a table), this demands altering three different parts. I wonder if this is good practice.

Class code extract:

public class Users: DataObjectBase
{
    #region Ctor
    public Users(string connString) : base()
    {
        _objName = "Users";
        _connectionString = connString;

        _columnNames = new string[] { "UserID", "FirstName", "LastName", "LoginName", "PWHash", "MediaID", "IsActive" };
        _columnSqlTypes = new SqlDbType[] { SqlDbType.Int, SqlDbType.NVarChar, SqlDbType.NVarChar, SqlDbType.NVarChar, SqlDbType.NVarChar, SqlDbType.BigInt, SqlDbType.Bit };
        _columnTypes = new string[] { "System.Int32", "System.String", "System.String", "System.String", "System.String", "System.Int64", "System.Boolean"};
        _columnSizes = new int[] { 4, 100, 100, 100, 40, 8, 1 };
        _fieldCount = 7;
        base.Initialize();
        //("Users", connString);
    }

.... I want to learn doing it the proper way. Please describe it easy, as I don't understand some technical terms yet. Thanks for your patience and any hints or reccomondations.

To built a database application and learning to handle database tables efficiently.

Answer 1

Actually your chef engineer have been used Database-first approach , but it is not advisable to use as if we need to change the schema , we would face difficulties , instead you can use code-first approach here. We will be writing the C# class which is called as 'Model' for example

public class StudentDetails
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string City {get ; set; }

}

If you need to add primaryKey or ForeignKey , you can use annotation like [PrimaryKey], [ForeignKey] as mentioned below.

  public class StudentDetails
{
    [PrimaryKey]
    public int Id { get; set; }
    [ForeignKey]
    public string Name { get; set; }
    public string City {get ; set; }

}

After creating this Class in Model Folder, then you need to create a dbContext file then add the following lines of code in it

public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
            
        }
      
        public DbSet<RegisterUser> UserAccoutDetails { get; set; }
}

Every model created should be updated in the dbContext file.After adding DbSet then you need to migrate to database, for migration you need to execute the following code in Terminal

Add-migration InitialMigration

InitialMigration is a name given to my migrationfile, after executing the above line , a folder called Migration will be generated . Now the model will not be not updated in database , to update u need to execute the below line.

update-database

now you could see the table will created in database. This approach is advisable , because if you need to add a column to the table in database , you need to just add the column name in the model then again execute the migration line , now you should change the migration file name and after executing it then execute the update-database , now you check the table in the database , it will be updated.