1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
| public class SqlGenerator { public static string GenerateCreateTableSql(Type type) { var tableAttr = (TableAttribute)Attribute.GetCustomAttribute(type, typeof(TableAttribute)); string tableName = tableAttr?.Name ?? type.Name;
var columns = new List<string>(); PropertyInfo[] properties = type.GetProperties();
foreach (var prop in properties) { var columnAttr = (ColumnAttribute)Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute)); string columnName = columnAttr?.Name ?? prop.Name; string columnType = GetSqlType(prop.PropertyType, columnAttr);
var columnDef = $"{columnName} {columnType}";
if (columnAttr?.IsPrimaryKey == true) columnDef += " PRIMARY KEY";
if (columnAttr?.IsAutoIncrement == true) columnDef += " AUTOINCREMENT";
if (prop.PropertyType == typeof(string) && columnAttr?.MaxLength.HasValue == true) columnDef = columnDef.Replace(columnType, $"VARCHAR({columnAttr.MaxLength})");
columns.Add(columnDef); }
return $"CREATE TABLE {tableName} ({string.Join(", ", columns)})"; }
public static string GenerateInsertSql(object obj) { Type type = obj.GetType(); var tableAttr = (TableAttribute)Attribute.GetCustomAttribute(type, typeof(TableAttribute)); string tableName = tableAttr?.Name ?? type.Name;
var columns = new List<string>(); var values = new List<string>();
PropertyInfo[] properties = type.GetProperties(); foreach (var prop in properties) { var columnAttr = (ColumnAttribute)Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute)); if (columnAttr?.IsAutoIncrement == true) continue;
string columnName = columnAttr?.Name ?? prop.Name; columns.Add(columnName);
object value = prop.GetValue(obj); values.Add(FormatValue(value)); }
return $"INSERT INTO {tableName} ({string.Join(", ", columns)}) VALUES ({string.Join(", ", values)})"; }
public static string GenerateUpdateSql(object obj) { Type type = obj.GetType(); var tableAttr = (TableAttribute)Attribute.GetCustomAttribute(type, typeof(TableAttribute)); string tableName = tableAttr?.Name ?? type.Name;
var setParts = new List<string>(); string whereClause = "";
PropertyInfo[] properties = type.GetProperties(); foreach (var prop in properties) { var columnAttr = (ColumnAttribute)Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute)); string columnName = columnAttr?.Name ?? prop.Name; object value = prop.GetValue(obj);
if (columnAttr?.IsPrimaryKey == true) { whereClause = $"WHERE {columnName} = {FormatValue(value)}"; } else { setParts.Add($"{columnName} = {FormatValue(value)}"); } }
return $"UPDATE {tableName} SET {string.Join(", ", setParts)} {whereClause}"; }
public static string GenerateSelectSql(Type type) { var tableAttr = (TableAttribute)Attribute.GetCustomAttribute(type, typeof(TableAttribute)); string tableName = tableAttr?.Name ?? type.Name;
var columns = new List<string>(); PropertyInfo[] properties = type.GetProperties();
foreach (var prop in properties) { var columnAttr = (ColumnAttribute)Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute)); string columnName = columnAttr?.Name ?? prop.Name; columns.Add(columnName); }
return $"SELECT {string.Join(", ", columns)} FROM {tableName}"; }
private static string GetSqlType(Type type, ColumnAttribute attr) { if (type == typeof(int)) return "INT"; if (type == typeof(long)) return "BIGINT"; if (type == typeof(decimal)) return "DECIMAL(18,2)"; if (type == typeof(bool)) return "BIT"; if (type == typeof(DateTime)) return "DATETIME"; if (type == typeof(string)) return attr?.MaxLength.HasValue == true ? $"VARCHAR({attr.MaxLength})" : "VARCHAR(255)"; return "VARCHAR(255)"; }
private static string FormatValue(object value) { if (value == null) return "NULL"; if (value is string str) return $"'{str.Replace("'", "''")}'"; if (value is bool b) return b ? "1" : "0"; if (value is DateTime dt) return $"'{dt:yyyy-MM-dd HH:mm:ss}'"; return value.ToString(); } }
[Table("users")] public class User { [Column("id", IsPrimaryKey = true, IsAutoIncrement = true)] public int Id { get; set; }
[Column("username", MaxLength = 50)] public string Username { get; set; }
[Column("email", MaxLength = 100)] public string Email { get; set; }
[Column("age")] public int Age { get; set; } }
Console.WriteLine(SqlGenerator.GenerateCreateTableSql(typeof(User)));
var user = new User { Id = 1, Username = "alice", Email = "alice@example.com", Age = 30 }; Console.WriteLine(SqlGenerator.GenerateInsertSql(user));
|