Get last cell in a column from an SQL

Question

Quite a specific question. I'm making a program which detects files. When there's a new file with a specific name, it saves it to a server. This works fine. The problem is, one of the variables it saves is a unique number, which is supposed to increase for every file it uploads. So the first file has the unique number 400, and the next file has unique numer 401 and so on.

I uploaded a test file to the server, so there's already a first file with unique number 400. What I want now is that my next file should have the next number. For that to happen, it has to get the the number from the last cell in the first column. I found out that the SQL statement is SELECT TOP 1 * FROM EKG_Temp ORDER BY ekgmaaleid DESC. I've outcommented the loadCMD which is the command containing the SQL statement.

The name of the table is EKG_Temp. The name of the column is ekgmaaleid, the type is a BigInt

I'm interested in saving the last cell in column ekgmaaleid as a variable in my program for later use basically.

    public void SaveFile(string fileName, byte[] BlobValue)
    {
        string fullPath = (folder + "/" + fileName);
        fileExtension = fileName.Substring(fileName.Length - 3);
        if (saveBool == true)
        {
            SqlConnection con = new SqlConnection(@"Data Source=Irrelavant");
            SqlCommand SaveCmdTemporary = new SqlCommand("insert into EKG_Temp(ekgmaaleid,dato,borger_cprnr,raa_data,maaleformat_type) values(@ekgmaaleid,@dato,@borger_cprnr,@raa_data,@maaleformat_type)", con);
            //SqlCommand SaveCmdPermanent = new SqlCommand("insert into EKG_Temp(ekgmaaleid,dato,borger_cprnr,raa_data,maaleformat_type) values(@ekgmaaleid,@dato,@borger_cprnr,@raa_data,@maaleformat_type)", con);
            //SqlCommand LoadCmd = new SqlCommand("SELECT TOP 1 * FROM EKG_Temp ORDER BY ekgmaaleid DESC", con); //Husk at den faktisk skal vælge måleid ud fra EKG_Gemt i virkeligheden
            SqlParameter ekgmaaleidParam = new SqlParameter("@ekgmaaleid", SqlDbType.BigInt);
            SqlParameter datoParam = new SqlParameter("@dato", SqlDbType.DateTime);
            SqlParameter borger_cprnrParam = new SqlParameter("@borger_cprnr", SqlDbType.NVarChar, 50);
            SqlParameter raa_dataParam = new SqlParameter("@raa_data", SqlDbType.VarBinary, -1);
            SqlParameter maaleformat_typeParam = new SqlParameter("@maaleformat_type", SqlDbType.NVarChar, 10);
            SqlParameter loadMaaleIDParam = new SqlParameter("@ekgmaaleid", SqlDbType.BigInt);
            con.Open();
            SaveCmdTemporary.Parameters.Add(ekgmaaleidParam);
            SaveCmdTemporary.Parameters.Add(datoParam);
            SaveCmdTemporary.Parameters.Add(borger_cprnrParam);
            SaveCmdTemporary.Parameters.Add(raa_dataParam);
            SaveCmdTemporary.Parameters.Add(maaleformat_typeParam);
            //LoadCmd.Parameters.Add(loadMaaleIDParam);
            datoParam.Value = dato;
            borger_cprnrParam.Value = cpr;
            raa_dataParam.Value = BlobValue; //Kommer fra metoden FileCreated.
            maaleformat_typeParam.Value = fileExtension;
            try
            {
                SaveCmdTemporary.Connection.Open();
                SaveCmdTemporary.ExecuteNonQuery();
                MessageBox.Show("File saved to database.", "BLOB Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                SaveCmdTemporary.Connection.Close();
                saveBool = false;
            }

        }
    }

Here's a picture of the database and what I wish it to do.

Thanks in advance.

Answer 1

The correct way to do this is to create the table with an identity column; then the database will generate this value for you. The value of the identity value inserted immediately before is available via SCOPE_IDENTITY() or @@IDENTITY (the former is preferred - especially if there are triggers; the latter works on more database versions, but has edge-cases).

On multi-user databases, this also avoids issues of race conditions (where obviously only one connection knows about the most recent inserts). You simply change the insert command to not specify the identity column (the database will supply this value; you don't have to).