public int AddCatalogItem(String title, String uri) { FbConnection conn = GetConnection(); try { using (FbCommand cmd = new FbCommand()) { using (cmd.Transaction = conn.BeginTransaction(FbTransactionOptions.Autocommit|FbTransactionOptions.Concurrency)) { cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_AddCatalogItem"; cmd.Parameters.Add("@title", FbDbType.VarChar, 4000); cmd.Parameters.Add("@uri", FbDbType.VarChar, 4000); cmd.Parameters.Add("@catalog_item_id", FbDbType.Numeric); cmd.Parameters[0].Value = title; cmd.Parameters[1].Value = uri; cmd.Parameters[2].Direction = ParameterDirection.ReturnValue; //Capture the catalog item ID, and build the list //of title characters cmd.ExecuteNonQuery(); //int catId = (int)cmd.ExecuteScalar(); int catId = (int)cmd.Parameters[2].Value; SetItemTitleChars(cmd.Transaction, catId, title); cmd.Transaction.Commit(); return catId; } } } finally { conn.Close(); } }
public void ClearCatalog() { FbConnection conn = GetConnection(); try { using (FbCommand cmd = new FbCommand("delete from catalog_items", conn)) { cmd.ExecuteNonQuery(); } } finally { conn.Close(); } }
public void IntergerArrayTest() { int id_value = this.GetId(); Console.WriteLine("\r\n"); Console.WriteLine("Integer Array Test"); string selectText = "SELECT iarray_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, iarray_field) values(@int_field, @array_field)"; // Insert new Record int[] insert_values = new int[4]; insert_values[0] = 10; insert_values[1] = 20; insert_values[2] = 30; insert_values[3] = 40; Console.WriteLine("Executing insert command"); FbCommand insert = new FbCommand(insertText, Connection, Transaction); insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value; insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values; insert.ExecuteNonQuery(); insert.Dispose(); Transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct FbCommand select = new FbCommand(selectText, Connection); FbDataReader reader = select.ExecuteReader(); if (reader.Read()) { if (!reader.IsDBNull(0)) { int[] select_values = new int[insert_values.Length]; System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length); for (int i = 0; i < insert_values.Length; i++) { if (insert_values[i] != select_values[i]) { throw new Exception("differences at index " + i.ToString()); } } } } reader.Close(); select.Dispose(); }
public void BinaryBlobTest() { int id_value = this.GetId(); string selectText = "SELECT blob_field FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, blob_field) values(@int_field, @blob_field)"; Console.WriteLine("\r\n\r\nBinary Blob Test"); Console.WriteLine("Generating an array of temp data"); // Generate an array of temp data byte[] insert_values = new byte[100000*4]; RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); rng.GetBytes(insert_values); Console.WriteLine("Executing insert command"); // Execute insert command FbTransaction transaction = Connection.BeginTransaction(); FbCommand insert = new FbCommand(insertText, Connection, transaction); insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value; insert.Parameters.Add("@blob_field", FbDbType.Binary).Value = insert_values; insert.ExecuteNonQuery(); transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct FbCommand select = new FbCommand(selectText, Connection); byte[] select_values = (byte[])select.ExecuteScalar(); for (int i = 0; i < insert_values.Length; i++) { if (insert_values[i] != select_values[i]) { throw new Exception("differences at index " + i.ToString()); } } Console.WriteLine("Finishing test"); }
public void FirebirdLikeTest00() { FbCommand command = new FbCommand("EXECUTE PROCEDURE GETVARCHARFIELD(?)", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input; command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output; command.Parameters[0].Value = 1; // This will fill output parameters values command.ExecuteNonQuery(); // Check the value Assert.AreEqual("IRow Number 1", command.Parameters[1].Value); // Dispose command - this will do a transaction commit command.Dispose(); }
public void UnicodeTest() { string createTable = "CREATE TABLE VARCHARTEST (VARCHAR_FIELD VARCHAR(10));"; FbCommand ct = new FbCommand(createTable, this.Connection); ct.ExecuteNonQuery(); ct.Dispose(); ArrayList l = new ArrayList(); l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1');"); l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('11');"); l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('111');"); l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1111');"); foreach (string statement in l) { FbCommand insert = new FbCommand(statement, this.Connection); insert.ExecuteNonQuery(); insert.Dispose(); } string sql = "select * from varchartest"; FbCommand cmd = new FbCommand(sql, this.Connection); FbDataReader r = cmd.ExecuteReader(); while (r.Read()) { Console.WriteLine("{0} :: {1}", r[0], r[0].ToString().Length); } r.Close(); }
public void InvalidParameterFormat() { string sql = "update test set timestamp_field = @timestamp where int_field = @integer"; FbTransaction transaction = this.Connection.BeginTransaction(); try { FbCommand command = new FbCommand(sql, this.Connection, transaction); command.Parameters.Add("@timestamp", FbDbType.TimeStamp).Value = 1; command.Parameters.Add("@integer", FbDbType.Integer).Value = 1; command.ExecuteNonQuery(); command.Dispose(); transaction.Commit(); } catch { transaction.Rollback(); } }
public void UpdatedClobFieldTest() { Console.WriteLine("\r\nUpdate CLOB field with implicit transaction."); FbCommand command = new FbCommand("update TEST set clob_field = @clob_field where int_field = @int_field", Connection); command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1; command.Parameters.Add("@clob_field", FbDbType.Text).Value = "Clob field update with implicit transaction"; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void VarCharArrayPartialUpdateTest() { Console.WriteLine("\r\n"); Console.WriteLine("VarChar Array Test"); Console.WriteLine("------- ----- ----"); string updateText = "update TEST set varray_field = @array_field " + "WHERE int_field = 1"; string[] new_values = new string[2]; new_values[0] = "abc"; new_values[1] = "abcdef"; FbCommand update = new FbCommand(updateText, Connection, Transaction); update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values; update.ExecuteNonQuery(); update.Dispose(); PrintArrayValues(new_values, false); }
public void UpdatedArrayFieldTest() { Console.WriteLine("\r\nUpdate CLOB field with implicit transaction."); int[] values = new int[4]; values[0] = 10; values[1] = 20; values[2] = 30; values[3] = 40; FbCommand command = new FbCommand("update TEST set iarray_field = @iarray_field where int_field = @int_field", Connection); command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1; command.Parameters.Add("@iarray_field", FbDbType.Array).Value = values; int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Array field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
/// <include file='Doc/en_EN/FbTransaction.xml' path='doc/class[@name="FbTransaction"]/method[@name="Rollback(System.String)"]/*'/> public void Rollback(string savePointName) { lock (this) { if (savePointName == null) { throw new ArgumentException("No transaction name was be specified."); } else { if (savePointName.Length == 0) { throw new ArgumentException("No transaction name was be specified."); } } if (this.isUpdated) { throw new InvalidOperationException("This Transaction has completed; it is no longer usable."); } try { FbCommand command = new FbCommand( "ROLLBACK WORK TO SAVEPOINT " + savePointName, this.connection, this); command.ExecuteNonQuery(); command.Dispose(); } catch (IscException ex) { throw new FbException(ex.Message, ex); } } }
public void InsertNullTest() { string sql = "insert into TEST (int_field) values (@value)"; FbCommand command = new FbCommand(sql, this.Connection); command.Parameters.Add("@value", FbDbType.Integer).Value = null; try { command.ExecuteNonQuery(); throw new Exception("The command was executed without throw an exception"); } catch { } }
public void SqlServerLikeTest00() { FbCommand command = new FbCommand("GETVARCHARFIELD", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input; command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output; command.Parameters[0].Value = 1; // This will fill output parameters values command.ExecuteNonQuery(); // Print output value Console.WriteLine("Output Parameters"); Console.WriteLine(command.Parameters[1].Value); // Dispose command - this will do a transaction commit command.Dispose(); }
public void SqlServerLikeTest01() { FbCommand command = new FbCommand("GETRECORDCOUNT", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@RECORDCOUNT", FbDbType.Integer).Direction = ParameterDirection.Output; // This will fill output parameters values command.ExecuteNonQuery(); // Print output value Console.WriteLine("Output Parameters - Record Count"); Console.WriteLine(command.Parameters[0].Value); // Dispose command - this will do a transaction commit command.Dispose(); }
public void SavePointTest() { FbCommand command = new FbCommand(); Console.WriteLine("Iniciada nueva transaccion"); Transaction = Connection.BeginTransaction("InitialSavePoint"); command.Connection = Connection; command.Transaction = Transaction; command.CommandText = "insert into TEST (INT_FIELD) values (200) "; command.ExecuteNonQuery(); Transaction.Save("FirstSavePoint"); command.CommandText = "insert into TEST (INT_FIELD) values (201) "; command.ExecuteNonQuery(); Transaction.Save("SecondSavePoint"); command.CommandText = "insert into TEST (INT_FIELD) values (202) "; command.ExecuteNonQuery(); Transaction.Rollback("InitialSavePoint"); Transaction.Commit(); command.Dispose(); }
public void AbortTransaction() { StringBuilder b1 = new StringBuilder(); b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST"); FbTransaction transaction = null; FbCommand command = null; try { transaction = this.Connection.BeginTransaction(); command = new FbCommand(b1.ToString(), this.Connection, transaction); command.ExecuteNonQuery(); transaction.Commit(); transaction = null; } catch (Exception) { transaction.Rollback(); transaction = null; } finally { if (command != null) { command.Dispose(); } } }
public void SimplifiedChineseTest() { string createTable = "CREATE TABLE TABLE1 (FIELD1 varchar(20))"; FbCommand create = new FbCommand(createTable, this.Connection); create.ExecuteNonQuery(); create.Dispose(); // insert using parametrized SQL string sql = "INSERT INTO Table1 VALUES (@value)"; FbCommand command = new FbCommand(sql, this.Connection); command.Parameters.Add("@value", FbDbType.VarChar).Value = "中文"; command.ExecuteNonQuery(); command.Dispose(); sql = "SELECT * FROM TABLE1"; FbCommand select = new FbCommand(sql, this.Connection); string result = select.ExecuteScalar().ToString(); select.Dispose(); Assert.AreEqual("中文", result, "Incorrect results in parametrized insert"); sql = "DELETE FROM TABLE1"; FbCommand delete = new FbCommand(sql, this.Connection); delete.ExecuteNonQuery(); delete.Dispose(); // insert using plain SQL sql = "INSERT INTO Table1 VALUES ('中文')"; FbCommand plainCommand = new FbCommand(sql, this.Connection); plainCommand.ExecuteNonQuery(); plainCommand.Dispose(); sql = "SELECT * FROM TABLE1"; select = new FbCommand(sql, this.Connection); result = select.ExecuteScalar().ToString(); select.Dispose(); Assert.AreEqual("中文", result, "Incorrect results in plain insert"); }
public void PrepareTest() { // Create a new test table FbCommand create = new FbCommand("create table PrepareTest(test_field varchar(20));", Connection); create.ExecuteNonQuery(); create.Dispose(); // Insert data using a prepared statement FbCommand command = new FbCommand( "insert into PrepareTest(test_field) values(@test_field);", Connection); command.Parameters.Add("@test_field", FbDbType.VarChar).Value = DBNull.Value; command.Prepare(); for (int i = 0; i < 5; i++) { if (i < 1) { command.Parameters[0].Value = DBNull.Value; } else { command.Parameters[0].Value = i.ToString(); } command.ExecuteNonQuery(); } command.Dispose(); try { // Check that data is correct FbCommand select = new FbCommand("select * from PrepareTest", Connection); FbDataReader reader = select.ExecuteReader(); int count = 0; while (reader.Read()) { if (count == 0) { Assert.AreEqual(DBNull.Value, reader[0], "Invalid value."); } else { Assert.AreEqual(count, reader.GetInt32(0), "Invalid value."); } count++; } reader.Close(); select.Dispose(); } catch (Exception) { throw; } finally { // Drop table FbCommand drop = new FbCommand("drop table PrepareTest", Connection); drop.ExecuteNonQuery(); drop.Dispose(); } }
public void InsertDateTest() { string sql = "insert into TEST (int_field, date_field) values (1002, @date)"; FbCommand command = new FbCommand(sql, this.Connection); command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString(); int ra = command.ExecuteNonQuery(); Assert.AreEqual(ra, 1); }
public void BigIntArrayPartialUpdateTest() { Console.WriteLine("\r\n"); Console.WriteLine("BigInt Array Test"); Console.WriteLine("------ ----- ----"); string updateText = "update TEST set larray_field = @array_field " + "WHERE int_field = 1"; long[] new_values = new long[4]; new_values[0] = 900; new_values[1] = 1000; new_values[2] = 1100; new_values[3] = 1200; FbCommand update = new FbCommand(updateText, Connection, Transaction); update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values; update.ExecuteNonQuery(); update.Dispose(); PrintArrayValues(new_values, false); }
public void ParameterDescribeTest() { string sql = "insert into TEST (int_field) values (@value)"; FbCommand command = new FbCommand(sql, this.Connection); command.Prepare(); command.Parameters.Add("@value", FbDbType.Integer).Value = 100000; command.ExecuteNonQuery(); command.Dispose(); }
public void NumericArrayPartialUpdateTest() { Console.WriteLine("\r\n"); Console.WriteLine("Numeric/Decimal Array Test"); Console.WriteLine("--------------- ----- ----"); string updateText = "update TEST set narray_field = @array_field " + "WHERE int_field = 1"; decimal[] new_values = new decimal[2]; new_values[0] = 2100.10M; new_values[1] = 2200.20M; FbCommand update = new FbCommand(updateText, Connection, Transaction); update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values; update.ExecuteNonQuery(); update.Dispose(); PrintArrayValues(new_values, false); }
public void NamedParametersAndLiterals() { string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer"; FbCommand command = new FbCommand(sql, this.Connection); command.Parameters.Add("@bigint", FbDbType.BigInt).Value = 200; command.Parameters.Add("@integer", FbDbType.Integer).Value = 1; int recordsAffected = command.ExecuteNonQuery(); command.Dispose(); Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected."); }
public void UpdatedBlobFieldTest() { Console.WriteLine("\r\nUpdate BLOB field with implicit transaction."); FbCommand command = new FbCommand("update TEST set blob_field = @blob_field where int_field = @int_field", Connection); command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1; command.Parameters.Add("@blob_field", FbDbType.Binary).Value = Encoding.Default.GetBytes("Blob field update with implicit transaction"); int i = command.ExecuteNonQuery(); Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed"); // Force the implicit transaction to be committed command.Dispose(); }
public void DoubleArrayPartialUpdateTest() { Console.WriteLine("\r\n"); Console.WriteLine("Double Array Test"); Console.WriteLine("------ ----- ----"); string updateText = "update TEST set barray_field = @array_field " + "WHERE int_field = 1"; double[] new_values = new double[2]; new_values[0] = 1700.10; new_values[1] = 1800.20; FbCommand update = new FbCommand(updateText, Connection, Transaction); update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values; update.ExecuteNonQuery(); update.Dispose(); PrintArrayValues(new_values, false); }
public void ExecuteStoredProcTest() { FbCommand command = new FbCommand("EXECUTE PROCEDURE GETVARCHARFIELD(?)", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input; command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output; command.Parameters[0].Value = 1; // This will fill output parameters values command.ExecuteNonQuery(); Console.WriteLine("Output Parameters"); Console.WriteLine(command.Parameters[1].Value); }
public void TimeStampArrayPartialUpdateTest() { Console.WriteLine("\r\n"); Console.WriteLine("TimeStamp Array Test"); Console.WriteLine("--------- ----- ----"); string updateText = "update TEST set tsarray_field = @array_field " + "WHERE int_field = 1"; DateTime[] new_values = new DateTime[2]; new_values[0] = DateTime.Now.AddSeconds(100); new_values[1] = DateTime.Now.AddSeconds(200); FbCommand update = new FbCommand(updateText, Connection, Transaction); update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values; update.ExecuteNonQuery(); update.Dispose(); PrintArrayValues(new_values, false); }
public void RecordsAffectedTest() { FbCommand selectCommand = new FbCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection); int recordsAffected = selectCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == -1); selectCommand.Dispose(); FbCommand deleteCommand = new FbCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection); recordsAffected = deleteCommand.ExecuteNonQuery(); Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected); Assert.IsTrue(recordsAffected == 0); deleteCommand.Dispose(); }
public void PartialUpdatesTest() { int id_value = this.GetId(); int elements = 16384; string selectText = "SELECT big_array FROM TEST WHERE int_field = " + id_value.ToString(); string insertText = "INSERT INTO TEST (int_field, big_array) values(@int_field, @array_field)"; Console.WriteLine("\r\n\r\nPartialUpdatesTest"); Console.WriteLine("Generating an array of temp data"); // Generate an array of temp data byte[] bytes = new byte[elements*4]; RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); rng.GetBytes(bytes); int[] insert_values = new int[elements]; Buffer.BlockCopy(bytes, 0, insert_values, 0, bytes.Length); Console.WriteLine("Executing insert command"); // Execute insert command FbCommand insert = new FbCommand(insertText, Connection, Transaction); insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value; insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values; insert.ExecuteNonQuery(); insert.Dispose(); Transaction.Commit(); Console.WriteLine("Checking inserted values"); // Check that inserted values are correct FbCommand select = new FbCommand(selectText, Connection); FbDataReader reader = select.ExecuteReader(); if (reader.Read()) { if (!reader.IsDBNull(0)) { int[] select_values = new int[insert_values.Length]; System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length); for (int i = 0; i < insert_values.Length; i++) { if (insert_values[i] != select_values[i]) { throw new Exception("differences at index " + i.ToString()); } } } } Console.WriteLine("Finishing test"); reader.Close(); select.Dispose(); // Start a new Transaction Transaction = Connection.BeginTransaction(); }
public void ExecuteNonQueryWithOutputParameters() { FbCommand command = new FbCommand("EXECUTE PROCEDURE GETASCIIBLOB(?)", Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input; command.Parameters.Add("@CLOB_FIELD", FbDbType.Text).Direction = ParameterDirection.Output; command.Parameters[0].Value = 1; // This will fill output parameters values command.ExecuteNonQuery(); // Check that the output parameter has a correct value Assert.AreEqual("IRow Number 1", command.Parameters[1].Value, "Output parameter value is not valid"); // Dispose command - this will do a transaction commit command.Dispose(); }