public void TestWriteAndReadCharacter(
[Values("odbc")] string type,
[Values(1, 4, 64, 254)] int length)
{
string character = Utility.RandomAsciiString(length);
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_CHARACTER(FIELD) VALUES(?)", new { character }, tn);
var list = db.QueryObjects <DBG_TABLE <String> >("SELECT FIELD FROM DBG_TABLE_CHARACTER", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD.Length, Is.EqualTo(Utility.FieldCharacterSize));
Assert.That(actual.FIELD, Does.StartWith(character));
Assert.That(actual.FIELD, Is.EqualTo(character + new String(' ', Utility.FieldCharacterSize - character.Length)));
Assert.That(actual.EMPTY, Is.EqualTo((char)0));
}
}
}
public void TestWriteAndReadXml(
[Values("odbc")] string type,
[Values(1024, 4096, 8192, 65536, 1048576, 4194304)] int length)
{
var d = new XmlObjectData()
{
Field = Utility.RandomString(length),
};
var xml = d.ToXmlDocument();
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_XML(FIELD) VALUES(?)", new { xml }, tn);
var list = db.QueryObjects <DBG_TABLE <XmlDocument> >("SELECT FIELD FROM DBG_TABLE_XML", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD, Is.Not.Null);
Assert.That(actual.EMPTY, Is.EqualTo((char)0));
Assert.That(actual.FIELD.OuterXml
.Substring("<?xml version=\"1.0\" encoding=\"UTF-16\"?>".Length),
Is.EqualTo(xml.OuterXml
.Substring("<?xml version=\"1.0\"?>".Length)));
}
}
}
public void TestSelectWhereAndRead(
[Values("odbc")] string type,
[Values("It is work", "Any simple string")] string name,
[Values(":ID", ":1")] string parameterName)
{
string d;
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var command = db.CreateCommand())
{
command.CommandText = "SELECT * FROM TABLE(VALUES(1, '" + name + "')) AS T(ID, LOG) WHERE ID = " + parameterName + " AND (0=0)";
IDbDataParameter parameterObject = command.CreateParameter();
parameterObject.ParameterName = parameterName;
parameterObject.Value = "1";
command.Parameters.Add(parameterObject);
using (var reader = command.ExecuteReader())
{
reader.Read();
d = reader.GetString(1);
}
}
}
Assert.That(d, Is.EqualTo(name));
}
public void TestQueryMultipleWithParameters(
[Values("odbc")] string type,
[Values("67766215", "D519C704", "7F3D0A9B", "80971111")] string packId,
[Values(1, 2, 3, 4, 5)] int categoryCode)
{
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO PACK_ENTRY(ID, CLOSE_DATE, CATEGORY_ID, GU_CODE, NO) VALUES(?, 2016, ?, 870000000, 1)", new { packId, categoryCode }, tn);
#region multuSql
const string multuSql =
@"SELECT
ID
, SHORTNAME
, NAME
FROM CATEGORY_LIST
WHERE ID=? AND 1=?;
SELECT
p.ID,
p.INCOME,
p.CLOSE_DATE as CloseDate,
p.CATEGORY_ID as CategoryCode,
p.GU_CODE as GuCode,
p.NO
FROM PACK_ENTRY p
WHERE p.CATEGORY_ID=?
FETCH FIRST 501 ROWS ONLY;";
#endregion
var list = new PACK_ENTRY_LIST(501);
using (var result = db.QueryMultiple(multuSql, new { id = categoryCode, p = 1, categoryCode }, tn))
{
var catrgoryList = result.Read <CATEGORY_ENTRY>().GroupBy(l => l.ID).ToDictionary(g => g.Key, g => g.Single());
foreach (var item in result.Read <PACK_ENTRY>())
{
item.CategoryName = catrgoryList[item.CategoryCode].ShortName;
list.Add(item);
}
}
foreach (PACK_ENTRY p in list)
{
Assert.That(p.ID, Is.EqualTo(packId));
Assert.That(p.CloseDate, Is.EqualTo(2016));
Assert.That(p.CategoryCode, Is.EqualTo(categoryCode));
Assert.That(p.GuCode, Is.EqualTo(870000000));
Assert.That(p.No, Is.EqualTo(1));
}
}
}
}
public void TO_CHAR([Values("odbc")] string type,
[Values("2016-06-20 16:49:05.057", "2016-08-08 13:59:26.784")] string valueString)
{
var value = DateTime.Parse(valueString).ToUniversalTime();
string toChar;
using (var db = Db2Driver.GetDbConnection(type))
{
toChar = db.QueryObjects <string>("SELECT TO_CHAR(INCOME,'YYYY-MM-DD HH24:MI:SS') FROM (VALUES (:TIMESTAMP)) AS T(INCOME)", new { TIMESTAMP = value }).First();
}
Assert.That(toChar, Is.EqualTo(value.ToString("yyyy-MM-dd HH:mm:ss")));
}
public void GetConectionCount(
[Values("odbc")] string type,
[Values(1, 17, 37, 67)] int lenght)
{
const string sql = "select nvl(count(*),0) from FILE_ENTRY";
var list = new List <IDbConnection>();
long total = 0;
var sw = new Stopwatch();
var begin = DateTime.Now;
sw.Start();
try
{
do
{
var conn = Db2Driver.GetDbConnection(type);
conn.Open();
list.Add(conn);
foreach (var cnn in list)
{
var count = cnn.Query <long>(sql).Single();
total += count;
}
if (list.Count % 10 == 0)
{
Console.WriteLine("Pool Size = " + list.Count);
}
} while (list.Count <= lenght);
}
catch (Exception ex)
{
throw;
}
finally
{
foreach (var cnn in list)
{
cnn.Dispose();
}
}
sw.Stop();
Console.WriteLine("Max Pool Size = " + list.Count);
Console.WriteLine("{0}: elapsed={1}ms or ~{2}ms, round={3}", type, sw.ElapsedMilliseconds, (DateTime.Now - begin).TotalMilliseconds, lenght);
}
public void TestReadNullCharacter([Values("odbc")] string type)
{
char ch = Utility.RandomAsciiChar();
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_CHARACTER(EMPTY) VALUES(?)", new { ch }, tn);
var list = db.QueryObjects <DBG_TABLE <String> >("SELECT FIELD, EMPTY FROM DBG_TABLE_CHARACTER", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD, Is.Null);
Assert.That(actual.EMPTY, Is.EqualTo(ch));
}
}
}
public void TestWriteAndReadSmallint(
[Values("odbc")] string type,
[Values(short.MinValue, -1, 0, 1, short.MaxValue)] short value)
{
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_SMALLINT(FIELD) VALUES(?)", new { value }, tn);
var list = db.QueryObjects <DBG_TABLE <short?> >("SELECT FIELD FROM DBG_TABLE_SMALLINT", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD, Is.EqualTo(value));
Assert.That(actual.EMPTY, Is.EqualTo((char)0));
}
}
}
public void TestWriteAndReadBlob(
[Values("odbc")] string type,
[Values(1024, 4096, 8192, 65536, 1048576, 4194304)] int length)
{
byte[] blob = Encoding.UTF8.GetBytes(Utility.RandomString(length));
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_BLOB(FIELD) VALUES(?)", new { blob }, tn);
var list = db.QueryObjects <DBG_TABLE <byte[]> >("SELECT FIELD FROM DBG_TABLE_BLOB", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD, Is.EqualTo(blob));
Assert.That(actual.EMPTY, Is.EqualTo((char)0));
}
}
}
public void TestSelectAndRead(
[Values("odbc")] string type,
[Values("It is work", "Any simple string")] string name)
{
string d;
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var command = db.CreateCommand())
{
command.CommandText = "SELECT * FROM TABLE(VALUES('" + name + "')) AS T(LOG)";
using (var reader = command.ExecuteReader())
{
reader.Read();
d = reader.GetString(0);
}
}
}
Assert.That(d, Is.EqualTo(name));
}
public void TestSelectAndGetByAnyName(
[Values("odbc")] string type,
[Values("67766215", "D519C704", "7F3D0A9B", "80971111")] string head_id,
[Values("F04D1BA3", "5B2BF4F1", "044815E1", "DB8CE88E")] string line_id,
[Values("simple", "any")] string note)
{
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
InitEnviroment(db, tn, head_id, line_id, note);
using (var command = db.CreateCommand())
{
command.Transaction = tn;
command.CommandText =
@"SELECT
h.ID,
h.NOTE,
l.ID,
l.NOTE
FROM HEAD h
JOIN LINE l ON l.HEAD_ID=h.ID";
using (var reader = command.ExecuteReader())
{
Assert.That(reader.Read(), Is.True);
Assert.That(reader["id"], Is.EqualTo(head_id));
Assert.That(reader["id"], Is.EqualTo(line_id));
Assert.That(reader["note"], Is.EqualTo(note + ":line"));
Assert.That(reader["id"], Is.EqualTo(head_id)); //???
Assert.That(reader["id"], Is.EqualTo(line_id)); //???
Assert.That(reader.NextResult(), Is.False);
}
}
}
}
}
public void TestWriteAndReadVarchar(
[Values("odbc")] string type,
[Values(1, 4, 64, 254)] int length)
{
string varchar = Utility.RandomAsciiString(length);
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
db.Execute("INSERT INTO DBG_TABLE_VARCHAR(FIELD) VALUES(?)", new { varchar }, tn);
var list = db.QueryObjects <DBG_TABLE <String> >("SELECT FIELD FROM DBG_TABLE_VARCHAR", new { }, tn);
Assert.That(list, Is.Not.Null);
Assert.That(list.Count, Is.EqualTo(1));
var actual = list[0];
Assert.That(actual.FIELD, Is.EqualTo(varchar));
Assert.That(actual.EMPTY, Is.EqualTo((char)0));
}
}
}
public void BasicLifecycle(
[Values("odbc")] string type)
{
using (var db = Db2Driver.GetDbConnection(type))
{
var conn = (OdbcConnection)(db as Db2Connection);
Assert.That(conn, Is.Not.Null);
bool eventOpen = false, eventClosed = false;
conn.StateChange += (s, e) =>
{
if (e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Open)
{
eventOpen = true;
}
if (e.OriginalState == ConnectionState.Open && e.CurrentState == ConnectionState.Closed)
{
eventClosed = true;
}
};
Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));
conn.Open();
Assert.That(conn.State, Is.EqualTo(ConnectionState.Open));
Assert.That(eventOpen, Is.True);
conn.Close();
Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));
Assert.That(eventClosed, Is.True);
eventOpen = eventClosed = false;
conn.Open();
Assert.That(conn.State, Is.EqualTo(ConnectionState.Open));
}
}
public void TestAddAndReadFile(
[Values("odbc")] string type,
[Values("AACH2", "BGD2M")] string id,
[Values("2016.04.06 12:18:11", "2016.02.17 17:26:24")] string incomeString,
[Values("AABH7", "BCAG3")] string packId,
[Values(1, 2, 3, 5, 7, 11, 13, 17)] int no,
[Values("НИКОЛАЙ", "Светлана", "АННА", "Вячеслав")] string fname,
[Values("Груздева", "ЗАЦЕПИН", "ИВАНОВА", "Давыдов")] string lname,
[Values(null, "НИКИТИЧНА", "григорьевич", "Андреевна", "АЛЕКСАНДРОВИЧ")] string mname,
[Values("30.09.1933", "08.09.1927", "15.10.1948")] string bdate,
[Values("00000000000", "00000000101")] string snils,
[Values("г.Москва ул.Осенний б-рд.XXX.корп.YY.кв.ZZZ", "матвеевская,XXX-YY")] string address,
[Values(0, 2013, 2015)] long closeDate,
[Values(1, 2, 3, 5)] long categoryCode)
{
var income = DateTime.Parse(incomeString);
using (var db = Db2Driver.GetDbConnection(type))
{
db.Open();
using (var tn = db.BeginTransaction())
{
{
const string sql = @"INSERT INTO FILE_ENTRY(ID, INCOME, PACK_ID, NO, FNAME, LNAME, MNAME, BDATE, SNILS, ADDRESS, CLOSE_DATE, CATEGORY_ID)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ;
var param = new
{
ID = id,
INCOME = income,
PACK_ID = packId,
NO = no,
FNAME = fname,
LNAME = lname,
MNAME = mname,
BDATE = bdate,
SNILS = snils,
ADDRESS = address,
CLOSE_DATE = closeDate,
CATEGORY_ID = categoryCode,
};
db.Execute(sql, param, tn);
}
{
const string sql = @"SELECT
ID, NO, PACK_ID, INCOME, FNAME, LNAME, MNAME, BDATE, ADDRESS,
CLOSE_DATE as CloseDate,
CATEGORY_ID as CategoryCode,
SNILS
FROM FILE_ENTRY" ;
var param = new
{
ID = id,
};
var list = db.QueryObjects <FILE_ENTRY>(sql, param, tn);
Assert.That(list, Is.Not.Null);
var actual = list[0];
Assert.That(actual, Is.Not.Null);
Assert.That(actual.Income, Is.EqualTo(income));
Assert.That(actual.PACK_ID, Is.EqualTo(packId));
Assert.That(actual.No, Is.EqualTo(no));
Assert.That(actual.FName, Is.EqualTo(fname));
Assert.That(actual.LName, Is.EqualTo(lname));
Assert.That(actual.MName, Is.EqualTo(mname));
Assert.That(actual.BDate, Is.EqualTo(bdate));
Assert.That(actual.SNILS, Is.EqualTo(snils));
Assert.That(actual.Address, Is.EqualTo(address));
Assert.That(actual.CloseDate, Is.EqualTo(closeDate));
Assert.That(actual.CategoryCode, Is.EqualTo(categoryCode));
Assert.That(actual.CategoryName, Is.Null);
}
}
}
}
