Extension Id

com.castsoftware.dotnet.dapper

What's new?

See Dapper Framework - 1.0 - Release Notes for more information.

Description

This extension provides support for the following:

Dapper Framework	Dapper is a simple Object Relational Mapper (ORM) for .NET, which is responsible for mapping between database and programming language. Dapper also extends the `IDbConnection` by providing useful extension methods to query your database. This extension supports the framework CRUD operations and SQL queries.
Dapper Plus (i.e. the Nuget repository Z.Dapper.Plus)	The Dapper Plus (Z.Dapper.Plus) mapper - see https://dapper-plus.net/ and https://www.nuget.org/packages/Z.Dapper.Plus/ allows mapping of the class model (Entity) with the storage model (database) and provides options to perform Bulk Actions. It extends the `IDbConnection` with high-performance bulk operations like BulkInsert, BulkUpdate, BulkDelete, BulkMerge etc.
Dapper.SimpleCRUD	Dapper.SimpleCRUD is a small library that provides simple crud APIs for Dapper. It takes advantage of dynamic programming to make it simple to work with data.
Dapper.Contrib	Dapper.Contrib is a NuGet library that extends the `IDbConnection` interface and adds some generic utility methods to Dapper. It provides methods that allow less code to be written to perform basic CRUD operations.
Dapper.SqlBuilder	Dapper.SqlBuilder library provides various methods to build our SQL queries dynamically. It provides various methods like .Select, .Where, .InnerJoin etc.
Dapper.FastCrud	Dapper.FastCrud is a fast orm that is built around essential features of the C# 6 / VB 14 which helps in improving the simplicity and maintenance levels of SQL operations.
Dapper.FluentMap.Dommel	Dapper.FluentMap.Dommel provide API's for resolving table and column names. Dapper.FluentMap.Dommel implements certain interfaces of Dommel and uses the configured mapping.

In what situation should you install this extension?

If your .NET application contains any source code from the frameworks listed above, and you if want to view these object types and their links, then you should install this extension. More specifically the extension will identify:

"callLink" from C# methods to Dapper Entity Operation objects.
"callLink" from C# methods to DotNet Dapper Sql Query objects.

Technology support

Item	Version	Supported Technology	Notes
Dapper	1.40.0 to 2.0.x	C#	see https://www.nuget.org/packages/Dapper
Dapper Plus (Z.Dapper.Plus)	up to version 4.0.31	C#	see https://www.nuget.org/packages/Z.Dapper.Plus.
Dapper.SimpleCRUD	up to version 2.3.0	C#	see https://www.nuget.org/packages/Dapper.SimpleCRUD/
Dapper.Contrib	up to version 2.0.78	C#	see https://www.nuget.org/packages/Dapper.Contrib/
Dapper.SqlBuilder	up to version 2.0.78	C#	see https://www.nuget.org/packages/Dapper.SqlBuilder/
Dapper.FastCrud	up to version 3.1.46	C#	see https://www.nuget.org/packages/Dapper.FastCrud
Dapper.FluentMap.Dommel	up to version 2.0.0	C#	see https://www.nuget.org/packages/Dapper.FluentMap.Dommel

AIP Core compatibility

This extension is compatible with:

AIP Core release	Supported
8.3.x

Supported DBMS servers

This extension is compatible with the following DBMS servers:

DBMS	Supported
CSS/PostgreSQL

Download and installation instructions

For .NET applications using Dapper, the extension will be automatically installed by CAST Console. This is in place since October 2023.

For upgrade, if the Extension Strategy is not set to Auto update, you can manually install the extension using the Application - Extensions interface.

What results can you expect?

Once the analysis/snapshot generation has completed, you can view the results in the normal manner. The following objects and links will be displayed in CAST Enlighten:

Objects

Icon	Type Description	When is this object created?
	DotNet Dapper SQLQuery	Created for each SQL query found and resolved in Dapper or Dapper SqlMapper CRUD method call
	Dapper_Entity	Created and used when CRUD operation is found in the method call
	Dapper Entity Operation	Created and used when CRUD operation is performed on Dapper Entity

