CodeArtEng.Sql 1.0.0

MS SQL Helper: A Micro-ORM for MS SQL Database

• Introduction
  • Dependency
  • Key Features of MS SQL Helper
  • Anatomy of SqlHelper
• Using SqlHelper
  • Create MS SQL Database class
  • Basic Query Functions
  • Read from Database Table
  • Write Data to Database
  • Reading and Writing Complex Tables (ORM)
    • Table Name
    • Column Name
    • Data Type
    • Index Table
    • Primary Key
    • Parent and Child Tables
    • Multiple Database Source
    • Array Table
• Suggestion and Feedback

Introduction

MS SQL Helper is a micro-Object-Relational Mapping (ORM) tool crafted to facilitate application development with Microsoft SQL Server databases. It is particularly well-suited for small to medium-scale applications, negating the necessity of authoring each SQL query from the ground up.

Conversely, Entity Framework (EF) is a comprehensive ORM offering a complete suite of functionalities. However, more features do not inherently equate to superiority. It is prudent to weigh the Pros and Cons of EF prior to its adoption.

While Entity Framework presents a robust ORM solution with an extensive feature set, MS SQL Helper is tailored for simplicity and expediency, enabling streamlined interactions with MS SQL databases through straightforward functions.

An article Micro ORM vs ORM written by Alex Shapovalov explained in details difference between Micro ORM vs ORM and how to choose among them.

Dependency

• .NET Framework 4.8 (System.Data.SqlClient)

License

• MIT

Key Features of MS SQL Helper

MS SQL Helper comes with a set of features aimed at making your interaction with MS SQL databases as smooth as possible:

1. Manage Connection String: SqlHelper requires only the server, database name, and authentication parameters to establish a connection, streamlining the process significantly.
2. Automatic Open and Close Connection: SqlHelper employs a helper class to manage database connections, obviating the need for manual tracking of connection states and ensuring proper release post-write operations.
3. Object Mapping to Database Class: SqlHelper facilitates single-method read and write operations to the database by mapping objects directly to the database class, thereby simplifying data manipulation tasks.
4. Handle Queries from Different Database Sources: SqlHelper is adept at processing queries from various database sources, offering the requisite flexibility for managing multiple databases.
5. Utility Functions: SqlHelper includes utility methods such as ClearTable, GetPrimaryKeys, GetTableSchema, among others, all designed with safeguards against SQL injection—a common oversight for novices.

In conclusion, SqlHelper is an essential tool for developers working with MS SQL databases. It simplifies database interactions, allowing developers to concentrate more on the application's logic rather than the complexities of SQL query writing. SqlHelper is effective in improving the development process for both small and medium-sized projects.

Anatomy of SqlHelper

• SqlHelper (abstract): Primary class for the package. It encompasses all methods necessary for database read and write operations.
• SqlDatabaseHandler (abstract): This is a subclass derived from SqlHelper. It inherits all features from the SqlHelper class and additionally has the ability to toggle between remote and local databases, as well as synchronize data from a remote source to a local cached copy.
• SqlDataReaderEx (extension): Extension class for SqlDataReader which handle null check for get value method.
• SQLAttribute: Attribute base class for table mapping.

Using SqlHelper

As a helper class, most methods within SqlHelper are designated as protected, as it is not anticipated that users of derived classes will interact directly with the database layer. All operations related to the database should remain concealed from the user at both the API and application levels.

Create MS SQL Database class

Create project specific database class inherits from SqlHelper class. SetSQLConnection takes parameters for server name, database name, and authentication settings. An optional readOnly parameter when set to true will open the database in read only mode.

public class MyDatabase : SqlHelper
{
    public MyDatabase(string database): base()
    {
        string server = ...
        base.SetSqlConnection(server, database);
    }
}

Handling Connection Timeouts and Retries

Proper handling of retry and timeout is crucial to ensure transaction completed successfully without impact user experience. Parameters SQLCommandTimeout define the timeout duration and number of retries for failed connections or deadlocks.

Basic Query Functions

In MS SQL Helper, we offer a suite of query methods that internally handle database connections, eliminating the need to search for unclosed connections that could result in resource leaks.

protected void ExecuteQuery(string query, Action<SqlDataReader> processQueryResults)
protected object ExecuteScalar(string query)
protected int ExecuteNonQuery(string query)
protected void ExecuteTransaction(Action performTransactions)

Example below show usage of ExecuteQuery method. Connection are closed and SqlDataReader object r is disposed at end of ExecuteQuery method.

  ExecuteQuery(query, (r)=>
  {
      while(r.Read())
      {
          //ToDo: Perform readback here...
      }
  });

Read from Database Table

MS SQL Helper offer 2 solutions to read data from Employee table into Employee class object as shown below:

Employee (Table)
  |- ID, INT, Primary Key
  |- Name, NVARCHAR(100)
  |- Department, NVARCHAR(50)
  |- Salary, INT

