Msb.DatabaseContext 4.0.0

DatabaseContext Utility Documentation

This document provides detailed documentation on the methods available in the DatabaseContext class, which facilitates database operations such as executing queries and retrieving data. It is designed to simplify the process of interacting with a database through DbConnection.

Getting Started

Connection String Example

Ensure you have a valid connection string to connect to the database. Below are examples for different database providers:

SQL Server:

string connectionStringsMsSql = @"Data Source=MADHUSUDAN\SQLEXPRESS;Initial Catalog=Nootbook;uid=MADHUSUDAN\Madhusudan;persistsecurityinfo=True;Integrated Security=True";

using (SqlConnection connection = new SqlConnection(connectionStringsMsSql))
{
    var data = connection.Execute<YourClass>("SP_Users_Details_By_Id", new { Id = 1 });
}

MySQL:

string connectionStringsMySql = "Server=localhost;Database=notebook;Uid=root;Pwd=password;";

using (MySqlConnection connection = new MySqlConnection(connectionStringsMySql))
{
    var data = connection.Execute<YourClass>("SP_Users_Details_By_Id", new { Id = 1 });
}

PostgreSQL:

string connectionStringsPostgres = "Host=localhost;Database=notebook;Username=postgres;Password=password;";

using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringsPostgres))
{
    var data = connection.RunTableQuery<YourClass>("SP_Users_Details_By_Id", new { Id = 1 });
}

Methods Overview

The DatabaseContext class provides the following types of methods:

1. Execute Methods: Execute stored procedures.
2. Query Methods: Execute raw SQL queries.
3. RunTableQuery Methods: Execute Table Function(PostgreSQL) SQL queries.
4. ExecuteScalar Methods: Retrieve single scalar values.
5. Asynchronous Variants: All of the above methods also have asynchronous versions (suffixed with Async).

Each type of method has overloads to support:

• No parameters.
• Anonymous object parameters.
• Generic parameters (P) for typed arguments.

Method Documentation

1. Execute

Executes a stored procedure and returns a list of results.

• Usage Example:

var result = connection.Execute<MyClass>("SP_Name", new { Param1 = value1 });

• Overloads:
  • Execute<T>(DbConnection connection, string query) where T : class, new()
  • Execute<T>(DbConnection connection, string query, object parameters) where T : class, new()
  • Execute<T, P>(DbConnection connection, string query, P parameters) where T : class, new()

2. Query

Executes a raw SQL query and returns a list of results.

• Usage Example:

var result = connection.Query<MyClass>("SELECT * FROM Table WHERE Id = @Id", new { Id = 1 });

• Overloads:
  • Query<T>(DbConnection connection, string query) where T : class, new()
  • Query<T>(DbConnection connection, string query, object parameters) where T : class, new()
  • Query<T, P>(DbConnection connection, string query, P parameters) where T : class, new()

3. RunTableQuery

Executes a SQL query and binds parameters, returning a list of results.

• Usage Example:

var result = connection.RunTableQuery<MyClass>("Your Table function Name", new { Name = "John" });

• Overloads:
  • RunTableQuery<T>(DbConnection connection, string query) where T : class, new()
  • RunTableQuery<T>(DbConnection connection, string query, object parameters) where T : class, new()
  • RunTableQuery<T, P>(DbConnection connection, string query, P parameters) where T : class, new()

4. ExecuteScalar

Executes a stored procedure or query and returns a single scalar value.

• Usage Example:

var result = connection.ExecuteScalar<int>("SP_Count_Records", new { TableName = "Users" });

• Overloads:
  • ExecuteScalar<T>(DbConnection connection, string query)
  • ExecuteScalar<T>(DbConnection connection, string query, object parameters)
  • ExecuteScalar<P, T>(DbConnection connection, string query, P parameters)

5. Asynchronous Methods

All the above methods have asynchronous counterparts with the Async suffix.

• Usage Example:

var result = await connection.ExecuteAsync<MyClass>("SP_Name", new { Param1 = value1 });

• Overloads: Same as synchronous methods but with Task<IEnumerable<T>> as return type.

Additional Information

Helper Utilities

The methods in DatabaseContext rely on helper utilities like SQLUtility for the actual execution of commands. Ensure these utilities are implemented and properly handle:

• Parameter validation.
• SQL injection prevention.
• Edge cases (e.g., null results, empty queries).

Dynamic Return Types

Methods returning IEnumerable<dynamic> can be used for flexible but untyped results:

var result = connection.Query("SELECT * FROM Users");

Error Handling

Always handle potential exceptions from database operations:

try
{
    var data = connection.ExecuteScalar<int>("SP_GetCount", new { Id = 1 });
}
catch (SqlException ex)
{
    // Handle SQL exceptions
}
catch (Exception ex)
{
    // Handle other exceptions
}