wGrow - Team Notes

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.