// will return new RowID
private static object InsertPdmUserDefineEntityRow(PdmEntityClrDto aPdmEntityClrDto, DataRow row, SqlConnection conn)
{
//PdmEntityColumnClrDto afirstPdmEntityColumnClrDto = userDefineColumns[0];
List <PdmEntityColumnClrDto> userDefineColumns = aPdmEntityClrDto.Columns;
int entityID = aPdmEntityClrDto.EntityId;
// Get keyValue
string keyCombineValue = GetOnePrimaryKeyColumnValue(row, userDefineColumns);
// string sqlInsertpdmUserDefineEntityRow = string.Format(@"
//
// Insert into dbo.pdmUserDefineEntityRow
// (
// EntityID,
// TextValue
//
//
// ) " +
// @" values
// (
// {0},'{1}'
//
// )
// ", entityID, keyCombineValue);
string sqlInsertpdmUserDefineEntityRow = string.Format(@"
Insert into dbo.pdmUserDefineEntityRow
(
EntityID,
) " +
@" values
(
{0},'{1}'
)
", entityID);
object rowID;
using (SqlCommand cmdInsertNewentity = new SqlCommand())
{
rowID = InsertNewEntityWithReturnNewIdentityId(conn, sqlInsertpdmUserDefineEntityRow, cmdInsertNewentity);
InsertRowCellValue(row, conn, aPdmEntityClrDto, rowID);
}
return(rowID);
}
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();
}
}
}
//?????????? 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);
}
}
}
