public void FetchGlobalSetup()
{
GlobalSetupBase();
using (var conn = new FbConnection(ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"create table foobar (x {DataType})";
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $@"execute block as
declare cnt int;
begin
cnt = 200000;
while (cnt > 0) do
begin
insert into foobar values (:cnt);
cnt = cnt - 1;
end
end";
cmd.ExecuteNonQuery();
}
}
}
protected override IDataReader PrepareSQLDatasetInternal(String sql, bool schemaOnly)
{
FbCommand command;
FbDataReader reader;
try
{
if (!Connected)
{
Connect();
}
command = _connection.CreateCommand();
command.CommandTimeout = CommandTimeout;
command.CommandText = sql;
if (schemaOnly)
{
reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
}
else
{
reader = command.ExecuteReader();
}
}
catch (Exception e)
{
throw new QueryBuilderException(ErrorCode.ErrorExecutingQuery,
e.Message + "\n\n" + Helpers.Localizer.GetString("strQuery", Constants.strQuery) + "\n" + sql);
}
return(reader);
}
public override int ExecuteNonQuery(string commandText)
{
using (var command = connection.CreateCommand()) {
command.CommandTimeout = 0;
command.CommandText = commandText;
return(command.ExecuteNonQuery());
}
}
/// <summary>
/// 储存到数据库里
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button2_Click(object sender, EventArgs e)
{
//存图片
string ImagePath = @"D:\XD\1-dis\UI2\firebird\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg";
Cv2.ImWrite(ImagePath, transformed_pic);
//存客户
using (FbCommand insertData = conn.CreateCommand())
{
insertData.CommandText = "insert into Client values(@ClientName, @AddressPhone)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@ClientName", FbDbType.VarChar).Value = TextBox_ClientName.Text;
insertData.Parameters.Add("@AddressPhone", FbDbType.VarChar).Value = TextBox_AddressNPhonenumber.Text;
insertData.ExecuteNonQuery();
}
//存销售单
using (FbCommand insertData = conn.CreateCommand())
{
insertData.CommandText = "insert into SellingTable values(@SheetID, @ClientName, @Dealer, @Datee, @Money, @Creator, @Picture)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@SheetID", FbDbType.VarChar).Value = TextBox_SheetID.Text;
insertData.Parameters.Add("@ClientName", FbDbType.VarChar).Value = TextBox_ClientName.Text;
insertData.Parameters.Add("@Dealer", FbDbType.VarChar).Value = TextBox_Dealer.Text;
insertData.Parameters.Add("@Datee", FbDbType.VarChar).Value = TextBox_Date.Text;
insertData.Parameters.Add("@Money", FbDbType.Float).Value = 136;
insertData.Parameters.Add("@Creator", FbDbType.VarChar).Value = "管理员";
insertData.Parameters.Add("@Picture", FbDbType.VarChar).Value = ImagePath;
insertData.ExecuteNonQuery();
}
int intA;
for (int i = 0; i < length; i++)
{
//销售单详表
using (FbCommand insertData = conn.CreateCommand())
{
insertData.CommandText = "insert into SellingTableInfo values(@SheetID, @ItemName, @Number, @Price, @Money, @Note)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@SheetID", FbDbType.VarChar).Value = TextBox_SheetID.Text;
insertData.Parameters.Add("@ItemName", FbDbType.VarChar).Value = dataGridView1.Rows[i].Cells[0].Value.ToString();
insertData.Parameters.Add("@Number", FbDbType.Integer).Value = dataGridView1.Rows[i].Cells[4].Value.ToString();
int.TryParse(dataGridView1.Rows[i].Cells[5].Value.ToString(), out intA);
insertData.Parameters.Add("@Price", FbDbType.Float).Value = intA;
int.TryParse(dataGridView1.Rows[i].Cells[6].Value.ToString(), out intA);
insertData.Parameters.Add("@Money", FbDbType.Float).Value = intA;
insertData.Parameters.Add("@Note", FbDbType.VarChar).Value = dataGridView1.Rows[i].Cells[7].Value.ToString();
insertData.ExecuteNonQuery();
}
}
label1.Text = "储存完成！";
}
public override void SetAndReleaseItemExclusive(HttpContext context, string id, SessionStateStoreData item, object lockId, bool newItem)
{
string sessionItems = Serialize((SessionStateItemCollection)item.Items);
using (FbConnection conn = new FbConnection(this.connectionString))
{
conn.Open();
if (newItem)
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM SESSIONS WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME AND EXPIRES < @EXPIRES";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.ExecuteNonQuery();
}
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO SESSIONS (SESSIONID, APPLICATIONNAME, CREATED, EXPIRES, " +
"LOCKDATE, LOCKID, TIMEOUT, LOCKED, SESSIONITEMS, FLAGS) VALUES(@SESSIONID, " +
"@APPLICATIONNAME, @CREATED, @EXPIRES, @LOCKDATE, @LOCKID , @TIMEOUT, @LOCKED, " +
"@SESSIONITEMS, @FLAGS)";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@CREATED", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now.AddMinutes((Double)item.Timeout);
cmd.Parameters.Add("@LOCKDATE", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@LOCKID", FbDbType.Integer).Value = 0;
cmd.Parameters.Add("@TIMEOUT", FbDbType.Integer).Value = item.Timeout;
cmd.Parameters.Add("@LOCKED", FbDbType.SmallInt).Value = false;
cmd.Parameters.Add("@SESSIONITEMS", FbDbType.Text, sessionItems.Length).Value = sessionItems;
cmd.Parameters.Add("@FLAGS", FbDbType.Integer).Value = 0;
cmd.ExecuteNonQuery();
}
}
else
{
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE SESSIONS SET EXPIRES = @EXPIRES, SESSIONITEMS = @SESSIONITEMS, " +
"LOCKED = @LOCKED WHERE SESSIONID = @SESSIONID AND APPLICATIONNAME = @APPLICATIONNAME AND " +
"LOCKID = @LOCKID";
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now.AddMinutes((Double)item.Timeout);
cmd.Parameters.Add("@SESSIONITEMS", FbDbType.Text, sessionItems.Length).Value = sessionItems;
cmd.Parameters.Add("@LOCKED", FbDbType.SmallInt).Value = false;
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@LOCKID", FbDbType.Integer).Value = lockId;
cmd.ExecuteNonQuery();
}
}
}
}
public void CreateTables()
{
Logger.Debug("Starting database table creation.");
using (FbCommand createDomain = con.CreateCommand())
{
createDomain.CommandText = "CREATE DOMAIN BOOLEAN AS SMALLINT CHECK (value is null or value in (0, 1))";
createDomain.ExecuteNonQuery();
}
using (FbCommand createTable = con.CreateCommand())
{
createTable.CommandText =
"CREATE TABLE eddb_systems (id int, name varchar(150), x float, y float, z float, faction varchar(150), population bigint, goverment varchar(130), allegiance varchar(130), state varchar(130), " +
"security varchar(150), primary_economy varchar(130), power varchar(130), power_state varchar(130), needs_permit boolean, updated_at bigint, simbad_ref varchar(150), lowercase_name varchar(150))";
createTable.ExecuteNonQuery();
}
using (FbCommand createTable = con.CreateCommand())
{
createTable.CommandText =
"CREATE TABLE eddb_stations (id bigint, name varchar(150), system_id bigint, max_landing_pad_size varchar(5), distance_to_star bigint, faction varchar(150), goverment varchar(120), allegiance varchar(130), " +
"state varchar(120), type_id int, type varchar(130), has_blackmarket boolean, has_market boolean, has_refuel boolean, has_repair boolean, has_rearm boolean, has_outfitting boolean, has_shipyard boolean, has_docking boolean, " +
"has_commodities boolean, prohibited_commodities varchar(10000), economies varchar(10000), updated_at bigint, shipyard_updated_at bigint, outfitting_updated_at bigint, market_updated_at bigint, is_planetary boolean, " +
"selling_ships varchar(20000), selling_modules varchar(20000), lowercase_name varchar(150))";
createTable.ExecuteNonQuery();
}
using (FbCommand createIndex = con.CreateCommand())
{
createIndex.CommandText = "CREATE INDEX ix_lcname on eddb_systems (lowercase_name)";
createIndex.ExecuteNonQuery();
}
Logger.Debug("Completed database table creation.");
Status = "Initialized";
}
public DataTable ReturnDataTable(string SQL)
{
using (FbCommand selectData = DBConnection.CreateCommand())
{
DataTable dt = new DataTable();
selectData.CommandText = SQL;
FbDataAdapter dat = new FbDataAdapter(selectData);
dat.Fill(dt);
return(dt);
}
}
private QueryResult ExecuteReader(FbConnection con, string query)
{
using (var command = con.CreateCommand())
{
command.CommandText = query;
var res = command.BeginExecuteReader(null, null);
var reader = command.EndExecuteReader(res);
var schema = reader.GetSchemaTable();
var table = new DataTable();
for (var i = 0; i < schema.Rows.Count; i++)
{
var col = new DataColumn();
col.ColumnName = schema.Rows[i]["ColumnName"].ToString();
col.DataType = Type.GetType(schema.Rows[i]["DataType"].ToString());
table.Columns.Add(col);
}
var startTime = DateTime.Now;
while (reader.Read())
{
var row = table.NewRow();
for (var i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
table.Rows.Add(row);
}
var executeTime = DateTime.Now - startTime;
return(new QueryResult(table, executeTime, query, table.Rows.Count));
}
}
protected FbCommand BuildCommand(FbConnection connection, object[] restrictions)
{
DataView collections = FbMetaDataCollections.GetSchema().DefaultView;
collections.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (collections.Count == 0)
{
throw new NotSupportedException("Unsupported collection name.");
}
if (restrictions != null &&
restrictions.Length > (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("The number of specified restrictions is not valid.");
}
DataView restriction = FbRestrictions.GetSchema().DefaultView;
restriction.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (restriction.Count != (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("Incorrect restriction definitions.");
}
StringBuilder builder = this.GetCommandText(restrictions);
FbCommand schema = connection.CreateCommand();
schema.CommandText = builder.ToString();
if (connection.InnerConnection.HasActiveTransaction)
{
schema.Transaction = connection.InnerConnection.ActiveTransaction;
}
if (restrictions != null && restrictions.Length > 0)
{
// Add parameters
int index = 0;
for (int i = 0; i < restrictions.Length; i++)
{
string rname = restriction[i]["RestrictionDefault"].ToString().ToLower(CultureInfo.CurrentCulture);
if (restrictions[i] != null &&
!rname.EndsWith("_catalog") &&
!rname.EndsWith("_schema") &&
rname != "table_type")
{
string pname = String.Format(CultureInfo.CurrentCulture, "@p{0}", index++);
FbParameter p = schema.Parameters.Add(pname, restrictions[i].ToString());
p.FbDbType = FbDbType.VarChar;
p.Size = 255;
}
}
}
return(schema);
}
public IEnumerable <IndexInfo> GetIndex(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText = @"select idx.rdb$index_name Name, seg.rdb$field_name FiledName from rdb$indices idx left outer join rdb$index_segments seg on idx.rdb$index_name = seg.rdb$index_name where rdb$relation_name = '{this.TableName}' and idx.rdb$system_flag = 0 order by seg.rdb$field_position";
var reader = command.ExecuteReader();
var tmpName = "";
IndexInfo tmpInf = null;
while (reader.Read())
{
if (tmpName == (string)reader["Name"])
{
tmpInf.FieldNames.Add((string)reader["FiledName"]);
}
else
{
if (tmpInf != null)
{
yield return(tmpInf);
}
tmpInf = new IndexInfo();
tmpInf.Name = (string)reader["Name"];
tmpInf.FieldNames.Add((string)reader["FiledName"]);
}
}
if (tmpInf != null)
{
yield return(tmpInf);
}
}
}
public IEnumerable <ColumInfo> GetColums(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select rf.rdb$field_name Name, f.rdb$field_type Type, f.rdb$character_length CharSize, ky.rdb$constraint_type ConstraintType " +
"from rdb$relation_fields rf " +
"join rdb$relations r on rf.rdb$relation_name = r.rdb$relation_name " +
"and r.rdb$view_blr is null " +
"and rdb$relation_type = 0 and r.rdb$system_flag = 0 " +
"join rdb$fields f on f.rdb$field_name = rf.rdb$field_source " +
"left outer join (select rel.rdb$relation_name, seg.rdb$field_name, rel.rdb$constraint_type " +
"from rdb$relation_constraints rel " +
"left outer join rdb$indices idx on rel.rdb$index_name = idx.rdb$index_name " +
"left outer join rdb$index_segments seg on idx.rdb$index_name = seg.rdb$index_name) ky " +
"on ky.rdb$relation_name = rf.rdb$relation_name and ky.rdb$field_name = rf.rdb$field_name " +
$"where rf.rdb$relation_name = '{this.TableName}' " +
"order by rf.rdb$field_position; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var key = (reader["ConstraintType"] == DBNull.Value) ? "" : (string)reader["ConstraintType"];
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
yield return(new ColumInfo(((string)reader["Name"]).TrimEnd(), (short)reader["Type"], size, GetConstraint(key)));
}
}
}
private void CreateTestTablesFb3(string connectionString)
{
using (var con = new FbConnection(connectionString))
using (var command = con.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("recreate table test(");
sb.Append("int_test integer default 0 not null primary key,");
sb.Append("bigint_test bigint,");
sb.Append("blob_test blob,");
sb.Append("char_test char(20),");
sb.Append("date_test date,");
sb.Append("decimal_test decimal,");
sb.Append("double_test double precision,");
sb.Append("float_test float,");
sb.Append("numeric_test numeric,");
sb.Append("smallint_test smallint,");
sb.Append("time_test time,");
sb.Append("timestamp_test timestamp,");
sb.Append("varchar_test varchar(100),");
sb.Append("boolean_test boolean");
sb.Append(")");
command.CommandText = sb.ToString();
con.Open();
command.ExecuteNonQuery();
}
}
public void DoNotGoBackToPoolAfterBroken()
{
var csb = BuildConnectionStringBuilder(FbServerType, Compression, WireCrypt);
csb.Pooling = true;
using (var conn = new FbConnection(csb.ToString()))
{
conn.Open();
}
using (var conn = new FbConnection(csb.ToString()))
{
conn.Open();
try
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from mon$statements union all select * from mon$statements";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
}
}
}
}
catch (FbException)
{ }
}
}
public override void Update(Aluno aluno)
{
if (Get(a => a.CPF == aluno.CPF && a.Matricula != aluno.Matricula).FirstOrDefault() != null)
{
throw new CPFInvalidoException("CPF já está sendo usado por outro aluno. Digite um CPF válido.");
}
using var fbConexao = new FbConnection(StringConexao);
fbConexao.Open();
var comando = $@"UPDATE ALUNOS
SET
NOME = @nome,
SEXO = @sexo,
NASCIMENTO = @nascimento,
CPF = @cpf
WHERE MATRICULA = {aluno.Matricula}";
using var fbCmd = fbConexao.CreateCommand();
fbCmd.CommandText = comando;
fbCmd.Parameters.AddWithValue("@nome", aluno.Nome);
fbCmd.Parameters.AddWithValue("@sexo", (int)aluno.Sexo);
fbCmd.Parameters.AddWithValue("@nascimento", aluno.Nascimento);
fbCmd.Parameters.AddWithValue("@cpf", aluno.CPF);
fbCmd.ExecuteNonQuery();
}
} // End Function CreateDatabase
public static void ExecuteNonQuery(string strSQL)
{
using (System.Data.IDbConnection con = new FbConnection(GetConnectionString()))
{
try
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
} // End Using System.Data.IDbCommand cmd
} // End Try
catch (System.Data.Common.DbException ex)
{
System.Console.WriteLine(ex.Message);
throw;
} // End Catch
finally
{
if (con != null)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End if (con != null)
} // End Finally
} // End Using System.Data.IDbConnection con
} // End Sub ExecuteNonQuery
public static void InsertNewMethodLog(string methodName, string token)
{
try
{
if (IsServer)
{
FbConnection connection = GetConnection();
Open();
using (FbCommand insertData = connection.CreateCommand())
{
insertData.CommandText =
"insert into MethodLogs values (@token, @methodName, @status, NULL, @dt, NULL, NULL)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@token", FbDbType.VarChar, 50).Value = token;
insertData.Parameters.Add("@methodName", FbDbType.VarChar, 200).Value = methodName;
insertData.Parameters.Add("@status", FbDbType.Integer).Value = -1;
insertData.Parameters.Add("@dt", FbDbType.TimeStamp).Value = DateTime.Now;
insertData.ExecuteNonQuery();
}
}
}
catch (Exception)
{
//eat exceptions of firebird. TODO
}
}
private List <object[]> QueryFb(string connectionstring, string query)
{
try
{
using (FbConnection dbConn = new FbConnection(connectionstring))
{
Console.WriteLine("Consultando Control Escolar");
dbConn.Open();
using (var command = dbConn.CreateCommand())
{
command.CommandText = query;
using (var reader = command.ExecuteReader())
{
var rows = new List <object[]>();
while (reader.Read())
{
var columns = new object[reader.FieldCount];
reader.GetValues(columns);
rows.Add(columns);
}
return(rows);
}
}
}
}catch (System.Exception oe)
{
throw new Exception(oe.Message);
}
}
private void Login(object sender, RoutedEventArgs e)
{
string name = loginName.Text;
string pass = loginPass.Text;
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(pass))
{
MessageBox.Show("Something is Empty!");
return;
}
using (FbConnection conn = new FbConnection(@"Server=localhost;User=SYSDBA;Password=admin;Database=C:\Users\Norbert\Documents\Visual Studio 2015\Projects\NCarRental\NCarRental\_Resources\DB.FDB"))
using (FbCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "select name from users where name=@name and pass=@pass";
cmd.Parameters.Add("name", name);
cmd.Parameters.Add("pass", pass);
var result = cmd.ExecuteScalar();
Console.WriteLine(result);
if (result == null)
{
MessageBox.Show("The username or the password is wrong");
return;
}
MessageBox.Show("Done!");
loginName.Text = "";
loginPass.Text = "";
}
}
private static List <DBGenerator> GetGenerators(FbConnection con)
{
using (var cmd = con.CreateCommand())
{
var generators = new List <DBGenerator>();
string[] restrict4 = { null, null, null, null };
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = null;
restrict4[3] = null;
var dtGenerators = con.GetSchema("Generators", restrict4);
for (var i = 0; i < dtGenerators.Rows.Count; i++)
{
var trRow = dtGenerators.Rows[i];
if (Convert.ToBoolean(trRow["IS_SYSTEM_GENERATOR"], CultureInfo.InvariantCulture))
{
continue;
}
var eGenerator = new DBGenerator
{
Name = trRow["GENERATOR_NAME"].ToString()
};
cmd.CommandText = string.Format("SELECT gen_id(\"{0}\", 0) FROM rdb$database", eGenerator.Name);
eGenerator.StartValue = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
generators.Add(eGenerator);
}
return(generators);
}
}
public override void Add(Aluno aluno)
{
if (GetByMatricula(aluno.Matricula) != null)
{
throw new MatriculaInvalidaException("Matricula já está sendo usada por outro aluno. Digite uma Matrícula válida.");
}
if (Get(a => a.CPF == aluno.CPF).FirstOrDefault() != null)
{
throw new CPFInvalidoException("CPF já está sendo usado por outro aluno. Digite um CPF válido.");
}
using var fbConexao = new FbConnection(StringConexao);
fbConexao.Open();
const string comando = @"INSERT INTO ALUNOS (MATRICULA, NOME, SEXO, NASCIMENTO, CPF)
VALUES (@matricula, @nome, @sexo, @nascimento, @cpf)";
using var fbCmd = fbConexao.CreateCommand();
fbCmd.CommandText = comando;
fbCmd.Parameters.AddWithValue("@matricula", aluno.Matricula);
fbCmd.Parameters.AddWithValue("@nome", aluno.Nome);
fbCmd.Parameters.AddWithValue("@sexo", (int)aluno.Sexo);
fbCmd.Parameters.AddWithValue("@nascimento", aluno.Nascimento);
fbCmd.Parameters.AddWithValue("@cpf", aluno.CPF);
fbCmd.ExecuteNonQuery();
}
private static string HelpDbscColumnDefault(FbConnection con, string cname, string tname)
{
using (var cmd = con.CreateCommand())
using (var result = new DataTable())
{
result.Locale = CultureInfo.InvariantCulture;
cmd.CommandText =
string.Format(
@"
SELECT
rfr.rdb$default_source AS DEFAULT_SOURCE
FROM
rdb$relation_fields rfr
WHERE
rfr.rdb$relation_name = '{0}' AND rfr.rdb$field_name='{1}';" ,
tname, cname);
using (var adapter = new FbDataAdapter(cmd))
adapter.Fill(result);
if (result.Rows.Count > 1)
{
throw new DBSchemaException("Ambiguous column");
}
return(result.Rows[0]["DEFAULT_SOURCE"].ToString());
}
}
public override IEnumerable <Aluno> GetAll()
{
using var fbConexao = new FbConnection(StringConexao);
fbConexao.Open();
var comando = @"SELECT * FROM ALUNOS";
using var fbCmd = fbConexao.CreateCommand();
fbCmd.CommandText = comando;
using var fbDtAd = new FbDataAdapter(fbCmd);
var dtble = new DataTable();
fbDtAd.Fill(dtble);
List <Aluno> alunos = new List <Aluno>();
for (var i = 0; i < dtble.Rows.Count; i++)
{
var aluno = new Aluno()
{
Matricula = (int)dtble.Rows[i][0],
Nome = dtble.Rows[i][1].ToString(),
Sexo = (EnumeradorSexo)dtble.Rows[i][2],
Nascimento = (DateTime)dtble.Rows[i][3],
CPF = dtble.Rows[i][4].ToString()
};
alunos.Add(aluno);
}
return(alunos);
}
protected bool IsInDatabase(Action <FbCommand> adjustCommand)
{
bool result;
using (var connection = new FbConnection(ConnectionString))
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
adjustCommand(cmd);
using (var reader = cmd.ExecuteReader())
{
result = reader.Read();
}
}
tx.Commit();
}
}
return(result);
}
public async Task DoNotGoBackToPoolAfterBroken()
{
var csb = BuildConnectionStringBuilder(ServerType, Compression, WireCrypt);
csb.Pooling = true;
await using (var conn = new FbConnection(csb.ToString()))
{
await conn.OpenAsync();
}
await using (var conn = new FbConnection(csb.ToString()))
{
await conn.OpenAsync();
try
{
await using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from mon$statements union all select * from mon$statements";
await using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
}
}
}
}
catch (FbException)
{ }
}
}
public async Task <IEnumerable <string> > ListOfX(FbConnection connection, string sqlName, bool noSys = true)
{
var lst = new List <string>();
using (var c = connection.CreateCommand())
using (c.Transaction = connection.BeginTransaction())
try
{
c.CommandText = $"SELECT DISTINCT R.RDB${sqlName}_NAME FROM RDB${sqlName}S R";
if (noSys)
{
c.CommandText += " where (RDB$SYSTEM_FLAG = 0)";
}
using (var reader = await c.ExecuteReaderAsync())
while (reader.Read())
{
lst.Add(reader.GetString(0));
}
}
finally
{
c.Transaction.Rollback();
}
return(lst);
}
protected IEnumerable <IDictionary <string, object> > Execute(string commandText)
{
using (var connection = new FbConnection(ConnectionString))
{
connection.Open();
var fbTransactionOptions =
new FbTransactionOptions
{
TransactionBehavior = FbTransactionBehavior.Read | FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion | FbTransactionBehavior.NoWait
};
using (var transaction = connection.BeginTransaction(fbTransactionOptions))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = commandText;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var values = new object[reader.FieldCount];
reader.GetValues(values);
var result = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
result.Add(reader.GetName(i), values[i]);
}
yield return(result);
}
}
}
}
}
}
#pragma warning restore 649
public override void CreateDatabase(string constr)
{
var csb = new FbConnectionStringBuilder(constr)
{
Pooling = false
};
FbConnection.CreateDatabase(csb.ConnectionString, 16384, false, true);
using (var con = new FbConnection(csb.ConnectionString))
using (var cmd = con.CreateCommand())
{
con.Open();
#region bug drug block
//cmd.CommandText = @"CREATE TABLE crdb (tid INTEGER, name CHAR(120));";
//cmd.ExecuteScalar();
//cmd.CommandText = @"DROP TABLE crdb;";
//cmd.ExecuteScalar();
#endregion
#region init actions: register udf functions
cmd.CommandText =
@"
DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';" ;
cmd.ExecuteScalar();
#endregion
}
}
public IEnumerable <ColumInfo> GetColums(FbConnection con)
{
using (var command = con.CreateCommand())
{
command.CommandText =
$"select rf.rdb$field_name Name, f.rdb$field_type Type, f.rdb$field_sub_type SubType , f.rdb$character_length CharSize, rf.rdb$field_source FieldSource, rf.rdb$null_flag NullFlag, f.rdb$field_precision FieldPrecision, f.rdb$field_scale FieldScale " +
"from rdb$relation_fields rf " +
"join rdb$relations r on rf.rdb$relation_name = r.rdb$relation_name " +
"and r.rdb$view_blr is not null " +
"and r.rdb$relation_type = 1 and r.rdb$system_flag = 0 " +
"join rdb$fields f on f.rdb$field_name = rf.rdb$field_source " +
$"where rf.rdb$relation_name = '{this.ViewName}' " +
"order by rf.rdb$field_position; ";
var reader = command.ExecuteReader();
while (reader.Read())
{
var size = (reader["CharSize"] == DBNull.Value) ? null : (short?)reader["CharSize"];
var subType = (reader["SubType"] == DBNull.Value) ? null : (short?)reader["SubType"];
var nullFlag = reader["NullFlag"] == DBNull.Value;
var precision = (reader["FieldPrecision"] == DBNull.Value) ? null : (short?)reader["FieldPrecision"];
var scale = (reader["FieldScale"] == DBNull.Value) ? null : (short?)reader["FieldScale"];
var type = new FieldType((short)reader["Type"], subType, size, precision, scale);
yield return(new ColumInfo(((string)reader["Name"]).TrimEnd(), type, null, ((string)reader["FieldSource"]).TrimEnd(), nullFlag));
}
}
}
public IEnumerable <string> GetTableNames(string connectionString)
{
var result = new List <string>();
using (var connection = new FbConnection(connectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = @"
select rdb$relation_name as Name
from rdb$relations
where rdb$view_blr is null and (rdb$system_flag is null or rdb$system_flag = 0)
order by Name asc;" ;
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(((string)reader["Name"]).Trim());
}
}
}
}
return(result);
}
public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
{
using (FbConnection conn = new FbConnection(this.connectionString))
{
conn.Open();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO SESSIONS(SESSIONID, APPLICATIONNAME, CREATED, EXPIRES, " +
"LOCKDATE, LOCKID, TIMEOUT, LOCKED, SESSIONITEMS, FLAGS) " +
"VALUES (@SESSIONID, @APPLICATIONNAME, @CREATED, @EXPIRES, @LOCKDATE, @LOCKID, " +
"@TIMEOUT, @LOCKED, @SESSIONITEMS, @FLAGS)";
cmd.Parameters.Add("@SESSIONID", FbDbType.VarChar, 80).Value = id;
cmd.Parameters.Add("@APPLICATIONNAME", FbDbType.VarChar, 100).Value = ApplicationName;
cmd.Parameters.Add("@CREATED", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@EXPIRES", FbDbType.TimeStamp).Value = DateTime.Now.AddMinutes((Double)timeout);
cmd.Parameters.Add("@LOCKDATE", FbDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("@LOCKID", FbDbType.Integer).Value = 0;
cmd.Parameters.Add("@TIMEOUT", FbDbType.Integer).Value = timeout;
cmd.Parameters.Add("@LOCKED", FbDbType.SmallInt).Value = false;
cmd.Parameters.Add("@SESSIONITEMS", FbDbType.Text, 0).Value = "";
cmd.Parameters.Add("@FLAGS", FbDbType.Integer).Value = 1;
cmd.ExecuteNonQuery();
}
}
}
protected FbCommand BuildCommand(FbConnection connection, object[] restrictions)
{
DataView collections = FbMetaDataCollections.GetSchema().DefaultView;
collections.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (collections.Count == 0)
{
throw new NotSupportedException("Unsupported collection name.");
}
if (restrictions != null &&
restrictions.Length > (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("The number of specified restrictions is not valid.");
}
DataView restriction = FbRestrictions.GetSchema().DefaultView;
restriction.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (restriction.Count != (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("Incorrect restriction definitions.");
}
StringBuilder builder = this.GetCommandText(restrictions);
FbCommand schema = connection.CreateCommand();
schema.CommandText = builder.ToString();
if (connection.InnerConnection.HasActiveTransaction)
{
schema.Transaction = connection.InnerConnection.ActiveTransaction;
}
if (restrictions != null && restrictions.Length > 0)
{
// Add parameters
int index = 0;
for (int i = 0; i < restrictions.Length; i++)
{
string rname = restriction[i]["RestrictionDefault"].ToString().ToLower(CultureInfo.CurrentCulture);
if (restrictions[i] != null &&
!rname.EndsWith("_catalog") &&
!rname.EndsWith("_schema") &&
rname != "table_type")
{
string pname = String.Format(CultureInfo.CurrentCulture, "@p{0}", index++);
FbParameter p = schema.Parameters.Add(pname, restrictions[i].ToString());
p.FbDbType = FbDbType.VarChar;
p.Size = 255;
}
}
}
return schema;
}
