private void GetMediaSetting(DbManager db)
{
var query = from m in db.GetTable<DataMedia>()
join s in db.GetTable<DataMediaSetting>() on m.IdMedia equals s.IdMedia
where m.IdLanguageData == 33 && s.IdLanguageDataI == 33
orderby m.Media
select
new
{
s.Activation,
m.IdMedia,
m.Media,
s.CaptureCode
};
if (!true)
query = query.Where(r => r.Activation < 10);
var res = query.ToList();
var mediae = res.Select(r => new Media
{
ID_MEDIA = r.IdMedia,
MEDIA = r.Media,
CaptureCode = r.CaptureCode,
IsActivate = r.Activation < 10,
}).ToList();
}
/// <summary>
/// Загружает все записи
/// </summary>
/// <returns>The entries.</returns>
protected override IEnumerable<GuestbookEntry> LoadEntries()
{
using (var db = new DbManager())
{
var query = from entry in db.GetTable<DbGuestbookEntry>()
join user in db.GetTable<DbGuestbookUser>() on entry.UserId equals user.Id
select new GuestbookEntry { User = user.Name, Message = entry.Message };
return query.ToList();
}
}
public void ShouldMapAssociationForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field4")
.NewRow("TestOne");
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<AssociationThis2Dbo>()
.MapField(_ => _.FieldThis1, "ThisId")
.MapField(_ => _.FieldThis3)
.Association(_ => _.FieldThis1).ToOne(_ => _.FieldOther3)
.MapTo(db);
var table = db.GetTable<AssociationThis2ChildDbo>();
// when
var dbo = (from t in table select t.FieldThis3.FieldOther4).First();
// then
Assert.AreEqual("TestOne", dbo, "Fail result for many");
conn.Commands[0]
.Assert().AreField("ThisId", "Fail this key");
conn.Commands[0]
.Assert().AreField("FieldOther3", "Fail other key");
conn.Commands[0]
.Assert().AreField("FieldOther4", "Fail other result");
Assert.AreEqual(3, conn.Commands[0].Fields.Count, "More fields");
}
}
public void ParameterPrefixTest()
{
try
{
using (var db = new DbManager(new MySqlDataProvider(), "Server=DBHost;Port=3306;Database=nodatabase;Uid=bltoolkit;Pwd=TestPassword;"))
{
db.GetTable<Person>().ToList();
}
}
catch (DataException ex)
{
var number = ex.Number;
}
}
public void Test([DataContextsAttribute(ExcludeLinqService=true)] string context)
{
var ids = new long[] { 1, 2, 3 };
using (var db = new DbManager(context))
{
var q =
from t1 in db.GetTable<Table2>()
where t1.Field3.Any(x => ids.Contains(x.Field1))
select new { t1.Field2 };
var sql = q.ToString();
Assert.That(sql.IndexOf("INNER JOIN"), Is.LessThan(0));
}
}
/// <summary>
/// Сохраняет запись
/// </summary>
/// <param name="entry">Entry.</param>
protected override void SaveEntry(GuestbookEntry entry)
{
using (var db = new DbManager())
{
var dbEntry = new DbGuestbookEntry ();
dbEntry.Message = entry.Message;
var dbUser = db.GetTable<DbGuestbookUser>().SingleOrDefault (u => u.Name == entry.User);
if (dbUser == null)
{
dbUser = new DbGuestbookUser { Name = entry.User };
dbUser.Id = Convert.ToInt32(db.InsertWithIdentity (dbUser));
}
dbEntry.UserId = dbUser.Id;
dbEntry.Id = Convert.ToInt32(db.InsertWithIdentity(dbEntry));
}
}
public void ShouldMapTableNameForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1")
.NewRow(1);
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<TableNameDbo>()
.TableName("TableNameDboT1")
.MapTo(db);
// when
db.GetTable<TableNameChildDbo>().ToArray();
// then
conn.Commands[0]
.Assert().AreTable("TableNameDboT1", "Fail mapping");
}
}
public void ShouldMapValueForMemberForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2")
.NewRow(true, false);
using (conn)
using (var db = new DbManager(conn))
{
#warning bug for maping TO db
// fluent config
new FluentMap<MapValueMemberDbo>()
.MapField(_ => _.Field1)
.MapValue("result true", true)
.MapValue("result false", false)
.MapField(_ => _.Field2)
.MapValue("value true", true)
.MapValue("value false", false)
.MapTo(db);
var table = db.GetTable<MapValueMemberChildDbo>();
// when
var dbo = (from t in table select t).First();
// then
Assert.AreEqual("result true", dbo.Field1, "Not map from value1");
Assert.AreEqual("value false", dbo.Field2, "Not map from value2");
}
}
public void ShouldMapTrimmableForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1")
.NewRow("test ");
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<TrimmableDbo>()
.Trimmable(_ => _.Field1)
.MapTo(db);
var table = db.GetTable<TrimmableChildDbo>();
// when
var dbo = (from t in table select t).First();
// then
Assert.AreEqual("test", dbo.Field1, "Not trimmable");
}
}
public void ShouldMapIgnoreForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1")
.NewRow(10, 1);
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<MapIgnoreSelectDbo>()
.MapIgnore(_ => _.Field2)
.MapTo(db);
var table = db.GetTable<MapIgnoreChildDbo>();
// when
(from t in table where t.Field1 == 10 select t).First();
// then
conn.Commands[0]
.Assert().AreNotField("Field2", "Field exists");
}
}
public void ShouldMapIgnoreInsert()
{
// db config
var conn = new MockDb()
.NewNonQuery();
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<MapIgnoreInsertDbo>()
.MapIgnore(_ => _.Field2)
.MapTo(db);
// when / then
new SqlQuery<MapIgnoreInsertDbo>(db).Insert(new MapIgnoreInsertDbo { Field1 = 20, Field2 = 2 });
AssertExceptionEx.AreException<LinqException>(
() => db.GetTable<MapIgnoreInsertDbo>().Insert(() => new MapIgnoreInsertDbo { Field1 = 10, Field2 = 1 })
, "Fail for linq");
// then
conn.Commands[0]
.Assert().AreNotField("Field2", "Field exists");
Assert.AreEqual(1, conn.Commands[0].Parameters.Count, "Fail params");
}
}
public Table<Person> People2(DbManager db)
{
return db.GetTable<Person>();
}
public void ShouldFailMapValueForInsert()
{
// db config
var conn = new MockDb()
.NewNonQuery()
.NewNonQuery();
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<MapValueMemberDbo>()
.MapField(_ => _.Field1)
.MapValue("result true", true)
.MapValue("result false", false)
.MapField(_ => _.Field2)
.MapValue("value true", true)
.MapValue("value false", false)
.MapTo(db);
var table = db.GetTable<MapValueMemberDbo>();
// when
table.Insert(() => new MapValueMemberDbo { Field1 = "result true", Field2 = "value false" }); // linq
db.Insert(new MapValueMemberDbo { Field1 = "result true", Field2 = "value false" }); // other?
// then
var i1 = conn.Commands[0].CommandText.IndexOf("result true", StringComparison.Ordinal);
Assert.IsTrue(-1 == i1, "Not map to value1 for linq");
var i2 = conn.Commands[0].CommandText.IndexOf("value false", StringComparison.Ordinal);
Assert.IsTrue(-1 == i2, "Not map to value2 for linq");
Assert.AreEqual(true, conn.Commands[1].Parameters[0].Value, "Not map to value1");
Assert.AreEqual(false, conn.Commands[1].Parameters[1].Value, "Not map to value2");
}
}
public void ShouldMapInheritanceMappingSelectChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2")
.NewRow(1, 1);
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<InheritanceMappingChDbo>()
.TableName("tt")
.InheritanceMapping<InheritanceMappingChDbo1>(11111)
.InheritanceMapping<InheritanceMappingChDbo2>(22222)
.InheritanceField(_ => _.Field2)
.MapTo(db);
var table = db.GetTable<InheritanceMappingChDbo1>();
// when
var dbos = (from t in table select t).ToArray();
// then
Assert.IsInstanceOfType(dbos[0], typeof(InheritanceMappingChDbo1), "Invalid type");
Assert.IsTrue(conn.Commands[0].CommandText.ToLower().Contains("where"), "Not condition");
Assert.IsTrue(conn.Commands[0].CommandText.ToLower().Contains("11111"), "Fail condition value");
conn.Commands[0]
.Assert().AreField("Field2", 2, "Not in where part");
}
}
public void ShouldMapValueForTypeForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2", "Field3")
.NewRow("one", "two", true);
using (conn)
using (var db = new DbManager(conn))
{
#warning bug for property any different types
#warning bug for maping TO db
// fluent config
new FluentMap<MapValueTypeDbo>()
.MapValue(1, "one", "1")
.MapValue(2, "two")
.MapValue(3, true)
.MapTo(db);
var table = db.GetTable<MapValueTypeChildDbo>();
// when
var dbo = (from t in table select t).First();
// then
Assert.AreEqual(1, dbo.Field1, "Not map from value1");
Assert.AreEqual(2, dbo.Field2, "Not map from value2");
Assert.AreEqual(3, dbo.Field3, "Not map from value3");
}
}
public void NonUpdatableOnUpdate()
{
using (var db = new DbManager())
{
db.BeginTransaction();
var person = new Person1
{
FirstName = "TestOnInsert",
LastName = "",
MiddleName = "1",
Gender = 'M'
};
var sqlQuery = new SqlQuery<Person1>();
sqlQuery.Insert(db, person);
var p = db.GetTable<Person1>().Single(_ => _.FirstName == "TestOnInsert");
Assert.AreEqual(person.MiddleName, p.MiddleName);
person.PersonID = p.PersonID;
person.MiddleName = "should not be updated";
sqlQuery.Update(db, person);
p = db.GetTable<Person1>().Single(_ => _.FirstName == "TestOnInsert");
Assert.AreNotEqual(person.MiddleName, p.MiddleName);
db.RollbackTransaction();
}
}
public void ShouldFailMapValueForTypeForPropertyAnyDifferentTypes()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2")
.NewRow("one", 2);
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<MapValueTypeDbo2>()
.MapValue(1, "one") // Field1 - int
.MapValue("two", 2) // Filed2 - string
.MapTo(db);
var table = db.GetTable<MapValueTypeDbo2>();
// when / then
AssertExceptionEx.AreNotException<Exception>(() => { var dbo = (from t in table select t).First(); }
, "Bug for property any different types");
}
}
public void ShouldMapValueForEnumForChild()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2", "Field3", "Field4")
.NewRow("ok", "super", "yes", 10);
using (conn)
using (var db = new DbManager(conn))
{
#warning bug for maping TO db
// fluent config
new FluentMap<MapValueEnumDbo>()
.MapValue(MapValueEnum.Value1, "ok", "yes")
.MapValue(MapValueEnum.Value2, "super")
.MapTo(db);
var table = db.GetTable<MapValueEnumChildDbo>();
// when
var dbo = (from t in table select t).First();
// then
Assert.AreEqual(MapValueEnum.Value1, dbo.Field1, "Not map from value1");
Assert.AreEqual(MapValueEnum.Value2, dbo.Field2, "Not map from value2");
Assert.AreEqual(MapValueEnum.Value1, dbo.Field3, "Not map from value3");
}
}
public void NonUpdatableOnInsert()
{
using (var db = new DbManager())
{
db.BeginTransaction();
new SqlQuery<Person1>().Insert(db, new Person1
{
FirstName = "TestOnInsert",
LastName = "",
MiddleName = "1",
Gender = 'M'
});
var p = db.GetTable<Person1>().Single(_ => _.FirstName == "TestOnInsert");
Assert.AreNotEqual("1", p.MiddleName);
db.RollbackTransaction();
}
}
public void ShouldMapInheritanceMapping()
{
// db config
var conn = new MockDb()
.NewReader("Field1", "Field2")
.NewRow(1, 1)
.NewRow(2, 2);
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<InheritanceMappingDbo>()
.InheritanceMapping<InheritanceMappingDbo1>(1)
.InheritanceMapping<InheritanceMappingDbo2>(2)
.InheritanceField(_ => _.Field2)
.MapTo(db);
var table = db.GetTable<InheritanceMappingDbo>();
// when
var dbos = (from t in table select t).ToArray();
// then
Assert.IsInstanceOfType(dbos[0], typeof(InheritanceMappingDbo1), "Invalid type1");
Assert.IsInstanceOfType(dbos[1], typeof(InheritanceMappingDbo2), "Invalid type2");
}
}
public void TestDataTypeTestInsertWithIdentity()
{
using (DbManager db = new DbManager())
{
var dt = new DataTypeTest2
{
Binary_ = new byte[2] { 1, 2 },
#if !ORACLE
Boolean_ = true,
Guid_ = Guid.Empty,
#endif
Byte_ = 250,
Bytes_ = new byte[] { 2, 1 },
DateTime_ = DateTime.Now,
Decimal_ = 9876543210.0m,
Double_ = 12345.67890,
Int16_ = 12345,
Int32_ = 1234567890,
Int64_ = 1234567890123456789,
Money_ = 99876543210.0m,
Single_ = 1234.0f,
String_ = "Crazy Frog",
Char_ = 'F',
SByte_ = 123,
//UInt16_ = 65432,
//UInt32_ = 4000000000,
//UInt64_ = 12345678901234567890,
#if !SQLCE
Stream_ = new MemoryStream(5),
Xml_ = new XmlTextReader(new StringReader("<xml/>")),
XmlDoc_ = new XmlDocument()
#endif
};
#if !SQLCE
string innerxml = "<root><sql id=\"2\">Other Verbiage</sql></root>";
dt.XmlDoc_.LoadXml(innerxml);
#endif
var id = Convert.ToInt32(db.InsertWithIdentity(dt));
var obj = db.GetTable<DataTypeTest2>().Where(_ => _.ID == id).First();
#if !SQLCE
Assert.AreEqual(innerxml, obj.XmlDoc_.OuterXml);
#endif
TypeAccessor.WriteConsole(obj);
}
}
public void ShouldMapField()
{
// db config
var conn = new MockDb()
.NewNonQuery();
using (conn)
using (var db = new DbManager(conn))
{
// fluent config
new FluentMap<MapFieldDbo>()
.MapField(_ => _.Field1, "f1")
.MapTo(db);
// when
db.GetTable<MapFieldDbo>().Insert(() => new MapFieldDbo { Field1 = 1 });
// then
conn.Commands[0]
.Assert().AreField("f1", "Fail mapping");
}
}
public void UpdateAssociation5([DataContexts(
ProviderName.Access, ProviderName.DB2, ProviderName.Firebird, ProviderName.Informix, "Oracle", ProviderName.PostgreSQL, ProviderName.SqlCe, ProviderName.SQLite,
ExcludeLinqService=true)] string context)
{
using (var db = new DbManager(context))
{
var ids = new[] { 10000, 20000 };
db.GetTable<Table2>()
.Where (x => ids.Contains(x.ParentID))
.Select(x => x.Table1)
.Distinct()
.Set(y => y.BoolValue, y => y.Tables2.All(x => x.Value1))
.Update();
var idx = db.LastQuery.IndexOf("INNER JOIN");
Assert.That(idx, Is.Not.EqualTo(-1));
idx = db.LastQuery.IndexOf("INNER JOIN", idx + 1);
Assert.That(idx, Is.EqualTo(-1));
}
}