You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
This repository has been archived by the owner on Oct 13, 2020. It is now read-only.
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
The text was updated successfully, but these errors were encountered:
Sign up for freeto subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Description
Parameterized query-syntax with unnamed parameters works with INSERT INTO and SELECT but not with CREATE TABLE.
Prerequisites:
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?
Thx
The text was updated successfully, but these errors were encountered: