// Dapper Plus

// Doc: https://dapper-tutorial.net/execute-reader

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

​

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using Dapper;

using System.Data;

using Z.Dapper.Plus;

using Z.BulkOperations;

​

public class Program

{   

    public static void Main()

    {

        CreateDatabase();

        DapperPlusManager.Entity<Customer>().Table("Customers")

            .Identity(x => x.CustomerID);

        var list = GenerateListCustomer();

        using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            connection.BulkMerge(list);

        }

        using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            var reader = connection.ExecuteReader("SelectAllCustomers @IsActive = @isActive", new {IsActive = true} );

​

            DataTable table = new DataTable();

            table.Load(reader);

            FiddleHelper.WriteTable(table);

        }        

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public Boolean IsActive { get; set; }

    }

    public static List<Customer> GenerateListCustomer() 

    {

        var customers = new List<Customer>();

        bool isActive = false;

        for (int i = 0; i < 10; i++)

        {

            isActive = !isActive;

            customers.Add(new Customer() { Name ="Customer_" + i, Description = "Description", IsActive = isActive });

        }

        return customers;

    }

    public static void CreateDatabase()

    {

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [dbo].[Customers] (

    [CustomerID]  INT            IDENTITY (1, 1) NOT NULL,

    [Name]        NVARCHAR (MAX) NULL,

    [Description] NVARCHAR (MAX) NULL,

    [IsActive]    BIT            NOT NULL,

    CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC)

);

​

";

                command.ExecuteNonQuery();

                command.CommandText = @"CREATE PROCEDURE SelectAllCustomers @IsActive BIT

AS  

BEGIN

    SELECT * FROM Customers WHERE IsActive = @IsActive; 

END";

                command.ExecuteNonQuery();

            }

        }

    }

}