Push your skills
Tags: beginners, database, csharp, dotnet
Published: 2024-06-02 18:44:26

## Introduction

Learn how to become a better developer requires continuous improvement of one’s skills. How does one learn to grow and become a better developer? Let’s explore several ideas which overall will work for the majority of developers. Code samples are all in C# which were selected as they are not common place for most developers which was done internally.

## Steps

- [Pluralsight](https://www.pluralsight.com/) which is a paid for site with hundreds of course on C#. Start off using their AI assessment which will direct you on the proper path. Many of the courses have their own assessments too. Pluralsight makes it easy to learn from highly rated authors to accessing courses from any device e.g. laptop, phone or tablet. Pluralsite has a free trial and also from time to time discounts on purchasing a subscription.

- Use [Microsoft Learn](https://learn.microsoft.com/en-us/training/). Whether you're just starting in a career, or you are an experienced professional, our self-directed approach helps you arrive at your goals faster, with more confidence and at your own pace. Develop skills through interactive modules and paths or learn from an instructor. Learn and grow your way.

- Take time to read Microsoft documentation e.g. read up on [general structure of a C# Program, types operators and expressions statements](https://learn.microsoft.com/en-us/dotnet/csharp/fundamentals/program-structure/) various [classes](https://learn.microsoft.com/en-us/dotnet/api/system.string?view=net-6.0) [Object-Oriented programming](https://learn.microsoft.com/en-us/dotnet/csharp/fundamentals/tutorials/oop) to name a few topics.

- During your learning try and keep things simple using either console or unit test projects, in other words separate backend learning from front end user interface learning.

- At some point in time when you feel comfortable, scope out a simple project, write out task before coding then write the code rather than thinking and coding at the same time. Thinking and coding at a novice level is simply out is a disaster waiting to happen.

- When seeking out information on the web and a solution is found do not simply copy and paste, examine the code, try and figure out what it's doing first before using said code.

- Learn how to use GitHub in Visual Studio to backup and version code. Suppose you wrote code and broke it, with proper versioning in a GitHub repository you can revert changes and un-break code.

- Use .NET Framework Core 6 or .NET Core Framework 8 rather than .NET Framework classic as there are more benefits to using .NET Core

- If learning to work with data, start off with SQL-Server Express and install SSMS (SQL-Server Management Studio) along with learning to work with Entity Framework Core.

- Know full well that moving slow is better than moving fast when learning any language and that nobody knows it all.

![Learn how to use the debugger](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xp3uj4nuhzx7sple8tad.png)

## Tools to accelerate learning

Microsoft Visual Studio is the absolute best IDE (Integrated Development Environment) which with the following items can increase learning and save time while coding.

- Red Gate [SQL-Prompt](https://www.red-gate.com/products/sql-prompt/) for Visual Studio and SSMS
   - Advanced IntelliSense-style code completion
   - Refactor SQL code
   - SSMS SQL History
   - And much more
- Jetbrains [ReSharper](https://www.jetbrains.com/resharper/) which an invaluable Visual Studio extension.
- [EF Power Tools](https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools) easy to reverse engineer SQL-Server databases for EF Core
 

## Diving into code basics

Once the basics have been mastered looks for code samples that will assist in growing to be a better developer.

One possible path is working with databases using [Microsoft Entity Framework Core](https://learn.microsoft.com/en-us/ef/core/) (EF Core) or using a data provider like [Dapper](https://www.learndapper.com/).

There are other ways to work with data yet EF Core and Dapper are the best  in regards to performance and easy learning.

When finding code samples on the web, make sure they work with the .NET Framework for your project as a .NET Framework 4.8 code sample will be vastly different from a .NET Core 8 Framework.

Every year Microsoft creates code samples for EF Core but in many cases may not be structured for inexperienced developers to learn from so Karen Payne took the EF Core 8 code samples and created the following [article](https://dev.to/karenpayneoregon/microsoft-entity-framework-core-8-samples-3dj8)/[repository](https://github.com/karenpayneoregon/ef-code-8-samples) which in most cases will be easy to learn from. 

## Lesson 1 - SQL-Server Computed columns

### EF Core version

{% cta https://github.com/karenpayneoregon/sql-basics/tree/master/EF_CoreBirthdaysComputedColumns %} Sample project {% endcta %}

A [computed column](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16) is a virtual column that isn't physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in SQL Server by using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

For a full article see [SQL-Server: Computed columns with Ef Core](https://dev.to/karenpayneoregon/sql-server-computed-columns-with-ef-core-3h8d)

But here we will create a computed column from start and walkthrough usage  using both EF Core and Dapper.

Originals came from the following Stackoverflow [post](https://stackoverflow.com/questions/9/how-do-i-calculate-someones-age-based-on-a-datetime-type-birthday?page=2&tab=modifieddesc#tab-top). Take a birthdate and current date, subtract current date from birthdate and divide by 10,000.

In SSMS (SQL-Server Management Studio)

Note in the code sample the full database exists in the project EF_CoreBirthdaysComputedColumns under the scripts folder. Before running the script, create the database in SSMS than run the script to create the table and populate with data.

Also note in the code sample the connection string resides in appsettings.json using NuGet package [ConsoleConfigurationLibrary](https://www.nuget.org/packages/ConsoleConfigurationLibrary/).

**Table structure**

![Table structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/06sfbbb9i4ru5203l1nx.png)

**SQL**

![SELECT statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8bqiabe7j0a4nvez1yqa.png)

Breaking the statement apart.

- Format both dates with date separators and cast each to an integer.

- Subtract birth date from current date, parentheses are important.

- Divide the above by 10,000 to get the years old.

**Results**

![results from SELECT](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvzc25dc1ojr4arurnqy.png)

Now create a new column of type nvarchar for the table named YearsOld and take this statement and place into the computed column property followed by saving changes.

```sql
(CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INTEGER) - CAST(FORMAT(BirthDate, 'yyyyMMdd') AS INTEGER)) / 10000
```

![table design in ssms](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tvwq73hel66uumzbixh5.png)

- Create a new C# Console project.

- Add a dependency for [Microsoft.EntityFrameworkCore.SqlServer](https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer/8.0.0?_src=template)

- Install Visual Studio extension [EF Power Tools](https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools). To learn how to use EF Power Tools see the following [video](https://www.youtube.com/watch?v=uph-AGyOd8c) by the author. Add [full documentation](https://github.com/ErikEJ/EFCorePowerTools/wiki/Reverse-Engineering).

Once using EF Power Tools the following classes are generated.

The model which represents the SQL-Server database table.

```csharp
public partial class BirthDays
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateOnly? BirthDate { get; set; }

    public int? YearsOld { get; set; }
}
```

The, what is known as a [DbContext](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbcontext?view=entity-framework-6.2.0) and configuration to interact with the database.

Note [HasComputedColumnSql](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationalpropertybuilderextensions.hascomputedcolumnsql?view=efcore-8.0) on YearsOld which is our computed column.

```csharp
public partial class Context : DbContext
{
    public Context()
    {
    }

    public Context(DbContextOptions<Context> options)
        : base(options)
    {
    }

    public virtual DbSet<BirthDays> BirthDays { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer(DataConnections.Instance.MainConnection);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BirthDays>(entity =>
        {
            entity.Property(e => e.YearsOld).HasComputedColumnSql("((CONVERT([int],format(getdate(),'yyyyMMdd'))-CONVERT([int],format([BirthDate],'yyyyMMdd')))/(10000))", false);
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
```

> **Note**
> There are two camps for performing the above work, database first or code first. For those just beginning with EF Core the above, database first is the best path.

To view the data [Spectre.Console](https://spectreconsole.net/) is used to create a pretty table.

```csharp
internal partial class Program
{
    static async Task Main(string[] args)
    {
        await Setup();
        var table = CreateTable();
        await using (var context = new Context())
        {
            var list = await context.BirthDays.ToListAsync();
            foreach (var bd in list)
            {
                table.AddRow(
                    bd.Id.ToString(),
                    bd.FirstName,
                    bd.LastName,
                    bd.BirthDate.ToString(),
                    bd.YearsOld.ToString());
            }

            AnsiConsole.Write(table);
        }
        ExitPrompt();
    }
    public static Table CreateTable()
    {
        var table = new Table()
            .AddColumn("[b]Id[/]")
            .AddColumn("[b]First[/]")
            .AddColumn("[b]Last[/]")
            .AddColumn("[b]Birth date[/]")
            .AddColumn("[b]Age[/]")
            .Alignment(Justify.Left)
            .BorderColor(Color.LightSlateGrey);
        return table;
    }
}
```

![Screenshot for above code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i60dye7au7fve08y1wfu.png)

To get our data, one line of code to instantiate EF Core and one line to read the data. EF Core is also great for relational databases, see the following [repository](https://github.com/karenpayneoregon/ef-code-8-samples). 

For logging SQL generated by EF Core, see the following [project](https://github.com/karenpayneoregon/ef-code-8-samples/tree/master/DualContextsApp) which also shows working with two different instances of SQL-Server.

### Dapper version

{% cta https://github.com/karenpayneoregon/sql-basics/tree/master/DapperBirthdaysComputedColumns %} Sample project {% endcta %}

Unlike EF Core, with Dapper a developer writes SQL statements in SSMS and adds the valid statement to code. For more on Dapper see my [series](https://dev.to/karenpayneoregon/series/25270).

Here the SQL is stored in a read-only string, the alternate is to stored the (or any statements) in stored procedures.


![Learn to write proper SQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uyn1awyunv0pvbhtgzy1.png)

```csharp
internal class SqlStatements
{
    public static string GetBirthdays =>
        """
        SELECT Id
            ,FirstName
            ,LastName
            ,BirthDate
            ,YearsOld
        FROM BirthDaysDatabase.dbo.BirthDays
        """;
}
```

Code to read data.

```csharp
internal class DapperOperations
{
    private IDbConnection _cn;

    public DapperOperations()
    {
        _cn = new SqlConnection(DataConnections.Instance.MainConnection);
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
        SqlMapper.AddTypeHandler(new SqlTimeOnlyTypeHandler());
    }

    public async Task<List<BirthDays>> GetBirthdaysAsync()
    {
        return (await _cn.QueryAsync<BirthDays>(SqlStatements.GetBirthdays)).AsList();
    }

}
```
In the class constructor

1. Create a connection using [Microsoft.Data.SqlClient](https://www.nuget.org/packages/Microsoft.Data.SqlClient/5.2.1?_src=template) NuGet package.

1. Add ability to Dapper to understand DateOnly type using [kp.Dapper.Handlers](https://www.nuget.org/packages/kp.Dapper.Handlers/1.0.0?_src=template) NuGet package.

Read data is a one liner which indicates we want a list of BirthDays asynchronously.

```csharp
public async Task<List<BirthDays>> GetBirthdaysAsync()
{
    return (await _cn.QueryAsync<BirthDays>(SqlStatements.GetBirthdays)).AsList();
}
```

Back in Program.cs, the code is the same as EF Core except creating an instance of the Dapper class and calling a method.

```csharp
internal partial class Program
{
    static async Task Main(string[] args)
    {
        await Setup();

        var table = CreateTable();
        var operations = new DapperOperations();
        var list = await operations.GetBirthdaysAsync();
        foreach (var bd in list)
        {
            table.AddRow(
                bd.Id.ToString(),
                bd.FirstName, 
                bd.LastName, 
                bd.BirthDate.ToString(), 
                bd.YearsOld.ToString());
        }

        AnsiConsole.Write(table);

        ExitPrompt();
    }

    public static Table CreateTable()
    {
        var table = new Table()
            .AddColumn("[b]Id[/]")
            .AddColumn("[b]First[/]")
            .AddColumn("[b]Last[/]")
            .AddColumn("[b]Birth date[/]")
            .AddColumn("[b]Age[/]")
            .Alignment(Justify.Left)
            .BorderColor(Color.LightSlateGrey);
        return table;
    }
}
```

### Summary for computed columns

Not every single aspect of code has been covered in detail which means before adapting the techniques in your projects take time to dissect code and what NuGet packages were used. Also consider running the code through [Visual Studio debugger](https://learn.microsoft.com/en-us/visualstudio/get-started/csharp/tutorial-debugger?view=vs-2022).

Debugging is something many novice developers overlook and is one of the best features of Visual Studio. Learn how to debug does not take a great deal of time.

## Lesson 2 - Refactoring code

Many believe that the main thing about coding is to get the code working then come back and refactor the code. From personal experience this tends not to happen. This is the very reason developers need to hone their skills outside of work projects.


![never stop learning](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ju4889uewhkpfrfmp0w5.png)

### Example 1

A developer is asked to split a string on upper cased characters in a string and place a string in front.

Example, given ThisIsATest the output would be This Is A Test. The developer search the web and finds the following.

```csharp
public static class StringExtensions
{
    private static readonly Regex CamelCaseRegex = new(@"([A-Z][a-z]+)");
    /// <summary>
    /// KarenPayne => Karen Payne
    /// </summary>
    [DebuggerStepThrough]
    public static string SplitCamelCase(this string sender) =>
        string.Join(" ", CamelCaseRegex.Matches(sender)
            .Select(m => m.Value));
}
```

This works but there is a better version which in the following example was written by GitHub Copilot and is the second iteration meaning the first time copilot was ask, it provided a unoptimized solution because how the question was asked.

```csharp
[DebuggerStepThrough]
public static string SplitCamelCase(this string input)
{
    if (string.IsNullOrEmpty(input))
    {
        return input;
    }

    Span<char> result = stackalloc char[input.Length * 2];
    var resultIndex = 0;

    for (var index = 0; index < input.Length; index++)
    {
        var currentChar = input[index];

        if (index > 0 && char.IsUpper(currentChar))
        {
            result[resultIndex++] = ' ';
        }

        result[resultIndex++] = currentChar;
    }

    return result[..resultIndex].ToString();
}
```

![Less code is not always best](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i8u0zjrr2qjgot6tmav8.png)

Wait a minute, the second version has a lot more code, how can this version be better? Both novice to experience developers have a mind-set that less lines of code is better, perhaps for readability. Sure a developer should always strive to write readable code yet code with many lines of code can be easy to read too.

How to write readable code.

- Use meaning variable names e.g. in a for statement, index rather than i or firstName rather than fName.
- Fold code rather than one line as shown below

```csharp
public static class CheckedListBoxExtensions
{
    public static List<T> CheckedList<T>(this CheckedListBox sender)
        => sender.Items.Cast<T>()
            .Where((_, index) => sender.GetItemChecked(index))
            .Select(item => item)
            .ToList();
}
```
Rather than

```csharp
public static class CheckedListBoxExtensions
{
    public static List<T> CheckedList<T>(this CheckedListBox sender)
        => sender.Items.Cast<T>().Where((_, index) => sender.GetItemChecked(index)).Select(item => item).ToList();
}
```
## Next steps

Here are a few ideas that even many experienced developers avoid, not you!!! 

- [Generics](https://learn.microsoft.com/en-us/dotnet/csharp/fundamentals/types/generics)
- [Interfaces](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/interface)
- Creating common libraries
- [JSON serialization and deserialization](https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/overview)

## Summary

These are a few of many more tips to becoming a better developer. And the only way this will happen is to continually learn outside of projects.

If your boss or team lead does not provide time to learn new skills its up to you to take an hour or two each week to learn and grow.