public MappedTables LinqToSqlMapping()
{
var mappedTables = new MappedTables();
foreach (var table in _context.Mapping.GetTables())
{
var tableSchemaModel = new TableSchemaModel();
var results = from dm in table.RowType.DataMembers
where dm.DbType != null
select new { dm.Name, dm.DbType, dm.Type, dm.MappedName, dm.IsPrimaryKey };
foreach (var colinfo in results)
{
var columnData = new TableMetaData
{
Name = colinfo.Name,
IsPrimeKey = colinfo.IsPrimaryKey,
DbType = colinfo.DbType,
Type = colinfo.Type
};
tableSchemaModel.MetaData.Add(columnData);
}
tableSchemaModel.TableName = table.TableName;
mappedTables.TablesMapped.Add(tableSchemaModel);
}
return(mappedTables);
}
/// <summary>
/// Gets the schema model.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns>TableSchemaModel.</returns>
public override TableSchemaModel GetSchemaModel(string tableName)
{
TableSchemaModel table = new TableSchemaModel();
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
cn.GetSchema("Tables", new string[] { tableName });
var schema = cn.GetSchema("Columns", new string[] { tableName }).AsEnumerable().GroupBy(g => g.Field <string>("TABLE_NAME"));
foreach (var item in schema)
{
table.TableName = item.Key;
table.Fields.AddRange(item.Select(s => new FieldSchemaModel()
{
Name = s.Field <string>("COLUMN_NAME"),
Ordinal = s.Field <Int64>("ORDINAL_POSITION"),
Type = Enum.GetName(typeof(DataTypeEnums), s.Field <object>("DATA_TYPE")),
MaxLength = s.Field <Int64?>("CHARACTER_MAXIMUM_LENGTH"),
TableName = item.Key
}).ToArray());
}
}
return(table);
}
/// <summary>
/// Fills the schema.
/// </summary>
/// <param name="select">The select.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns>List<TableSchemaModel>.</returns>
protected override List <TableSchemaModel> FillSchema(string select, string tableName)
{
var dataset = ExecuteQuery(select);
List <TableSchemaModel> tableSchemaModel = new List <TableSchemaModel>();
var schema = dataset.Tables[0];
TableSchemaModel table = new TableSchemaModel();
tableSchemaModel.Add(table);
table.TableName = tableName;
DqlDataTypeMap map = new DqlDataTypeMap();
//attr_type, min_length, attr_length, attr_repeating, mandatory
int ordinal = 0;
var fields = schema.AsEnumerable().Select(s => new FieldSchemaModel
{
Name = (string)s["attr_name"],
MaxLength = (int)s["attr_length"],
Type = map[(int)s["attr_type"]].ToString(),
IsRepeating = int.Parse(s["attr_repeating"].ToString()) == 0 ? false : true,
Ordinal = ordinal++,
TableName = tableName
});
table.Fields.AddRange(fields);
return(tableSchemaModel);
}
public TableSchemaModel GetSchemaTableColumns(string tableName)
{
var tableSchemaModel = new TableSchemaModel();
using (var connection = new SqlConnection(_connectionString))
{
var restrictions = new string[4] {
null, null, tableName, null
};
connection.Open();
var columnList =
connection.GetSchema("Columns", restrictions)
.AsEnumerable()
.ToList();
foreach (var colinfo in columnList)
{
var columnData = new TableMetaData
{
Name = colinfo.ItemArray[_colName].ToString(),
IsPrimeKey = false,
DbType = colinfo[_colType].ToString()
};
tableSchemaModel.MetaData.Add(columnData);
}
return(tableSchemaModel);
}
}
/// <summary>
/// Compiles this instance.
/// </summary>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
private bool Compile()
{
try
{
if (Verify() == false)
{
return(false);
}
Cursor = Cursors.WaitCursor;
var query = model.DataSource.ExecuteQuery(txtQuery.Text);
compiledSchema = new TableSchemaModel(query.Table(), txtQueryName.EditValue.ToString(), txtQuery.Text);
//TODO: Need to update this schema not to lose the validation paramters
gridSourcehSchema.DataSource = compiledSchema.Fields.ToList();
gridSourcehSchema.RefreshDataSource();
xtraTabControl1.SelectedTabPage = xtraTabPage2;
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().Name);
return(false);
}
finally
{
Cursor = Cursors.Default;
}
}
public static bool TryCreate(Schema.Schema schema, FlatBufferObject table, [NotNullWhen(true)] out TableSchemaModel?model)
{
model = null;
if (table.IsStruct)
{
return(false);
}
model = new TableSchemaModel(schema, table);
return(true);
}
public bool CreateTable(TableSchemaModel tableSchema, string connectionName = "default")
{
var sql = GenerateCreateTableSql(tableSchema);
return(sqlService.OpenConnection(
(connection) =>
{
var result = connection.Execute(sql);
return result > 0;
}, connectionName));
}
/// <summary>
/// Initializes a new instance of the <see cref="frmSchemaEditor"/> class.
/// </summary>
/// <param name="model">The model.</param>
public frmSchemaEditor(DataSourceModel model, string queryName, bool isNew) : this()
{
this.IsNew = isNew;
this.model = model;
if (isNew == false)
{
tableSchema = model.TableSchemas.Where(w => w.TableName == queryName).SingleOrDefault();
txtQuery.Text = tableSchema.Query.Replace("\r\n", "\n").Replace("\n", "\r\n");
txtQueryName.EditValue = queryName;
txtQueryName.Enabled = false;
}
}
private string GenerateCreateTableSql(TableSchemaModel tableSchema)
{
var columnsBuilder = new string[tableSchema.Columns.Count];
for (int i = 0; i < tableSchema.Columns.Count; i++)
{
var column = tableSchema.Columns[i];
columnsBuilder[i] = $"[{column.Name}] {column.Type} {GetNullableText(column.Null)} {GetPrimaryKeyText(column.PrimaryKey)}";
}
return($"CREATE TABLE IF NOT EXISTS [{tableSchema.TableName}] ({string.Join(",\n", columnsBuilder)});");
}
/// <summary>
/// Fills the schema model.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="item">The item.</param>
private void FillSchemaModel(TableSchemaModel table, IGrouping <string, DataRow> item)
{
table.TableName = item.Key;
table.Fields.AddRange(item.Select(s => new FieldSchemaModel()
{
Name = s.Field <string>("COLUMN_NAME"),
Ordinal = s.Field <Int64>("ORDINAL_POSITION"),
Type = Enum.GetName(typeof(DataTypeEnums), s.Field <object>("DATA_TYPE")),
// Type = s.Field<object>("DATA_TYPE").ToString(),
MaxLength = s.Field <Int64?>("CHARACTER_MAXIMUM_LENGTH"),
TableName = item.Key
}).ToArray());
}
public void LogTableError(TableSchemaModel tableSchema, Exception exception, string connectionName = "default")
{
var sql = $"INSERT INTO {LogTableName} (TableName, SqlQuery, Data, ExceptionDetails) VALUES (:TableName, :SqlQuery, :Data, :ExceptionDetails)";
sqlService.OpenConnection((connection) =>
{
connection.Execute(sql, new
{
TableName = tableSchema.TableName,
SqlQuery = GenerateCreateTableSql(tableSchema),
Data = JsonConvert.SerializeObject(tableSchema, Formatting.Indented),
ExceptionDetails = exception
});
}, connectionName);
}
/// <summary>
/// Gets the schema model.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns>TableSchemaModel.</returns>
public override TableSchemaModel GetSchemaModel(string tableName)
{
TableSchemaModel table = new TableSchemaModel();
using (DqlConnection cn = new DqlConnection(ConnectionString))
{
cn.Open();
var schema = cn.GetSchema(tableName).AsEnumerable().GroupBy(g => g.Field <string>("name"));
foreach (var item in schema)
{
FillSchemaModel(table, item);
}
}
return(table);
}
/// <summary>
/// Gets the schema model external.
/// </summary>
/// <returns>List<TableSchemaModel>.</returns>
public List <TableSchemaModel> GetSchemaModelExternal()
{
DqlDataTypeMap map = new DqlDataTypeMap();
List <TableSchemaModel> tableSchemaModel = new List <TableSchemaModel>();
string select = "select distinct t.name, t.attr_name, t.attr_type, '0' as min_length, t.attr_length, t.attr_repeating, a.not_null as mandatory from dm_type t, dmi_dd_attr_info a where t.name = a.type_name and t.attr_name = a.attr_name enable(row_based)";
var schema = ExecuteQueryExternal(select).Tables[0].AsEnumerable().GroupBy(g => g.Field <string>("name"));
foreach (var item in schema)
{
TableSchemaModel table = new TableSchemaModel();
tableSchemaModel.Add(table);
FillSchemaModel(table, item);
}
return(tableSchemaModel);
}
/// <summary>
/// Fills the schema model.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="item">The item.</param>
private void FillSchemaModel(TableSchemaModel table, IGrouping <string, DataRow> item)
{
DqlDataTypeMap map = new DqlDataTypeMap();
table.TableName = item.Key;
int ordinal = 0;
table.Fields.AddRange(item.Select(s => new FieldSchemaModel()
{
Name = s.Field <string>("attr_name"),
Ordinal = ordinal++,
Type = map[(int)s["attr_type"]].ToString(),
MaxLength = (int)s["attr_length"],
IsRepeating = Convert.ToBoolean(s["attr_repeating"]),
TableName = item.Key
}).ToArray());
}
/// <summary>
/// Gets the schema model.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns>TableSchemaModel.</returns>
public override TableSchemaModel GetSchemaModel(string tableName)
{
TableSchemaModel table = new TableSchemaModel();
using (OleDbConnection cn = new OleDbConnection(ConnectionString))
{
cn.Open();
cn.GetSchema("Tables");
var schema = cn.GetSchema("Columns").AsEnumerable().GroupBy(g => g.Field <string>("TABLE_NAME"));
foreach (var item in schema.Where(w => w.Key == tableName))
{
FillSchemaModel(table, item);
}
}
return(table);
}
/// <summary>
/// Fills the schema.
/// </summary>
/// <param name="select">The select.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns>List<TableSchemaModel>.</returns>
protected virtual List <TableSchemaModel> FillSchema(string select, string tableName)
{
var schema = ExecuteQuery(select);
List <TableSchemaModel> tableSchemaModel = new List <TableSchemaModel>();
TableSchemaModel table = new TableSchemaModel();
tableSchemaModel.Add(table);
table.TableName = tableName;
foreach (DataColumn col in schema.Tables[0].Columns)
{
var field = new FieldSchemaModel(col, tableName);
table.Fields.Add(field);
}
return(tableSchemaModel);
}
public string ConvertSimpleTableQuery(string tableName)
{
try
{
TableSchemaModel columnData = schemaRepository.GetSchemaTableColumns(tableName);
var simpleQuery = new ComposedQuerySimple
{
root = new Root
{
enabled = true,
linkType = "All"
},
modelId = Guid.NewGuid().ToString(),
modelName = null
};
var columns = new List <Column>();
foreach (var item in columnData.MetaData)
{
var column = new Column
{
caption = $"{tableName} {item.Name}",
sortIndex = -1,
sorting = "None",
expr = new Expr
{
typeName = "ENTATTR",
id = $"{tableName}.{item.Name.ToString()}",
},
ParmObjects = new List <object>()
};
columns.Add(column);
}
simpleQuery.columns = columns;
return(JsonConvert.SerializeObject(simpleQuery));
}
catch (Exception ex)
{
throw ex;
}
}
public RootModel ToRootModel()
{
RootModel model = new RootModel(this.AdvancedFeatures);
foreach (var @enum in this.Enums)
{
if (EnumSchemaModel.TryCreate(this, @enum, out var enumModel))
{
model.AddElement(enumModel);
}
else if (UnionSchemaModel.TryCreate(this, @enum, out var unionModel))
{
model.AddElement(unionModel);
}
}
foreach (var obj in this.Objects)
{
if (TableSchemaModel.TryCreate(this, obj, out var tableModel))
{
model.AddElement(tableModel);
}
else if (ReferenceStructSchemaModel.TryCreate(this, obj, out var refStructModel))
{
model.AddElement(refStructModel);
}
else if (ValueStructSchemaModel.TryCreate(this, obj, out var valueStructModel))
{
model.AddElement(valueStructModel);
}
}
if (this.Services is not null)
{
foreach (var service in this.Services)
{
model.AddElement(new RpcServiceSchemaModel(this, service));
}
}
return(model);
}
/// <summary>
/// Gets the schema model.
/// </summary>
/// <returns>List<TableSchemaModel>.</returns>
public override List <TableSchemaModel> GetSchemaModel()
{
List <TableSchemaModel> tableSchemaModel = new List <TableSchemaModel>();
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
cn.GetSchema("Tables");
var schema = cn.GetSchema("Columns").AsEnumerable().GroupBy(g => g.Field <string>("TABLE_NAME"));
foreach (var item in schema)
{
// var type = item.Select(s => s.Table.Columns[index++].DataType.ToString()).Single();
TableSchemaModel table = new TableSchemaModel();
tableSchemaModel.Add(table);
FillSchemaModel(table, item);
}
}
return(tableSchemaModel);
}
/// <summary>
/// Gets the schema model.
/// </summary>
/// <returns>List<TableSchemaModel>.</returns>
public override List <TableSchemaModel> GetSchemaModel()
{
if (UseExternalQueryEngine())
{
return(GetSchemaModelExternal());
}
DqlDataTypeMap map = new DqlDataTypeMap();
List <TableSchemaModel> tableSchemaModel = new List <TableSchemaModel>();
using (DqlConnection cn = new DqlConnection(ConnectionString))
{
cn.Open();
var schema = cn.GetSchema().AsEnumerable().GroupBy(g => g.Field <string>("name"));
foreach (var item in schema)
{
TableSchemaModel table = new TableSchemaModel();
tableSchemaModel.Add(table);
FillSchemaModel(table, item);
}
}
return(tableSchemaModel);
}
public void LogTableError(TableSchemaModel tableSchema, Exception exception, string connectionName = "default")
{
repository.LogTableError(tableSchema, exception, connectionName);
}
