public void TestGetPrimaryKeyField() { DatabaseField df = ctg.GetPrimaryKeyField(); Assert.IsTrue(df.fieldName != string.Empty); }
public void TestGetFieldByIndex() { DatabaseField df = ctg.GetField(1); Assert.IsTrue(df.fieldName != string.Empty); }
public void TestGetFieldByString() { DatabaseField df = ctg.GetField("CategoryID"); Assert.IsTrue(df.fieldName != string.Empty); }
internal DatabaseField PopulateFieldWithTheirSpreadsheetValue(SheetsService service, string spreadsheetId, DatabaseField databaseField) { var values = SpreadsheetHandler.GetValuesFromRange(service, spreadsheetId, databaseField.CellReference); if (values == null) { return(null); } var flattenedValues = FlattenSpreadsheetValues(values); databaseField.CellValue = flattenedValues.FirstOrDefault(); return(databaseField); }
internal void UpdateField(DatabaseField field) { SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, field.CellValue, field.CellReference); }
public bool IsUnique(DatabaseField field) { return(persistent.GetValue(criteria)); }
public object GetMin(DatabaseField field) { return(persistent.GetMin(field)); }
public SqlParameter BuildSqlParameter() => DatabaseField.BuildSqlParameter(ParameterName, FieldValue);
private List <DatabaseTable> GetDatabaseTables(IAuditContext db) { db.Database.OpenConnection(); using (var cmd = db.Database.GetDbConnection().CreateCommand()) { cmd.CommandText = @"SELECT DISTINCT S.schema_id AS SchemaId, S.Name as SchemaName, T.object_id AS ObjectId, T.Name AS TableName, C.column_id AS ColumnId, C.Name AS ColumnName, TY.name AS DataTypeName, C.max_length AS MaxLength, C.precision, C.scale, C.is_nullable AS IsNullable, C.is_identity AS IsIdentity, CAST(CASE WHEN PKs.object_id IS NOT NULL THEN 1 ELSE 0 END AS BIT) AS IsPrimaryKey, CAST(CASE WHEN IsNull(C.default_object_id, 0) > 0 THEN 1 ELSE 0 END AS Bit) AS HasDefaultValue FROM sys.schemas S JOIN sys.tables T ON T.schema_id = S.schema_id JOIN sys.columns C ON C.object_id = T.object_id JOIN sys.types TY ON TY.user_type_id = C.user_type_id LEFT OUTER JOIN( SELECT DISTINCT T.object_id, c.column_id FROM sys.tables T JOIN sys.columns C ON C.object_id = T.object_id JOIN sys.index_columns IC ON IC.object_id = C.object_id AND IC.column_id = C.column_id JOIN sys.indexes I ON I.object_id = IC.object_id AND I.index_id = IC.index_id AND I.is_primary_key = 1 ) as PKs ON PKs.object_id = c.object_id AND PKs.column_id = c.column_id ORDER BY S.Name, T.Name, C.Name" ; using (var reader = cmd.ExecuteReader()) { Dictionary <string, DatabaseTable> tables = new Dictionary <string, DatabaseTable>(); while (reader.Read()) { string fullTableName = $"{reader["SchemaName"]}.{reader["TableName"]}"; if (!tables.TryGetValue(fullTableName, out DatabaseTable table)) { table = new DatabaseTable { SchemaId = reader.GetInt32(reader.GetOrdinal("SchemaId")), SchemaName = reader.GetString(reader.GetOrdinal("SchemaName")), ObjectId = reader.GetInt32(reader.GetOrdinal("ObjectId")), TableName = reader.GetString(reader.GetOrdinal("TableName")), Fields = new List <DatabaseField>() }; tables.Add(fullTableName, table); } DatabaseField field = new DatabaseField { ColumnId = reader.GetInt32(reader.GetOrdinal("ColumnId")), ColumnName = reader.GetString(reader.GetOrdinal("ColumnName")), DataTypeName = reader.GetString(reader.GetOrdinal("DataTypeName")), MaxLength = reader.GetInt16(reader.GetOrdinal("MaxLength")), Precision = reader.GetByte(reader.GetOrdinal("Precision")), Scale = reader.GetByte(reader.GetOrdinal("Scale")), IsNullable = reader.GetBoolean(reader.GetOrdinal("IsNullable")), IsIdentity = reader.GetBoolean(reader.GetOrdinal("IsIdentity")), IsPrimaryKey = reader.GetBoolean(reader.GetOrdinal("IsPrimaryKey")), HasDefaultValue = reader.GetBoolean(reader.GetOrdinal("HasDefaultValue")) }; table.Fields.Add(field); } return(tables.Values.ToList()); } } }
/// <summary> /// Constructor /// </summary> /// <param name="joinType">The Join type</param> /// <param name="primaryKeyFieldTableName"> Name of the table to which the primary key field belongs</param> /// <param name="primaryKey">The primary key used in the join</param> /// <param name="foreignKeyFieldTableName">Name of the table to which the foreign key belongs</param> /// <param name="foreignKey">The foreign key used in join</param> /// <param name="criteria">The QueryCriteria with which we join</param> public JoinCriteriaCondition(JoinType joinType, string primaryKeyFieldTableName, DatabaseField primaryKey, string foreignKeyFieldTableName, DatabaseField foreignKey, QueryCriteria criteria) { Join = joinType; PrimaryKeyFieldTableName = primaryKeyFieldTableName; ForeignKeyFieldTableName = foreignKeyFieldTableName; PrimaryKey = primaryKey; ForeignKey = foreignKey; Criteria = criteria; }
//TODO: Better system for "string addOrRemove" internal DatabaseField ChangeActivity(string familyName, string columnHeader, string addOrRemove) { var row = _spreadsheet.DatabaseSheet.DatabaseRows.GetValueOrDefault(familyName); if (row == null) { return(null); } var column = _supportMethods .GetColumnsOfSection(_spreadsheet.DatabaseSheet.DatabaseColumns, DatabaseColumn.ColumnSectionEnum.Activity) .GetValueOrDefault(columnHeader); if (column == null) { return(null); } var cellOldValue = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}"); var field = new DatabaseField(_spreadsheet.DatabaseSheet, column, row); int newValue; if (addOrRemove == "add") { if (cellOldValue == null) { newValue = 1; } else { newValue = Convert.ToInt32(cellOldValue[0][0]) + 1; } field.CellValue = newValue.ToString(); } if (addOrRemove == "remove") { if (cellOldValue == null) { return(null); } else { newValue = Convert.ToInt32(cellOldValue[0][0]) - 1; } //If 0 put blank if (newValue == 0) { field.CellValue = ""; } else { field.CellValue = newValue.ToString(); } } SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, field.CellValue, $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}"); column = _spreadsheet.DatabaseSheet.DatabaseColumns.GetValueOrDefault("Activity last updated"); if (column == null) { return(null); } SpreadsheetHandler.UpdateCell(_service, _spreadsheet.Id, DateTime.Now.ToString(), $"{_spreadsheet.DatabaseSheet.Name}!{column.ColumnLetters}{row.RowNumber}"); return(field); }
private void Build_CreateTable_Script(string targetTable) { StringBuilder script = new StringBuilder(); script.AppendLine($"CREATE TABLE [{targetTable}]"); script.AppendLine("("); script.AppendLine("\t[RowNumber] int IDENTITY(1,1) PRIMARY KEY,"); for (int i = 0; i < _catalog.Properties.Count; i++) { MetadataProperty property = _catalog.Properties[i]; string propertyType = GetPropertyType(property); string propertyName = GetPropertyName(property); if (string.IsNullOrEmpty(propertyName)) { continue; } for (int ii = 0; ii < property.Fields.Count; ii++) { DatabaseField field = property.Fields[ii]; if (propertyType == "Guid") { script.Append($"\t[{propertyName}] nvarchar(36) NOT NULL"); } else if (propertyType == "bool") { script.Append($"\t[{propertyName}] bit NOT NULL"); } else if (propertyType == "decimal") { script.Append($"\t[{propertyName}] numeric({field.Precision},{field.Scale}) NOT NULL"); } else if (propertyType == "DateTime") { script.Append($"\t[{propertyName}] datetime2 NOT NULL"); } else if (propertyType == "string") { script.Append($"\t[{propertyName}] {field.TypeName}({field.Length}) NOT NULL"); } else { continue; } if (i != (_catalog.Properties.Count - 1)) { script.Append(","); } else if (ii != (property.Fields.Count - 1)) { script.Append(","); } script.AppendLine(); } } script.AppendLine(");"); _sql_script_CreateTable = script.ToString(); }
public object Count(DatabaseField field) { return(persistent.Count(field)); }
public object Max(DatabaseField field) { return(persistent.Max(field)); }
public ArrayList GetFieldList(DatabaseField field) { return(persistent.GetFieldList(field)); }
/// <summary> /// Constructor /// </summary> /// <param name="criteriaOperator">Condition's criteria operator</param> /// <param name="field">Condition's Field</param> /// <param name="values">Condition's values</param> public CriteriaCondition(CriteriaOperator criteriaOperator, DatabaseField field, params object[] values) { CriteriaOperator = criteriaOperator; Field = field; Values = values; }
public bool IsUnique(DatabaseField field, object value) { return(persistent.IsUnique(field, value)); }
void SelectData() { dataToInject = new Dictionary <DTIKey, DTIValue>(); for (int i = 0; i < DBFieldsPanel.Controls.Count; i++) { DBFieldsPanel.Controls[i].Dispose(); } DBFieldsPanel.Controls.Clear(); for (int i = 0; i < selectedFields.Length; i++) { string fieldTitle = selectedFields[i]; dataToInject.Add(new DTIKey(i, fieldTitle), new DTIValue()); DatabaseField databaseField = new DatabaseField(i, fieldTitle); databaseField.EditField += (object sender, int Id, string Value, bool isKey) => { bool isValid = true; try { if (dataToInject.Keys.Any(x => x.Key == Value && x.uID != Id)) { MessageBoxAdv.Show("This field already exists.", "Hold On", MessageBoxButtons.OK, MessageBoxIcon.Warning); isValid = false; } var dictionaryList = dataToInject.ToList(); var dictObject = dictionaryList.Find(x => x.Key.uID == Id); var dictKey = dictObject.Key; var dictValue = dictObject.Value.Values; dataToInject.Remove(dictKey); dataToInject.Add(new DTIKey(Id, Value), new DTIValue(dictValue, isKey)); } catch (Exception ex) { isValid = false; MessageBoxAdv.Show(ex.Message, "Hold On", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(isValid); }; databaseField.RemoveField += (object sender, int Id, string Value, bool isKey) => { bool isValid = true; try { if (!dataToInject.Keys.Any(x => x.Key == Value)) { MessageBoxAdv.Show("Invalid field.", "Hold On", MessageBoxButtons.OK, MessageBoxIcon.Warning); isValid = false; } dataToInject.Remove(dataToInject.Keys.First(x => x.Key == Value)); var toRemove = (DatabaseField)sender; DBFieldsPanel.Controls.Remove(toRemove); toRemove.Dispose(); if (dataToInject.Count == 0) { emptyListLabel.Visible = true; } } catch (Exception ex) { isValid = false; MessageBoxAdv.Show(ex.Message, "Hold On", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(isValid); }; emptyListLabel.Visible = false; Controls.Add(databaseField); DBFieldsPanel.Controls.Add(databaseField); databaseField.Dock = DockStyle.Top; databaseField.InitializeLayout(); } for (int i = 0; i < DataToInject.Count; i++) { for (int j = 0; j < selectedFields.Length; j++) { string value = (DataToInject[i] as IDictionary <string, object>)[selectedFields[j]].ToString(); dataToInject.First(x => x.Key.Key == selectedFields[j]).Value.Values.Add(value); } } }