Msb.DatabaseContext 5.0.0

MSB Database Context

A lightweight and flexible .NET ORM (Object-Relational Mapping) library that provides simple database operations with support for multiple database providers including SQL Server, MySQL, and PostgreSQL,Oracle,IBM Db2,SQLite.

Features

• 🚀 Multi-Database Support: Works with SQL Server, MySQL, and PostgreSQL,Oracle,IBM Db2,SQLite
• 📝 Code-First Approach: Define your models with attributes
• 🔄 CRUD Operations: Full Create, Read, Update, Delete support
• 🔀 Case Conversion: Automatic property-to-column name conversion (Pascal, Snake, Camel case)
• 📊 Complex Data Types: JSON serialization support for complex objects
• 🔁 Async/Sync: Both asynchronous and synchronous method variants
• 📦 Bulk Operations: Efficient bulk insert operations
• 🎯 Stored Procedures: Execute stored procedures with ease
• 🔒 Transaction Support: Built-in transaction handling
• 🛠️ Custom Queries: Execute raw SQL queries when needed

Installation

Install-Package Msb.DatabaseContext

Quick Start

1. Define Your Model

[CaseTypeConversion(CaseType.SNAKE)]
public class User
{
    [HasPrimaryKey]
    
    public long Id { get; set; }
    
