ROW_NUMBER() OLEDB

[Anwind]

A "rowid table" is any table in an schema.
sqlite automatically creates a column called rowid when you create a table.
The rowid starts from 1 and increases by one when a row is added.
It literally represents the ID of the row.

ROW_NUMBER() OVER (ORDER BY ID) ===> There is no OLED.

SELECT A.*, (SELECT COUNT(*) FROM tblNames WHERE A.ID>=ID) AS RowNum FROM tblNames AS A ORDER BY A.ID; ===>

this way it doesn't Becuase My tables don't have A.ID (numbers).

 

[cjard]

OLEDB is a data access technology, SQLite is a database management system (and OLED is a computer display panel technology) - you're comparing chalk with cheese. When you said OLEDB, did you mean Access?

What do you need it for?

 

[cjard]

What do you need it for? What will you do with this number once you have it?

 

[cjard]

What do you "need the index (int type) of the row" for? What will you do with this number when you have it?

 

[cjard]

Get your front end (that implements the algorithm) to generate the number then (which can be as simple as using LINQ Select(e,n => ...) overload to number every row, or even a for(;;) loop). If you want the number to be stable, order the resultset by something stable. If there is nothing stable, add a number column to the database table and populate it

 

[cjard]

Show us the code that runs your query right now, at the moment. I want to see what you're downloading it into and what you're doing with it after you download it. Expecting an answer that contains code e.g. some arrays of objects, maybe a DataTable, a loop, calls to ExecuteReader, a dataadapter, or some more advanced technology like dapper etc

 

[Skydiver]

Algorithm based on row index(int type)

You are in serious trouble if the algorithm is based on the row index and you are not ordering the rows. A relational database is allowed to return rows in any order at any given query if you don't specify a sort order. So that means what you get back as the first row on a query today, may not be the same first row tomorrow.

 

[Skydiver]

I need ROW_NUMBER() OVER (ORDER BY ID) in OLEDB

As previously mentioned it's not OLEDB that doesn't have ROW_NUMBER() function. OLEDB is just a a database interface. It's the database engine/driver that you are talking to which doesn't support ROW_NUMBER() function.

 

[Skydiver]

You can't easily because it depends on the SQL dialect supported by the database driver being hosted by OLEDB. You can do it as a C# post processing step to add another field that effectively has that value.

 

[cjard]

Show us the code that runs your query right now, at the moment. I want to see what you're downloading it into and what you're doing with it after you download it. Expecting an answer that contains code e.g. some arrays of objects, maybe a DataTable, a loop, calls to ExecuteReader, a dataadapter, or some more advanced technology like dapper etc

​

 

[Skydiver]

But there's no way for that to work since you said your data has no ID column as you stated in post #1.

 

[Skydiver]

