Convert Object to SQL Statements with Custom Attribute in C#

Create Attributes

Create new folder named Attributes and create new attributes as below:

Table Attribute

Create new file named Table.cs as below:

using System;

namespace LearnAdvancedCSharpWithRealApps.Attributes
{
	[AttributeUsage(AttributeTargets.Class)]
	public class Table : Attribute
	{
		public string Name { get; set; }
	}
}

Column Annotation

Create new file named Column.cs as below:

using System;

namespace LearnAdvancedCSharpWithRealApps.Attributes
{
	[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
	public class Column : Attribute
	{
		public string Name { get; set; }
	}
}				

Create Entity Class

Create Entities folder and create new class named Product.cs as below:

using LearnAdvancedCSharpWithRealApps.Attributes;
using System;

namespace LearnAdvancedCSharpWithRealApps.Entities
{
	[Table(Name = "product")]
	public class Product
	{
		[Column(Name = "id")]
		public string Id { get; set; }

		[Column(Name = "name")]
		public string Name { get; set; }

		[Column(Name = "price")]
		public double Price { get; set; }

		[Column(Name = "quantity")]
		public int Quantity { get; set; }

		[Column(Name = "status")]
		public bool Status { get; set; }

		[Column(Name = "created")]
		public DateTime Created { get; set; }
	}
}							

Run Application

using LearnAdvancedCSharpWithRealApps.Attributes;
using LearnAdvancedCSharpWithRealApps.Entities;
using System;
using System.Collections.Generic;
using System.Reflection;

namespace LearnAdvancedCSharpWithRealApps
{
	class Program
	{
		static void Main(string[] args)
		{
			var product = new Product
			{
				Id = "p01",
				Name = "Name 1",
				Price = 4.5,
				Quantity = 12,
				Status = true,
				Created = DateTime.Now
			};

			Console.WriteLine(ConvertToInsertIntoSQL(product));

			Console.ReadLine();
		}

		private static string ConvertToInsertIntoSQL(object obj)
		{
			var table = obj.GetType().GetCustomAttribute(typeof(Table)) as Table;
			var sql = "insert into " + table.Name + "(";
			var columns = new List<string>();
			var values = new List<object>();
			foreach (var propertyInfo in obj.GetType().GetProperties())
			{
				var column = propertyInfo.GetCustomAttribute(typeof(Column)) as Column;
				columns.Add(column.Name);
				if (propertyInfo.PropertyType.Name == "String" || propertyInfo.PropertyType.Name == "Boolean")
				{
					values.Add("\"" + propertyInfo.GetValue(obj).ToString() + "\"");
				}
				else if (propertyInfo.PropertyType.Name == "DateTime")
				{
					var dateTime = (DateTime)propertyInfo.GetValue(obj);
					values.Add("\"" + dateTime.ToString("yyyy-MM-dd") + "\"");
				}
				else
				{
					values.Add(propertyInfo.GetValue(obj).ToString());
				}
			}
			sql += string.Join(", ", columns) + ") values(";
			sql += string.Join(", ", values) + ")";
			return sql;
		}

	}
}

Structure of Project

Output

insert into product(id, name, price, quantity, status, created) values("p01", "Name 1", 4.5, 12, "True", "2021-10-10")