TypeTest sample (C#)
Demonstrates how to use ADO.NET and TankardSoft data provider to read and write data in Oracle TimesTen database:
using System;
using System.Text;
using System.Data;
using TimesTen; // for Direct-Linked version
// using TimesTenCS // for Client-Server version
namespace TypeTestNS
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Start...\n");
TypeTest tt = new TypeTest(
"DSN=ttsource;PoolSize=0;Validate=false;");
tt.Run();
Console.WriteLine("Done\n");
}
}
/// <summary>
/// Worker class
/// </summary>
public class TypeTest
{
private TTConnection _conn;
public TypeTest(string dsn)
{
_conn = new TTConnection(dsn);
}
public void Run()
{
TTTransaction tnx = null;
try
{
_conn.Open();
tnx = _conn.BeginTransaction();
CreateTable();
InsertRecords();
SelectRecords();
DropTable();
tnx.Commit();
}
catch
{
if (tnx != null)
tnx.Rollback();
throw;
}
finally
{
if (_conn.State != ConnectionState.Closed)
_conn.Close();
}
}
public void CreateTable()
{
TTCommand cmd = _conn.CreateCommand();
cmd.CommandText =
"create table typetest " +
"(name char(10) not null primary key, " +
" i integer, " +
" ti tinyint, " +
" si smallint, " +
" bi bigint, " +
" nu numeric(30,10), " +
" c char(10), " +
" vc varchar(200), " +
" nc nchar(10), " +
" nvc nvarchar(1000), " +
" b binary(8), " +
" vb varbinary(8), " +
" ts timestamp, " +
" f real, " +
" d double, " +
" dt date, " +
" tm time) " +
"unique hash on (name) pages=100";
cmd.ExecuteNonQuery();
}
public void SelectRecords()
{
TTCommand cmd = _conn.CreateCommand();
cmd.CommandText = "select * from typetest";
TTDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(
"=======================================");
Console.WriteLine(
"Reading using TTDataReader.GetValue:");
Console.WriteLine(
"=======================================");
Console.WriteLine("name = {0}", rdr.GetValue(0));
Console.WriteLine("i = {0}", rdr.GetValue(1));
Console.WriteLine("ti = {0}", rdr.GetValue(2));
Console.WriteLine("si = {0}", rdr.GetValue(3));
Console.WriteLine("bi = {0}", rdr.GetValue(4));
Console.WriteLine("nu = {0}", rdr.GetValue(5));
Console.WriteLine("c = {0}", rdr.GetValue(6));
Console.WriteLine("vc = {0}", rdr.GetValue(7));
Console.WriteLine("nc = {0}", rdr.GetValue(8));
Console.WriteLine("nvc = {0}", rdr.GetValue(9));
Console.WriteLine("b = {0}",
ShowArray((byte[])rdr.GetValue(10)));
Console.WriteLine("vb = {0}",
ShowArray((byte[])rdr.GetValue(11)));
Console.WriteLine("ts = {0}", rdr.GetValue(12));
Console.WriteLine("f = {0}", rdr.GetValue(13));
Console.WriteLine("d = {0}", rdr.GetValue(14));
Console.WriteLine("dt = {0}", rdr.GetValue(15));
Console.WriteLine("tm = {0}", rdr.GetValue(16));
Console.WriteLine(
"=======================================\n");
Console.WriteLine(
"=======================================");
Console.WriteLine(
"Reading using other GetXXX methods:");
Console.WriteLine(
"=======================================");
Console.WriteLine("name = {0}", rdr.GetString(0));
Console.WriteLine("i = {0}", rdr.GetInt32(1));
Console.WriteLine("ti = {0}", rdr.GetByte(2));
Console.WriteLine("si = {0}", rdr.GetInt16(3));
Console.WriteLine("bi = {0}", rdr.GetInt64(4));
Console.WriteLine("nu = {0}", rdr.GetDecimal(5));
Console.WriteLine("c = {0}", rdr.GetString(6));
Console.WriteLine("vc = {0}", rdr.GetString(7));
Console.WriteLine("nc = {0}", rdr.GetString(8));
Console.WriteLine("nvc = {0}", rdr.GetString(9));
// Allocate buffer
byte[] arr = new byte[8];
rdr.GetBytes(10, 0, arr, 0, 8);
Console.WriteLine("b = {0}", ShowArray(arr));
rdr.GetBytes(11, 0, arr, 0, 8);
Console.WriteLine("vb = {0}", ShowArray(arr));
Console.WriteLine("ts = {0}", rdr.GetDateTime(12));
Console.WriteLine("f = {0}", rdr.GetFloat(13));
Console.WriteLine("d = {0}", rdr.GetDouble(14));
Console.WriteLine("dt = {0}", rdr.GetDateTime(15));
Console.WriteLine("tm = {0}", rdr.GetTime(16));
Console.WriteLine(
"=======================================\n");
}
rdr.Close();
}
public void InsertRecords()
{
TTCommand cmd = _conn.CreateCommand();
cmd.CommandText =
"insert into typetest values " +
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
// Parameters passed by position...
cmd.Parameters.Add("name", "test");
cmd.Parameters.Add("i", (int)2000000000);
cmd.Parameters.Add("ti", (byte)255);
cmd.Parameters.Add("si", (short)2000);
cmd.Parameters.Add("bi", (long)2000000000);
cmd.Parameters.Add("nu", (decimal)10.2);
cmd.Parameters.Add("c", "char");
cmd.Parameters.Add("vc", "varchar");
// Explicit type specification
cmd.Parameters.Add("nc", "nchar", TTType.NChar);
// Explicit type specification
cmd.Parameters.Add("nvc", "nvarchar", TTType.NVarChar);
// Be careful here, check array size!!!
cmd.Parameters.Add("b",
new byte[8]{255, 1, 1, 1, 1, 1, 1, 255});
// Be careful here, check array size!!!
cmd.Parameters.Add("vb",
new byte[8]{255, 0, 0, 0, 0, 0, 0, 255});
// DateTime
cmd.Parameters.Add("ts", DateTime.Now);
cmd.Parameters.Add("f", (float)2.99);
cmd.Parameters.Add("d", (double)2.99);
// Explicit type specification
cmd.Parameters.Add("dt", DateTime.Now, TTType.Date);
// TimeSpan
cmd.Parameters.Add("tm", DateTime.Now.TimeOfDay);
cmd.ExecuteNonQuery();
}
public void DropTable()
{
TTCommand cmd = _conn.CreateCommand();
cmd.CommandText = "drop table typetest";
cmd.ExecuteNonQuery();
}
private string ShowArray(byte[] arr)
{
StringBuilder sb = new StringBuilder();
sb.Append("{");
for (int i = 0; i < arr.Length; i++)
{
sb.Append(arr[i]);
if (i < arr.Length - 1)
sb.Append(", ");
}
sb.Append("}");
return sb.ToString();
}
}
}
Up