Adding Parameters in SQLite with C#

Adding parameters in SQLite with C#

Try VALUES instead of VALUE.

SQLite with C# - Parameterized Insert of String will be stored as Numeric


using (SQLiteCommand cmd = ctx.DB.Query("INSERT INTO tableName(Text1, Text2) VALUES (@text1, @text2)"))
    {
          cmd.Prepare();
          cmd.Parameters.Add("@text1", DbType.AnsiString).Value = "abc";
          cmd.Parameters.Add("@text2", DbType.AnsiString).Value = "123";
          cmd.ExecuteNonQuery();
    }

Thanks to @DourHighArch for that hint

Inserting multiple parameter values to a sqlite table using c#

When you call the Add method of the DbParameterCollection (the base class behind the SQLiteParameterCollection) passing a DbParameter object, the return value is an Integer, not the DbParameter just added to the collection.

Thus you cannot use the return value as it was a DbParameter and try to set the Value property.

Instead you should use the Add overload that takes the parameter name and type. This returns an DbParameter object and thus you could write:

insertCommand.Parameters.Add("@param1", System.Data.SqlDbType.VarChar).Value = inputText;
if(fasting)
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 1;
}
else
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 0;
}

You are creating an integer type parameter so set its value to an integer not to a string

C# passing a string to an Sqlite parameter


  1. You were creating a new command after you assigned the parameter to the command which resulted in a command without parameters being executed.
  2. The Add on the parameter collection can be rewritten so it is more fluent, see change below.
  3. ExecuteReader should be ExecuteNonQuery

If you structure your code with using blocks it is easier to see the scope of the command instance. See below:

public async Task initialiseClasses()
{
    using (SqliteConnection _db = new SqliteConnection(@"Filename=.\myDb.db"))
    {
        _db.Open();
        string newSQL = "ALTER TABLE DiscordUser ADD Class char";
        using(SqliteCommand command = new SqliteCommand(newSQL, _db))
        {
            command.ExecuteNonQuery();
        }

        string tempClass = setClass(7);  //temporary input
        newSQL = "UPDATE DiscordUser SET Class = @newClass";
        using(SqliteCommand command = new SqliteCommand(newSQL, _db))
        {
            command.Parameters.Add("@newClass", SqliteType.Text).Value = tempClass;
            command.ExecuteNonQuery();
        }
    }
}

How do I pass multiple parameters to a sqlite IN clause and protect against SQL injection?

Direct Answer

To allow deleting of variable number of entries using IN, use this code:

// Example data
var valuesToDelete = new List<string> { "1", "2", "4" };

sqliteCommand.CommandText = @"delete from [test_table] where col1 in(" + string.Join(",", Enumerable.Range(0, valuesToDelete.Count).Select(z => "@para" + z)) + ")";

for (var i = 0; i < valuesToDelete.Count; i++)
{
    sqliteCommand.Parameters.Add(new SqliteParameter("@para" + i, valuesToDelete[i]));
}

sqliteCommand.ExecuteNonQuery();

string.Join is used to setup the right number of parameters in the SQL, and the for loop is designed to populate those parameters. This allows multiple values to be used inside the IN, and is safe from SQL Injection.

Background

The reason why:

sqliteCommand.CommandText = @"delete from [test_table] where col1 in(@para1,@para2)";
sqliteCommand.Parameters.Add(new SqliteParameter("@para1", "1"));
sqliteCommand.Parameters.Add(new SqliteParameter("@para2", "2"));
sqliteCommand.ExecuteNonQuery();

works but:

sqliteCommand.CommandText = @"delete from [test_table] where col1 in(@para1,@para2)";
sqliteCommand.Parameters.Add(new SqliteParameter("@para1", '1'));
sqliteCommand.Parameters.Add(new SqliteParameter("@para2", '2'));
sqliteCommand.ExecuteNonQuery();

doesn't is that with the latter you accidentally passed in the parameters as char instead of string.

The latter can feel correct, since ' works fine in SQL normally. But SQL and C# are different - ' can be used around a string in SQL, but not in C# (C# uses ' for char and " for `string).