Links

The following link types are created by the Dapper extension, or by other extensions.

Link Type	Caller type	Callee type	Methods Supported
callLink	C# Method	DotNet Dapper Sql Query	Dapper Dapper.SqlMapper.Query Dapper.SqlMapper.QueryAsync Dapper.SqlMapper.QueryFirst Dapper.SqlMapper.QueryFirstAsync Dapper.SqlMapper.QueryFirstOrDefault Dapper.SqlMapper.QueryFirstOrDefaultAsync Dapper.SqlMapper.QueryMultiple Dapper.SqlMapper.QueryMultipleAsync Dapper.SqlMapper.QuerySingle Dapper.SqlMapper.QuerySingleAsync Dapper.SqlMapper.QuerySingleOrDefault Dapper.SqlMapper.QuerySingleOrDefaultAsync Dapper.SqlMapper.Execute Dapper.SqlMapper.ExecuteAsync Dapper.SqlMapper.ExecuteScalar Dapper.SqlMapper.ExecuteReader Dapper.SqlMapper.ExecuteScalarAsync Dapper.SqlMapper.ExecuteReaderAsync Dapper.SqlBuilder Dapper.SqlBuilder.AddTemplate Dapper.SqlBuilder.GroupBy Dapper.SqlBuilder.Having Dapper.SqlBuilder.InnerJoin Dapper.SqlBuilder.Intersect Dapper.SqlBuilder.Join Dapper.SqlBuilder.LeftJoin Dapper.SqlBuilder.OrderBy Dapper.SqlBuilder.OrWhere Dapper.SqlBuilder.RightJoin Dapper.SqlBuilder.Select Dapper.SqlBuilder.Set Dapper.SqlBuilder.Where
callLink	C# Method	Dapper Entity Operation	Z.DapperPlus Z.Dapper.Plus.DapperPlusExtensions.BulkInsert Z.Dapper.Plus.DapperPlusExtensions.BulkMerge Z.Dapper.Plus.DapperPlusExtensions.BulkUpdate Z.Dapper.Plus.DapperPlusExtensions.BulkDelete Dapper.SimpleCRUD Dapper.SimpleCRUD.DeleteAsync Dapper.SimpleCRUD.DeleteList Dapper.SimpleCRUD.DeleteListAsync Dapper.SimpleCRUD.Get Dapper.SimpleCRUD.GetAsync Dapper.SimpleCRUD.GetList Dapper.SimpleCRUD.GetListAsync Dapper.SimpleCRUD.GetListPaged Dapper.SimpleCRUD.GetListPagedAsync Dapper.SimpleCRUD.RecordCount Dapper.SimpleCRUD.RecordCountAsync Dapper.SimpleCRUD.Insert Dapper.SimpleCRUD.InsertAsync Dapper.SimpleCRUD.Update Dapper.SimpleCRUD.UpdateAsync Dapper.Contrib Dapper.Contrib.Extensions.SqlMapperExtensions.Delete Dapper.Contrib.Extensions.SqlMapperExtensions.DeleteAll Dapper.Contrib.Extensions.SqlMapperExtensions.DeleteAllAsync Dapper.Contrib.Extensions.SqlMapperExtensions.DeleteAsync Dapper.Contrib.Extensions.SqlMapperExtensions.Get Dapper.Contrib.Extensions.SqlMapperExtensions.GetAll Dapper.Contrib.Extensions.SqlMapperExtensions.GetAllAsync Dapper.Contrib.Extensions.SqlMapperExtensions.GetAsync Dapper.Contrib.Extensions.SqlMapperExtensions.Insert Dapper.Contrib.Extensions.SqlMapperExtensions.InsertAsync Dapper.Contrib.Extensions.SqlMapperExtensions.Update Dapper.Contrib.Extensions.SqlMapperExtensions.UpdateAsync Dapper.FastCrud Dapper.FastCrud.DapperExtensions.BulkDelete Dapper.FastCrud.DapperExtensions.BulkDeleteAsync Dapper.FastCrud.DapperExtensions.BulkUpdate Dapper.FastCrud.DapperExtensions.BulkUpdateAsync Dapper.FastCrud.DapperExtensions.CountAsync Dapper.FastCrud.DapperExtensions.Delete Dapper.FastCrud.DapperExtensions.DeleteAsync Dapper.FastCrud.DapperExtensions.Find Dapper.FastCrud.DapperExtensions.FindAsync Dapper.FastCrud.DapperExtensions.Get Dapper.FastCrud.DapperExtensions.GetAsync Dapper.FastCrud.DapperExtensions.Insert Dapper.FastCrud.DapperExtensions.InsertAsync Dapper.FastCrud.DapperExtensions.Update Dapper.FastCrud.DapperExtensions.UpdateAsync
useLink	DotNet Dapper Sql Query	Table, View	Created by SQL Analyzer when DDL source files are analyzed
callLink	DotNet Dapper Sql Query	Procedure
useLink	Dapper Entity Operation	Table, View	Created by WBS when DDL source files are analyzed by SQL Analyzer
callLink	Dapper Entity Operation	Procedure
useLink	DotNet Dapper Sql Query	Missing Table	Created by Missing tables and procedures for .Net extension when the object is not analyzed.
callLink	DotNet Dapper Sql Query	Missing Procedure

