public void AutoUpdateFromRowDataObject() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select().WithMetadata(true, SetupData.MetadataFileName) .From("Account") .AllColumns(false) .Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526) .Builder; Console.WriteLine(builder.ToSql()); ResultTable r = builder.Execute(); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account selected in {0}ms")); g = StopWatch.Start(); Assert.IsTrue(r.Count == 1, "Executed 1 account"); RowData row = r.First(); row.Column("Name", Guid.NewGuid().ToString()); builder = SqlBuilder.Update().Update(row, new string[] { "AccountID", "Name" }); Console.WriteLine(builder.ToSql()); r = builder.Execute(); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account updated in {0}ms")); row.AcceptChanges(); Assert.IsTrue(r.First().Column <string>("Name") == row.Column <string>("Name"), "Names are equal"); Assert.IsFalse(row.HasChanges, "The row does not have changes"); }
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 ContentResult Update(string rowData) { RowData row = SerializationExtensions.FromJson <RowData>(rowData); row.LoadMetadata(); row.Column("Name", "Random " + Guid.NewGuid().ToString()); SqlBuilder builder = row.Update(false, true); ResultTable result = builder.Execute(30, false); if (result.Count == 1) { builder = SqlBuilder.Select() .From("Contact") .Column("ContactID") .Column("Name") .Column("Telephone") .Column("WorkEmail") .Column("ModifiedOn") .WithMetadata().InnerJoin("AccountID") .Column("Name", "AccountName") .From("Contact") .Where <decimal>("Contact", "ContactID", SqlOperators.Equal, result.First().Column <decimal>("ContactID")) .Builder(); result = builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate); row = result.First(); return(Content(SerializationExtensions.ToJson <dynamic>(row), "application/json")); } return(Content("Hmmmm...?", "application/text")); }
public void TestIfElseInsertUpdate() { decimal Id = 0; Console.WriteLine("Pass 1/2: Inserting Account ID {0}", Id); ResultTable result = InsertOrUpdate(Id); Console.WriteLine("{0} rows returned", result.Count); Assert.IsTrue(result.Count == 1); Id = result.First().Column <decimal>("AccountID"); Console.WriteLine("Updating returned Account ID {0}", Id); result = InsertOrUpdate(Id); Assert.IsTrue(result.Count == 1); decimal Id2 = result.First().Column <decimal>("AccountID"); Console.WriteLine("Pass 2/2: Account ID {0} returned", Id); Assert.IsTrue(Id.Equals(Id2)); }
public void CrossJoinAccountsAndContactsWithMetadata() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select(100000) .From("Account") .AllColumns() .WithMetadata().CrossJoin("Contact", null) .AllColumns() .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(30, false); Console.WriteLine("{0} rows selected in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); Console.WriteLine(SerializationExtensions.ToJson <dynamic>(result.First(), true)); g = StopWatch.Start(); result = builder.Execute(30, false); Console.WriteLine("Executed a second time in {0}ms", StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); Console.WriteLine(SerializationExtensions.ToJson <dynamic>(result.First(), true)); }
public PartialViewResult Edit(string Id, string Table, ListTypes ListType, string ListName) { MetadataTable table = SqlBuilder.DefaultMetadata.FindTable(Table); // SqlBuilder builder = table.ToSqlBuilder(ListType != ListTypes.Custom ? ListType.ToString() : ListName); SqlBuilder builder = table.ToSqlBuilder(""); builder.BaseTable().WithMetadata().WherePrimaryKey(new object[] { (object)Id }); ResultTable result = builder.Execute(); Form model = FormFactory.Default.BuildForm(builder); model.Initialize(result.First()); return(PartialView(model.EditDialogViewUrl, model)); }
public int InsertOneAccountInternal(bool WriteSql = true) { Guid g = StopWatch.Start(); string id = DateTime.Now.Ticks.ToString(); SqlBuilder builder = SqlBuilder.Insert() .Into("account") .Value <string>("Name", "Test Account " + id, System.Data.SqlDbType.VarChar) .Value <string>("Address1", "Address1 " + id, System.Data.SqlDbType.VarChar) .Value <string>("Address2", "Address2 " + id, System.Data.SqlDbType.VarChar) .Value <string>("Address3", "Address3 " + id, System.Data.SqlDbType.VarChar) .Value <string>("PostalCode", "1165", System.Data.SqlDbType.VarChar) .Value <string>("City", "City " + id, System.Data.SqlDbType.VarChar) .Value <string>("Telephone", "500-500-2015", System.Data.SqlDbType.VarChar) .Value <string>("Telefax", "500-500-2015", System.Data.SqlDbType.VarChar) .Value <string>("Web", "http://www.company.com", System.Data.SqlDbType.VarChar) .Value <decimal>("AccountTypeID", 1, System.Data.SqlDbType.Decimal) .Value <decimal>("DataSourceID", 1, System.Data.SqlDbType.Decimal) .Value <decimal>("StateID", 1, System.Data.SqlDbType.Decimal) .Value <decimal>("CreatedBy", 1, System.Data.SqlDbType.Decimal) .Value <decimal>("ModifiedBy", 1, System.Data.SqlDbType.Decimal) .Value <DateTime>("CreatedOn", DateTime.Now, System.Data.SqlDbType.DateTime) .Value <DateTime>("ModifiedOn", DateTime.Now, System.Data.SqlDbType.DateTime) .Value <decimal>("OwningUserID", 1, System.Data.SqlDbType.Decimal) .Value <decimal>("OwningBusinessUnitID", 1, System.Data.SqlDbType.Decimal) .Output() .Column("AccountID", System.Data.SqlDbType.Decimal) .Builder; if (WriteSql) { Console.WriteLine(builder.ToSql()); } g = StopWatch.Start(); ResultTable result = builder.Execute(); if (FirstInsertedId == 99999999) { FirstInsertedId = result.First().Column <decimal>("AccountID"); } if (WriteSql) { Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account inserted in {0}ms")); } Assert.IsTrue(result.Count == 1); return(result.Count); }
public void PopulateClassFromResultTable() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select(1) .From("Account") .AllColumns(false) .SubSelect("Contact", "AccountID", "AccountID", null, null, "Contacts") .AllColumns(false) .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(); Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); g = StopWatch.Start(); Account account = TypeBuilder.PopulateObject <Account>(result.First()); Console.WriteLine("Account {0}-{1} with {2} Contacts created from RowData executed in {3}ms", account.AccountID, account.Name, account.Contacts.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); }
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 ActionResult EditForm() { MetadataTable table = SqlBuilder.DefaultMetadata.FindTable("Contact"); // Form model = FormFactory.Default.GetForm(table, FormTypes.Primary); //SqlBuilder builder = SqlBuilder.Select() // .From("Contact").AllColumns() // .Where<decimal>("Contact","ContactID", SqlOperators.Equal,1403) // .Builder(); SqlBuilder builder = SqlBuilder.Select() .From("Contact").Columns("ContactID", "Name", "Title", "WorkEmail", "JobfunctionID", "JobpositionID", "StateID") .WithMetadata() .AutoJoin("AccountID") .From("Contact").WithMetadata().AutoJoin("JobfunctionID") .From("Contact").WithMetadata().AutoJoin("JobpositionID") .Where <decimal>("Contact", "ContactID", SqlOperators.Equal, 1429) .Builder(); ResultTable result = builder.Execute(); Form model = FormFactory.Default.BuildForm(builder); MetadataColumn mc; if (table.Columns.TryGetValue("JobfunctionID", out mc)) { } model.Initialize(result.First()); //model.FormLayout = FormLayouts.Horizontal; //model.CssFormLayout = "form-horizontal"; //model.Sections[0].SectionLayout = SectionLayouts.VerticalTwoColumns; //return View("~/Views/TinySql/Details/Form.cshtml", model); return(View(model)); }
//public ContentResult Save(FormCollection Model, string Table, ListTypes ListType, string ListName) // public ContentResult Save(SaveModel Model) public ContentResult Save(string rowData, string Table, ListTypes ListType, string ListName) { RowData row = SerializationExtensions.FromJson <RowData>(rowData); row.LoadMetadata(); row.LoadMissingColumns <bool>(); SqlBuilder builder = row.Update(false, true); ResultTable result = builder.Execute(); if (result.Count == 1) { builder = row.Select(ListType != ListTypes.Custom ? ListType.ToString() : ListName); //builder = row.Metadata.ToSqlBuilder(ListType != ListTypes.Custom ? ListType.ToString() : ListName); //builder.WhereConditions = row.PrimaryKey(builder); result = builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate); if (result.Count == 1) { return(Content(SerializationExtensions.ToJson <dynamic>(result.First()), "application/json")); } //// object PK = result.First().Column(mt.PrimaryKey.Columns.First().Name); //Builder.BaseTable().WithMetadata().WherePrimaryKey(new object[] { (object)PK }); //ResultTable updated = Builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate); //if (updated.Count == 1) //{ // return Content(SerializationExtensions.ToJson<dynamic>(updated.First()), "application/json"); //} } //// Retrieve by Primary key //MetadataTable mt = SqlBuilder.DefaultMetadata.FindTable(Table); //List<object> PKs = new List<object>(); //foreach (MetadataColumn mc in mt.PrimaryKey.Columns) //{ // PKs.Add(Model[Table + "_" + mc.Name]); //} //// Create an empty row with the primary key set //RowData row = RowData.Create(mt, true, PKs.ToArray()); //// Change the row //foreach (string key in Model.Keys) //{ // if (!key.StartsWith("__")) // { // string ColumnName = key.Replace(Table, "").Replace("_", ""); // MetadataColumn mc; // if (mt.Columns.TryGetValue(ColumnName, out mc)) // { // if (!mc.IsReadOnly) // { // // row.Column(mc.Name, (object)Model[key]); // row.Column(mc.Name, Model[key]); // } // } // } //} //// Build SQL and update //SqlBuilder builder = row.Update(true, true); //ResultTable result = builder.Execute(30, false); //if (result.Count == 1) //{ // object PK = result.First().Column(mt.PrimaryKey.Columns.First().Name); // SqlBuilder Builder = mt.ToSqlBuilder(ListType != ListTypes.Custom ? ListType.ToString() : ListName); // Builder.BaseTable().WithMetadata().WherePrimaryKey(new object[] { (object)PK }); // ResultTable updated = Builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate); // if (updated.Count == 1) // { // return Content(SerializationExtensions.ToJson<dynamic>(updated.First()), "application/json"); // } //} return(Content("")); }
private static ResultTable ExecuteRelatedInternal(SqlBuilder builder, Dictionary <string, RowData> results) { if (results.Count > 0) { MetadataTable mt = builder.BaseTable().WithMetadata().Model; foreach (string key in results.Keys) { foreach (MetadataForeignKey fk in mt.ForeignKeys.Values.Where(x => (x.ReferencedSchema + "." + x.ReferencedTable).Equals(key, StringComparison.OrdinalIgnoreCase))) { RowData row = results[key]; foreach (MetadataColumnReference mcr in fk.ColumnReferences) { if (row.Columns.Contains(mcr.Column.Name)) { Field f = builder.BaseTable().FindField(mcr.Column.Name); if (f != null) { f.Value = row.Column(mcr.Column.Name); } else { (builder.BaseTable() as InsertIntoTable).Value(mcr.Column.Name, row.Column(mcr.Column.Name), SqlDbType.VarChar); } } } } } } DataTable dt = new DataTable(); ResultTable table = new ResultTable(); using (SqlConnection context = new SqlConnection(builder.ConnectionString)) { context.Open(); SqlCommand cmd = new SqlCommand(builder.ToSql(), context); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.AcceptChangesDuringFill = false; adapter.Fill(dt); context.Close(); } if (builder.SubQueries.Count > 0) { Dictionary <string, RowData> subresults = new System.Collections.Generic.Dictionary <string, RowData>(results); if (dt.Rows.Count > 0) { MetadataTable mt = builder.BaseTable().WithMetadata().Model; if (!subresults.ContainsKey(mt.Fullname)) { ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None); RowData row = rt.First(); table.Add(row); subresults.Add(mt.Fullname, row); } } foreach (SqlBuilder Builder in builder.SubQueries.Values) { ResultTable sub = ExecuteRelatedInternal(Builder, subresults); foreach (RowData row in sub) { table.Add(row); } } } return(table); }
public static ResultTable Execute(this SqlBuilder[] Builders, int TimeoutSeconds = 30) { ResultTable table = new ResultTable(); using (TransactionScope trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromSeconds(TimeoutSeconds) })) { try { foreach (SqlBuilder builder in Builders) { DataTable dt = new DataTable(); using (SqlConnection context = new SqlConnection(builder.ConnectionString)) { context.Open(); SqlCommand cmd = new SqlCommand(builder.ToSql(), context); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.AcceptChangesDuringFill = false; adapter.Fill(dt); context.Close(); } if (builder.SubQueries.Count > 0) { Dictionary <string, RowData> results = new Dictionary <string, RowData>(); if (dt.Rows.Count > 0) { MetadataTable mt = builder.BaseTable().WithMetadata().Model; if (!results.ContainsKey(mt.Fullname)) { ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None); RowData row = rt.First(); results.Add(mt.Fullname, row); table.Add(row); } } foreach (SqlBuilder Builder in builder.SubQueries.Values) { ResultTable sub = ExecuteRelatedInternal(Builder, results); foreach (RowData row in sub) { table.Add(row); } } } } } catch (TransactionException exTrans) { trans.Dispose(); throw exTrans; } catch (SqlException exSql) { trans.Dispose(); throw exSql; } catch (ApplicationException exApplication) { trans.Dispose(); throw exApplication; } trans.Complete(); } return(table); }
public void InsertAccountAndRelatedContact() { SqlBuilder max = SqlBuilder.Select(1) .From("Account") .Column("AccountID") .OrderBy("AccountID", OrderByDirections.Desc) .Builder; decimal Accountid = max.Execute().First().Column <decimal>("AccountID"); int numAccounts = 5; int numContacts = 10; int numActivities = 5; List <SqlBuilder> builders = new List <SqlBuilder>(); for (int i = 0; i < numAccounts; i++) { SqlBuilder AccountBuilder = SqlBuilder.Insert() .Into("Account") .Value("Name", "Account " + DateTime.Now.ToString(), System.Data.SqlDbType.VarChar) .Value("AccountTypeID", 1, System.Data.SqlDbType.VarChar) .Value("DataSourceID", 1, System.Data.SqlDbType.VarChar) .Value("StateID", 1, System.Data.SqlDbType.VarChar) .Value("CreatedBy", 2, System.Data.SqlDbType.VarChar) .Value("CreatedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("ModifiedBy", 2, System.Data.SqlDbType.VarChar) .Value("ModifiedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("OwningUserID", 2, System.Data.SqlDbType.VarChar) .Value("OwningBusinessUnitID", 2, System.Data.SqlDbType.VarChar) .Output().PrimaryKey() .Builder(); for (int x = 0; x < numContacts; x++) { SqlBuilder ContactBuilder = SqlBuilder.Insert() .Into("Contact") .Value("Name", "Contact " + DateTime.Now.ToString(), SqlDbType.VarChar) .Value("AccountID", 0, SqlDbType.VarChar) .Value("JobfunctionID", 1, SqlDbType.VarChar) .Value("JobpositionID", 1, SqlDbType.VarChar) .Value("DataSourceID", 1, System.Data.SqlDbType.VarChar) .Value("StateID", 1, System.Data.SqlDbType.VarChar) .Value("CreatedBy", 2, System.Data.SqlDbType.VarChar) .Value("CreatedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("ModifiedBy", 2, System.Data.SqlDbType.VarChar) .Value("ModifiedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("OwningUserID", 2, System.Data.SqlDbType.VarChar) .Value("OwningBusinessUnitID", 2, System.Data.SqlDbType.VarChar) .Output().PrimaryKey() .Builder(); for (int y = 0; y < numActivities; y++) { ContactBuilder.AddSubQuery( "Activity" + y.ToString(), SqlBuilder.Insert() .Into("Activity") .Value("ActivityTypeID", 1, SqlDbType.VarChar) .Value("Title", "Activity " + DateTime.Now.ToString(), SqlDbType.VarChar) .Value("Date", DateTime.Now.AddDays(1), SqlDbType.VarChar) .Value("ActivityStatusID", 1, SqlDbType.VarChar) .Value("CreatedBy", 2, System.Data.SqlDbType.VarChar) .Value("CreatedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("ModifiedBy", 2, System.Data.SqlDbType.VarChar) .Value("ModifiedOn", DateTime.Now, System.Data.SqlDbType.VarChar) .Value("OwningUserID", 2, System.Data.SqlDbType.VarChar) .Value("OwningBusinessUnitID", 2, System.Data.SqlDbType.VarChar) .Output().PrimaryKey() .Builder() ); } AccountBuilder.AddSubQuery("Contact" + x.ToString(), ContactBuilder); } builders.Add(AccountBuilder); } ResultTable results = builders.Execute(); Assert.IsTrue(results.Count >= numAccounts); Console.WriteLine("Inserted {0} accounts with {1} contacts with {2} activities. Total = {3}", numAccounts, numContacts, numActivities, numActivities * numContacts * numActivities); Console.WriteLine("{0} Results returned:", results.Count); foreach (RowData r in results) { foreach (string c in r.Columns) { Console.Write("{0} = {1}. ", c, r.Column(c)); } Console.WriteLine(""); } Console.WriteLine(""); SqlBuilder builder = SqlBuilder.Select() .From("Account").AllColumns() .Where("Account", "AccountID", SqlOperators.Equal, results.First().Column("AccountID")) .Builder.BaseTable() .SubSelect("Contact") .AllColumns() .Builder(); ResultTable account = builder.Execute(); dynamic row = account.First(); ResultTable contacts = (row.ContactList as ResultTable); Console.WriteLine("Account ID {0} with name: {1} and a total of {2} contacts", row.AccountID, row.Name, contacts.Count); foreach (dynamic contact in contacts) { Console.WriteLine("Related contact ID {0} with name: {1}", contact.ContactID, contact.Name); } Console.WriteLine("\r\nDeleting accounts > {0}", Accountid); builder = SqlBuilder.Delete() .From("Account") .Where <decimal>("Account", "AccountID", SqlOperators.GreaterThan, Accountid) .Builder(); Assert.IsTrue(builder.ExecuteNonQuery() == numAccounts); Console.WriteLine("Account ID {0} deleted", row.AccountID); }