    public string Name { get; set; }
    public string Email { get; set; }
    public string Contact { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    
    [WrapperData] // Maps complex object to JSON column
    public AddressLog Address { get; set; }
    
    [WrapperData] // Maps list of objects to JSON column
    public IList<AddressLog> AddressLogs { get; set; } = new List<AddressLog>();
    
    public UserStatus? Status { get; set; }
    public bool? IsActive { get; set; }
    public DateTime? CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
    
    [SkipMapping] // Skip mapping for navigation properties
    public User Parent { get; set; }
    
    [SkipMapping]
    public IList<User> Children { get; set; } = new List<User>();
}

public enum UserStatus
{
    Inactive,
    Active,
    Blocked
}

2. Initialize Database Context

// SQL Server
var connection = new SqlConnection("your-connection-string");

// MySQL
var connection = new MySqlConnection("your-connection-string");

// PostgreSQL
var connection = new NpgsqlConnection("your-connection-string");

var dbContext = new MsbDatabaseContext(connection);

Basic CRUD Operations

Insert

var newUser = new User
{
    Name = "John Doe",
    Email = "john.doe@example.com",
    Contact = "+1-202-555-0147",
    UserName = "johndoe",
    Password = "SecurePass123",
    CreatedAt = DateTime.Now,
    Status = UserStatus.Active
};

// Synchronous
User user = connection.Insert(newUser);

// Asynchronous
User user = await connection.InsertAsync(newUser);

Select

// Get all users
IEnumerable<User> users = await connection.SelectAsync<User>();

// Get single user by ID
User user = await connection.SelectOneAsync<User>(1);

Update

user.Status = UserStatus.Inactive;
user.UpdatedAt = DateTime.Now;
user.UserName = "john";

// Synchronous
User updatedUser = connection.Update(user);

// Asynchronous
User updatedUser = await connection.UpdateAsync(user);

Delete

// Synchronous
connection.Delete(user);

// Asynchronous
await connection.DeleteAsync(user);

Advanced Features

Bulk Operations

var bulkUsers = new List<User>() { user1, user2, user3 };

// Synchronous
int count = connection.BulkInsert(bulkUsers);

// Asynchronous
int count = await connection.BulkInsertAsync(bulkUsers);

Custom Queries

// Execute raw SQL
DataTable dataTable = await connection.QueryDataTableAsync("SELECT * FROM users WHERE status = @status", 
    new { status = UserStatus.Active });

// Convert DataTable to List<T>
IEnumerable<User> users = dataTable.ToList<User>();

Stored Procedures

// Execute stored procedure
IEnumerable<User> users = connection.Execute<User>("SP_Users_GetAll");

// With parameters
User user = connection.ExecuteSingle<object, User>("SP_Users_GetOne", new { Id = 1 });

// Get scalar result
string result = connection.ExecuteScalar<string>("SP_Users_Create", userParams);

Transaction Support

Manual Transaction

connection.Open();
using (var transaction = connection.BeginTransaction())
{
    try
    {
        var result = await connection.RunScalarQueryAsync<string>(
            "run_users_create",
            userCreate,
            dbTransaction: transaction
        );
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
    finally
    {
        connection.Close();
    }
}

Internal Transaction

string result = await connection.RunScalarQueryAsync<string>(
    "run_users_create",
    userCreate,
    useInternalTransaction: true
);

JSON Data Handling

// Serialize object to JSON
string jsonData = MsbContext.Serialize(userCreate);

// Deserialize JSON to object
UserCreateParams data = MsbContext.Deserialize<UserCreateParams>(jsonData);

Query Parameters

var userParameter = new QueryParameter();
userParameter.Add("name", "John Doe");
userParameter.Add("email", "john.doe@example.com");
userParameter.Add("address", addressObject.Serialize());

string result = await connection.RunScalarQueryAsync<string>("run_users_create", userParameter);

Attributes Reference

Model Attributes

• [CaseTypeConversion(CaseType)]: Converts property names to database column names

  • CaseType.PASCAL: PascalCase (default)
  • CaseType.SNAKE: snake_case
  • CaseType.CAMEL: camelCase

• [MappedName("column_name")]: Maps property to specific column name

• [HasPrimaryKey]: Defines primary key (default is "Id")

• [SkipMapping]: Excludes property from database mapping

• [WrapperData]: Maps complex objects/lists to JSON columns

Data Conversion Utilities

// DataTable to List<T>
IEnumerable<User> users = MsbContext.ToList<User>(dataTable);

// Class to DataTable
DataTable data = MsbContext.ToDataTable(users);

// Object conversion (DTO mapping)
UserDto userDto = MsbContext.ConvertAs<User, UserDto>(user);
IEnumerable<UserDto> userDtos = MsbContext.ConvertListAs<User, UserDto>(users);

// ExpandoObject to class
User user = MsbContext.ToConvert<User>(expandoObject);

// MsbData to class
User user = MsbContext.ToConvert<User>(msbData);

Working with MsbData

For dynamic data access without strongly-typed models:

IEnumerable<MsbData> users = await connection.RunTableQueryAsync("run_users_get_all");

foreach (var user in users)
{
    Console.WriteLine($"Id: {user.Get<int>("id")}");
    Console.WriteLine($"Name: {user.Get<string>("name")}");
    Console.WriteLine($"Email: {user.Get<string>("email")}");
    Console.WriteLine($"Address: {user.Get<AddressLog>("address")}");
}

Connection String Examples

SQL Server

var connection = new SqlConnection(
    "Data Source=SERVER\\INSTANCE;Initial Catalog=DATABASE;Integrated Security=True;TrustServerCertificate=True"
);

MySQL

var connection = new MySqlConnection(
    "Server=localhost;Database=DATABASE;User ID=root;Password=PASSWORD;"
);

PostgreSQL

var connection = new NpgsqlConnection(
    "server=localhost;port=5432;database=DATABASE;user id=USER;password=PASSWORD"
);

Best Practices

1. Use Async Methods: Prefer async methods for better performance in web applications
2. Transaction Management: Use transactions for operations that require consistency
3. Connection Management: Always dispose connections properly or use using statements
4. Parameter Binding: Use parameterized queries to prevent SQL injection
5. Case Conversion: Use consistent case conversion across your models
6. Complex Objects: Use [WrapperData] for objects that should be stored as JSON

CRUD Operations

Bulk Operations

Custom SQL Query Methods

Stored Procedure Methods

Function-Based Query Methods (run_ prefix)

Utility and Conversion Methods

MsbData Methods

Transaction Parameters

Most methods support additional transaction parameters:

Method Usage Patterns

Pattern 1: Direct Connection Methods

// Use connection directly for operations
var result = connection.Insert(entity);
var result = await connection.InsertAsync(entity);

Pattern 2: Database Context Methods

// Use MsbDatabaseContext wrapper
var result = connection.Insert(entity);
var result = await connection.InsertAsync(entity);

Pattern 3: Function vs Stored Procedure

// Database functions (typically prefixed with "run_")
var result = connection.RunTableQuery<User>("run_get_users");

// Stored procedures
var result = connection.Execute<User>("SP_GetUsers");