public async Task Test_Version_PropName() { var p = new { Id = new Guid("cf9fad7a-9775-28b9-7693-11e6ea3b1484"), Name = "John", BirthDate = new DateTime(1975, 03, 17), Version = Environment.TickCount }; using (var ts = DbContext.CreateTransactionScope()) { await db.QueryAsync("create table #Person (Id uniqueidentifier primary key, Name nvarchar(50), BirthDate datetime2, Version timestamp);", sqlTextOnly : true); var query = Helpers.QB.Upsert(p, tableName: "#Person", excludedInsertProperties: n => (n == "Version"), includedUpdateProperties: n => (n != "Version" && n != "Id") ); var batch = QueryBatch.Create(); batch.AddQuery(query); await db.CommitQueryBatchAsync(batch); dynamic row = (await db.QueryAsync("select * from #Person;")).Single(); Assert.IsTrue(row.Id == p.Id && row.Name == p.Name && row.BirthDate == p.BirthDate); await db.QueryAsync("drop table #Person;"); } } // Test_Version_PropName()
public void A_query_with_GOs_produces_multiple_Query_objects() { var batch = QueryBatch.Parse(@"-- test query... select 1 GO select foo from bar go insert into table 1 (name, desc) values('a name like gogo', 'i want to GO now.') GO select 3 GO GO GO GO "); Assert.That(batch.Queries.Count, Is.EqualTo(4), "Empty queries should be ignored"); Assert.That(batch.Queries[0].Sql, Is.EqualTo(@"-- test query... select 1")); Assert.That(batch.Queries[1].Sql, Is.EqualTo("select foo\r\nfrom bar")); Assert.That(batch.Queries[2].Sql, Is.EqualTo(@"insert into table 1 (name, desc) values('a name like gogo', 'i want to GO now.')" )); Assert.That(batch.Queries[3].Sql, Is.EqualTo("select 3")); }
private object Enqueue(IQuery query) { if (_currentBatch == null) { _currentBatch = new QueryBatch(_context); } return(_currentBatch.Add(query)); }
public async Task TestExecuteAsync() { var queryBatch = QueryBatch.Create(); queryBatch.AddQuery("declare @foo table(id int not null); insert @foo values (@id), (@id), (@id);", new { id = 37 }); var val = await db.CommitQueryBatchAsync(queryBatch); Assert.AreEqual(val, 3); }
/// <summary>Initializes a new instance of the <see cref="ViewTableForm"/> class.</summary> /// <param name="services">The services.</param> /// <param name="settings">The settings.</param> public ViewTableForm(IApplicationServices services, IApplicationSettings settings) : this() { _services = services; _settings = settings; _batch = new QueryBatch(); TableName = string.Empty; Text = Resources.ViewData; dataGridViewResult.DefaultCellStyle.NullValue = _settings.NullText; dataGridViewResult.DataBindingComplete += DataGridViewResultDataBindingComplete; _services.Settings.DatabaseConnectionReset += SettingsDatabaseConnectionReset; _services.SystemMessagePosted += ServicesSystemMessagePosted; }
public void Batch_indicators_on_a_line_alone_cause_issues() { var batch = QueryBatch.Parse(@"-- issue... insert into table 1 (name, desc) values('foo', 'if the go is on a line by itself we have a problem...')"); Assert.That(batch.Queries.Count, Is.EqualTo(1), "This fails for now..."); // Basically there is no easy workaround short of writing an actual SQL // query parser... allow 'turn off batches' will do... // A good comprimise would token parse taking string (and escaping) into account // but even that would add up... }
/// <summary>Fills the list with the batch result sets.</summary> /// <param name="batch">The query batch.</param> public void Fill(QueryBatch batch) { _batch = batch; lstBatches.Items.Clear(); if (batch == null) { return; } for (int setIndex = 0; setIndex < batch.Queries.Count; setIndex++) { var query = batch.Queries[setIndex]; if (query.Result != null && query.Result.Tables.Count > 0) { string setName = string.Format("Result Set {0} ({1} tables)", setIndex, query.Result.Tables.Count); lstBatches.Items.Add(setName); } } lstBatches.SelectedIndex = 0; }
/// <summary>Fills the list with the named batch results.</summary> /// <param name="batch">The batch.</param> public void Fill(QueryBatch batch) { batchQuerySelectControl1.Fill(batch); }
void UpdateBatch(QueryBatch queryBatch) { Profiler.BeginSample("UpdateBatch"); var queryCount = queryBatch.queryIds.Count; Profiler.BeginSample("Get hitcollision entities"); var hitCollEntityArray = m_colliderGroup.GetEntityArraySt(); var hitCollDataArray = m_colliderGroup.GetComponentDataArraySt <HitCollisionData>(); var hitColliders = new NativeList <Entity>(hitCollEntityArray.Length, Allocator.TempJob); var hitColliderData = new NativeList <HitCollisionData>(hitCollEntityArray.Length, Allocator.TempJob); var hitColliderFlags = new NativeList <uint>(hitCollEntityArray.Length, Allocator.TempJob); for (int i = 0; i < hitCollEntityArray.Length; i++) { var hitCollisionOwner = EntityManager.GetComponentData <HitCollisionOwnerData>(hitCollDataArray[i].hitCollisionOwner); if (hitCollisionOwner.collisionEnabled == 0) { continue; } queryBatch.boundsHistory.Add(EntityManager.GetBuffer <HitCollisionData.BoundsHistory>(hitCollEntityArray[i])); hitColliderData.Add(hitCollDataArray[i]); hitColliders.Add(hitCollEntityArray[i]); hitColliderFlags.Add(hitCollisionOwner.colliderFlags); } Profiler.EndSample(); // Environment test Profiler.BeginSample("Environment test"); var envTestCommands = new NativeArray <RaycastCommand>(queryCount, Allocator.TempJob); var envTestResults = new NativeArray <RaycastHit>(queryCount, Allocator.TempJob); for (int nQuery = 0; nQuery < queryCount; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; // Start environment test var query = queryData.query; envTestCommands[nQuery] = new RaycastCommand(query.origin, query.direction, query.distance, m_environmentMask); } var envTestHandle = RaycastCommand.ScheduleBatch(envTestCommands, envTestResults, 10); envTestHandle.Complete(); Profiler.EndSample(); Profiler.BeginSample("Handle environment test"); for (int nQuery = 0; nQuery < queryCount; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; var result = envTestResults[nQuery]; var impact = result.collider != null; // query distance is adjusted so followup tests only are done before environment hit point if (impact) { queryData.query.distance = result.distance; // Set environment as default hit. Will be overwritten if HitCollision is hit queryData.result.hit = 1; queryData.result.hitPoint = result.point; queryData.result.hitNormal = result.normal; if (result.collider.gameObject.layer == m_hitCollisionLayer) { var hitCollision = result.collider.GetComponent <HitCollision>(); if (hitCollision != null) { queryData.result.hitCollisionOwner = hitCollision.owner; } } } } Profiler.EndSample(); // Start broadphase tests Profiler.BeginSample("Broadphase test"); var broadphaseHandels = new NativeArray <JobHandle>(queryCount, Allocator.Temp); for (int nQuery = 0; nQuery < queryCount; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; var query = queryData.query; queryData.broadPhaseBounds = new NativeArray <sphere>(hitColliderData.Length, Allocator.TempJob); for (int i = 0; i < hitColliderData.Length; i++) { // Get bounds for tick var histIndex = hitColliderData[i].GetHistoryIndex(query.hitCollisionTestTick); var boundSphere = primlib.sphere(queryBatch.boundsHistory[i][histIndex].pos, hitColliderData[i].boundsRadius); queryData.broadPhaseBounds[i] = boundSphere; } queryData.broadTestJob = new BroadPhaseSphereCastJob(hitColliders, hitColliderData, hitColliderFlags, queryData.broadPhaseBounds, query.ExcludeOwner, Entity.Null, query.mask, new ray(query.origin, query.direction), query.distance, query.radius); broadphaseHandels[nQuery] = queryData.broadTestJob.Schedule(); } var broadphaseHandle = JobHandle.CombineDependencies(broadphaseHandels); broadphaseHandels.Dispose(); broadphaseHandle.Complete(); Profiler.EndSample(); // Start narrow tests Profiler.BeginSample("Narrow test"); // TODO (mogensh) find out how to combine jobs without "write to same native" issue //var handles = new NativeArray<JobHandle>(queryCount, Allocator.TempJob); for (int nQuery = 0; nQuery < queryCount; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; var query = queryData.query; var broadPhaseResult = queryData.broadTestJob.result; // Start narrow tests queryData.narrowTestJobs.Clear(); //var narrowTestHandles = new NativeArray<JobHandle>(broadPhaseResult.Length, Allocator.Temp); for (var i = 0; i < broadPhaseResult.Length; i++) { var entity = broadPhaseResult[i]; var ray = new ray(query.origin, query.direction); queryData.narrowTestJobs.Add(new SphereCastSingleJob(EntityManager, entity, ray, query.distance, query.radius, query.hitCollisionTestTick)); // narrowTestHandles[i] = queryData.narrowTestJobs[i].Schedule(); var handle = queryData.narrowTestJobs[i].Schedule(); handle.Complete(); } //handles[nQuery] = JobHandle.CombineDependencies(narrowTestHandles); // narrowTestHandles.Dispose(); } // var handle = JobHandle.CombineDependencies(handles); // handles.Dispose(); // handle.Complete(); Profiler.EndSample(); // Find closest Profiler.BeginSample("Find closest"); for (int nQuery = 0; nQuery < queryBatch.queryIds.Count; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; var query = queryData.query; var closestIndex = -1; var closestDist = float.MaxValue; for (int i = 0; i < queryData.narrowTestJobs.Count; i++) { var result = queryData.narrowTestJobs[i].result[0]; var hit = result.hit == 1; if (hit) { var dist = math.distance(query.origin, result.primCenter); if (dist < closestDist) { closestDist = dist; closestIndex = i; } } } if (closestIndex != -1) { var result = queryData.narrowTestJobs[closestIndex].result[0]; queryData.result.hit = 1; queryData.result.hitPoint = result.primCenter; queryData.result.hitNormal = -queryData.query.direction; // TODO (mogensh) find correct normal var hitCollisionData = EntityManager.GetComponentData <HitCollisionData>( queryData.narrowTestJobs[closestIndex].hitCollObject); queryData.result.hitCollisionOwner = hitCollisionData.hitCollisionOwner; } // TODO (mogensh) keep native arrays for next query queryData.broadTestJob.Dispose(); for (int i = 0; i < queryData.narrowTestJobs.Count; i++) { queryData.narrowTestJobs[i].Dispose(); } } Profiler.EndSample(); for (int nQuery = 0; nQuery < queryBatch.queryIds.Count; nQuery++) { var queryId = queryBatch.queryIds[nQuery]; var queryData = m_queries[queryId]; queryData.broadPhaseBounds.Dispose(); } envTestCommands.Dispose(); envTestResults.Dispose(); hitColliders.Dispose(); hitColliderData.Dispose(); hitColliderFlags.Dispose(); Profiler.EndSample(); }
private void ExecuteBatch() { _currentBatch.Execute(_mapTokenToResult); _currentBatch = null; }
public async Task SanityTestBatchedQueries() { { var batch1 = QueryBatch.Create(); for (int i = 0; i < 50; ++i) { batch1.AddQuery("select [Answer] = 2;"); } int result1 = await db.CommitQueryBatchAsync(batch1); Console.WriteLine(result1); Assert.IsTrue(result1 == -1); // -1 // No rows were changed per batch; 1 batch only (default batch size is 50). var batch2 = QueryBatch.Create(); for (int i = 0; i < 65; ++i) { batch2.AddQuery("select [Answer] = 2;"); } int result2 = await db.CommitQueryBatchAsync(batch2); Console.WriteLine(result2); Assert.IsTrue(result2 == -1); // -1 // No rows were changed per batch; 1 batch only (short 2nd batch of 15 queries merged into the 1st batch) // short batch is 50/3 = 16 queries or less } { var batch3 = QueryBatch.Create(); for (int i = 0; i < 70; ++i) { batch3.AddQuery("select [Answer] = 2;"); } int result3 = await db.CommitQueryBatchAsync(batch3); Console.WriteLine(result3); Assert.IsTrue(result3 == -2); // -2 // No rows were changed per batch; 2 batches: // 1st batch of 50 queries and 2nd batch of 20 queries // last batch is larger than short batch - triggers an additional db call } { var batch1 = QueryBatch.Create(); var batch2 = QueryBatch.Create(); for (int i = 0; i < 40; ++i) { batch1.AddQuery("select [Answer] = 1;"); batch2.AddQuery("select [Answer] = 2;"); } batch2.Append(batch1); // adding batch1 queries to batch2 int result = await db.CommitQueryBatchAsync(QueryBatch.Create(new[] { batch1, batch2 })); Console.WriteLine(result); Assert.IsTrue(result == -3); // -3 // No rows were changed per batch; 3 batches: // 1st batch of 50 queries; 2nd batch of 50 queries; 3rd batch of 20 queries // last batch is larger than short batch - triggers an additional db call } { var batch = QueryBatch.Create(); for (int i = 0; i < 43; ++i) { batch.AddQuery("select [Answer] = 1;"); } int result = await db.CommitQueryBatchAsync(queryBatch : batch, batchSize : 10); Console.WriteLine(result); Assert.IsTrue(result == -4); // -4 // 3 batches with 10 queries and last batch with 13 queries } { var dbTemp = DbContext.Create(db.ConnectionString); var batch = QueryBatch.Create(); for (int i = 0; i < 43; ++i) { batch.AddQuery("select [Answer] = 1;"); } dbTemp.BatchSize = 10; int result = await dbTemp.CommitQueryBatchAsync(batch); Console.WriteLine(result); Assert.IsTrue(result == -4); // -4 // 3 batches with 10 queries and last batch with 13 queries } { var queryBatch = QueryBatch.Create(); var queryInfo = QueryInfo.Create("declare @foo table(id int not null); insert @foo values (@id), (@id), (@id);", new { id = 37 }); queryBatch.AddQuery(queryInfo); int result = await db.CommitQueryBatchAsync(queryBatch); Console.WriteLine(result); Assert.IsTrue(result == 3); } }
/// <summary>The get.</summary> /// <param name="viewOrTableName">The view or table name.</param> /// <returns></returns> public DataTable Get(string viewOrTableName) { DbDataAdapter adapter = null; DbCommand cmd = null; DataTable dt = null; QueryBatch batch = new QueryBatch(); Query query = new Query("SELECT * FROM " + viewOrTableName); if (string.IsNullOrEmpty(viewOrTableName)) { return(null); } if (_dataTables.ContainsKey(viewOrTableName)) { return(_dataTables[viewOrTableName]); } try { if (_dbConnection == null || _dbConnection.State != ConnectionState.Open) { _dbConnection = Services.Settings.GetOpenConnection(); } query.Result = new DataSet(viewOrTableName + " View"); batch.Clear(); batch.Add(query); adapter = Services.Settings.ProviderFactory.CreateDataAdapter(); cmd = _dbConnection.CreateCommand(); cmd.CommandText = query.Sql; cmd.CommandType = CommandType.Text; adapter.SelectCommand = cmd; adapter.Fill(query.Result); } // catch (Exception exp) // { // throw; // } finally { if (adapter != null) { adapter.Dispose(); } if (cmd != null) { cmd.Dispose(); } } if (query.Result.Tables.Count > 0) { dt = query.Result.Tables[0]; _dataTables[viewOrTableName] = dt; } return(dt); }
public void A_single_query_produces_1_batch_with_1_Query() { var batch = QueryBatch.Parse("select 1"); Assert.That(batch.Queries.Count, Is.EqualTo(1)); }
public void No_single_query_produces_empty_batch() { var batch = QueryBatch.Parse(""); Assert.That(batch.Queries.Count, Is.EqualTo(0)); }