public void SaveTableAnotherByIdAndName1(TableAnother tableAnother)
{
const string query = "IF EXISTS(SELECT 1 FROM [dbo].[Table1] WHERE [Id] = @id AND [Name1] = @name1) UPDATE [dbo].[Table1] SET [Id] = @id, [Name] = @name, [Name1] = @name1, [Name2] = @name2, [Name3] = @name3, [Name4] = @name4, [Name5] = @name5, [Name6] = @name6, [Name7] = @name7, [Name8] = @name8, [Name9] = @name9, [Name0] = @name0, [Name11] = @name11, [Name12] = @name12, [Name13] = @name13, [Name14] = @name14, [Name15] = @name15, [Name16] = @name16, [Name17] = @name17, [Name18] = @name18, [Name20] = @name20, [Name21] = @name21, [Name22] = @name22, [Name23] = @name23, [Name24] = @name24, [Name25] = @name25, [Name26] = @name26, [Name27] = @name27, [Name28] = @name28 WHERE [Id] = @id AND [Name1] = @name1 ELSE INSERT INTO [dbo].[Table1] ([Id], [Name], [Name1], [Name2], [Name3], [Name4], [Name5], [Name6], [Name7], [Name8], [Name9], [Name0], [Name11], [Name12], [Name13], [Name14], [Name15], [Name16], [Name17], [Name18], [Name20], [Name21], [Name22], [Name23], [Name24], [Name25], [Name26], [Name27], [Name28]) VALUES(@id, @name, @name1, @name2, @name3, @name4, @name5, @name6, @name7, @name8, @name9, @name0, @name11, @name12, @name13, @name14, @name15, @name16, @name17, @name18, @name20, @name21, @name22, @name23, @name24, @name25, @name26, @name27, @name28)";
var ret = _db.BeginNonQuery(query)
.AddIntInParam("Id", tableAnother.Id, true)
.AddCharInParam("Name", tableAnother.Name, true)
.AddBigIntInParam("Name1", tableAnother.Name1, true)
.AddBinaryInParam("Name2", tableAnother.Name2, true)
.AddBitInParam("Name3", tableAnother.Name3, true)
.AddCharInParam("Name4", tableAnother.Name4, true)
.AddDateTimeInParam("Name5", tableAnother.Name5, true)
.AddDecimalInParam("Name6", tableAnother.Name6, true)
.AddFloatInParam("Name7", tableAnother.Name7, true)
.AddImageInParam("Name8", tableAnother.Name8, true)
.AddIntInParam("Name9", tableAnother.Name9, true)
.AddDecimalInParam("Name0", tableAnother.Name0, true)
.AddNcharInParam("Name11", tableAnother.Name11, true)
.AddNtextInParam("Name12", tableAnother.Name12, true)
.AddNvarcharInParam("Name13", tableAnother.Name13, true)
.AddDecimalInParam("Name14", tableAnother.Name14, true)
.AddDateTimeInParam("Name15", tableAnother.Name15, true)
.AddSmallIntInParam("Name16", tableAnother.Name16, true)
.AddDecimalInParam("Name17", tableAnother.Name17, true)
.AddTextInParam("Name18", tableAnother.Name18, true)
.AddTinyIntInParam("Name20", tableAnother.Name20, true)
.AddGuidInParam("Name21", tableAnother.Name21, true)
.AddBinaryInParam("Name22", tableAnother.Name22, true)
.AddVarcharInParam("Name23", tableAnother.Name23, true)
.AddXmlInParam("Name24", tableAnother.Name24, true)
.AddDateInParam("Name25", tableAnother.Name25, true)
.AddTimeInParam("Name26", tableAnother.Name26, true)
.AddDateTimeOffsetInParam("Name27", tableAnother.Name27, true)
.AddDateTime2InParam("Name28", tableAnother.Name28, true)
.ExecuteSqlString();
}
/// <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 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);
}
