C# read MySql int field return wrong number

Question

I'm writing a web page using C# MVC5 & MySQL, when I request a number from int field, MySQL always return a wrong number, I can't find some solution.

MySQL like:

| Id | Type | Level | Parent_id |ChildrenCount |
| 1  | T    | 1     | 1         |3             |
| 2  | SC   | 2     | 1         |2             |
| 3  | SH   | 3     | 2         |1             |
| 4  | TK   | 4     | 3         |0             |
...

I can use sql show them normal.

But C# can not.

Below is C# code.

public Item GetItem(int id, int level, int pos)
{
    Item item = new Item { Id = -1 };

    using (MySqlConnection con = new DBConnect().MySql())
    {
        string sql = $"SELECT * FROM item WHERE Parent_id={id} AND Level={level} LIMIT {pos}, 1;";
        using (MySqlCommand cmd = new MySqlCommand(sql, con))
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                item.Id = reader.GetInt32(0);
                item.Type = reader.GetString(1);
                item.Level = reader.GetInt32(2);
                item.ChildrenCount = reader.GetInt32(3);
            };
        }
    }
    return item;
}

public void GetItemList(int id, int level, int count, ref List<Item> pli, int initPos=0)
{
    int pos = 0;
    pos += initPos;
    Item item = GetItem(id, level, pos);

    if (item.Id == -1)
    {
        return;
    }

    pli.Add(item);

    // Here item.Level is wrong.

    if (pli.Count() < count)
    {
        if (item.ChildrenCount > 0)
        {
            int ChildPos = 0;
            int newLevel = item.Level + 1;
            GetItemList(item.Id, newLevel, count, ref pli, ChildPos);
        }
        else
        {
            int newPos = pos + 1;
            GetItemList(id, level, count, ref pli, newPos);
        }
    }
}

call by

public List<Item> GetInitItem(int id)
{
    List<Item> resItemList = new List<Item> { };
    GetItemList(id, level: 1, count: 50, pli: ref resItemList, initPos: 0);

    return resItemList;
}

other information all right, only Level column is wrong, all return 1.

That's result in page:

ID:6133   Type:T   Level:1   Count:744   
ID:6154   Type:SC   Level:1   Count:67

Which MySQL version do you have? As quering a hierarchical table can be difficult to handle in MySQL versions under 8. — Raymond Nijland, May 27 '19 at 10:54
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, May 27 '19 at 10:54
I think the problem is query `string sql = $"SELECT * FROM item WHERE Parent_id={id} AND Level={level} LIMIT {pos}, 1;";` . Use `SELECT Id, Count, Level, ChildrenCount FROM item WHERE Parent_id={id} AND Level={level} LIMIT {pos}, 1;";` — Raka, May 27 '19 at 11:01
@raka It can't help me, I can use this sql right in shell. thanks. — jeremyjone, May 28 '19 at 01:45
@RaymondNijland I will read the links you provide, I will try again today — jeremyjone, May 28 '19 at 01:46
@jeremyjone you are passing 1 as level in `GetItemList(id, level: 1, count: 50, pli: ref resItemList, initPos: 0);` and your query is having check for Level as `Level = {level}` . Put debugger and see if your code actually passing through `if (item.ChildrenCount > 0) { int ChildPos = 0; int newLevel = item.Level + 1; GetItemList(item.Id, newLevel, count, ref pli, ChildPos); }` . All other places it will return records having level=1 — Raka, May 28 '19 at 04:16
@Raka I tried it, unfortunately, result can be return right, only `Level` column changed 1, just as I query `item 4`, return `{Id: 4, Type: TK, Level: 1, Parent_id: 3, ChildrenCount: 0}`. I wonder at the question. So far I've discarded this column, judge by `Type`. — jeremyjone, May 28 '19 at 07:08

C# read MySql int field return wrong number

0 Answers0