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