public IList <Table2> GetTable2sByForIdAndForName(int?forId, long?forName)
{
const string query = "SELECT * FROM [dbo].[Table2] WHERE [ForId] = @forId AND [ForName] = @forName";
var reader = _db.BeginReader(query)
.AddIntInParam("ForId", forId, true)
.AddBigIntInParam("ForName", forName, true)
.AutoMapResult <Table2>()
.ExecuteSqlString();
var ret = reader.GetResult <IList <Table2> >();
return(ret);
}
/// <summary>
/// executing a stored procedure which returns one set of query and the columns matches the entity property defination
/// </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);
}
public void TestGeneratedCode()
{
var db = new DbHelperFactory(ConnectionString.FormatInvariantCulture(DbName));
var da = new CpsDbHelperDataAccess(ConnectionString.FormatInvariantCulture(DbName));
Task.WaitAll(db.BeginNonQuery("delete from dbo.table2").ExecuteSqlStringAsync());
db.BeginNonQuery("delete from dbo.Table1")
.ExecuteSqlString();
var t1 = new TableAnother()
{
Id = 10,
Name1 = 10,
Name = "t1r1",
Name13 = "t1r1"
};
da.BeginTransaction();
da.SaveTableAnotherByIdAndName1(t1);
var t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.AreEqual(t1load.Name13, t1.Name13);
var t2 = new Table2()
{
Descript = 1,
Name = "t2r1"
};
var id = da.SaveTable2ById(t2);
var t2load = da.GetTable2ById(id.Value);
Assert.AreEqual(t2.Name, t2load.Name);
da.EndTransaction(false);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.IsNull(t1load);
t1.Name13 = "t1r1m";
da.SaveTableAnotherByIdAndName1(t1);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.AreEqual(t1load.Name13, t1.Name13);
da.BeginTransaction();
t1.Name13 = "t1r1m2";
da.SaveTableAnotherByIdAndName1(t1);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
da.EndTransaction();
Assert.AreEqual(t1load.Name13, t1.Name13);
t1load = da.GetTableAnotherByIdAndName1(11, 11);
Assert.IsNull(t1load);
id = da.SaveTable2ById(t2);
t2load = da.GetTable2ByNameAndDescript(t2.Name, t2.Descript);
Assert.AreEqual(t2load.Id, id.Value);
var task = db.BeginReader("select * from table2")
.AutoMapResult<Table2>()
.ExecuteSqlStringAsync();
task.Wait();
var ret = task.Result
.GetResultCollection<Table2>();
Assert.AreEqual(1, ret.Count);
Assert.AreEqual(ret.First().Id, id.Value);
t2 = new Table2()
{
ForId = t1.Id,
ForName = t1.Name1,
Name = "hehe"
};
id = da.SaveTable2ByForIdAndForName(t2);
var retT2 = da.GetTable2sByForIdAndForName(t2.ForId, t2.ForName);
Assert.AreEqual(1,retT2.Count);
Assert.AreEqual(t2.Name, retT2.First().Name);
t1load = da.GetTableAnotherByIdAndName1(t2.ForId.Value, t2.ForName.Value, true);
Assert.AreEqual(1, t1load.Table2s.Count);
Assert.AreEqual(t2.Name, t1load.Table2s.First().Name);
t1load = da.GetTableAnotherByIdAndName1(t2.ForId.Value, t2.ForName.Value);
Assert.AreEqual(null, t1load.Table2s);
}
public void TestGeneratedCode()
{
var db = new DbHelperFactory(ConnectionString.FormatInvariantCulture(DbName));
var da = new CpsDbHelperDataAccess(ConnectionString.FormatInvariantCulture(DbName));
Task.WaitAll(db.BeginNonQuery("delete from dbo.table2").ExecuteSqlStringAsync());
db.BeginNonQuery("delete from dbo.Table1")
.ExecuteSqlString();
var t1 = new TableAnother()
{
Id = 10,
Name1 = 10,
Name = "t1r1",
Name13 = "t1r1"
};
da.BeginTransaction();
da.SaveTableAnotherByIdAndName1(t1);
var t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.AreEqual(t1load.Name13, t1.Name13);
var t2 = new Table2()
{
Descript = 1,
Name = "t2r1"
};
var id = da.SaveTable2ById(t2);
var t2load = da.GetTable2ById(id.Value);
Assert.AreEqual(t2.Name, t2load.Name);
da.EndTransaction(false);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.IsNull(t1load);
t1.Name13 = "t1r1m";
da.SaveTableAnotherByIdAndName1(t1);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
Assert.AreEqual(t1load.Name13, t1.Name13);
da.BeginTransaction();
t1.Name13 = "t1r1m2";
da.SaveTableAnotherByIdAndName1(t1);
t1load = da.GetTableAnotherByIdAndName1(t1.Id, t1.Name1);
da.EndTransaction();
Assert.AreEqual(t1load.Name13, t1.Name13);
t1load = da.GetTableAnotherByIdAndName1(11, 11);
Assert.IsNull(t1load);
id = da.SaveTable2ById(t2);
t2load = da.GetTable2ByNameAndDescript(t2.Name, t2.Descript);
Assert.AreEqual(t2load.Id, id.Value);
var task = db.BeginReader("select * from table2")
.AutoMapResult<Table2>()
.ExecuteSqlStringAsync();
task.Wait();
var ret = task.Result
.GetResultCollection<Table2>();
Assert.AreEqual(1, ret.Count);
Assert.AreEqual(ret.First().Id, id.Value);
t2 = new Table2()
{
ForId = t1.Id,
ForName = t1.Name1,
Name = "hehe"
};
id = da.SaveTable2ByForIdAndForName(t2);
var retT2 = da.GetTable2sByForIdAndForName(t2.ForId, t2.ForName);
Assert.AreEqual(1,retT2.Count);
Assert.AreEqual(t2.Name, retT2.First().Name);
t1load = da.GetTableAnotherByIdAndName1(t2.ForId.Value, t2.ForName.Value, true);
Assert.AreEqual(1, t1load.Table2s.Count);
Assert.AreEqual(t2.Name, t1load.Table2s.First().Name);
t1load = da.GetTableAnotherByIdAndName1(t2.ForId.Value, t2.ForName.Value);
Assert.AreEqual(null, t1load.Table2s);
}
