Execute multiple sql statements

How to execute multiple SQL statements? I want reset user_id column in accounts table. Thanks.

public void ResetUserId()
{
    try
    {
        string sql = "SET @count= 0; UPDATE accounts SET user_id= @count:= @count + 1;";
        Db.Update(sql);
    }
    catch (Exception)
    {
        throw;
    }
}

EDH4 ☺, Oct 07, 2014

Reply 1

I solved this problem by creating a store procedure. I call the store procedure, shortly after the deletion of data.

Mysql store procedure with name = reset_accounts:

BEGIN
SET @count= 0; UPDATE accounts SET user_id= @count:= @count + 1;
ALTER TABLE accounts AUTO_INCREMENT = 1;
END

Db.cs:

public static void ResetUserId(string sql)
{
    using (DbConnection connection = _dbFactory.CreateConnection())
    {
        connection.ConnectionString = _connectionString;
        using (DbCommand command = _dbFactory.CreateCommand())
        {
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = sql;
 
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

MySqlServerAccountDao.cs:

public void ResetUserId()
{
       //SET @count = 0; UPDATE accounts SET user_id = @count:= @count + 1;";    
    string sql = "reset_accounts;"; 
    Db.ResetUserId(sql);
}

EDH4 ☺, Oct 18, 2014