SQL with Dapper: Quick tutorial on interfacing with SQL to complete queries without using a massive ORM like Entity Framework.

Purpose
This quick tutorial is meant to provide some snippets in using Dapper and querying SQL along with some useful ways to effectively deal with an error like System.InvalidOperationException: 'Sequence contains no elements'. Here we go:

Some ways to conduct SQL queries

using Dapper;

-- Example calling an SP that will return a single result
int result = 0;
string sql = "stored_procedure_name @parameter1";
var values = new { parameter1 = "some value" }
using (var connection = new SqlConnection(ConnectionString)) {
	result = connection.Query<int>(sql, values, commandTimeout: 60).First();
}

-- Example calling an SP that will return multiple rows
List<datamodel> resultList = new();
string sql = "stored_procedure_name @parameter1";
var values = new { parameter1 = "some value" }
using (var connection = new SqlConnection(ConnectionString)) {
	resultList = connection.Query<datamodel>(sql, values).ToList();
}

Dealing with System.InvalidOperationException: 'Sequence contains no elements'
There are a few different ways that you can deal with this error. The first example, below, shows the typical way to deal with missing elements you are querying for. The second is an anonymous function expression that can be useful when you need to do much more with regard to missing elements.

-- Example 1
string somevalue = (datamodel.Where(a => a.firstNameLabel == "Name Label").Any()) ? (datamodel.Where(a => a.firstNameLabel == "Name Label").First().firstNameValue) : null;

-- Example 2
string somevalue = new Func<string>(() => {
	string r = string.Empty;
	try {
		if (!string.IsNullOrEmpty(datamodel.Where(a => a.firstNameLabel == "Name Label").First().firstNameValue)) {
			r = datamodel.Where(a => a.firstNameLabel == "Name Label").First().firstNameValue;
		}
	}
	catch {
		r = string.Empty;
	}
	return r;
})();