A db helper designed to allow code to be chained and simplified, sql call/parameter managing can be much easier with the help of it and it's auto mapping ability
Search for "CpsDbHelper" in Nuget Package manager:)
An OPTIONAL build tool to auto generate c# data models and data access classes from a Dacpac package (output of a SqldbProject in visual studio)
- Code generator generated code can give a good insight about how the helper works, scroll down to find the code example of using the db helper without code generator.
- Install-Package CpsDbHelper.CodeGenerator.BuildTask in a csproject
- Config db project path in CodeGeneratorSettings.xml
- Config the output path in CodeGeneratorSettings.xml
- Specify the build configuration which will generate code, default Release and Debug
- Specify the file name patterns and the name spaces.
- Specify whether to break the build if dacpac is not found
- Config whether to process primary key/foreign key/unique index/non-unique index, default all true
- It will generate models out of each table
- It will generate GetModel methods from each unique index/primary key
- It will generate GetModels methods from each non-unique index
- It will generate SaveModel/DeleteModel methods from each unique index/primary key
- It will generate SaveModel/DeleteModel methods from each foreign key, and load entities to a IList member in foreign entity
- It will generate classes as partial for you to extend.
- Identity columns will have their identify scope value returned if a new row is inserted
- Further, follow the examples in CodeGeneratorSettings.xml shipped with the package to do the following
- Define whether to use async for Get/Save/Delete methods overall.
- Overall async setting can be overrided by an "AsyncMappings" entry for specific index/primary key with the full name.
- If a specific enum class is needed, put a 'EnumMappings' map the type fullname with the column full name in settings, you can actually put any types you want.
- If a specific column needs to be overrided, e.g.: dataType/nullable different from db, put a 'ColumnOverrides' entry with the column's full name.
- If a Model name's Plural form is not '-s', put 'PluralMappings' entry to make the 'GetModels' method prettier.
- If a column/table/index needs to be ignored, put an 'ObjectsToIgnore' entry with its full name.
- If a column needs to be readonly, put it to ignore list and define it mannually with the same column name and compatible type.
- After generating, the code files will be in the path specified in CodeGeneratorSettings.xml, include them in the project to use.
- And we are all good!
public class Address
{
public string City { get; set; }
public string Country { get; set; }
}
public class Company
{
public string Name { get; set; }
public Address Address { get; set; }
public string PropertyWithNoMatchingColumn { get; set; }
}
public enum Gender
{
Male = 0,
Female = 1
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public Gender Gender { get; set; }
}public class UsingReader
{
private readonly DbHelperFactory _db = new DbHelperFactory("dummy connection string");
/// <summary>
/// executing a stored procedure which returns one set of query and the columns matches the entity property definition
/// </summary>
/// <param name="id">an optional id parameter</param>
/// <returns></returns>
public IEnumerable<Person> GetPersion(int? id)
{
var reader = _db.BeginReader("sp_getPeople")
.AddIntInParam("Id", id)
.AddOutParam("totalCount", SqlDbType.Int)
.AutoMapResult<Person>() //the result key is used to identify result set, the default value can be used once
//the same as this way:
//.BeginMapResult<Person>().AutoMap().FinishMap()
.Execute();
//retrieve the result. the reader always use a List<T> to store the returned table.
var ret = reader.GetResult<IList<Person>>(); //the result key is default as the one when mapping.
//retrive the output parameter with it's name.
var count = reader.GetParamResult<int>("totalCount");
return ret;
}
/// <summary>
/// executing a stored procedure which returns two sets of results, people and address, of which the columns matches the property definitions
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public KeyValuePair<Person, Address> GetPersionAndAddres(int id)
{
var reader = _db.BeginReader("sp_getPersonAndAddress")
.AddIntInParam("personId", id)
.AutoMapResult<Person>("Persion") //we can name the result set this way. or we can still use the default parameter
.AutoMapResult<Address>("Address") //map the second reader result, if the previous line uses the default value. this line must specify a different result key
.Execute();
//retrieve the results with result key
var ret = reader.GetResult<IList<Person>>("Persion");
var addressRet = reader.GetResult<IList<Address>>("Address");
return new KeyValuePair<Person, Address>(ret.FirstOrDefault(), addressRet.FirstOrDefault());
}
/// <summary>
/// executing a stored procedure which returns a company with it's address in one set
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Company GetCompanyWithAddress(int id)
{
var presavedColumnIndexToImprovePerformance = 0;
var reader = _db.BeginReader("sp_getCompanyWithAddress")
.AddIntInParam("companyId", id)
.PreProcessResult(rd => presavedColumnIndexToImprovePerformance = rd.GetOrdinal("Country"))
.BeginMapResult<Company>("Company") //it is also ok to leave the result key default
.AutoMap() //can partially use the automap ability, the mapper will map the columns with properties with same names and leave the others to you
.MapField<string>("AdditionalColumnName", (item, columnValue) => item.PropertyWithNoMatchingColumn = columnValue)
.MapField((item, rd) =>
{ //customizing a map logic to assign value to non-automapable field.
item.Address = new Address()
{
City = rd.Get<string>("City"), //operating at current row. use the reader extension to read value of Column 'City'
Country = rd.Get<string>(presavedColumnIndexToImprovePerformance) //or we can pre-get the ordinal and use it to get better performance
};
})
.FinishMap()
.Execute();
//retrieve the results with result key
var ret = reader.GetResult<IList<Company>>("Company");
return ret.FirstOrDefault();
}
/// <summary>
/// executing a stored procedure which returns a set of addresses
/// this time we demonstrate controlling the reader without any mapping functionality, and a transaction
/// </summary>
/// <returns></returns>
public Address GetAddresses()
{
return _db.BeginReader("sp_getAddresses")
.DefineResult((rd, helper) =>
{
var ret = new List<Address>();
var ordinalCity = rd.GetOrdinal("City");
while (rd.Read())
{
ret.Add(new Address()
{
City = rd.Get<string>(ordinalCity),
Country = rd.Get<string>("Country")
});
}
//this return value is exactly result which the reader stores. we will get it later at the end of this method
return ret.FirstOrDefault();
})
.BeginTransaction()
.Execute(false)
.CommitTransaction()
.End()
.GetResult<Address>();
}
/// <summary>
/// executing a stored procedure which returns a set of addresses
/// using define list result
/// </summary>
/// <returns></returns>
public Address GetAddresses2()
{
return _db.BeginReader("sp_getAddresses")
.DefineListResult((rd, helper) => new Address()
{
//the reader will loop the result set and apply this action and put the return value into a list
//the result will be List<Address>
City = rd.Get<string>("City"),
Country = rd.Get<string>("Country")
})
.BeginTransaction()
.Execute(false)
.CommitTransaction()
.End()
.GetResult<IList<Address>>()
.FirstOrDefault();
}
}public class UsingNonReader
{
private readonly DbHelperFactory _db = new DbHelperFactory("dummy connection string");
/// <summary>
/// Save a set of addresses with a user-defined table-valued structure type parameter
/// and finally selects a count out
/// </summary>
/// <param name="addresses"></param>
public int SaveAddresses(IEnumerable<Address> addresses)
{
return _db.BeginScalar<int>("sp_saveAddresses")
.BeginAddStructParam<ScalarHelper<int>, Address>("Addresses")
.MapField("City", item => item.City)
.MapField("Country", item => item.Country)
.FinishMap(addresses)
//can also use the auto mapper if the columns are name match and sequence match
.AutoMapStructParam("Addresses", addresses)
.Execute()
.GetResult();
}
/// <summary>
/// Save a person and return the id value
/// </summary>
/// <param name="person"></param>
/// <returns></returns>
public int SavePerson(Person person)
{
return _db.BeginNonQuery("sp_SavePerson")
.AddVarcharInParam("name", person.Name)
.AddIntInParam("gender", (int) person.Gender)
.Execute()
.GetReturnValue<int>();
}
public XElement UsingXmlReader()
{
return _db.BeginXmlReader("sp_returnXml")
.Execute()
.GetResult();
}
}