public void Insert_With_Defaults()
{
var cols = new string[] { "intCol", "textCol", "boolCol", "floatCol" };
var valueExprs = new string[] { "1", "'text'", "0", "0.0" };
var builder = new SqlInsertCommand()
{
TableName = "something"
};
VerifyCommandSyntex(builder.ToString() + ";");
}
public void Insert_With_Values()
{
var values = new { intCol = 1, textCol = "text", boolCol = true, floatCol = 0.1 };
IDictionary <string, object> valueDict = values.AsDictionary();
var builder = new SqlInsertCommand()
{
TableName = "something",
Values = valueDict
};
VerifyCommandSyntex(builder.ToString() + ";");
}
public void Tables_Test()
{
using (var datastore = new SQLiteDatastore())
{
var createTable = new CreateTable()
{
TableName = "MyTable",
Columns = new[]
{
new ColumnInfo("col1", SqliteDataType.INTEGER)
{
AutoIncrement = true, IsPK = true
},
new ColumnInfo("col2", SqliteDataType.REAL),
new ColumnInfo("col3", SqliteDataType.TEXT),
new ColumnInfo("col4", SqliteDataType.BOOLEAN),
new ColumnInfo("IgnoreMe"),
},
};
datastore.Execute(createTable.ToString());
datastore.Execute("CREATE VIEW MyView AS SELECT * FROM MyTable;");
// inserting will trigger the creation of Sqlite_squince table
var insert = new SqlInsertCommand()
{
TableName = createTable.TableName
};
datastore.Equals(insert.ToString());
var schemaInfoProvider = new SqliteDatastoreSchemaInfoProvider(datastore, new[] { "IgnoreMe" });
var tables = schemaInfoProvider.Tables.ToArray();
tables.Should().HaveCount(2);
var myTableInfo = tables.First();
myTableInfo.TableName.Should().Be("MyTable");
myTableInfo.PrimaryKeyField.Should().NotBeNull();
myTableInfo.PrimaryKeyField.IsPK.Should().BeTrue();
myTableInfo.Fields.Should().HaveSameCount(createTable.Columns.Where(x => x.Name != "IgnoreMe"));
var myViewInfo = tables[1];
myViewInfo.TableName.Should().Be("MyView");
}
}
public void BuildInsert(IDbCommand command,
object data,
string tableName,
IFieldInfoCollection fields,
OnConflictOption option = OnConflictOption.Default,
object keyValue = null,
bool persistKeyvalue = true)
{
if (command is null)
{
throw new ArgumentNullException(nameof(command));
}
if (data is null)
{
throw new ArgumentNullException(nameof(data));
}
if (fields is null)
{
throw new ArgumentNullException(nameof(fields));
}
if (tableName is null)
{
throw new ArgumentNullException(nameof(tableName));
}
var columnNames = new List <string>();
var valueExpressions = new List <string>();
var pkField = fields.PrimaryKeyField;
if (pkField != null &&
(pkField.PersistanceFlags & PersistanceFlags.OnInsert) == PersistanceFlags.OnInsert &&
keyValue == null)
{
keyValue = pkField.GetFieldValueOrDefault(data);
}
if (persistKeyvalue && keyValue != null)
{
if (pkField is null)
{
throw new InvalidOperationException($"keyValue provided but [{data.GetType().Name}] has no property with a primary key attribute");
}
var param = MakeParameter(command, pkField, keyValue);
command.Parameters.Add(param);
columnNames.Add(pkField.Name);
valueExpressions.Add(param.ParameterName);
}
foreach (var field in fields)
{
if ((field.PersistanceFlags & PersistanceFlags.OnInsert) == PersistanceFlags.OnInsert &&
field.IsKeyField == false)
{
object value = field.GetFieldValueOrDefault(data);
if (value == null)
{
continue;
}
var param = MakeParameter(command, field, value);
command.Parameters.Add(param);
columnNames.Add(field.Name);
valueExpressions.Add(param.ParameterName);
}
}
var builder = new SqlInsertCommand
{
TableName = tableName,
ConflictOption = option,
ColumnNames = columnNames,
ValueExpressions = valueExpressions
};
var keyFieldName = pkField?.Name ?? "RowID";
command.CommandText = builder.ToString() + $"; SELECT {keyFieldName} FROM { tableName} WHERE RowID = last_insert_rowid();";
}
