public QueryContext <T> GetQueryContext(T item)
{
ISqlBuilder <T> builder = new InsertSqlBuilder <T>(item);
QueryDirector <T> director = new QueryDirector <T>(builder);
return(director.GetDataContext());
}
/// <summary>
/// Builds the command text to insert a database record for the specified <see cref="IObjectInfo"/>.
/// </summary>
/// <param name="objectInfo">The object information.</param>
/// <returns>
/// The created command text.
/// </returns>
protected virtual string BuildInsertCommandText(IObjectInfo objectInfo)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
var counter = 0;
var insertColumns = new string[objectInfo.TableInfo.InsertColumnCount];
for (int i = 0; i < objectInfo.TableInfo.Columns.Count; i++)
{
var columnInfo = objectInfo.TableInfo.Columns[i];
if (columnInfo.AllowInsert)
{
insertColumns[counter++] = columnInfo.ColumnName;
}
}
var insertSqlQuery = new InsertSqlBuilder(this.SqlCharacters)
.Into(objectInfo)
.Columns(insertColumns)
.Values(new object[objectInfo.TableInfo.InsertColumnCount])
.ToSqlQuery();
return(insertSqlQuery.CommandText);
}
/// <summary>
/// Inserts the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
/// <returns></returns>
/// <exception cref="GoliathDataException"></exception>
public int Insert(TEntity entity)
{
try
{
var insertBuilder = new InsertSqlBuilder();
var execList = insertBuilder.Build(entity, entityMap, session);
var result = execList.Execute(session);
//if it's a trackable entity let's reset it
var trackable = entity as ITrackable;
//ResetTrackableEntity(trackable);
if (trackable != null && !trackable.ChangeTracker.IsTracking)
{
trackable.ChangeTracker.Init();
//TODO: write method to initialize values;
trackable.ChangeTracker.Start();
}
return(result);
}
catch (GoliathDataException)
{
throw;
}
catch (Exception exception)
{
throw new GoliathDataException($"Error while trying to insert mapped entity {entityMap.FullName} of type {entity.GetType()}", exception);
}
}
public void AutoIncrement_Build_insert_entity_with_new_entity_in_many2one_relation()
{
var monkEntMap = SessionHelper.Factory.DbSettings.Map.EntityConfigs.FirstOrDefault(c => string.Equals(c.Name, "Monkey", StringComparison.Ordinal));
Monkey monkey = new Monkey()
{
Age = 12,
CanDoTricks = true,
Family = "Singe",
Location = "cage2344",
Name = "smart monkey",
ReceivedOn = DateTime.Now,
Zoo = new Zoo()
{
AcceptNewAnimals = true, Name = Guid.NewGuid().ToString(), City = "sfdfsdfsfe"
},
};
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
var execList = builder.Build(monkey, monkEntMap, session);
Assert.AreEqual(3, execList.Statements.Count);
Assert.IsTrue(execList.Statements[2].Processed);
Assert.IsTrue(monkey.Id > 0);
Assert.IsTrue(monkey.Zoo.Id > 0);
}
public static async Task <InsertedId> InsertAsync <T>(this DbConnection db, T data, CancellationToken?cancel = null, Action <IInsertableOptions <T> > cfg = null) where T : class
{
data.MustNotBeNull();
cancel = cancel ?? CancellationToken.None;
if (data.IsAnonymousType())
{
cfg.MustNotBeNull("You need to specify table name at least");
}
var info = db.GetPocoInfo <T>();
var options = info.CreateInsertOptions <T>();
cfg?.Invoke(options);
var provider = db.Provider();
var builder = new InsertSqlBuilder(data, provider, options);
if (options.IdentityColumn.IsNullOrEmpty())
{
var count = await db.ExecuteAsync(builder.GetCommandConfiguration(), cancel.Value);
if (count != 0)
{
return(InsertedId.OkWithNoResult);
}
return(InsertedId.NotOkWithNoResult);
}
return(await db.GetValueAsync <InsertedId>(builder.GetCommandConfiguration(), cancel.Value));
}
public void Guid_build_insert_entity_with_many_to_many_create()
{
var session = SessionHelper.Factory.OpenSession();
var builder = new InsertSqlBuilder();
var role = new Role {
Description = "test", Name = "Fake Role 2"
};
var account = new UserAccount()
{
AccountCreatedOn = DateTime.Now,
EmailAddress = "*****@*****.**",
UserName = "******"
};
account.RolesOnUserRole_UserId.Add(role);
var execList = builder.Build(account, session);
Assert.AreEqual(3, execList.Statements.Count);
Assert.IsFalse(account.Id.Equals(Guid.Empty));
Assert.IsFalse(role.Id.Equals(Guid.Empty));
Assert.AreEqual(3, execList.Execute(session));
}
public void Guid_build_insert_entity_with_many_to_one_relationship()
{
var session = SessionHelper.Factory.OpenSession();
var builder = new InsertSqlBuilder();
UserAccount account = new UserAccount()
{
AccountCreatedOn = DateTime.Now,
EmailAddress = "*****@*****.**",
UserName = "******"
};
Task task = new Task()
{
AssignedTo = account,
CreatedOn = DateTime.Now,
Title = "fake task 133",
TaskDescription = "fake fake fake... ldjlkj ljljlk jlj"
};
var execList = builder.Build(task, session);
Assert.AreEqual(2, execList.Statements.Count);
Assert.IsFalse(task.Id.Equals(Guid.Empty));
Assert.IsFalse(account.Id.Equals(Guid.Empty));
Assert.AreEqual(2, execList.Execute(session));
var x = session.SelectAll <Task>().Where(c => c.Id).EqualToValue(task.Id).FetchAll().FirstOrDefault();
Assert.AreEqual(task, x);
}
public int Insert <T>(EntityMap entityMap, T entity)
{
var insert = new InsertSqlBuilder();
var execList = insert.Build(entity, entityMap, this);
return(execList.Execute(this));
}
public InsertSqlBuilder GetInsertSqlBuilder()
{
if (insertBuilder == null)
{
insertBuilder = CreateInsertSqlBuilder();
}
return(insertBuilder);
}
public void InsertIntoSpecifyingType()
{
var sqlBuilder = new InsertSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Into(typeof(Customer))
.ToSqlQuery();
Assert.Equal("INSERT INTO Sales.Customers", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void InsertIntoSpecifyingTableNameWithSqlCharacters()
{
var sqlBuilder = new InsertSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Into("Table")
.ToSqlQuery();
Assert.Equal("INSERT INTO [Table]", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void InsertIntoSpecifyingTypeWithSqlCharacters()
{
var sqlBuilder = new InsertSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Into(typeof(Customer))
.ToSqlQuery();
Assert.Equal("INSERT INTO [Sales].[Customers]", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public static Task <InsertedId> InsertAsync <T>(this DbConnection db, T data, CancellationToken cancel, Action <IInsertableOptions <T> > cfg = null)
{
var info = db.GetPocoInfo <T>();
var options = info.CreateInsertOptions <T>();
cfg?.Invoke(options);
var provider = db.Provider();
var builder = new InsertSqlBuilder(info, data, provider, options);
return(db.GetValueAsync <InsertedId>(builder.GetCommandConfiguration(), cancel));
}
public InsertBuilderTests()
{
_opt = new InsertSqlOptions(Setup.GetTableInfo <Post>());
_data = new Post()
{
Id = Guid.NewGuid(),
Email = Email.CreateRandomTestValue().Value,
SomeId = 23,
};
_sut = new InsertSqlBuilder(_data, new FakeDbProvider(), _opt);
}
protected override string GetInsertSql(Dictionary <string, Field> values)
{
InsertSqlBuilder insertBuilder = Program.Container.GetInsertSqlBuilder();
return(insertBuilder
.InsertTo("Автомобиль")
.SetValue("Номер_Госрегистрации", values["register_number"].Value)
.SetValue("Марка", values["mark"].Value)
.SetValue("Год_Выпуска", values["year"].Value)
.SetValue("Изготовитель", values["creator"].Value)
.GetResult());
}
public void Guid_build_sql_insert()
{
var session = SessionHelper.Factory.OpenSession();
var builder = new InsertSqlBuilder();
Role role = new Role {
Description = "test", Name = "Fake Role 1"
};
var execList = builder.Build(role, session);
Assert.AreEqual(1, execList.Statements.Count);
Assert.IsFalse(execList.Statements[0].Processed);
Assert.AreEqual(1, execList.Execute(session));
Assert.IsFalse(role.Id.Equals(Guid.Empty));
}
public void AutoIncrement_Build_insert_statements_for_simple_entity_without_relation_should_create_one_insert_statement()
{
//insert a zoo
var zooEntMap = SessionHelper.Factory.DbSettings.Map.EntityConfigs.FirstOrDefault(c => string.Equals(c.Name, "Zoo", StringComparison.Ordinal));
Zoo zoo = new Zoo()
{
AcceptNewAnimals = true, Name = Guid.NewGuid().ToString(), City = "kaaow"
};
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
var execList = builder.Build(zoo, zooEntMap, session);
Assert.AreEqual(1, execList.Statements.Count);
Assert.IsTrue(execList.Statements[0].Processed);
Assert.IsTrue(zoo.Id > 0);
}
public void InsertIntoColumnsValuesWithSqlCharacters()
{
var sqlBuilder = new InsertSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Into("Table")
.Columns("Column1", "Column2")
.Values("Foo", 12)
.ToSqlQuery();
Assert.Equal("INSERT INTO [Table] ([Column1],[Column2]) VALUES (@p0,@p1)", sqlQuery.CommandText);
Assert.Equal(2, sqlQuery.Arguments.Count);
Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
Assert.Equal("Foo", sqlQuery.Arguments[0].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
Assert.Equal(12, sqlQuery.Arguments[1].Value);
}
public void InsertIntoColumnsValues()
{
var sqlBuilder = new InsertSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Into("Table")
.Columns("Column1", "Column2")
.Values("Foo", 12)
.ToSqlQuery();
Assert.Equal("INSERT INTO Table (Column1,Column2) VALUES (?,?)", sqlQuery.CommandText);
Assert.Equal(2, sqlQuery.Arguments.Count);
Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
Assert.Equal("Foo", sqlQuery.Arguments[0].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
Assert.Equal(12, sqlQuery.Arguments[1].Value);
}
public void ExcuteInsertCommand <B>(B aEntity, string con) where B : EntityBase, new()
{
aEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now);
SqlConnection l_Connection = new SqlConnection(con);
SqlCommand l_Command = new SqlCommand();
l_Command.Connection = l_Connection;
var l_InsertQuery = new InsertSqlBuilder <B>(aEntity);
QueryDirector <B> l_Construct = new QueryDirector <B>(l_InsertQuery);
var l_Context = l_Construct.GetDataContext();
l_Command.CommandTimeout = 0;
l_Command.CommandText = l_Context.mSQL;
foreach (var l_SQLParameters in l_Context.mSQLParameters)
{
l_Command.Parameters.Add(l_SQLParameters);
}
try
{
l_Connection.Open();
l_Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
l_Connection.Close();
}
}
public void AutoIncrement_Build_insert_entity_with_existing_entity_in_many2one_relation()
{
var monkEntMap = SessionHelper.Factory.DbSettings.Map.EntityConfigs.FirstOrDefault(c => string.Equals(c.Name, "Monkey", StringComparison.Ordinal));
var zooEntMap = SessionHelper.Factory.DbSettings.Map.EntityConfigs.FirstOrDefault(c => string.Equals(c.Name, "Zoo", StringComparison.Ordinal));
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
Zoo zoo = new Zoo()
{
AcceptNewAnimals = true, Name = Guid.NewGuid().ToString(), City = "exwee"
};
builder.Build(zoo, zooEntMap, session);
Assert.IsTrue(zoo.Id > 0);
Monkey monkey = new Monkey()
{
Age = 12,
CanDoTricks = true,
Family = "Singe",
Location = "cage 48875",
Name = "curious george",
ReceivedOn = DateTime.Now,
Zoo = zoo,
};
Console.WriteLine("---- Building monkey ----");
var execList = builder.Build(monkey, monkEntMap, session);
Assert.AreEqual(2, execList.Statements.Count);
Assert.IsTrue(execList.Statements[1].Processed);
Assert.IsTrue(monkey.Id > 0);
Assert.AreEqual(zoo, monkey.Zoo);
}
public void AutoIncrement_Build_many_to_many_relation_should_save_new_entities()
{
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
Employee empl = new Employee {
AssignedToZooId = 1, EmailAddress = "mail@com", FirstName = "Joe", HiredOn = DateTime.Now, LastName = "Smith", Title = "some edm", Telephone = "125699555"
};
Animal marsu = new Animal()
{
Name = "marsupilami",
Age = 4,
ReceivedOn = DateTime.Now,
Location = "marsu L23",
ZooId = 1
};
empl.AnimalsOnAnimalsHandler_EmployeeId.Add(marsu);
var execList = builder.Build(empl, session);
Assert.AreEqual(3, execList.Statements.Count);
Assert.IsFalse(execList.Statements[2].Processed);
}
public void AutoIncrement_Build_many_to_many_inverse_is_false_should_not_create_inserts_for_relations()
{
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
Employee empl = new Employee {
AssignedToZooId = 1, EmailAddress = "mail@com", FirstName = "Joe", HiredOn = DateTime.Now, LastName = "Smith", Title = "some edm", Telephone = "125699555"
};
Animal marsu = new Animal()
{
Name = "marsupilami 5",
Age = 4,
ReceivedOn = DateTime.Now,
Location = "marsu L24",
ZooId = 1
};
marsu.EmployeesOnAnimalsHandler_AnimalId.Add(empl);
var execList = builder.Build(marsu, session);
Assert.AreEqual(1, execList.Statements.Count);
}
public void AutoIncrement_Build_insert_entity_check_that_it_generate_parent_sql_first_inheritance()
{
var monkEntMap = SessionHelper.Factory.DbSettings.Map.EntityConfigs.FirstOrDefault(c => string.Equals(c.Name, "Monkey", StringComparison.Ordinal));
Monkey monkey = new Monkey()
{
Age = 12,
CanDoTricks = true,
Family = "Singe",
Location = "somecage",
Name = "joemonkey",
ReceivedOn = DateTime.Now,
ZooId = 1
};
var session = SessionHelper.Factory.OpenSession();
InsertSqlBuilder builder = new InsertSqlBuilder();
var execList = builder.Build(monkey, monkEntMap, session);
Assert.AreEqual(2, execList.Statements.Count);
Assert.IsTrue(execList.Statements[1].Processed);
Assert.IsTrue(monkey.Id > 0);
}
