public ColumnTreeNode(ProjectSchemaTreeNode parent, ColumnMapping columnMapping)
: base(parent)
{
this.ColumnMapping = columnMapping;
Initialize();
}
public void Should_replace_pascal_cased_column()
{
var mapping = new TestMapping();
var columnMapping = new ColumnMapping(typeof(Customer).GetProperty("DateOfBirth"));
mapping.Mappings.Add(columnMapping);
modification.ApplyTo(mapping);
Assert.That(columnMapping.Attributes["Name"], Is.EqualTo("Date_Of_Birth"));
}
protected IdMapping(AttributeStore store, ColumnMapping columnMapping)
: base(store)
{
_attributes = new AttributeStore<IdMapping>(store);
_columns = new List<ColumnMapping>();
if(columnMapping != null)
AddIdColumn(columnMapping);
}
public void Should_not_replace_column_with_explict_name()
{
var mapping = new TestMapping();
var columnMapping = new ColumnMapping(typeof(Customer).GetProperty("DateOfBirth"));
columnMapping.Named("Foo");
mapping.Mappings.Add(columnMapping);
modification.ApplyTo(mapping);
Assert.That(columnMapping.Attributes["Name"], Is.EqualTo("Foo"));
}
public ColumnMapping CreateColumnMapping(TableMapping table)
{
string random = this.Random.Next().ToString();
string columnName = "Column{0}".FormatString(random);
string fieldName = "Field{0}".FormatString(random);
DataTypeMapping dataType = table.ContainingProject.DataTypeMappings.Random(this.Random);
bool nullable = (dataType.Nullable ? this.Random.NextBool() : false);
ColumnMapping column = new ColumnMapping() {
ColumnName = columnName,
DatabaseDataType = dataType.DatabaseDataType,
DataType = dataType.ApplicationDataType,
FieldName = fieldName,
Nullable = nullable,
NullableInDatabase = nullable
};
return column;
}
private void DoAddNewRow(object sender, RoutedEventArgs e)
{
var mapping = new ColumnMapping();
_mappings.Add(mapping);
mappingsGrid.SelectedItem = mapping;
mappingsGrid.CurrentColumn = mappingsGrid.Columns[0];
mappingsGrid.UpdateLayout();
// Denis: using both (non)dispatchered ways does not help
// there's some sort of issue with scrollbar as it does not scroll properly
// user needs to move scrollbar to the bottom in order it to work properly next times
//mappingsGrid.Dispatcher.BeginInvoke(() =>
//{
mappingsGrid.Focus();
mappingsGrid.ScrollIntoView(mapping, mappingsGrid.Columns[0]);
mappingsGrid.BeginEdit();
//});
}
public CopyBehavior(XmlElement configuration)
: base(configuration)
{
targetProjectName = GetParameter("TargetProject");
targetView = GetParameter("TargetView");
targetViewType = GetViewType(targetView);
targetFind = GetParameter("TargetFind");
sourceProjectName = GetParameter("SourceProject");
sourceView = GetParameter("SourceView");
sourceViewType = GetViewType(sourceView);
sourceFind = GetParameter("SourceFind");
columnMappings = new List<ColumnMapping>();
foreach (XmlElement cEl in configuration.GetElementsByTagName("ColumnMapping"))
{
ColumnMapping columnMapping = new ColumnMapping();
columnMapping.SourceColumn = GetColumnDefinition(cEl, "Source");
columnMapping.TargetColumn = GetColumnDefinition(cEl, "Target");
columnMappings.Add(columnMapping);
}
title = "CopyBehavior: " + configuration.InnerText;
}
public static void GetConditionsByPrimaryKeyString(StringBuilder sbCodes, ColumnMapping.ColumnInfos colInfos, StringBuilder sbPrimaryKeyValues)
{
StringBuilder sbMethodContent = new StringBuilder();
sbMethodContent.AppendLine("\t\t\tstring[] primaryKeys = primaryKeyString.Split('|');");
int index = 0;
foreach (ColumnMapping.ColumnInfo ci in colInfos)
{
if (ci.IsPrimaryKey)
{
string columnType = ColumnMapping.GetColumnType(colInfos, ci.ColumnName);
sbMethodContent.Append("\t\t\t").AppendLine(GetFieldDefine(columnType, ci.ColumnName, index));
index++;
}
}
sbMethodContent.AppendFormat("\t\t\treturn GetConditionsByPrimaryKey({0});", sbPrimaryKeyValues.ToString());
sbCodes.AppendLine("\t\t///<summary>");
sbCodes.AppendLine("\t\t///Get conditions by primary key string.");
sbCodes.AppendLine("\t\t///</summary>");
sbCodes.AppendLine(string.Format("\t\tpublic static ParameterCollection GetConditionsByPrimaryKeyString({0})", "string primaryKeyString"));
sbCodes.AppendLine("\t\t{");
sbCodes.AppendLine(sbMethodContent.ToString());
sbCodes.AppendLine("\t\t}");
}
internal static string SqlDecl(string columnName, ColumnMapping columnMapping)
{
string decl = "\"" + columnName + "\" " + columnMapping.Metadata.DeclaredType + " ";
if (columnMapping.Metadata.IsPrimaryKeyPart && columnMapping.Metadata.IsAutoIncrement)
{
decl += "PRIMARY KEY AUTOINCREMENT NOT NULL ";
}
else if (columnMapping.Metadata.IsPrimaryKeyPart)
{
decl += "PRIMARY KEY NOT NULL ";
}
else if (columnMapping.Metadata.HasNotNullConstraint)
{
decl += "NOT NULL DEFAULT \"" + columnMapping.DefaultValue.ToString() + "\" ";
}
if (!string.IsNullOrEmpty (columnMapping.Metadata.CollationSequence))
{
decl += "COLLATE " + columnMapping.Metadata.CollationSequence + " ";
}
return decl;
}
/// <summary>Applies the configuration.</summary>
/// <param name="bulkOperation">The bulk operation.</param>
/// <param name="config">The configuration.</param>
public void ApplyConfig(BulkOperation bulkOperation, DapperPlusEntityMapper config)
{
if (config == null)
{
return;
}
// Verify Column Mappings
Map(bulkOperation, config);
// Batch
{
var batchDelayInterval = config.BatchDelayInterval();
var batchSize = config.BatchSize();
var batchTimeout = config.BatchTimeout();
if (batchDelayInterval.HasValue)
{
bulkOperation.BatchDelayInterval = batchDelayInterval.Value;
}
if (batchSize.HasValue)
{
bulkOperation.BatchSize = batchSize.Value;
}
if (batchTimeout.HasValue)
{
bulkOperation.BatchTimeout = batchTimeout.Value;
}
}
// Destination
{
var table = config.Table();
if (!string.IsNullOrEmpty(table))
{
bulkOperation.DestinationTableName = table;
}
}
// SqlServer
{
var sqlBulkCopyOptions = config.SqlBulkCopyOptions();
if (sqlBulkCopyOptions.HasValue)
{
bulkOperation.SqlBulkCopyOptions = sqlBulkCopyOptions.Value;
}
}
// TemproaryTable
{
var temporaryTableBatchByTable = config.TemporaryTableBatchByTable();
var temporaryTableInsertBatchSize = config.TemporaryTableInsertBatchSize();
var temporaryTableMinRecord = config.TemporaryTableMinRecord();
var temporaryTableSchemaName = config.TemporaryTableSchemaName();
if (temporaryTableBatchByTable.HasValue)
{
bulkOperation.TemporaryTableBatchByTable = temporaryTableBatchByTable.Value;
}
if (temporaryTableInsertBatchSize.HasValue)
{
bulkOperation.TemporaryTableInsertBatchSize = temporaryTableInsertBatchSize.Value;
}
if (temporaryTableMinRecord.HasValue)
{
bulkOperation.TemporaryTableMinRecord = temporaryTableMinRecord.Value;
}
if (!string.IsNullOrEmpty(temporaryTableSchemaName))
{
bulkOperation.TemporaryTableSchemaName = temporaryTableSchemaName;
}
}
// Transient
{
var retryCount = config.RetryCount();
var retryInterval = config.RetryInterval();
if (retryCount.HasValue)
{
bulkOperation.RetryCount = retryCount.Value;
}
if (retryInterval.HasValue)
{
bulkOperation.RetryInterval = new TimeSpan(0, 0, 0, 0, retryInterval.Value);
}
}
// Column Mapping
{
foreach (var column in config._columnMappings)
{
var bulkMapping = new ColumnMapping
{
SourceName = column.SourceName,
DestinationName = column.DestinationName
};
if (column.IsPrimaryKey)
{
bulkMapping.IsPrimaryKey = true;
}
if (column.Input && column.Output)
{
bulkMapping.Direction = ColumnMappingDirectionType.InputOutput;
}
else if (column.Output)
{
bulkMapping.Direction = ColumnMappingDirectionType.Output;
}
if (column.IsIdentity &&
(config == config._masterConfig._configInsert) ||
(config == config._masterConfig._configMerge))
{
bulkMapping.Direction = ColumnMappingDirectionType.Output;
}
bulkOperation.ColumnMappings.Add(bulkMapping);
}
}
}
public IdMapping(ColumnMapping columnMapping)
: this()
{
AddIdColumn(columnMapping);
}
public virtual void Visit(ColumnMapping columnMapping)
{
}
/// <summary>
/// Imports records to Microsoft Dynamics CRM from the specified .csv file.
/// </summary>
public void ImportRecords()
{
// Create an import map.
ImportMap importMap = new ImportMap()
{
Name = "Import Map " + DateTime.Now.Ticks.ToString(),
Source = "Import Accounts.csv",
Description = "Description of data being imported",
EntitiesPerFile =
new OptionSetValue((int)ImportMapEntitiesPerFile.SingleEntityPerFile),
EntityState = EntityState.Created
};
Guid importMapId = _serviceProxy.Create(importMap);
// Create column mappings.
#region Column One Mappings
// Create a column mapping for a 'text' type field.
ColumnMapping colMapping1 = new ColumnMapping()
{
// Set source properties.
SourceAttributeName = "src_name",
SourceEntityName = "Account_1",
// Set target properties.
TargetAttributeName = "name",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with the data map.
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process)
};
// Create the mapping.
Guid colMappingId1 = _serviceProxy.Create(colMapping1);
#endregion
#region Column Two Mappings
// Create a column mapping for a 'lookup' type field.
ColumnMapping colMapping2 = new ColumnMapping()
{
// Set source properties.
SourceAttributeName = "src_parent",
SourceEntityName = "Account_1",
// Set target properties.
TargetAttributeName = "parentaccountid",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with the data map.
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process),
};
// Create the mapping.
Guid colMappingId2 = _serviceProxy.Create(colMapping2);
// Because we created a column mapping of type lookup, we need to specify lookup details in a lookupmapping.
// One lookupmapping will be for the parent account, and the other for the current record.
// This lookupmapping is important because without it the current record
// cannot be used as the parent of another record.
// Create a lookup mapping to the parent account.
LookUpMapping parentLookupMapping = new LookUpMapping()
{
// Relate this mapping with its parent column mapping.
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId2),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)LookUpMappingProcessCode.Process),
// Set the lookup for an account entity by its name attribute.
LookUpEntityName = Account.EntityLogicalName,
LookUpAttributeName = "name",
LookUpSourceCode =
new OptionSetValue((int)LookUpMappingLookUpSourceCode.System)
};
// Create the lookup mapping.
Guid parentLookupMappingId = _serviceProxy.Create(parentLookupMapping);
// Create a lookup on the current record's "src_name" so that this record can
// be used as the parent account for another record being imported.
// Without this lookup, no record using this account as its parent will be imported.
LookUpMapping currentLookUpMapping = new LookUpMapping()
{
// Relate this lookup with its parent column mapping.
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId2),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)LookUpMappingProcessCode.Process),
// Set the lookup for the current record by its src_name attribute.
LookUpAttributeName = "src_name",
LookUpEntityName = "Account_1",
LookUpSourceCode =
new OptionSetValue((int)LookUpMappingLookUpSourceCode.Source)
};
// Create the lookup mapping
Guid currentLookupMappingId = _serviceProxy.Create(currentLookUpMapping);
#endregion
#region Column Three Mappings
// Create a column mapping for a 'picklist' type field
ColumnMapping colMapping3 = new ColumnMapping()
{
// Set source properties
SourceAttributeName = "src_addresstype",
SourceEntityName = "Account_1",
// Set target properties
TargetAttributeName = "address1_addresstypecode",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with its parent data map
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process)
};
// Create the mapping
Guid colMappingId3 = _serviceProxy.Create(colMapping3);
// Because we created a column mapping of type picklist, we need to specify picklist details in a picklistMapping
PickListMapping pickListMapping1 = new PickListMapping()
{
SourceValue = "bill",
TargetValue = 1,
// Relate this column mapping with its column mapping data map
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId3),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)PickListMappingProcessCode.Process)
};
// Create the mapping
Guid picklistMappingId1 = _serviceProxy.Create(pickListMapping1);
// Need a picklist mapping for every address type code expected
PickListMapping pickListMapping2 = new PickListMapping()
{
SourceValue = "ship",
TargetValue = 2,
// Relate this column mapping with its column mapping data map
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId3),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)PickListMappingProcessCode.Process)
};
// Create the mapping
Guid picklistMappingId2 = _serviceProxy.Create(pickListMapping2);
#endregion
// Create Import
Import import = new Import()
{
// IsImport is obsolete; use ModeCode to declare Create or Update.
ModeCode = new OptionSetValue((int)ImportModeCode.Create),
Name = "Importing data"
};
Guid importId = _serviceProxy.Create(import);
// Create Import File.
ImportFile importFile = new ImportFile()
{
Content = BulkImportHelper.ReadCsvFile("Import Accounts.csv"), // Read contents from disk.
Name = "Account record import",
IsFirstRowHeader = true,
ImportMapId = new EntityReference(ImportMap.EntityLogicalName, importMapId),
UseSystemMap = false,
Source = "Import Accounts.csv",
SourceEntityName = "Account_1",
TargetEntityName = Account.EntityLogicalName,
ImportId = new EntityReference(Import.EntityLogicalName, importId),
EnableDuplicateDetection = false,
FieldDelimiterCode =
new OptionSetValue((int)ImportFileFieldDelimiterCode.Comma),
DataDelimiterCode =
new OptionSetValue((int)ImportFileDataDelimiterCode.DoubleQuote),
ProcessCode =
new OptionSetValue((int)ImportFileProcessCode.Process)
};
// Get the current user to set as record owner.
WhoAmIRequest systemUserRequest = new WhoAmIRequest();
WhoAmIResponse systemUserResponse =
(WhoAmIResponse)_serviceProxy.Execute(systemUserRequest);
// Set the owner ID.
importFile.RecordsOwnerId =
new EntityReference(SystemUser.EntityLogicalName, systemUserResponse.UserId);
Guid importFileId = _serviceProxy.Create(importFile);
//<snippetImportWithCreate1>
// Retrieve the header columns used in the import file.
GetHeaderColumnsImportFileRequest headerColumnsRequest = new GetHeaderColumnsImportFileRequest()
{
ImportFileId = importFileId
};
GetHeaderColumnsImportFileResponse headerColumnsResponse =
(GetHeaderColumnsImportFileResponse)_serviceProxy.Execute(headerColumnsRequest);
// Output the header columns.
int columnNum = 1;
foreach (string headerName in headerColumnsResponse.Columns)
{
Console.WriteLine("Column[" + columnNum.ToString() + "] = " + headerName);
columnNum++;
}
//</snippetImportWithCreate1>
//<snippetImportWithCreate2>
// Parse the import file.
ParseImportRequest parseImportRequest = new ParseImportRequest()
{
ImportId = importId
};
ParseImportResponse parseImportResponse =
(ParseImportResponse)_serviceProxy.Execute(parseImportRequest);
Console.WriteLine("Waiting for Parse async job to complete");
//</snippetImportWithCreate2>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, parseImportResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
//<snippetImportWithCreate3>
// Retrieve the first two distinct values for column 1 from the parse table.
// NOTE: You must create the parse table first using the ParseImport message.
// The parse table is not accessible after ImportRecordsImportResponse is called.
GetDistinctValuesImportFileRequest distinctValuesRequest = new GetDistinctValuesImportFileRequest()
{
columnNumber = 1,
ImportFileId = importFileId,
pageNumber = 1,
recordsPerPage = 2,
};
GetDistinctValuesImportFileResponse distinctValuesResponse =
(GetDistinctValuesImportFileResponse)_serviceProxy.Execute(distinctValuesRequest);
// Output the distinct values. In this case: (column 1, row 1) and (column 1, row 2).
int cellNum = 1;
foreach (string cellValue in distinctValuesResponse.Values)
{
Console.WriteLine("(1, " + cellNum.ToString() + "): " + cellValue);
Console.WriteLine(cellValue);
cellNum++;
}
//</snippetImportWithCreate3>
//<snippetImportWithCreate4>
// Retrieve data from the parse table.
// NOTE: You must create the parse table first using the ParseImport message.
// The parse table is not accessible after ImportRecordsImportResponse is called.
RetrieveParsedDataImportFileRequest parsedDataRequest = new RetrieveParsedDataImportFileRequest()
{
ImportFileId = importFileId,
PagingInfo = new PagingInfo()
{
// Specify the number of entity instances returned per page.
Count = 2,
// Specify the number of pages returned from the query.
PageNumber = 1,
// Specify a total number of entity instances returned.
PagingCookie = "1"
}
};
RetrieveParsedDataImportFileResponse parsedDataResponse =
(RetrieveParsedDataImportFileResponse)_serviceProxy.Execute(parsedDataRequest);
// Output the first two rows retrieved.
int rowCount = 1;
foreach (string[] rows in parsedDataResponse.Values)
{
int colCount = 1;
foreach (string column in rows)
{
Console.WriteLine("(" + rowCount.ToString() + "," + colCount.ToString() + ") = " + column);
colCount++;
}
rowCount++;
}
//</snippetImportWithCreate4>
//<snippetImportWithCreate5>
// Transform the import
TransformImportRequest transformImportRequest = new TransformImportRequest()
{
ImportId = importId
};
TransformImportResponse transformImportResponse =
(TransformImportResponse)_serviceProxy.Execute(transformImportRequest);
Console.WriteLine("Waiting for Transform async job to complete");
//</snippetImportWithCreate5>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, transformImportResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
//<snippetImportWithCreate6>
// Upload the records.
ImportRecordsImportRequest importRequest = new ImportRecordsImportRequest()
{
ImportId = importId
};
ImportRecordsImportResponse importResponse =
(ImportRecordsImportResponse)_serviceProxy.Execute(importRequest);
Console.WriteLine("Waiting for ImportRecords async job to complete");
//</snippetImportWithCreate6>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, importResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
}
public static string GetSubDAL(ProjectParameter projParam, int index, ColumnMapping.ColumnInfos colInfos, string[] objMapping)
{
StringBuilder sbCodes = new StringBuilder();
sbCodes.AppendLine(string.Format("\t\tpublic class {0}{1}", projParam.UOPrefix, GetObjectName(index, objMapping)));
sbCodes.AppendLine("\t\t{");
sbCodes.AppendLine(string.Format("\t\t\tpublic {0}{1}()", projParam.UOPrefix, GetObjectName(index, objMapping)));
sbCodes.AppendLine("\t\t\t{");
sbCodes.AppendLine("\t\t\t}");
StringBuilder sb_columns = new StringBuilder();
sb_columns.AppendLine("\t\t\t#region Columns");
foreach (ColumnMapping.ColumnInfo c in colInfos)
{
sb_columns.AppendLine(string.Format("\t\t\tprivate {0} _{1};", c.ColumnType, c.ColumnName));
sb_columns.Append(string.Format("\t\t\t[Mapping(\"{0}", c.ColumnName));
sb_columns.AppendLine("\")]");
sb_columns.AppendLine(string.Format("\t\t\tpublic {0} {1}", c.ColumnType, c.ColumnName));
sb_columns.AppendLine("\t\t\t{");
sb_columns.AppendLine("\t\t\t\tget");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\treturn _{0};", c.ColumnName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t\tset");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\t_{0} = value;", c.ColumnName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t}");
}
sb_columns.AppendLine("\t\t\t#endregion");
sbCodes.AppendLine(sb_columns.ToString());
sbCodes.Append("\t\t}");
return sbCodes.ToString();
}
public IdMapping(ColumnMapping columnMapping)
: this(new AttributeStore(), columnMapping)
{
}
/// <summary>
/// Imports records to Microsoft Dynamics CRM from the specified .csv file.
/// </summary>
public void ImportRecords()
{
// Create an import map.
ImportMap importMap = new ImportMap()
{
Name = "Import Map " + DateTime.Now.Ticks.ToString(),
Source = "Import Accounts.csv",
Description = "Description of data being imported",
EntitiesPerFile =
new OptionSetValue((int)ImportMapEntitiesPerFile.SingleEntityPerFile),
EntityState = EntityState.Created
};
Guid importMapId = _serviceProxy.Create(importMap);
// Create column mappings.
#region Column One Mappings
// Create a column mapping for a 'text' type field.
ColumnMapping colMapping1 = new ColumnMapping()
{
// Set source properties.
SourceAttributeName = "src_name",
SourceEntityName = "Account_1",
// Set target properties.
TargetAttributeName = "name",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with the data map.
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process)
};
// Create the mapping.
Guid colMappingId1 = _serviceProxy.Create(colMapping1);
#endregion
#region Column Two Mappings
// Create a column mapping for a 'lookup' type field.
ColumnMapping colMapping2 = new ColumnMapping()
{
// Set source properties.
SourceAttributeName = "src_parent",
SourceEntityName = "Account_1",
// Set target properties.
TargetAttributeName = "parentaccountid",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with the data map.
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process),
};
// Create the mapping.
Guid colMappingId2 = _serviceProxy.Create(colMapping2);
// Because we created a column mapping of type lookup, we need to specify lookup details in a lookupmapping.
// One lookupmapping will be for the parent account, and the other for the current record.
// This lookupmapping is important because without it the current record
// cannot be used as the parent of another record.
// Create a lookup mapping to the parent account.
LookUpMapping parentLookupMapping = new LookUpMapping()
{
// Relate this mapping with its parent column mapping.
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId2),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)LookUpMappingProcessCode.Process),
// Set the lookup for an account entity by its name attribute.
LookUpEntityName = Account.EntityLogicalName,
LookUpAttributeName = "name",
LookUpSourceCode =
new OptionSetValue((int)LookUpMappingLookUpSourceCode.System)
};
// Create the lookup mapping.
Guid parentLookupMappingId = _serviceProxy.Create(parentLookupMapping);
// Create a lookup on the current record's "src_name" so that this record can
// be used as the parent account for another record being imported.
// Without this lookup, no record using this account as its parent will be imported.
LookUpMapping currentLookUpMapping = new LookUpMapping()
{
// Relate this lookup with its parent column mapping.
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId2),
// Force this column to be processed.
ProcessCode =
new OptionSetValue((int)LookUpMappingProcessCode.Process),
// Set the lookup for the current record by its src_name attribute.
LookUpAttributeName = "src_name",
LookUpEntityName = "Account_1",
LookUpSourceCode =
new OptionSetValue((int)LookUpMappingLookUpSourceCode.Source)
};
// Create the lookup mapping
Guid currentLookupMappingId = _serviceProxy.Create(currentLookUpMapping);
#endregion
#region Column Three Mappings
// Create a column mapping for a 'picklist' type field
ColumnMapping colMapping3 = new ColumnMapping()
{
// Set source properties
SourceAttributeName = "src_addresstype",
SourceEntityName = "Account_1",
// Set target properties
TargetAttributeName = "address1_addresstypecode",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with its parent data map
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, importMapId),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process)
};
// Create the mapping
Guid colMappingId3 = _serviceProxy.Create(colMapping3);
// Because we created a column mapping of type picklist, we need to specify picklist details in a picklistMapping
PickListMapping pickListMapping1 = new PickListMapping()
{
SourceValue = "bill",
TargetValue = 1,
// Relate this column mapping with its column mapping data map
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId3),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)PickListMappingProcessCode.Process)
};
// Create the mapping
Guid picklistMappingId1 = _serviceProxy.Create(pickListMapping1);
// Need a picklist mapping for every address type code expected
PickListMapping pickListMapping2 = new PickListMapping()
{
SourceValue = "ship",
TargetValue = 2,
// Relate this column mapping with its column mapping data map
ColumnMappingId =
new EntityReference(ColumnMapping.EntityLogicalName, colMappingId3),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)PickListMappingProcessCode.Process)
};
// Create the mapping
Guid picklistMappingId2 = _serviceProxy.Create(pickListMapping2);
#endregion
// Create Import
Import import = new Import()
{
// IsImport is obsolete; use ModeCode to declare Create or Update.
ModeCode = new OptionSetValue((int)ImportModeCode.Create),
Name = "Importing data"
};
Guid importId = _serviceProxy.Create(import);
// Create Import File.
ImportFile importFile = new ImportFile()
{
Content = BulkImportHelper.ReadCsvFile("Import Accounts.csv"), // Read contents from disk.
Name = "Account record import",
IsFirstRowHeader = true,
ImportMapId = new EntityReference(ImportMap.EntityLogicalName, importMapId),
UseSystemMap = false,
Source = "Import Accounts.csv",
SourceEntityName = "Account_1",
TargetEntityName = Account.EntityLogicalName,
ImportId = new EntityReference(Import.EntityLogicalName, importId),
EnableDuplicateDetection = false,
FieldDelimiterCode =
new OptionSetValue((int)ImportFileFieldDelimiterCode.Comma),
DataDelimiterCode =
new OptionSetValue((int)ImportFileDataDelimiterCode.DoubleQuote),
ProcessCode =
new OptionSetValue((int)ImportFileProcessCode.Process)
};
// Get the current user to set as record owner.
WhoAmIRequest systemUserRequest = new WhoAmIRequest();
WhoAmIResponse systemUserResponse =
(WhoAmIResponse)_serviceProxy.Execute(systemUserRequest);
// Set the owner ID.
importFile.RecordsOwnerId =
new EntityReference(SystemUser.EntityLogicalName, systemUserResponse.UserId);
Guid importFileId = _serviceProxy.Create(importFile);
//<snippetImportWithCreate1>
// Retrieve the header columns used in the import file.
GetHeaderColumnsImportFileRequest headerColumnsRequest = new GetHeaderColumnsImportFileRequest()
{
ImportFileId = importFileId
};
GetHeaderColumnsImportFileResponse headerColumnsResponse =
(GetHeaderColumnsImportFileResponse)_serviceProxy.Execute(headerColumnsRequest);
// Output the header columns.
int columnNum = 1;
foreach (string headerName in headerColumnsResponse.Columns)
{
Console.WriteLine("Column[" + columnNum.ToString() + "] = " + headerName);
columnNum++;
}
//</snippetImportWithCreate1>
//<snippetImportWithCreate2>
// Parse the import file.
ParseImportRequest parseImportRequest = new ParseImportRequest()
{
ImportId = importId
};
ParseImportResponse parseImportResponse =
(ParseImportResponse)_serviceProxy.Execute(parseImportRequest);
Console.WriteLine("Waiting for Parse async job to complete");
//</snippetImportWithCreate2>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, parseImportResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
//<snippetImportWithCreate3>
// Retrieve the first two distinct values for column 1 from the parse table.
// NOTE: You must create the parse table first using the ParseImport message.
// The parse table is not accessible after ImportRecordsImportResponse is called.
GetDistinctValuesImportFileRequest distinctValuesRequest = new GetDistinctValuesImportFileRequest()
{
columnNumber = 1,
ImportFileId = importFileId,
pageNumber = 1,
recordsPerPage = 2,
};
GetDistinctValuesImportFileResponse distinctValuesResponse =
(GetDistinctValuesImportFileResponse)_serviceProxy.Execute(distinctValuesRequest);
// Output the distinct values. In this case: (column 1, row 1) and (column 1, row 2).
int cellNum = 1;
foreach (string cellValue in distinctValuesResponse.Values)
{
Console.WriteLine("(1, " + cellNum.ToString() + "): " + cellValue);
Console.WriteLine(cellValue);
cellNum++;
}
//</snippetImportWithCreate3>
//<snippetImportWithCreate4>
// Retrieve data from the parse table.
// NOTE: You must create the parse table first using the ParseImport message.
// The parse table is not accessible after ImportRecordsImportResponse is called.
RetrieveParsedDataImportFileRequest parsedDataRequest = new RetrieveParsedDataImportFileRequest()
{
ImportFileId = importFileId,
PagingInfo = new PagingInfo()
{
// Specify the number of entity instances returned per page.
Count = 2,
// Specify the number of pages returned from the query.
PageNumber = 1,
// Specify a total number of entity instances returned.
PagingCookie = "1"
}
};
RetrieveParsedDataImportFileResponse parsedDataResponse =
(RetrieveParsedDataImportFileResponse)_serviceProxy.Execute(parsedDataRequest);
// Output the first two rows retrieved.
int rowCount = 1;
foreach (string[] rows in parsedDataResponse.Values)
{
int colCount = 1;
foreach (string column in rows)
{
Console.WriteLine("(" + rowCount.ToString() + "," + colCount.ToString() + ") = " + column);
colCount++;
}
rowCount++;
}
//</snippetImportWithCreate4>
//<snippetImportWithCreate5>
// Transform the import
TransformImportRequest transformImportRequest = new TransformImportRequest()
{
ImportId = importId
};
TransformImportResponse transformImportResponse =
(TransformImportResponse)_serviceProxy.Execute(transformImportRequest);
Console.WriteLine("Waiting for Transform async job to complete");
//</snippetImportWithCreate5>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, transformImportResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
//<snippetImportWithCreate6>
// Upload the records.
ImportRecordsImportRequest importRequest = new ImportRecordsImportRequest()
{
ImportId = importId
};
ImportRecordsImportResponse importResponse =
(ImportRecordsImportResponse)_serviceProxy.Execute(importRequest);
Console.WriteLine("Waiting for ImportRecords async job to complete");
//</snippetImportWithCreate6>
BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, importResponse.AsyncOperationId);
BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
}
private void AnalyzeWorksheet(Worksheet worksheet)
{
string lastProcessedItem = "Jeszcze nie zaczelismy przetwarzania";
int lastReadColumn = -1;
string tempStr = string.Empty;
CompaniesReadFromFile = new List <InputCompany>();
try
{
lastProcessedItem = "Przed wczytywaniem nagłówka";
HeaderRow = -1;
HeaderRow = SpreadSheetHelper.FindHeaderRow(worksheet, _columnsConfig);
if (HeaderRow == -1)
{
throw new SpreadSheetReaderHeaderException(string.Format("Błąd formatu aruksza. W arkuszu: {0} nie znaleziono nagłówka danych. Sprawdź czy któryś nagłówek zawiera słowo 'nip'", worksheet.Name));
}
DetermineColumns(worksheet);
ValidateColumns();
lastProcessedItem = "Wczytano nagłówek";
InputCompany tempCompany;
for (int i = HeaderRow + 1; !IsEndOfTable(i, 1, worksheet); i++)
{
tempCompany = new InputCompany();
tempCompany.RowNumber = i;
lastProcessedItem = $"Przed wczytaniem NIPu. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NIP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NIP]]).Formula.ToString();
tempCompany.NIP = GetNip(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano NIP. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem konta bankowego. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.AccountNumber];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.AccountNumber]]).Formula.ToString();
tempCompany.BankAccountNumber = GetBankAccountNumber(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano Numer Konta. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem LP. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.LP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.LP]]).Formula.ToString();
tempCompany.LP = GetLP(tempStr);
tempStr = string.Empty;
if (CompaniesReadFromFile.Any(c => c.ID == tempCompany.ID))
{
throw new SpreadSheetReaderException($"Dane zawierają już wpis o takiej samej pozycji i nipie. Aktualna wiersz: {i}, Nip: {tempCompany.NIP}, LP: {tempCompany.LP}");
}
lastProcessedItem = $"Wczytano LP.Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty zapłaty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.PaymentDate];
var dateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.PaymentDate]]);
tempStr = dateRange.Formula.ToString();
tempCompany.PaymentDate = GetDate(dateRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę zapłaty. Aktualna pozycja: wiersz {i}";
if (ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate))
{
lastProcessedItem = $"Przed wczytaniem daty faktury. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.InvoiceDate];
var invoiceDateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.InvoiceDate]]);
tempStr = invoiceDateRange.Formula.ToString();
double tempDateInDouble;
if (double.TryParse(tempStr, out tempDateInDouble))
{
try
{
tempCompany.InvoiceDate = DateTime.FromOADate(tempDateInDouble);
}
catch (ArgumentException)
{ tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError); }
}
else
{
tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError);
}
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę faktury. Aktualna pozycja: wiersz {i}";
}
if ((_generateNotes && AreColumnsToGenerateNotesPresent()))
{
lastProcessedItem = $"Przed wczytaniem ID noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteID];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteID]]).Formula.ToString().Trim();
tempCompany.NoteID = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano ID Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem tytułu Noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteTitle];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteTitle]]).Formula.ToString().Trim();
tempCompany.NoteTitle = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano tytuł Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem netto noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteAmount];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteAmount]]).Formula.ToString().Trim();
tempCompany.NoteNettoAmount = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano netto noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteDate];
var dataRange = (Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteDate]];
tempStr = dataRange.Formula.ToString();
tempCompany.NoteDate = GetDate(dataRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę noty. Aktualna pozycja: wiersz {i}";
}
tempCompany.FormatErrors.AddRange(ValidateCompany(tempCompany));
CompaniesReadFromFile.Add(tempCompany);
}
}
catch (Exception e)
{
if (e is SpreadSheetReaderHeaderException || e is SpreadSheetReaderMissingColumnsException)
{
throw;
}
string errorMsg = string.Format("\nZłapano błąd w rzędzie gdy procedura była na kroku: {0}, w kolumnie: {2}, odczytała wartość: {1}.\n\n", lastProcessedItem, tempStr, lastReadColumn);
throw new SpreadSheetReaderException(errorMsg, e);
}
}
private bool AreColumnsToGenerateNotesPresent()
{
if (ColumnMapping.ContainsKey(ImportColumnName.NoteAmount) && ColumnMapping.ContainsKey(ImportColumnName.NoteDate) && ColumnMapping.ContainsKey(ImportColumnName.NoteID) && ColumnMapping.ContainsKey(ImportColumnName.NoteTitle))
{
return(true);
}
return(false);
}
private void DetermineColumns(Worksheet worksheet)
{
string accountHeaderCaption = null, paymentDateCaption = null, idCaption = null, nipCaption = null, invoiceDateCaption = null;
try
{
nipCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => string.Compare(c.ID, ImportColumnName.NIP.ToString(), true) == 0).HeaderText.ToLower());
accountHeaderCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.AccountNumber.ToString()).HeaderText.ToLower());
paymentDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.PaymentDate.ToString()).HeaderText.ToLower());
idCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.LP.ToString()).HeaderText.ToLower());
invoiceDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.InvoiceDate.ToString()).HeaderText.ToLower());
}
catch (Exception e)
{
throw new SpreadSheetReaderException("Brak podstawowych kolumn w pliku konfiguracyjnym tj. konto bankowe, data zapłaty, lp, data faktury", e);
}
int maxNumOfColumnToRead = Enum.GetNames(typeof(ImportColumnName)).Length; // nip is read
string noteIDsCaption = null, noteAmountCaption = null, noteTitleCaption = null, noteDateCaption = null;
noteIDsCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteID.ToString()).HeaderText.ToLower());
noteAmountCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteAmount.ToString()).HeaderText.ToLower());
noteTitleCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteTitle.ToString()).HeaderText.ToLower());
noteDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteDate.ToString()).HeaderText.ToLower());
for (int column = 1; column <= 75 && ColumnMapping.Count < maxNumOfColumnToRead; column++)
{
var tempCellContent = ((string)((Range)worksheet.Cells[HeaderRow, column]).Formula).ToLower().Trim();
tempCellContent = DataFormatHelper.RemovePolishLetters(tempCellContent);
if (tempCellContent.Contains(_columnPrefixToIgnore) || string.IsNullOrEmpty(tempCellContent))
{
continue;
}
if (tempCellContent.Contains(nipCaption) && !ColumnMapping.ContainsKey(ImportColumnName.NIP))
{
ColumnMapping.Add(ImportColumnName.NIP, column);
}
else if (tempCellContent.Contains(idCaption) && !ColumnMapping.ContainsKey(ImportColumnName.LP))
{
ColumnMapping.Add(ImportColumnName.LP, column);
}
else if (tempCellContent.Contains(accountHeaderCaption) && !ColumnMapping.ContainsKey(ImportColumnName.AccountNumber))
{
ColumnMapping.Add(ImportColumnName.AccountNumber, column);
}
else if (tempCellContent.Contains(paymentDateCaption) && !ColumnMapping.ContainsKey(ImportColumnName.PaymentDate))
{
ColumnMapping.Add(ImportColumnName.PaymentDate, column);
}
else if (_readInvoiceDate && tempCellContent.Contains(invoiceDateCaption) && !ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate))
{
ColumnMapping.Add(ImportColumnName.InvoiceDate, column);
}
else if (noteIDsCaption != null && tempCellContent.Equals(noteIDsCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteID))
{
ColumnMapping.Add(ImportColumnName.NoteID, column);
}
else if (noteAmountCaption != null && tempCellContent.Equals(noteAmountCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteAmount))
{
ColumnMapping.Add(ImportColumnName.NoteAmount, column);
}
else if (noteTitleCaption != null && tempCellContent.Equals(noteTitleCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteTitle))
{
ColumnMapping.Add(ImportColumnName.NoteTitle, column);
}
else if (noteDateCaption != null && tempCellContent.Equals(noteDateCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteDate))
{
ColumnMapping.Add(ImportColumnName.NoteDate, column);
}
}
}
private bool MinimumColumnsArePresent()
{
if (ColumnMapping.ContainsKey(ImportColumnName.LP) && ColumnMapping.ContainsKey(ImportColumnName.AccountNumber) && ColumnMapping.ContainsKey(ImportColumnName.NIP) && ColumnMapping.ContainsKey(ImportColumnName.PaymentDate))
{
return(true);
}
return(false);
}
private bool IsAtLeastOneColumnForNotesPresent()
{
if (ColumnMapping.ContainsKey(ImportColumnName.NoteAmount) || ColumnMapping.ContainsKey(ImportColumnName.NoteDate) || ColumnMapping.ContainsKey(ImportColumnName.NoteID) || ColumnMapping.ContainsKey(ImportColumnName.NoteTitle))
{
return(true);
}
return(false);
}
private void ValidateColumns()
{
bool areColumnsForNotesPresent = AreColumnsToGenerateNotesPresent();
bool isAtLeastOneColumnForNotesPresent = IsAtLeastOneColumnForNotesPresent();
if (_readInvoiceDate && !ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate) && isAtLeastOneColumnForNotesPresent)
{
throw new SpreadSheetReaderMissingColumnsException($"Chcesz sprawdzić firmy na datę faktury, ale podajesz plik, który ma NOTY.");
}
if (!MinimumColumnsArePresent() || (_generateNotes && !areColumnsForNotesPresent) || (_readInvoiceDate && !ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate)))
{
throw new SpreadSheetReaderMissingColumnsException($"Nie wszystkie kolumny są obecne w arkuszu. Brakuje: {GetMissingColumnsNames()}");
}
}
public static string GetSubDataObject(Settings cf, int index, ColumnMapping.ColumnInfos columnInfos)
{
StringBuilder sbReturn = new StringBuilder();
sbReturn.AppendLine(string.Format("\t\tpublic class {0}Result{1}", cf.UOPrefix, index));
sbReturn.AppendLine("\t\t{");
sbReturn.AppendLine(string.Format("\t\t\tpublic {0}Result{1}()", cf.UOPrefix, index));
sbReturn.AppendLine("\t\t\t{");
sbReturn.AppendLine("\t\t\t}");
StringBuilder sb_columns = new StringBuilder();
sb_columns.AppendLine("\t\t\t#region Columns");
foreach (ColumnMapping.ColumnInfo c in columnInfos)
{
sb_columns.AppendLine(string.Format("\t\t\tprivate {0} _{1};", c.ColumnType, c.Column));
sb_columns.Append(string.Format("\t\t\t[Mapping(\"{0}", c.Column));
sb_columns.AppendLine("\")]");
sb_columns.AppendLine(string.Format("\t\t\tpublic {0} {1}", c.ColumnType, c.Column));
sb_columns.AppendLine("\t\t\t{");
sb_columns.AppendLine("\t\t\t\tget");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\treturn _{0};", c.Column));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t\tset");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\t_{0} = value;", c.Column));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t}");
}
sb_columns.AppendLine("\t\t\t#endregion");
sbReturn.AppendLine(sb_columns.ToString());
sbReturn.Append("\t\t}");
return sbReturn.ToString();
}
public void MapColumn(string excelColumnName, string ssisColumnName, System.Type dataType, bool treatBlanksAsNulls)
{
string methodName = "set_" + ssisColumnName.Replace(" ", "");
VerboseLog("Creating " + dataType.ToString() + " mapping from '" + excelColumnName + "' to '" + ssisColumnName + "' via " + methodName);
ColumnMapping mapping = new ColumnMapping(excelColumnName, ssisColumnName, dataType, treatBlanksAsNulls);
#region Code to create delegates I'd have liked to have inside the ColumnMapping class itself if I could pass Output0Buffer...
VerboseLog("Creating delegate for " + methodName + "_IsNull");
mapping.SetNull = (ColumnMapping.NullSetter)Delegate.CreateDelegate(typeof(ColumnMapping.NullSetter), Output0Buffer, methodName + "_IsNull");
if (dataType == typeof(string))
{
VerboseLog("Creating delegate for " + methodName + "string");
mapping.SetString = (ColumnMapping.StringSetter)Delegate.CreateDelegate(typeof(ColumnMapping.StringSetter), Output0Buffer, methodName);
}
else if (dataType == typeof(int))
{
VerboseLog("Creating delegate for " + methodName + "int");
mapping.SetInt = (ColumnMapping.Int32Setter)Delegate.CreateDelegate(typeof(ColumnMapping.Int32Setter), Output0Buffer, methodName);
}
else if (dataType == typeof(DateTime))
{
mapping.SetDateTime = (ColumnMapping.DateTimeSetter)Delegate.CreateDelegate(typeof(ColumnMapping.DateTimeSetter), Output0Buffer, methodName);
}
else if (dataType == typeof(bool))
{
mapping.SetBoolean = (ColumnMapping.BooleanSetter)Delegate.CreateDelegate(typeof(ColumnMapping.BooleanSetter), Output0Buffer, methodName);
}
//ADD_DATATYPES_HERE
else
{
ReportUnhandledDataTypeError(dataType);
}
this._columnMappings.Add(mapping);
#endregion
}
public async Task <object> GetValue(DbDataReader reader, int ordinal, ColumnMapping columnMapping)
{
return(await base.GetFieldValue(reader, ordinal));
}
public override void Visit(ColumnMapping columnMapping)
{
columnMapping.AcceptVisitor(this);
}
DataTable ToSqlBulkCopyDataTable <TModel>(List <TModel> modelList, TypeDescriptor typeDescriptor)
{
DataTable dt = new DataTable();
List <SysColumn> columns = GetTableColumns(typeDescriptor.Table.Name);
List <ColumnMapping> columnMappings = new List <ColumnMapping>();
var mappingMemberDescriptors = typeDescriptor.MappingMemberDescriptors.Select(a => a.Value).ToList();
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
MappingMemberDescriptor mappingMemberDescriptor = mappingMemberDescriptors.Find(a => string.Equals(a.Column.Name, column.Name));
if (mappingMemberDescriptor == null)
{
mappingMemberDescriptor = mappingMemberDescriptors.Find(a => string.Equals(a.Column.Name, column.Name, StringComparison.OrdinalIgnoreCase));
}
ColumnMapping columnMapping = new ColumnMapping(column);
Type dataType;
if (mappingMemberDescriptor == null)
{
/*
* 由于 SqlBulkCopy 要求传入的列必须与表列一一对应，因此，如果 model 中没有与列对应的属性，则使用列数据类型的默认值
*/
SysType sysType = GetSysTypeByTypeName(column.TypeName);
columnMapping.DefaultValue = column.IsNullable ? null : sysType.DefaultValue;
dataType = sysType.CSharpType;
}
else
{
columnMapping.MapMember = mappingMemberDescriptor.MemberInfo;
dataType = mappingMemberDescriptor.MemberInfoType.GetUnderlyingType();
if (dataType.IsEnum)
{
dataType = Enum.GetUnderlyingType(dataType);
}
}
columnMappings.Add(columnMapping);
dt.Columns.Add(new DataColumn(column.Name, dataType));
}
foreach (var model in modelList)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < columnMappings.Count; i++)
{
ColumnMapping columnMapping = columnMappings[i];
MemberInfo member = columnMapping.MapMember;
object value = null;
if (member == null)
{
value = columnMapping.DefaultValue;
}
else
{
value = member.GetMemberValue(model);
if (member.GetMemberType().GetUnderlyingType().IsEnum)
{
if (value != null)
{
value = Convert.ChangeType(value, Enum.GetUnderlyingType(value.GetType()));
}
}
}
dr[i] = value ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
return(dt);
}
private static void ConvertListParameterByValue(IDataParameter parameter, IEnumerable list, IDbCommand command)
{
// we are going to replace the current parameter with a list of new parameters
command.Parameters.Remove(parameter);
string parameterName = parameter.ParameterName;
var count = 0;
bool isString = list is IEnumerable <string>;
// add a parameter for each item
foreach (var item in list)
{
count++;
// create the parameter for the item
var listParam = command.CreateParameter();
listParam.ParameterName = parameterName + count;
listParam.Value = item ?? DBNull.Value;
// if we are dealing with strings, add the length of the string
if (isString && item != null)
{
int length = ((string)item).Length;
if (length > 4000)
{
length = -1;
}
listParam.Size = length;
}
listParam.DbType = ColumnMapping.MapParameterDataType(
isString ? typeof(string) : (item?.GetType() ?? typeof(object)),
command,
parameter,
listParam.DbType);
command.Parameters.Add(listParam);
}
if (count == 0)
{
command.CommandText = Regex.Replace(command.CommandText, _parameterPrefixRegex + Regex.Escape(parameterName), "NULL", RegexOptions.IgnoreCase);
}
else
{
command.CommandText = Regex.Replace(
command.CommandText,
_parameterPrefixRegex + Regex.Escape(parameterName),
match =>
{
var grp = match.Value;
var sb = new StringBuilder();
// append the parameters
sb.Append(grp).Append(1);
for (int i = 2; i <= count; i++)
{
sb.Append(',').Append(grp).Append(i);
}
return(sb.ToString());
},
RegexOptions.IgnoreCase);
}
}
/// <summary>
/// Creates the import mapping record.
/// </summary>
private void CreateImportMapping()
{
// Create the import map and populate a column
ImportMap importMap = new ImportMap()
{
Name = "Original Import Mapping" + DateTime.Now.Ticks.ToString(),
Source = "Import Accounts.csv",
Description = "Description of data being imported",
EntitiesPerFile =
new OptionSetValue((int)ImportMapEntitiesPerFile.SingleEntityPerFile),
EntityState = EntityState.Created
};
_importMapId = _serviceProxy.Create(importMap);
Console.WriteLine(String.Concat("Import map created: ", _importMapId.Value));
#region Column One Mappings
// Create a column mapping for a 'text' type field
ColumnMapping colMapping1 = new ColumnMapping()
{
// Set source properties
SourceAttributeName = "name",
SourceEntityName = "Account_1",
// Set target properties
TargetAttributeName = "name",
TargetEntityName = Account.EntityLogicalName,
// Relate this column mapping with the data map
ImportMapId =
new EntityReference(ImportMap.EntityLogicalName, _importMapId.Value),
// Force this column to be processed
ProcessCode =
new OptionSetValue((int)ColumnMappingProcessCode.Process)
};
// Create the mapping
Guid colMappingId1 = _serviceProxy.Create(colMapping1);
Console.WriteLine(String.Concat("Column mapping added SourceAttributeName: name",
", TargetAttributeName: name, TargetEntityName: account"));
#endregion
}
public ColumnInstance(Type parentType, ColumnMapping mapping)
: base(parentType, mapping)
{
this.mapping = mapping;
}
public override void ProcessColumn(ColumnMapping columnMapping)
{
Process(columnMapping, columnMapping.PropertyInfo);
}
public void SetUp()
{
this.mapping = new ColumnMapping();
this.columnPart = new ColumnPart(mapping);
}
public virtual void ProcessColumn(ColumnMapping columnMapping)
{
}
private static Action <IDbCommand, object> CreateClassInputParameterGenerator(IDbCommand command, Type type)
{
var provider = InsightDbProvider.For(command);
var parameters = provider.DeriveParameters(command);
// special case if the parameters object is an IEnumerable or Array
// look for the parameter that is a Structured object and pass the array to the TVP
// note that string supports ienumerable, so exclude atomic types
var enumerable = type.GetInterfaces().FirstOrDefault(i => i.GetTypeInfo().IsGenericType&& i.GetGenericTypeDefinition() == typeof(IEnumerable <>));
if (enumerable != null && type != typeof(string) && parameters.OfType <IDataParameter>().Where(p => p.Direction.HasFlag(ParameterDirection.Input)).Count() == 1)
{
return((IDbCommand cmd, object o) =>
{
// don't use the provider above. The command may be unwrapped by the time we get back here
var tableParameter = InsightDbProvider.For(cmd).CloneParameter(cmd, parameters.OfType <IDataParameter>().Single(p => p.Direction.HasFlag(ParameterDirection.Input)));
cmd.Parameters.Add(tableParameter);
ListParameterHelper.ConvertListParameter(tableParameter, o, cmd);
});
}
// get the mapping of the properties for the type
var mappings = ColumnMapping.MapParameters(type, command, parameters);
// start creating a dynamic method
Type typeOwner = type.HasElementType ? type.GetElementType() : type;
var dm = new DynamicMethod(String.Format(CultureInfo.InvariantCulture, "CreateInputParameters-{0}", Guid.NewGuid()), null, new[] { typeof(IDbCommand), typeof(object) }, typeOwner, true);
var il = dm.GetILGenerator();
// copy the parameters into the command object
var parametersLocal = il.DeclareLocal(typeof(IDataParameter[]));
StaticFieldStorage.EmitLoad(il, provider);
il.Emit(OpCodes.Ldarg_0);
StaticFieldStorage.EmitLoad(il, parameters);
il.Emit(OpCodes.Call, typeof(InsightDbProvider).GetMethod("CopyParameters", BindingFlags.NonPublic | BindingFlags.Instance));
il.Emit(OpCodes.Stloc, parametersLocal);
// go through all of the mappings
for (int i = 0; i < mappings.Count; i++)
{
var mapping = mappings[i];
var dbParameter = parameters[i];
// if there is no mapping for the parameter
if (mapping == null)
{
// sql will silently eat table parameters that are not specified, and that can be difficult to debug
if (provider.IsTableValuedParameter(command, dbParameter))
{
throw new InvalidOperationException(String.Format(CultureInfo.InvariantCulture, "Table parameter {0} must be specified", dbParameter.ParameterName));
}
// unspecified input parameters get skipped
if (dbParameter.Direction == ParameterDirection.Input)
{
parameters[i] = null;
}
continue;
}
var memberType = mapping.Member.MemberType;
var serializer = mapping.Serializer;
// get the parameter
il.Emit(OpCodes.Ldloc, parametersLocal);
il.Emit(OpCodes.Ldc_I4, i);
il.Emit(OpCodes.Ldelem, typeof(IDataParameter));
// look up the best type to use for the parameter
DbType sqlType = LookupDbType(memberType, serializer, dbParameter.DbType);
// give the provider an opportunity to fix up the template parameter (e.g. set UDT type names)
provider.FixupParameter(command, dbParameter, sqlType, memberType, mapping.Member.SerializationMode);
///////////////////////////////////////////////////////////////
// We have a parameter, start handling all of the other types
///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////
// Get the value from the object onto the stack
///////////////////////////////////////////////////////////////
il.Emit(OpCodes.Ldarg_1);
if (type.GetTypeInfo().IsValueType)
{
il.Emit(OpCodes.Unbox_Any, type);
}
///////////////////////////////////////////////////////////////
// Special case support for enumerables. If the type is -1 (our workaround, then call the list parameter method)
///////////////////////////////////////////////////////////////
if (sqlType == DbTypeEnumerable)
{
// we have the parameter and the value as object, add the command
ClassPropInfo.EmitGetValue(type, mapping.PathToMember, il);
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Call, typeof(ListParameterHelper).GetMethod("ConvertListParameter", BindingFlags.Static | BindingFlags.NonPublic));
continue;
}
Label readyToSetLabel = il.DefineLabel();
ClassPropInfo.EmitGetValue(type, mapping.PathToMember, il, readyToSetLabel);
// special conversions for timespan to datetime
if ((sqlType == DbType.Time && dbParameter.DbType != DbType.Time) ||
(dbParameter.DbType == DbType.DateTime || dbParameter.DbType == DbType.DateTime2 || dbParameter.DbType == DbType.DateTimeOffset))
{
IlHelper.EmitLdInt32(il, (int)dbParameter.DbType);
il.Emit(OpCodes.Call, typeof(TypeConverterGenerator).GetMethod("ObjectToSqlDateTime"));
}
// if it's class type, boxed value type (in an object), or nullable, then we have to check for null
if (!memberType.GetTypeInfo().IsValueType || Nullable.GetUnderlyingType(memberType) != null)
{
Label notNull = il.DefineLabel();
// check to see if it's not null
il.Emit(OpCodes.Dup);
il.Emit(OpCodes.Brtrue, notNull);
// it's null. replace the value with DbNull
il.Emit(OpCodes.Pop);
il.Emit(OpCodes.Ldsfld, _dbNullValue);
// value is set to null. ready to set the property.
il.Emit(OpCodes.Br, readyToSetLabel);
// we know the value is not null
il.MarkLabel(notNull);
}
///////////////////////////////////////////////////////////////
// if this is a linq binary, convert it to a byte array
///////////////////////////////////////////////////////////////
if (memberType == TypeHelper.LinqBinaryType)
{
il.Emit(OpCodes.Callvirt, TypeHelper.LinqBinaryToArray);
}
else if (memberType == typeof(XmlDocument))
{
// we are sending up an XmlDocument. ToString just returns the classname, so use the outerxml.
il.Emit(OpCodes.Callvirt, memberType.GetProperty("OuterXml").GetGetMethod());
}
else if (memberType == typeof(XDocument))
{
// we are sending up an XDocument. Use ToString.
il.Emit(OpCodes.Callvirt, memberType.GetMethod("ToString", new Type[] { }));
}
else if (serializer != null && serializer.CanSerialize(memberType, sqlType))
{
il.EmitLoadType(memberType);
StaticFieldStorage.EmitLoad(il, serializer);
il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SerializeParameterValue", BindingFlags.NonPublic | BindingFlags.Static));
}
///////////////////////////////////////////////////////////////
// p.Value = value
///////////////////////////////////////////////////////////////
// push parameter is at top of method
// value is above
il.MarkLabel(readyToSetLabel);
if (memberType == typeof(string))
{
il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SetParameterStringValue", BindingFlags.NonPublic | BindingFlags.Static));
}
else if ((memberType == typeof(Guid?) || (memberType == typeof(Guid))) && dbParameter.DbType != DbType.Guid && command.CommandType == CommandType.StoredProcedure)
{
il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SetParameterGuidValue", BindingFlags.NonPublic | BindingFlags.Static));
}
else
{
il.Emit(OpCodes.Callvirt, _iDataParameterSetValue);
}
}
il.Emit(OpCodes.Ret);
return((Action <IDbCommand, object>)dm.CreateDelegate(typeof(Action <IDbCommand, object>)));
}
public ColumnTreeNode(ColumnMapping columnMapping)
{
_columnMapping = columnMapping;
Initialize();
}
/// <summary>
/// Creates a converter from output parameters to an object of a given type.
/// </summary>
/// <param name="command">The command to analyze for the results.</param>
/// <param name="type">The type to put the values into.</param>
/// <returns>The converter method.</returns>
private static Action <IDbCommand, object> CreateClassOutputParameterConverter(IDbCommand command, Type type)
{
// get the parameters
List <IDataParameter> parameters = command.Parameters.Cast <IDataParameter>().ToList();
// if there are no output parameters, then return an empty method
if (!parameters.Cast <IDataParameter>().Any(p => p.Direction.HasFlag(ParameterDirection.Output)))
{
return (IDbCommand c, object o) => { }
}
;
// create a dynamic method
Type typeOwner = type.HasElementType ? type.GetElementType() : type;
// start creating a dynamic method
var dm = new DynamicMethod(String.Format(CultureInfo.InvariantCulture, "CreateOutputParameters-{0}", Guid.NewGuid()), null, new[] { typeof(IDbCommand), typeof(object) }, typeOwner, true);
var il = dm.GetILGenerator();
var localParameters = il.DeclareLocal(typeof(IDataParameterCollection));
// get the parameters collection from the command into loc.0
il.Emit(OpCodes.Ldarg_0); // push arg.0 (command), stack => [command]
il.Emit(OpCodes.Callvirt, _iDbCommandGetParameters); // call getparams, stack => [parameters]
il.Emit(OpCodes.Stloc, localParameters);
// go through all of the mappings
var mappings = ColumnMapping.MapParameters(type, command, parameters);
for (int i = 0; i < mappings.Count; i++)
{
var finishLabel = il.DefineLabel();
// if there is no parameter for this property, then skip it
var mapping = mappings[i];
if (mapping == null)
{
continue;
}
// if the property is readonly, then skip it
var prop = mapping.Member;
if (!prop.CanSetMember)
{
continue;
}
// if the parameter is not output, then skip it
IDataParameter parameter = parameters[i];
if (parameter == null || !parameter.Direction.HasFlag(ParameterDirection.Output))
{
continue;
}
// push the object on the stack. we will need it to set the value below
il.Emit(OpCodes.Ldarg_1);
// if this is a deep mapping, then get the parent object, and do a null test if its not a value type
if (mapping.IsDeep)
{
ClassPropInfo.EmitGetValue(type, mapping.Prefix, il);
if (!ClassPropInfo.FindMember(type, mapping.Prefix).MemberType.GetTypeInfo().IsValueType)
{
il.Emit(OpCodes.Dup);
var label = il.DefineLabel();
il.Emit(OpCodes.Brtrue, label);
il.Emit(OpCodes.Pop); // pop the object before finishing
il.Emit(OpCodes.Br, finishLabel);
il.MarkLabel(label);
}
}
// get the parameter out of the collection
il.Emit(OpCodes.Ldloc, localParameters);
il.Emit(OpCodes.Ldstr, parameter.ParameterName); // push (parametername)
il.Emit(OpCodes.Callvirt, _iDataParameterCollectionGetItem);
// get the value out of the parameter
il.Emit(OpCodes.Callvirt, _iDataParameterGetValue);
// emit the code to convert the value and set it on the object
TypeConverterGenerator.EmitConvertAndSetValue(il, _dbTypeToTypeMap[parameter.DbType], mapping);
il.MarkLabel(finishLabel);
}
il.Emit(OpCodes.Ret);
return((Action <IDbCommand, object>)dm.CreateDelegate(typeof(Action <IDbCommand, object>)));
}
public StatementColumnMappingPart(ColumnMapping columnMapping, SourceDataRow sourceDataRow)
{
this.columnMapping = columnMapping;
this.sourceDataRow = sourceDataRow;
}
public static string GetSubDataObject(Settings cf, string key, ColumnMapping.ColumnInfos columnInfos)
{
StringBuilder sbReturn = new StringBuilder();
sbReturn.AppendLine(string.Format("\t\tpublic class {0}{1}", cf.UOPrefix, key));
sbReturn.AppendLine("\t\t{");
sbReturn.AppendLine(string.Format("\t\t\tpublic {0}{1}()", cf.UOPrefix, key));
sbReturn.AppendLine("\t\t\t{");
sbReturn.AppendLine("\t\t\t}");
StringBuilder sb_columns = new StringBuilder();
sb_columns.AppendLine("\t\t\t#region Columns");
foreach (ColumnMapping.ColumnInfo c in columnInfos)
{
string decorateColumnName = SQL.Util.ConvertStyle(c.Column, cf.DecorateStyle);
sb_columns.AppendLine(string.Format("\t\t\tprivate {0} _{1};", c.ColumnType, decorateColumnName));
sb_columns.Append(string.Format("\t\t\t[Mapping(\"{0}", c.Column));
sb_columns.AppendLine("\")]");
sb_columns.AppendLine(string.Format("\t\t\tpublic {0} {1}", c.ColumnType, decorateColumnName));
sb_columns.AppendLine("\t\t\t{");
sb_columns.AppendLine("\t\t\t\tget");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\treturn _{0};", decorateColumnName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t\tset");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\t_{0} = value;", decorateColumnName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t}");
}
sb_columns.AppendLine("\t\t\t#endregion");
sbReturn.AppendLine(sb_columns.ToString());
sbReturn.Append("\t\t}");
return sbReturn.ToString();
}
public void Id(int i, ColumnMapping column)
{
idIndex = i;
Column(i, column);
}
// Exports an IEntityCollection to a table in given Document. BookmarkName is the name of the bookmark associated
// with the table.
public static dynamic ExportEntityCollection(string DocumentPath, string BookmarkName, int StartRow, bool BuildColumnHeadings, IEntityCollection collection, List<string> ColumnNames)
{
dynamic functionReturnValue = null;
List<ColumnMapping> mappings = new List<ColumnMapping>();
ColumnMapping map = default(ColumnMapping);
dynamic doc = null;
WordHelper wordProxy = new WordHelper();
// if Word is active then use it
if (!wordProxy.GetWord())
{
if (!wordProxy.CreateWord())
{
throw new System.Exception("Could not start Microsoft Word.");
}
}
wordProxy.OpenDocument(DocumentPath);
doc = wordProxy.Document;
foreach (string name in ColumnNames)
{
map = new ColumnMapping("", name);
map.TableField.DisplayName = name;
mappings.Add(map);
}
functionReturnValue = ExportEntityCollection(doc, BookmarkName, StartRow, BuildColumnHeadings, collection, mappings);
wordProxy.ShowDocument();
return functionReturnValue;
}
public void AddIdColumn(ColumnMapping column)
{
columns.Add(column);
}
public static string GetBusinessObject(Settings cf, ColumnMapping.ColumnInfos cis, string tableName, string PK, string queryRemark)
{
//string decorateTableName = Util.ConvertStyle(tableName, cf.DecorateStyle);
string decorateTableName = tableName;
StringBuilder sb_ret = new StringBuilder();
StringBuilder sbCondition = new StringBuilder();
StringBuilder sbParameter = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
StringBuilder sbPagingCondition = new StringBuilder();
string[] primaryKeys = PK.Split(',');
sbPagingCondition.AppendLine("\t\t\tParameterCollection objectConditions = new ParameterCollection();");
sbPagingCondition.AppendLine("\t\t\tTokenTypes tt = tokenTypes;");
sbPagingCondition.AppendLine("\t\t\tParameterType pt = isAnd ? ParameterType.And : ParameterType.Or;");
foreach (ColumnMapping.ColumnInfo c in cis)
{
string decorateColName = Util.ConvertStyle(c.Column, cf.DecorateStyle);
if (c.ColumnType.Equals("System.String"))
{
sbPagingCondition.AppendLine(string.Format("\t\t\tif (!string.IsNullOrEmpty(parameterObj.{0}))", decorateColName));
sbPagingCondition.AppendLine("\t\t\t{");
sbPagingCondition.AppendLine(string.Format("\t\t\t\tobjectConditions.AddCondition(pt, GetColumnTokenType(tt,{0}{1}.Columns.{2},extTokens), {0}{1}.Columns.{2},parameterObj.{2});", cf.DOPrefix, decorateTableName, decorateColName));
sbPagingCondition.AppendLine("\t\t\t}");
}
else if (c.ColumnType.Equals("System.Int32") || c.ColumnType.Equals("System.Decimal") || c.ColumnType.Equals("System.Single") || c.ColumnType.Equals("System.Int16") || c.ColumnType.Equals("System.Int64"))
{
sbPagingCondition.AppendLine(string.Format("\t\t\tif (parameterObj.{0} != 0 || (extTokens != null && extTokens.ContainsKey({1}{2}.Columns.{0})))", decorateColName, cf.DOPrefix, decorateTableName));
sbPagingCondition.AppendLine("\t\t\t{");
sbPagingCondition.AppendLine(string.Format("\t\t\t\tobjectConditions.AddCondition(pt, GetColumnTokenType(tt,{0}{1}.Columns.{2},extTokens), {0}{1}.Columns.{2},parameterObj.{2});", cf.DOPrefix, decorateTableName, decorateColName));
sbPagingCondition.AppendLine("\t\t\t}");
}
else if (c.ColumnType.Equals("System.DateTime"))
{
sbPagingCondition.AppendLine(string.Format("\t\t\tif (parameterObj.{0} != DateTime.MinValue)", decorateColName));
sbPagingCondition.AppendLine("\t\t\t{");
sbPagingCondition.AppendLine(string.Format("\t\t\t\tobjectConditions.AddCondition(pt, GetColumnTokenType(tt,{0}{1}.Columns.{2},extTokens), {0}{1}.Columns.{2},parameterObj.{2});", cf.DOPrefix, decorateTableName, decorateColName));
sbPagingCondition.AppendLine("\t\t\t}");
}
}
sbCondition.AppendLine("\t\t\tParameterCollection primaryConditions = new ParameterCollection();");
for (int i = 0; i < primaryKeys.Length; i++)
{
string decoratePKName = Util.ConvertStyle(primaryKeys[i], cf.DecorateStyle);
string camelPKName = Util.ConvertStyle(primaryKeys[i], CodeDecorateStyle.Camel);
if (sbParameter.Length > 0) sbParameter.Append(",");
sbParameter.Append(GetColumnType(cis, primaryKeys[i])).Append(" ").Append(camelPKName);
if (sbValues.Length > 0) sbValues.Append(",");
sbValues.Append(camelPKName);
if (i == 0)
{
sbCondition.AppendLine(string.Format("\t\t\tprimaryConditions.AddCondition(ParameterType.Initial, TokenTypes.Equal, {0}{1}.Columns.{2}, {3});", cf.DOPrefix, decorateTableName, decoratePKName, camelPKName));
}
else
{
sbCondition.AppendLine(string.Format("\t\t\tprimaryConditions.AddCondition(ParameterType.And, TokenTypes.Equal, {0}{1}.Columns.{2}, {3});", cf.DOPrefix, decorateTableName, decoratePKName, camelPKName));
}
}
sb_ret.AppendLine("using System;");
sb_ret.AppendLine("using System.Collections.Generic;");
sb_ret.AppendLine("using System.Text;");
sb_ret.AppendLine("using System.Data;");
sb_ret.AppendLine("using OracleDataAccess;");
sb_ret.AppendLine("using OracleDataAccess.Data;");
sb_ret.AppendLine("using DataMapping;");
sb_ret.Append("using ").Append(string.Concat(cf.DataObjectNameSpace, ".Query")).AppendLine(";");
sb_ret.AppendLine("");
sb_ret.Append("namespace ").AppendLine(string.Concat(cf.BusinessObjectNameSpace, ".Query"));
sb_ret.AppendLine("\t{");
if (!string.IsNullOrEmpty(queryRemark))
{
sb_ret.AppendLine("\t/// <summary>");
sb_ret.AppendLine(string.Concat("\t///", "BO Unit:", queryRemark.Replace("\r\n", " ").Replace("\n", " ")));
sb_ret.AppendLine("\t/// </summary>");
}
sb_ret.AppendLine(string.Format("\tpublic class {0}{1}", cf.BOPrefix, decorateTableName));
sb_ret.AppendLine("\t{");
sb_ret.Append("\t\t#region This source code was auto-generated by tool,Version=").AppendLine(Config.Version);
sb_ret.AppendLine(string.Format(@"
//------------------------------------------------------------------------------
// <auto-generated>
// Date time = {1}
// This code was generated by tool,Version={0}.
// Changes to this code may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------", Config.Version, DateTime.Now));
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t#region Condition functions");
if (primaryKeys != null && primaryKeys.Length > 0)
{
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get conditions by primary key.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static ParameterCollection GetConditionsByPrimaryKey({0})", sbParameter.ToString()));
sb_ret.AppendLine("\t\t{");
sb_ret.Append(sbCondition.ToString());
sb_ret.AppendLine("\t\t\treturn primaryConditions;");
sb_ret.AppendLine("\t\t}");
}
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get the tokenType of the column of condition query.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tprivate static TokenTypes GetColumnTokenType(TokenTypes defaultTokenType,{0}{1}.Columns column,Dictionary<{0}{1}.Columns,TokenTypes> extTokens)", cf.DOPrefix, decorateTableName, cf.UOPrefix));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine("\t\t\tif (extTokens != null && extTokens.ContainsKey(column))");
sb_ret.AppendLine("\t\t\t\treturn extTokens[column];");
sb_ret.AppendLine("\t\t\telse");
sb_ret.AppendLine("\t\t\t\treturn defaultTokenType;");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get conditions by object with Multi-TokenType.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static ParameterCollection GetConditionsByObject({0}{1}.{2}{1} parameterObj, bool isAnd, TokenTypes tokenTypes, Dictionary<{0}{1}.Columns, TokenTypes> extTokens)", cf.DOPrefix, decorateTableName, cf.UOPrefix));
sb_ret.AppendLine("\t\t{");
sb_ret.Append(sbPagingCondition.ToString());
sb_ret.AppendLine("\t\t\treturn objectConditions;");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("\t\t#endregion");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t#region Query functions");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get all records.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static {0}{1}.{2}{1} GetAllList({3})", cf.DOPrefix, decorateTableName, cf.UOListPrefix, cf.IsPassConnectionStringToBusiness ? "string connString" : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\t{0}{1} da = new {0}{1}();", cf.DOPrefix, decorateTableName));
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tda.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine("\t\t\treturn da.GetAllList();");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get all records count.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static int GetAllRecordsCount({0})", cf.IsPassConnectionStringToBusiness ? "string connString" : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\t{0}{1} da = new {0}{1}();", cf.DOPrefix, decorateTableName));
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tda.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine("\t\t\treturn da.GetRecordsCount();");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get records count.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static int GetRecordsCount({3}{0}{1}.{2}{1} parameterObj)", cf.DOPrefix, decorateTableName, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\treturn GetRecordsCount({0}parameterObj, true, TokenTypes.Equal,null);", cf.IsPassConnectionStringToBusiness ? "connString, " : ""));
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get records count.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static int GetRecordsCount({3}{0}{1}.{2}{1} parameterObj, bool isAnd, TokenTypes tokenTypes, Dictionary<{0}{1}.Columns, TokenTypes> extTokens)", cf.DOPrefix, decorateTableName, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\t{0}{1} da = new {0}{1}();", cf.DOPrefix, decorateTableName));
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tda.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine(string.Format("\t\t\treturn da.GetRecordsCount(GetConditionsByObject(parameterObj, isAnd, tokenTypes, extTokens));", cf.DOPrefix, decorateTableName));
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get list by object.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static {0}{1}.{2}{1} GetList({4}{0}{1}.{3}{1} parameterObj, bool isAnd, TokenTypes tokenTypes, Dictionary<{0}{1}.Columns, TokenTypes> extTokens)", cf.DOPrefix, decorateTableName, cf.UOListPrefix, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tparameterObj.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine("\t\t\treturn parameterObj.GetList(GetConditionsByObject(parameterObj, isAnd, tokenTypes, extTokens));");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get list by object.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static {0}{1}.{2}{1} GetList({4}{0}{1}.{3}{1} parameterObj)", cf.DOPrefix, decorateTableName, cf.UOListPrefix, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\treturn GetList({0}parameterObj, true, TokenTypes.Equal, null);", cf.IsPassConnectionStringToBusiness ? "connString, " : ""));
sb_ret.AppendLine("\t\t}");
if (primaryKeys != null && primaryKeys.Length > 0)
{
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get object by primary key.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static {0}{1}.{2}{1} GetObject({4}{3})", cf.DOPrefix, decorateTableName, cf.UOPrefix, sbParameter.ToString(), cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\t{0}{1} da = new {0}{1}();", cf.DOPrefix, decorateTableName));
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tda.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine(string.Format("\t\t\t{0}{1}.{2}{1} l = da.GetList(GetConditionsByPrimaryKey({3}));", cf.DOPrefix, decorateTableName, cf.UOListPrefix, sbValues.ToString()));
sb_ret.AppendLine("\t\t\treturn l.Count > 0 ? l[0] : null;");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get paging list.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static PagingResult<{0}{1}.{3}{1}, {0}{1}.{2}{1}> GetPagingList({4}{0}{1}.{3}{1} parameterObj,int pageNumber, int pageSize,string sortBy,bool isAsc, bool isAnd, TokenTypes tokenTypes, Dictionary<{0}{1}.Columns, TokenTypes> extTokens)", cf.DOPrefix, decorateTableName, cf.UOListPrefix, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tparameterObj.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine(string.Format("\t\t\treturn parameterObj.GetPagingList(GetConditionsByObject(parameterObj, isAnd, tokenTypes,extTokens), pageNumber, pageSize, sortBy, isAsc);", cf.DOPrefix, decorateTableName));
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t///<summary>");
sb_ret.AppendLine("\t\t///Get paging list.");
sb_ret.AppendLine("\t\t///</summary>");
sb_ret.AppendLine(string.Format("\t\tpublic static PagingResult<{0}{1}.{3}{1}, {0}{1}.{2}{1}> GetPagingList({4}{0}{1}.{3}{1} parameterObj,int pageNumber, int pageSize,string sortBy,bool isAsc)", cf.DOPrefix, decorateTableName, cf.UOListPrefix, cf.UOPrefix, cf.IsPassConnectionStringToBusiness ? "string connString, " : ""));
sb_ret.AppendLine("\t\t{");
if (cf.IsPassConnectionStringToBusiness)
{
sb_ret.AppendLine("\t\t\tparameterObj.ConnInfo.ConnectionString = connString;");
}
sb_ret.AppendLine(string.Format("\t\t\treturn parameterObj.GetPagingList(GetConditionsByObject(parameterObj, true, TokenTypes.Like,null), pageNumber, pageSize, sortBy, isAsc);", cf.DOPrefix, decorateTableName));
sb_ret.AppendLine("\t\t}");
}
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t#endregion");
//if (primaryKeys != null && primaryKeys.Length > 0)
//{
// sb_ret.AppendLine("");
// sb_ret.AppendLine("\t\t#region Update functions");
// sb_ret.AppendLine("\t\t///<summary>");
// sb_ret.AppendLine("\t\t///Update object by primary key.");
// sb_ret.AppendLine("\t\t///</summary>");
// sb_ret.AppendLine(string.Format("\t\tpublic static bool UpdateObject({0}{1}.{2}{1} obj, {3})", cf.DOPrefix, tableName, cf.UOPrefix, sbParameter.ToString()));
// sb_ret.AppendLine("\t\t{");
// sb_ret.AppendLine(string.Format("\t\t\treturn obj.Update(GetConditionsByPrimaryKey({0}), obj) > 0;", sbValues.ToString()));
// sb_ret.AppendLine("\t\t}");
// sb_ret.AppendLine("");
// sb_ret.AppendLine("\t\t///<summary>");
// sb_ret.AppendLine("\t\t///Update object by primary key(with transation).");
// sb_ret.AppendLine("\t\t///</summary>");
// sb_ret.AppendLine(string.Format("\t\tpublic static bool UpdateObject({0}{1}.{2}{1} obj, {3}, IDbConnection connection, IDbTransaction transaction)", cf.DOPrefix, tableName, cf.UOPrefix, sbParameter.ToString()));
// sb_ret.AppendLine("\t\t{");
// sb_ret.AppendLine(string.Format("\t\t\treturn obj.Update(connection, transaction, GetConditionsByPrimaryKey({0}), obj) > 0;", sbValues.ToString()));
// sb_ret.AppendLine("\t\t}");
// sb_ret.AppendLine("\t\t#endregion");
// sb_ret.AppendLine("");
// sb_ret.AppendLine("\t\t#region Delete functions");
// sb_ret.AppendLine("\t\t///<summary>");
// sb_ret.AppendLine("\t\t///Delete object by primary key.");
// sb_ret.AppendLine("\t\t///</summary>");
// sb_ret.AppendLine(string.Format("\t\tpublic static int Delete({0})", sbParameter.ToString()));
// sb_ret.AppendLine("\t\t{");
// sb_ret.AppendLine(string.Format("\t\t\treturn new {0}{1}().Delete(GetConditionsByPrimaryKey({2}));", cf.DOPrefix, tableName, sbValues.ToString()));
// sb_ret.AppendLine("\t\t}");
// sb_ret.AppendLine("");
// sb_ret.AppendLine("\t\t///<summary>");
// sb_ret.AppendLine("\t\t///Delete object by primary key(with transation).");
// sb_ret.AppendLine("\t\t///</summary>");
// sb_ret.AppendLine(string.Format("\t\tpublic static int Delete({0}, IDbConnection connection, IDbTransaction transaction)", sbParameter.ToString()));
// sb_ret.AppendLine("\t\t{");
// sb_ret.AppendLine(string.Format("\t\t\treturn new {0}{1}().Delete(connection, transaction, GetConditionsByPrimaryKey({2}));", cf.DOPrefix, tableName, sbValues.ToString()));
// sb_ret.AppendLine("\t\t}");
// sb_ret.AppendLine("\t\t#endregion");
// sb_ret.AppendLine("");
//}
sb_ret.AppendLine("\t\t#endregion");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t#region User extensions");
sb_ret.AppendLine("");
sb_ret.AppendLine("\t\t#endregion");
sb_ret.AppendLine("\t}");
sb_ret.AppendLine("}");
return sb_ret.ToString();
}
internal static string AlterTableAddColumn(string tableName, string columnName, ColumnMapping columnMapping) =>
string.Format("ALTER TABLE \"{0}\" ADD COLUMN {1}", tableName, SqlDecl(columnName, columnMapping));
private static string GetColumnType(ColumnMapping.ColumnInfos cis, string columnName)
{
foreach (ColumnMapping.ColumnInfo c in cis)
{
if (c.Column.Trim().ToLower() == columnName.Trim().ToLower())
{
return c.ColumnType;
}
}
return "System.String";
}
static string GetDatabaseType(ColumnMapping column)
{
var dbType = DatabaseTypeConverter.AsDbType(column.Type);
switch (dbType)
{
case DbType.AnsiString:
return("varchar" + GetLength(column));
case DbType.AnsiStringFixedLength:
return("char");
case DbType.Binary:
return("varbinary(max)");
case DbType.Byte:
return("tinyint");
case DbType.Boolean:
return("bit");
case DbType.Currency:
return("decimal");
case DbType.Date:
return("date");
case DbType.DateTime:
return("datetime");
case DbType.Decimal:
return("decimal");
case DbType.Double:
return("float");
case DbType.Guid:
return("uniqueidentifier");
case DbType.Int16:
return("smallint");
case DbType.Int32:
return("int");
case DbType.Int64:
return("bigint");
case DbType.SByte:
break;
case DbType.Single:
break;
case DbType.StringFixedLength:
return("nchar");
case DbType.String:
return("nvarchar" + GetLength(column));
case DbType.Time:
return("time");
case DbType.DateTime2:
case DbType.DateTimeOffset:
return("datetimeoffset");
}
return("nvarchar(max)");
}
public ColumnMappingViewModel(ColumnMapping columnMapping,
TableMappingViewModel tableMappingViewModel)
{
this.tableMappingViewModel = tableMappingViewModel;
this.columnMapping = columnMapping;
}
// Exports a collection to a table in given Document. BookmarkName is the name of the bookmark associated
// with the table.
public static dynamic Export(dynamic Document, string BookmarkName, int StartRow, bool BuildColumnHeadings, IVisualCollection collection, List<string> ColumnNames)
{
List<ColumnMapping> mappings = new List<ColumnMapping>();
ColumnMapping map = default(ColumnMapping);
FieldDefinition fd = default(FieldDefinition);
foreach (string name in ColumnNames)
{
fd = collection.GetFieldDefinition(name);
map = new ColumnMapping("", name);
if (fd == null)
{
map.TableField.DisplayName = name;
}
else
{
map.TableField = fd;
}
mappings.Add(map);
}
return Export(Document, BookmarkName, StartRow, BuildColumnHeadings, collection, mappings);
}
public TParent Add(ColumnMapping column)
{
columns.Add(column);
return(parent);
}
// Exports an IEntityCollection to a table in given Document. BookmarkName is the name of the bookmark associated
// with the table.
public static dynamic ExportEntityCollection(dynamic Document, string BookmarkName, int StartRow, bool BuildColumnHeadings, IEntityCollection collection, List<string> ColumnNames)
{
List<ColumnMapping> mappings = new List<ColumnMapping>();
ColumnMapping map = default(ColumnMapping);
foreach (string name in ColumnNames)
{
map = new ColumnMapping("", name);
map.TableField.DisplayName = name;
mappings.Add(map);
}
return ExportEntityCollection(Document, BookmarkName, StartRow, BuildColumnHeadings, collection, mappings);
}
///<summary>Appends a string to create a column to a string builder.</summary>
protected virtual void AppendColumn(StringBuilder sql, ColumnMapping column)
{
if (sql == null) throw new ArgumentNullException("sql");
if (column == null) throw new ArgumentNullException("column");
var nullable = ((ValueColumn)column.Column).AllowNulls;
sql.AppendFormat("\t{0,-30}\t{1,-20}\t{2}",
column.SqlName.EscapeSqlIdentifier(),
GetSqlType(column.Column.DataType),
nullable ? "NULL" : "NOT NULL"
);
}
public static string GetDataObject(Settings cf, ColumnMapping.ColumnInfos ci, string tableName, string PK, string sql, string queryRemark)
{
//string decorateTableName = Util.ConvertStyle(tableName, cf.DecorateStyle);
if (string.IsNullOrEmpty(sql))
throw new Exception("Empty sql.");
else
{
sql = sql.Replace("\r\n", " ").Replace('\n', ' ');
}
string decorateTableName = tableName;
string[] primaryKeys = PK.Split(',');
StringBuilder sb_ret = new StringBuilder();
sb_ret.AppendLine(string.Format(@"
//------------------------------------------------------------------------------
// <auto-generated>
// Date time = {1}
// This code was generated by tool,Version={0}.
// Changes to this code may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------", Config.Version, DateTime.Now));
sb_ret.AppendLine("using System;");
sb_ret.AppendLine("using System.Collections.Generic;");
sb_ret.AppendLine("using System.Text;");
sb_ret.AppendLine("using OracleDataAccess.Data;");
sb_ret.AppendLine("using DataMapping;");
sb_ret.AppendLine("");
sb_ret.Append("namespace ").AppendLine(string.Concat(cf.DataObjectNameSpace, ".Query"));
sb_ret.AppendLine("{");
if (!string.IsNullOrEmpty(queryRemark))
{
sb_ret.AppendLine("\t/// <summary>");
sb_ret.AppendLine(string.Concat("\t///", "DO Unit:", queryRemark.Replace("\r\n", " ").Replace("\n", " ")));
sb_ret.AppendLine("\t/// </summary>");
}
sb_ret.AppendLine(string.Format("\tpublic class {1}{0} : DOBase<{1}{0}.{2}{0}, {1}{0}.{3}{0}>", decorateTableName, cf.DOPrefix, cf.UOPrefix, cf.UOListPrefix));
sb_ret.AppendLine("\t{");
sb_ret.AppendLine("\t\tpublic enum Columns");
sb_ret.AppendLine("\t\t{");
StringBuilder sb_columns = new StringBuilder();
sb_columns.AppendLine("\t\t\t#region Columns");
StringBuilder sbPks = new StringBuilder();
foreach (string p in primaryKeys)
{
if (sbPks.Length > 0) sbPks.Append(", ");
sbPks.Append("\"").Append(p).Append("\"");
}
sbPks.Insert(0, "new string[] {");
sbPks.Append("}");
foreach (ColumnMapping.ColumnInfo c in ci)
{
bool isPrimaryKey = false;
foreach (string s in primaryKeys)
{
if (c.Column.Trim().ToLower().Equals(s.Trim().ToLower()))
{
isPrimaryKey = true;
break;
}
}
string decorateColName = Util.ConvertStyle(c.Column, cf.DecorateStyle);
StringBuilder sbColumnInfo = new StringBuilder();
if (isPrimaryKey)
{
sbColumnInfo.Append("Primary Key,");
}
//sbColumnInfo.Append("Database Type:").Append(c.ColumnType);
//if (!string.IsNullOrEmpty(c.max_length))
//{
// sbColumnInfo.Append(",Max Length:").Append(c.max_length);
//}
//sbColumnInfo.Append(",Is Nullable:").Append(c.is_nullable);
//if (!string.IsNullOrEmpty(c.column_default))
//{
// sbColumnInfo.Append(",Default Value:").Append(c.column_default);
//}
//foreach (doRemarks.uoRemarks r in listRemarks)
//{
// if (r.column_name.Equals(c.column_name))
// {
// sbColumnInfo.Append(",Remark:").Append(r.remark);
// break;
// }
//}
//sb_ret.AppendLine("\t\t\t/// <summary>");
//sb_ret.Append("\t\t\t///").AppendLine(sbColumnInfo.ToString());
//sb_ret.AppendLine("\t\t\t/// </summary>");
sb_ret.Append("\t\t\t").Append(decorateColName).AppendLine(",");
sb_columns.AppendLine(string.Format("\t\t\tprivate {0} _{1};", c.ColumnType, decorateColName));
//sb_columns.AppendLine("\t\t\t/// <summary>");
//sb_columns.Append("\t\t\t///").AppendLine(sbColumnInfo.ToString());
//sb_columns.AppendLine("\t\t\t/// </summary>");
sb_columns.Append(string.Format("\t\t\t[Mapping(\"{0}\"", c.Column));
const string specailAttr = ",\"{0}\"";
if (isPrimaryKey)
{
sb_columns.AppendFormat(specailAttr, ",un-insert,un-update");
}
else if (cf.UnInsertAndUnUpdate.ToLower().IndexOf(decorateColName.ToLower()) >= 0)
{
sb_columns.AppendFormat(specailAttr, ",un-insert,un-update");
}
else if (cf.UnInsert.ToLower().IndexOf(decorateColName.ToLower()) >= 0)
{
sb_columns.AppendFormat(specailAttr, ",un-insert");
}
else if (cf.UnUpdate.ToLower().IndexOf(decorateColName.ToLower()) >= 0)
{
sb_columns.AppendFormat(specailAttr, ",un-update");
}
sb_columns.AppendLine(")]");
sb_columns.AppendLine(string.Format("\t\t\tpublic {0} {1}", c.ColumnType, decorateColName));
sb_columns.AppendLine("\t\t\t{");
sb_columns.AppendLine("\t\t\t\tget");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\treturn _{0};", decorateColName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t\tset");
sb_columns.AppendLine("\t\t\t\t{");
sb_columns.AppendLine(string.Format("\t\t\t\t\t_{0} = value;", decorateColName));
sb_columns.AppendLine("\t\t\t\t}");
sb_columns.AppendLine("\t\t\t}");
}
sb_columns.AppendLine("\t\t\t#endregion");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine(string.Format("\t\tpublic {1}{0}()", decorateTableName, cf.DOPrefix));
sb_ret.AppendLine("\t\t{");
if (primaryKeys != null && primaryKeys.Length > 0)
{
sb_ret.AppendLine(string.Format("\t\t\tConnInfo = new ConnectionInformation(\"{0}\", {1}, {2});", sql, cf.ConnectionKey, sbPks.ToString()));
}
else
{
sb_ret.AppendLine(string.Format("\t\t\tConnInfo = new ConnectionInformation(\"{0}\", {1});", sql, cf.ConnectionKey));
}
sb_ret.AppendLine("\t\t\tConnInfo.IsSqlSentence = true;");
sb_ret.AppendLine("\t\t}");
if (!string.IsNullOrEmpty(queryRemark))
{
sb_ret.AppendLine("\t\t/// <summary>");
sb_ret.AppendLine(string.Concat("\t\t///", queryRemark.Replace("\r\n", " ").Replace("\n", " ")));
sb_ret.AppendLine("\t\t/// </summary>");
}
sb_ret.AppendLine(string.Format("\t\tpublic class {1}{0} : UOBase<{1}{0}, {2}{0}>", decorateTableName, cf.UOPrefix, cf.UOListPrefix));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(sb_columns.ToString());
sb_ret.AppendLine(string.Format("\t\t\tpublic {1}{0}()", decorateTableName, cf.UOPrefix));
sb_ret.AppendLine("\t\t\t{");
sb_ret.AppendLine(string.Format("\t\t\t\tConnInfo = new {0}{1}().ConnInfo;", cf.DOPrefix, decorateTableName));
sb_ret.AppendLine("\t\t\t}");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine(string.Format("\t\tpublic class {1}{0} : CommonLibrary.ObjectBase.ListBase<{2}{0}>", decorateTableName, cf.UOListPrefix, cf.UOPrefix));
sb_ret.AppendLine("\t\t{");
sb_ret.AppendLine(string.Format("\t\t\tpublic {1}{0}()", decorateTableName, cf.UOListPrefix));
sb_ret.AppendLine("\t\t\t{");
sb_ret.AppendLine("\t\t\t}");
sb_ret.AppendLine("\t\t}");
sb_ret.AppendLine("\t}");
sb_ret.AppendLine("}");
return sb_ret.ToString();
}
///<summary>Appends a string to create a foreign key column to a string builder.</summary>
protected virtual void AppendForeignKey(StringBuilder sql, ColumnMapping column, IEnumerable<SchemaMapping> parentSchemas)
{
if (sql == null) throw new ArgumentNullException("sql");
if (column == null) throw new ArgumentNullException("column");
var fkc = (ForeignKeyColumn)column.Column;
var foreignTable = parentSchemas.First(s => s.Schema == fkc.ForeignSchema);
//[Caller] UNIQUEIDENTIFIER NULL DEFAULT(NULL) REFERENCES Data.MasterDirectory(Id),
sql.AppendFormat(CultureInfo.InvariantCulture, "\t{0,-30}\t{1,-20}\t{2}\tREFERENCES {3}({4})",
column.SqlName.EscapeSqlIdentifier(),
GetSqlType(foreignTable.PrimaryKey.Column.DataType),
fkc.AllowNulls ? "NULL" : "NOT NULL",
QualifyTable(foreignTable),
foreignTable.PrimaryKey.SqlName.EscapeSqlIdentifier()
);
}
///<summary>Appends a string to create a primary key column to a string builder.</summary>
protected virtual void AppendPrimaryKey(StringBuilder sql, ColumnMapping column)
{
if (sql == null) throw new ArgumentNullException("sql");
if (column == null) throw new ArgumentNullException("column");
sql.AppendFormat("\t{0,-30}\t{1,-20}\tNOT NULL\tROWGUIDCOL\tPRIMARY KEY DEFAULT(newid())",
column.SqlName.EscapeSqlIdentifier(),
"UNIQUEIDENTIFIER"
);
}
/// <summary>
/// Initializes a new instance of the ListDataReader class.
/// </summary>
/// <param name="collection">A list to load.</param>
/// <param name="mapping">The column mapping with properties.</param>
public ListDataReader(IEnumerable <T> collection, ColumnMapping mapping) : base(collection)
{
columnMapping = mapping ?? new ColumnMapping();
}