CODEWITHSUNDEEP
c#mysqlsql
aliazik
aliazik

Reputation: 195

MYSQL / SQL query in database not working

I have an application that works like a web crawler; it gets me a bunch of information about players on a certain game. in this information are things like their Level and their Online Status (online or offline). What I want to do is add all players to the database. But, if they exist it should only update their already existing row.

To do this, I decided to first of all DELETE all the names in the database that matches the name I am going to insert. Their name is stored in @name. I then want to set everyone in the table to onlinestatus=0 (meaning they are offline). And then I want to set onlinestatus to 1, to the players I am inserting. The players I am currently inserting are the only ones online in the game. So if they are not in my list that I insert, they should be offline. That's why I first try to reset their online status.

I then also want to reset the levels to 0, of all the players I am going to insert. And then at the end I insert their things again. But it seems not to be working and I don't know whats the best way to do things.

Any help is appreciated!

What I want to do:

  1. Insert a list of players into the database

  2. If they already exist in the database, do not insert them, but only update their level and onlinestatus

  3. onlinestatus = 1 when I am inserting them. It's a list of players. If they are not in the list, make them offline.

        using (var connection = new MySqlConnection(connString))
    {
        connection.Open();
        using (var command = new MySqlCommand())
        {
            command.Connection = connection;
            command.CommandText = @"DELETE FROM rookstayers WHERE name = @name;
            UPDATE rookstayers SET onlinestatus CASE WHEN name = @name THEN 1 ELSE 0 END;UPDATE rookstayers SET level = 0 WHERE name = @name; INSERT INTO rookstayers (name, sex, vocation, level, achievements, world, lastlogin, accountstatus, onlinestatus) VALUES (@name, @sex, @vocation, @level, @achievements, @world, @lastLogin, @accountStatus, @onlineStatus)";
            command.Prepare();

            foreach (var rooker in Players)
            {
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@name", rooker.Name);
                command.Parameters.AddWithValue("@sex", rooker.Sex);
                command.Parameters.AddWithValue("@vocation", rooker.Vocation);
                command.Parameters.AddWithValue("@level", rooker.Level);
                command.Parameters.AddWithValue("@achievements", rooker.Achievements);
                command.Parameters.AddWithValue("@world", rooker.World);
                command.Parameters.AddWithValue("@lastLogin", rooker.LastLogin);
                command.Parameters.AddWithValue("@accountStatus", rooker.AccountStatus);
                command.Parameters.AddWithValue("@onlineStatus", rooker.OnlineStatus);
                command.ExecuteNonQuery();
            }

            Players.Clear();
        }
        connection.Close();
    }

Upvotes: 0

Views: 482

Answers (5)

Kevin Rodriguez
Kevin Rodriguez

Reputation: 301

            try
            {
                using (var connection = new MySqlConnection(connString))
                {
                    connection.Open();
                    using (var command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = @"SELECT FROM rookstayers where name = @name";
                        MySqlDataReader reader = command.ExecuteReader();

                        foreach (var rooker in Players)
                        {
                            while (reader.Read())
                            {
                                if (reader.HasRows())
                                {
                                    command.CommandText = @"UPDATE rookstayers SET onlinestatus CASE WHEN name = @name THEN 1 ELSE 0 END; UPDATE rookstayers SET level = 0 WHERE name = @name";
                                    command.Parameters.AddWithValue("@name", rooker.Name);
                                    command.Parameters.AddWithValue("@onlineStatus", rooker.OnlineStatus);
                                    command.ExecuteNonQuery();
                                }
                                else
                                {
                                    command.CommandText = @"INSERT INTO rookstayers (name, sex, vocation, level, achievements, world, lastlogin, accountstatus, onlinestatus) VALUES (@name, @sex, @vocation, @level, @achievements, @world, @lastLogin, @accountStatus, @onlineStatus)";
                                    command.Parameters.AddWithValue("@name", rooker.Name);
                                    command.Parameters.AddWithValue("@sex", rooker.Sex);
                                    command.Parameters.AddWithValue("@vocation", rooker.Vocation);
                                    command.Parameters.AddWithValue("@level", rooker.Level);
                                    command.Parameters.AddWithValue("@achievements", rooker.Achievements);
                                    command.Parameters.AddWithValue("@world", rooker.World);
                                    command.Parameters.AddWithValue("@lastLogin", rooker.LastLogin);
                                    command.Parameters.AddWithValue("@accountStatus", rooker.AccountStatus);
                                    command.Parameters.AddWithValue("@onlineStatus", "1");
                                    command.ExecuteNonQuery();
                                } 

                            }
                        }
                    }
                }
            }
            finally
            {
                Players.Clear();
                connection.Close();
            }

