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