Dapper examples using SQL and C#

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	var p = new { LastName = "Doe" };
	string sql = "Select * from dbo.Person where LastName = @lastName";
	return cnn.Query<PersonModel>(sql, p);
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	string sql = @"select pe.*, ph.* from dbo.Person pe
		left join dbo.Phone ph
		on pe.CellPhoneId = ph.Id;";
		
	var people = cnn.Query<PersonModel, PhoneModel, FullPersonModel>(
		sql,
		(person, phone) => 
		{ 
			person.CellPhone = phone; 
			return person; 
		});
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	var parameters = new { LastName = "Doe" };
	
	string sql = @"select pe.*, ph.* from dbo.Person pe
		left join dbo.Phone ph
		on pe.CellPhoneId = ph.Id
		where pe.LastName = @LastName;";
		
	var people = cnn.Query<PersonModel, PhoneModel, FullPersonModel>(
		sql,
		(person, phone) => 
		{ 
			person.CellPhone = phone; 
			return person; 
		},
		parameters);
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	string sql = @"select * from dbo.People;
		select * from db.Phone;";
	
	List<PersonModel> people = null;
	List<PhoneModel> phones = null;
	
	using(var lists = cnn.QueryMultiple(sql)){
		people = lists.Read<PersonModel>().ToList();
		phones = lists.Read<PhoneModel>().ToList();
	}
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	string sql = @"select * from dbo.People where LastName = @LastName;
		select * from db.Phone where PhoneNumber like '%' + @PartialPhoneNumber + '%';";
	
	List<PersonModel> people = null;
	List<PhoneModel> phones = null;
	
	var parameters = new{ LastName = "Doe", PartialPhoneNumber = '1234' };
	
	using(var lists = cnn.QueryMultiple(sql, parameters)){
		people = lists.Read<PersonModel>().ToList();
		phones = lists.Read<PhoneModel>().ToList();
	}
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	var parameters = new DynamicParameters();
	p.Add("@Id", 0, DbType.Int32, ParameterDirection.Output);
	p.Add("@FirstName", "John");
	p.Add("@LastName", "Doe");
	
	string sql = $@"insert into db.Person (FirstName, LastName)
		values(@FirstName, @LastName);
		select @Id = @@IDENTITY;";
	
	cnn.Execute(sql, parameters);
	
	int newId = p.Get<int>("@Id");
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	var parameters = new DynamicParameters();
	p.Add("@FirstName", "John");
	p.Add("@LastName", "Doe");
	
	string sql = $@"insert into db.Person (FirstName, LastName)
		values(@FirstName, @LastName);";
	
	cnn.Open();
	using(var trans = cnn.BeginTransaction())
	{
		int recordsUpdated = cnn.Execute(sql, parameters, trans);
		try
		{
			// Below statement throws and exception
			cnn.Execute("update dbo.Person set Id = 1;", transaction: trans);
			trans.Commit();
		}
		catch(Exception ex)
		{
			trans.Rollback();
		}
	}
}

using(IDbConnection cnn = new SqlConnection(GetConnectionString())
{
	var troopers = GetTroppersDataSer(); // Returns a dataset of person
	var parameters = new 
	{ 
		people = troopers.AdTableValuedParameter("TrooperModel") 
	};
	
	int recordsAffected = cnn.Execute("dbo.spPerson_InsertSet", 
		parameters, 
		commandType: CommandType.StoredProcedure);
}