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:
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:
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:
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.
 
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++;
}
 
Your Answer ) If your data doesn't have a column named [id]

My Answer ) select ROW_NUMBER() OVER (ORDER BY id ASC) as RowNumber <==== There Is Column " id "
That not what you said in your original post. Since you love removing posts and back editing your posts, let me quote what you wrote in your original post:
this way it doesn't Becuase My tables don't have A.ID (numbers).
 
And now that you've stated that you have an ID column, here's an update for post #23:
C#:
var command = new OleDbCommand("SELECT * FROM Table ORDER BY id ASC", connection);
DbDataReader reader = command.ExecuteReader();
int rowNum = 1;
while (reader.Read())
{
    String.Format("Row Number : {0}", rowNum);
    rowNum++;
}
 
Given our OP's other threads, I suspect that they are hoping to have a single query that can be used with either SQLite or with OLEDB+ACE driver. I think they are hoping to eventually get back to their original question to see the memory footprint of SQLite vs. OLEDB+ACE driver by having an apples to apples test harness and where the only thing that changes would be which database to use. What's sad here is that OLEDB+ACE driver is just being used to read a CSV file. Seems like overkill, specially if the only reason for the database is to be able to get a row number.
 
Why should it be like that?

You're trying to get your database to do something that:

a) it is incapable of
b) can be done by the front end, to no overall effective difference

Get the front end to do it

SELECT *, DCount("[ID"] , "[Tabble]" , "[ID] <=" & [ID] ) as rownumber From Table

C# What is the C# syntax??
 
Given our OP's other threads, I suspect that they are hoping to have a single query that can be used with either SQLite or with OLEDB+ACE driver. I think they are hoping to eventually get back to their original question to see the memory footprint of SQLite vs. OLEDB+ACE driver by having an apples to apples test harness and where the only thing that changes would be which database to use. What's sad here is that OLEDB+ACE driver is just being used to read a CSV file. Seems like overkill, specially if the only reason for the database is to be able to get a row number.
SELECT *, DCount("[ID"] , "[Tabble]" , "[ID] <=" & [ID] ) as rownumber From Table

C# What is the C# syntax??
 
Back
Top Bottom