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