Here is an untested DbDataReader() that wraps another DbDataReader. The wrapper adds a new last column named "RowNumber" that has the row number. (If there is already a column named "RowNumber" the last column is called "RowNumberX" where X is a GUID.

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;

namespace WeilandYutani.Database.Utilities
{
    public class RowNumberDataReader : DbDataReader
    {
        public RowNumberDataReader(DbDataReader inner)
        {
            Inner = inner;
            RowNumberOrdinal = Inner.FieldCount;

            if (GetColumnNames().Any(n => n == RowNumberColumnName))
            {
                var id = Guid.NewGuid().ToString("N");
                RowNumberColumnName = $"RowNumber{id}";
            }

            IEnumerable<string> GetColumnNames()
            {
                for (int i = 0; i < Inner.FieldCount; i++)
                    yield return GetName(i);
            }
        }

        ulong _rowNumber = 0;

        public DbDataReader Inner { get; private set; }
        public string RowNumberColumnName { get; private set; } = "RowNumber";
        public int RowNumberOrdinal { get; private set; }
        public override object this[int ordinal] => ordinal == RowNumberOrdinal ? _rowNumber : Inner[ordinal];
        public override object this[string name] => name == RowNumberColumnName ? _rowNumber : Inner[name];
        public override int Depth => Inner.Depth;
        public override int FieldCount => Inner.FieldCount + 1;
        public override bool HasRows => Inner.HasRows;
        public override bool IsClosed => Inner.IsClosed;
        public override int RecordsAffected => Inner.RecordsAffected;

        public override bool GetBoolean(int ordinal) => GetInnerOrThrow(ordinal, Inner.GetBoolean);
        public override byte GetByte(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetByte, (byte)_rowNumber);
        public override char GetChar(int ordinal) => GetInnerOrThrow((int)ordinal, Inner.GetChar);
        public override DateTime GetDateTime(int ordinal) => GetInnerOrThrow(ordinal, Inner.GetDateTime);
        public override decimal GetDecimal(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetDecimal, _rowNumber);
        public override double GetDouble(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetDouble, _rowNumber);
        public override float GetFloat(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetFloat, _rowNumber);
        public override Guid GetGuid(int ordinal) => GetInnerOrThrow(ordinal, Inner.GetGuid);
        public override short GetInt16(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetInt16, (short)_rowNumber);
        public override int GetInt32(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetInt32, (int)_rowNumber);
        public override long GetInt64(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetInt64, (long)_rowNumber);
        public override string GetName(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetName, RowNumberColumnName);
        public override int GetOrdinal(string name) => name == RowNumberColumnName ? RowNumberOrdinal : Inner.GetOrdinal(name);
        public override string GetString(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetString, _rowNumber.ToString());
        public override object GetValue(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetValue, _rowNumber);
        public override bool IsDBNull(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.IsDBNull, false);
        public override IEnumerator GetEnumerator() => new DbEnumerator(this);
        public override bool NextResult() => IncrementRowNumberAndDo(Inner.NextResult);
        public override bool Read() => IncrementRowNumberAndDo(Inner.Read);

        [return: DynamicallyAccessedMembers(DynamicallyAccessedMemberTypes.PublicFields | DynamicallyAccessedMemberTypes.PublicProperties)]
        public override Type GetFieldType(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetFieldType, _rowNumber.GetType());
        public override string GetDataTypeName(int ordinal) => GetInnerOrRowNumber(ordinal, Inner.GetDataTypeName, _rowNumber.GetType().Name);

        public override long GetBytes(int ordinal, long dataOffset, byte[]? buffer, int bufferOffset, int length)
            => GetInnerOrThrow(ordinal, () => Inner.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length));

        public override long GetChars(int ordinal, long dataOffset, char[]? buffer, int bufferOffset, int length)
            => GetInnerOrThrow(ordinal, () => Inner.GetChars(ordinal, dataOffset, buffer, bufferOffset, length));

        [MethodImpl(MethodImplOptions.AggressiveInlining)]
        T GetInnerOrThrow<T>(int ordinal, Func<int, T> func)
            => GetInnerOrThrow<T>(ordinal, () => func(ordinal));

        [MethodImpl(MethodImplOptions.AggressiveInlining)]
        T GetInnerOrThrow<T>(int ordinal, Func<T> func)
            => ordinal < RowNumberOrdinal ? func() : throw new InvalidOperationException();

        [MethodImpl(MethodImplOptions.AggressiveInlining)]
        T GetInnerOrRowNumber<T>(int ordinal, Func<int, T> func, T rowNumberValue)
            => ordinal < RowNumberOrdinal ? func(ordinal) : rowNumberValue;

        [MethodImpl(MethodImplOptions.AggressiveInlining)]
        T IncrementRowNumberAndDo<T>(Func<T> func)
        {
            _rowNumber++;
            return func();
        }

        public override int GetValues(object[] values)
        {
            values[RowNumberOrdinal] = _rowNumber;
            return Inner.GetValues(values) + 1;
        }
    }
}

 

[Skydiver]

The code above is in lambda.
also ERROR CS8370 : error cs8370 feature 'nullable reference types' is not available in c# 7.3 8.0
I have a hard time using this code. version doesn't match.

so I need to change the above code from lambda code to IF statement form.

Just like with your issue with your SQL row number, you are looking at the wrong thing and trying to solve the wrong problem. Lambdas have been part of C# since C# 3.0. The error above clearly stated that the problem is with nullable reference types. Why would you try to switch from using lambdas to address an issue that has nothing to do with lambdas?

 

[Skydiver]

Nullable reference types - C# reference

Learn about C# nullable reference types and how to use them

learn.microsoft.com

 

[cjard]

string sQuery = "SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as RN FROM People";
public DbDataReader ExecutDataReader(sQery)
{
    System.Data.Oledb.OledbCommand.ExecuteReader(Default);
}
DbDataReader reader = ExecutDataReader();
while (reader.Read())
{
    var p = new Person();
    p.Name = reader.GetString(0);
    p.CountNum = reader.GetInteger(1); //the RN rownumber
}

I took the liberty of guessing at what you do inside the loop. I didn't fix any typos

string sQuery = "SELECT * FROM People";
public DbDataReader ExecutDataReader(sQery)
{
    System.Data.Oledb.OledbCommand.ExecuteReader(Default);
}
DbDataReader reader = ExecutDataReader();
int i = 1;
while (reader.Read())
{
    var p = new Person();
    p.Name = reader.GetString(0);
    p.CountNum = i++; //replaces the RN rownumber
}