public static XmlDocument DeleteByDataClass(object obj)
{
ObjectSqlizer os = new ObjectSqlizer(obj);
DBHelper.Execute(os.DeleteSql);
return(new SuccessDocument());
}
//データクラスをテーブルにマッピングしてDBを更新します。
public static DataRowAccessor InsertOrUpdateByDataClass(Object obj)
{
ObjectSqlizer os = new ObjectSqlizer(obj);
if (IsExist(os))
{
DBHelper.Execute(os.UpdateSql);
}
else
{
using (DBHelperWithTransaction db = new DBHelperWithTransaction())
{
db.Execute(os.InsertSql);
if (os.Sequence == 0) //Sequence使ってて且つ新規の場合
{
os.Sequence = db.Select(
new DBSqlStatement("SELECT IDENT_CURRENT('@DB@@TABLE_NAME@') SEQUENCE")
.ReplaceStripString("TABLE_NAME", os.TableName)
)[0]["SEQUENCE"].Int;
}
db.Commit();
}
}
//TODO 0件しか取れなかったらどうするか考える
return(DBHelper.Select(os.SelectSql)[0]);
}
public void UPDATE_DATEの自動挿入()
{
ObjectSqlizer zer = new ObjectSqlizer(new UPDATE());
Assert.That(zer.CountByPKSql, Is.EqualTo("SELECT COUNT(*) CT FROM ClassLibraryTest.dbo. UPDATE WHERE PK = 1234 "));
Assert.That(zer.UpdateSql, Is.EqualTo("UPDATE ClassLibraryTest.dbo. UPDATE SET UPDATE_DATE = GETDATE() WHERE PK = 1234 "));
Assert.That(zer.InsertSql, Is.EqualTo("INSERT INTO ClassLibraryTest.dbo. UPDATE(PK,UPDATE_DATE) VALUES(1234,GETDATE())"));
}
public void 正常系()
{
ObjectSqlizer zer = new ObjectSqlizer(new ABC());
Assert.That(zer.CountByPKSql, Is.EqualTo("SELECT COUNT(*) CT FROM ClassLibraryTest.dbo. ABC WHERE INT_COLUMN = 1234 "));
Assert.That(zer.UpdateSql, Is.EqualTo("UPDATE ClassLibraryTest.dbo. ABC SET TEXT_COLUMN = N'1234',TEST_COLUMN2 = N'ABCD',BOOL_COLUMN = 1,DATETIME_COLUMN = '2009/07/17 00:00:00' WHERE INT_COLUMN = 1234 "));
Assert.That(zer.InsertSql, Is.EqualTo("INSERT INTO ClassLibraryTest.dbo. ABC(INT_COLUMN,TEXT_COLUMN,TEST_COLUMN2,BOOL_COLUMN,DATETIME_COLUMN) VALUES(1234,N'1234',N'ABCD',1,'2009/07/17 00:00:00')"));
}
//複数のデータクラスを1トランザクションでDB更新します。
public static XmlDocument InsertOrUpdateByDataClass(Object[] objs)
{
using (DBHelperWithTransaction db = new DBHelperWithTransaction())
{
foreach (Object obj in objs)
{
ObjectSqlizer os = new ObjectSqlizer(obj);
if (IsExistWithTrans(os, db))
{
db.Execute(os.UpdateSql);
}
else
{
db.Execute(os.InsertSql);
}
}
db.Commit();
}
return(new SuccessDocument());
}
public void SequenceはInsert分に含めない()
{
ObjectSqlizer zer = new ObjectSqlizer(new Sequence());
Assert.That(zer.InsertSql, Is.EqualTo("INSERT INTO ClassLibraryTest.dbo. Sequence(VALUE) VALUES(N'ABCD')"));
}
public void PKがないとUpdate文を作れません()
{
string sql = new ObjectSqlizer(new NoPK()).UpdateSql;
}
public void PKがないとSelect文を作れません()
{
string sql = new ObjectSqlizer(new NoPK()).CountByPKSql;
}
private static bool IsExistWithTrans(ObjectSqlizer os, DBHelperWithTransaction db)
{
return(os.Sequence > 0 || db.Select(os.CountByPKSql)[0]["CT"].Int != 0);
}
/// <summary>
/// PKを使って既存のレコードがあるかどうか判定します。
/// Sequenceがあればレコードを一つに特定出来るので、PKで検索せずにTrueを返します。
/// </summary>
/// <param name="os"></param>
/// <returns></returns>
private static bool IsExist(ObjectSqlizer os)
{
return(os.Sequence > 0 || DBHelper.Select(os.CountByPKSql)[0]["CT"].Int != 0);
}