C#:ejecución de consultas SQL básicas con Dapper

Este es un ejemplo del uso de Dapper para ejecutar una consulta SELECT básica:

using System.Data.SqlClient;
using Dapper;

public IEnumerable<Movie> GetAllMovies()
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies");
	}
}
Code language: C# (cs)

Dapper abstrae el código repetitivo involucrado en la ejecución de consultas SQL, incluidos los parámetros de asignación y los resultados de la consulta. Hace esto sin ninguna configuración (mapea usando reflejo).

En este artículo, mostraré más ejemplos del uso de Dapper en escenarios comunes, como agregar parámetros de consulta e insertar registros.

Nota:si aún no lo ha hecho, agregue el paquete Dapper nuget .

Agregar parámetros de consulta

Para agregar parámetros de consulta con Dapper, pase un objeto para el param argumento:

public IEnumerable<Movie> GetMoviesForYear(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies WHERE YearOfRelease=@year", 
			param: new { year });
	}
}
Code language: C# (cs)

Puede pasar cualquier objeto, incluidos los tipos anónimos (como se muestra arriba). Dapper intentará mapear las propiedades desde el param objeto a los marcadores de posición de parámetros (es decir, "@year") en la consulta.

Llamar a un proceso almacenado

Digamos que desea llamar al siguiente proceso almacenado:

CREATE PROCEDURE spGetMoviesForYear
	@year int
AS
BEGIN
	SELECT * FROM Movies WHERE YearOfRelease=@year
END
Code language: plaintext (plaintext)

Aquí hay un ejemplo de cómo llamar a este proceso almacenado con Dapper:

public IEnumerable<Movie> GetMoviesForYearSP(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("dbo.spGetMoviesForYear", 
			param: new { year }, 
			commandType: System.Data.CommandType.StoredProcedure);
	}
}
Code language: C# (cs)

Usted especifica el nombre del proceso almacenado, cualquier parámetro y establece el argumento CommandType en CommandType.StoredProcedure.

Insertar registros

Este es un ejemplo de cómo insertar un solo registro de película:

private const string INSERT_SQL =
@"INSERT INTO [Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

public void Insert(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movie);
	}
}
Code language: C# (cs)

Las inserciones son un poco tediosas de codificar porque la declaración INSERT es muy detallada en comparación con otras declaraciones SQL. Hay bibliotecas de extensión para Dapper que abstraen las operaciones CRUD para que no tenga que escribir SQL, pero personalmente no las uso. Uno de los beneficios de Dapper es que ejecuta la consulta SQL que escribes, por lo que no hay sorpresas.

Ser capaz de pasar el objeto de la película para el param argumento ayuda, ya que no tiene que escribir la lista de parámetros. Para escribir la declaración INSERT, generalmente uso Script Table as> INSERT INTO en SSMS para crear un script de punto de partida o generar las consultas usando metadatos.

Obtener el valor de identidad insertado

Cuando inserta un registro en una tabla con una columna de identidad, puede obtener el valor de identidad insertado agregando OUTPUT INSERTED.Id a la consulta. Utilice ExecuteScalar() para obtener el único valor devuelto:

private const string INSERT_OUTPUT_ID_SQL =
@"INSERT INTO [Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])
OUTPUT INSERTED.Id
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";
	
public int InsertAndReturnId(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.ExecuteScalar<int>(INSERT_OUTPUT_ID_SQL, param: movie);
	}
}
Code language: C# (cs)

Insertar varios registros

Cuando pasa una lista de objetos para el param argumento, Dapper ejecutará la consulta SQL para cada objeto en la lista:

private const string INSERT_SQL =
@"INSERT INTO [dbo].[Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

public void InsertMultiple(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movies);
	}
}
Code language: C# (cs)

Ejecutar muchas declaraciones INSERT secuencialmente puede realmente degradar el rendimiento. Asegúrese de probar el rendimiento de su código si espera insertar muchos datos como este con frecuencia. Recomiendo encarecidamente hacer un BULK INSERT si tiene problemas de rendimiento.

Actualización de registros

Este es un ejemplo de cómo actualizar un registro con Dapper:

public void UpdateYear(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id=@id", 
			param: new {year = movie.YearOfRelease, id = movie.Id });
	}
}
Code language: C# (cs)

Puede mantener la instrucción UPDATE lo más simple posible al incluir solo las columnas mínimas en la consulta. Si no es necesario que una columna esté en la lista de actualización o en la cláusula where, déjala fuera.

Actualización de múltiples registros

Si está actualizando varios registros con diferentes valores, deberá ejecutar varias declaraciones de ACTUALIZACIÓN. Por ejemplo, supongamos que desea configurar:

• Película A YearOfRelease=2021
• Año de lanzamiento de la película B=2022

Para hacer esto con Dapper, puede pasar una lista para el param argumento. Ejecutará la instrucción UPDATE para cada objeto de película en la lista:

public void UpdateMultipleToDifferentYears(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@YearOfRelease WHERE Id=@Id",
			param: movies);
	}
}
Code language: C# (cs)

Si está actualizando varios registros con el mismo valor, puede ejecutar una sola instrucción UPDATE con una cláusula WHERE IN. Por ejemplo, supongamos que desea actualizar varias películas para que tengan YearOfRelease=2022.

He aquí cómo hacer esto con Dapper:

using System.Linq;

public void UpdateMultipleWithSameYear(int year, List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id IN @ids",
			param: new { year, ids = movies.Select(m => m.Id) });
	}
}
Code language: C# (cs)

Esto es más eficiente que ejecutar varias declaraciones de ACTUALIZACIÓN cuando solo necesita una.

Eliminar registros

Eliminar un registro es sencillo con Dapper:

public void Delete(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("DELETE FROM Movies WHERE Id=@id",
			param: new { id = movie.Id });
	}
}
Code language: C# (cs)

Eliminar varios registros

Cuando está eliminando varios registros, puede ejecutar una sola instrucción DELETE con una cláusula WHERE IN. He aquí cómo hacerlo con Dapper:

using System.Linq;

public void DeleteMultiple(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("DELETE FROM Movies WHERE Id IN @ids",
			param: new { ids = movies.Select(m => m.Id) });
	}
}
Code language: C# (cs)

Esto es más eficiente que ejecutar varias instrucciones DELETE.