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