First time posting. I am trying sqlite for a little project of mine and I have encountered something really weird. The problem seem to be with the parameter but I don't understand it. Maybe someone here could explain me why it work in some place but not in another one.
Here it is: in this code everything is working perfectly:
public void SaveObject(PlayerCharacter playerCharacter)
{
SQLiteConnection sqliteConnection = new SQLiteConnection(ConnectionString.Connection);
sqliteConnection.Open();
String query = String.Empty;
switch (playerCharacter.InternalState)
{
case InternalStates.New:
query = "INSERT INTO PlayerCharacters(Id, Name, ArmorClass, InitiativeBonus) VALUES (@Id, @Name, @ArmorClass, @InitiativeBonus)";
break;
case InternalStates.Modified:
query = @" UPDATE PlayerCharacters
SET Name = @Name,
ArmorClass = @ArmorClass,
InitiativeBonus = @InitiativeBonus
WHERE Id = @Id";
break;
case InternalStates.Deleted:
//To maybe implement in the future
break;
}
List<SQLiteParameter> parameters = new List<SQLiteParameter>()
{
new SQLiteParameter("@Id", playerCharacter.Id),
new SQLiteParameter("@Name", playerCharacter.Name),
new SQLiteParameter("@ArmorClass", playerCharacter.ArmorClass),
new SQLiteParameter("@InitiativeBonus", playerCharacter.InitiativeBonus)
};
SQLiteCommand command = new SQLiteCommand(query, sqliteConnection);
command.Parameters.AddRange(parameters.ToArray());
command.ExecuteNonQuery();
playerCharacter.SetInternalState(InternalStates.UnModified, true);
sqliteConnection.Close();
}
Here I tried to isolate the problem. When I remove the parameter Id and the Where clause, everything is updated like it should be, but when I try with the parameter, it never find the row to update:
public void SaveObject(Monster monster)
{
SQLiteConnection sqliteConnection = new SQLiteConnection(ConnectionString.Connection);
sqliteConnection.Open();
String query = String.Empty;
switch (monster.InternalState)
{
case InternalStates.New:
query = @" INSERT INTO Monsters(Id,
Name,
Size,
Type,
Subtype,
Alignment,
ArmorClass,
HitPoints,
HitDice,
Speed,
DamageVulnerabilities,
DamageResistances,
DamageImmunities,
ConditionImmunities,
Senses,
Languages,
ChallengeRating)
VALUES (@Id,
@Name,
@Size,
@Type,
@Subtype,
@Alignment,
@ArmorClass,
@HitPoints,
@HitDice,
@Speed,
@DamageVulnerabilities,
@DamageResistances,
@DamageImmunities,
@ConditionImmunities,
@Senses,
@Languages,
@ChallengeRating)";
break;
case InternalStates.Modified:
query = @" UPDATE Monsters
SET Name = @Name
WHERE Monsters.Id = @Id";
//Size = @Size,
// Type = @Type,
// Subtype = @Subtype,
// Alignment = @Alignment,
// ArmorClass = @ArmorClass,
// HitPoints = @HitPoints,
// HitDice = @HitDice,
// Speed = @Speed,
// DamageVulnerabilities = @DamageVulnerabilities,
// DamageResistances = @DamageResistances,
// DamageImmunities = @DamageImmunities,
// ConditionImmunities = @ConditionImmunities,
// Senses = @Senses,
// Languages = @Languages,
// ChallengeRating = @ChallengeRating
break;
case InternalStates.Deleted:
//To maybe implement in the future
break;
}
List<SQLiteParameter> parameters = new List<SQLiteParameter>()
{
new SQLiteParameter("@Id", monster.Id ),
new SQLiteParameter("@Name", monster.Name)
//new SQLiteParameter("@Size", monster.Size),
//new SQLiteParameter("@Type", monster.Type),
//new SQLiteParameter("@Subtype", monster.Subtype),
//new SQLiteParameter("@Alignment", monster.Alignment),
//new SQLiteParameter("@ArmorClass", monster.ArmorClass),
//new SQLiteParameter("@HitPoints", monster.HitPoints),
//new SQLiteParameter("@HitDice", monster.HitDice),
//new SQLiteParameter("@Speed", monster.Speed),
//new SQLiteParameter("@DamageVulnerabilities", monster.DamageVulnerabilities),
//new SQLiteParameter("@DamageResistances", monster.DamageResistances),
//new SQLiteParameter("@DamageImmunities", monster.DamageImmunities),
//new SQLiteParameter("@ConditionImmunities", monster.ConditionImmunities),
//new SQLiteParameter("@Senses", monster.Senses),
//new SQLiteParameter("@Languages", monster.Languages),
//new SQLiteParameter("@ChallengeRating", monster.ChallengeRating)
};
SQLiteCommand command = new SQLiteCommand(query, sqliteConnection);
command.Parameters.AddRange(parameters.ToArray());
int i = command.ExecuteNonQuery();
monster.SetInternalState(InternalStates.UnModified, true);
sqliteConnection.Close();
}
I already checked and the Id exist in the database. It should find a result. If someone know why and can explain it to me it will make my day !
UPDATE
I still don't know why it work in the first sample and not the other one but here a solution that work for me.
The sql parameter have a DbType property and it is automatically set to fit the value, in my case it is set to Guid. In Sqlite uniqueidentifier type doesn't exist and it is process like a string. So here is what i did :
...
List<SQLiteParameter> parameters = new List<SQLiteParameter>()
{
new SQLiteParameter("@Id", monster.Id ) {DbType = DbType.String},
new SQLiteParameter("@Name", monster.Name)
//new SQLiteParameter("@Size", monster.Size),
//new SQLiteParameter("@Type", monster.Type),
//new SQLiteParameter("@Subtype", monster.Subtype),
//new SQLiteParameter("@Alignment", monster.Alignment),
//new SQLiteParameter("@ArmorClass", monster.ArmorClass),
//new SQLiteParameter("@HitPoints", monster.HitPoints),
//new SQLiteParameter("@HitDice", monster.HitDice),
//new SQLiteParameter("@Speed", monster.Speed),
//new SQLiteParameter("@DamageVulnerabilities", monster.DamageVulnerabilities),
//new SQLiteParameter("@DamageResistances", monster.DamageResistances),
//new SQLiteParameter("@DamageImmunities", monster.DamageImmunities),
//new SQLiteParameter("@ConditionImmunities", monster.ConditionImmunities),
//new SQLiteParameter("@Senses", monster.Senses),
//new SQLiteParameter("@Languages", monster.Languages),
//new SQLiteParameter("@ChallengeRating", monster.ChallengeRating)
};
...
Can you try changing this:
UPDATE Monsters
SET Name = @Name
WHERE Monsters.Id = @Id
To
UPDATE Monsters SET Name = @Name
WHERE Monsters.Id like @Id
And in C#:
new SQLiteParameter("@Id", "%" + monster.Id + "%");
Now, you have to declare the list above the switch/case statement:
List<SQLiteParameter> parameters = new List<SQLiteParameter>()
{
// remove the @id parameter here
new SQLiteParameter("@Name", monster.Name)
//new SQLiteParameter("@Size", monster.Size),
//new SQLiteParameter("@Type", monster.Type),
//new SQLiteParameter("@Subtype", monster.Subtype),
//new SQLiteParameter("@Alignment", monster.Alignment),
//new SQLiteParameter("@ArmorClass", monster.ArmorClass),
//new SQLiteParameter("@HitPoints", monster.HitPoints),
//new SQLiteParameter("@HitDice", monster.HitDice),
//new SQLiteParameter("@Speed", monster.Speed),
//new SQLiteParameter("@DamageVulnerabilities", monster.DamageVulnerabilities),
//new SQLiteParameter("@DamageResistances", monster.DamageResistances),
//new SQLiteParameter("@DamageImmunities", monster.DamageImmunities),
//new SQLiteParameter("@ConditionImmunities", monster.ConditionImmunities),
//new SQLiteParameter("@Senses", monster.Senses),
//new SQLiteParameter("@Languages", monster.Languages),
//new SQLiteParameter("@ChallengeRating", monster.ChallengeRating)
};
In your case for insert:
parameters.Add(new SQLiteParameter("@Id", monster.Id ));
In your case for Update:
parameters.Add(new SQLiteParameter("@Id", "%" + monster.Id + "%"));
Your final code:
public void SaveObject(Monster monster)
{
SQLiteConnection sqliteConnection = new SQLiteConnection(ConnectionString.Connection);
sqliteConnection.Open();
String query = String.Empty;
List<SQLiteParameter> parameters = new List<SQLiteParameter>()
{
new SQLiteParameter("@Name", monster.Name)
//new SQLiteParameter("@Size", monster.Size),
//new SQLiteParameter("@Type", monster.Type),
//new SQLiteParameter("@Subtype", monster.Subtype),
//new SQLiteParameter("@Alignment", monster.Alignment),
//new SQLiteParameter("@ArmorClass", monster.ArmorClass),
//new SQLiteParameter("@HitPoints", monster.HitPoints),
//new SQLiteParameter("@HitDice", monster.HitDice),
//new SQLiteParameter("@Speed", monster.Speed),
//new SQLiteParameter("@DamageVulnerabilities", monster.DamageVulnerabilities),
//new SQLiteParameter("@DamageResistances", monster.DamageResistances),
//new SQLiteParameter("@DamageImmunities", monster.DamageImmunities),
//new SQLiteParameter("@ConditionImmunities", monster.ConditionImmunities),
//new SQLiteParameter("@Senses", monster.Senses),
//new SQLiteParameter("@Languages", monster.Languages),
//new SQLiteParameter("@ChallengeRating", monster.ChallengeRating)
};
switch (monster.InternalState)
{
case InternalStates.New:
query = @" INSERT INTO Monsters(Id,
Name,
Size,
Type,
Subtype,
Alignment,
ArmorClass,
HitPoints,
HitDice,
Speed,
DamageVulnerabilities,
DamageResistances,
DamageImmunities,
ConditionImmunities,
Senses,
Languages,
ChallengeRating)
VALUES (@Id,
@Name,
@Size,
@Type,
@Subtype,
@Alignment,
@ArmorClass,
@HitPoints,
@HitDice,
@Speed,
@DamageVulnerabilities,
@DamageResistances,
@DamageImmunities,
@ConditionImmunities,
@Senses,
@Languages,
@ChallengeRating)";
parameters.Add(new SQLiteParameter("@Id", monster.Id ));
break;
case InternalStates.Modified:
query = @" UPDATE Monsters
SET Name = @Name
WHERE Monsters.Id like @Id";
//Size = @Size,
// Type = @Type,
// Subtype = @Subtype,
// Alignment = @Alignment,
// ArmorClass = @ArmorClass,
// HitPoints = @HitPoints,
// HitDice = @HitDice,
// Speed = @Speed,
// DamageVulnerabilities = @DamageVulnerabilities,
// DamageResistances = @DamageResistances,
// DamageImmunities = @DamageImmunities,
// ConditionImmunities = @ConditionImmunities,
// Senses = @Senses,
// Languages = @Languages,
// ChallengeRating = @ChallengeRating
parameters.Add(new SQLiteParameter("@Id", "%" + monster.Id + "%"));
break;
case InternalStates.Deleted:
//To maybe implement in the future
break;
}
SQLiteCommand command = new SQLiteCommand(query, sqliteConnection);
command.Parameters.AddRange(parameters.ToArray());
int i = command.ExecuteNonQuery();
monster.SetInternalState(InternalStates.UnModified, true);
sqliteConnection.Close();
}
Update:
There is no GUId support for sqllite. Converting Guid to String or changind datatype of id to string may help you.
See here: