示例#1
0
        public async Task UpdateBusRoutes(Objects vehicle)
        {
            var sql = $@"
UPDATE busdata b 
SET b.rout_ = {vehicle.LastRouteId} 
WHERE b.obj_id_ = {vehicle.ObjId} 
    AND b.proj_id_ = {vehicle.ProjectId} 
    AND b.time_ BETWEEN CURRENT_DATE AND CURRENT_DATE + 1 
    AND b.rout_ != {vehicle.LastRouteId}
";

            using (var connection = new FbConnection(_dataConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new FbCommand(sql, connection, transaction))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            transaction.Commit();
                        }
                    }
                }
            }
        }
        public async Task SimpleSelectTest()
        {
            var csb = BuildConnectionStringBuilder(ServerType, Compression, WireCrypt);

            csb.Enlist = true;

            using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
            {
                await using (var c = new FbConnection(csb.ToString()))
                {
                    c.Open();

                    await using (var command = new FbCommand("select * from TEST where (0=1)", c))
                    {
                        await using (var r = await command.ExecuteReaderAsync())
                        {
                            while (await r.ReadAsync())
                            {
                            }
                        }
                    }
                }

                scope.Complete();
            }
        }
示例#3
0
        public async Task GetOrdinalTest()
        {
            await using (var transaction = await Connection.BeginTransactionAsync())
            {
                await using (var command = new FbCommand("select first 1 0 as fOo, 0 as \"BaR\", 0 as BAR from TEST", Connection, transaction))
                {
                    await using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var foo = reader.GetOrdinal("foo");
                            var FOO = reader.GetOrdinal("FOO");
                            var fOo = reader.GetOrdinal("fOo");
                            Assert.AreEqual(0, foo);
                            Assert.AreEqual(0, FOO);
                            Assert.AreEqual(0, fOo);

                            var bar = reader.GetOrdinal("bar");
                            var BaR = reader.GetOrdinal("BaR");
                            Assert.AreEqual(1, bar);
                            Assert.AreEqual(1, BaR);

                            var BAR = reader.GetOrdinal("BAR");
                            Assert.AreEqual(2, BAR);
                        }
                    }
                }
                await transaction.RollbackAsync();
            }
        }
示例#4
0
        public async Task <List <TrolleybusTodayStatus> > GetTrolleybusTodayStatus()
        {
            var result = new List <TrolleybusTodayStatus>();
            var sql    = "select * from TROLL_V3009";

            using (var connection = new FbConnection(_dataConnectionString))
            {
                connection.Open();
                using (var command = new FbCommand(sql, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                var item = new TrolleybusTodayStatus()
                                {
                                    Name      = Convert.ToString(reader["NAME"]),
                                    RouteName = Convert.ToString(reader["ROUTE_NAME"]),
                                    Place     = Convert.ToString(reader["PLACE"]),
                                    CoordTime = reader["COORD_TIME"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["COORD_TIME"])
                                };

                                result.Add(item);
                            }
                        }
                    }
                }
            }

            return(result);
        }
示例#5
0
        public async Task TimeStampArrayTest()
        {
            var id_value      = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
            var insert_values = new DateTime[] { DateTime.Now.AddSeconds(10), DateTime.Now.AddSeconds(20), DateTime.Now.AddSeconds(30), DateTime.Now.AddSeconds(40) };

            await using (var transaction = await Connection.BeginTransactionAsync())
            {
                await using (var insert = new FbCommand("INSERT INTO TEST (int_field, tsarray_field) values(@int_field, @array_field)", Connection, transaction))
                {
                    insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
                    insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
                    await insert.ExecuteNonQueryAsync();
                }
                await transaction.CommitAsync();
            }

            await using (var select = new FbCommand($"SELECT tsarray_field FROM TEST WHERE int_field = {id_value}", Connection))
            {
                await using (var reader = await select.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        if (!await reader.IsDBNullAsync(0))
                        {
                            var select_values = new DateTime[insert_values.Length];
                            Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
                            insert_values = insert_values.Select(x => new DateTime(x.Ticks / 1000 * 1000)).ToArray();
                            CollectionAssert.AreEqual(insert_values, select_values);
                        }
                    }
                }
            }
        }
