public string GetSql(IdentifierQuoting identifierQuoting, bool indented = true, bool useThreePartName = false)
{
if (SelectAll)
{
return((indented ? "SELECT\n\t*\nFROM\n\t" : "SELECT * FROM ") + GetRef(identifierQuoting, useThreePartName));
}
else
{
var sqlText = "SELECT";
var first = true;
foreach (var col in IncludedColumns)
{
sqlText += (indented ? "\n\t" : "") + (first ? " " : ",");
switch (identifierQuoting)
{
case IdentifierQuoting.None: sqlText += $"{col}"; break;
case IdentifierQuoting.DoubleQuote: sqlText += $"\"{col}\""; break;
case IdentifierQuoting.SingleQuote: sqlText += $"'{col}'"; break;
case IdentifierQuoting.Backtick: sqlText += $"`{col}`"; break;
default: sqlText += $"[{col}]"; break;
}
first = false;
}
sqlText += indented ? "\n" : " ";
sqlText += "FROM";
sqlText += indented ? "\n\t" : " ";
sqlText += GetRef(identifierQuoting, useThreePartName);
return(sqlText);
}
}
public string GetRef(IdentifierQuoting identifierQuoting, bool useThreePartName)
{
switch (identifierQuoting)
{
case IdentifierQuoting.None:
return(useThreePartName ? $"{Database}.{Schema}.{Name}" : $"{Schema}.{Name}");
case IdentifierQuoting.DoubleQuote:
return(useThreePartName ? $"\"{Database}\".\"{Schema}\".\"{Name}\"" : $"\"{Schema}\".\"{Name}\"");
case IdentifierQuoting.SingleQuote:
return(useThreePartName ? $"'{Database}'.'{Schema}'.'{Name}'" : $"'{Schema}'.'{Name}'");
case IdentifierQuoting.Backtick:
return(useThreePartName ? $"`{Database}`.`{Schema}`.`{Name}`" : $"`{Schema}`.`{Name}`");
default:
return(useThreePartName ? $"[{Database}].[{Schema}].[{Name}]" : $"[{Schema}].[{Name}]");
}
}
public abstract DataTable GetSchemaTable(SchemaNode tableOrView, IdentifierQuoting identifierQuoting);
protected abstract DataTable InternalGetSampleData(SchemaNode tableOrView, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting, out bool isError);
public DataTable GetSampleData(SchemaNode tableOrView, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting, out bool isError)
{
var result = InternalGetSampleData(tableOrView, rowLimitClause, identifierQuoting, out isError);
tableOrView.LoadColumnsFromSample(result);
return(result);
}
protected override DataTable InternalGetSampleData(SchemaNode tableOrView, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting, out bool isError)
{
try
{
var sql = GetSampleSql(rowLimitClause, tableOrView.GetRef(identifierQuoting, false), 200);
var adapter = new System.Data.Odbc.OdbcDataAdapter(sql, ProviderString);
adapter.SelectCommand.CommandTimeout = 30;
var result = new DataTable();
adapter.Fill(0, 200, result);
isError = false;
return(result);
}
catch (Exception ex)
{
ErrorTable.Rows[0][0] = ex.Message;
isError = true;
return(ErrorTable);
}
}
public override DataTable GetSchemaTable(SchemaNode tableOrView, IdentifierQuoting identifierQuoting)
{
return(GetSchemaTable(tableOrView.GetSql(identifierQuoting)));
}
public override DataTable GetSchemaTable(SchemaNode tableOrView, IdentifierQuoting identifierQuoting)
{
throw new NotImplementedException();
}
protected override DataTable InternalGetSampleData(SchemaNode tableOrView, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting, out bool isError)
{
throw new NotImplementedException();
}
public override DataTable GetSchemaTable(SchemaNode tableOrView, IdentifierQuoting identifierQuoting)
{
return(GetSchemaTable(tableOrView.GetSql(identifierQuoting, false, UseThreePartName)));
}
protected override DataTable InternalGetSampleData(SchemaNode tableOrView, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting, out bool isError)
{
try
{
var csb = new SC.SqlConnectionStringBuilder(ProviderString);
var adapter = new SC.SqlDataAdapter($"SELECT TOP 200 * FROM {tableOrView.GetRef(identifierQuoting, UseThreePartName)}" + (rowLimitClause == RowLimitClause.Top ? " WITH (NOLOCK)" : ""), csb.ConnectionString);
adapter.SelectCommand.CommandTimeout = 30;
var result = new DataTable();
adapter.Fill(0, 200, result);
isError = false;
return(result);
}
catch (Exception ex)
{
ErrorTable.Rows[0][0] = ex.Message;
isError = true;
return(ErrorTable);
}
}
public static void SetIdentifierQuoting(this Table table, IdentifierQuoting identifierQuoting)
{
table.Partitions[0].DataSource.SetIdentifierQuoting(identifierQuoting);
}
public static void SetIdentifierQuoting(this TOMWrapper.DataSource source, IdentifierQuoting identifierQuoting)
{
source.SetAnnotation(QUOTING, $"{(int)identifierQuoting}");
}
private static void DoImport(Pages.ImportMode importMode, Model model, TypedDataSource source, IEnumerable <SchemaNode> schemaNodes, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting)
{
foreach (var tableSchema in schemaNodes)
{
var newTable = model.AddTable(tableSchema.Name);
if (newTable.Partitions[0] is MPartition)
{
Partition.CreateNew(newTable);
newTable.Partitions[0].Delete();
}
newTable.Partitions[0].Name = tableSchema.Name;
newTable.Partitions[0].Query = tableSchema.GetSql(identifierQuoting, true, source.UseThreePartName);
if (source?.TabularDsName != null && model.DataSources.Contains(source.TabularDsName))
{
newTable.Partitions[0].DataSource = model.DataSources[source.TabularDsName];
}
if (importMode != Pages.ImportMode.UseTempDs && !(source is SqlDataSource))
{
newTable.SetRowLimitClause(rowLimitClause);
newTable.SetIdentifierQuoting(identifierQuoting);
}
var schemaTable = source.GetSchemaTable(tableSchema, identifierQuoting);
foreach (DataRow row in schemaTable.Rows)
{
var sourceColumn = row["ColumnName"].ToString();
var dataType =
schemaTable.Columns.Contains("DataTypeName") ?
row["DataTypeName"].ToString() :
(row["DataType"] as Type).Name;
var col = newTable.AddDataColumn(
sourceColumn, sourceColumn,
null,
TableMetadata.DataTypeMap(dataType)
);
col.SourceProviderType = dataType;
}
newTable.SetTableSchema(tableSchema);
newTable.Select();
if (UIController.Current.TreeModel.Perspective != null)
{
newTable.InPerspective[UIController.Current.TreeModel.Perspective] = true;
}
}
}
private static void DoUpdate(Table table, TypedDataSource source, SchemaNode tableSchema, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting)
{
table.Partitions[0].Name = tableSchema.Name;
table.Partitions[0].Query = tableSchema.GetSql(identifierQuoting, true, source.UseThreePartName);
table.SetTableSchema(tableSchema);
if (!(source is SqlDataSource))
{
table.SetRowLimitClause(rowLimitClause);
table.SetIdentifierQuoting(identifierQuoting);
}
var schemaTable = source.GetSchemaTable(tableSchema, identifierQuoting);
var updatedColumns = new HashSet <TOMWrapper.DataColumn>();
foreach (DataRow row in schemaTable.Rows)
{
var sourceColumn = row["ColumnName"].ToString();
var dataTypeName =
schemaTable.Columns.Contains("DataTypeName") ?
row["DataTypeName"].ToString() :
(row["DataType"] as Type).Name;
var column = table.DataColumns.FirstOrDefault(c => c.SourceColumn.EqualsI(sourceColumn));
if (column == null)
{
column = table.AddDataColumn(sourceColumn, sourceColumn);
}
column.DataType = TableMetadata.DataTypeMap(dataTypeName);
column.SourceProviderType = dataTypeName;
updatedColumns.Add(column);
}
foreach (var col in table.DataColumns.Except(updatedColumns).ToList())
{
col.Delete();
}
}
