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