public SQLColumn(string name, XmlSchemaSimpleType schemaType, XmlSchemaObjectTable schemaTypes, bool required, bool isAttribute, SQLTable table, string nameSpace)
{
Name = name;
Required = required;
DataType = GetDataType(schemaType, schemaTypes, 0, nameSpace);
IsAttribute = isAttribute;
Table = table;
}
//public SQLColumn(SQLTable table)
//{
// Table = table;
//}
public SQLColumn(string name, SQLDataType dataType, bool required, bool isAttribute, SQLTable table, string nameSpace)
{
Name = name;
DataType = dataType;
Required = required;
IsAttribute = isAttribute;
Table = table;
}
public XmlDocument GenerateBulkLoadSchema(XmlNode CurrentSchemaNode = null, XmlNode CurrentTableNode = null)
{
XmlDocument bulkLoadSchema = new XmlDocument();
bulkLoadSchema.AppendChild(bulkLoadSchema.CreateElement("xsd", "schema", "http://www.w3.org/2001/XMLSchema"));
bulkLoadSchema.DocumentElement.SetAttribute("xmlns:sql", "urn:schemas-microsoft-com:mapping-schema");
bulkLoadSchema.DocumentElement.SetAttribute("attributeFormDefault", "unqualified");
bulkLoadSchema.DocumentElement.SetAttribute("elementFormDefault", "qualified");
bulkLoadSchema.DocumentElement.SetAttribute("targetNamespace", NameSpace);
Dictionary <string, string> tableRelationships = new Dictionary <string, string>();
if (Relationships != null)
{
XmlElement annotation = bulkLoadSchema.CreateElement("xsd", "annotation", "http://www.w3.org/2001/XMLSchema");
XmlElement appinfo = bulkLoadSchema.CreateElement("xsd", "appinfo", "http://www.w3.org/2001/XMLSchema");
annotation.AppendChild(appinfo);
bulkLoadSchema.DocumentElement.AppendChild(annotation);
XmlNodeList keyNodes = Relationships.SelectNodes("//__keys__");
foreach (XmlNode keyNode in keyNodes)
{
foreach (XmlNode childNode in keyNode.ParentNode.ChildNodes)
{
if (childNode.Name != "__keys__")
{
SQLTable thisTable = Tables[childNode.Name];
string keys = thisTable.InheritedKeyString;
XmlElement sqlRelationship = bulkLoadSchema.CreateElement("sql", "relationship", "urn:schemas-microsoft-com:mapping-schema");
sqlRelationship.SetAttribute("name", keyNode.ParentNode.Name + "_" + childNode.Name);
sqlRelationship.SetAttribute("parent", keyNode.ParentNode.Name);
sqlRelationship.SetAttribute("parent-key", keys);
sqlRelationship.SetAttribute("child", childNode.Name);
sqlRelationship.SetAttribute("child-key", keys);
appinfo.AppendChild(sqlRelationship);
tableRelationships.Add(childNode.Name, keyNode.ParentNode.Name + "_" + childNode.Name);
}
}
}
}
AddTablesToBulkLoadSchema(bulkLoadSchema.DocumentElement, TableHierarchy, tableRelationships);
return(bulkLoadSchema);
}
public SQLSchema(string XMLSchemaPath, bool identity, bool includeFileId = false)
{
//Store whether we are including a file id
IncludeFileId = includeFileId;
Identity = identity;
//If required, create a table to hold the names and ids of multiple files
if (includeFileId)
{
SQLDatabase.DropTable("__File");
FileTable = new SQLTable();
FileTable.Name = "__File";
FileTable.Columns.Add(new SQLColumn("FileName", new SQLDataType("string", 255), true, false, FileTable));
FileTable.Schema = this;
FileTable.Create(false);
}
//Load the schema
XmlSchemaSet ss = new XmlSchemaSet();
ss.Add(null, XMLSchemaPath);
//For each schema in the collection - ha, there's only ever one, but how else do you get to the schema collection?
foreach (XmlSchema schema in ss.Schemas())
{
//Get the namespace
NameSpace = schema.TargetNamespace;
//Loop through the top-level elements - only be one of these too
foreach (XmlSchemaElement schemaElement in schema.Elements.Values)
{
//Cast the schema element to a particle
XmlSchemaParticle schemaParticle = (XmlSchemaParticle)schemaElement;
//Point to the root of the table hierarchy
XmlNode node = TableHierarchy;
//Recurse through all of the particles in the schema
TraverseParticle(schemaParticle, schema, node);
}
}
}
private void StoreNamespaceStuff(XmlDocument Document)
{
string tableName = TableNamePrefix + "__XMLNamespaceStuff";
SQLTable table = Tables[Document.DocumentElement.Name];
foreach (XmlAttribute attribute in Document.DocumentElement.Attributes)
{
if (table.Columns[attribute.Name] == null)
{
string sql = "insert into [" + tableName + "] values(";
if (IncludeFileId)
{
sql += FileTable.LastPK.ToString() + ",";
}
sql += "'" + attribute.Name + "',";
sql += "'" + attribute.Value + "'";
sql += ")";
SQLDatabase.Execute(sql);
}
}
}
public SQLTable(string name, SQLTable parent, SQLSchema schema)
{
Name = name;
Parent = parent;
Schema = schema;
}
//public SQLTable(string name, SQLTable parent)//, SQLSchema schema)
//{
// Name = name;
// Parent = parent;
// //Schema = schema;
//}
//public string GetCreateTableSQL()
//{
// string tableName = Name;// Schema.TableNamePrefix + Name;
// string sql = $"create table [{tableName}] (";
// //if (Schema.IncludeFileId && Name != "__File")
// // sql += "[PK_File] int,";
// sql += "[PK_" + Name + "] int,";
// if (It.Has(Parent))
// sql += "[FK_" + Parent.Name + "] int not null,";
// sql += string.Join(",", Columns.Select(x => x.GetCreateColumnSQL()));
// sql += $" constraint {tableName}_PK primary key ([PK_{Name}]))";
// return sql;
//}
//public string GetCreateTableWithIdentitySQL()
//{
// string tableName = Name; // Schema.TableNamePrefix + Name;
// string sql = "create table [" + tableName + "] (";
// //if (Schema.IncludeFileId && Name != "__File")
// // sql += "[PK_File] int,";
// sql += "[PK_" + Name + "] int identity,";
// if (It.Has(Parent))
// {
// sql += "[FK_" + Parent.Name + "] int not null,";
// }
// sql += string.Join(",", Columns.Select(x => x.GetCreateColumnSQL()));
// sql += $" constraint {tableName}_PK primary key ([PK_{Name}]))";
// return sql;
//}
public string GetParentForeignKeyPart(SQLTable parent)
{
return(It.Has(Parent) && It.HasValues(Parent.Columns)
? $"[FK_{Parent.Name}] int not null,"
: string.Empty);
}
public SQLTable(string name, SQLTable parent)
{
Name = name;
Parent = parent;
}
//public string CreateMainIndexSQL()
//{
// string tableName = Schema.TableNamePrefix + Name;
// string sql = "create index [IDX_" + tableName + "] on [" + tableName + "] (";
// if (Schema.IncludeFileId)
// sql += "[PK_File] asc,";
// if (Parent != null)
// sql += "[FK_" + Parent.Name + "] asc,";
// sql += "[PK_" + Name + "] asc)";
// return sql;
//}
public string GetParentIndexPart(SQLTable parent)
{
return(It.Has(Parent) && It.HasValues(Parent.Columns)
? $"[FK_{Parent.Name}] asc,"
: string.Empty);
}
private void TraverseParticle(XmlSchemaParticle particle, XmlSchema schema, XmlNode tableHierarchyParent, SQLTable parentTable = null)
{
//If the particle is an element
if (particle is XmlSchemaElement)
{
//Cast the particle to an element
XmlSchemaElement element = (XmlSchemaElement)particle;
//If the element has a schema type
if (element.SchemaType != null)
{
//Get the schema type
var schemaType = element.SchemaType;
if (schemaType is XmlSchemaSimpleType)
{
parentTable.Columns.Add(new SQLColumn(element.Name, (XmlSchemaSimpleType)schemaType, schema.SchemaTypes, element.MinOccurs > 0, false, parentTable));
}
//If the type is complex, then we'll need a table and we'll need to traverse the particles inside it
else if (schemaType is XmlSchemaComplexType)
{
var table = new SQLTable(element.Name, parentTable, this);
Tables.Add(table);
var complexType = schemaType as XmlSchemaComplexType;
var tableElement = TableHierarchy.CreateElement(table.Name);
tableHierarchyParent.AppendChild(tableElement);
if (complexType.Particle != null)
{
TraverseParticle(complexType.Particle, schema, tableElement, table);
}
else if (complexType.ContentModel != null)
{
if (complexType.ContentModel.Content is XmlSchemaComplexContentExtension)
{
var complexContentExtension = complexType.ContentModel.Content as XmlSchemaComplexContentExtension;
if (complexContentExtension.BaseTypeName.Namespace == NameSpace)
{
//Get the schema type for the schema type name
var contentSchemaType = (XmlSchemaType)schema.SchemaTypes[complexContentExtension.BaseTypeName];
var contentComplexType = (XmlSchemaComplexType)contentSchemaType;
//Check for and process attributes
foreach (var schemaAttribute in contentComplexType.Attributes.OfType <XmlSchemaAttribute>())
{
if (schemaAttribute.SchemaTypeName.Namespace == NameSpace)
{
table.Columns.Add(new SQLColumn(schemaAttribute.Name, schemaAttribute.SchemaType, schema.SchemaTypes, (schemaAttribute.Use == XmlSchemaUse.Required), true, table));
}
else
{
table.Columns.Add(new SQLColumn(schemaAttribute.Name, new SQLDataType(schemaAttribute.SchemaTypeName.Name), (schemaAttribute.Use == XmlSchemaUse.Required), true, table));
}
}
TraverseParticle(contentComplexType.Particle, schema, tableElement, table);
}
}
}
}
else
{
parentTable.Columns.Add(new SQLColumn(element.Name, new SQLDataType("unknown(1)"), element.MinOccurs > 0, false, parentTable));
}
}
//Element has no schemaType
else
{
//If the element has a schema type name in the IA namespace
if (element.SchemaTypeName.Namespace == NameSpace)
{
//Get the schema type for the schema type name
XmlSchemaType schemaType = (XmlSchemaType)schema.SchemaTypes[element.SchemaTypeName];
//If the type is complex, then we'll need a table and we'll need to traverse the particles inside it
if (schemaType is XmlSchemaComplexType)
{
SQLTable table = new SQLTable(element.Name, parentTable, this);
Tables.Add(table);
XmlSchemaComplexType complexType = (XmlSchemaComplexType)schemaType;
XmlElement tableElement = TableHierarchy.CreateElement(table.Name);
tableHierarchyParent.AppendChild(tableElement);
//Check for and process attributes
foreach (XmlSchemaAttribute schemaAttribute in complexType.Attributes)
{
if (schemaAttribute.SchemaTypeName.Namespace == NameSpace)
{
table.Columns.Add(new SQLColumn(schemaAttribute.Name, (XmlSchemaSimpleType)schemaAttribute.SchemaType, schema.SchemaTypes, (schemaAttribute.Use == XmlSchemaUse.Required), true, table));
}
else
{
table.Columns.Add(new SQLColumn(schemaAttribute.Name, new SQLDataType(schemaAttribute.SchemaTypeName.Name), (schemaAttribute.Use == XmlSchemaUse.Required), true, table));
}
}
TraverseParticle(complexType.Particle, schema, tableElement, table);
}
else if (schemaType is XmlSchemaSimpleType)
{
parentTable.Columns.Add(new SQLColumn(element.Name, (XmlSchemaSimpleType)schemaType, schema.SchemaTypes, element.MinOccurs > 0, false, parentTable));
}
else
{
parentTable.Columns.Add(new SQLColumn(element.Name, new SQLDataType("unknown(2)"), element.MinOccurs > 0, false, parentTable));
}
}
//A really simple type!
else
{
parentTable.Columns.Add(new SQLColumn(element.Name, new SQLDataType(element.SchemaTypeName.Name), element.MinOccurs > 0, false, parentTable));
}
}
}
else if (particle is XmlSchemaGroupBase)
{
XmlSchemaGroupBase baseParticle = particle as XmlSchemaGroupBase;
foreach (XmlSchemaParticle subParticle in baseParticle.Items)
{
TraverseParticle(subParticle, schema, tableHierarchyParent, parentTable);
}
}
}
public SQLColumn(SQLTable table)
{
Table = table;
}