Método consulta genérica/rápida C#/SQL/DataTable/Dapper

public static class DBManager

    {

        private static int _Intentos = 5;

        public static DataTable Execute_QueryToDataTable(string query, string connectionString = "")

        {

            DataTable dt = new DataTable();

            int Contador = 0;

           

            while (Contador <= _Intentos)

            {

                try

                {

                    using (SqlConnection connection = new SqlConnection(connectionString))

                    {

                        using (SqlCommand command = new SqlCommand())

                        {

                            connection.Open();

                            command.Connection = connection;

                            command.CommandType = System.Data.CommandType.Text;

                            command.CommandText = query;

                            using (SqlDataReader reader = command.ExecuteReader())

                            {

                                dt.Load(reader);

                            }

                            Contador = _Intentos + 1;

                        }

                    }

                }

                catch (Exception ex)

                {

                    if (Contador == _Intentos)

                    {

                        throw ex;

                    }

                    Contador++;

                }

            }

            return dt;

        }

        public static DataTable Execute_QueryToDataTable(string spName, Dictionary<string,string> dParameters, string connectionString = "")

        {

            DataTable dt = new DataTable();

            int Contador = 0;

           

            while (Contador <= _Intentos)

            {

                try

                {

                    using (SqlConnection connection = new SqlConnection(connectionString))

                    {

                        using (SqlCommand command = new SqlCommand())

                        {

                            connection.Open();

                            command.Connection = connection;

                            command.CommandType = System.Data.CommandType.StoredProcedure;

                            command.CommandText = spName;

                           

                            foreach (var item in dParameters)

                            {

                                command.Parameters.Add(new SqlParameter(item.Key.Trim(), item.Value.Trim()));

                            }

                            using (SqlDataReader reader = command.ExecuteReader())

                            {

                                dt.Load(reader);

                            }

                            Contador = _Intentos + 1;

                        }

                    }

                }

                catch (Exception ex)

                {

                    if (Contador == _Intentos)

                    {

                        throw ex;

                    }

                    Contador++;

                }

            }

            return dt;

        }

      

        public static IEnumerable<T> Execute_Query<T>(string query, string connectionString = "")

        {

            IEnumerable<T> items = null;

            int Contador = 0;

            while (Contador <= _Intentos)

            {

                try

                {

                    using (SqlConnection connection = new SqlConnection(connectionString))

                    {

                        items = connection.Query<T>(query);

                    }

                    Contador = _Intentos + 1;

                }

                catch (Exception ex)

                {

                    if (Contador == _Intentos)

                    {

                        throw ex;

                    }

                    Contador++;

                }

            }

           

            return items;

        }

        public static IEnumerable<T> Execute_Query<T>(string spName, Dictionary<string, object> dParameters, string connectionString = "")

        {            

            var dbArgs = new DynamicParameters();

            IEnumerable<T> items = null;

            int Contador = 0;

            while (Contador <= _Intentos)

            {

                try

                {

                    foreach (var pair in dParameters)

                    {

                        dbArgs.Add(pair.Key, pair.Value);

                    }

                    using (SqlConnection connection = new SqlConnection(connectionString))

                    {

                        return (connection.Query<T>(spName, new DynamicParameters(dbArgs), commandType: CommandType.StoredProcedure));

                    }

                    Contador = _Intentos + 1;

                }

                catch (Exception ex)

                {

                    if (Contador == _Intentos)

                    {

                        throw ex;

                    }

                    Contador++;

                }

            }

            return null;

        }

    }

https://stackoverflow.com/questions/5957774/performing-inserts-and-updates-with-dapper

Performing CRUD operations using Dapper is an easy task. I have mentioned the below examples that should help you in CRUD operations.

Code for CRUD:

Method #1: This method is used when you are inserting values from different entities.

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName,
        StateModel.State,
        CityModel.City,
        isActive,
        CreatedOn = DateTime.Now
    });
}

Method #2: This method is used when your entity properties have the same names as the SQL columns. So, Dapper being an ORM maps entity properties with the matching SQL columns.

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, customerViewModel);
}

Code for CRUD:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string selectQuery = @"SELECT * FROM [dbo].[Customer] WHERE FirstName = @FirstName";

    var result = db.Query(selectQuery, new
    {
        customerModel.FirstName
    });
}

Code for CRUD:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string updateQuery = @"UPDATE [dbo].[Customer] SET IsActive = @IsActive WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(updateQuery, new
    {
        isActive,
        customerModel.FirstName,
        customerModel.LastName
    });
}

Code for CRUD:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string deleteQuery = @"DELETE FROM [dbo].[Customer] WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(deleteQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName
    });
}

Using Dapper.Contrib it is as simple as this:

Insert list:
public int Insert(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        return conn.Insert(yourClass) ;
    }
}

Insert single:
public int Insert(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        return conn.Insert(yourClass) ;
    }
}

Update list:
public bool Update(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        return conn.Update(yourClass) ;
    }
}

Update single:
public bool Update(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        return conn.Update(yourClass) ;
    }
}

Source: https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib