I have a SQLite table which I've created and it works fine when inserting data which is non-zero. However, I need to insert some zero default values and the SQLiteParameter seems to be converting the zero values to null
Can someone explain why I'm getting @xxxx3=null instead of @xxxx3=0 and also how to fix it.
This appears to happen for any numeric field (INTEGER/NUMERIC).
I've put together a simplified example that shows the problem
class Program
{
private static List<SQLiteParameter> DefaultSystemParameters()
{
List<SQLiteParameter> sp = new List<SQLiteParameter>()
{
new SQLiteParameter("@xxxx2", 60),
//new SQLiteParameter("@xxxx3", 1), // Works fine
new SQLiteParameter("@xxxx3", 0), // Throws 'System.Data.SQLite.SQLiteException' NOT NULL constraint failed: tblxxxx.xxxx3
};
return sp;
}
static void Main(string[] args)
{
//Add Nuget package - System.Data.SQLite v 1.0.99
string baseDir = AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath + "db\\";
string fileName = "test.db";
string sqlCreateTable = "CREATE TABLE IF NOT EXISTS tblxxxx (" +
"xxxx1 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"xxxx2 INTEGER NOT NULL," +
"xxxx3 INTEGER NOT NULL" +
")";
string sqlInsert = "INSERT INTO tblxxxx (xxxx2, xxxx3) VALUES (@xxxx2, @xxxx3)";
if (!Directory.Exists(baseDir))
Directory.CreateDirectory(baseDir);
DataTable dt = new DataTable();
string connectionString = $"Data Source={baseDir + fileName};Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
//CREATE
using (SQLiteCommand command = new SQLiteCommand(sqlCreateTable, connection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
//INSERT
command.CommandText = sqlInsert;
command.Parameters.AddRange(DefaultSystemParameters().ToArray());
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
}