Upvotes: 0

user862319
user862319

Reputation:

I think this is a really good use-case for Entity Framework. It is really easy to work here with unless you are dealing with massive tables. All you need to do is install the following packages via NuGet and set up your app.config to connect to the appropriate database instance.

Nuget packages:

EntityFramework
MySql.Data
MySql.Data.Entity

Example connection string in app.config:

<connectionStrings>
    <add name="RookstayersDbContext" 
        connectionString="server=localhost;userid=root;password=mypass;database=mydb" 
        providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Player class:

public class Player
{
    public int Id { get; set; }
    public string Sex { get; set; }
    public string Vocation { get; set; }
    public int Level { get; set; }
    public string Achievements { get; set; }
    public string World { get; set; }
    public DateTime? LastLogin { get; set; }
    public string AccountStatus { get; set; }
    public bool IsOnline { get; set; }
}

RookstayersDbContext:

public class RookstayersDbContext : DbContext
{
    public RookstayersDbContext() : base("RookstayersDbContext") { }
    public DbSet<Player> Players { get; set; }
}

Your ETL code:

//Replace this with wherever you pull the info from (as a Player entity)
var currentPlayersData = new List<Player>();

using(var ctx = new RookstayersDbContext())
{
    //Iterate through your new data collection
    foreach(var player in currentPlayersData)
    {
        var dbPlayer = ctx.Players.FirstOrDefault(x => x.Name == player.Name);

        //Insert new player if not existing
        if(dbPlayer == null)
        {
            player.IsOnline = true; //ensure flagged as online
            ctx.Players.Add(player); //add new player
            continue; //proceed through to next current player
        }

        //If player already exists, flag as online
        dbPlayer.IsOnline = true;
    }

    //Now iterate through your database collection
    foreach(var player in ctx.Players)
    {
        //If no player information found in current players data, flag as offline
        if (!currentPlayersData.Any(x => x.Name == player.Name))
            player.IsOnline = false; //flag player as offline
    }

    //Save any changes made in this context to database
    ctx.SaveChanges();
}

Upvotes: 0

Harry
Harry

Reputation: 3195

You need something like this, because your name is unique. Please note below is just rough guide. You can use MySQL IF Function.

IF(name == @name) THEN
-- update

ElSE

-insert query

END IF;

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The first UPDATE statement is missing an =:

UPDATE rookstayers SET onlinestatus CASE WHEN name = 
  -- Need an equal sign here ------^

Additionally, I suspect the Parameters.Clear() call may be throwing things off. It actually removes all the parameters from the collection, when you all really need to do is change the values. Try this instead:

command.Prepare();
//Guessing a column names/types here. Use actual column types from your DB
command.Parameters.Add("@name", MySqlDbType.VarString, 20);
command.Parameters.Add("@sex", MySqlDbType.VarString, 1);
command.Parameters.Add("@vocation", MySqlDbType.VarString, 20);
command.Parameters.Add("@level", MySqlDbType.Int32);
command.Parameters.Add("@achievements", MySqlDbType.VarString, 255);
command.Parameters.Add("@world", MySqlDbType.VarString, 20);
command.Parameters.Add("@lastLogin", MySqlDbType.DateTime);
command.Parameters.Add("@accountStatus", MySqlDbType.Int32);

foreach (var rooker in Players)
{
     command.Parameters["@name"].Value = rooker.Name;
     command.Parameters["@sex"].Value = rooker.Sex;
     command.Parameters["@vocation"].Value = rooker.Vocation;
     command.Parameters["@level"].Value = rooker.Level;
     command.Parameters["@achievements"].Value = rooker.Achievements;
     command.Parameters["@world"].Value = rooker.World;
     command.Parameters["@lastLogin"].Value = rooker.LastLogin;
     command.Parameters["@accountStatus"].Value = rooker.AccountStatus;
     command.Parameters["@onlineStatus"].Value = rooker.OnlineStatus;
     command.ExecuteNonQuery();
}

Once you've made those changes, post what your actual error is, or what you're seeing for the expected vs observed results.

Upvotes: 2

Mel_T
Mel_T

Reputation: 451

After deleting all players that are in your list, you can't update them.

So you probable need to do this for everyone left in the table:

UPDATE rookstayers SET onlinestatus = 0;
UPDATE rookstayers SET level = 0;

Upvotes: 2

Related Questions