public ProductLookupFlow(TargetTable dimTableTarget, int batchSize = 8192, int cacheSize = 1048576) : base( //Tells DbDataJoiner the join condition is 'OrderEx.Product.FullName = TABLE.ProductFullName' o => o.Product.FullName, dimTableTarget, DataflowOptions.Default, batchSize, cacheSize) { }
private DataTable GetDataRangeEndDT(TargetTable lottoType) { if (ViewState["DataRangeEnd" + lottoType.ToString()] == null) { string strlngTotalSN; #region get the first 0 in ingL1 as th last ingTotalSN using SqlCommand sqlCommand = new SqlCommand { Connection = new SqlConnection(new CglDBData().SetDataBase(lottoType, DatabaseType.Data)), CommandText = "SELECT [lngTotalSN] , [lngDateSN] FROM [tblData] WHERE [lngL1] = 0 ORDER BY [lngTotalSN] ASC ;" }; using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand); using DataTable dtImportFirst = new DataTable { Locale = InvariantCulture }; sqlDataAdapter.Fill(dtImportFirst); strlngTotalSN = dtImportFirst.Rows[0]["lngTotalSN"].ToString(); #endregion using SqlCommand sqlCommand01 = new SqlCommand { Connection = new SqlConnection(new CglData().SetDataBase(lottoType, DatabaseType.Data)), CommandText = "SELECT [lngTotalSN] , [lngDateSN] FROM [tblData] WHERE [lngTotalSN] <= @lngTotalSN ORDER BY [lngTotalSN] DESC ;" }; sqlCommand01.Parameters.AddWithValue("lngTotalSN", strlngTotalSN); using SqlDataAdapter sqlDataAdapter01 = new SqlDataAdapter(sqlCommand01); using DataTable dtImport = new DataTable { Locale = InvariantCulture }; sqlDataAdapter01.Fill(dtImport); ViewState["DataRangeEnd" + lottoType.ToString()] = dtImport; return(dtImport); } else { return((DataTable)ViewState["DataRangeEnd" + lottoType.ToString()]); } }
public async Task CommitBatch() { await Task.WhenAll(PrerequisiteOperation); if (BatchOperation.Count > 0) { try { await TargetTable.ExecuteBatchAsync(BatchOperation); } catch (StorageException exception) { if (exception.RequestInformation.HttpStatusCode == (int)HttpStatusCode.PreconditionFailed) { throw new ConcurrencyException(); } else { throw exception; } } } StartNewBatch(); }
public IQueryFactoryResult Process(IQueryContainer container) { var joinBuilder = new StringBuilder(); joinBuilder.Append($"{_joinAs.JoinType} JOIN {TargetTable.GetAlias().EnsureAlias()} AS {Alias.GetAlias().EnsureAlias()}" + $" ON {Alias.GetAlias().EnsureAlias()}.{TargetColumn.ColumnName.EnsureAlias()}" + $" = " + $"{SourceTable.GetAlias().EnsureAlias()}.{SourceColumn.ColumnName.EnsureAlias()}"); var parameters = new List <IQueryParameter>(); if (Condition != null) { var condQueryBuilder = new ConditionalQueryBuilder(); condQueryBuilder.QueryBuilder.Append(" "); foreach (var conditionCondition in Condition.Conditions) { conditionCondition.Render(condQueryBuilder, null); } joinBuilder.Append(condQueryBuilder.QueryBuilder); parameters.AddRange(condQueryBuilder.QueryParameters); } return(new QueryFactoryResult(joinBuilder.ToString(), parameters.ToArray())); }
//public static targettable gettargettable(string _targettable) //{ // targettable table = targettable.notfound; // if (!string.isnullorempty(_targettable)) // { // switch (_targettable) // { // case "vpntype": // table = targettable.vpntype; // break; // case "machine": // table = targettable.machine; // break; // case "plant": // table = targettable.plant; // break; // case "customer": // table = targettable.customer; // break; // case "connectiontype": // break; // } // } // return table; //} public static int GetTargetTable(string _targetTable) { TargetTable table = TargetTable.NotFound; if (!string.IsNullOrEmpty(_targetTable)) { switch (_targetTable) { case "VpnType": table = TargetTable.VpnType; break; case "Machine": table = TargetTable.Machine; break; case "Plant": table = TargetTable.Plant; break; case "Customer": table = TargetTable.Customer; break; case "ConnectionType": break; } } return((int)table); }
public override void WriteToStream(IndentStream stream) { stream.Write("MERGE"); if (!string.IsNullOrEmpty(IntoToken)) { stream.Write($" {IntoToken}"); } stream.Write(" "); TargetTable.WriteToStream(stream); if (WithOptions != null && WithOptions.Count > 0) { stream.Write(" WITH("); WithOptions.WriteToStreamWithComma(stream); stream.Write(")"); } if (TargetTableAliasName != null) { stream.Write(" AS "); TargetTableAliasName.WriteToStream(stream); } stream.WriteLine(); stream.Write("USING "); TableSource.WriteToStream(stream); if (TableSourceAliasName != null) { stream.Write(" AS "); TableSourceAliasName.WriteToStream(stream); } if (SourceColumnList != null && SourceColumnList.Count > 0) { stream.Write("("); SourceColumnList.WriteToStreamWithComma(stream); stream.Write(")"); } stream.Write(" ON "); OnMergeSearchCondition.WriteToStream(stream); if (WhenList != null && WhenList.Count > 0) { stream.WriteLine(); WhenList.WriteToStream(stream); } if (OutputList != null && OutputList.Count > 0) { stream.WriteLine(); stream.Write("OUTPUT "); OutputList.WriteToStreamWithComma(stream); } stream.Write(" ;"); }
/// <summary> Creates a new DataTable object. </summary> /// <param name="context"></param> protected override void DoWork(CodeActivityContext context) { DataTable table = TargetTable.Get(context); table.Rows.Add(Row.Get(context)); Result.Set(context, table); }
public DeleteBySearchImpl(SQLVendorImpl vendor, TargetTable targetTable, BooleanExpression condition) : base(vendor) { ArgumentValidator.ValidateNotNull(nameof(targetTable), targetTable); this._targetTable = targetTable; this._condition = condition; }
public IQueryFactoryResult Process(IQueryContainer container) { var joinBuilder = new StringBuilder(); joinBuilder.Append($"{_joinAs.JoinType} JOIN [{TargetTable.GetAlias()}] AS [{Alias.GetAlias()}]" + $" ON [{Alias.GetAlias()}].[{TargetColumn.ColumnName.TrimAlias()}]" + $" = " + $"[{SourceTable.GetAlias()}].[{SourceColumn.ColumnName}]"); return(new QueryFactoryResult(joinBuilder.ToString())); }
/// <summary> Performs the query requested. </summary> /// <param name="context"></param> protected override void DoWork(CodeActivityContext context) { DataTable table = TargetTable.Get(context); DataColumn column = new DataColumn(); column.DataType = ColumnType.Get(context); column.ColumnName = ColumnName.Get(context); table.Columns.Add(column); Result.Set(context, table); }
/// <summary> /// Validates properties needed for audience segmentation. /// </summary> /// <exception cref="ArgumentNullOrEmptyException">Throws when one of parameters is null or empty.</exception> public void ValidateForAudienceSegmentation() { ValidateTemplateWithDependencies(); SourceAudience.CheckArgumentNull("SourceAudience"); UserConnection.CheckArgumentNull("UserConnection"); SourceAlias.CheckArgumentNull("SourceAlias"); EntityIdSourceColumn.CheckArgumentNull("EntityIdSourceColumn"); SourceColumnForFilter.CheckArgumentNull("SourceColumnForFilter"); TargetTable.CheckArgumentNull("TargetTable"); EntityIdTargetColumn.CheckArgumentNull("EntityIdTargetColumn"); ReplicaIdTargetColumn.CheckArgumentNull("ReplicaIdTargetColumn"); RecordIdTargetColumn.CheckArgumentNull("RecordIdTargetColumn"); }
private void DataRangeStartInit(TargetTable lottoType) { using DataTable dtDataRangeEnd = GetDataRangeEndDT(lottoType); cmbDataRangeEnd.DataSource = dtDataRangeEnd.DefaultView; cmbDataRangeEnd.DataValueField = "lngTotalSN"; cmbDataRangeEnd.DataTextField = "lngDateSN"; cmbDataRangeEnd.SelectedIndex = 0; cmbDataRangeEnd.DataBind(); _gstuSearch.LngDataStart = long.Parse(dtDataRangeEnd.Rows[dtDataRangeEnd.Rows.Count - 1]["lngTotalSN"].ToString(), InvariantCulture); _gstuSearch.LngDataEnd = long.Parse(dtDataRangeEnd.Rows[0]["lngTotalSN"].ToString(), InvariantCulture); _gstuSearch.LngTotalSN = long.Parse(dtDataRangeEnd.Rows[0]["lngTotalSN"].ToString(), InvariantCulture); ViewState["_gstuSearch"] = _gstuSearch; }
public UpdateBySearchImpl(SQLVendorImpl vendor, TargetTable targetTable, BooleanExpression condition, ImmutableArray <SetClause> setClauses) : base(vendor) { setClauses.ValidateNotEmpty(nameof(setClauses)); foreach (var clause in setClauses) { ArgumentValidator.ValidateNotNull(nameof(clause), clause); } this._targetTable = targetTable; this._condition = condition; this._setClauses = setClauses; }
public override int UploadImpl(DataTable dt) { var matchedColumns = GetMapping(dt.Columns.Cast <DataColumn>()); int affected = 0; using (var cmd = new MySqlCommand("", (MySqlConnection)Connection.Connection, (MySqlTransaction)Connection.Transaction)) { if (BulkInsertBatchTimeoutInSeconds != 0) { cmd.CommandTimeout = BulkInsertBatchTimeoutInSeconds; } string commandPrefix = string.Format("INSERT INTO {0}({1}) VALUES ", TargetTable.GetFullyQualifiedName(), string.Join(",", matchedColumns.Values.Select(c => "`" + c.GetRuntimeName() + "`"))); StringBuilder sb = new StringBuilder(); int row = 0; foreach (DataRow dr in dt.Rows) { sb.Append('('); DataRow dr1 = dr; sb.Append(string.Join(",", matchedColumns.Keys.Select(k => ConstructIndividualValue(matchedColumns[k].DataType.SQLType, dr1[k])))); sb.AppendLine("),"); row++; //don't let command get too long if (row % BulkInsertRowsPerNetworkPacket == 0) { cmd.CommandText = commandPrefix + sb.ToString().TrimEnd(',', '\r', '\n'); affected += cmd.ExecuteNonQuery(); sb.Clear(); } } //send final batch if (sb.Length > 0) { cmd.CommandText = commandPrefix + sb.ToString().TrimEnd(',', '\r', '\n'); affected += cmd.ExecuteNonQuery(); sb.Clear(); } } return(affected); }
private void DataRangeStartInit(TargetTable lottoType) { //StuGLSearch searchTemp = (StuGLSearch)Session["SearchOption"]; using DataTable dtDataRangeEnd = GetDataRangeEndDT(lottoType); cmbDataRangeEnd.DataSource = dtDataRangeEnd.DefaultView; cmbDataRangeEnd.DataValueField = "lngTotalSN"; cmbDataRangeEnd.DataTextField = "lngDateSN"; cmbDataRangeEnd.SelectedIndex = 0; cmbDataRangeEnd.DataBind(); //searchTemp.LngDataStart = long.Parse(dtDataRangeEnd.Rows[dtDataRangeEnd.Rows.Count - 1]["lngTotalSN"].ToString(), InvariantCulture); //searchTemp.LngDataEnd = long.Parse(dtDataRangeEnd.Rows[0]["lngTotalSN"].ToString(), InvariantCulture); //searchTemp.LngTotalSN = long.Parse(dtDataRangeEnd.Rows[0]["lngTotalSN"].ToString(), InvariantCulture); //Session["SearchOption"] = searchTemp; }
/// <summary> /// Constructs a DbDataJoiner instance /// </summary> /// <param name="joinOn">Property path from the root object down to the lookup key</param> /// <param name="dimTableTarget">Table information of the remote dimension table</param> /// <param name="option">Option to use for this dataflow</param> /// <param name="batchSize">The batch size for a batched remote look up</param> /// <param name="cacheSize">The local cache item count (part of the remote table)</param> public DbDataJoiner(Expression <Func <TIn, TLookupKey> > joinOn, TargetTable dimTableTarget, DataflowOptions option, int batchSize = 8 * 1024, int cacheSize = 1024 * 1024) : base(option) { m_dimTableTarget = dimTableTarget; m_batchSize = batchSize; m_batcher = new BatchBlock <TIn>(batchSize, option.ToGroupingBlockOption()).ToDataflow(option); m_batcher.Name = "Batcher"; m_lookupNode = new TransformManyDataflow <JoinBatch <TIn>, TIn>(this.JoinBatch, option); m_lookupNode.Name = "LookupNode"; m_typeAccessor = TypeAccessorManager <TIn> .GetAccessorForTable(dimTableTarget); m_keyComparer = typeof(TLookupKey) == typeof(byte[]) ? (IEqualityComparer <TLookupKey>)((object)new ByteArrayEqualityComparer()) : EqualityComparer <TLookupKey> .Default; m_rowCache = new RowCache <TLookupKey>(cacheSize, m_keyComparer); m_logger = Utils.GetNamespaceLogger(); m_joinOnMapping = m_typeAccessor.DbColumnMappings.First(m => m.Host.PropertyInfo == this.ExtractPropertyInfo(joinOn)); var transformer = new TransformBlock <TIn[], JoinBatch <TIn> >( array => new JoinBatch <TIn>(array, CacheLookupStrategy.RemoteLookup), option.ToExecutionBlockOption()).ToDataflow(option); transformer.Name = "ArrayToJoinBatchConverter"; m_batcher.LinkTo(transformer); transformer.LinkTo(m_lookupNode); RegisterChild(m_batcher); RegisterChild(transformer); RegisterChild(m_lookupNode); m_dimInserter = new DimTableInserter(this, dimTableTarget, joinOn, option) { Name = "DimInserter" }; var hb = new HeartbeatNode <JoinBatch <TIn> >(option); m_dimInserter.RegisterDependency(m_lookupNode); m_dimInserter.LinkTo(hb); hb.LinkTo(m_lookupNode); RegisterChild(m_dimInserter); RegisterChild(hb); RegisterChildRing(transformer.CompletionTask, m_lookupNode, m_dimInserter, hb); }
public static bool AddExtensionObject(int targetId, TargetTable table, string name, string value) { bool retVal = false; if (table != null) { using (VpnManagerEntities entities = new VpnManagerEntities()) { var checkedTargetId = 0; var targetTable = string.Empty; switch (table) { case TargetTable.VpnType: checkedTargetId = (from vpn in entities.VpnType where vpn.Id == targetId select vpn.Id).FirstOrDefault(); targetTable = TargetTable.VpnType.ToString(); break; case TargetTable.Machine: checkedTargetId = (from machine in entities.Machine where machine.Id == targetId select machine.Id).FirstOrDefault(); targetTable = TargetTable.Machine.ToString(); break; case TargetTable.Customer: checkedTargetId = (from customer in entities.Customer where customer.Id == targetId select customer.Id).FirstOrDefault(); targetTable = TargetTable.Customer.ToString(); break; } if (checkedTargetId != 0) { ExtensionObjects eObj = new ExtensionObjects(); eObj.Name = name; eObj.Value = value; eObj.IdTargetElement = targetId; eObj.TargetTableName = targetTable; entities.ExtensionObjects.Add(eObj); } retVal = entities.SaveChanges() > 0; } } return(retVal); }
public override int UploadImpl(DataTable dt) { var con = (NpgsqlConnection)Connection.Connection; var matchedColumns = GetMapping(dt.Columns.Cast <DataColumn>()); //see https://www.npgsql.org/doc/copy.html StringBuilder sb = new StringBuilder(); sb.Append("COPY "); sb.Append(TargetTable.GetFullyQualifiedName()); sb.Append(" ("); sb.Append(string.Join(",", matchedColumns.Values.Select(v => v.GetWrappedName()))); sb.Append(")"); sb.Append(" FROM STDIN (FORMAT BINARY)"); var tt = new PostgreSqlTypeTranslater(); var dataColumns = matchedColumns.Keys.ToArray(); var types = matchedColumns.Keys.Select(v => tt.GetNpgsqlDbTypeForCSharpType(v.DataType)).ToArray(); using (var import = con.BeginBinaryImport(sb.ToString())) { foreach (DataRow r in dt.Rows) { import.StartRow(); for (var index = 0; index < dataColumns.Length; index++) { DataColumn dc = dataColumns[index]; if (r[dc] == DBNull.Value) { import.WriteNull(); } else { import.Write(r[dc], types[index]); } } } import.Complete(); } return(dt.Rows.Count); }
public static bool DeleteExtensionObject(int targetId, TargetTable table, string name) { bool retVal = false; using (VpnManagerEntities entities = new VpnManagerEntities()) { ExtensionObjects eobj = (from extObj in entities.ExtensionObjects where extObj.TargetTableName == table.ToString() && extObj.IdTargetElement == targetId && extObj.Name == name select extObj).FirstOrDefault(); if (eobj != null) { entities.ExtensionObjects.Remove(eobj); retVal = entities.SaveChanges() > 0; } } return(retVal); }
public void TestWriteBulkSameRowKeys() { using (var dl = new DisposableList()) { var table = new TableWrapper(TestHelpers.GenUnique(TestConfig.TableName), TestConfig.PrimaryStorageAccount, true); dl.Add(table.Delete); List <DynamicTableEntity> entities = new List <DynamicTableEntity>(); for (int i = 0; i < 350; ++i) { var entity = TestHelpers.CreateStarEntity("same"); entities.Add(TableConvert.ToDynamicTableEntity(entity)); } TargetTable ttable = new TargetTable(table); Assert.Throws <ArgumentException>(() => ttable.Write(entities)); } }
public void TestETagViolation412WithSuccessfulRetry() { using (var dl = new DisposableList()) { var table = new TableWrapper(TestHelpers.GenUnique(TestConfig.TableName), TestConfig.PrimaryStorageAccount, true); dl.Add(table.Delete); var entity1 = TestHelpers.CreateStarEntity(); entity1.Version = 1; var entity2 = TestHelpers.CreateStarEntity(); entity2.Version = 2; entity2.Name = "Entity Version 2 name"; var entity3 = TestHelpers.CreateStarEntity(); entity3.Version = 3; entity3.Name = "Entity Version 3 name"; TargetTable ttable = new TargetTable(table); XTableResult result; StarEntity rentity; result = ttable.Write(TableConvert.ToDynamicTableEntity(entity1)); Assert.AreEqual(false, result.Discarded); // Test ETag violation as HTTP code 412 int retryAttempt = 0; result = ttable.Write( TableConvert.ToDynamicTableEntity(entity3), () => { if (retryAttempt++ == 0) { result = ttable.Write(TableConvert.ToDynamicTableEntity(entity2)); Assert.IsFalse(result.Discarded); rentity = table.ReadEntity <StarEntity>(entity2.PartitionKey, entity2.RowKey); TestHelpers.AssertEqualStars(entity2, rentity); } } ); Assert.IsFalse(result.Discarded); rentity = table.ReadEntity <StarEntity>(entity3.PartitionKey, entity3.RowKey); TestHelpers.AssertEqualStars(entity3, rentity); } }
public static ExtensionObjectDTO GetExtensionObject(int targetId, TargetTable table, string name) { ExtensionObjectDTO retVal = null; string tabName = table.ToString(); using (VpnManagerEntities entities = new VpnManagerEntities()) { retVal = (from eobj in entities.ExtensionObjects where eobj.IdTargetElement == targetId && eobj.Name == name && eobj.TargetTableName == tabName select new ExtensionObjectDTO { Id = eobj.Id, Name = eobj.Name, Value = eobj.Value, TargetTable = (int)table, }).FirstOrDefault(); } return(retVal); }
public void TestWriteBulk() { using (var dl = new DisposableList()) { var table = new TableWrapper(TestHelpers.GenUnique(TestConfig.TableName), TestConfig.PrimaryStorageAccount, true); dl.Add(table.Delete); List <DynamicTableEntity> entities = new List <DynamicTableEntity>(); for (int i = 0; i < 350; ++i) { entities.Add( TableConvert.ToDynamicTableEntity( TestHelpers.CreateStarEntity(i.ToString()) )); } TargetTable ttable = new TargetTable(table); var results = ttable.Write(entities); Assert.AreEqual(entities.Count, results.Count); Assert.IsTrue(results.All(x => !x.Discarded)); } }
public DimTableInserter(DbDataJoiner <TIn, TLookupKey> host, TargetTable targetTable, Expression <Func <TIn, TLookupKey> > joinBy, DataflowOptions option) : base(targetTable, option, host.m_batchSize) { this.m_host = host; m_keyGetter = joinBy.Compile(); m_keyComparer = m_host.m_keyComparer; m_outputBuffer = new BufferBlock <JoinBatch <TIn> >(option.ToGroupingBlockOption()).ToDataflow(option); m_outputBuffer.Name = "OutputBuffer"; RegisterChild(m_outputBuffer); m_outputBuffer.RegisterDependency(m_actionBlock); m_tmpTargetTable = new TargetTable( targetTable.DestLabel, targetTable.ConnectionString, targetTable.TableName + "_tmp"); //create tmp table m_createTmpTable = string.Format( "if OBJECT_ID('{0}', 'U') is not null drop table {0};" + "select * into {0} from {1} where 0 = 1", this.m_tmpTargetTable.TableName, targetTable.TableName); //merge m_mergeTmpToDimTable = string.Format( "MERGE INTO {0} as TGT " + "USING {1} as SRC on TGT.[{2}] = SRC.[{2}] " + "WHEN MATCHED THEN UPDATE SET TGT.[{2}] = TGT.[{2}] " + "WHEN NOT MATCHED THEN INSERT {3} VALUES {4} " + "OUTPUT inserted.[{5}], inserted.[{2}] ;", targetTable.TableName, this.m_tmpTargetTable.TableName, m_host.m_joinOnMapping.DestColumnName, Utils.FlattenColumnNames(m_typeAccessor.SchemaTable.Columns, ""), Utils.FlattenColumnNames(m_typeAccessor.SchemaTable.Columns, "SRC"), Utils.GetAutoIncrementColumn(m_typeAccessor.SchemaTable.Columns) ); }
internal void AddKey(Document document) { Keys.Add(TargetTable.MakeKey(document)); }
/// <summary> /// Add a single item to get, identified by its key. /// </summary> /// <param name="key">Key of the item to get.</param> public void AddKey(IDictionary <string, DynamoDBEntry> key) { Keys.Add(TargetTable.MakeKey(key)); }
/// <summary> /// Add a single item to get, identified by its hash-and-range primary key. /// </summary> /// <param name="hashKey">Hash key element of the item to get.</param> /// <param name="rangeKey">Range key element of the item to get.</param> public void AddKey(Primitive hashKey, Primitive rangeKey) { Keys.Add(TargetTable.MakeKey(hashKey, rangeKey)); }
/// <summary> /// Add a single item to delete, identified by a Document object. /// </summary> /// <param name="document">Document representing the item to be deleted.</param> public void AddItemToDelete(Document document) { AddKeyToDelete(TargetTable.MakeKey(document)); }
/// <summary> /// Add a single item to delete, identified by its key. /// </summary> /// <param name="key">Key of the item to delete.</param> public void AddKeyToDelete(IDictionary <string, DynamoDBEntry> key) { AddKeyToDelete(TargetTable.MakeKey(key)); }
/// <summary> /// Add a single item to delete, identified by its hash-and-range primary key. /// </summary> /// <param name="hashKey">Hash key element of the item to delete.</param> /// <param name="rangeKey">Range key element of the item to delete.</param> public void AddKeyToDelete(Primitive hashKey, Primitive rangeKey) { AddKeyToDelete(TargetTable.MakeKey(hashKey, rangeKey)); }