private static List <string> GetExistingExDatabaseTableNameAndStructure(List <string> exChangeDbTableList, Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto)
{
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
string QueryExchangeTable = string.Format(@" select distinct Left( sysobj .Name , 50 ) AS [ExChangeTableName] from sysObjects as sysobj
inner join dbo.sysColumns as sysColumn on sysColumn.ID = sysobj .ID
where sysobj .name like '{0}%' and sysColumn.Name in ( '{1}', '{2}' ,'{3}' ,'{4}' ,'{5}')",
PLMConstantString.EX_PLM_Import_Prefix,
PLMConstantString.ExchangeRowDataERPFlagColumn,
PLMConstantString.ExchangeRowDataPLMFlagColumn,
PLMConstantString.ExchangeRowDataERPExportDateTimeColumn,
PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn
);
CLROutput.Output("QueryExchangeTable=" + QueryExchangeTable);
exChangeDbTableList = DataAcessHelper.GetDataTableQueryResult(conn, QueryExchangeTable).AsEnumerable().Select(o => (o["ExChangeTableName"] as string).Trim().ToLowerInvariant()).ToList();
foreach (string exchangeTable in exChangeDbTableList)
{
List <PdmEntityColumnClrDto> exEntituyColumn = PLMSEntityClrBL.GetExchangeDatabaseTableColumnDto(conn, exchangeTable);
dictExchangeTableColumnDto.Add(exchangeTable, exEntituyColumn);
}
}
return(exChangeDbTableList);
}
private static object InsertNewExportUserDefineEntity(string folderId, SqlConnection conn, string newEntityCode)
{
string sqlInsertPdmEntity = string.Format(@"
Insert into dbo.PdmEntity
(
EntityCode,
FolderID,
IsImport
) " +
@" values
(
'{0}',{1},{2}
)
", newEntityCode, folderId, 1);
using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntity, conn))
{
CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntity);
insertNewentity.ExecuteNonQuery();
}
string newEntityIdQuery = @"select EntityID from pdmEntity where entitycode='" + newEntityCode + "' and EntityID > 3000 ";
using (SqlCommand cmdGetNewEntityId = new SqlCommand(newEntityIdQuery, conn))
{
return(cmdGetNewEntityId.ExecuteScalar());;
}
}
private static object InsertNewEntityWithReturnNewIdentityId(SqlConnection conn, string sqlInsertpdmUserDefineEntityRow, SqlCommand cmdInsertNewentity)
{
cmdInsertNewentity.Connection = conn;
cmdInsertNewentity.CommandText = sqlInsertpdmUserDefineEntityRow;
CLROutput.Output("sqlInsertpdmUserDefineEntityRow" + sqlInsertpdmUserDefineEntityRow);
cmdInsertNewentity.ExecuteNonQuery();
cmdInsertNewentity.CommandText = "SELECT SCOPE_IDENTITY()";
object rowID = cmdInsertNewentity.ExecuteScalar();
CLROutput.Output("SELECT SCOPE_IDENTITY( ) RowId=" + rowID);
return(rowID);
}
private static void InsertPdmUserDefineColunmDto(PdmEntityColumnClrDto pdmEntityColumnClrDto, SqlConnection conn)
{
int bitPrimaryKey = 0;
if (pdmEntityColumnClrDto.IsPrimaryKey.HasValue && pdmEntityColumnClrDto.IsPrimaryKey.Value)
{
bitPrimaryKey = 1;
}
int NBDecimal = 0;
if (pdmEntityColumnClrDto.Nbdecimal.HasValue)
{
NBDecimal = pdmEntityColumnClrDto.Nbdecimal.Value;
}
string sqlInsertPdmEntityColumn = string.Format(@"
Insert into dbo.pdmUserDefineEntityColumn
(
EntityID,
ColumnName,
IsPrimaryKey,
UIControlType,
NBDecimal
) " +
@" values
(
{0},'{1}',{2},{3},{4}
)
", pdmEntityColumnClrDto.EntityId, pdmEntityColumnClrDto.ColumnName, bitPrimaryKey, pdmEntityColumnClrDto.UicontrolType, NBDecimal);
using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntityColumn, conn))
{
CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntityColumn);
insertNewentity.ExecuteNonQuery();
}
}
private static void InsertRowCellValue(DataRow row, SqlConnection conn, PdmEntityClrDto aPdmEntityClrDto, object rowID)
{
foreach (PdmEntityColumnClrDto columnClrDto in aPdmEntityClrDto.Columns)
{
SqlParameter aRowIdPameter = new SqlParameter("@RowID", SqlDbType.Int);
SqlParameter aUserDefineColumnIdIdPameter = new SqlParameter("@UserDefineEntityColumnID", SqlDbType.Int);
SqlParameter aValueIdPameter = new SqlParameter("@ValueID", SqlDbType.Int);
SqlParameter aValueDatePameter = new SqlParameter("@ValueDate", SqlDbType.DateTime);
SqlParameter aValueTextPameter = new SqlParameter("@ValueText", SqlDbType.NVarChar);
aRowIdPameter.Value = rowID;
aUserDefineColumnIdIdPameter.Value = columnClrDto.UserDefineEntityColumnID;
aValueIdPameter.Value = System.Data.SqlTypes.SqlInt32.Null;
aValueDatePameter.Value = System.Data.SqlTypes.SqlDateTime.Null;
aValueTextPameter.Value = string.Empty;
// CLROutput.Output("SELECT SCOPE_IDENTITY( ) RowId=" + rowID);
CLROutput.Output("columnClrDto.ColumnName" + columnClrDto.ColumnName + "row[columnClrDto.ColumnName]" + row[columnClrDto.ColumnName]);
if (columnClrDto.UicontrolType == (int)EmControlType.DDL)
{
aValueIdPameter.Value = Converter.ToDDLSqlInt32(row[columnClrDto.ColumnName]);
}
else if (columnClrDto.UicontrolType == (int)EmControlType.DATE)
{
aValueDatePameter.Value = Converter.ToSqlDateTime(row[columnClrDto.ColumnName]);
}
else if (columnClrDto.UicontrolType == (int)EmControlType.CHKBOX)
{
//From Datable
SqlBoolean boolValue = Converter.ToSqlBoolean(row[columnClrDto.ColumnName]);
if (!boolValue.IsNull)
{
if (boolValue.Value)
{
aValueTextPameter.Value = "true";
}
else
{
aValueTextPameter.Value = "false";
}
}
}
else
{
aValueTextPameter.Value = Converter.ToString(row[columnClrDto.ColumnName]);
}
string sqlpdmUserDefineEntityRowValue = @"
Insert into dbo.pdmUserDefineEntityRowValue
(
RowID,
UserDefineEntityColumnID,
ValueID,
ValueDate,
ValueText
) " +
@" values
(
@RowID,
@UserDefineEntityColumnID,
@ValueID,
@ValueDate,
@ValueText
)";
using (SqlCommand cmdInsertCell = new SqlCommand())
{
cmdInsertCell.Connection = conn;
cmdInsertCell.CommandText = sqlpdmUserDefineEntityRowValue;
cmdInsertCell.Parameters.Add(aRowIdPameter);
cmdInsertCell.Parameters.Add(aUserDefineColumnIdIdPameter);
cmdInsertCell.Parameters.Add(aValueIdPameter);
cmdInsertCell.Parameters.Add(aValueDatePameter);
cmdInsertCell.Parameters.Add(aValueTextPameter);
cmdInsertCell.ExecuteNonQuery();
}
}
}
private static void TransferNewRecordFromERPToPLM(List <PdmEntityClrDto> plmImportEntityDtoList)
{
foreach (PdmEntityClrDto aPdmEntityClrDto in plmImportEntityDtoList)
{
string exchangeTableName = aPdmEntityClrDto.EntityCode;
DataTable newRecordsTable = null;
//Get Ex-change new Row Data
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
string queryERPNewRecord = string.Format(@" select * from {0} where {1}={2} ", aPdmEntityClrDto.EntityCode, PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.New);
newRecordsTable = DataAcessHelper.GetDataTableQueryResult(conn, queryERPNewRecord);
// CLROutput.SendDataTable(newRecordsTable);
}
// insert to PLM
Dictionary <object, DataRow> dictNewRowIdDataRow = new Dictionary <object, DataRow>();
List <PdmEntityColumnClrDto> userDefineColumns = aPdmEntityClrDto.Columns;
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
conn.Open();
foreach (DataRow row in newRecordsTable.Rows)
{
object rowId = InsertPdmUserDefineEntityRow(aPdmEntityClrDto, row, conn);
//CLROutput.Output("rowid=" + rowId);
dictNewRowIdDataRow.Add(rowId, row);
}
}
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
//select CURRENT_TIMESTAMP
// SELECT {fn NOW()}
// CURRENT_TIMESTAMP
// SELECT GETDATE() ",
string erpPKColumn = aPdmEntityClrDto.Columns.Where(o => o.IsPrimaryKey.HasValue && o.IsPrimaryKey.Value).First().ColumnName;
foreach (object rowId in dictNewRowIdDataRow.Keys)
{
DataRow row = dictNewRowIdDataRow[rowId];
string updateExchangeStatus = string.Format(@"
update {0}
set {1}={2} ,
{3}=CURRENT_TIMESTAMP ,
{4}='{5}'
where {6}='{7}'
and {8}={9}
",
aPdmEntityClrDto.EntityCode,
PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.NoChange,
PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn, rowId,
erpPKColumn, GetOnePrimaryKeyColumnValue(row, aPdmEntityClrDto.Columns),
PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.New);
CLROutput.Output("updateExchangeStatus=" + updateExchangeStatus);
SqlCommand cmdUpdateExTableFromNewToNochnage = new SqlCommand(updateExchangeStatus, conn);
cmdUpdateExTableFromNewToNochnage.ExecuteNonQuery();
}
}
// Update Ex-change Tabl
}
}
//?????????? TODO list....
private static void InsertEntityWithFKRelaship(PdmEntityClrDto EntityWithFKEntityDto, string EntityWithFKEntityForeighKeyColumn, string masterEntityTableName, SqlConnection conn)
{
// EntityWithFKEntityDto.EntityId, Both PrimaryKeyColumn.UserDefineEntityColumnID and MasterEntityColumn.UserDefineEntityColumnID are in the same EntityWithFKEntityDto
var PrimaryKeyColumn = EntityWithFKEntityDto.Columns.Where(o => o.IsPrimaryKey.HasValue && o.IsPrimaryKey.Value).First();;
var MasterEntityColumn = EntityWithFKEntityDto.Columns.Where(o => o.ColumnName.ToLowerInvariant() == EntityWithFKEntityForeighKeyColumn.ToLowerInvariant()).First();
string selectRelationEntity = string.Format(@" select EntityID from dbo.pdmentity
where EntityWithFKEntityID={0}
and ChildEntityColumnID={1}
and MasterEntityColumnID={2}",
EntityWithFKEntityDto.EntityId,
PrimaryKeyColumn.UserDefineEntityColumnID,
MasterEntityColumn.UserDefineEntityColumnID
);
SqlCommand cmdGetRelationEntityId = new SqlCommand(selectRelationEntity, conn);
object relationEntityId = cmdGetRelationEntityId.ExecuteScalar();
CLROutput.Output("cmdGetRelationEntityId=" + cmdGetRelationEntityId);
if (relationEntityId == null)
{
string insertNewRaltionEntity = string.Format(@" insert into dbo.pdmentity
(
EntityCode,
IsImport,
EntityType,
EntityWithFKEntityID,
MasterEntityColumnID ,
ChildEntityColumnID,
IsRelationEntity
)
values
(
'{0}',
{1},
{2},
{3},
{4},
{5},
{6}
) ",
"Import_Relation_" + masterEntityTableName + "_" + EntityWithFKEntityDto.EntityCode,
1,
(int)EmEntityType.RelationFKEntity,
EntityWithFKEntityDto.EntityId,
MasterEntityColumn.UserDefineEntityColumnID,
PrimaryKeyColumn.UserDefineEntityColumnID,
1
);
SqlCommand cmdInsertRelationEntityId = new SqlCommand(insertNewRaltionEntity, conn);
CLROutput.Output("insertNewRaltionEntity=" + insertNewRaltionEntity);
cmdInsertRelationEntityId.ExecuteNonQuery();
}
// need to add new RelatipnEntity
}
private static void UpdateForeignKeyRelationFromERPToPLM(List <PdmEntityClrDto> plmImportEntityDtoList)
{
List <string> exchangeTableNameList = plmImportEntityDtoList.Select(o => o.EntityCode).ToList();
Dictionary <string, PdmEntityClrDto> dictAllImportEntity = plmImportEntityDtoList.ToDictionary(o => o.EntityCode.ToLowerInvariant(), o => o);
string queryForeignTable = @" SELECT distinct OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
PT.NAME FIELD_NAME,
OBJECT_NAME(REFERENCED_OBJECT_ID) REFTABLE_NAME,
FT.NAME REFFIELD_NAME
FROM SYS.FOREIGN_KEY_COLUMNS FKC
JOIN SYS.COLUMNS PT
ON FKC.PARENT_OBJECT_ID = PT.OBJECT_ID
AND FKC.PARENT_COLUMN_ID = PT.COLUMN_ID
JOIN SYS.COLUMNS FT
ON FKC.REFERENCED_OBJECT_ID = FT.OBJECT_ID
AND FKC.REFERENCED_COLUMN_ID = FT.COLUMN_ID ";
// WHERE (OBJECT_NAME(FKC.parent_object_id))";
string tableInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(exchangeTableNameList, "OBJECT_NAME(FKC.parent_object_id)", false);
queryForeignTable = queryForeignTable + " WHERE " + tableInClause;
DataTable foreignKeyTable = null;
using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
{
conn.Open();
foreignKeyTable = DataAcessHelper.GetDataTableQueryResult(conn, queryForeignTable);
CLROutput.SendDataTable(foreignKeyTable);
}
foreach (DataRow fkDataRow in foreignKeyTable.Rows)
{
string childEntityEntityName = (fkDataRow["TABLE_NAME"] as string).Trim().ToLowerInvariant();
string childFkColumnName = (fkDataRow["FIELD_NAME"] as string).Trim().ToLowerInvariant();
string masterEntityTableName = (fkDataRow["REFTABLE_NAME"] as string).Trim().ToLowerInvariant();;
string masterKeyName = (fkDataRow["REFFIELD_NAME"] as string).Trim().ToLowerInvariant();
// clear old relationship
int childEntityId = dictAllImportEntity[childEntityEntityName].EntityId;
int masterEntityId = dictAllImportEntity[masterEntityTableName].EntityId;
// clear old relationship
using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
{
conn.Open();
PdmEntityClrDto EntityWithFKEntityDto = dictAllImportEntity[childEntityEntityName];
// need to updat FK control Type
foreach (var column in EntityWithFKEntityDto.Columns)
{
if (column.ColumnName.ToLowerInvariant() == childFkColumnName.ToLowerInvariant())
{
string updatepdmUserDefineEntityColumn = string.Format(@"update pdmUserDefineEntityColumn
set UIControlType={0},
FKEntityID ={1}
where UserDefineEntityColumnID ={2}", (int)EmControlType.DDL, masterEntityId, column.UserDefineEntityColumnID);
SqlCommand cmdupdate = new SqlCommand(updatepdmUserDefineEntityColumn, conn);
CLROutput.Output("updatepdmUserDefineEntityColumn=" + updatepdmUserDefineEntityColumn);
cmdupdate.ExecuteNonQuery();
}
}
InsertEntityWithFKRelaship(EntityWithFKEntityDto, childFkColumnName, masterEntityTableName, conn);
}
}
}