private void CopyValueToLocalizedTable(ConfirmedLocalizedTable confirmedLocalizedTable) { List<string> columnsToLocalize = confirmedLocalizedTable.ColumnsToLocalize; try { var localizedColumnPrimaryKeyName = get_LOCALIZEDColumnPrimaryKeyName(confirmedLocalizedTable.TableName); columnsToLocalize.Add(localizedColumnPrimaryKeyName); //Add coresponding ID Column //Add data to %_LOCALIZED Table if (!AddDataTo_LocalizedTable(confirmedLocalizedTable)) { listViewFailTables.Items.Add(confirmedLocalizedTable.TableName); return; } //} listViewSuccessTables.Items.Add(confirmedLocalizedTable.TableName); } catch (Exception ex) { MessageBox.Show(ex.Message + @" Er 1"); listViewFailTables.Items.Add(confirmedLocalizedTable.TableName); } }
private static List<ConfirmedLocalizedTable> GetConfirmedLocalizedTablesList(string pathToXls) { DataTable confirmedDataTable = ExcelManipulation.ExcelToDataTable(pathToXls, "Confirmed"); var confirmedLocalizedTablesList = new List<ConfirmedLocalizedTable>(); foreach (DataRow row in confirmedDataTable.Rows) { var confirmedLocalizedTable = new ConfirmedLocalizedTable { TableName = row["Table Name"].ToString().Trim() }; var columnsToLocalize = new List<string>(); if (string.Equals(row["Note"].ToString().Trim(), "Localize", StringComparison.CurrentCultureIgnoreCase)) { for (var i = 1; i <= 8; i++) { string columnName = "Column " + i; if (!string.IsNullOrWhiteSpace(row[columnName].ToString())) { columnsToLocalize.Add(row[columnName].ToString().Trim()); } else { break; } } confirmedLocalizedTable.ColumnsToLocalize = columnsToLocalize; confirmedLocalizedTablesList.Add(confirmedLocalizedTable); } } return confirmedLocalizedTablesList; }
private List<List<string>> getEN_AUData(ConfirmedLocalizedTable confirmedLocalizedTable) { string query = @"SELECT "; foreach (var item in confirmedLocalizedTable.ColumnsToLocalize) { query += string.Format(@"{0}, ", item); } var tablePrimaryKey = GetColumnPrimaryKeyName(confirmedLocalizedTable.TableName); foreach (var item in tablePrimaryKey) { query += item + ", "; } query = query.Substring(0, query.Length - 2) + string.Format(@" FROM {0}", confirmedLocalizedTable.TableName); //Old: {0}_LOCALIZED WHERE var command = new SqlCommand(query, _connection); var tableListTable = new DataTable("TableList"); var dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(tableListTable); var dataEnEuList = new List<List<string>>(); if (tableListTable.Rows != null && tableListTable.Rows.Count > 0) { foreach (DataRow row in tableListTable.Rows) { var columnData = new List<string>(); for (int i = 0; i < tableListTable.Columns.Count; i++) { columnData.Add(row.ItemArray[i].ToString()); } dataEnEuList.Add(columnData); } } return dataEnEuList; }
private void UpdateTranslatedData() { var confirmedLocalizedTablesList = GetConfirmedLocalizedTablesList(tbXLSFilePath.Text); if (_connection == null || _connection.State != ConnectionState.Open) return; try { foreach (var itemTablesList in confirmedLocalizedTablesList) { _currentWorkingTable = itemTablesList; var dataEnEuList = getEN_AUData(itemTablesList); var tablePrimaryKey = GetColumnPrimaryKeyName(itemTablesList.TableName); List<string> supportedLanguage = GetSupportedLaguages(); foreach (var itemSupportedLanguage in supportedLanguage) { var query = string.Format(@"UPDATE {0}_LOCALIZED SET ", itemTablesList.TableName); foreach (var itemColumnsToLocalize in itemTablesList.ColumnsToLocalize) { //Avoid backspace in Column name query += string.Format(@"[{0}] = @{1}, ", itemColumnsToLocalize, itemColumnsToLocalize.Replace(' ', '.')); } query = query.Substring(0, query.Length - 2) + " WHERE "; query += string.Format(@"[{0}] = @{1} ", tablePrimaryKey[0], tablePrimaryKey[0].Replace(' ', '.')); for (int i = 1; i < tablePrimaryKey.Length; i++) { query += string.Format(@"AND [{0}] = @{1}", tablePrimaryKey[i], tablePrimaryKey[i].Replace(' ', '.')); } query += @" AND ("; int[] ouCultureKeyArray = getOU_CULTURE_KEYByCultureName(itemSupportedLanguage); foreach (var itemOuCultureKeyArray in ouCultureKeyArray) { query += string.Format(" OU_CULTURE_KEY = {0} OR ", itemOuCultureKeyArray); } query = query.Substring(0, query.Length - 3) + " )"; foreach (var itemDataEnEuList in dataEnEuList) { var cmd = new SqlCommand(query, _connection); int i = 0; foreach (var itemColumnsToLocalize in itemTablesList.ColumnsToLocalize) { var value = itemDataEnEuList.ElementAt(i++); if (value.Equals(string.Empty)) { cmd.Parameters.AddWithValue(itemColumnsToLocalize, DBNull.Value); } else { if (itemSupportedLanguage.Substring(0, 2).Equals("en", StringComparison.CurrentCultureIgnoreCase)) { cmd.Parameters.AddWithValue(itemColumnsToLocalize, value); } else { var translatedText = ""; var charactersCount = value.Length; var end = 0; var hasPostionOfEndChange = false; if (charactersCount > 2000) { string translatedPart; for (int ic = 0; ic < charactersCount / 2000; ic++) { int start = 2000 * ic; if (hasPostionOfEndChange) { start = end; hasPostionOfEndChange = false; } end = 2000 * (ic + 1); const int maxWordLeng = 50; int changePositionToBackspace = 0; while (changePositionToBackspace++ < maxWordLeng && end < value.Length && value[end] != ' ') { end++; hasPostionOfEndChange = true; } translatedPart = TranslateText(value.Substring(start, end - start), "en", itemSupportedLanguage.Substring(0, 2)); if (translatedPart.Equals(string.Empty)) { SetText(listFail, _currentWorkingTable.TableName); return; } translatedText += translatedPart; } translatedPart = TranslateText(value.Substring(end, value.Length - end), "en", itemSupportedLanguage.Substring(0, 2)); if (translatedPart.Equals(string.Empty)) { SetText(listFail, _currentWorkingTable.TableName); return; } translatedText += translatedPart; } else { translatedText = TranslateText(value, "en", itemSupportedLanguage.Substring(0, 2)); if (translatedText.Equals(string.Empty)) { SetText(listFail, _currentWorkingTable.TableName); return; } } cmd.Parameters.AddWithValue(itemColumnsToLocalize, translatedText); } } } foreach (string itemTablePrimaryKey in tablePrimaryKey) { cmd.Parameters.AddWithValue(itemTablePrimaryKey, itemDataEnEuList.ElementAt(i++)); } cmd.ExecuteNonQuery(); SetText(lblCurentTb, _currentWorkingTable.TableName); } } } ConnectionManager.Instance.CloseConnection(ref _connection); } catch (Exception) { SetText(listFail, _currentWorkingTable.TableName); } }
private bool AddDataTo_LocalizedTable(ConfirmedLocalizedTable confirmedLocalizedTable) { //Get Data from Text boxes var newCultureName = NewCultureNameTextBox.Text; var newOU_CULTURE_KEY = OUCultureKeyTextbox.Text; var localizedColumnPrimaryKeyName = get_LOCALIZEDColumnPrimaryKeyName(confirmedLocalizedTable.TableName); if (_connection == null || _connection.State != ConnectionState.Open) return false; try { var columnsToLocalize = confirmedLocalizedTable.ColumnsToLocalize; var tableName = confirmedLocalizedTable.TableName + "_LOCALIZED"; string query = string.Format("INSERT INTO [{0}] (", tableName); query = columnsToLocalize.Aggregate(query, (current, coumnToLocalize) => current + string.Format(@"[{0}], ", coumnToLocalize)); query += "[OU_CULTURE_KEY]) SELECT "; foreach (string column in columnsToLocalize) { if (!column.Equals(localizedColumnPrimaryKeyName)) { query += string.Format("'{0}' + cast([{1}] as nvarchar(max)) as [{1}], ", newCultureName, column); } else { query += string.Format("[{0}], ", column); } } query += string.Format("{0} as [OU_CULTURE_KEY]", newOU_CULTURE_KEY); query += string.Format(" FROM {0}", confirmedLocalizedTable.TableName); var cmd = new SqlCommand(query, _connection); cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } }