How to write async C# crud methods for Employee model from ArangoDB

Using Linq:

 

using System;

using System.Collections.Generic;

using System.Linq.Expressions;

using System.Threading.Tasks;

using ArangoDB.Client;

using ArangoDB.Client.Linq;

using ArangoDB.Client.Query;

public class EmployeeRepository

{

    private readonly string _databaseName;

    private readonly ArangoDatabase _database;

    public EmployeeRepository(string url, string databaseName, string username, string password)

    {

        var connection = new ConnectionSettings(url, databaseName)

        {

            Database = databaseName,

            Credential = new NetworkCredential(username, password)

        };

        var database = new ArangoDatabase(connection);

        _databaseName = databaseName;

        _database = database;

    }

    public async Task<string> CreateEmployeeAsync(Employee employee)

    {

        var collection = _database.Collection<Employee>();

        await collection.InsertAsync(employee);

        return employee.Id;

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        var collection = _database.Collection<Employee>();

        return await collection.ToListAsync();

    }

    public async Task<Employee> GetEmployeeByIdAsync(string id)

    {

        var collection = _database.Collection<Employee>();

        var query = from employee in collection.Linq()

                    where employee.Id == id

                    select employee;

        return await query.FirstOrDefaultAsync();

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        var collection = _database.Collection<Employee>();

        var result = await collection.UpdateAsync(employee.Id, employee);

        return result.Updated == 1;

    }

    public async Task<bool> DeleteEmployeeAsync(string id)

    {

        var collection = _database.Collection<Employee>();

        var result = await collection.RemoveByIdAsync<Employee>(id);

        return result.Removed == 1;

    }

}

 

Using AQL:

 

using System;

using System.Collections.Generic;

using System.Linq.Expressions;

using System.Threading.Tasks;

using ArangoDB.Client;

using ArangoDB.Client.Query;

public class EmployeeRepository

{

    private readonly string _databaseName;

    private readonly ArangoDatabase _database;

    public EmployeeRepository(string url, string databaseName, string username, string password)

    {

        var connection = new ConnectionSettings(url, databaseName)

        {

            Database = databaseName,

            Credential = new NetworkCredential(username, password)

        };

        var database = new ArangoDatabase(connection);

        _databaseName = databaseName;

        _database = database;

    }

    public async Task<string> CreateEmployeeAsync(Employee employee)

    {

        var query = new AqlQuery()

        {

            Query = $"INSERT {employee.ToJson()} INTO employees RETURN NEW._key",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.FirstOrDefault();

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        var query = new AqlQuery()

        {

            Query = "FOR employee IN employees RETURN employee",

        };

        var result = await _database.QueryAsync<Employee>(query);

        return result.ToList();

    }

    public async Task<Employee> GetEmployeeByIdAsync(string id)

    {

        var query = new AqlQuery()

        {

            Query = $"FOR employee IN employees FILTER employee._key == '{id}' RETURN employee",

        };

        var result = await _database.QueryAsync<Employee>(query);

        return result.FirstOrDefault();

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        var query = new AqlQuery()

        {

            Query = $"UPDATE '{employee.Id}' WITH {employee.ToJson()} IN employees RETURN NEW._id",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.Any();

    }

    public async Task<bool> DeleteEmployeeAsync(string id)

    {

        var query = new AqlQuery()

        {

            Query = $"REMOVE '{id}' IN employees RETURN OLD._id",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.Any();

    }

}

 

 

How to write async C# crud methods for Employee model from MongoDB

 

using System.Collections.Generic;

using System.Threading.Tasks;

using MongoDB.Driver;

public class EmployeeRepository

{

    private readonly IMongoCollection<Employee> _employeeCollection;

    public EmployeeRepository(string connectionString, string databaseName)

    {

        var client = new MongoClient(connectionString);

        var database = client.GetDatabase(databaseName);

        _employeeCollection = database.GetCollection<Employee>("employees");

    }

    public async Task<string> CreateEmployeeAsync(Employee employee)

    {

        await _employeeCollection.InsertOneAsync(employee);

        return employee.Id;

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        var employees = await _employeeCollection.FindAsync(_ => true);

        return await employees.ToListAsync();

    }

    public async Task<Employee> GetEmployeeByIdAsync(string id)

    {

        var filter = Builders<Employee>.Filter.Eq(e => e.Id, id);

        var employees = await _employeeCollection.FindAsync(filter);

        return await employees.FirstOrDefaultAsync();

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        var filter = Builders<Employee>.Filter.Eq(e => e.Id, employee.Id);

        var result = await _employeeCollection.ReplaceOneAsync(filter, employee);

        return result.ModifiedCount > 0;

    }

    public async Task<bool> DeleteEmployeeAsync(string id)

    {

        var filter = Builders<Employee>.Filter.Eq(e => e.Id, id);

        var result = await _employeeCollection.DeleteOneAsync(filter);

        return result.DeletedCount > 0;

    }

}

How to implement async CRUD methods for an Employee model using Dapper with Oracle

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using Oracle.ManagedDataAccess.Client;

using Dapper;

public class EmployeeRepository

{

    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public async Task<int> CreateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +

                           "VALUES (:FirstName, :LastName, :Email, :PhoneNumber) " +

                           "RETURNING Id INTO :id";

            var parameters = new DynamicParameters();

            parameters.Add(":FirstName", employee.FirstName);

            parameters.Add(":LastName", employee.LastName);

            parameters.Add(":Email", employee.Email);

            parameters.Add(":PhoneNumber", employee.PhoneNumber);

            parameters.Add(":id", DbType.Int32, direction: ParameterDirection.Output);

            await connection.ExecuteAsync(query, parameters);

            return parameters.Get<int>(":id");

        }

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

            return await connection.QueryAsync<Employee>(query);

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees WHERE Id = :id";

            return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });

        }

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "UPDATE Employees SET FirstName = :FirstName, LastName = :LastName, " +

                           "Email = :Email, PhoneNumber = :PhoneNumber WHERE Id = :Id";

            int rowsAffected = await connection.ExecuteAsync(query, employee);

            return rowsAffected > 0;

        }

    }

    public async Task<bool> DeleteEmployeeAsync(int id)

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "DELETE FROM Employees WHERE Id = :id";

            int rowsAffected = await connection.ExecuteAsync(query, new { id });

            return rowsAffected > 0;

        }

    }

}

