public void Manual_Delete_Operation_Deletes_Records()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(6) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
dataSet.Delete().AndWhere(q => q.Value == 5).Execute();
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.Column(nameof(Flat_Entity.Value)) }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual(6, queryResult.GetInt32(0));
}
}
}
public void Delete_Returns_Number_Of_Deleted_Records()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(6) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
Assert.AreEqual(2, dataSet.Delete().Execute());
}
}
public void Insert_Custom_Embedded_ColumnName()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>(cfg => cfg.Field(q => q.Flat.Value, cfg => cfg.ColumnName("Custom")).AutoModel())
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Custom] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entity = new Complex_Entity {
Value = 5, Flat = new Flat_Entity {
Value = 6
}
};
dataSet.Insert(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(entity.Value, result.GetInt32(0));
Assert.AreEqual(entity.Flat.Value, result.GetInt32(1));
}
}
}
public void Delete_Can_Delete_From_Non_Default_Table()
{
var tableName = "Delete_Can_Delete_From_Non_Default_Table";
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {tableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(tableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(6) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
// no need to test the results of this operation, if it fails it'll throw an exception
dataSet.Delete().Table(tableName).Execute();
}
}
public void Can_Update_With_Value()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("Hello") }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
dataSet.Update().Set(q => q.Value, "World").Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual("World", result.GetString(1));
}
}
}
public void Select_View_Returns_View()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(1) },
new[] { QueryExpression.Value(2) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var valueSet = dataSet.Select <Flat_Entity_Value_View>().ToList();
Assert.IsTrue(
valueSet.Select(q => q.Value).SequenceEqual(new[] { 1, 2 })
);
}
}
public void Can_Select_Count()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(1) },
new[] { QueryExpression.Value(2) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var id = dataSet.Select(q => dataSet.ServerFunctions.Count()).ToSingle();
Assert.AreEqual(2, id);
}
}
public void Can_Select_IsIn_With_SubQuery()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(1) },
new[] { QueryExpression.Value(2) },
new[] { QueryExpression.Value(3) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var subQuery = dataSet.Select(q => q.Id).AndWhere(q => q.Id == 1 || q.Id == 3);
var valueSet = dataSet.Select(q => dataSet.ServerFunctions.IsIn(q.Id, subQuery)).ToList();
Assert.IsTrue(new[] { true, false, true }.SequenceEqual(valueSet));
}
}
public void Can_Select_HasFlag_With_Expression()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Enum_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Enum_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Requirement] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Requirement") },
new[] { QueryExpression.Value(0x1), QueryExpression.Value(0x1) },
new[] { QueryExpression.Value(0x2), QueryExpression.Value(0x1) },
new[] { QueryExpression.Value(0x1 | 0x4), QueryExpression.Value(0x1) }
));
var dataSet = new SqlDataSet <Enum_Entity>(sqlDataModel, dataProvider);
var valueSet = dataSet.Select(q => dataSet.ServerFunctions.HasFlag(q.Value, q.Requirement)).ToList();
Assert.IsTrue(new[] { true, false, true }.SequenceEqual(valueSet));
}
}
public void Can_Extend_Server_Method_Support()
{
var schema = new DataSchemaBuilder()
.AddSqlMethodCallConverter(new ServerFunctionConverter())
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(1) },
new[] { QueryExpression.Value(2) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var resultSet = dataSet.Select().AndWhere(q => dataSet.ServerFunctions.ValueIsGreaterThanOne(q)).ToList();
Assert.AreEqual(1, resultSet.Count);
Assert.AreEqual(2, resultSet[0].Value);
}
}
public void Can_Select_IsLike()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity <string> >(q => q.DefaultTableName = "TestTable")
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity <string> > >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("This is like") },
new[] { QueryExpression.Value("Not so much") }
));
var dataSet = new SqlDataSet <Flat_Entity <string> >(sqlDataModel, dataProvider);
var valueSet = dataSet.Select(q => dataSet.ServerFunctions.IsLike(q.Value, "%is%")).ToList();
Assert.IsTrue(new[] { true, false }.SequenceEqual(valueSet));
}
}
public void Execute_Returns_Affected_Row_Count()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("Hello") },
new[] { QueryExpression.Value("Hello") }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var result = dataSet.Update().Set(q => q.Value, "World").Execute();
Assert.AreEqual(2, result);
}
}
public void Does_Not_Select_Computed_Field_On_Entity()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Entity_With_Computed_Field>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Entity_With_Computed_Field> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
// leave "C" off the schema, if we try to query for "C" it'll throw an exception and fail the test
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[A] INTEGER,
[B] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("A"), QueryExpression.Column("B") },
new[] { QueryExpression.Value(1), QueryExpression.Value(1) }
));
var dataSet = new SqlDataSet <Entity_With_Computed_Field>(sqlDataModel, dataProvider);
var result = dataSet.Select().ToSingle();
}
}
public void Entity_Delete_Operation_Deletes_Only_Entity_Record()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(5) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var entity = new Flat_Entity {
Id = 1
};
dataSet.Delete(entity).Execute();
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(queryResult.HasRows);
}
}
}
public void Insert_Flat_Entity_Into_NonDefault_Table()
{
var tableName = "MyCustomTable";
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE MyCustomTable
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
dataSet.Insert(new Flat_Entity {
Value = value
}).Table(tableName).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(tableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(value, result.GetInt32(0));
}
}
}
public void Insert_Flat_View_With_Binding()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
var view = new { Custom = 5 };
dataSet.Insert(view, binding => binding.Bind(t => t.Value, s => s.Custom)).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(value, result.GetInt32(0));
}
}
}
public void Can_Insert_Entity_With_Null_Member()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entity = new Complex_Entity
{
Value = 5
};
dataSet.Insert(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(entity.Value, result.GetInt32(0));
Assert.AreEqual(0, result.GetInt32(1));
}
}
}
public void Can_Select_Computed_Field_With_Expression()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Entity_With_Computed_Field>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Entity_With_Computed_Field> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[A] INTEGER,
[B] INTEGER,
[C] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("A"), QueryExpression.Column("B"), QueryExpression.Column("C") },
new[] { QueryExpression.Value(1), QueryExpression.Value(1), QueryExpression.Value(3) } // note C isn't A + B as defined on the entity class itself to prove it's read direct from the db
));
var dataSet = new SqlDataSet <Entity_With_Computed_Field>(sqlDataModel, dataProvider);
var value = dataSet.Select(q => q.C).ToSingle();
Assert.AreEqual(3, value);
}
}
public void Insert_Default_Values()
{
var defaultValue = 4;
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>(cfg => cfg.Field(q => q.Value, cfg => cfg.DefaultValue(() => defaultValue)))
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
dataSet.Insert().Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(defaultValue, result.GetInt32(0));
}
}
}
public void Select_Expression_With_Complex_Join_Returns_Value()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Really_Complex_Entity>()
.AddSqlEntity <Complex_Entity>()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Really_Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Flat_Entity
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Complex_Entity
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Complex_Id] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Flat_Entity"),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(7) },
new[] { QueryExpression.Value(8) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Complex_Entity"),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id") },
new[] { QueryExpression.Value(6), QueryExpression.Value(1) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id"), QueryExpression.Column("Complex_Id") },
new[] { QueryExpression.Value(5), QueryExpression.Value(2), QueryExpression.Value(1) }
));
var dataSet = new SqlDataSet <Really_Complex_Entity>(sqlDataModel, dataProvider);
var valueSet = dataSet.Select(q => q.Complex.Flat.Value).ToList();
Assert.AreEqual(7, valueSet[0]);
}
}
public void Insert_Complex_Entity_With_Relationship_View()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.AddSqlEntity <Complex_Entity>()
.Build();
var flatDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
var complexDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {flatDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Id] INTEGER PRIMARY KEY AUTOINCREMENT
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {complexDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var flatDataSet = new SqlDataSet <Flat_Entity>(flatDataModel, dataProvider);
var complexDataSet = new SqlDataSet <Complex_Entity>(complexDataModel, dataProvider);
// the ID here isn't written to the INSERT query because the ORM see's it as server generated (which it should be)
var flatEntity = new Flat_Entity {
Id = 1, Value = 5
};
var complexEntityView = new { Value = 6, FlatId = flatEntity.Id };
flatDataSet.Insert(flatEntity).Execute();
complexDataSet.Insert(complexEntityView).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(
new[] { QueryExpression.All() },
from: QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName),
joins: new[]
{
QueryExpression.Join(
QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName),
QueryExpression.AreEqual(
QueryExpression.Column("Flat_Id", QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName)),
QueryExpression.Column("Id", QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName))
)
)
})))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(complexEntityView.Value, result.GetInt32(0));
Assert.AreEqual(complexEntityView.FlatId, result.GetInt32(1));
Assert.AreEqual(flatEntity.Value, result.GetInt32(2));
Assert.AreEqual(flatEntity.Id, result.GetInt32(3));
}
}
}
//------------------------------------------------------------------
// Métodos de usuario.
//------------------------------------------------------------------
// Leer DataRow.
public Char OdbcSeekRow(string SeekFichero,
string SeekFields = "*",
string SeekWhere = "1=1",
string SeekOrder = "",
string SeekGroup = "")
{
Char retorno = 'S';
_inicializar();
try
{
// Montar la sentencia SQL.
sqlsql = "Select " + (SeekFields == "" ? " * " : SeekFields);
sqlsql += " From " + SeekFichero;
sqlsql += (SeekWhere == "" ? "1=1" : " Where " + SeekWhere);
sqlsql += (SeekOrder == "" ? "" : " Order By " + SeekOrder);
sqlsql += (SeekGroup == "" ? "" : " Group By " + SeekGroup);
// cmTF.Connection = cnTF; // This way or CreateCommand().
cmTF = cnTF.CreateCommand(); // This way or Set connection parameter.
cmTF.CommandType = CommandType.Text;
cmTF.CommandText = sqlsql;
sqldatatable = new DataTable(SeekFichero);
sqldataadapter = new OdbcDataAdapter(cmTF);
sqldataset = new DataSet();
sqlnoofrows = sqldataadapter.Fill(sqldataset, SeekFichero);
sqldatatable = sqldataset.Tables[SeekFichero];
sqltablereader = SqlDataSet.CreateDataReader();
retorno = (SqlNoOfRows > 0) ? 'S' : 'N';
if (retorno == 'S')
{
// DataRow a devolver.
sqldatarow = sqldatatable.NewRow();
sqldatarow = sqldataset.Tables[SeekFichero].Rows[0];
}
}
catch (OdbcException ex)
{
sqlstatus = -1;
UsrErrorC = ex.HResult;
UsrError = ex.Message;
UsrErrorE = ex.StackTrace;
retorno = 'C';
}
return(retorno);
}
public void Select_Entity_With_Join_Returns_Entity()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Flat_Entity
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Flat_Entity"),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(6) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id") },
new[] { QueryExpression.Value(3), QueryExpression.Value(1) },
new[] { QueryExpression.Value(4), QueryExpression.Value(2) }
));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entitySet = dataSet.Select().ToList();
Assert.IsTrue(
entitySet.Select(q => q.Value).SequenceEqual(new[] { 3, 4 })
);
Assert.IsTrue(
entitySet.Select(q => q.Flat.Id).SequenceEqual(new[] { 1, 2 })
);
Assert.IsTrue(
entitySet.Select(q => q.Flat.Value).SequenceEqual(new[] { 5, 6 })
);
}
}
public void Insert_Complex_Embedded_View_With_Complex_Type_Binding()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var view = new
{
TopValue = 5,
DeepValue = 6
};
dataSet.Insert(view, binding =>
{
binding
.Bind(t => t.Value, s => s.TopValue)
.Bind(t => t.Flat, s => s.DeepValue, value => new Flat_Entity {
Value = value
});
}).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(view.TopValue, result.GetInt32(0));
Assert.AreEqual(view.DeepValue, result.GetInt32(1));
}
}
}
public void Insert_Returns_Number_Of_Inserted_Rows()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
Assert.AreEqual(1, dataSet.Insert(new Flat_Entity {
Value = value
}).Execute());
}
}
public void Can_Select_Bound_Complex_Type_With_Transform()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Has_Complex_Type>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Has_Complex_Type> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Complex_A] INTEGER,
[Complex_B] INTEGER
)" ));
var a = 2;
var b = 4;
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Complex_A"), QueryExpression.Column("Complex_B") },
new[] { QueryExpression.Value(a), QueryExpression.Value(b) }
));
var binding = new TypeBindingBuilder <Has_Complex_Type, Custom_View>()
.Bind(q => q.Sum, q => q.Complex, o => o.A + o.B)
.Bind(q => q.Product, q => q.Complex, o => o.A * o.B)
.BuildBinding();
var dataSet = new SqlDataSet <Has_Complex_Type>(sqlDataModel, dataProvider);
var result = dataSet.Select <Custom_View>(binding).ToSingle();
Assert.AreEqual(a + b, result.Sum);
Assert.AreEqual(a * b, result.Product);
}
}
protected SqlDataOperation(SqlDataSet <TEntity> dataSet, IQueryProvider queryProvider) : base(dataSet, queryProvider)
{
}
public async static Task <Mitarbeiter> LoadAsync(ISqlHandler Sql, string[] fields, int Id)
{
SqlDataSet data = await Sql.SelectWhereAsync(Table, SqlUtility.AppendWithCommas(fields), SqlUtility.Equal(FieldId, Id));
Mitarbeiter mit = new Mitarbeiter();
for (int i = 0; i < data.FieldCount; i++)
{
string f = data.FieldNames[i];
object o = data.Data[0][i];
if (o is DBNull)
{
o = default(object);
}
if (f == FieldId)
{
mit.Id = (int)o;
}
else if (f == FieldKennung)
{
mit.Kennung = (string)o;
}
else if (f == FieldName)
{
mit.Name = (string)o;
}
else if (f == FieldVorname)
{
mit.Vorname = (string)o;
}
else if (f == FieldFähigkeiten)
{
mit.Fähigkeiten = (string)o;
}
else if (f == FieldSchicht)
{
mit.Schicht.Id = (int)o;
}
else if (f == FieldTelefon)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FestnetzPrivat));
}
else if (f == FieldMobil)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.MobilDienst));
}
else if (f == FieldTelefonIntern)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FestnetzIntern));
}
else if (f == FieldFaxIntern)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FaxIntern));
}
else if (f == FieldEMail)
{
mit.Kontakt.EMail = (string)o;
}
}
return(mit);
}
public async static Task <List <Mitarbeiter> > LoadAllAsync(string[] Fields, ISqlHandler Sql)
{
SqlDataSet data = await Sql.SelectAsync(Table, SqlUtility.AppendWithCommas(Fields));
List <Mitarbeiter> mitarbeiter = new List <Mitarbeiter>();
foreach (List <object> singleData in data.Data)
{
Mitarbeiter mit = new Mitarbeiter();
for (int i = 0; i < data.FieldCount; i++)
{
string f = data.FieldNames[i];
object o = singleData[i];
if (o is DBNull)
{
o = default(object);
}
if (f == FieldId)
{
mit.Id = (int)o;
}
else if (f == FieldKennung)
{
mit.Kennung = (string)o;
}
else if (f == FieldName)
{
mit.Name = (string)o;
}
else if (f == FieldVorname)
{
mit.Vorname = (string)o;
}
else if (f == FieldFähigkeiten)
{
mit.Fähigkeiten = (string)o;
}
else if (f == FieldSchicht)
{
mit.Schicht.Id = (int)o;
}
else if (f == FieldTelefon)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FestnetzPrivat));
}
else if (f == FieldMobil)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.MobilDienst));
}
else if (f == FieldTelefonIntern)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FestnetzIntern));
}
else if (f == FieldFaxIntern)
{
mit.Kontakt.Rufnummern.Add(new Rufnummer((string)o, RufnummerTyp.FaxIntern));
}
else if (f == FieldEMail)
{
mit.Kontakt.EMail = (string)o;
}
}
mitarbeiter.Add(mit);
}
return(mitarbeiter);
}
public SqlDeleteOperation(SqlDataSet <TEntity> dataSet, IQueryProvider queryProvider) : base(dataSet, queryProvider)
{
_queryBuilder = new SqlDeleteBuilder <TEntity>(DataSet.DataModel);
}
