public OutboundTable GetTableData(string tableName)
{
var result = new OutboundTable();
result.Shape = new Shape();
result.Id = tableName;
result.Shape.Name = tableName;
result.Shape.Attribute = new List <BindingRow>();
result.Shape.Name = tableName;
var query = $"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{tableName}'";
var reader = ExecuteQuery(query);
while (reader.Read())
{
var lenght = reader.GetSqlValue(2);
if (lenght != null)
{
lenght = lenght as string;
}
result.Shape.Attribute.Add(new BindingRow {
Column = $"{reader.GetString(1)} | {lenght} | {reader.GetString(3)}",
Name = reader.GetString(0)
});
}
reader.Close();
SqlConnection.Close();
return(result);
}
public OutboundTable GetTableData(string tableName, List <OutboundTableConnection> foreginKeys)
{
var result = new OutboundTable();
var primaryAutoInc = string.Empty;
var getPrimaryKey = ExecuteQuery($"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{tableName}'and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1");
while (getPrimaryKey.Read())
{
primaryAutoInc = getPrimaryKey.GetString(0);
}
getPrimaryKey.Close();
SqlConnection.Close();
var query = $"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{tableName}'";
var reader = ExecuteQuery(query);
var notNull = "NOT NULL";
var tableData = $"CREATE TABLE IF NOT EXISTS {tableName} (";
var localData = new List <BindingRowModel>();
var i = 0;
while (reader.Read())
{
if (reader.GetString(3) == "NO")
{
notNull = "NOT NULL";
}
else
{
notNull = "";
}
if (reader.GetString(0) == primaryAutoInc)
{
localData.Add(new BindingRowModel {
TableName = reader.GetString(0),
DataType = $"INT AUTO_INCREMENT PRIMARY KEY",
IsNull = notNull
});
}
else if (foreginKeys.Any(x => x.TableName == tableName && x.ConnectionName == reader.GetString(0)))
{
localData.Add(new BindingRowModel {
TableName = reader.GetString(0),
DataType = $"INT AUTO_INCREMENT PRIMARY KEY",
IsNull = notNull
});
}
else if (reader.IsDBNull(2))
{
localData.Add(new BindingRowModel {
TableName = reader.GetString(0),
DataType = $"{DetermineType(reader.GetString(1), reader.IsDBNull(2) ? -1 : reader.GetInt32(2))}",
IsNull = notNull
});
}
}
var lastRow = localData.Count;
localData.ForEach(x => {
i++;
var next = ",";
if (i == lastRow)
{
next = "";
}
if (x.TableName == primaryAutoInc)
{
tableData += $"{x.TableName} {x.DataType}{next}";
}
else
{
tableData += $"{x.TableName} {x.DataType} {x.IsNull}{next}";
}
});
tableData += " );";
result.CreationgString = tableData;
reader.Close();
SqlConnection.Close();
return(result);
}
