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.
Basics
To add a new record to a Person table, create an INSERT statement followed by a SELECT separated by a semi-colon.
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.
{
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 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.
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.
[
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
Open SSMS to localdb
Create a database named OutputDatabase
Run the script under Scripts/createDatabase_with_Action_In_Transaction_Table.sql
Build and run the project,
