如何在C#中用自定义属性将对象转换为SQL语句

创建属性

创建名为Attributes的新文件夹，并创建新的属性，如下所示。

表属性

创建名为Table.cs的新文件，如下所示。

using System;

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

列注释

创建名为Column.cs的新文件，如下所示。

using System;

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

创建实体类

创建Entities文件夹，并创建名为Product.cs的新类，如下所示。

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; }
	}
}							

运行应用程序

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();
			var values = new List();
			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;
		}

	}
}						

项目结构

输出

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