This repository was archived by the owner on Oct 13, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 10
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
Copy link
Copy link
Open
Description
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
Labels
No labels