I apply SQLite database. Earlier I used autoincrement for id field in each table. But because of the limitation of possible values that can be recorded into the variable containing the id, I've decided to use another shceme. I've created class ConvinientID with only one method that should solve the problem. According to my idea if I delete some row and create new one, the method of ConvinientID may fill in id field by missed values. In an instance, if I delete row with id=2 and there are still 4 rows, the next id should be 2.

My actions:

1. Record the amount of table rows. If it equals 0, id=1.
2. Record the column into datatable and later into array.
3. Sort the array in ascending order.
4. Find the missed value if there's missed one.
5. Return id

Class ConvinientID:

class ConvinientID
{
    SQLiteConnection m_dbConn = new SQLiteConnection();
    SQLiteCommand m_dbCmd = new SQLiteCommand();
    DataTable dTable;

    public Int32 GetID(string TableName)
    {
        Int32 amount = 0;
        m_dbConn = new SQLiteConnection();
        m_dbCmd = new SQLiteCommand();
        try
        {
            InceptDb cl = new InceptDb();
            m_dbConn = new SQLiteConnection("Data Source="   cl.dbFileName   ";Version=3;");
            m_dbConn.Open();
            m_dbCmd.Connection = m_dbConn;
            m_dbCmd.CommandText = "SELECT COUNT(*) FROM "   TableName;
            amount = Convert.ToInt32(m_dbCmd.ExecuteScalar());
        }
        catch (SQLiteException ex)
        {
            MessageBox.Show("Error in class ConvinientID. COUNT(*): "   ex.Message);
        }
        m_dbConn.Close();

        if (amount == 0)
            return 1;

        MessageBox.Show($"{amount}");

        Int32[] IDs = new Int32[amount];
        m_dbConn = new SQLiteConnection();
        try
        {
            InceptDb cl = new InceptDb();
            m_dbConn = new SQLiteConnection("Data Source="   cl.dbFileName   ";Version=3;");
            m_dbConn.Open();
            dTable = new DataTable();
            String sqlQuery1 = "SELECT id FROM "   TableName;
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery1, m_dbConn);
            adapter.Fill(dTable);
            IDs = dTable.AsEnumerable().Select(r => r.Field<Int32>("id")).ToArray();
        }
        catch (SQLiteException ex)
        {
            MessageBox.Show("Error in class ConvinientID. Select id column: "   ex.Message);
        }
        m_dbConn.Close();

        Array.Sort(IDs);
        for(Int32 i = 1; i <= amount;i   )
        {
            if (IDs[i - 1] != i)
                return i;
        }
        return amount   1;
    }
}

But I got the error described in the header of this question. Specified cast is invalid. The complaint is about this row of code:

IDs = dTable.AsEnumerable().Select(r => r.Field<Int32>("id")).ToArray();

I am able to add only the first row.

CodePudding user response：

I don't think you need to reuse IDs because the max value of SQLite Integer is 9223372036854775807 and i quess you would not get this much records :) And the performanche of finding the next free ID is getting worse the more records there are.

But I would quess that the InvalidCastException coms from "r.Field("id")" maybe there is a record where "id" has a value which is not castable to int.

CodePudding user response：

So I've found the solution already, but this solution is a little bit weird. I've noticed all fields named like "id" have SQLite datatype INTEGER and PRIMARY KEY property. If I change this one to INT, I obtain the working method. It has just turned out the information about the name of data types in SQLite website wasn't true. At least it seems so.