public void CreateTable(string tablename, DbFields fields)
{
var fieldsTxt = fields.Select(x => $"{x.Key} {x.Value}");
this.ExecuteNonQuery(
$"CREATE TABLE {tablename} ({string.Join(",", fieldsTxt)})");
}
public virtual DbFields GetQuerySchema(string querySQL)
{
var result = new DbFields();
var dbParameters = new DbParameters(
GetQueryParameters(querySQL)
.ToDictionary(x => x, x => (object)DBNull.Value));
var reader = this.Select(querySQL, dbParameters).ToDataReader();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
string columnName = row.ItemArray[0].ToString().Length > 0
? row.ItemArray[0].ToString()
: $"Column{schemaTable.Rows.IndexOf(row) + 1}";
if (string.IsNullOrEmpty(columnName))
{
int columnIndex = 0;
do
{
columnName = $"Column{++columnIndex}";
}while (result.ContainsKey(columnName));
}
if (!result.ContainsKey(columnName))
{
result.Add(columnName, string.Empty);
}
}
return(result);
}
/// <summary>
/// Insert values into a Database table
/// </summary>
/// <param name="tableName">Table Name</param>
/// <param name="values">Name and Value pairs to insert into new row</param>
/// <param name="uuidField">The UUID field name</param>
/// <returns>Object query result</returns>
public object Insert(string tableName, DbFields values, string uuidField = null)
{
/* NOTE! This code prevents SQL Injection */
var command = this.GetCommand();
command.CommandType = CommandType.Text;
command.CommandText = string.Format(
string.IsNullOrWhiteSpace(uuidField)
? "INSERT INTO [{0}] ({1}) VALUES ({2}); SELECT @@IDENTITY"
: "INSERT INTO [{0}] ({1}) OUTPUT inserted.[" + uuidField + "] VALUES ({2});",
tableName,
string.Join(", ", values.Select(x => $"[{x.Key}]")),
string.Join(", ", values.Select(x => $"@{x.Key}")));
command.AddParameters(values);
if (this.Connection.State == ConnectionState.Closed || this.Connection.State == ConnectionState.Broken)
{
this.Connection.Open();
}
return(command.ExecuteScalar());
}
public void Update_TableNameModelMoqIgnoreFields_Success()
{
// Arranje
var idField = "Id";
var ignoreField = new[] { "version" };
DbFields dbFields = null;
DbParameters dbParameters = null;
var tableName = this.fixture.Create <string>();
var value = this.fixture.Create <ModelMoq>();
var propertyCount = typeof(ModelMoq).GetProperties().Count();
var expected = this.fixture.Create <int>();
this.dbProviderMock
.Setup(x => x.Update(It.IsAny <string>(), It.IsAny <DbFields>(), It.IsAny <string>(), It.IsAny <DbParameters>()))
.Callback <string, DbFields, string, DbParameters>((param1, param2, param3, param4) =>
{
dbFields = param2;
dbParameters = param4;
})
.Returns(expected);
// Act
var result = this.target.Update(tableName, value, idField, ignoreField);
// Assert
dbProviderMock.Verify(x => x.Update(tableName, dbFields, It.IsAny <string>(), It.IsAny <DbParameters>()), Times.Once);
Assert.True(dbFields.Count == propertyCount - ignoreField.Count() - 1);
Assert.True(dbParameters.Count == 1);
Assert.Equal(expected, result);
}
public void Insert_TableNameModelMoqIgnoreFields_Success()
{
// Arranje
var idField = "Id";
var ignoreField = new[] { "id", "version" };
DbFields dbFields = null;
var tableName = this.fixture.Create <string>();
var value = this.fixture.Create <ModelMoq>();
var fieldCount = typeof(ModelMoq).GetProperties().Count();
var expected = this.fixture.Create <object>();
this.dbProviderMock
.Setup(x => x.Insert(It.IsAny <string>(), It.IsAny <DbFields>(), It.IsAny <string>()))
.Callback <string, DbFields, string>((param1, param2, param3) => dbFields = param2)
.Returns(expected);
// Act
var result = this.target.Insert(tableName, value, idField, ignoreField);
// Assert
dbProviderMock.Verify(x => x.Insert(tableName, It.IsAny <DbFields>(), idField), Times.Once);
Assert.True(dbFields.Count == fieldCount - ignoreField.Count());
Assert.Equal(expected, result);
}
/// <summary>
/// Attempt to locate a field in this definition's field list. If the field
/// is not found, it will be added to the field list. Either way, the field's
/// index in the view will be returned.
/// </summary>
/// <param name="dBField">The field to find or add.</param>
/// <returns>The index of the field in the view (0-based).</returns>
internal int FindOrAddField(DBField dBField)
{
int fieldIndex = DbFields.IndexOf(dBField);
if (fieldIndex == -1)
{
fieldIndex = DbFields.Count;
DbFields.Add(dBField);
}
return(fieldIndex);
}
public int Update(string tableName, DbFields values, string whereCondition, DbParameters parameters = null)
{
if (parameters == null)
{
parameters = new DbParameters();
}
if (!string.IsNullOrWhiteSpace(whereCondition) && !whereCondition.Trim().ToUpper().StartsWith("WHERE"))
{
whereCondition = $"WHERE {whereCondition}";
}
var queryParameters = new DbParameters(
values.ToDictionary(x => x.Key, x => x.Value));
queryParameters.Concat(
parameters.ToDictionary(x => x.Key, x => x.Value));
return(this.ExecuteNonQuery(
$"UPDATE [{tableName}] SET {string.Join(", ", values.Select(x => $"[{x.Key}]=@{x.Key}"))} {whereCondition}",
queryParameters));
}
public string GetEntityFieldName(string dbFieldName)
{
var index = DbFields.FindIndex(f => String.Compare(f, dbFieldName, StringComparison.OrdinalIgnoreCase) == 0);
return((index >= 0 && EntityFields.Count > index) ? EntityFields.ElementAt(index) : dbFieldName);
}
