This page is available in the following languages:
AdoHelper – a small ORM (objective-relational mapping), built on top of ADO.NET technology and making its use smarter.
To install a project, simply download the nuget package by running the Install-Package DevEvolution.AdoHelper command in the Nuget packet manager or install dependencies manually by adding the dll file in the project dependency column in VisualStudio.
Automatic mapping query results to a collection of instances of the specified type:
public class SimpleTestEntity
{
public long Id { get; set; }
public string TextField { get; set; }
public double FloatField { get; set; }
public decimal NumericField { get; set; }
public long IntegerField { get; set; }
}
IEnumerable<SimpleTestEntity> entities = new AdoHelper<SimpleTestEntity>(_connection)
.Query("SELECT * FROM TestTable")
.ExecuteReader();The displayed type can be a class, structure, tuple (System.Tuple) or (System.ValueTuple) or dynamic type as well as a generic collection (IEnumerable<>) or a list (List<>). Mapping is made on public properties, available for writing (set;) and public fields.
public class ClassEntity
{
public int Id {get; set;}
public string text;
}
public struct StructEntity
{
public int id;
public string Text {get; set;}
}
_connection.Open();
var classEntity = new AdoHelper<ClassEntity>(_connection)
.Query("SELECT * FROM TestTable WHERE id=@id")
.Parameters((“@id”, 1))
.ExecuteReader().First();
var structEntity = new AdoHelper<StructEntity>(_connection)
.Query("SELECT * FROM TestTable WHERE id=@id")
.Parameters((“@id”, 1))
.ExecuteReader().First();
var valueTupleEntity = new AdoHelper<(int id, string text)>(_connection)
.Query("SELECT id, text FROM TestTable WHERE id=@id")
.Parameters((“@id”, 1))
.ExecuteReader().First();
var tupleEntity = new AdoHelper<Tuple<int, string>>(_connection)
.Query("SELECT id, text FROM TestTable WHERE id=@id")
.Parameters((“@id”, 1))
.ExecuteReader().First();
var enumerableEntity = new AdoHelper<IEnumerable<string>>(_connection)
.Query("SELECT id, text FROM TestTable WHERE id=@id")
.Parameters((“@id”, 1))
.ExecuteReader().First();
var dynamicEntity = new AdoHelper<dynamic>(_connection)
.Query("SELECT * FROM TestTable")
.ExecuteReader().First();
Assert.AreEqual(classEntity.text, structEntity.Text);
Assert.AreEqual(structEntity.Text, valueTupleEntity.text);
Assert.AreEqual(valueTupleEntity.text, tupleEntity.Item2);
Assert.AreEqual(tupleEntity.Item2, dynamicEntity.Text);The query to the database (DB) is as follows:
[var Return value] = [await] new AdoHelper<Return value type>(DB connection object)
[.Parameters(Params)]
[.Transaction(Transaction object)]
.ExecuteNonQuery() || .ExecuteScalar() || .ExecuteReader() ||
.ExecuteNonQueryAsync([cancellation token]) || .ExecuteScalarAsync([cancellation token]) || .ExecuteReaderAsync([cancellation token])-
Return valueis the value that will be returned as a result of the query. Depending on the type of request, it can be a collection of objects, a single value, and the value may not be returned at all. Important! The number of elements specified inValueTupleorTupleof thereturn value, the order of declaration and the type of element must match the number of columns and the order of elements in the resulting query table. -
DB connection objectis an object of type IDbConnection (for example, SqlConnection). -
Params- a collection of parameters that represent a pair (parameter - value). The default type of parameters isAdoParameter. You can also use tuples (ValueTuple<string, object>andTuple<string, object>) andDbParameterobjects to specify each parameter.
Example code :
var entity = new AdoHelper<SimpleTestEntity>(_connection)
.Query("SELECT * FROM TestTable WHERE IntegerField = @intParam AND TextField = @textParam" +
"AND FloatField = @floatParam")
.Parameters(
new AdoParameter("@intParam", 123), // AdoHelper param
("@textParam", "Hello"), // ValueTuple param
new Tuple<string, object>("@floatParam", 123.123f)) // Tuple param
.ExecuteReader()
.FirstOrDefault();Transaction objectis an object of typeIDbTransaction(for example,SqlTransaction).
The result of the query (return value) may be as follows:
- A collection of rows of the resulting table in the form
IEnumerable<T>. To get this return value, you must use theExecuteReader()orExecuteReaderAsync()command. - A single value in the form of object of type
T. To get a single value, you must run the final commandExecuteScalar()orExecuteScalarAsync(). - Do not return value. For this, there are the
ExecuteNonQuery()andExecuteNonQueryAsync()methods.
AdoHelper can independently match the names of class / structure members and columns of the resulting table, and the order of the columns does not matter. The comparison is not case sensitive. Example:
public struct TestEntity
{
public int id { get; set; }
public DateTime DeliverDate;
public double longitude { get; set; }
public double LATITUDE { get; set; }
public int customerId;
}However, in some situations it makes sense to give the members of the mapping class names that differ from the column names of the resulting table. In this case, you should use the attributes [Field(Name="Name of the column in the table")]. There are also cases in which it is necessary to abandon the mapping of some class members with table columns. For this there is an attribute [NonMapped].
An example of such a class:
public class ExcludedFieldTestEntity
{
public int Id { get; set; }
[NonMapped]
public string TextField { get; set; } // that property is excluded from mapping
[NonMapped]
public double FloatField { get; set; } // that property is excluded from mapping
public DateTime DateField { get; } // that property is excluded too because set property is unreachable
[Field(Name = "NumericField")]
public decimal Numeric { get; set; } // that property is mapped to NumericField column
[Field(Name = "IntegerField")]
public long Integer { get; set; } // that property is mapped to IntegerField column
}Tuples can also be used as an object for matching. However, in this case, the elements of which the tuple consists must go in the same order as in the resulting table. Tuples of any size are fully supported.
Note. The names of the ValueTuple elements do not participate in the comparison, as they are only syntactic sugar and are not used in the compiled application.
var entities = new AdoHelper<(int id, string name, int nextId)>(_connection)
.Query("SELECT current_id, category_name, next_id FROM categories WHERE category LIKE ‘TMP’")
.ExecuteReader();Equals to:
var entities = new AdoHelper<Tuple<int, string, int>>(_connection)
.Query("SELECT current_id, category_name, next_id FROM categories WHERE category LIKE ‘TMP’")
.ExecuteReader();It is also possible to use the dynamic type as the return value. In this case, the result will be an object of type ExpandoObject.
Note: Element names are stored in the register in which they were returned from the database query.
Example:
FbConnection connection = new FbConnection(...);
connection.Open();
var entity = new AdoHelper<dynamic>(connection)
.Query("SELECT * FROM TestTable")
.ExecuteReader().First();
...
Assert.AreEqual("Hello", entity.TEXTFIELD);
Assert.AreEqual(123.123, entity.FLOATFIELD, 10e-5);
Assert.AreEqual(123, entity.NUMERICFIELD);
Assert.AreEqual(123, entity.INTEGERFIELD);The project is published under the license MIT and is supplied as is, without any guarantees.