ROW_NUMBER() OLEDB

Anwind

Well-known member
Joined
Nov 6, 2021
Messages
48
Programming Experience
1-3
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).
 
Last edited:

cjard

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
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

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
What do you "need the index (int type) of the row" for? What will you do with this number when you have it?
 

cjard

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
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

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
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
 
Last edited:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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.

C#:
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+

cjard

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
C#:
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

C#:
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
}
 
Last edited:

cjard

Well-known member
Joined
Jan 25, 2012
Messages
439
Programming Experience
10+
Use Dapper; it makes life simple:

C#:
  //this downlaods all the people into a list
  List<Person> people = myConnectionToTheDb.Query<Person>("SELECT * FROM People").ToList();

Then you can apply a number to them
C#:
  int i = 1;
  people.ForEach(p => p.CountNum = i++);

If you don't understand lambdas
C#:
  int i = 1;
  foreach(var p in people)
    p.CountNum = i++;

---

Probably worth getting to understand them tho; theyre a big part of C# nowadays and theyre not hard - they're just methods without a name, and a set of parameters and return types that the compiler guesses for you
C#:
//traditional method
string GetName(Person p){
  return p.Name;
}

//lambda
p => p.Name;


//using traditional method:
people.Select(GetName);

//using lambda
people.Select(p => p.Name);

* return type is string because p.Name is a string
* type of parameter p is a Person because all objects in the List<Person> people are of type Person. Lambda parameter type comes from the type of objects in the list because of how Select method is defined
* no return statement; not needed in this single line case as a hard rule in C#: that kind of lambda must resolve to a single value which is returned
* no { } brackets again as a hard rule: single line lambdas do not need blocks to define their scope, so they dont need { }, in the same way that an if that only works on one line of code also needs no brackets
 
Last edited:

Anwind

Well-known member
Joined
Nov 6, 2021
Messages
48
Programming Experience
1-3
This tells me nothing useful. Expand it to clearly say why

C#:
string sQuery = select ROW_NUMBER() OVER (ORDER BY id ASC) as RowNumber, * from Table

public DbDataReader ExecutDataReader(sQery)
{
    System.Data.Oledb.OledbCommand.ExecuteReader(Default);
}
DbDataReader reader = ExecutDataReader();
while (reader.Read())
{
    String.Format("Row Number : {0}", reader["RowNumber"]);
}


select ROW_NUMBER() OVER (ORDER BY id ASC) as RowNumber <==== There Is Column " id "
I need Row Index (int type) ROW_NUMBER() OVER (ORDER BY ID)
ROW_NUMBER() OVER (ORDER BY ID) is not generated in oledb.
reader["RowNumber"] <===== It should be like this.
 
Last edited:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
Let me say this one more time. If your data doesn't have a column named id, even if the database behind your OLEDB supported the ROW_NUMBER() function, there's no way for ORDER by id ASC to work.

Also, there's no way for your line 10 to work because you are trying to access reader["Row Number"], but in your SQL statement, your row number column is named "rownum", due to your use of ROW_NUMBER() OVER (ORDER BY id ASC) rownum.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
6,559
Location
Chesapeake, VA
Programming Experience
10+
Anyway here's how to get your mythical row number:
C#:
var command = new OleDbCommand("SELECT * FROM Table", connection);
DbDataReader reader = command.ExecuteReader();
int rowNum = 1;
while (reader.Read())
{
    String.Format("Row Number : {0}", rowNum);
    rowNum++;
}
 
Top Bottom