private void CreateColumns(System.Data.Common.DbDataReader reader, List <AltaxoColumnMapping> mapping)
{
mapping.Clear();
var schemaTable = reader.GetSchemaTable();
int currentPosition = -1;
foreach (System.Data.DataRow row in schemaTable.Rows)
{
++currentPosition;
var ct = (Type)row["DataType"];
var axoColType = GetAltaxoColumnType(ct);
var axoColName = (string)row["ColumnName"];
var axoColumn = _table.DataColumns.EnsureExistence(axoColName, axoColType, Data.ColumnKind.V, 0);
mapping.Add(new AltaxoColumnMapping()
{
AltaxoColumn = axoColumn, AltaxoColumnItemSetter = GetColumnItemSetter(ct)
});
}
// clear all mapped columns
foreach (var item in mapping)
{
item.AltaxoColumn.Clear();
}
}
public static IEnumerable <T> FromDataReader <T>(this IEnumerable <T> list, System.Data.Common.DbDataReader dr)
{
//Instance reflec object from Reflection class coded above
Reflection reflec = new Reflection();
//Declare one "instance" object of Object type and an object list
Object instance;
List <Object> lstObj = new List <Object>();
//dataReader loop
while (dr.Read())
{
//Create an instance of the object needed.
//The instance is created by obtaining the object type T of the object
//list, which is the object that calls the extension method
//Type T is inferred and is instantiated
instance = Activator.CreateInstance(list.GetType().GetGenericArguments()[0]);
// Loop all the fields of each row of dataReader, and through the object
// reflector (first step method) fill the object instance with the datareader values
foreach (DataRow drow in dr.GetSchemaTable().Rows)
{
reflec.FillObjectWithProperty(ref instance,
drow.ItemArray[0].ToString(), dr[drow.ItemArray[0].ToString()]);
}
//Add object instance to list
lstObj.Add(instance);
}
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
List <T> lstResult = new List <T>();
foreach (Object item in lstObj)
{
lstResult.Add((T)Convert.ChangeType(item, typeof(T)));
}
return(lstResult);
}
private DataTable ObterTabela(System.Data.Common.DbDataReader reader)
{
//while (reader.NextResult())
//{
//}
DataTable tbEsquema = reader.GetSchemaTable();
DataTable tbRetorno = new DataTable();
foreach (DataRow r in tbEsquema.Rows)
{
if (!tbRetorno.Columns.Contains(r["ColumnName"].ToString()))
{
DataColumn col = new DataColumn()
{
ColumnName = r["ColumnName"].ToString(),
Unique = Convert.ToBoolean(r["IsUnique"]),
AllowDBNull = Convert.ToBoolean(r["AllowDBNull"])
//ReadOnly = false //Convert.ToBoolean(r["IsReadOnly"])
};
tbRetorno.Columns.Add(col);
}
else
{
/* break point aqui quando der louca na montagem da estrutura da tabela dinâmica */
}
}
while (reader.Read())
{
DataRow novaLinha = tbRetorno.NewRow();
for (int i = 0; i < tbRetorno.Columns.Count; i++)
{
novaLinha[i] = reader.GetValue(i);
}
tbRetorno.Rows.Add(novaLinha);
}
return(tbRetorno);
}
public List <Column> getColumns()
{
//System.Data.Common.DbDataAdapter dataAdapter= dataUtility.GetDataAdapter(FormatSql(sqlInfo.sql),dataUtility.con);
dataUtility.con.Open();
System.Data.Common.DbCommand cmd = new System.Data.SQLite.SQLiteCommand(FormatSql(sqlInfo.sql), (System.Data.SQLite.SQLiteConnection)dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
List <Column> columns = new List <Column>();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
Column column = new Column();
column.name = dt.Rows[i]["BaseColumnName"].ToString();
column.asName = dt.Rows[i]["ColumnName"].ToString();
column.tableName = dt.Rows[i]["BaseTableName"].ToString();
column.returnType = dt.Rows[i]["DataType"].ToString();
columns.Add(column);
}
dataUtility.con.Close();
return(columns);
}
/// <summary>
/// Generate CSV formatted output for the given reader.
/// </summary>
public string Generate(System.Data.Common.DbDataReader reader)
{
var builder = new StringBuilder();
var schema = reader.GetSchemaTable();
var colcount = reader.FieldCount;
var nullable = new bool[colcount];
var datatype = new Type[colcount];
var typename = new string[colcount];
for (int c = 0; c < colcount; c++)
{
nullable[c] = true;
datatype[c] = reader.GetFieldType(c);
typename[c] = reader.GetDataTypeName(c);
if (c == 0)
{
if (this.Settings.AddLineNumbers)
{
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append("Line");
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(this.Settings.FieldSeparator);
}
}
else
{
builder.Append(this.Settings.FieldSeparator);
}
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(reader.GetName(c));
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
}
builder.Append(this.Settings.LineSeparator);
var lineNumber = 0;
while (reader.Read())
{
lineNumber++;
for (int c = 0; c < colcount; c++)
{
if (c == 0)
{
if (this.Settings.AddLineNumbers)
{
builder.Append(lineNumber);
builder.Append(this.Settings.FieldSeparator);
}
}
else
{
builder.Append(this.Settings.FieldSeparator);
}
if (nullable[c] && reader.IsDBNull(c))
{
}
else
{
if (datatype[c] == typeof(String))
{
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(ToCsvableString(reader.GetString(c)));
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
}
else if (datatype[c] == typeof(DateTime))
{
builder.Append(reader.GetDateTime(c).ToString(this.Settings.DateTimeFormat, this.Settings.FormatProvider));
}
else if (datatype[c] == typeof(Boolean))
{
builder.Append(reader.GetBoolean(c) ? this.Settings.BooleanTrue : this.Settings.BooleanFalse);
}
else
{
builder.AppendFormat(this.Settings.FormatProvider, "{0}", reader.GetValue(c));
}
}
}
builder.Append(this.Settings.LineSeparator);
}
return(builder.ToString());
}
public List <DB.Coding.Field> GetFields(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement)
{
List <DB.Coding.Field> fields = new List <DB.Coding.Field>();
string selectFiledsSql = string.Format("select {0} from {1} where 1>2", selectStatement.selectClause, selectStatement.fromClause);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(selectFiledsSql), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
Regex nameReg = new Regex(@"^[_0-9a-zA-Z]+$");
for (int i = 0; i < dt.Rows.Count; i++)
{
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
//查看名称是否合法,不合法则重命名.
if (!nameReg.IsMatch(field.name))
{
field.name = "_column" + i.ToString();
}
if (!nameReg.IsMatch(field.structFieldName))
{
field.structFieldName = "_column" + i.ToString();
}
field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
if (dataUtility.dbType == DB.DBType.Sqlite)
{
field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
}
else
{
field.sqlType = reader.GetDataTypeName(i);
}
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
field.localType = dt.Rows[i]["DataType"].ToString();
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
//field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
//field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
//如果没有相对应的数据库类型
if (!string.IsNullOrEmpty(field.sqlType))
{
if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
{
field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
}
else
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
}
else
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
fields.Add(field);
}
reader.Close();
return(fields);
}
public List <SqlVarParameter> GetParameters(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement, ref List <SqlVarParameter> allSqlVarParameters)
{
List <SqlVarParameter> sqlVarParameters = ParseVarName(selectStatement.sqlWithOutSubSelect);
if (sqlVarParameters.Count > 0)
{
string sqlParametersSelect = "select ";
for (int i = 0; i < sqlVarParameters.Count; i++)
{
if (i != 0)
{
sqlParametersSelect += ",";
}
sqlParametersSelect += sqlVarParameters[i].fullName;
}
sqlParametersSelect += " from " + selectStatement.fromClause + " where 1>2 ";
Regex fromReg = new Regex(@"\s+from\s+", RegexOptions.IgnoreCase);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(sqlParametersSelect), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlVarParameter parameter = sqlVarParameters[i];
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
if (dataUtility.dbType == DB.DBType.Sqlite)
{
field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
}
else
{
field.sqlType = reader.GetDataTypeName(i);
}
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
field.localType = dt.Rows[i]["DataType"].ToString();
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
//field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
//field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
if (!string.IsNullOrEmpty(field.sqlType))
{
if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
{
field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
}
else
{
field.simpleType = "String";
}
}
else
{
field.simpleType = "";
}
parameter.field = field;
allSqlVarParameters.Add(parameter);
}
reader.Close();
}
//分析子查询里的参数
for (int i = 0; i < selectStatement.selects.Count; i++)
{
GetParameters(dataUtility, selectStatement.selects[i], ref allSqlVarParameters);
}
return(sqlVarParameters);
}
public override void Exec()
{
try
{
UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer;
UIHierarchyItem hierItem = (UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0);
ProjectItem projItem = (ProjectItem)hierItem.Object;
DsvColumnResult results = null;
string sFileName = ((ProjectItem)hierItem.Object).Name.ToLower();
if (sFileName.EndsWith(".bim"))
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox = TabularHelpers.GetTabularSandboxFromBimFile(this, true);
#if DENALI || SQL2014
DataSourceViewCollection dsvs = sandbox.Database.DataSourceViews;
foreach (DataSourceView o in dsvs)
{
results = DsvHelpers.IterateDsvColumns(o);
}
#else
if (sandbox.IsTabularMetadata)
{
Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(sandbox);
results = new DsvColumnResult(null);
TabularHelpers.EnsureDataSourceCredentials(sandbox);
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingTable table in sandbox.Tables)
{
if (table.IsCalculated || table.IsPushedData)
{
continue;
}
if (table.IsStructuredDataSource)
{
MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
return;
}
//new 1200 models don't appear to have an equivalent of the DSV where the list of columns from the SQL query are cached, so we will have to get the columns from executing (schema only) the SQL query
var conn = ((Microsoft.AnalysisServices.BackEnd.RelationalDataStorage)((util.GetDataSourceConnection(util.GetDataSourceID(table.Id), sandbox)))).DataSourceConnection;
conn.Open();
System.Data.Common.DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sandbox.GetSourceQueryDefinition(table.Id);
cmd.CommandTimeout = 0;
cmd.Prepare();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable tbl = reader.GetSchemaTable();
for (int i = 0; i < tbl.Rows.Count; i++)
{
string sColumnName = Convert.ToString(tbl.Rows[i]["ColumnName"]);
Type oDataType = (Type)tbl.Rows[i]["DataType"];
bool bFound = false;
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn col in table.Columns)
{
if (col.IsCalculated || col.IsRowNumber)
{
continue;
}
if (sColumnName == col.DBColumnName)
{
bFound = true;
break;
}
}
if (!bFound)
{
DataTable t = new DataTable(table.Name);
DataColumn c = t.Columns.Add(sColumnName, oDataType);
results.UnusedColumns.Add("[" + t.TableName + "].[" + sColumnName + "]", new UnusedColumn(c, null));
}
}
}
}
else //AMO Tabular
{
DataSourceViewCollection dsvs = sandbox.AMOServer.Databases[sandbox.DatabaseID].DataSourceViews;
foreach (DataSourceView o in dsvs)
{
results = DsvHelpers.IterateDsvColumns(o);
}
}
#endif
}
else
{
DataSourceView dsv = (DataSourceView)projItem.Object;
results = DsvHelpers.IterateDsvColumns(dsv);
}
if (results == null || results.UnusedColumns == null || results.UnusedColumns.Count == 0)
{
MessageBox.Show("There are no unused columns.", "BIDS Helper - Unused Columns Report");
return;
}
ReportViewerForm frm = new ReportViewerForm();
frm.ReportBindingSource.DataSource = results.UnusedColumns.Values;
frm.Report = "SSAS.UnusedColumns.rdlc";
Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
reportDataSource1.Name = "BIDSHelper_UnusedColumn";
reportDataSource1.Value = frm.ReportBindingSource;
frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "SSAS.UnusedColumns.rdlc";
frm.Caption = "Unused Columns Report";
frm.WindowState = FormWindowState.Maximized;
frm.Show();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}