public DataTable ParseData(string xmlInputData, ref List <string> csvRows,
ref List <string> validationErrors, ref List <string> validationWarnings)
{
DataTable dataTable = new DataTable("SRERequest");
if (string.IsNullOrEmpty(xmlInputData))
{
return(dataTable);
}
ValidateXML(xmlInputData);
validationErrors = _XMLValidationErrors;
validationWarnings = _XMLValidationWarnings;
if (validationErrors.Count > 0)
{
return(dataTable);
}
try
{
MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(xmlInputData));
XPathDocument docNav = new XPathDocument(ms);
XPathNavigator nav = docNav.CreateNavigator();
XPathNodeIterator nodes = nav.Select("/SRERequest/Rows/Row");
int rowCounter = 0;
while (nodes.MoveNext())
{
#region Attribute Traverse
XPathNodeIterator attributeNodes = nodes.Current.SelectDescendants(XPathNodeType.Element, false);
List <object> oneRow = new List <object>();
foreach (XPathNavigator attributeNode in attributeNodes)
{
if (!attributeNode.LocalName.Equals("Attribute", StringComparison.OrdinalIgnoreCase))
{
continue;
}
XPathNodeIterator nodeAVPairs = attributeNode.SelectDescendants(XPathNodeType.Element, false);
string columnName = string.Empty;
foreach (XPathNavigator nodeAVPair in nodeAVPairs)
{
if (nodeAVPair.LocalName.Equals("Name", StringComparison.OrdinalIgnoreCase))
{
columnName = nodeAVPair.Value;
}
else if (nodeAVPair.LocalName.Equals("Value", StringComparison.OrdinalIgnoreCase))
{
oneRow.Add(nodeAVPair.Value);
}
}
if (rowCounter == 0)
{
dataTable.Columns.Add(columnName);
}
}
#endregion Attribute Traverse
#region CSVRows
string csvRow = string.Empty;
if (rowCounter == 0)
{
foreach (DataColumn column in dataTable.Columns)
{
csvRow += "\"" + column.ColumnName + "\",";
}
csvRows.Add(csvRow);
csvRow = string.Empty;
}
foreach (object cell in oneRow)
{
csvRow += "\"" + cell.ToString() + "\",";
}
csvRows.Add(csvRow);
#endregion CSVRows
dataTable.Rows.Add(oneRow.ToArray());
rowCounter++;
}
}
catch (Exception ex)
{
string errorId = Guid.NewGuid().ToString();
string errorMessage = errorId + ex.ToString() + (ex.InnerException == null ? string.Empty : ex.InnerException.Message);
DataSource.TraceError(errorMessage);
validationErrors.Add(string.Format(__validationErrorMsg, errorId));
}
return(dataTable);
}
protected override void Execute(CodeActivityContext context)
{
DataSource dataSource = null;
Job job = context.GetValue(this.Job);
if (job != null)
{
dataSource = job.DataSource;
}
else
{
WorkerData data = context.GetValue(this.Data);
data.ThrowErrorIfNull(this.DisplayName);
dataSource = data.Job.DataSource;
}
bool createTable = context.GetValue(this.CreateTable);
DataTable table = context.GetValue(this.Table);
string connectionStringKeyName = context.GetValue(this.ConnectionStringKeyName);
string tableName = context.GetValue(this.TableName);
string specificColumnTypes = context.GetValue(this.SpecificColumnTypes);
int timeOut = context.GetValue(this.TimeOut);
if (timeOut == 0)
{
timeOut = 5;//default
}
int batchSize = context.GetValue(this.BatchSize);
if (batchSize == 0)
{
batchSize = 5000;//default
}
IdpeKey connectionStringKey = null;
if (string.IsNullOrEmpty(connectionStringKeyName))
{
//default is repository database
connectionStringKey = new IdpeKey();
connectionStringKey.Name = "cs";
connectionStringKey.Type = (int)Information.EyediaCoreConfigurationSection.Database.DatabaseType.GetSreType();
connectionStringKey.Value = ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ToString();
}
else
{
connectionStringKey = dataSource.Keys.GetKey(connectionStringKeyName);
if (connectionStringKey == null)
{
throw new Exception(string.Format("Can not load data table, the connection string was null! Connection string key name was '{0}'"
, connectionStringKeyName));
}
}
try
{
#region Bulk Insert
bool keepNulls = true;
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
if (keepNulls)
{
options = options |= SqlCeBulkCopyOptions.KeepNulls;
}
DatabaseTypes databaseType = connectionStringKey.GetDatabaseType();
if (databaseType == DatabaseTypes.SqlServer)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringKey.Value))
{
bulkCopy.BulkCopyTimeout = timeOut * 60;
bulkCopy.BatchSize = batchSize;
for (int c = 0; c < table.Columns.Count; c++)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(table.Columns[c].ColumnName, table.Columns[c].ColumnName));
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(table);
}
}
else
{
if (createTable)
{
SqlCeTableCreator.Create(table, new System.Data.SqlServerCe.SqlCeConnection(connectionStringKey.Value), tableName, specificColumnTypes);
}
using (SqlCeBulkCopy bulkCopy = new SqlCeBulkCopy(connectionStringKey.Value, options))
{
bulkCopy.BulkCopyTimeout = timeOut * 60;
bulkCopy.BatchSize = batchSize;
for (int c = 0; c < table.Columns.Count; c++)
{
bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(table.Columns[c].ColumnName, table.Columns[c].ColumnName));
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(table);
}
}
#endregion Bulk Insert
}
catch (Exception ex)
{
context.SetValue(ErrorMessage, ex.Message);
}
}
public SreXmlToDataTable(DataSource dataSource)
: base(dataSource)
{
_XMLValidationErrors = new List <string>();
_XMLValidationWarnings = new List <string>();
}