Code Examples

Dapper

Insert Operation1

Insert Operation

  private static void AddRecord(var builder)
            {
           
             var conn = new SqlConnection(builder.ConnectionString);
             int insertedUser= conn.Execute("INSERT INTO dbo.[Users] VALUES (1140481,'Andrew','Tate')");
             Console.WriteLine("'Insert' Inserted Rows: {0}", insertedUser);
             int insertedInfo= conn.Execute("INSERT INTO dbo.[Info] VALUES (1140481,'SSE','UK',6)");
             Console.WriteLine("'Insert' Inserted Rows: {0}", insertedInfo);
           
            }

Insert Operation2

Real caller example

 private static void method_calls()
   {
            var insertQuery= "INSERT INTO dbo.[Users] VALUES (1140481,'Andrew','Tate')";
            var updateQuery= "UPDATE dbo.[Users] SET [FirstName] = 'Andrew' WHERE [Id] = 2";
            var deleteQuery= "DELETE FROM dbo.[Users] WHERE [ID] = 1140491";
            AddRecord(builder,insertQuery);
            UpdateRecord(builder,updateQuery);
            DeleteRecord(builder,deleteQuery);

    }

 private static void AddRecord(var builder,var insertQuery)
     {
           
         var conn = new SqlConnection(builder.ConnectionString);
         int insertedUser= conn.Execute(insertQuery);
         Console.WriteLine("'Insert' Inserted Rows: {0}", insertedUser);
         int insertedInfo= conn.Execute("INSERT INTO dbo.[Info] VALUES (1140481,'SSE','UK',6)");
         var insert_query="INSERT INTO dbo.[Info] VALUES (1140500,'SSE','USA',7)";
         int insertedInfo1=conn.Execute(insert_query);
           
       }

Update Operation

private static void UpdateRecord(var builder)
            {
              var conn = new SqlConnection(builder.ConnectionString);
              int affectedRows = conn.Execute("UPDATE dbo.[Users] SET [FirstName] = 'John' WHERE [Id] = 3");
              Console.WriteLine("'UPDATE' Affected Rows: {0}", affectedRows);
            }

Delete Operation

private static void DeleteRecord(var builder)
{
var conn = new SqlConnection(builder.ConnectionString);
int deletedRows= conn.Execute("DELETE FROM dbo.[Users] WHERE [ID]=1140481");
Console.WriteLine("'DELETE' Deleted Rows: {0}", DeletedRows);
}

Z.DapperPlus Operations

