public static StoredProcedure StoredProcedure(string Name, string Schema = null)
{
SqlBuilder builder = new SqlBuilder()
{
StatementType = StatementTypes.Procedure
};
builder.Procedure = new StoredProcedure()
{
Builder = builder,
Name = Name,
Schema = Schema
};
return builder.Procedure;
}
public void InsertAccountFromObject()
{
Guid g = StopWatch.Start();
Account acc = new Account()
{
AccountID = 88888888,
Name = "Test Account",
AccountTypeID = 1,
DatasourceID = 1,
StateID = 1,
CreatedBy = 1,
CreatedOn = DateTime.Now,
ModifiedBy = 1,
ModifiedOn = DateTime.Now,
OwningUserID = 1,
OwningBusinessUnitID = 1
};
SqlBuilder builder = TypeBuilder.Insert<Account>(acc);
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Assert.IsTrue(result.Count == 1);
decimal i = result.First().Column<decimal>("AccountID");
Console.WriteLine("Inserted Account {0}", i);
SqlBuilder b1 = SqlBuilder.Delete()
.From("account", null)
.Where<decimal>("account", "AccountID", SqlOperators.Equal, i)
.Builder;
i = new SqlBuilder[] { b1 }.ExecuteNonQuery();
Console.WriteLine("{0} Accounts deleted", i);
Assert.IsTrue(i == 1);
}
public void DeleteInsertedAccounts()
{
SqlBuilder b1 = SqlBuilder.Delete()
.From("account", null)
.Where<decimal>("account", "AccountID", SqlOperators.GreaterThanEqual, FirstInsertedId)
.Builder;
Console.WriteLine(b1.ToSql());
Guid g = StopWatch.Start();
int i = new SqlBuilder[] { b1 }.ExecuteNonQuery();
Console.WriteLine("{0} Accounts deleted", i);
Assert.IsTrue(i >= 1000);
FirstInsertedId = 99999999;
}
public void InsertOneAccountAsStoredProcedure()
{
Guid g = StopWatch.Start();
SqlBuilder builder = GetInsertUpdateBuilder();
Console.WriteLine(builder.ToSql());
int i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The insert procedure did not return 1 row");
decimal ID = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Assert.IsTrue(ID > 0, "The Account was not inserted");
Console.WriteLine(string.Format("An account with the ID {0} was inserted in {1}ms", ID, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
g = StopWatch.Start();
SqlBuilder select = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.Where<decimal>("Account", "AccountID", SqlOperators.Equal, ID)
.Builder();
Console.WriteLine(select.ToSql());
ResultTable result = select.Execute();
Assert.IsTrue(result.Count == 1, "The Account could not be retrieved after insert");
dynamic row = result.First();
Console.WriteLine("Account ID {0}: {1} {2} retrieved in {3}ms", row.AccountID, row.Name, row.Address1, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
builder = GetInsertUpdateBuilder(ID, "Nørregade 28D");
i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The update procedure did not return 1 row");
decimal ID2 = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Assert.AreEqual<decimal>(ID, ID2);
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "The Account was updated in {0}ms"));
g = StopWatch.Start();
result = select.Execute();
Assert.IsTrue(result.Count == 1, "The Account could not be retrieved after update");
row = result.First();
Console.WriteLine("Account ID {0}: {1} {2} retrieved in {3}ms", row.AccountID, row.Name, row.Address1, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
builder = SqlBuilder.Delete()
.From("Account")
.Where<decimal>("Account", "AccountID", SqlOperators.Equal, ID2)
.Builder();
Console.WriteLine(builder.ToSql());
i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The Account could not be deleted");
}
public static bool CacheSqlBuilder(string Key, SqlBuilder Builder, CacheItemPolicy Policy = null)
{
CacheItem item = MemoryCache.Default.AddOrGetExisting(new CacheItem(Key, Builder), (Policy ?? CachePolicy));
return item.Value == null;
}
public SqlBuilder AddSubQuery(string Name, SqlBuilder Builder)
{
if (Builder.StatementType != StatementTypes.Insert && Builder.StatementType != StatementTypes.Update)
{
// Only set the parent builder for statements that share parameter declarations
Builder.ParentBuilder = this;
}
return _SubQueries.AddOrUpdate(Name, Builder, (k, v) => { return Builder; });
}