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); } } } }