C Sharp

C Sharp - SQL - Insert

Here is a code sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public virtual async Task<T> AddAsync(T entity)
{
    using var connection = _connectionFactory.CreateConnection();
    using var transaction = connection.BeginTransaction();

    try
    {
        var sql = "INSERT INTO EmailMessages (CompanyCode, ApplicationCode, FromAddress, ToAddresses, Subject, Body, ImportanceFlag, HtmlFlag, SplitFlag,Status, SentAt, CreatedAt, UpdatedAt, IsDeleted, UpdatedBy, UpdatedFrom) VALUES (@CompanyCode, @ApplicationCode, @FromAddress, @ToAddresses, @Subject, @Body, @ImportanceFlag, @HtmlFlag, @SplitFlag, @Status, @SentAt, @CreatedAt, @UpdatedAt, @IsDeleted, @UpdatedBy, @UpdatedFrom); SELECT CAST(SCOPE_IDENTITY() as bigint)";
        
        var id = await connection.ExecuteScalarAsync<long>(sql, entity, transaction);
        
        // Set the generated ID on the entity
        var idProperty = typeof(T).GetProperty("Id");
        if (idProperty != null)
        {
            idProperty.SetValue(entity, id);
        }

        transaction.Commit();
        return entity;
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Entity looks like this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
? entity
{Email.Infrastructure.Entities.EmailMessage}
    ApplicationCode: "CheckBook"
    Body: "This is a test"
    CompanyCode: "MarkLand"
    CreatedAt: {8/28/2025 4:36:30 AM}
    EmailHistory: Count = 0
    ErrorLogs: Count = 0
    FromAddress: "checkbook@theCoffeePlace.com"
    Header: ""
    HtmlFlag: false
    Id: 0
    ImportanceFlag: Normal
    IsDeleted: false
    LastAttemptedAt: null
    RowVersion: {byte[0]}
    SentAt: null
    SplitFlag: false
    Status: Pending
    StatusMessage: null
    Subject: "Test message from Swagger"
    TemplateCode: ""
    TemplateParameters: ""
    ToAddresses: "wachdorfm@hotmail.com"
    UpdatedAt: {8/28/2025 4:36:30 AM}
    UpdatedBy: null
    UpdatedFrom: null

Note, the entity being passed in has properties not being written into the database.