// 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); } } }