示例#6
0
        public async Task ShortArrayTest()
        {
            var id_value      = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);
            var insert_values = new short[] { 50, 60, 70, 80 };

            await using (var transaction = await Connection.BeginTransactionAsync())
            {
                await using (var insert = new FbCommand("INSERT INTO TEST (int_field, sarray_field) values(@int_field, @array_field)", Connection, transaction))
                {
                    insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
                    insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
                    await insert.ExecuteNonQueryAsync();
                }

                await transaction.CommitAsync();
            }

            await using (var select = new FbCommand($"SELECT sarray_field FROM TEST WHERE int_field = {id_value}", Connection))
            {
                await using (var reader = await select.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        if (!await reader.IsDBNullAsync(0))
                        {
                            var select_values = new short[insert_values.Length];
                            Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
                            CollectionAssert.AreEqual(insert_values, select_values);
                        }
                    }
                }
            }
        }
示例#7
0
        public async Task VarCharArrayTest()
        {
            var id_value      = GetId();
            var insert_values = new string[] { "abc", "abcdef", "abcdefghi", "abcdefghijkl" };

            await using (Transaction = await Connection.BeginTransactionAsync())
            {
                await using (var insert = new FbCommand("INSERT INTO TEST (int_field, varray_field) values(@int_field, @array_field)", Connection, Transaction))
                {
                    insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
                    insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
                    await insert.ExecuteNonQueryAsync();
                }
                await Transaction.CommitAsync();
            }

            await using (var select = new FbCommand($"SELECT varray_field FROM TEST WHERE int_field = {id_value}", Connection))
            {
                await using (var reader = await select.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        if (!await reader.IsDBNullAsync(0))
                        {
                            var select_values = new string[insert_values.Length];
                            Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
                            CollectionAssert.AreEqual(insert_values, select_values);
                        }
                    }
                }
            }
        }
示例#8
0
        public async Task InsertNullGuidTest()
        {
            var id = RandomNumberGenerator.GetInt32(int.MinValue, int.MaxValue);

            await using (var insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection))
            {
                insert.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
                insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value   = DBNull.Value;
                await insert.ExecuteNonQueryAsync();
            }

            await using (var select = new FbCommand("SELECT * FROM GUID_TEST WHERE INT_FIELD = @IntField", Connection))
            {
                select.Parameters.Add("@IntField", FbDbType.Integer).Value = id;
                await using (var r = await select.ExecuteReaderAsync())
                {
                    if (await r.ReadAsync())
                    {
                        if (!await r.IsDBNullAsync(1))
                        {
                            Assert.Fail();
                        }
                    }
                }
            }
        }
示例#9
0
        public async Task NumericArrayTest()
        {
            var id_value      = GetId();
            var insert_values = new decimal[] { 210.10M, 220.20M, 230.30M, 240.40M };

            await using (Transaction = await Connection.BeginTransactionAsync())
            {
                await using (var insert = new FbCommand("INSERT INTO TEST (int_field, narray_field) values(@int_field, @array_field)", Connection, Transaction))
                {
                    insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
                    insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
                    await insert.ExecuteNonQueryAsync();
                }
                await Transaction.CommitAsync();
            }

            await using (var select = new FbCommand($"SELECT narray_field FROM TEST WHERE int_field = {id_value}", Connection))
            {
                await using (var reader = await select.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        if (!await reader.IsDBNullAsync(0))
                        {
                            var select_values = new decimal[insert_values.Length];
                            Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
                            CollectionAssert.AreEqual(insert_values, select_values);
                        }
                    }
                }
            }
        }
