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();"; }