Getting the ID of the row c# Mysql

Question

Im was changing my Mysql databaseclient to the one of mysql.data.dll from the System.data.sqlclient.

But before with sqlclient could I do this:

 public long insertQuery()
    {
        long lastInsertedId = 0L;
        try
        {
            lastInsertedId = (long)command.ExecuteScalar();
        }
        catch (Exception exception)
        {
            Exeption.WriteMySQLError(exception.ToString());
        }
        return lastInsertedId;
    }
dbClient.setQuery("INSERT INTO items (base_item, user_id) VALUES (" + BaseItem + ", " + UserId + ")");
                    Id = (uint)dbClient.insertQuery();

and then did i got the itemId that i just inserted but with the same code and with ExecuteScalar() from MySql.Data.MySqlClient.MySqlCommand Do I get a null exeption. so how does this work with MySqlCommand?

Command comes from this:

        private MySql.Data.MySqlClient.MySqlCommand Command;

Fixed it thanks to Steve heres the fixed code.

   public long insertQuery()
        {
            Command.CommandText += "; SELECT LAST_INSERT_ID()";
            long lastInsertedId = 0L;
            try
            {
                lastInsertedId = Convert.ToInt32(Command.ExecuteScalar().ToString());
            }
            catch (Exception exception)
            {
                Console.WriteLine("Nou er is een error in insertquery! :" + exception.ToString());
            }   
            return lastInsertedId;
        }

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

MySql returns the last inserted id using the function LAST_INSERT_ID

So your command text should be changed to something like this

 dbClient.setQuery(@"INSERT INTO items (base_item, user_id) VALUES (....);
                     SELECT LAST_INSERT_ID()");
 Id = (uint)dbClient.insertQuery();

However your code suffers of a serious problem called SQL Injection. Passing a string formed concatenating pieces of other strings exposes your code to a security vulnerability very easy to use.

Read about SQL Injection and how to use a Parameterized query
SQL Injection short story

edited May 23 '17 at 10:33

Community

1
1

answered Aug 06 '14 at 20:15

Steve

213,761
22
232
286

Ow the baseitem/userid are static so its impossible to change it :p mm and thanks gona try it :) – Guus Aug 06 '14 at 20:20

Getting the ID of the row c# Mysql

1 Answers1