public class Employee
{
    public int ID {get; set;}
    public string Name {get; set;}
    public string Department {get; set;}
    public int Salary {get; set;}
}

1. The convention way, manually read data from database using ExecuteQuery method:

public Employee[] ReadEmployeeData()
{
    List<Employee> results = new List<Employee>();
    //Execute Query handle database connection
    ExecuteQuery("SELECT * FROM Employee", (r) =>
    {
        //(r) = Delegate call back function with SqlDataReader parameter r.
        //Disposal of r is taken care by ExecuteQuery method.
        int x;
        while(r.Read())
        {
            x = 0;
            Employee e = new Employee();
            e.ID = r.GetInt32(x++);
            e.Name = r.GetStringEx(x++);  //Extension method. Handle null value.
            e.Department = r.GetStringEx(x++);
            e.Salary = r.GetInt32Ex(x++);
        }
    });
}

2. Implementation above can be further simplify using query with class - ReadFromDatabase

public Employee[] ReadEmployeeData()
{
    return ReadFromDatabase<Employee>().ToArray();
}

Write Data to Database

To update or write new data into database, you can utilize the WriteToDatabase method. Although it is possible perform the same action using ExecuteNonQuery method for simple table structure, WriteToDatabase method capable to handle more complex database structure which described in following section.

public void WriteEmployeeData(Employee[] newDatas)
{
    WriteToDatabase(newDatas);
}

Reading and Writing Complex Tables (ORM)

The ReadFromDatabase and WriteToDatabase methods make it easy to link objects in your code to tables in your database. They work well with tables that have relationships (child tables) and can handle working with more than one database using simple commands. Let's take a closer look at what they can do.

These methods follow the Fail Fast Principle, which means they quickly check if the structure of your objects matches the structure of your database tables when you first use them. This check is to make sure that all the columns match up. To avoid problems with older versions, your database tables can have extra columns that aren't in your objects, but not the other way around.

MS SQL Write Option

The 'WriteOptions' properties specify by SqlWriteOption class, sets how SqlHelper behaves when reading and writing data with following options:

• CreateTable: Automatic create table in database if not exists when set to true. Do nothing if table already exists.
• WriteMode: Used by WriteToDatabase method to decide what to update. (Reserved for future implementation, not available yet)
• CreateTable_TextFieldLenght: Define the length for column type VARCHAR or NVARCHAR when create new table.
• CreateTable_UnicodeText: Define if text field should be created as NVARCHAR instead of VARCHAR.

Table Name

Mapping a class to database table named Employee. Use SqlName attribute to overwrite default table name.

public class Employee { ... }

[SqlName("Employee")]
public class Emp { ... }

Column Name

All public properties that have public getters and setters are regarded as SQL columns. The names of these properties are, by default, used as the names of the corresponding columns. The SqlName attribute can be used to overwrite the default column name or table name.

public class Employee
{
    //Database Column: Name = 'Name', Type = NVARCHAR
    public string Name {get; set;}

    //Database Column: Name = 'Department', Type = NVARCHAR
    [SqlName("Department")]
    public string Dept {get; set;}

    //Database Column: Name = 'Salary', Type = INT
    public int Salary {get; set;}

    //Database Column: Name = 'Cost', Type = FLOAT
    public double Cost {get; set;}
    
    //Database Column: Name = 'Time', Type = DateTime2
    public DateTime Time {get; set;}

    //Read only property is not a valid SQL Column
    public int Age {get;}
}

Data Type

The table below displays the default data type mappings between objects and the database. Ensuring matching data types is crucial for the accurate writing and reading of data.

The SqlDataType attribute can be utilized to explicitly define the storage type of a value in the database. For instance, Enum and DateTime types can be stored as integers by applying the SqlDataType attribute, with Enum Status and DateTime being stored as an integer.

public enum Status { ... }
public class MyTable
{
    [SqlDataType(DataType.INT)]
    public Status CurrentStatus {get; set;}

    [SqlDataType(DataType.INT)]
    public DateTime LastUpdate {get; set;}
}

Index Table

The example below demonstrates that UserName is stored as an index in the NameID column of the Employee table, while the actual string value is kept in a key-value pair table named Name. This method facilitates efficient data retrieval and management, particularly when the same name is used multiple times across different tables.

Table name parameter for SqlIndexTable is optional. If left blank, the property name UserName will be used as the table name. The values for the index table can be shared among multiple tables.

public class Employee
{
    [SqlIndexTable("Name")]
    [SqlName("NameID")]
    public string UserName {get; set;}
}

Employee (Table)
  |- NameID, INT
  | ...

Name (Table)
  |- ID, INT, Primary Key
  |- Name, NVARCHAR(100), Unique

Primary Key

The primary key attribute is linked to the primary key in the database table. When the WriteToDatabase method is executed with an item whose ID is 0, it will create a new entry in the database table and assign it a unique ID. If the ID is not 0, it will update the existing row with the matching ID. NOTE: Primary key must be declared with int type.

