public static IEnumerable <string> GetDDLStatements(TableSchema schema)
{
var keys = new HashSet <string>(StringComparer.InvariantCultureIgnoreCase);
if (schema.PrimaryKey != null)
{
keys.UnionWith(schema.PrimaryKey);
}
if (schema.Index1 != null)
{
keys.UnionWith(schema.Index1);
}
if (schema.Index2 != null)
{
keys.UnionWith(schema.Index2);
}
var sb = new StringBuilder();
sb.Append($"CREATE TABLE {schema.TableName} (\n");
if (!string.IsNullOrEmpty(schema.PartitionColumnName))
{
sb.Append($"\t{schema.PartitionColumnName} TEXT NOT NULL,\n");
}
foreach (var column in schema.Columns)
{
var prop = schema.ColumnMap[column];
var sqlAttr = prop.GetCustomAttribute <SqlColumnAttribute>();
if (!TypeMap.TryGetValue(prop.PropertyType, out var colType))
{
colType = ColumnType.String;
}
var notNull = !sqlAttr.CanBeNull || keys.Contains(column);
sb.Append($"\t{column} {GetSqliteType(colType)} {(notNull ? "NOT" : "")} NULL,\n");
}
var idx = new List <string>();
if (!string.IsNullOrEmpty(schema.PartitionColumnName))
{
idx.Add(schema.PartitionColumnName);
}
if (schema.PrimaryKey != null)
{
idx.AddRange(schema.PrimaryKey);
}
sb.Append($"\tPRIMARY KEY ({string.Join(", ", idx)})\n");
sb.Append(")\n");
yield return(sb.ToString());
var indexNo = 0;
foreach (var index in new[] { schema.Index1, schema.Index2 })
{
if (index == null)
{
continue;
}
indexNo++;
sb.Length = 0;
idx.Clear();
if (!string.IsNullOrEmpty(schema.PartitionColumnName))
{
idx.Add(schema.PartitionColumnName);
}
idx.AddRange(index);
yield return($"CREATE INDEX {schema.TableName}_Index_{indexNo} ON {schema.TableName} ({string.Join(", ", idx)})");
}
}
public static IEnumerable <TD> PopulateDataObjects <TD>(this TableSchema schema, IDataReader reader)
where TD : new()
{
while (reader.Read())
{
var data = new TD();
for (var i = 0; i < schema.Columns.Count; i++)
{
if (reader.IsDBNull(i))
{
continue;
}
var column = schema.ColumnMap[schema.Columns[i]];
if (column.PropertyType == typeof(string))
{
column.SetMethod.Invoke(data, new object[] { reader.GetString(i) });
}
else if (column.PropertyType == typeof(bool))
{
column.SetMethod.Invoke(data, new object[] { reader.GetBoolean(i) });
}
else if (column.PropertyType == typeof(int))
{
column.SetMethod.Invoke(data, new object[] { reader.GetInt32(i) });
}
else if (column.PropertyType == typeof(uint))
{
column.SetMethod.Invoke(data, new object[] { (uint)reader.GetInt32(i) });
}
else if (column.PropertyType == typeof(long))
{
column.SetMethod.Invoke(data, new object[] { reader.GetInt64(i) });
}
else if (column.PropertyType == typeof(ulong))
{
column.SetMethod.Invoke(data, new object[] { (ulong)reader.GetInt64(i) });
}
else if (column.PropertyType == typeof(byte))
{
column.SetMethod.Invoke(data, new object[] { reader.GetByte(i) });
}
else if (column.PropertyType == typeof(sbyte))
{
column.SetMethod.Invoke(data, new object[] { (sbyte)reader.GetByte(i) });
}
else if (column.PropertyType == typeof(short))
{
column.SetMethod.Invoke(data, new object[] { reader.GetInt16(i) });
}
else if (column.PropertyType == typeof(ushort))
{
column.SetMethod.Invoke(data, new object[] { (ushort)reader.GetInt16(i) });
}
else if (column.PropertyType == typeof(float))
{
column.SetMethod.Invoke(data, new object[] { reader.GetFloat(i) });
}
else if (column.PropertyType == typeof(double))
{
column.SetMethod.Invoke(data, new object[] { reader.GetDouble(i) });
}
else if (column.PropertyType == typeof(decimal))
{
column.SetMethod.Invoke(data, new object[] { reader.GetDecimal(i) });
}
}
yield return(data);
}
}