示例#10
0
        public async Task TimeArrayTest()
        {
            var id_value      = GetId();
            var insert_values = new TimeSpan[] { new TimeSpan(3, 9, 10), new TimeSpan(4, 11, 12), new TimeSpan(6, 13, 14), new TimeSpan(8, 15, 16) };

            await using (Transaction = await Connection.BeginTransactionAsync())
            {
                await using (var insert = new FbCommand("INSERT INTO TEST (int_field, tarray_field) values(@int_field, @array_field)", Connection, Transaction))
                {
                    insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
                    insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
                    await insert.ExecuteNonQueryAsync();
                }
                await Transaction.CommitAsync();
            }

            await using (var select = new FbCommand($"SELECT tarray_field FROM TEST WHERE int_field = {id_value}", Connection))
            {
                await using (var reader = await select.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        if (!await reader.IsDBNullAsync(0))
                        {
                            var select_values = new TimeSpan[insert_values.Length];
                            Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
                            CollectionAssert.AreEqual(insert_values, select_values);
                        }
                    }
                }
            }
        }
示例#11
0
        public async Task ReadBinaryTest()
        {
            await using (var transaction = await Connection.BeginTransactionAsync())
            {
                var bytes  = new byte[1024];
                var random = new Random();
                for (var i = 0; i < bytes.Length; i++)
                {
                    bytes[i] = (byte)random.Next(byte.MinValue, byte.MaxValue);
                }
                var binaryString = $"x'{BitConverter.ToString(bytes).Replace("-", string.Empty)}'";

                await using (var command = new FbCommand($"select {binaryString} from TEST", Connection, transaction))
                {
                    await using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            var value = (byte[])reader[0];
                            Assert.AreEqual(bytes, value);
                        }
                    }
                }
                await transaction.RollbackAsync();
            }
        }
示例#12
0
        public static async Task <DbResult> Subject(FbConnection fbConnection, int tenantId, string code)
        {
            string sql =
                $"SELECT \"ID\" FROM \"Faecher\" " +
                "WHERE " +
                "  \"Mandant\" = @TenantId and " +
                "  \"Kuerzel\" = @Code";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
            Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);

            var sqlReader = await fbCommand.ExecuteReaderAsync();

            var id = -1;
            var numberOfRecords = 0;

            while (sqlReader.Read())
            {
                // do things
                id = (int)sqlReader["ID"];
                numberOfRecords++;
            }

            if (numberOfRecords == 1)
            {
                return(new DbResult(true, id));
            }

            return(new DbResult(false, -1));;
        }
示例#13
0
        public static async Task <DbResult> StudentSchoolClassAttendances(FbConnection fbConnection, int tenantId, int classTermId, int studentId)
        {
            string sql =
                "SELECT \"ID\" FROM \"SchuelerZeitraeume\" " +
                "WHERE " +
                "  \"Mandant\" = @TenantId and " +
                "  \"KlassenZeitraumID\" = @ClassTermId and " +
                "  \"Schueler\" = @StudentId";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
            Helper.SetParamValue(fbCommand, "@ClassTermId", FbDbType.BigInt, classTermId);
            Helper.SetParamValue(fbCommand, "@StudentId", FbDbType.BigInt, studentId);

            var sqlReader = await fbCommand.ExecuteReaderAsync();

            var id = -1;
            var numberOfRecords = 0;

            while (sqlReader.Read())
            {
                // do things
                id = (int)sqlReader["ID"];
                numberOfRecords++;
            }

            if (numberOfRecords == 1)
            {
                return(new DbResult(true, id));
            }

            return(new DbResult(false, id));;
        }
示例#14
0
        public static async Task <DbResult> SchoolTerm(FbConnection fbConnection, string validFrom, string validTo)
        {
            string sql =
                "SELECT \"ID\" FROM \"Zeitraeume\" " +
                "WHERE " +
                "  \"Von\" = @validFrom and " +
                "  \"Bis\" = @validTo";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            Helper.SetParamValue(fbCommand, "@validFrom", FbDbType.Date, validFrom);
            Helper.SetParamValue(fbCommand, "@validTo", FbDbType.Date, validTo);

            var sqlReader = await fbCommand.ExecuteReaderAsync();

            var id = -1;
            var numberOfRecords = 0;

            while (sqlReader.Read())
            {
                // do things
                id = (int)sqlReader["ID"];
                numberOfRecords++;
            }

            if (numberOfRecords == 1)
            {
                return(new DbResult(true, id));
            }

            return(new DbResult(false, id));;
        }