How to implement async CRUD methods for an Employee model using Dapper with MySQL

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using MySql.Data.MySqlClient;

using Dapper;

public class EmployeeRepository

{

    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public async Task<int> CreateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +

                           "VALUES (@FirstName, @LastName, @Email, @PhoneNumber);" +

                           "SELECT LAST_INSERT_ID()";

            return await connection.ExecuteScalarAsync<int>(query, employee);

        }

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

            return await connection.QueryAsync<Employee>(query);

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees WHERE Id = @id";

            return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });

        }

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, " +

                           "Email = @Email, PhoneNumber = @PhoneNumber WHERE Id = @Id";

            int rowsAffected = await connection.ExecuteAsync(query, employee);

            return rowsAffected > 0;

        }

    }

    public async Task<bool> DeleteEmployeeAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "DELETE FROM Employees WHERE Id = @id";

            int rowsAffected = await connection.ExecuteAsync(query, new { id });

            return rowsAffected > 0;

        }

    }

}

 

How to implement async CRUD methods for an Employee model using Dapper with PostgreSQL

 You will need to install and add reference to the Npgsql package to communicate with PostgreSQL database.

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using Npgsql;

using Dapper;

public class EmployeeRepository

{

    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public async Task<int> CreateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new NpgsqlConnection(_connectionString))

        {

            string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +

                           "VALUES (@FirstName, @LastName, @Email, @PhoneNumber);" +

                           "SELECT CAST(CURRENT_VAL('employees_id_seq') AS INTEGER)";

            return await connection.ExecuteScalarAsync<int>(query, employee);

        }

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        using (IDbConnection connection = new NpgsqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

            return await connection.QueryAsync<Employee>(query);

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new NpgsqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees WHERE Id = @id";

            return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });

        }

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new NpgsqlConnection(_connectionString))

        {

            string query = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, " +

                           "Email = @Email, PhoneNumber = @PhoneNumber WHERE Id = @Id";

            int rowsAffected = await connection.ExecuteAsync(query, employee);

            return rowsAffected > 0;

        }

    }

    public async Task<bool> DeleteEmployeeAsync(int id)

    {

        using (IDbConnection connection = new NpgsqlConnection(_connectionString))

        {

            string query = "DELETE FROM Employees WHERE Id = @id";

            int rowsAffected = await connection.ExecuteAsync(query, new { id });

            return rowsAffected > 0;

        }

    }

}

How to read data in .net from Oracle using Dapper

Following is an async C# method, that use the OracleConnection class to create a connection to the Oracle database. The QueryAsync method is called on the connection object to execute the query asynchronously and retrieve the Employee objects already mapped to the data returned by the SQL query. The AsList method is called on the result of the query to convert it to a list of Employee objects.

Note that you will need to add the reference to the Oracle.ManagedDataAccess.Client package.

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using Dapper;

using Oracle.ManagedDataAccess.Client;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromOracleAsync()

{

    List<Employee> employees;

    string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=hostName)(PORT=portNumber))(CONNECT_DATA=(SERVER=DEDICATED)

(SERVICE_NAME=serviceName)));User Id=userName;Password=password;";

 

    using (IDbConnection connection = new OracleConnection(connectionString))

    {

        employees = (await connection.QueryAsync<Employee>("SELECT * FROM Employee")).AsList();

    }

   

    return employees;

}