public static ImportColumnsTreeModel CreateFromOLEDBQuery(string connectionString, string queryText)
{
var result = new ImportColumnsTreeModel();
try
{
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmd = new OleDbCommand(queryText, conn);
var rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
var schema = rdr.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
var name = (string)row["ColumnName"];
var type = (Type)row["DataType"];
if (!result.Columns.Any(c => c.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase)))
{
var col = new ImportColumn(result, name, Table.DataTypeMapping.ContainsKey(type) ? Table.DataTypeMapping[type] : DataType.Automatic);
result.Columns.Add(col);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Could not establish connection to data source", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(null);
}
return(result);
}
public ImportColumn(ImportColumnsTreeModel treeModel, string name, DataType dataType, OleDbType providerType)
{
TreeModel = treeModel;
SourceColumn = name;
Name = name;
ProviderType = providerType;
Import = true;
switch (dataType)
{
case TOMWrapper.DataType.Boolean: DataType = "Boolean"; break;
case TOMWrapper.DataType.DateTime: DataType = "Date/Time"; break;
case TOMWrapper.DataType.Decimal: DataType = "Currency"; break;
case TOMWrapper.DataType.Double: DataType = "Real"; break;
case TOMWrapper.DataType.Int64: DataType = "Integer"; break;
case TOMWrapper.DataType.String: DataType = "Text"; break;
case TOMWrapper.DataType.Binary: DataType = "Binary"; break;
default: DataType = "Text"; break;
}
}
private void btnImportFromQuery_Click(object sender, EventArgs e)
{
currentTreeModel = ImportColumnsTreeModel.CreateFromOLEDBQuery((comboBox1.SelectedItem as ProviderDataSource).ConnectionString, textBox1.Text);
if (currentTreeModel != null)
{
treeViewAdv1.Model = currentTreeModel;
FromQuery = true;
}
UpdateUI();
}
private void button1_Click(object sender, EventArgs e)
{
currentTreeModel = ImportColumnsTreeModel.CreateFromClipboard();
if (currentTreeModel != null)
{
treeViewAdv1.Model = currentTreeModel;
FromQuery = false;
}
UpdateUI();
}
public static ImportColumnsTreeModel CreateFromClipboard()
{
var result = new ImportColumnsTreeModel();
var text = Clipboard.GetText();
try
{
var dataRows = text.Split(new string[] { "\r\n" }, StringSplitOptions.None);
var header = dataRows[0].Split('\t');
var nameIndex = Array.FindIndex(header, s => s.Equals("Name", StringComparison.InvariantCultureIgnoreCase));
var typeIndex = Array.FindIndex(header, s => s.Equals("TypeName", StringComparison.InvariantCultureIgnoreCase));
if (typeIndex == -1)
{
Array.FindIndex(header, s => s.IndexOf("Type", StringComparison.InvariantCultureIgnoreCase) >= 0);
}
for (int i = 1; i < dataRows.Length; i++)
{
var dataCols = dataRows[i].Split('\t');
var importColumn = new ImportColumn(result, dataCols[nameIndex], dataCols[typeIndex]);
result.Columns.Add(importColumn);
}
if (result.Columns.Count == 0)
{
throw new Exception();
}
}
catch (Exception ex)
{
MessageBox.Show("The clipboard does not currently contain any Power Query schema data.", "No schema data in clipboard", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(null);
}
return(result);
}
public ImportColumn(ImportColumnsTreeModel treeModel, string name, string typeName)
{
TreeModel = treeModel;
SourceColumn = name;
Name = name;
Import = true;
switch (typeName.ToLower())
{
case "any.type":
case "text.type":
case "text":
case "string":
case "char":
case "nchar":
case "varchar":
case "nvarchar":
DataType = "Text"; break;
case "number.type":
case "percentage.type":
case "duration.type":
case "number":
case "real":
case "float":
case "single":
case "double":
DataType = "Real"; break;
case "currency.type":
case "currency":
case "decimal":
case "numeric":
DataType = "Currency"; break;
case "int64.type":
case "int":
case "integer":
case "whole":
case "bigint":
case "smallint":
case "int64":
case "int32":
case "long":
DataType = "Integer"; break;
case "datetimezone.type":
case "datetime.type":
case "date.type":
case "time.type":
case "datetime":
case "date":
case "time":
DataType = "Date/Time"; break;
case "logical.type":
case "boolean":
case "bool":
DataType = "Boolean"; break;
default:
DataType = "Text"; break;
}
}