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();
}
}
private void writeSql(string sql)
{
if (ConnectTest())
{
try
{
MyOpen();
FbCommand cmd = new FbCommand(sql, _conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
int res = cmd.ExecuteNonQuery();
// FbDataAdapter fbAda = new FbDataAdapter(cmd);
// fbAda.Dispose();
MessageBox.Show(string.Format("Write sucessfull, use time:{0}", res));
cmd.Dispose();
MyClose();
}
catch (Exception ex)
{
MessageBox.Show(string.Format($"写入失败，原因如下：{ex.Message}"));
}
}
}
public void Tabs()//list the tables of dataBase
{
try
{
string sql = "select rdb$field_name from rdb$relation_fields where rdb$relation_name = '" + table + "'; ";
FbCommand cmd = new FbCommand(sql, con);
FbDataReader reader = cmd.ExecuteReader();
al = new ArrayList();
while (reader.Read())
{
al.Add(reader.GetString(0).Trim());
}
columns.DataSource = al;
columns.SelectedIndexChanged += columns_SelectedIndexChanged;
cmd.Dispose();
}
catch (Exception)
{
MessageBox.Show("You are not connected to a database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void SqlServerLikeTest02()
{
FbCommand command = new FbCommand("GETVARCHARFIELD", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Value = 1;
FbDataReader r = command.ExecuteReader();
int count = 0;
while (r.Read())
{
count++;
}
r.Close();
command.Dispose();
Assert.AreEqual(1, count);
}
public void NamedParametersTest()
{
FbCommand command = Connection.CreateCommand();
command.CommandText = "select CHAR_FIELD from TEST where INT_FIELD = @int_field or CHAR_FIELD = @char_field";
command.Parameters.Add("@int_field", 2);
command.Parameters.Add("@char_field", "TWO");
FbDataReader reader = command.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
Assert.AreEqual(1, count, "Invalid number of records fetched.");
reader.Close();
command.Dispose();
}
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();
}
public int[] runQueryInt(string query, bool dataReturned)
{
List <int> Lint = new List <int>();
openConnection();
myTransaction = myConnection.BeginTransaction();
FbCommand command = new FbCommand(query, myConnection);
command.CommandType = CommandType.TableDirect;
command.Transaction = myTransaction;
using (IDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Lint.Add((int)reader.GetInt16(0));
}
}
myTransaction.Commit();
command.Dispose();
closeConnection();
// clean up the query result array because the FetchSize
// returns more than the actual number of rows with useful data
return(Lint.ToArray());
}
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 FirebirdLikeTest01()
{
FbCommand command = new FbCommand("SELECT * FROM GETVARCHARFIELD(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters[0].Value = 1;
// This will fill output parameters values
FbDataReader reader = command.ExecuteReader();
reader.Read();
// Print output value
TestContext.WriteLine("Output Parameters - Result of SELECT command");
TestContext.WriteLine(reader[0]);
reader.Close();
// Dispose command - this will do a transaction commit
command.Dispose();
}
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 TimeArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("Time Array Test");
Console.WriteLine("---- ----- ----");
string updateText = "update TEST set tarray_field = @array_field " +
"WHERE int_field = 1";
TimeSpan[] new_values = new TimeSpan[2];
new_values[0] = new TimeSpan(11, 13, 14);
new_values[1] = new TimeSpan(12, 15, 16);
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 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 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 Tabs()//list the tables of dataBase
{
try
{
string sql = "SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$VIEW_BLR IS NULL AND RDB$SYSTEM_FLAG =0;";
FbCommand cmd = new FbCommand(sql, con);
FbDataReader reader = cmd.ExecuteReader();
al = new ArrayList();
while (reader.Read())
{
al.Add(reader.GetString(0).Trim());
}
tabs.DataSource = al;
tabs.SelectedIndexChanged += tabs_SelectedIndexChanged;
cmd.Dispose();
}
catch (Exception)
{
MessageBox.Show("You are not connected to a database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void OKButton_Click(object sender, RoutedEventArgs e)
{
var nxt = NextId();
var s = "";
var t = 0;
while (NameBox.Text.IndexOf('\'', t) > 0)
{
s += NameBox.Text.Substring(t, NameBox.Text.IndexOf('\'', t) - t + 1) + '\'';
t = NameBox.Text.IndexOf('\'', t) + 1;
}
s += NameBox.Text.Substring(t, NameBox.Text.Length - t);
var insertSql = new FbCommand($"INSERT INTO MEDICINE (ID, NAME) VALUES ({nxt}, '{s}');", fb, fbt);
insertSql.ExecuteNonQuery();
if (SubstanceCBox.SelectedItem == null)
{
MessageBox.Show("Оберіть хоч одну діючу речовину");
}
else
{
foreach (var p in substanceCBs.Where(p => p.SelectedItem != null))
{
insertSql = new FbCommand(
$"INSERT INTO INGREDIENT (KMEDICINE, KSUBSTANCE, AMOUNT) VALUES ({nxt}, {substanceId[p.SelectedIndex]}, {Convert.ToDouble(concentrationBs[substanceCBs.IndexOf(p)].Text)});",
fb, fbt);
insertSql.ExecuteNonQuery();
}
insertSql = new FbCommand(
$"INSERT INTO WAREHOUSE (KMEDICINE, AMOUNT) VALUES ({nxt}, {Convert.ToDouble(AmountBox.Text)});",
fb, fbt);
insertSql.ExecuteNonQuery();
insertSql.Dispose();
Close();
}
}
public void NextResultTest()
{
string querys = "select * from TEST order by INT_FIELD asc;" +
"select * from TEST order by INT_FIELD desc;";
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand(querys, Connection, transaction);
FbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
Console.WriteLine("===");
if (reader.NextResult())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
/// <summary>
/// Executes a query and stores the results in a DataTable
/// </summary>
/// <param name="query">SQL query to execute</param>
/// <returns>DataTable of results</returns>
public System.Data.DataTable ExecuteQuery(string query)
{
DataTable dt = null;
if (IsOpen)
{
query = AdjustQuotedFields(query);
dt = new DataTable();
FbCommand myCmd = new FbCommand(query, fbDBConnection);
myCmd.CommandType = CommandType.Text;
try
{
myCmd.ExecuteNonQuery();
FbDataAdapter da = new FbDataAdapter(myCmd);
da.Fill(dt);
}
catch (Exception ex)
{
throw new FirebirdException("Cannot execute the SQL statement \r\n" + query + "\r\n" + ex.Message);
}
finally
{
if (myCmd != null)
{
myCmd.Dispose();
myCmd = null;
}
}
}
else
{
throw new FirebirdException("Firebird database is not open.");
}
return(dt);
}
public int insertarDetalleMovArticulo(int idMovArt, Item articulo)
{
try
{
int id = 0;
open();
String sqlQuery = "select next value for GEN_NUM_DET_MOV_ARTICULOS from RDB$DATABASE";
FbCommand sqlCommand = new FbCommand(sqlQuery, connection);
FbDataReader idReader = sqlCommand.ExecuteReader();
while (idReader.Read())
{
id = idReader.GetInt32(0);
}
idReader.Close();
FbTransaction insertTransaction = connection.BeginTransaction();
FbCommand insertCommand = new FbCommand();
insertCommand.CommandText = "insert into DET_MOV_ARTICULOS (NUMERO,NROINT,ARTICU,CANTID,CTRCOM,TRANID,STKFIS) values" +
" (" + id + "," + idMovArt + ",'" + articulo.codigoArticulo + "'," + articulo.cantidad + ","
+ id + "," + 0 + "," + 0 + ")";
insertCommand.Connection = connection;
insertCommand.Transaction = insertTransaction;
insertCommand.ExecuteNonQuery();
insertTransaction.Commit();
insertCommand.Dispose();
close();
return(id);
}
catch (Exception e)
{
return(0);
throw new Exception(e.Message.ToString());
}
}
public void DisposeAll()
{
if (_com != null)
{
_com.Dispose();
_com = null;
}
if (_con != null)
{
_con.Dispose();
_con = null;
}
if (_adapter != null)
{
_adapter.Dispose();
_adapter = null;
}
_tran = null;
}
public DataTable GetSchema(FbConnection connection, string collectionName, string[] restrictions)
{
DataTable dataTable = new DataTable(collectionName);
FbCommand command = BuildCommand(connection, collectionName, ParseRestrictions(restrictions));
FbDataAdapter adapter = new FbDataAdapter(command);
try
{
adapter.Fill(dataTable);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataTable);
return(ProcessResult(dataTable));
}
public void get()
{
this.Clear();
try
{
FbConnection fb = new FbConnection(connection.connectionString());
fb.Open();
FbTransaction fbt = fb.BeginTransaction(); //стартуем транзакцию; стартовать транзакцию можно только для открытой базы (т.е. мутод Open() уже был вызван ранее, иначе ошибка)
FbCommand SelectSQL = new FbCommand();
SelectSQL.CommandText = "select d.name,a.price,a.quanshop, a.usernameback from backshop a, goods b, name d where a.goodscode=b.goodscode and b.namecode=d.namecode and a.datatimeback>@datetime"; //задаем запрос на выборку
SelectSQL.Parameters.Add("@datetime", DateTime.Now.Date);
SelectSQL.Connection = fb;
SelectSQL.Transaction = fbt; //необходимо проинициализить транзакцию для объекта SelectSQL
FbDataReader reader = SelectSQL.ExecuteReader(); //для запросов, которые возвращают результат в виде набора данных надо использоваться метод ExecuteReader()
try
{
while (reader.Read()) //пока не прочли все данные выполняем...
{
var nch = new strokavozvrata(reader.GetString(0), reader.GetInt16(2), reader.GetDouble(1), reader.GetString(3));
this.Add(nch);
}
}
finally
{
//всегда необходимо вызывать метод Close(), когда чтение данных завершено
reader.Close();
fb.Close(); //закрываем соединение, т.к. оно нам больше не нужно
}
SelectSQL.Dispose();
}
catch (Exception e)
{
}
}
public void FillMultipleTest()
{
var transaction = Connection.BeginTransaction();
var command = new FbCommand("select * from TEST", Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
var builder = new FbCommandBuilder(adapter);
var ds1 = new DataSet();
var ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
private void CreateExamButton_Click(object sender, EventArgs e)
{
metroButton1.Select();
if (CourseComboBox.Text.Length < 1)
{
MessageBox.Show("Выберите курс!");
return;
}
string questions = get_questions(int.Parse(CourseComboBox.SelectedValue.ToString()));
if (fb.State == ConnectionState.Closed)
{
fb.Open();
}
FbTransaction fbt = fb.BeginTransaction();
FbCommand InsertSQL = new FbCommand("INSERT INTO final_exams VALUES (0, @USER_ID, @COURSE_ID, @QUESTIONS, @NOTHING, @NOTHING)", fb);
InsertSQL.Parameters.Add("USER_ID", FbDbType.Integer).Value = userid;
InsertSQL.Parameters.Add("COURSE_ID", FbDbType.Integer).Value = int.Parse(CourseComboBox.SelectedValue.ToString());
InsertSQL.Parameters.Add("NOTHING", FbDbType.Text).Value = "";
InsertSQL.Parameters.Add("QUESTIONS", FbDbType.Text).Value = questions;
InsertSQL.Transaction = fbt;
try
{
int res = InsertSQL.ExecuteNonQuery();
MessageBox.Show("Добавлено!");
fbt.Commit();
InsertSQL.Dispose();
fb.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
updateExams();
}
public void InsertGuidTest()
{
var newGuid = Guid.Empty;
var guidValue = Guid.NewGuid();
// Insert the Guid
var insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection);
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
using (var select = new FbCommand("SELECT * FROM GUID_TEST", Connection))
using (var r = select.ExecuteReader())
{
if (r.Read())
{
newGuid = r.GetGuid(1);
}
}
Assert.AreEqual(guidValue, newGuid);
}
private void updateExams()
{
user_exams.Clear();
if (fb.State == ConnectionState.Closed)
{
fb.Open();
}
FbTransaction fbt = fb.BeginTransaction();
FbCommand SelectSQL = new FbCommand("SELECT id, course_id, exam_time FROM final_exams WHERE user_id = " + userid, fb);
SelectSQL.Transaction = fbt;
FbDataReader reader = SelectSQL.ExecuteReader();
while (reader.Read())
{
user_exams.Rows.Add(int.Parse(reader[0].ToString()), int.Parse(reader[1].ToString()), reader[2].ToString());
}
reader.Close();
SelectSQL.Dispose();
fbt.Commit();
fb.Close();
updateForm();
}
public FinalExamForm(int userid)
{
InitializeComponent();
user_exams.Columns.Add("id", typeof(int));
user_exams.Columns.Add("course_id", typeof(int));
user_exams.Columns.Add("exam_time", typeof(string));
this.userid = userid;
fb.Open();
FbTransaction fbt = fb.BeginTransaction();
FbCommand SelectSQL = new FbCommand("SELECT * FROM users WHERE id = " + this.userid, fb);
SelectSQL.Transaction = fbt;
FbDataReader reader = SelectSQL.ExecuteReader();
reader.Read();
UserNameLabel.Text = reader[2].ToString() + " " + reader[1].ToString() + " " + reader[3].ToString();
reader.Close();
SelectSQL.Dispose();
fbt.Commit();
fb.Close();
}
public void GetValuesTest()
{
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < values.Length; i++)
{
Console.Write(values[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
/////////////////////////////////////////////////////////////////////////////////////
private int exec_proc_ADD_COMMON(ref FbConnection Connection, ref FbTransaction Transaction, ref BattleResult_v2 BRv2)
{
FbCommand myCommand = new FbCommand();
myCommand.CommandText = "execute procedure ADD_COMMON ("
+ BRv2.ArenaUniqueID.ToString() + ","
+ BRv2.Common["arenaTypeID"].ToString() + ","
+ BRv2.Common["arenaCreateTime"].ToString() + ","
+ BRv2.Common["winnerTeam"].ToString() + ","
+ BRv2.Common["finishReason"].ToString() + ","
+ BRv2.Common["duration"].ToString(cultureUS) + ","
+ BRv2.Common["bonusType"].ToString() + ","
+ BRv2.Common["guiType"].ToString() + ","
+ BRv2.Common["vehLockMode"].ToString() + ")";
myCommand.Connection = Connection;
myCommand.Transaction = Transaction;
Int32 BATTLE_ID = (Int32)myCommand.ExecuteScalar();
myCommand.Dispose();
return(BATTLE_ID);
}
public static void Alterar(Model.FuncionarioModel funcionario)
{
var conexaoFireBird = Connection.GetInstancia().GetConexao();
conexaoFireBird.Open();
var commandText = new StringBuilder();
var transaction = conexaoFireBird.BeginTransaction();
var cmd = new FbCommand(commandText.ToString(), conexaoFireBird, transaction);
try
{
const string mSql = @"Update Funcionario set usuario_funcionario= @UsuarioFuncionario, senha_funcionario= @SenhaFuncionario,
id_pessoa= @IdPessoa, id_cargo= @Cargo WHERE id_funcionario= @IdFuncionario";
cmd.Parameters.Add("@UsuarioFuncionario", FbDbType.VarChar).Value = funcionario.UsuarioFuncionario;
cmd.Parameters.Add("@SenhaFuncionario", FbDbType.VarChar).Value = funcionario.SenhaFuncionario;
cmd.Parameters.Add("@IdPessoa", FbDbType.VarChar).Value = funcionario.IdPessoa;
cmd.Parameters.Add("@Cargo", FbDbType.VarChar).Value = funcionario.Cargo;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
cmd.Dispose();
conexaoFireBird.Close();
}
}
private int NextId()
{
var selectSql = new FbCommand("SELECT MAX(ID) FROM MEDICINE;", fb)
{
Transaction = fbt
};
var reader = selectSql.ExecuteReader();
var s = 0;
try
{
while (reader.Read())
{
s = reader.GetInt32(0);
}
}
finally
{
reader.Close();
}
selectSql.Dispose();
return(s + 1);
}
private static void CreateProcedures(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// DATAREADERTEST
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("content VARCHAR(128))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("content = 'test';\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void CreateTables(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("IARRAY_FIELD INTEGER [0:3],");
commandText.Append("SARRAY_FIELD SMALLINT [0:4],");
commandText.Append("LARRAY_FIELD BIGINT [0:5],");
commandText.Append("FARRAY_FIELD FLOAT [0:3],");
commandText.Append("BARRAY_FIELD DOUBLE PRECISION [1:4],");
commandText.Append("NARRAY_FIELD NUMERIC(10,6) [1:4],");
commandText.Append("DARRAY_FIELD DATE [1:4],");
commandText.Append("TARRAY_FIELD TIME [1:4],");
commandText.Append("TSARRAY_FIELD TIMESTAMP [1:4],");
commandText.Append("CARRAY_FIELD CHAR(21) [1:4],");
commandText.Append("VARRAY_FIELD VARCHAR(30) [1:4],");
commandText.Append("BIG_ARRAY INTEGER [1:32767],");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000),");
commandText.Append("CS_FIELD CHAR(1) CHARACTER SET UNICODE_FSS,");
commandText.Append("UCCHAR_ARRAY CHAR(10) [1:10] CHARACTER SET UNICODE_FSS);");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
private static void CreateTriggers(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// new_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// update_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}