Back to list
Views:   0
Replies:  0
Archived
,
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
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas