public int?SaveTable2ByNameAndDescript(Table2 table2)
{
const string query = "IF EXISTS(SELECT 1 FROM [dbo].[Table2] WHERE [Name] = @name AND [Descript] = @descript) UPDATE [dbo].[Table2] SET [Name] = @name, [ForId] = @forId, [ForName] = @forName, [Descript] = @descript WHERE [Name] = @name AND [Descript] = @descript ELSE BEGIN INSERT INTO [dbo].[Table2] ([Name], [ForId], [ForName], [Descript]) VALUES(@name, @forId, @forName, @descript) SELECT SCOPE_IDENTITY() END";
var scalar = _db.BeginScalar <int?>(query)
.AddIntInParam("Id", table2.Id, true)
.AddNvarcharInParam("Name", table2.Name, true)
.AddIntInParam("ForId", table2.ForId, true)
.AddBigIntInParam("ForName", table2.ForName, true)
.AddIntInParam("Descript", table2.Descript, true)
.ExecuteSqlString();
var ret = scalar.GetResult();
return(ret);
}
public int? SaveTable2ByNameAndDescript(Table2 table2)
{
const string query = "IF EXISTS(SELECT 1 FROM [dbo].[Table2] WHERE [Name] = @name AND [Descript] = @descript) UPDATE [dbo].[Table2] SET [Name] = @name, [ForId] = @forId, [ForName] = @forName, [Descript] = @descript WHERE [Name] = @name AND [Descript] = @descript ELSE BEGIN INSERT INTO [dbo].[Table2] ([Name], [ForId], [ForName], [Descript]) VALUES(@name, @forId, @forName, @descript) SELECT SCOPE_IDENTITY() END";
var scalar = _db.BeginScalar<int?>(query)
.AddIntInParam("Id", table2.Id, true)
.AddNvarcharInParam("Name", table2.Name, true)
.AddIntInParam("ForId", table2.ForId, true)
.AddBigIntInParam("ForName", table2.ForName, true)
.AddIntInParam("Descript", table2.Descript, true)
.ExecuteSqlString();
var ret = scalar.GetResult();
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);
}