/// <summary>
/// Returns schema object which contains information about structure of the database. Schema returned start at specified root element (if it applicable).
/// </summary>
public StiDataSchema RetrieveSchema()
{
if (string.IsNullOrEmpty(ConnectionString))
{
return(null);
}
var schema = new StiDataSchema(StiConnectionIdent.ODataDataSource);
try
{
using (var client = GetDefaultWebClient())
{
var metadata = client.DownloadString(StiUrl.Combine(Address, "$metadata"));
using (var reader = new StringReader(metadata))
using (var xmlReader = XmlReader.Create(reader))
{
var root = XElement.Load(xmlReader);
var edmx = root.GetNamespaceOfPrefix("edmx");
var edm = root.Element(edmx + "DataServices").Elements().First().GetDefaultNamespace();
var elementSchema = root.Element(edmx + "DataServices").Elements().FirstOrDefault();
var namespaceStr = elementSchema.Attribute("Namespace") != null ? (string)elementSchema.Attribute("Namespace") : null;
var types = new Hashtable();
var hash = new Hashtable();
#region Parse Types
foreach (var entityType in elementSchema.Elements().Where(e => e.Name.LocalName == "EntityType" || e.Name.LocalName == "ComplexType"))
{
try
{
var name = (string)entityType.Attribute("Name");
var baseType = entityType.Attribute("BaseType") != null ? (string)entityType.Attribute("BaseType") : null;
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
var properties = entityType.Elements(edm + "Property");
var table = new StiDataTableSchema(name, name);
if (baseType != null)
{
var str = baseType.Replace(namespaceStr + ".", "");
hash[str] = table;
}
foreach (var property in properties)
{
try
{
var propertyName = (string)property.Attribute("Name");
if (string.IsNullOrWhiteSpace(propertyName))
{
continue;
}
var propertyNullable = property.Attribute("Nullable") != null && property.Attribute("Nullable").Value == "true";
var propertyType = (string)property.Attribute("Type");
var columnType = GetNetType(propertyType);
if (propertyNullable)
{
columnType = typeof(Nullable <>).MakeGenericType(columnType);
}
var column = new StiDataColumnSchema(propertyName, columnType);
table.Columns.Add(column);
}
catch
{
}
}
types[namespaceStr + "." + table.Name] = table;
}
catch
{
}
foreach (string tableName in hash.Keys)
{
var table = hash[tableName] as StiDataTableSchema;
var baseTable = schema.Tables.FirstOrDefault(t => t.Name == tableName);
if (baseTable == null)
{
continue;
}
foreach (var column in baseTable.Columns)
{
if (table.Columns.Any(c => c.Name == column.Name))
{
continue;
}
table.Columns.Add(column);
}
}
}
#endregion
#region Parse Containers
foreach (var entityCont in elementSchema.Elements().Where(e => e.Name.LocalName == "EntityContainer"))
{
foreach (var entitySet in entityCont.Elements().Where(e => e.Name.LocalName == "EntitySet"))
{
try
{
var name = (string)entitySet.Attribute("Name");
var type = (string)entitySet.Attribute("EntityType");
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
var table = new StiDataTableSchema(name, name);
var columnsTable = types[type] as StiDataTableSchema;
if (columnsTable != null)
{
table.Columns.AddRange(columnsTable.Columns);
}
schema.Tables.Add(table);
}
catch
{
}
foreach (string tableName in hash.Keys)
{
var table = hash[tableName] as StiDataTableSchema;
var baseTable = schema.Tables.FirstOrDefault(t => t.Name == tableName);
if (baseTable == null)
{
continue;
}
foreach (var column in baseTable.Columns)
{
if (table.Columns.Any(c => c.Name == column.Name))
{
continue;
}
table.Columns.Add(column);
}
}
}
}
#endregion
}
}
return(schema);
}
catch
{
return(null);
}
}
public void FillDataTable(DataTable table, string query)
{
if (string.IsNullOrEmpty(this.ConnectionString))
{
return;
}
var currentCulture = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US", false);
using (var client = GetDefaultWebClient())
{
var url = StiUrl.Combine(this.Address, query);
var metadata = client.DownloadString(url);
#region JSON
object odata = null;
try
{
odata = JsonConvert.DeserializeObject(metadata);
}
catch
{
}
if (odata != null && odata is JObject)
{
JArray values = null;
var jObject = odata as JObject;
if (jObject != null)
{
foreach (var child in jObject.Children())
{
var jProperty = child as JProperty;
if (jProperty != null && jProperty.Name == "value" && jProperty.Value is JArray)
{
values = jProperty.Value as JArray;
}
}
}
if (values != null)
{
foreach (JObject value in values.ChildrenTokens)
{
var row = table.NewRow();
foreach (JProperty columnObjValue in value.ChildrenTokens)
{
try
{
var columnName = columnObjValue.Name;
var columnValue = columnObjValue.Value;
var currentColumn = table.Columns[columnName];
if (currentColumn != null)
{
var currentValue = StiConvert.ChangeType(columnValue, currentColumn.DataType);
row[columnName] = currentValue ?? DBNull.Value;
}
}
catch
{
}
}
table.Rows.Add(row);
}
}
}
#endregion
#region XML
else
{
using (var reader = new StringReader(metadata))
using (var xmlReader = XmlReader.Create(reader))
{
var root = XElement.Load(xmlReader);
var title = root.Elements().FirstOrDefault(e => e.Name.LocalName == "title");
if (title != null)
{
table.TableName = title.Value;
}
foreach (var entry in root.Elements().Where(e => e.Name.LocalName == "entry"))
{
var elementContent = entry.Elements().FirstOrDefault(e => e.Name.LocalName == "content");
if (elementContent == null)
{
continue;
}
var elementProperties = elementContent.Elements().FirstOrDefault(e => e.Name.LocalName.EndsWith("properties"));
if (elementProperties == null)
{
continue;
}
var row = table.NewRow();
#region Name
try
{
var elementTitle = entry.Elements().FirstOrDefault(e => e.Name.LocalName == "title");
if (elementTitle != null && table.Columns["Name"] != null)
{
row["Name"] = elementTitle.Value;
}
}
catch
{
}
#endregion
#region Description
try
{
var elementSummary = entry.Elements().FirstOrDefault(e => e.Name.LocalName == "summary");
if (elementSummary != null && table.Columns["Description"] != null)
{
row["Description"] = elementSummary.Value;
}
}
catch
{
}
#endregion
foreach (var elementProperty in elementProperties.Elements())
{
try
{
var columnName = elementProperty.Name.LocalName.Replace("d:", "");
var columnValue = elementProperty.Value;
var currentColumn = table.Columns[columnName];
if (currentColumn != null)
{
var value = StiConvert.ChangeType(columnValue, currentColumn.DataType);
row[columnName] = value ?? DBNull.Value;
}
}
catch
{
}
}
table.Rows.Add(row);
}
}
}
#endregion
}
}
catch
{
}
finally
{
Thread.CurrentThread.CurrentCulture = currentCulture;
}
}