internal static SqliteCommand GetSqliteCommand <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction) { SqliteCommand command = connection.CreateCommand(); command.Transaction = transaction; var operationType = tableInfo.BulkConfig.OperationType; switch (operationType) { case OperationType.Insert: command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert); break; case OperationType.InsertOrUpdate: command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate); break; case OperationType.InsertOrUpdateDelete: throw new NotSupportedException("Sqlite supports only UPSERT(analog for MERGE WHEN MATCHED) but does not have functionality to do: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'" + "What can be done is to read all Data, find rows that are not in input List, then with those do the BulkDelete."); case OperationType.Update: command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo); break; case OperationType.Delete: command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo); break; } type = tableInfo.HasAbstractList ? entities[0].GetType() : type; var entityType = context.Model.FindEntityType(type); var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a); var properties = type.GetProperties(); foreach (var property in properties) { if (entityPropertiesDict.ContainsKey(property.Name)) { var propertyEntityType = entityPropertiesDict[property.Name]; string columnName = propertyEntityType.GetColumnName(); var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; /*var sqliteType = SqliteType.Text; // "String" || "Decimal" || "DateTime" * if (propertyType.Name == "Int16" || propertyType.Name == "Int32" || propertyType.Name == "Int64") * sqliteType = SqliteType.Integer; * if (propertyType.Name == "Float" || propertyType.Name == "Double") * sqliteType = SqliteType.Real; * if (propertyType.Name == "Guid" ) * sqliteType = SqliteType.Blob; */ var parameter = new SqliteParameter($"@{columnName}", propertyType); // ,sqliteType // ,null command.Parameters.Add(parameter); } } var shadowProperties = tableInfo.ShadowProperties; foreach (var shadowProperty in shadowProperties) { var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string)); command.Parameters.Add(parameter); } command.Prepare(); // Not Required (check if same efficiency when removed) return(command); }
internal static SqliteCommand GetSqliteCommand <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction) { SqliteCommand command = connection.CreateCommand(); command.Transaction = transaction; var operationType = tableInfo.BulkConfig.OperationType; switch (operationType) { case OperationType.Insert: command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert); break; case OperationType.InsertOrUpdate: command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate); break; case OperationType.InsertOrUpdateDelete: throw new NotSupportedException("'BulkInsertOrUpdateDelete' not supported for Sqlite. Sqlite has only UPSERT statement (analog for MERGE WHEN MATCHED) but no functionality for: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'." + " Another way to achieve this is to BulkRead existing data from DB, split list into sublists and call separately Bulk methods for Insert, Update, Delete."); case OperationType.Update: command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo); break; case OperationType.Delete: command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo); break; } type = tableInfo.HasAbstractList ? entities[0].GetType() : type; var entityType = context.Model.FindEntityType(type); var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a); var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic); foreach (var property in properties) { if (entityPropertiesDict.ContainsKey(property.Name)) { var propertyEntityType = entityPropertiesDict[property.Name]; string columnName = propertyEntityType.GetColumnName(tableInfo.ObjectIdentifier); var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; //SqliteType(CpropertyType.Name): Text(String, Decimal, DateTime); Integer(Int16, Int32, Int64) Real(Float, Double) Blob(Guid) var parameter = new SqliteParameter($"@{property.Name}", propertyType); // ,sqliteType // ,null //() command.Parameters.Add(parameter); } } var shadowProperties = tableInfo.ShadowProperties; foreach (var shadowProperty in shadowProperties) { var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string)); command.Parameters.Add(parameter); } command.Prepare(); // Not Required but called for efficiency (prepared should be little faster) return(command); }