示例#15
0
        public async Task <List <EdsmSystem> > GetSystemAsEdsm(string systemname)
        {
            List <EdsmSystem> systemResult = new List <EdsmSystem>();

            Status = "Working...";
            using (FbCommand getSystem = con.CreateCommand())
            {
                getSystem.CommandText = "SELECT FIRST 50 name,id,x,y,z FROM eddb_systems WHERE lowercase_name LIKE '%" + systemname.ToLower() + "%'";
                using (DbDataReader r = await getSystem.ExecuteReaderAsync())
                {
                    while (r.Read())
                    {
                        EdsmSystem tmpsys = new EdsmSystem();
                        tmpsys.Coords   = new EdsmCoords();
                        tmpsys.Name     = r.GetString(0);
                        tmpsys.Coords.X = r.GetDouble(r.GetOrdinal("X"));
                        tmpsys.Coords.Y = r.GetDouble(r.GetOrdinal("Y"));
                        tmpsys.Coords.Z = r.GetDouble(r.GetOrdinal("Z"));
                        systemResult.Add(tmpsys);
                        Logger.Debug("GetSystemEDSM added: " + r.GetString(0) + ": " + r.GetString(1) + " X: " + r.GetString(2) + " Y: " + r.GetString(3) + " Z: " + r.GetString(4));
                    }
                }
            }
            Status = "Ready!";
            return(systemResult);
        }
示例#16
0
        private async Task <int> ReadMagellanVersion(FbConnection fbConnection)
        {
            var sql = "select first 1 \"Release\" from \"Version\"";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            using var reader = await fbCommand.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                var version = reader.GetInt32(reader.GetOrdinal("Release"));

                if (version >= 800)
                {
                    return(8);
                }
                else if (version >= 700)
                {
                    return(7);
                }
                else
                {
                    return(6);
                }
            }
            else
            {
                return(6);
            }
        }
