Dapper Framework - 1.0

  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);
           
            }

 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);
           
       }

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);
            } 

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);
}

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);
        }
    }

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);
            }
        
        
    }

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);
        }
    }

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);
        }
    }

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;
        }
    }

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);
        }
      }

    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);
        }
    }
    

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");
        }
     }

 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);
            }
        }

 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);
            }
        }

 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;
            }
        }

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;
                }
            }

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;
                }
            }
    }   

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;
                    }
            }

  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"} });

    }

 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);
    }

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>();

    }

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))));
        }

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

        // ...
    }
}