Last active
October 5, 2022 03:10
-
-
Save Eihen/293abb308016b26909d2177d3bf32f08 to your computer and use it in GitHub Desktop.
C# - Generic DB Management Class + Specialized class for PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Collections.Generic; | |
using System.Data; | |
// Abstract class, can't be used directly, needs to be inherited in another class implementing the abstract methods | |
/// <summary> | |
/// Generic class for DataBase management implementing methods that doesn't depend on the DataBase used | |
/// </summary> | |
public abstract class Database | |
{ | |
/// <summary> | |
/// Connection string | |
/// </summary> | |
protected string sConnection = string.Empty; | |
/// <summary> | |
/// Connection object | |
/// </summary> | |
protected IDbConnection connection = null; | |
/// <summary> | |
/// Transaction object for groups of operations | |
/// </summary> | |
protected IDbTransaction transaction = null; | |
/// <summary> | |
/// Retrieves the Connection object | |
/// Method to be implemented by the specialized class because it depends on the DataBase used | |
/// </summary> | |
/// <returns> | |
/// The Connection object | |
/// </returns> | |
protected abstract IDbConnection GetConnection(); | |
/// <summary> | |
/// Retrieves the Commands Execution object | |
/// Method to be implemented by the specialized class because it depends on the DataBase used | |
/// </summary> | |
/// <returns> | |
/// The Commads object | |
/// </returns> | |
protected abstract IDbCommand GetCommand(string sSql); | |
/// <summary> | |
/// Retrieves the Data Adapter object | |
/// Method to be implemented by the specialized class because it depends on the DataBase used | |
/// </summary> | |
/// <returns> | |
/// The Data Adapter object | |
/// </returns> | |
protected abstract IDbDataAdapter GetAdapter(IDbCommand command); | |
/// <summary> | |
/// Create a new instance of the class <see cref="Database"/> | |
/// </summary> | |
public Database() | |
{ | |
} | |
/// <summary> | |
/// Opens the connection with the DataBase | |
/// </summary> | |
public void Open() | |
{ | |
if (connection == null) | |
{ | |
// Get the connection object from the heir class | |
connection = GetConnection(); | |
} | |
connection.Open(); | |
} | |
/// <summary> | |
/// Closes the connection | |
/// </summary> | |
public void Close() | |
{ | |
// The connection.State can the used to check situations in which the connection should not be closed | |
// in order to avoid errors, it's not needed if the class is used correctly in your program | |
if (connection != null) // && connection.State == ConnectionState.Open) | |
{ | |
connection.Close(); | |
} | |
} | |
/// <summary> | |
/// Commits the operations on the current transaction | |
/// </summary> | |
public void CommitTransaction() | |
{ | |
if (transaction != null) // Checks if the transaction exists | |
{ | |
transaction.Commit(); | |
transaction.Dispose(); // Free the transaction resources | |
transaction = null; | |
} | |
} | |
/// <summary> | |
/// Rollsback the operations on the current transaction | |
/// </summary> | |
public void RollbackTransaction() | |
{ | |
if (transaction != null) // Checks if the transaction exists | |
{ | |
transaction.Rollback(); | |
transaction.Dispose(); // Free the transaction resources | |
transaction = null; | |
} | |
} | |
/// <summary> | |
/// Run the SQL command with the provided parameters | |
/// </summary> | |
/// <param name="sSql">The SQL command</param> | |
/// <param name="parameters">The SQL command parameters</param> | |
/// <returns>The value on the first column of the first line returned by the command</returns> | |
public string SqlExecute(string sSql, Dictionary<string, object> parameters = null) | |
{ | |
// Get the command object from the heir class | |
var command = GetCommand(sSql); | |
// Checks if there's any parameters | |
if (parameters != null) | |
{ | |
foreach (var item in parameters) | |
{ | |
// Creates the parameter relative to the Commands object | |
var parameter = command.CreateParameter(); | |
parameter.ParameterName = item.Key; | |
parameter.Value = item.Value; | |
// Add the parameter to the Commands object | |
command.Parameters.Add(parameter); | |
} | |
} | |
// Checks if there's an active transaction | |
if (transaction != null) | |
{ | |
// If there is, executes the command inside the transaction | |
command.Transaction = transaction; | |
} | |
// Executes the command and takes the value on the first column of the first line | |
var result = (string) command.ExecuteScalar(); | |
// Free the command resources | |
command.Dispose(); | |
return result; | |
} | |
/// <summary> | |
/// Run the SQL select with the provided parameters | |
/// </summary> | |
/// <param name="sSql">The SQL select</param> | |
/// <param name="parameters">The SQL select parameters</param> | |
/// <param name="commandType">The SQL command type</param> | |
/// <returns>The Data Set returned by the SQL select</returns> | |
public DataSet SqlSelect(string sSql, Dictionary<string, object> parameters = null, CommandType commandType = CommandType.Text) | |
{ | |
// Creates a new Data Set | |
var dataSet = new DataSet(); | |
// Retrieves the Commands object | |
var command = GetCommand(sSql); | |
// Retrieves the Data Adapter object | |
var adapter = GetAdapter(command); | |
// Checks if there's any parameters | |
if (parameters != null) | |
{ | |
foreach(var item in parameters) | |
{ | |
// Creates the paramater relative to the Commands object | |
var parameter = command.CreateParameter(); | |
parameter.ParameterName = item.Key; | |
parameter.Value = item.Value; | |
// Add the parameter to the Commands object | |
command.Parameters.Add(parameter); | |
} | |
} | |
// Set the type of the command to be executed | |
command.CommandType = commandType; | |
// Checks if there's an active transaction | |
if (transaction != null) | |
{ | |
// If there is, execute the command inside the transaction | |
command.Transaction = transaction; | |
} | |
// Use the Data Adapter to fill the Data Set with the data returned from the SQL select | |
adapter.Fill(dataSet); | |
// Free the command resources | |
command.Dispose(); | |
return dataSet; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//Plugin/DLL of the NpgSql connector needed | |
using Npgsql; | |
using System.Data; | |
/// <summary> | |
/// Inherits class <see cref="Database"/> and implements needed methods for PostgreSQL | |
/// </summary> | |
public class PostgresDB : Database | |
{ | |
/// <summary> | |
/// Creates a new instance of the class <see cref="PostgresDB"/>, setting the connection string | |
/// </summary> | |
/// <param name="host">The address of the DataBase Host</param> | |
/// <param name="database">The DataBase name</param> | |
/// <param name="user">The userrname</param> | |
/// <param name="password">The password</param> | |
public PostgresDB(string host, string database = "", string user = "postgres", string password = "") | |
{ | |
sConnection = string.Format("Host={0};Username={1};Password={2};Database={3}", host, user, password, database); | |
} | |
/// <summary> | |
/// Creates the Connection object | |
/// </summary> | |
/// <returns> | |
/// The Connection object | |
/// </returns> | |
protected override IDbConnection GetConnection() | |
{ | |
return new NpgsqlConnection(sConnection); | |
} | |
/// <summary> | |
/// Creates the Commands Execution object | |
/// </summary> | |
/// <param name="sSql">The SQL command to be executed</param> | |
/// <returns> | |
/// The Commmands object | |
/// </returns> | |
protected override IDbCommand GetCommand(string sSql) | |
{ | |
return new NpgsqlCommand(sSql, (NpgsqlConnection) connection); | |
} | |
/// <summary> | |
/// Creates the Data Adapter object | |
/// </summary> | |
/// <param name="command">The Commands object</param> | |
/// <returns> | |
/// The Data Adapter object | |
/// </returns> | |
protected override IDbDataAdapter GetAdapter(IDbCommand command) | |
{ | |
return new NpgsqlDataAdapter((NpgsqlCommand) command); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Bom, dia, sou iniciante, autodidata,
gostaria de saber como seriam as instanciações, em pratica
em uma inserção, ou select
estou implementando mvc, camada model, camada dao, camada aplicação, e camada view
Agradeço muito.