c# - C# Sqlite 参数工作/不工作在不同的页面
问题描述
第一次发帖。我正在为我的一个小项目尝试 sqlite，我遇到了一些非常奇怪的事情。问题似乎出在参数上，但我不明白。也许这里有人可以解释为什么它在某个地方有效，但在另一个地方无效。
就是这样：在这段代码中，一切都运行良好：
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();
}
在这里，我试图隔离问题。当我删除参数Id和Where子句时，一切都会按原样更新，但是当我尝试使用参数时，它永远找不到要更新的行：
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();
}
我已经检查过，并且 Id 存在于数据库中。它应该找到一个结果。如果有人知道为什么并且可以向我解释，那会让我很开心！
更新
我仍然不知道为什么它在第一个示例而不是另一个示例中有效，但这里有一个对我有用的解决方案。
sql 参数有一个 DbType 属性，它会自动设置为适合该值，在我的例子中它设置为 Guid。在 Sqlite 中，uniqueidentifier 类型不存在，它像字符串一样处理。所以这就是我所做的：
...
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)
};
...
解决方案
你可以尝试改变这个：
UPDATE Monsters
SET Name = @Name
WHERE Monsters.Id = @Id
至
UPDATE Monsters SET Name = @Name
WHERE Monsters.Id like @Id
在 C# 中：
new SQLiteParameter("@Id", "%" + monster.Id + "%");
现在，您必须在 switch/case 语句上方声明列表：
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)
};
在您的情况下插入：
parameters.Add(new SQLiteParameter("@Id", monster.Id ));
在你的情况下更新：
parameters.Add(new SQLiteParameter("@Id", "%" + monster.Id + "%"));
您的最终代码：
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();
}
更新：
sqllite 没有 GUId 支持。将 Guid 转换为字符串或将 id 的数据类型更改为字符串可能会对您有所帮助。
看这里：
推荐阅读
- laravel - 使用 Laravel Excel 插入到集合中的表名不正确
- javascript - 使用 Symfony 和 Webpack 找不到 tarteaucitronjs
- xamarin.forms - 未在 Visual Studio 中列出的配置文件
- html - CSS：获取按钮和对齐
- excel - 如何从日期过滤器中删除时间
- python - 使用 Pandas 进行 Pivot 和 Groupby
- typescript - 我可以在 Typescript 中扩展接口的一部分吗？
- email - URL 查询字符串值在通过电子邮件发送后损坏
- karate - 有没有办法在使用空手道对 Ghatling 执行负载测试时将 API 响应存储到文件
- mongodb - 如何将默认值设置为填充的 3D 数组并仍然定义其中的对象？