As such, when passing string parameters in - be sure to pass them as string (using ") rather than char.

SQLite with C# - UPDATE Command with Parameters

You aren't using the correct syntax for update:

string sql = "update customers set balance = '" + balance + "' where first_name = '" + forename + "'";

Note that this is not a parameterized query, and might be vulnerable to SQL Injection attacks (or just break if one of the values contains a '). It's recommended to use a prepared statement with parameters:

string sql = "update customers set balance = @balance where first_name = @forename";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.Parameters.AddWithValue("@balance", balance);
command.Parameters.AddWithValue("@forename", forename);
command.ExecuteNonQuery();

c# SQLite Insert Into Command with parameter

Sqlite SQL does not work like that. You cannot declare and store a value in a variable, then reuse that variable later. Terms like @groudId are only placeholders for parameters passed to the query. That means that the expression @groudId = ID is NOT an assignment, rather it is a comparison. Since you are not binding the parameter @groudId to anything, the parameter value is null, so the expression is a comparison like null = ID which will result in false which numerically is 0 (zero). The select statement returns 0 and is not used in the INSERT statement.

If the INSERT is working at all, it is probably resulting in something like INSERT INTO VS_Types(Name, Group_id, Color) Values('20', null, 1245679);

At the end of the question, you also say "The existing record is ..." and you only show a single record for VS_Groups, although the insert statement is for the table VS_Types. But you don't show output for the VS_Types table! You are inspecting the wrong table for the inserted data. If you query the table VS_Types, you will likely find many records with Name == '20', GroupID == null and Color == 123456789... exactly as the INSERT statement says.

In summary, you are not using parameters correctly, but you really don't even need a parameter in the code you show, so it is difficult to know how to answer properly. An answer showing proper use of parameters would be wasted, but a replacement SQL may not be want you want in the end either. I suggest researching parameters separately to learn how to use them properly. For now, use this nested statement: 

INSERT INTO VS_Types(Name, Group_id, Color) 
VALUES ('20', (Select ID From VS_Groups Where Name = 'Ato_h'), 1245679);

Regrettably that's not all. The table definition does not show that VS_Groups.Name is unique, so technically there could be multiple rows that match the nested query, so the INSERT statement could still fail. I suggest adding a UNIQUE constraint to the VS_Groups.Name column.

C# Sqlite Parameter Working/Not working in different pages

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:

SQLite Parameter Issue with Guids

Dapper query with SQLite in C# throws error 'Must add values for the following parameters

I was able to reproduce this issue in a test. The test below shows that OP's original code works when using @tableName instead of $tableName inside SQL.

public class DalTableConfig
{
    public int Id { get; set; }
    public string ModelId { get; set; }
    public string TableName { get; set; }
    public string SchemaName { get; set; }
}

public class DapperTests
{
    [Fact]
    public void StackOverflowDapperTest()
    {
        using var conn = new SqliteConnection("Data Source=test.db");

        conn.Execute("drop table if exists tables");
        conn.Execute("create table tables(id int, modelId varchar(255), tableName varchar(255), schemaName varchar(255))");
        conn.Execute("insert into tables select 1, 'modelId1', 'tableName1', 'schemaName1'");

        const string sql = @"
            select 
                id
                ,modelId
                ,tableName
                ,schemaName 
            from 
                tables 
            where 
               tableName = @tableName"; // here's the fix

        const string tableName = "tableName1";

        var actual = conn.QuerySingleOrDefault<DalTableConfig>(sql, new {tableName});

        Assert.NotNull(actual);
        Assert.Equal(tableName, actual.TableName);
    }
}


Related Topics

How to Combine More Than Two Generic Lists in C# Zip

Why Should I Use Ihttpactionresult Instead of Httpresponsemessage

How to Show Animated Gifs on a Windows Form (C#)

How to Do a Subquery in Linq

How to Change Xml Attribute

Returning Only Part of Match from Regular Expression

Under What Circumstances Is an SQLconnection Automatically Enlisted in an Ambient Transactionscope Transaction

Display Image from Database in Asp MVC

Sort Observablecollection<String> Through C#

Bind to Selecteditems from Datagrid or Listbox in Mvvm

Convert a Binary String Representation to a Byte Array

Why Use 'Virtual' for Class Properties in Entity Framework Model Definitions

How to Inject a Dbcontext Instance into an Ihostedservice

Why Does C# Xmldocument.Loadxml(String) Fail When an Xml Header Is Included

Strip the Byte Order Mark from String in C#

Passing a Value from One Form to Another Form

How to Set Up Automapper in ASP.NET Core

Not Ableto Serialize Dictionary with Complex Key Using JSON.Net


Leave a reply



Submit