private static bool IsSchemaMatch(List <ColumnSchema> tableSchema, List <ColumnSchema> listSchema, string modifyType)
{
ColumnSchemaMatcher schemaMatcher;
// Check if all columns in table are presented in list
foreach (ColumnSchema schema in tableSchema)
{
schemaMatcher = new ColumnSchemaMatcher(schema);
if (!listSchema.Exists(schemaMatcher.SchemaMatch))
{
return(false);
}
}
if (modifyType == "Insert")
{
// Check if all required column in list are presented in table
List <ColumnSchema> requiredColumnInList = listSchema.FindAll(MatchNotNullColumn);
foreach (ColumnSchema schema in requiredColumnInList)
{
schemaMatcher = new ColumnSchemaMatcher(schema);
if (!tableSchema.Exists(schemaMatcher.SchemaMatch))
{
return(false);
}
}
}
return(true);
}
private static XmlElement GenerateUpdateCAML(DataTable tableData, List <ColumnSchema> listSchema, string keyColumnName, string serviceUrl, string listName)
{
keyColumnName = keyColumnName.Trim('[', ']');
List <ColumnSchema> mappingSchemas = new List <ColumnSchema>();
ColumnSchemaMatcher schemaMatcher = null;
ColumnSchema schemaItem = null;
ColumnSchema keyColumnSchemaItem = null;
foreach (DataColumn dc in tableData.Columns)
{
schemaMatcher = new ColumnSchemaMatcher(dc.ColumnName);
schemaItem = listSchema.Find(schemaMatcher.ColumnDisplayNameMatch);
if (schemaItem != null)
{
if (string.Compare(schemaItem.ColumnDisplayName, keyColumnName, true) == 0)
{
keyColumnSchemaItem = schemaItem;
}
else
{
mappingSchemas.Add(schemaItem);
}
}
}
XmlDocument doc = new XmlDocument();
XmlElement batch_element = doc.CreateElement("Batch");
batch_element.SetAttribute("PreCalc", "True");
batch_element.SetAttribute("OnError", "Continue");
StringBuilder batchXml = new StringBuilder();
if (tableData.Rows.Count > 0)
{
if (keyColumnSchemaItem != null)
{
string typeName = keyColumnSchemaItem.DataTypeName;
SPListDataSourceReader spReader = new SPListDataSourceReader(serviceUrl, listName);
XmlNamespaceManager nsMgr = Utility.GetXmlNameSpaceManager();
string camlQueryToken = "<Eq><FieldRef Name=\"{0}\" /><Value Type=\"{1}\">{2}</Value></Eq>";
StringBuilder sbCamlQuery = new StringBuilder();
StringCollection orClauses = new StringCollection();
foreach (DataRow dr in tableData.Rows)
{
object pkColumnValue = dr[keyColumnName];
if (pkColumnValue != null)
{
if (pkColumnValue != DBNull.Value)
{
orClauses.Add(string.Format(camlQueryToken, keyColumnSchemaItem.ColumnInternalName, typeName, pkColumnValue.ToString()));
}
}
}
sbCamlQuery.Append("<Where>");
sbCamlQuery.Append("<And>");
if (orClauses.Count == 1)
{
sbCamlQuery.Append(orClauses[0].ToString());
}
else if (orClauses.Count == 2)
{
sbCamlQuery.Append("<Or>" + orClauses[0].ToString() + orClauses[1].ToString() + "</Or>");
}
else if (orClauses.Count > 2)
{
for (int i = orClauses.Count - 1; i > 0; i--)
{
sbCamlQuery.Append("<Or>");
}
sbCamlQuery.Append(orClauses[0].ToString() + orClauses[1].ToString() + "</Or>");
for (int j = 2; j < orClauses.Count; j++)
{
sbCamlQuery.Append(orClauses[j].ToString() + "</Or>");
}
}
sbCamlQuery.Append("<Neq>");
sbCamlQuery.Append("<FieldRef Name=\"FSObjType\"/>");
sbCamlQuery.Append("<Value Type=\"Lookup\">1</Value>");
sbCamlQuery.Append("</Neq>");
sbCamlQuery.Append("</And>");
sbCamlQuery.Append("</Where>");
XmlNode resultNode = spReader.GetListItems(sbCamlQuery.ToString());
XPathNavigator navigator = resultNode.CreateNavigator();
XPathNodeIterator itemsNavigator = navigator.Select("/rs:data/z:row", nsMgr);
int k = 1;
string fieldValue = "";
while (itemsNavigator.MoveNext())
{
XPathNavigator currentNode = itemsNavigator.Current;
XPathNavigator idNode = currentNode.SelectSingleNode("./@ows_ID");
XPathNavigator keyColumnNode = currentNode.SelectSingleNode("./@ows_" + keyColumnSchemaItem.ColumnInternalName);
if (idNode != null && keyColumnNode != null)
{
string strExpression = string.Empty;
if (keyColumnSchemaItem.DataTypeName == "Text")
{
strExpression = "[" + keyColumnName + "]" + " = '" + keyColumnNode.Value + "'";
}
else
{
strExpression = "[" + keyColumnName + "]" + " = " + keyColumnNode.Value;
}
DataRow[] rows = tableData.Select(strExpression);
if (rows.Length == 1)
{
batchXml.Append("<Method ID=\"" + k + "\" Cmd=\"Update\">");
batchXml.Append("<Field Name=\"ID\">" + idNode.Value + "</Field>");
foreach (ColumnSchema schema in mappingSchemas)
{
object columnValue = rows[0][schema.ColumnDisplayName];
if (columnValue != DBNull.Value)
{
if (schema.DataType == typeof(DateTime))
{
fieldValue = ((DateTime)columnValue).ToString("yyyy-MM-ddTHH:mm:ss.fff+00:00");
}
else if (schema.DataType == typeof(Guid))
{
fieldValue = ((Guid)columnValue).ToString("B");
}
else
{
fieldValue = columnValue.ToString();
}
}
else
{
fieldValue = "";
}
batchXml.Append("<Field Name=\"" + schema.ColumnInternalName + "\">" + Utility.HtmlEncode(fieldValue) + "</Field>");
}
batchXml.Append("</Method>");
k = k + 1;
}
else
{
throw new Exception(keyColumnName + " is not an identity column (or Primary Key) in the table.");
}
}
}
}
batch_element.InnerXml = batchXml.ToString();
}
return(batch_element);
}
private static XmlElement GenerateInsertCAML(DataTable tableData, string folderPath, string listName, List <ColumnSchema> listSchema)
{
List <ColumnSchema> mappingSchemas = new List <ColumnSchema>();
ColumnSchemaMatcher schemaMatcher = null;
ColumnSchema schemaItem = null;
foreach (DataColumn dc in tableData.Columns)
{
schemaMatcher = new ColumnSchemaMatcher(dc.ColumnName);
schemaItem = listSchema.Find(schemaMatcher.ColumnDisplayNameMatch);
if (schemaItem != null)
{
mappingSchemas.Add(schemaItem);
}
}
XmlDocument doc = new XmlDocument();
XmlElement batch_element = doc.CreateElement("Batch");
batch_element.SetAttribute("PreCalc", "True");
batch_element.SetAttribute("RootFolder", Utility.GetFolderUrl(folderPath, listName));
batch_element.SetAttribute("OnError", "Continue");
StringBuilder batchXml = new StringBuilder();
int i = 1;
string fieldValue = "";
foreach (DataRow dr in tableData.Rows)
{
batchXml.Append("<Method ID=\"" + i + "\" Cmd=\"New\">");
batchXml.Append("<Field Name=\"ID\">New</Field>");
foreach (ColumnSchema schema in mappingSchemas)
{
object columnValue = dr[schema.ColumnDisplayName];
if (columnValue != DBNull.Value)
{
if (schema.DataType == typeof(DateTime))
{
fieldValue = ((DateTime)columnValue).ToString("yyyy-MM-ddTHH:mm:ss.fff+00:00");
}
else if (schema.DataType == typeof(Guid))
{
fieldValue = ((Guid)columnValue).ToString("B");
}
else
{
fieldValue = columnValue.ToString();
}
}
else
{
fieldValue = "";
}
batchXml.Append("<Field Name=\"" + schema.ColumnInternalName + "\">" + Utility.HtmlEncode(fieldValue) + "</Field>");
}
batchXml.Append("</Method>");
i = i + 1;
}
batch_element.InnerXml = batchXml.ToString();
return(batch_element);
}
