Skip to content
This repository was archived by the owner on Oct 13, 2020. It is now read-only.
This repository was archived by the owner on Oct 13, 2020. It is now read-only.

Parameterized query-syntax with unnamed parameters - Not working with create table #2

@iiinnniii

Description

@iiinnniii

Description
Parameterized query-syntax with unnamed parameters works with INSERT INTO and SELECT but not with CREATE TABLE.

Prerequisites:

  • C# Console application
  • As stated in the comments -> Nuget-package System.Data.Sqlite + update everything after install.
  • You need a NorthwindTest.sl3 database in the debug-folder.

Error
The section that is commented with "test" fails. I guess it is generally not necessary to support that syntax with CREATE TABLE at the table-name, but is this a bug or as intended, that this syntax cannot be used at the table-name?

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;

// Important: Does use nuget packages: System.Data.SQlite (the official one). Must be downloaded + insert using-statements (using System.Data.SQLite;).

// Important: Always use parameterized query, because of SQL-injection-attacks.

namespace ParameterizedQueryUnnamedParameters
{
    class Program
    {
        static void Main(string[] args)
        {
            var qe = new QueryExeceuter();
        }
    }

    class QueryExeceuter
    {
        const string dbConnectionString = @"Data Source=NorthwindTest.sl3;Version=3;"; // NorthwindTest.sl3 is the path when NorthwindTest.sl3 is in the Debug-folder
        SQLiteConnection sqliteCon;
        const string tableName = "MyTable";
        string query;
        SQLiteCommand sqlCommand;

        public QueryExeceuter()
        {
            sqliteCon = new SQLiteConnection(dbConnectionString);

            // Open connection to database
            try
            {
                sqliteCon.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // Create table if does not exist
            if (!TableExists(tableName))
            {
                query = $@"CREATE TABLE {tableName}(Id INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT, Age INTEGER);";
                sqlCommand = new SQLiteCommand(query, sqliteCon);
                sqlCommand.ExecuteNonQuery();
            }

            // Parameterized query-syntax with unnamed parameters
            query = @"insert into MyTable values ((SELECT max(id) FROM MyTable) + 1, ?, ?, ?); ";
            sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "Bob" });
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "Johnson" });
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = 24 });
            sqlCommand.ExecuteNonQuery();

            // test
            query = $@"CREATE TABLE ?(Id INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT, Age INTEGER);";
            sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "MyTableLala" });
            sqlCommand.ExecuteNonQuery();

            // Wait for the user to quit the program.
            Console.WriteLine("Press \'q\' to quit the sample.");
            while (Console.Read() != 'q') ;
        }

        bool TableExists(string tableName)
        {
            string query = $@"SELECT * FROM sqlite_master WHERE type='table' AND tbl_name=?;";
            SQLiteCommand sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = tableName });
            SQLiteDataReader reader = sqlCommand.ExecuteReader();
            return reader.Read(); // I like to use Read() over other options, because other options like the 'ExecuteScalar'-method require a cast.
        }
    }
}

Thx

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions