SQL-Server add record tip

Introduction

Learn how to add a new record to a SQL-Server database table and in the same SQL statement insert the new primary key and current user name into a secondary table. This permits a way to track who added a new record. Although not shown, the same technique can be used for update and delete operations.

Sample project

Basics

To add a new record to a Person table, create an INSERT statement followed by a SELECT separated by a semi-colon.

INSERT INTO dbo.Person (FirstName,
                        LastName,
                        Gender)
VALUES ('Karen', 'Payne', 'Female');
SELECT CAST(SCOPE_IDENTITY() AS INT);

In SSMS (SQL-Server Management Studio) after executing the above statements the new identifier is shown in the results window.

Dapper sample 1

Dapper is used here while a developer can perform the same operations with a connection and command objects, Dapper simply makes the process easier.

First create the statements, here the statement is in a read only string, since Dapper handles stored procedures the statement can be in a stored procedure, here it is easy to follow in a raw string literal.

Microsoft documentation for OUTPUT clause which, in this case provides our new primary key.

internal class SqlStatements
{
    public static string InsertPerson =>
        """
        INSERT INTO dbo.Person (FirstName,
                                LastName,
                                Gender)
        OUTPUT Inserted.Id
        VALUES (@FirstName, @LastName, @Gender);
        """;
}

Here the code is in a console project in Program/Main method.

Person class

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
}

• Create a new Person
• Create a connection with the connection string in appsettings.json
• Using Dapper's QueryFirstAsync method which will return an int, first parameter is the SQL statement from above and the second parameter the person to add.
• Display the new identifier via Spectre.Console NuGet package.

Person p = new Person() { FirstName = "Karen", LastName = "Payne", Gender = "Female"};

await using var cn = new SqlConnection(DataConnections.Instance.MainConnection);

AnsiConsole.MarkupLine("[yellow]Adding a single record[/]");
var identifier = await cn.QueryFirstAsync<int>(SqlStatements.InsertPerson, p);
AnsiConsole.MarkupLine($"[cyan]Identifier: {identifier}[/]");

Dapper sample 2

Insert a new Person record and also insert a record into another table, Transaction to remember who added the Person record.

As mention above the same can be done with updates and deletions. If going this route, add another column to the transition table for what type of action was performed e.g. add, delete or edit. Also, a date column has been added to know when the action was performed.

First, here is the database structure

• Person table which is for storing people.
• Transactions table which will store an id from an insert statement for Person table and the identifier of who inserted the person record.

SQL Statement, InsertPersonToTransactions, in the OUTPUT clause the new primary key and the current user identifier are passed to the Transaction table to be added.

C# Code which will insert two records into the Person table and Transaction table.

List<Person> list =
[
    new() { FirstName = "Mary", LastName = "Adams", Gender = "Female" },
    new() { FirstName = "Bill", LastName = "Jones", Gender = "Male" }
];

await using var cn = new SqlConnection(DataConnections.Instance.MainConnection);
await cn.ExecuteAsync(SqlStatements.InsertPersonToTransactions, list);

Adding a type to the Transaction table

Add a new column, in this case named Action.

SQL Statement

The code does not change from the last example.

Summary

Code has been presented to show how to insert a new record in a primary table along with at the same time insert a new record into a secondary table. Although Dapper was used, the same will work with conventional connection and command objects.

As stated earlier, SQL statements are embedded in code while a developer may consider using stored procedures as an option.

Setup sample code

Once you have the sample code

1. Open SSMS to localdb
2. Create a database named OutputDatabase
3. Run the script under Scripts/createDatabase_with_Action_In_Transaction_Table.sql
4. Build and run the project,

🚀 Visit My GitHub Profile