public void RefreshSchema()
{
tableName = null;
columns = null;
if (deleteCommand != null)
{
if (adapter != null && adapter.DeleteCommand == deleteCommand)
{
adapter.DeleteCommand = null;
}
deleteCommand.Dispose();
deleteCommand = null;
}
if (insertCommand != null)
{
if (adapter != null && adapter.InsertCommand == insertCommand)
{
adapter.InsertCommand = null;
}
insertCommand.Dispose();
insertCommand = null;
}
if (updateCommand != null)
{
if (adapter != null && adapter.UpdateCommand == updateCommand)
{
adapter.UpdateCommand = null;
}
updateCommand.Dispose();
updateCommand = null;
}
}
private void CreateTable()
{
VirtuosoCommand cmd = new VirtuosoCommand();
cmd.Connection = connection;
try
{
cmd.CommandText = "drop table foo";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmd.CommandText = "create table foo (id int primary key, c long varchar, nc long nvarchar, b long varbinary)";
cmd.ExecuteNonQuery();
cmd.Dispose();
checkTable = new DataTable();
checkTable.Columns.Add("id", typeof(int));
checkTable.Columns.Add("c", typeof(string));
checkTable.Columns.Add("nc", typeof(string));
checkTable.Columns.Add("b", typeof(byte[]));
}
public void ThreeCommands(TestCaseResult result)
{
/*
* VirtuosoCommand cmd1 = connection.CreateCommand ();
* Sleeper sleeper1 = new Sleeper ("first", cmd1, 30, 60);
* Thread thread1 = new Thread (new ThreadStart (sleeper1.Sleep));
*/
VirtuosoCommand cmd2 = connection.CreateCommand();
Sleeper sleeper2 = new Sleeper("second", cmd2, 20, 40);
Thread thread2 = new Thread(new ThreadStart(sleeper2.Sleep));
VirtuosoCommand cmd3 = connection.CreateCommand();
Sleeper sleeper3 = new Sleeper("third", cmd3, 10, 20);
Thread thread3 = new Thread(new ThreadStart(sleeper3.Sleep));
try
{
//thread1.Start ();
//Thread.Sleep (2000);
thread2.Start();
Thread.Sleep(2000);
thread3.Start();
}
finally
{
//thread1.Join ();
thread2.Join();
thread3.Join();
//cmd1.Dispose ();
cmd2.Dispose();
cmd3.Dispose();
}
}
public void OutputParameters(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create procedure bar (in x integer, out y integer, inout z integer)\n" +
"{\n" +
" y := x * 2;\n" +
" z := z * 2;\n" +
" return y + z;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "bar";
VirtuosoParameter returnValue = command.CreateParameter();
returnValue.ParameterName = "ReturnValue";
returnValue.Direction = ParameterDirection.ReturnValue;
returnValue.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(returnValue);
VirtuosoParameter x = command.CreateParameter();
x.ParameterName = "x";
x.Direction = ParameterDirection.Input;
x.VirtDbType = VirtDbType.Integer;
x.Value = 2;
command.Parameters.Add(x);
VirtuosoParameter y = command.CreateParameter();
y.ParameterName = "y";
y.Direction = ParameterDirection.Output;
y.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(y);
VirtuosoParameter z = command.CreateParameter();
z.ParameterName = "z";
z.Direction = ParameterDirection.InputOutput;
z.VirtDbType = VirtDbType.Integer;
z.Value = 3;
command.Parameters.Add(z);
try
{
command.ExecuteNonQuery();
result.FailIfNotEqual(this, "Return Value", 10, returnValue.Value);
result.FailIfNotEqual(this, "Out Parameter", 4, y.Value);
result.FailIfNotEqual(this, "InOut Parameter", 6, z.Value);
}
finally
{
command.Dispose();
}
}
private void CreateTable()
{
VirtuosoCommand create = connection.CreateCommand();
create.CommandText = "create table xmlt (id int primary key, data long xml)";
create.ExecuteNonQuery();
create.Dispose();
}
private void InsertRowText()
{
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText = "insert into xmlt (id, data) values (1, ?)";
insert.Parameters.Add(new VirtuosoParameter(":0", TheXml));
insert.ExecuteNonQuery();
insert.Dispose();
}
public void MultipleResultSets(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create procedure bar ()\n" +
"{\n" +
" declare i int;\n" +
" declare c char;\n" +
" result_names (i);\n" +
" result (1);\n" +
" result (2);\n" +
" end_result ();\n" +
" result_names (c);\n" +
" result ('a');\n" +
" result ('b');\n" +
" return 0;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "bar";
VirtuosoDataReader reader = null;
try
{
reader = command.ExecuteReader();
result.FailIfNotEqual(1, reader.FieldCount);
result.FailIfNotEqual("i", reader.GetName(0).ToLower());
result.FailIfNotEqual(typeof(int), reader.GetFieldType(0));
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(1, reader["i"]);
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(2, reader["i"]);
result.FailIfNotEqual(false, reader.Read());
result.FailIfNotEqual(true, reader.NextResult());
result.FailIfNotEqual(1, reader.FieldCount);
result.FailIfNotEqual("c", reader.GetName(0).ToLower());
result.FailIfNotEqual(typeof(string), reader.GetFieldType(0));
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual("a", reader["c"]);
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual("b", reader["c"]);
result.FailIfNotEqual(false, reader.NextResult());
}
finally
{
if (reader != null)
{
reader.Close();
}
command.Dispose();
}
}
public DataTable ExecuteQuery(string queryString, ITransaction transaction = null)
{
DataTable result = new DataTable();
VirtuosoDataAdapter adapter = null;
VirtuosoCommand command = null;
try
{
command = Connection.CreateCommand();
command.CommandText = queryString;
if (transaction != null && transaction is VirtuosoTransaction)
{
command.Transaction = (transaction as VirtuosoTransaction).Transaction;
}
result.Columns.CollectionChanged += OnColumnsCollectionChanged;
adapter = new VirtuosoDataAdapter(command);
adapter.Fill(result);
result.Columns.CollectionChanged -= OnColumnsCollectionChanged;
}
catch (InvalidOperationException ex)
{
string msg = string.Format("Error: Caught {0} exception.", ex.GetType());
Debug.WriteLine(msg);
} /* This seems to be different in 7.x version of Openlink.Virtuoso.dll
* catch (VirtuosoException e)
* {
*
* if (e.ErrorCode == 40001)
* throw new ResourceLockedException(e);
* else
*
* throw;
* } */
finally
{
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(result);
}
private void ExecuteNonQuery(string text)
{
VirtuosoCommand command = connection.CreateCommand();
command.CommandText = text;
try
{
command.ExecuteNonQuery();
}
finally
{
command.Dispose();
}
}
public void DelayLessThanTimeout(TestCaseResult result)
{
VirtuosoCommand command = connection.CreateCommand();
try
{
command.CommandTimeout = 50;
command.CommandText = "delay(5)";
command.ExecuteNonQuery();
}
finally
{
command.Dispose();
}
}
public void CreateTable(VirtuosoConnection connection)
{
VirtuosoCommand create = connection.CreateCommand();
create.CommandText = "create table foo (id int primary key, txt varchar(100))";
create.ExecuteNonQuery();
create.Dispose();
checkTable = new DataTable();
checkTable.Columns.Add("id", typeof(int));
checkTable.Columns.Add("txt", typeof(string));
InsertRow(connection, 1);
InsertRow(connection, 2);
}
protected void ExecuteDirectQuery(string queryString, ITransaction transaction = null)
{
VirtuosoCommand command = null;
try
{
command = Connection.CreateCommand();
command.CommandText = queryString;
Log?.Invoke(queryString);
if (transaction is VirtuosoTransaction)
{
command.Transaction = (transaction as VirtuosoTransaction).Transaction;
}
command.ExecuteNonQuery();
}
catch (InvalidOperationException)
{
Debug.WriteLine("Caught InvalidOperationExcetion.");
}
catch (VirtuosoException e)
{
if (e.Errors.Count > 0)
{
var er = e.Errors[0];
if (er.SQLState == "40001")
{
throw new ResourceLockedException(e);
}
}
else
{
throw;
}
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void DeriveParamters(TestCaseResult result)
{
DropProcedure();
ExecuteNonQuery(
"create function BAR (in X integer, out Y integer, inout Z integer, in V varchar(20), in W nvarchar(20), in D numeric(20, 5)) returns real\n" +
"{\n" +
" return 0.0;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "BAR";
try
{
VirtuosoCommandBuilder.DeriveParameters(command);
result.FailIfNotEqual("Parameter Count", 7, command.Parameters.Count);
CheckParameter(result, command.Parameters[0],
"ReturnValue", ParameterDirection.ReturnValue, VirtDbType.Real, DbType.Single,
4, 0, 0); // FIXME: The precision should be 7.
CheckParameter(result, command.Parameters[1],
"X", ParameterDirection.Input, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[2],
"Y", ParameterDirection.Output, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[3],
"Z", ParameterDirection.InputOutput, VirtDbType.Integer, DbType.Int32,
4, 10, 0);
CheckParameter(result, command.Parameters[4],
"V", ParameterDirection.Input, VirtDbType.VarChar, DbType.AnsiString,
20, 0, 0);
CheckParameter(result, command.Parameters[5],
"W", ParameterDirection.Input, VirtDbType.NVarChar, DbType.String,
20, 0, 0);
CheckParameter(result, command.Parameters[6],
"D", ParameterDirection.Input, VirtDbType.Decimal, DbType.Decimal,
19, 20, 5);
}
finally
{
command.Dispose();
}
}
private void DropTable()
{
VirtuosoCommand drop = connection.CreateCommand();
drop.CommandText = "drop table xmlt";
try
{
drop.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
drop.Dispose();
}
}
private void ExecuteDropCommand(string text)
{
VirtuosoCommand command = connection.CreateCommand();;
command.CommandText = text;
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
command.Dispose();
}
}
public void InsertRow(VirtuosoConnection connection, int i)
{
string s = new string (new char[3] {
(char)('a' + i), (char)('b' + i), (char)('c' + i)
});
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText = "insert into foo values (" + i + ", '" + s + "')";
insert.ExecuteNonQuery();
insert.Dispose();
DataRow row = checkTable.NewRow();
row["id"] = i;
row["txt"] = s;
checkTable.Rows.Add(row);
}
public void TestInsertSqlXml(TestCaseResult result)
{
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText = "insert into xmlt (id, data) values (1, ?)";
insert.Parameters.Add(new VirtuosoParameter(":0", new SqlXml(TheXml)));
insert.ExecuteNonQuery();
insert.Dispose();
VirtuosoCommand cmd = connection.CreateCommand();
cmd.CommandText = "select data from xmlt";
VirtuosoDataReader rdr = cmd.ExecuteReader();
rdr.Read();
SqlXml x = rdr.GetSqlXml(0);
FailIfXmlNotEqual(result, x.ToString(), TheXml);
}
public void DelayMoreThanTimeout(TestCaseResult result)
{
VirtuosoCommand command = connection.CreateCommand();
bool thrown = false;
try
{
command.CommandTimeout = 5;
command.CommandText = "delay(50)";
command.ExecuteNonQuery();
}
catch (SystemException)
{
thrown = true;
}
finally
{
command.Dispose();
}
result.FailIfNot("No timeout exception is thrown", thrown);
}
private void DoGetDataTest(TestCaseResult result, string text, int column,
CommandBehavior behavior, Selector selector, Sequence sequence)
{
VirtuosoCommand cmd = null;
VirtuosoDataReader dr = null;
try
{
cmd = new VirtuosoCommand(text, connection);
dr = cmd.ExecuteReader(behavior);
CheckGetData(result, dr, column, selector, sequence);
}
finally
{
if (dr != null)
{
dr.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
}
}
private void InsertRow(int id)
{
object i, si, d, r, n, year, month, day, dt, tm, dtm, str, bin;
if (id == 0)
{
i = si = d = r = n = year = month = day = dt = tm = dtm = str = bin = DBNull.Value;
}
else
{
i = id + 1000;
si = (short)(id + 2000);
d = id * 1000.0001;
r = (float)(id * 100.001);
n = (decimal)id * 10000001;
year = 1990 + id;
month = (id - 1) % 12 + 1;
day = (id - 1) % DateTime.DaysInMonth((int)year, (int)month) + 1;
dt = new DateTime((int)year, (int)month, (int)day);
tm = new TimeSpan(id % 24, id % 60, id % 60);
dtm = new DateTime((int)year, (int)month, (int)day, id % 24, id % 60, id % 60);
int length = id % 128;
char[] chars = new char[length];
byte[] bytes = new byte[length];
for (int count = 0; count < length; count++)
{
chars[count] = (char)('a' + (id + count) % 26 - 1);
bytes[count] = (byte)(id + count);
}
str = new String(chars);
bin = bytes;
}
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText =
"insert into foo "
+ "(id, i, si, d, r, n, dt, tm, dtm, c, vc, lvc, nc, nvc, lnvc, b, vb, lvb) "
+ "values "
+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
VirtuosoParameterCollection parameters = insert.Parameters;
VirtuosoParameter idParam = insert.CreateParameter();
idParam.ParameterName = "id";
idParam.DbType = DbType.Int32;
idParam.Value = id;
parameters.Add(idParam);
VirtuosoParameter iParam = insert.CreateParameter();
iParam.ParameterName = "i";
iParam.DbType = DbType.Int32;
iParam.Value = i;
parameters.Add(iParam);
VirtuosoParameter siParam = insert.CreateParameter();
siParam.ParameterName = "si";
siParam.DbType = DbType.Int16;
siParam.Value = si;
parameters.Add(siParam);
VirtuosoParameter dParam = insert.CreateParameter();
dParam.ParameterName = "d";
dParam.DbType = DbType.Double;
dParam.Value = d;
parameters.Add(dParam);
VirtuosoParameter rParam = insert.CreateParameter();
rParam.ParameterName = "r";
rParam.DbType = DbType.Single;
rParam.Value = r;
parameters.Add(rParam);
VirtuosoParameter nParam = insert.CreateParameter();
nParam.ParameterName = "n";
nParam.DbType = DbType.Decimal;
nParam.Value = n;
parameters.Add(nParam);
VirtuosoParameter dtParam = insert.CreateParameter();
dtParam.ParameterName = "dt";
dtParam.DbType = DbType.Date;
dtParam.Value = dt;
parameters.Add(dtParam);
VirtuosoParameter tmParam = insert.CreateParameter();
tmParam.ParameterName = "tm";
tmParam.DbType = DbType.Time;
tmParam.Value = tm;
parameters.Add(tmParam);
VirtuosoParameter dtmParam = insert.CreateParameter();
dtmParam.ParameterName = "dtm";
dtmParam.DbType = DbType.DateTime;
dtmParam.Value = dtm;
parameters.Add(dtmParam);
VirtuosoParameter cParam = insert.CreateParameter();
cParam.ParameterName = "c";
cParam.DbType = DbType.AnsiStringFixedLength;
cParam.Value = str;
parameters.Add(cParam);
VirtuosoParameter vcParam = insert.CreateParameter();
vcParam.ParameterName = "vc";
vcParam.DbType = DbType.AnsiString;
vcParam.Value = str;
parameters.Add(vcParam);
VirtuosoParameter lvcParam = insert.CreateParameter();
lvcParam.ParameterName = "lvc";
lvcParam.DbType = DbType.AnsiString;
lvcParam.Value = str;
parameters.Add(lvcParam);
VirtuosoParameter ncParam = insert.CreateParameter();
ncParam.ParameterName = "nc";
ncParam.DbType = DbType.StringFixedLength;
ncParam.Value = str;
parameters.Add(ncParam);
VirtuosoParameter nvcParam = insert.CreateParameter();
nvcParam.ParameterName = "nvc";
nvcParam.DbType = DbType.String;
nvcParam.Value = str;
parameters.Add(nvcParam);
VirtuosoParameter lnvcParam = insert.CreateParameter();
lnvcParam.ParameterName = "lnvc";
lnvcParam.DbType = DbType.String;
lnvcParam.Value = str;
parameters.Add(lnvcParam);
VirtuosoParameter bParam = insert.CreateParameter();
bParam.ParameterName = "b";
bParam.DbType = DbType.Binary;
bParam.Value = bin;
parameters.Add(bParam);
VirtuosoParameter vbParam = insert.CreateParameter();
vbParam.ParameterName = "vb";
vbParam.DbType = DbType.Binary;
vbParam.Value = bin;
parameters.Add(vbParam);
VirtuosoParameter lvbParam = insert.CreateParameter();
lvbParam.ParameterName = "lvb";
lvbParam.DbType = DbType.Binary;
lvbParam.Value = bin;
parameters.Add(lvbParam);
try
{
insert.ExecuteNonQuery();
}
finally
{
insert.Dispose();
insert = null;
}
DataRow row = checkTable.NewRow();
row["id"] = id;
row["i"] = i;
row["si"] = si;
row["d"] = d;
row["r"] = r;
row["n"] = n;
row["dt"] = dt;
row["tm"] = tm;
row["dtm"] = dtm;
row["c"] = str;
row["vc"] = str;
row["lvc"] = str;
row["nc"] = str;
row["nvc"] = str;
row["lnvc"] = str;
row["b"] = bin;
row["vb"] = bin;
row["lvb"] = bin;
checkTable.Rows.Add(row);
}
private void InsertRow(int id)
{
object c, nc, b;
if (id == 0)
{
c = nc = b = DBNull.Value;
}
else
{
int length = 1 << (id - 1);
char[] chars = new char[length];
byte[] bytes = new byte[length];
for (int i = 0; i < length; i++)
{
chars[i] = (char)(' ' + i % (127 - ' '));
bytes[i] = (byte)(i % 256);
}
c = nc = new String(chars);
b = bytes;
}
VirtuosoCommand insert = connection.CreateCommand();
insert.CommandText =
"insert into foo "
+ "(id, c, nc, b) "
+ "values "
+ "(?, ?, ?, ?)";
VirtuosoParameterCollection parameters = insert.Parameters;
VirtuosoParameter idParam = insert.CreateParameter();
idParam.ParameterName = "id";
idParam.DbType = DbType.Int32;
idParam.Value = id;
parameters.Add(idParam);
VirtuosoParameter cParam = insert.CreateParameter();
cParam.ParameterName = "c";
cParam.DbType = DbType.AnsiString;
cParam.Value = c;
parameters.Add(cParam);
VirtuosoParameter ncParam = insert.CreateParameter();
ncParam.ParameterName = "nc";
ncParam.DbType = DbType.String;
ncParam.Value = nc;
parameters.Add(ncParam);
VirtuosoParameter bParam = insert.CreateParameter();
bParam.ParameterName = "b";
bParam.DbType = DbType.Binary;
bParam.Value = b;
parameters.Add(bParam);
try
{
insert.ExecuteNonQuery();
}
finally
{
insert.Dispose();
insert = null;
}
DataRow row = checkTable.NewRow();
row["id"] = id;
row["c"] = c;
row["nc"] = nc;
row["b"] = b;
checkTable.Rows.Add(row);
}
public void ResultSetAndOutputParameters(TestCaseResult result)
{
DropProcedure();
DropProcedure();
ExecuteNonQuery(
"create procedure bar (out x integer)\n" +
"{\n" +
" declare i int;\n" +
" result_names (i);\n" +
" result (1);\n" +
" result (2);\n" +
" x := 3;\n" +
" return 4;\n" +
"}\n"
);
VirtuosoCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "bar";
VirtuosoParameter returnValue = command.CreateParameter();
returnValue.ParameterName = "ReturnValue";
returnValue.Direction = ParameterDirection.ReturnValue;
returnValue.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(returnValue);
VirtuosoParameter x = command.CreateParameter();
x.ParameterName = "x";
x.Direction = ParameterDirection.Output;
x.VirtDbType = VirtDbType.Integer;
command.Parameters.Add(x);
VirtuosoDataReader reader = null;
bool closed = false;
try
{
reader = command.ExecuteReader();
result.FailIfNotEqual(1, reader.FieldCount);
result.FailIfNotEqual("i", reader.GetName(0).ToLower());
result.FailIfNotEqual(typeof(int), reader.GetFieldType(0));
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(1, reader["i"]);
result.FailIfNotEqual(true, reader.Read());
result.FailIfNotEqual(2, reader["i"]);
result.FailIfNotEqual(false, reader.Read());
reader.Close();
closed = true;
result.FailIfNotEqual("Out Parameter", 3, x.Value);
result.FailIfNotEqual("Return Value", 4, returnValue.Value);
}
finally
{
if (reader != null && !closed)
{
reader.Close();
}
command.Dispose();
}
}
public void DisposeNewCommand(TestCaseResult result)
{
VirtuosoCommand command = connection.CreateCommand();
command.Dispose();
}