示例#17
0
        public static async Task <DbResult> SchoolClassTerm(FbConnection fbConnection, int tenantId, string classTermId)
        {
            Guid guidExtern = GuidFactory.Create(GuidFactory.DnsNamespace, classTermId);

            string sql =
                "SELECT \"ID\" FROM \"KlassenZeitraeume\" " +
                "WHERE " +
                "  \"Mandant\" = @TenantId AND " +
                "  \"GUIDExtern\" = @GUIDExtern";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
            Helper.SetParamValue(fbCommand, "@GUIDExtern", FbDbType.VarChar, guidExtern);

            var sqlReader = await fbCommand.ExecuteReaderAsync();

            var id = -1;
            var numberOfRecords = 0;

            while (sqlReader.Read())
            {
                // do things
                id = (int)sqlReader["ID"];
                numberOfRecords++;
            }

            if (numberOfRecords == 1)
            {
                return(new DbResult(true, id));
            }

            return(new DbResult(false, -1));
        }
 public async Task ExecuteReaderWithBehaviorTest()
 {
     await using (var command = new FbCommand("select * from TEST", Connection))
     {
         await using (var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
         { }
     }
 }
        public async Task ExecuteMultipleReaderTest()
        {
            await using (FbCommand
                         command1 = Connection.CreateCommand(),
                         command2 = Connection.CreateCommand())
            {
                command1.CommandText = "select * from test where int_field = 1";
                command2.CommandText = "select * from test where int_field = 2";

                await using (var r1 = await command1.ExecuteReaderAsync())
                {
                    await using (var r2 = await command2.ExecuteReaderAsync())
                    { }

                    // Try to call ExecuteReader in	command1
                    // it should throw an exception
                    Assert.ThrowsAsync <InvalidOperationException>(() => command1.ExecuteReaderAsync());
                }
            }
        }
示例#20
0
 public async Task RecordAffectedTest()
 {
     await using (var command = new FbCommand("insert into test (int_field) values (100000)", Connection))
     {
         await using (var reader = await command.ExecuteReaderAsync())
         {
             while (await reader.ReadAsync())
             {
             }
             Assert.AreEqual(1, reader.RecordsAffected);
         }
     }
 }
示例#21
0
 public async Task DisposeTest()
 {
     await using (var command = new FbCommand("DATAREADERTEST", Connection))
     {
         command.CommandType = CommandType.StoredProcedure;
         FbCommandBuilder.DeriveParameters(command);
         await using (var reader = await command.ExecuteReaderAsync())
         {
             while (await reader.ReadAsync())
             {
             }
         }
     }
 }
示例#22
0
        public async Task GetCharsLengthTest()
        {
            await using (var command = new FbCommand("select clob_field from TEST where int_field = @int_field", Connection))
            {
                command.Parameters.Add("@int_field", FbDbType.Integer).Value = 50;
                await using (var reader = await command.ExecuteReaderAsync())
                {
                    await reader.ReadAsync();

                    var length = reader.GetChars(0, 0, null, 0, 0);
                    Assert.AreEqual(14, length, "Incorrect clob length");
                }
            }
        }
示例#23
0
 public async Task ValidateDecimalSchema()
 {
     await using (var test = new FbCommand("select decimal_field from test", Connection))
     {
         await using (var r = await test.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
         {
             using (var schema = await r.GetSchemaTableAsync())
             {
                 Assert.AreEqual(schema.Rows[0]["ColumnSize"], 8, "Invalid length");
                 Assert.AreEqual(schema.Rows[0]["NumericPrecision"], 15, "Invalid precision");
                 Assert.AreEqual(schema.Rows[0]["NumericScale"], 2, "Invalid scale");
             }
         }
     }
 }
示例#24
0
        private async Task <int> ExportSubjects(FbConnection fbConnection, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
        {
            string sql = $"select * from \"Faecher\" where \"Mandant\" = @tenantId";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            fbCommand.Parameters.Add("@tenantId", _tenantId);

            using var reader = await fbCommand.ExecuteReaderAsync();

            var ecfRecordCounter = 0;

            if (ecfHeaders != null && ecfHeaders.Length > 0)
            {
                await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
            }
            else
            {
                await ecfTableWriter.WriteHeadersAsync(
                    EcfHeaders.Id,
                    EcfHeaders.Code,
                    EcfHeaders.StatisticalCode,
                    EcfHeaders.InternalCode,
                    EcfHeaders.Name,
                    EcfHeaders.SubjectTypeId,
                    EcfHeaders.SubjectCategoryId,
                    EcfHeaders.SubjectGroupId);
            }

            while (await reader.ReadAsync())
            {
                ecfTableWriter.SetValue(EcfHeaders.Id, reader["ID"]);
                ecfTableWriter.SetValue(EcfHeaders.Code, reader["Kuerzel"]);
                ecfTableWriter.SetValue(EcfHeaders.StatisticalCode, reader["StatistikID"]);
                ecfTableWriter.SetValue(EcfHeaders.InternalCode, reader["Schluessel"]);
                ecfTableWriter.SetValue(EcfHeaders.Name, reader["Bezeichnung"]);
                ecfTableWriter.SetValue(EcfHeaders.SubjectTypeId, reader["Kategorie"]);
                ecfTableWriter.SetValue(EcfHeaders.SubjectCategoryId, reader["Aufgabenbereich"]);
                ecfTableWriter.SetValue(EcfHeaders.SubjectGroupId, reader["Gruppe"]);

                await ecfTableWriter.WriteAsync();

                ecfRecordCounter++;
            }

            return(ecfRecordCounter);
        }
示例#25
0
 public async Task BigIntGetStringTest()
 {
     await using (var transaction = await Connection.BeginTransactionAsync())
     {
         await using (var command = new FbCommand("select * from TEST", Connection, transaction))
         {
             await using (var reader = await command.ExecuteReaderAsync())
             {
                 while (await reader.ReadAsync())
                 {
                     reader.GetString("bigint_field");
                 }
             }
         }
         await transaction.RollbackAsync();
     }
 }
示例#26
0
 public async Task GetSchemaTableWithExpressionFieldTest()
 {
     await using (var transaction = await Connection.BeginTransactionAsync())
     {
         await using (var command = new FbCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction))
         {
             await using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
             {
                 using (var schema = await reader.GetSchemaTableAsync())
                 {
                     var currRows = schema.Select(null, null, DataViewRowState.CurrentRows);
                 }
             }
         }
         await transaction.RollbackAsync();
     }
 }
 public async Task SqlServerLikeTest02()
 {
     await using (var command = new FbCommand("GETVARCHARFIELD", Connection))
     {
         command.CommandType = CommandType.StoredProcedure;
         command.Parameters.Add("@ID", FbDbType.VarChar).Value = 1;
         await using (var r = await command.ExecuteReaderAsync())
         {
             var count = 0;
             while (await r.ReadAsync())
             {
                 count++;
             }
             Assert.AreEqual(1, count);
         }
     }
 }
示例#28
0
 public async Task GetValuesTest()
 {
     await using (var transaction = await Connection.BeginTransactionAsync())
     {
         await using (var command = new FbCommand("select * from TEST", Connection, transaction))
         {
             await using (var reader = await command.ExecuteReaderAsync())
             {
                 while (await reader.ReadAsync())
                 {
                     var values = new object[reader.FieldCount];
                     reader.GetValues(values);
                 }
             }
         }
         await transaction.RollbackAsync();
     }
 }
示例#29
0
        public static async Task <DbResult> ByCodeAndTenant(FbConnection fbConnection, string tableName, int tenantId, string code)
        {
            string codeColumn;

            if (tableName == MagellanTables.GradeValues)
            {
                codeColumn = "Notenkuerzel";
            }
            else
            {
                codeColumn = "Kuerzel";
            }

            string sql =
                $"SELECT \"ID\" FROM \"{tableName}\" " +
                "WHERE " +
                "  \"Mandant\" = @TenantId AND " +
                $"  \"{codeColumn}\" = @Code";

            using var fbTransaction = fbConnection.BeginTransaction();
            using var fbCommand     = new FbCommand(sql, fbConnection, fbTransaction);

            Helper.SetParamValue(fbCommand, "@TenantId", FbDbType.BigInt, tenantId);
            Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);

            var sqlReader = await fbCommand.ExecuteReaderAsync();

            var id = -1;
            var numberOfRecords = 0;

            while (sqlReader.Read())
            {
                // do things
                id = (int)sqlReader["ID"];
                numberOfRecords++;
            }

            if (numberOfRecords == 1)
            {
                return(new DbResult(true, id));
            }

            return(new DbResult(false, -1));;
        }
示例#30
0
        public async Task <object> GetTableAsync(string tableName, string[] blacklistedFields = null)
        {
            if (blacklistedFields == null)
            {
                blacklistedFields = new string[0];
            }

            List <List <KeyValuePair <string, string> > > content = null;

            FbCommand cmd = new FbCommand($"SELECT * FROM `{tableName}`", connection);

            var reader = await cmd.ExecuteReaderAsync();

            if (reader.HasRows)
            {
                content = new List <List <KeyValuePair <string, string> > >();

                while (await reader.ReadAsync())
                {
                    var colSchema = reader.GetColumnSchema();

                    List <KeyValuePair <string, string> > obj = new List <KeyValuePair <string, string> >();

                    for (int col = 0; col < colSchema.Count; col++)
                    {
                        if (!blacklistedFields.Contains(colSchema[col].ColumnName.ToLowerInvariant()))
                        {
                            obj.Add(
                                new KeyValuePair <string, string>(
                                    colSchema[col].ColumnName,
                                    Convert.ToString(reader[col])
                                    )
                                );
                        }
                    }

                    content.Add(obj);
                }
            }

            await reader.CloseAsync();

            return(content);
        }