BulkInsert Operation

BulkInsert

public static void StepBulkInsert() 
	{
		suppliers.Add(  new Supplier() { SupplierName = "BulkInsert", ContactName = "BulkInsert", Products = new List<Product> 
		{ new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"},new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"} ,new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"}  }});
		
		products.Add(  new Product() {ProductID=321,ProductName = "BulkInsert", Unit = "BulkInsert",SupplierID=123});

		
		// STEP BULKINSERT
        var builder = new SqlConnectionStringBuilder();
		using (var connection = new SqlConnection(builder.ConnectionString))
		{
			connection.BulkInsert(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkInsert(x => x.Products);
			connection.BulkInsert(products);
		}
	}

BulkMerge Operation

BulkMerge

BulkMerge

public static void StepBulkMerge() 
	{
		suppliers.Add(new Supplier() { SupplierName = "BulkMerge", ContactName = "BulkMerge", Products = new List<Product>()
		{ new Product() { ProductName = "BulkMerge", Unit = "BulkMerge"},  new Product() { ProductName = "BulkMerge" , Unit = "BulkMerge"} ,  new Product() { ProductName = "BulkMerge", Unit = "BulkMerge" } 	  
		}});
		 
		suppliers.ForEach(x => 
		{
			x.SupplierName = "BULKMERGE";
			x.Products.ForEach(y => y.ProductName = "BULKMERGE");
		}); 
		
		// STEP BULKMERGE
		

			var builder = new SqlConnectionStringBuilder();
			using (var connection = new SqlConnection(builder.ConnectionString))
			{
			connection.BulkMerge(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkMerge(x => x.Products);
			}
		
		
	}

BulkUpdate Operation

BulkUpdate

BulkUpdate

public static void StepBulkUpdate() 
	{ 
		suppliers.ForEach(x => 
		{
			x.SupplierName = "BULKUPDATE";
			x.Products.ForEach(y => y.ProductName = "BULKUPDATE");
		}); 
		
		
		// STEP BULKUPDATE
        var builder = new SqlConnectionStringBuilder();
		using (var connection = new SqlConnection(builder.ConnectionString))
		{
			connection.BulkUpdate(suppliers, x => x.Products);
		}
	}

BulkDelete Operation

BulkDelete

public static void SteBulkDelete() 
	{
		// STEP BULKDELETE
        var builder = new SqlConnectionStringBuilder();
		using (var connection = new SqlConnection(builder.ConnectionString))
		{
			connection.BulkDelete(suppliers.SelectMany(x => x.Products)).BulkDelete(suppliers);
		}
	}

Dapper.SimpleCRUD

Select Operation

Select operation

static void Main(string[] args)
    {
        DeleteSingleAuthor();
        List<Author> authors = GetAllAuthors();
        List<Book> books = GetAllBooks();
        InsertSingleAuthor();
        UpdateSingleBook();
        DeleteSingleAuthor();
        DeleteMultipleBooks();

    }

private static List<Book> GetAllBooks(string category)
    {
        var builder = new SqlConnectionStringBuilder();
        using (var conn= new SqlConnection(builder.ConnectionString))
        {
            List<Book> books = conn.GetList<Book>(new { Category = category }).ToList();
            return books;
        }
    }

Insert Operation

Insert Operation

static void Main(string[] args)
    {
        DeleteSingleAuthor();
        List<Author> authors = GetAllAuthors();
        List<Book> books = GetAllBooks();
        InsertSingleAuthor();
        UpdateSingleBook();
        DeleteSingleAuthor();
        DeleteMultipleBooks();

    }

    
private static void InsertSingleAuthor()
    {
        var builder = new SqlConnectionStringBuilder();
        using (var conn= new SqlConnection(builder.ConnectionString))
        {
            Author author = new Author()
            {
                FirstName = "Cokie",
                LastName = "Roberts"
            };
			
            conn.Insert<Author>(author);
        }
      }

Update Operation

Update operation

Update operation

    static void Main(string[] args)
    {
        DeleteSingleAuthor();
        List<Author> authors = GetAllAuthors();
        List<Book> books = GetAllBooks();
        InsertSingleAuthor();
        UpdateSingleBook();
        DeleteSingleAuthor();
        DeleteMultipleBooks();

    }

    private static void UpdateSingleBook()
    {
        var builder = new SqlConnectionStringBuilder();
        using (var conn= new SqlConnection(builder.ConnectionString))
        {
            Book book = new Book { Id = 1, Title = "Introduction to AI", Category = "Software", AuthorId = 1 };
            conn.Update<Book>(book);
        }
    }

Delete Operation

Delete Operation

static void Main(string[] args)
    {
        DeleteSingleAuthor();
        List<Author> authors = GetAllAuthors();
        List<Book> books = GetAllBooks();
        InsertSingleAuthor();
        UpdateSingleBook();
        DeleteSingleAuthor();
        DeleteMultipleBooks();

    }

private static void DeleteMultipleBooks()
     {
        var builder = new SqlConnectionStringBuilder();
        using (var conn= new SqlConnection(builder.ConnectionString))
        {
            conn.DeleteList<Book>("where Id > 3");
        }
     }

Dapper.Contrib

Insert Operation

 private static void InsertSingleAuthor()
		{
			var builder = new SqlConnectionStringBuilder();
		    using (var conn= new SqlConnection(builder.ConnectionString))
		    {
		        Author author = new Author()
		        {
		            FirstName = "William",
		            LastName = "Shakespeare"
		        };
		
		        conn.Insert<Author>(author);
		    }
		}

Update Operation

 private static void UpdateSingleBook()
	    {
	        var builder = new SqlConnectionStringBuilder();
	        using (var conn= new SqlConnection(builder.ConnectionString))
	        {
	            Book book = new Book { Id = 1, Title = "Introduction to AI", Category = "Software", AuthorId = 1 };
	            conn.Update<Book>(book);
	        }
	    }

Select Operation

 private static List<Author> GetAllAuthors()
	    {
	        var builder = new SqlConnectionStringBuilder();
	        using ( var conn= new SqlConnection(builder.ConnectionString))
	        {
	            List<Author> authors = conn.GetList<Author>().ToList();
	            return authors;
	        }
	    }

Dapper.SqlBuilder

Select Query

private static List<Author> GetAuthors()
			
			{
				var builder1 = new SqlConnectionStringBuilder();
				using (var connection = new SqlConnection(builder1.ConnectionString))
				{
					var builder = new SqlBuilder();
					builder.Select("Id");
					builder.Select("FirstName");
					builder.Select("LastName");
			
					var builderTemplate = builder.AddTemplate("Select /**select**/ from Authors");
			
					var authors = connection.Query<Author>(builderTemplate.RawSql).ToList();
			
					return authors;
				}
			}

InnerJoin Query

private static List<Author> GetAuthorWithBooks()
			{
				var builder = new SqlBuilder();
				builder.Select("*");
			
				builder.InnerJoin("Books on Books.AuthorId=Authors.Id");
			
				var builderTemplate = builder.AddTemplate("Select /**select**/ from Authors /**innerjoin**/ ");
			
				var authorDictionary = new Dictionary<int, Author>();
				var builder1 = new SqlConnectionStringBuilder();
				using (var connection = new SqlConnection(builder1.ConnectionString))
				{
					var authors = connection.Query<Author, Book, Author>(
						builderTemplate.RawSql,
						(author, book) =>
						{
							Author authorEntry;
			
							if (!authorDictionary.TryGetValue(author.Id, out authorEntry))
							{
								authorEntry = author;
								authorEntry.Books = new List<Book>();
								authorDictionary.Add(authorEntry.Id, authorEntry);
							}
			
							authorEntry.Books.Add(book);
							return authorEntry;
						},
						splitOn: "Id")
					.Distinct()
					.ToList();
			
					return authors;
				}
			}
	}

Where Query

private static Author GetAuthor(int id)
			{
				var builder1 = new SqlConnectionStringBuilder();
				using (var connection = new SqlConnection(builder1.ConnectionString))
					{
						var builder = new SqlBuilder();
						builder.Select("Id");
						builder.Select("FirstName");
						builder.Select("LastName");
				
						DynamicParameters parameters = new DynamicParameters();
						parameters.Add("@MyParam", id, DbType.Int32, ParameterDirection.Input);
				
						builder.Where("Id = @MyParam", parameters);
				
						var builderTemplate = builder.AddTemplate("Select /**select**/ from Authors /**where**/ ");
				
						var author = connection.Query<Author>(builderTemplate.RawSql, builderTemplate.Parameters).FirstOrDefault();
				
						return author;
					}
			}

Dapper.FastCrud

Insert Operation

  private static void InsertBook()
    {
    	var builder = new SqlConnectionStringBuilder();
		var dbConnection = new SqlConnection(builder.ConnectionString);
		dbConnection.Insert(new BookDbEntity() {BookID=324,BookName = "Data Structure",new StudentDbEntity(){FirstName="David", LastName="Roy"} });

    }

Update Operation

 private static void UpdateStudents()
    {
    	var builder = new SqlConnectionStringBuilder();
		var dbConnection = new SqlConnection(builder.ConnectionString);

        students.Add(new StudentDbEntity(){StudentId=1140481,FirstName="Ishanya", LastName="Mittal",
        new TeacherDbEntity(){OnlineTeacher="Vijay Chabraa", OnlineTeacherId=32 ,ClassroomTeacher="Priyanka Alahawat " ,ClassroomTeacherId=53 }},

        new StudentDbEntity(){StudentId=1140491,FirstName="Rachit", LastName="Bansal",
        new TeacherDbEntity(){OnlineTeacher="Vijay Chabraa", OnlineTeacherId=32 ,ClassroomTeacher="Priyanka Alahawat " ,ClassroomTeacherId=53 }}
        );

        dbConnection.BulkUpdate(students);
    }

Delete Operation

private static void DeleteBooks()
    {
    	var builder = new SqlConnectionStringBuilder();
		var dbConnection = new SqlConnection(builder.ConnectionString);
		dbConnection.Delete(new BookDbEntity() { BookID=328, BookName = "Head First Java",new StudentDbEntity(){FirstName="Ishanya", LastName="Mittal"}});
        dbConnection.BulkDelete<BookDbEntity>();

    }

Select Operation

Select operation

private static void GetWorkstationInventoryIndex()
		{
			var queryParams = new {
									  EmployeeFirstName = "Jane",
									  EmplopyeeLastName = "Doe",
									  WorkstationMinInventoryIndex = 5
									}
			var builder = new SqlConnectionStringBuilder();
		    var dbConnection = new SqlConnection(builder.ConnectionString);
			dbConnection.Find<EmployeeDbEntity>(options => options
					.WithAlias("em")
					.Include<WorkstationDbEntity>(join => join
					.WithAlias("ws")                        
					.On($"{nameof(EmployeeDbEntity.WorkstationId):of em} = {nameof(WorkstationDbEntity.WorkstationId):of ws}")
					.MapResults(true)                                                                                                          
					.Referencing<EmployeeDbEntity>(relationship => relationship
						.FromAlias("em")
						.FromProperty(employee => employee.Workstation)
						.ToProperty(workstation => workstation.Employees))));
		}

Dommel

Overriding of table name

Table Name

This class derives from EntityMap<TEntity> and allows you to map an entity to a database table using the ToTable() method:

Overriding Table Name

public class AuthorMap : DommelEntityMap<TEntity>
{
    public AuthorMap()
    {
        ToTable("tbl_Authors");

        // ...
    }
}

Limitations

Objects will not be created if evaluation fails to resolve the necessary parameter.
The set of supported methods is limited to what is documented.