public string InsertSQL(List <string> Values)
{
//Start building the string with the insert SQL
string tableName = Schema.TableNamePrefix + Name;
string sql = "insert into [" + tableName + "] values(";
//If not loading the __File table itself and we're using file Ids then add the file Id
if (Schema.IncludeFileId && Name != "__File")
{
sql += (Schema.FileTable.LastPK).ToString() + ",";
}
//Add the primary key field value to the insert SQL and the node
sql += (++LastPK).ToString() + ",";
//Add data for each column
for (int i = 0; i < Columns.Count; i++)
{
SQLColumn column = Columns[i];
string value = Values[i];
if (value != null)
{
switch (column.DataType.Name.ToLower())
{
case "string":
sql += "'" + value.Replace("'", "''") + "',";
break;
case "date":
case "datetime":
DateTime dateTime = XmlConvert.ToDateTime(value);
sql += "#" + dateTime.ToString("yyyy-MM-dd HH:mm:ss") + "#,";
break;
default:
sql += value + ",";
break;
}
}
else
{
sql += "null,";
}
}
//Drop the trailing comma and add a closing bracket to complete the sql statement
sql = sql.Substring(0, sql.Length - 1) + ")";
return(sql);
}
public XmlElement ConvertColumnToElement(SQLColumn column, XmlNode currentSchemaNode)
{
var columnElement = currentSchemaNode.OwnerDocument.CreateElement("xsd", "element", "http://www.w3.org/2001/XMLSchema");
columnElement.SetAttribute("name", column.Name);
columnElement.SetAttribute("type", "xsd:" + column.DataType.Name);
var sqlTypeAttribute = currentSchemaNode.OwnerDocument.CreateAttribute("sql", "datatype", "urn:schemas-microsoft-com:mapping-schema");
sqlTypeAttribute.Value = column.SQLDeclaration;
columnElement.Attributes.Append(sqlTypeAttribute);
if (!column.Required)
{
columnElement.SetAttribute("minOccurs", "0");
}
return(columnElement);
}