wGrow - Team Notes

Sharing Expertise: Tech Insights and Case Studies

Exploring Reflection in C#: Dynamically Accessing Object Properties and Database Operations

Introduction:

Reflection is a powerful feature in C# that allows us to inspect and interact with the metadata of types, objects, and assemblies at runtime. In this article, we will delve into the basics of Reflection in C#, demonstrate how to explore object properties dynamically, and create Microsoft SQL Server strings for database operations such as insert, update, and delete.

What is Reflection?

Reflection, in the context of C#, enables developers to examine and manipulate the metadata of assemblies, types, and objects during runtime. This capability proves useful in various scenarios, such as creating instances of types, accessing members (properties, fields, and methods), and examining attributes.

Using Reflection to Explore Object Properties:

To explore an object's properties, we can use the Type class provided by the System.Reflection namespace. Here's an example of how to retrieve an object's properties using Reflection:

using System;
using System.Reflection;

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime JoiningDate { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        Employee employee = new Employee { Id = 1, Name = "John Doe", JoiningDate = DateTime.Now };
        
        Type type = employee.GetType();
        PropertyInfo[] properties = type.GetProperties();

        foreach (PropertyInfo property in properties)
        {
            Console.WriteLine($"Property: {property.Name}, Value: {property.GetValue(employee)}");
        }
    }
}


In the example above, we first create an instance of the Employee class and then use the GetType() method to obtain its Type object. Next, we call the GetProperties() method to retrieve an array of PropertyInfo objects representing the object's properties. Finally, we iterate through the PropertyInfo objects and print the property names and their corresponding values using the GetValue() method.

Generating SQL Statements with Reflection:

We can leverage Reflection to generate SQL statements dynamically for database operations. Let's see how to create Insert, Update, and Delete SQL strings based on the Employee class:

using System;
using System.Reflection;
using System.Text;

public static class SqlStatementGenerator
{
    public static string GenerateInsertSql(T obj)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();

        foreach (PropertyInfo property in properties)
        {
            columns.Append($"[{property.Name}], ");
            values.Append($"@{property.Name}, ");
        }

        string tableName = type.Name;

        return $"INSERT INTO [{tableName}] ({columns.ToString().TrimEnd(',', ' ')}) VALUES ({values.ToString().TrimEnd(',', ' ')})";
    }

    public static string GenerateUpdateSql(T obj, string keyColumn)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        StringBuilder columns = new StringBuilder();

        foreach (PropertyInfo property in properties)
        {
            if (!property.Name.Equals(keyColumn, StringComparison.OrdinalIgnoreCase))
            {
                columns.Append($"[{property.Name}] = @{property.Name}, ");
            }
        }

        string tableName = type.Name;

        return $"UPDATE [{tableName}] SET {columns.ToString().TrimEnd(',', ' ')} WHERE [{keyColumn}] = @{keyColumn}";
    }

    public static string GenerateDeleteSql(T obj, string keyColumn)
    {
        Type type = obj.GetType();
        string tableName = type.Name;

        return $"DELETE FROM [{tableName}] WHERE [{keyColumn}] = @{keyColumn}";
    }
}

In this example, we have created a SqlStatementGenerator class with three static methods: GenerateInsertSql, GenerateUpdateSql, and GenerateDeleteSql. These methods take a generic object and a key column name (in the case of Update and Delete) as parameters and generate the corresponding SQL statements.

Here's a brief explanation of each method:

  1. GenerateInsertSql: Iterates through the properties of the object and builds the SQL INSERT statement by appending the column names and parameter placeholders.
  2. GenerateUpdateSql: Similar to GenerateInsertSql, but creates an SQL UPDATE statement while skipping the key column in the SET clause.
  3. GenerateDeleteSql: Generates an SQL DELETE statement using the object's type and the key column name.

Now, let's use these methods with an instance of the Employee class:

class Program
{
    static void Main(string[] args)
    {
        Employee employee = new Employee { Id = 1, Name = "John Doe", JoiningDate = DateTime.Now };

        string insertSql = SqlStatementGenerator.GenerateInsertSql(employee);
        string updateSql = SqlStatementGenerator.GenerateUpdateSql(employee, "Id");
        string deleteSql = SqlStatementGenerator.GenerateDeleteSql(employee, "Id");

        Console.WriteLine("Generated SQL Statements:");
        Console.WriteLine($"INSERT: {insertSql}");
        Console.WriteLine($"UPDATE: {updateSql}");
        Console.WriteLine($"DELETE: {deleteSql}");
    }
}


The output of this program will be:

Generated SQL Statements:
INSERT: INSERT INTO [Employee] ([Id], [Name], [JoiningDate]) VALUES (@Id, @Name, @JoiningDate)
UPDATE: UPDATE [Employee] SET [Name] = @Name, [JoiningDate] = @JoiningDate WHERE [Id] = @Id
DELETE: DELETE FROM [Employee] WHERE [Id] = @Id

These SQL statements can be used with the SqlCommand class to perform actual database operations, such as inserting, updating, and deleting records. This approach provides a more dynamic and flexible way of generating SQL statements for various objects without hardcoding them.

Conclusion:

Reflection in C# is a powerful feature that allows us to inspect and manipulate metadata at runtime. In this article, we explored the basics of Reflection and demonstrated how to use it for dynamically accessing object properties and generating SQL statements for database operations. It is essential to note that Reflection comes with performance overhead and security concerns, so it should be used judiciously and with caution.

And for experienced developers, from here you can actually create your own generic auto-processing logic for all submitted objects. You can only to write a single submission handling code, and based on the submission values to determine what to do with the posted values. Usually, you may want to name the object properties with some logic to make the processing much more easier.


 

Related

Creating a Windows Service to Monitor Remote Desktop Authentication Attempts and Trigger Alerts

Creating a Windows Service to Monitor Remote Desktop Authentication Attempts and Trigger Alerts

Remote Desktop Protocol (RDP) is a widely used feature in Windows to access a computer remotely. How...

Read More >
TECOM, Order Processing System

TECOM, Order Processing System

TECOM is our in-house ERP system for Order Process. System is great for sellers running multiple sho...

Read More >
Case Study: Setting up a Network Firewall for an Internet Application Server

Case Study: Setting up a Network Firewall for an Internet Application Server

In today's world, securing networks from possible threats and attacks is more than a necessity; ...

Read More >
Smart Quotation Generation and Management

Smart Quotation Generation and Management

A unified system for managing your customers, sales team, products, and quotations for all clients. ...

Read More >
Securing Data in ASP.NET Web Projects with SQL Server Transparent Data Encryption (TDE)

Securing Data in ASP.NET Web Projects with SQL Server Transparent Data Encryption (TDE)

This technical article provides an overview of SQL Server Transparent Data Encryption (TDE) and demo...

Read More >
Implementing Business Continuity Management for a Singapore Real Estate Agency's ERP Solution

Implementing Business Continuity Management for a Singapore Real Estate Agency's ERP Solution

This technical summary documents the process of designing and implementing a Business Continuity Man...

Read More >
Contact Us
  • Our Address:
    114 Lavender Street, #07-51, CT Hub 2, Singapore 338729
    Malaysia Johor - 99-01 Jalan Adda 3/1 Taman Adda Height 81100 Johor Bahru Johor, Malaysia
  • Phone Number:
    +65 6652 3398
  • WhatsApp:
    WhatsApp Us
  • Email:
    [email protected]