public class Employee
{
    [PrimaryKey]
    public int ID {get; set;}
    ...
}

Parent and Child Tables

Let's examine the example provided: In database, Department (Table Name: Department) serves as a parent table, and List<Employee> (Table Name: Employees) functions as a child table with a one-to-many relationship, where each department can be associated with multiple employees. In other words, for every single entry in the Department table, there can be several corresponding entries in the Employee table, each representing an individual employee belonging to that department, while each Employee is assigned to only one Department.

Child table must have a properties ID declared with ParentKey attribute which function as mapping between child and parent table. Value of DepartmentID in example below is assigned by MS SQL Helper. PrimaryKey for class class Department is mandatory while it is optional for class Employee depends on need of the design.

A child table must have an ID property, decorated with ParentKey attribute, which serves as the link between child and parent table. In the example below, parent key value DepartmentID is assigned by MS SQL Helper.

public class Department
{
    [PrimaryKey]
    public int ID { get; set; }
    public string Name { get; set; }
    public List<Employee> Employees { get; set; } = new List<Employee>();
    ...
}

public class Employee
{
    public string Name { get; set; }
    [ParentKey(typeof(Department))]
    public int DepartmentID { get; set; }
    ...
}

Equivalent database table are given as follow:

Department (Table)
  |- ID, INT, Primary Key
  |- Name, NVARCHAR(100)

Employee (Table)
  |- Name, NVARCHAR(100)
  |- DepartmentID, INT  

Multiple Database Source

MS SQL Helper also supports multiple database connections, allowing data to be read from and written to tables stored in different MS SQL databases. The example below shows that the Department table is stored in the main database while the Employee table is stored in a different database specified by the connection string named "EmployeeDB". Switching between main and other databases is handled internally by read and write methods. This SQLDatabase attribute can only be used with child table.

public class Department
{
    ...
    [SqlName("Employee")]
    [SQLDatabase("EmployeeDB")]
    public List<Employee> Employees { get; set; } = new List<Employee>();
}

Array Table

The array table functionality allows the storage of array properties from a sample table, TableWithArray, into separate MS SQL tables. This process involves creating specific MS SQL tables for each type of array property, enabling efficient storage and retrieval of array data. The following example demonstrates how to map the array properties into MS SQL tables.

public class TableWithArray
{
    [PrimaryKey]
    public int ID { get; set; }

    ...
    
    public string[] ArrayData { get; set; }

    [SqlName("ArrayIntValue")]
    public int[] ItemValue { get; set; }
}

ArrayDatais a string array which mapped into ArrayTable with NVARCHAR value.

ArrayData (Table)
  |- ID, INT
  |- Value, NVARCHAR(MAX)

ItemValue is an integer array, mapped to the MS SQL table ArrayIntValue using the SqlName attribute.

ArrayIntValue (Table)
  |- ID, INT
  |- Value, INT

Unique Constraint

The SQLUnique and SQLUniqueMultiColumn attributes are used to mark columns with a unique constraint. SQLUnique sets a unique constraint on a single column, while SQLUniqueMultiColumn sets unique constraints across multiple columns.

Example usage of these attributes as follow:

public class User
{
    [PrimaryKey]
    public int ID { get; set; }

    [SQLUnique]
    public string Email { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    [SQLUniqueMultiColumn]
    public string Username { get; set; }

    [SQLUniqueMultiColumn]
    public string PhoneNumber { get; set; }
}

SQL Table Structure

User (Table)
  |- ID, INT, Primary Key
  |- Email, NVARCHAR(100), Unique
  |- FirstName, NVARCHAR(50)
  |- LastName, NVARCHAR(50)
  |- Username, NVARCHAR(50)
  |- PhoneNumber, NVARCHAR(20)
     (Unique Username, PhoneNumber)     

SQL Schema

CREATE TABLE User (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Email NVARCHAR(100) UNIQUE,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Username NVARCHAR(50),
    PhoneNumber NVARCHAR(20),
    CONSTRAINT UQ_Username_PhoneNumber UNIQUE (Username, PhoneNumber)
);

In this example:

The Email column is marked with the SQLUnique attribute, ensuring that each email address is unique. The Username and PhoneNumber columns are marked with the SQLUniqueMultiColumn attribute, ensuring that the combination of Username and PhoneNumber is unique across the table.

Suggestion and Feedback

We hope this document has provided you with clear and helpful information to use this tool. Your feedback is invaluable to us as it helps improve the quality of our work and clarity of our documentation. Please share your suggestions, comments, or any difficulties you encountered while using this guide. Your input will assist us in enhancing our resources and supporting users like you more effectively. Thank you for your attention and contribution.

Additional Resources

For a wide range of powerful development tools, visit our homepage at www.codearteng.com. Explore utilities designed to streamline your workflow, boost productivity, and tackle diverse programming challenges efficiently.