public static List <SchemaRow> ToSchemaRowCollection(this DataTable dt)
{
return(dt.AsEnumerable()
.Select(row =>
{
var obj = new SchemaRow();
FillObject(obj, row);
return obj;
})
.ToList());
}
private static void LoadForeignKeys(List <SchemaRow> rows, TableName tname)
{
string SQL = $"SELECT * FROM PRAGMA_FOREIGN_KEY_LIST('{tname.Name}')";
var dt = new SqlCmd(tname.Provider, SQL).FillDataTable();
foreach (DataRow row in dt.Rows)
{
string columnName = row.GetField <string>("from");
SchemaRow _row = rows.Where(x => x.TableName == tname.Name && x.ColumnName == columnName).SingleOrDefault();
_row.PK_Schema = SchemaName.empty;
_row.PK_Table = row.GetField <string>("table");
_row.PK_Column = row.GetField <string>("to");
_row.FKContraintName = $"FK_{tname.Name}_{_row.PK_Table}";
}
}
public static void FillObject(this SchemaRow item, DataRow row)
{
item.SchemaName = row.Field <string>(_SCHEMANAME);
item.TableName = row.Field <string>(_TABLENAME);
item.ColumnName = row.Field <string>(_COLUMNNAME);
item.DataType = row.Field <string>(_DATATYPE);
item.Length = row.Field <short>(_LENGTH);
item.Nullable = row.Field <bool>(_NULLABLE);
item.precision = row.Field <byte>(_PRECISION);
item.scale = row.Field <byte>(_SCALE);
item.IsPrimary = row.Field <bool>(_ISPRIMARY);
item.IsIdentity = row.Field <bool>(_ISIDENTITY);
item.IsComputed = row.Field <bool>(_ISCOMPUTED);
item.definition = row.Field <string>(_DEFINITION);
item.PKContraintName = row.Field <string>(_PKCONTRAINTNAME);
item.PK_Schema = row.Field <string>(_PK_SCHEMA);
item.PK_Table = row.Field <string>(_PK_TABLE);
item.PK_Column = row.Field <string>(_PK_COLUMN);
item.FKContraintName = row.Field <string>(_FKCONTRAINTNAME);
}
public static void CopyTo(this SchemaRow from, SchemaRow to)
{
to.SchemaName = from.SchemaName;
to.TableName = from.TableName;
to.ColumnName = from.ColumnName;
to.DataType = from.DataType;
to.Length = from.Length;
to.Nullable = from.Nullable;
to.precision = from.precision;
to.scale = from.scale;
to.IsPrimary = from.IsPrimary;
to.IsIdentity = from.IsIdentity;
to.IsComputed = from.IsComputed;
to.definition = from.definition;
to.PKContraintName = from.PKContraintName;
to.PK_Schema = from.PK_Schema;
to.PK_Table = from.PK_Table;
to.PK_Column = from.PK_Column;
to.FKContraintName = from.FKContraintName;
}
public static bool CompareTo(this SchemaRow a, SchemaRow b)
{
return(a.SchemaName == b.SchemaName &&
a.TableName == b.TableName &&
a.ColumnName == b.ColumnName &&
a.DataType == b.DataType &&
a.Length == b.Length &&
a.Nullable == b.Nullable &&
a.precision == b.precision &&
a.scale == b.scale &&
a.IsPrimary == b.IsPrimary &&
a.IsIdentity == b.IsIdentity &&
a.IsComputed == b.IsComputed &&
a.definition == b.definition &&
a.PKContraintName == b.PKContraintName &&
a.PK_Schema == b.PK_Schema &&
a.PK_Table == b.PK_Table &&
a.PK_Column == b.PK_Column &&
a.FKContraintName == b.FKContraintName);
}
public static void UpdateRow(this SchemaRow item, DataRow row)
{
row.SetField(_SCHEMANAME, item.SchemaName);
row.SetField(_TABLENAME, item.TableName);
row.SetField(_COLUMNNAME, item.ColumnName);
row.SetField(_DATATYPE, item.DataType);
row.SetField(_LENGTH, item.Length);
row.SetField(_NULLABLE, item.Nullable);
row.SetField(_PRECISION, item.precision);
row.SetField(_SCALE, item.scale);
row.SetField(_ISPRIMARY, item.IsPrimary);
row.SetField(_ISIDENTITY, item.IsIdentity);
row.SetField(_ISCOMPUTED, item.IsComputed);
row.SetField(_DEFINITION, item.definition);
row.SetField(_PKCONTRAINTNAME, item.PKContraintName);
row.SetField(_PK_SCHEMA, item.PK_Schema);
row.SetField(_PK_TABLE, item.PK_Table);
row.SetField(_PK_COLUMN, item.PK_Column);
row.SetField(_FKCONTRAINTNAME, item.FKContraintName);
}
public static string ToSimpleString(this SchemaRow obj)
{
return(string.Format("{{SchemaName:{0}, TableName:{1}, ColumnName:{2}, DataType:{3}, Length:{4}, Nullable:{5}, precision:{6}, scale:{7}, IsPrimary:{8}, IsIdentity:{9}, IsComputed:{10}, definition:{11}, PKContraintName:{12}, PK_Schema:{13}, PK_Table:{14}, PK_Column:{15}, FKContraintName:{16}}}",
obj.SchemaName,
obj.TableName,
obj.ColumnName,
obj.DataType,
obj.Length,
obj.Nullable,
obj.precision,
obj.scale,
obj.IsPrimary,
obj.IsIdentity,
obj.IsComputed,
obj.definition,
obj.PKContraintName,
obj.PK_Schema,
obj.PK_Table,
obj.PK_Column,
obj.FKContraintName));
}
public static IDictionary <string, object> ToDictionary(this SchemaRow item)
{
return(new Dictionary <string, object>()
{
[_SCHEMANAME] = item.SchemaName,
[_TABLENAME] = item.TableName,
[_COLUMNNAME] = item.ColumnName,
[_DATATYPE] = item.DataType,
[_LENGTH] = item.Length,
[_NULLABLE] = item.Nullable,
[_PRECISION] = item.precision,
[_SCALE] = item.scale,
[_ISPRIMARY] = item.IsPrimary,
[_ISIDENTITY] = item.IsIdentity,
[_ISCOMPUTED] = item.IsComputed,
[_DEFINITION] = item.definition,
[_PKCONTRAINTNAME] = item.PKContraintName,
[_PK_SCHEMA] = item.PK_Schema,
[_PK_TABLE] = item.PK_Table,
[_PK_COLUMN] = item.PK_Column,
[_FKCONTRAINTNAME] = item.FKContraintName
});
}
private static void LoadColumns(List <SchemaRow> rows, TableName tname)
{
string SQL = $"SELECT * FROM PRAGMA_TABLE_INFO('{tname.Name}')";
var dt = new SqlCmd(tname.Provider, SQL).FillDataTable();
foreach (DataRow row in dt.Rows)
{
string columnName = row.GetField <string>("name");
SchemaRow _row = rows.Where(x => x.TableName == tname.Name && x.ColumnName == columnName).SingleOrDefault();
if (_row == null)
{
_row = new SchemaRow
{
SchemaName = SchemaName.empty,
TableName = tname.Name,
ColumnName = row.GetField <string>("name"),
};
}
_row.DataType = row.GetField <string>("type");
_row.Length = 0;
_row.Nullable = row.GetField <long>("notnull") == 0;
_row.precision = 0;
_row.scale = 0;
_row.IsPrimary = row.GetField <long>("pk") == 1;
_row.IsIdentity = _row.DataType.ToLower().Contains("identity");
_row.IsComputed = false;
_row.definition = null;
_row.PKContraintName = _row.IsPrimary ? $"PK_{tname.Name}" : null;
ParseType(_row, _row.DataType);
rows.Add(_row);
}
}
private static void ParseType(SchemaRow row, string dataType)
{
string type = dataType.ToLower();
//possible type: int IDENTITY(1,1)
string[] L = type.Split(' ');
string ty;
if (L.Length > 1)
{
ty = L[0];
}
else
{
ty = type;
}
switch (ty)
{
case "int":
case "integer":
row.DataType = "int";
return;
case "real":
case "double":
row.DataType = "float";
return;
case "blob":
row.DataType = "binary";
return;
}
if (type.IndexOf('(') > 0 && type.IndexOf(')') > 0)
{
string[] items = type.Split(new char[] { '(', ',', ')' }, StringSplitOptions.RemoveEmptyEntries);
string _type = items[0].Trim();
int a1 = int.Parse(items[1]);
switch (_type)
{
case "nvarchar":
row.DataType = "nvarchar";
row.Length = Convert.ToInt16(a1 * 2);
return;
case "nchar":
case "varchar":
row.DataType = _type;
row.Length = Convert.ToInt16(a1);
return;
case "numeric":
row.DataType = "numeric";
row.precision = (byte)short.Parse(items[1]);
row.scale = (byte)short.Parse(items[2]);
return;
default:
throw new NotImplementedException();
}
}
row.DataType = type;
return;
}
