IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new[]
{
new SqlMetaData("ID", SqlDbType.VarChar, 36),
//new SqlMetaData("WORKORDERID", SqlDbType.VarChar, 36),
//new SqlMetaData("LOCATIONID", SqlDbType.VarChar, 6),
new SqlMetaData("STARTDATE", SqlDbType.DateTime),
new SqlMetaData("ENDDATE", SqlDbType.DateTime),
new SqlMetaData("ASSIGNMENTTYPE", SqlDbType.Int),
new SqlMetaData("ASSIGNMENTSTATUS", SqlDbType.Int),
//new SqlMetaData("DESCRIPTION", SqlDbType.VarChar, 200),
//new SqlMetaData("PRIORITY", SqlDbType.Int),
//new SqlMetaData("REGISTERDATE", SqlDbType.DateTime),
//new SqlMetaData("ESTIMATEDSTARTDATE", SqlDbType.DateTime),
//new SqlMetaData("ESTIMATEDENDDATE", SqlDbType.DateTime),
});
foreach (var activity in this)
{
sdr.SetString(0, activity.Id);
sdr.SetDateTime(1, activity.StartDate);
sdr.SetDateTime(2, activity.EndDate);
sdr.SetInt32(3, (int)activity.AssignmentType);
sdr.SetInt32(4, (int)activity.Status);
//sdr.SetString(1, activity.WorkOrderId);
//sdr.SetString(2, activity.Location.Id);
//sdr.SetString(5, activity.Description);
//sdr.SetDateTime(6, activity.RequestDate);
//sdr.SetDateTime(7, activity.EstimatedStartDate);
//sdr.SetDateTime(8, activity.EstimatedEndDate);
yield return(sdr);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("DireccionesAnexasId", SqlDbType.Int),
new SqlMetaData("EmpresaId", SqlDbType.Int),
new SqlMetaData("DepartamentoId", SqlDbType.Int),
new SqlMetaData("ProvinciaId", SqlDbType.Int),
new SqlMetaData("DistritoId", SqlDbType.Int),
new SqlMetaData("Direccion", SqlDbType.NVarChar, 100),
new SqlMetaData("Riesgo", SqlDbType.Bit),
new SqlMetaData("CreatedDate", SqlDbType.DateTime),
new SqlMetaData("CreatedBy", SqlDbType.Int),
new SqlMetaData("LastUpdateDate", SqlDbType.DateTime),
new SqlMetaData("LastUpdateBy", SqlDbType.Int),
new SqlMetaData("Estado", SqlDbType.Int)
);
foreach (clsAddressesRisk data in this)
{
ret.SetInt32(0, data.ID);
ret.SetInt32(1, data.EmpresaId);
ret.SetInt32(2, data.DepartamentoId);
ret.SetInt32(3, data.ProvinciaId);
ret.SetInt32(4, data.DistritoId);
ret.SetString(5, data.Direccion);
ret.SetBoolean(6, data.Riesgo);
ret.SetDateTime(7, data.CreationDate);
ret.SetInt32(8, data.Createdby);
ret.SetDateTime(9, data.UpdateDate);
ret.SetInt32(10, data.Updatedby);
ret.SetInt32(11, data.Estado);
yield return(ret);
}
}
public List <SqlDataRecord> BuildBaselines(List <ArtBaselineModel> baselines, int siteId)
{
var records = new List <SqlDataRecord>();
var errors = new List <ArtBaselineModel>();
SqlMetaData[] sqlMetaData = new SqlMetaData[8];
sqlMetaData[0] = new SqlMetaData("PatientIdentifier", SqlDbType.NVarChar, 450);
sqlMetaData[1] = new SqlMetaData("IdNo", SqlDbType.Int);
sqlMetaData[2] = new SqlMetaData("HivConfirmationDate", SqlDbType.DateTime2);
sqlMetaData[3] = new SqlMetaData("EnrolmentDate", SqlDbType.DateTime2);
sqlMetaData[4] = new SqlMetaData("ArtDate", SqlDbType.DateTime2);
sqlMetaData[5] = new SqlMetaData("DispositionDate", SqlDbType.NVarChar, 450);
sqlMetaData[6] = new SqlMetaData("DispositionCode", SqlDbType.NVarChar, 50);
sqlMetaData[7] = new SqlMetaData("FacilityId", SqlDbType.Int);
var duplicateBaselines = baselines.GroupBy(x => x.PatientIdentifier).Where(x => x.Count() > 1).Select(x => x.Key).ToList();
baselines.ForEach(p =>
{
var row = new SqlDataRecord(sqlMetaData);
row.SetString(0, p.PatientIdentifier);
row.SetInt32(1, p.IdNo);
row.SetDateTime(2, p.HivConfirmationDate != null ? p.HivConfirmationDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetDateTime(3, p.EnrolmentDate != null ? p.EnrolmentDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetDateTime(4, p.ArtDate != null ? p.ArtDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetString(5, !string.IsNullOrEmpty(p.DispositionDate)? p.DispositionDate : string.Empty);
row.SetString(6, !string.IsNullOrEmpty(p.DispositionCode) ? p.DispositionCode : string.Empty);
row.SetInt32(7, siteId);
records.Add(row);
});
return(records);
}
public SqlDataRecord ToSqlDataRecord()
{
var record = new SqlDataRecord(myRecordSchema);
record.SetString(0, A);
record.SetDateTime(1, B);
record.SetDateTime(2, C);
return(record);
}
public SqlDataRecord ToSqlDataRecord()
{
var record = new SqlDataRecord(myRecordSchema);
record.SetString(0, ID);
record.SetDateTime(1, LastUpdated);
record.SetDateTime(2, LastPulled);
return(record);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new[]
{
new SqlMetaData("ID", SqlDbType.VarChar, 36),
new SqlMetaData("ACTIVITYID", SqlDbType.VarChar, 36),
new SqlMetaData("ACTIVITYSTATE", SqlDbType.Int),
new SqlMetaData("LATITUDE", SqlDbType.Float),
new SqlMetaData("LONGITUDE", SqlDbType.Float),
new SqlMetaData("TRACEDATE", SqlDbType.DateTime),
}
);
foreach (var trace in this)
{
sdr.SetString(0, trace.Id);
sdr.SetString(1, trace.ActivityId);
sdr.SetInt32(2, (int)trace.ActivityState);
sdr.SetDouble(3, trace.Latitude);
sdr.SetDouble(4, trace.Longitude);
sdr.SetDateTime(5, trace.TraceDate);
yield return(sdr);
}
}
public static void PushLeaseBlobResponse(Responses.LeaseBlobResponse lbr)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("LeaseId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("LeaseBreakTimeSeconds", System.Data.SqlDbType.Int),
new SqlMetaData("RequestId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 4000)
});
if (lbr.LeaseId.HasValue)
{
record.SetGuid(0, lbr.LeaseId.Value);
}
record.SetDateTime(1, lbr.Date);
if (lbr.LeaseTimeSeconds.HasValue)
{
record.SetInt32(2, lbr.LeaseTimeSeconds.Value);
}
record.SetGuid(3, lbr.RequestID);
record.SetString(4, lbr.Version);
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <Observation> observations)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("Province", SqlDbType.NVarChar, 100),
new SqlMetaData("Country", SqlDbType.NVarChar, 100),
new SqlMetaData("Timestamp", SqlDbType.DateTime2),
new SqlMetaData("Confirmed", SqlDbType.Int),
new SqlMetaData("Deaths", SqlDbType.Int),
new SqlMetaData("Recovered", SqlDbType.Int),
new SqlMetaData("Lat", SqlDbType.Real),
new SqlMetaData("Lon", SqlDbType.Real)
);
// Now yield the Measurements in the Data Record:
foreach (var observation in observations)
{
sdr.SetString(0, observation.Province);
sdr.SetString(1, observation.Country);
sdr.SetDateTime(2, observation.Timestamp);
sdr.SetInt32(3, observation.Confirmed);
sdr.SetInt32(4, observation.Deaths);
sdr.SetInt32(5, observation.Recovered);
sdr.SetFloat(6, (float)observation.Lat);
sdr.SetFloat(7, (float)observation.Lon);
yield return(sdr);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new[]
{
new SqlMetaData("STRID", SqlDbType.NVarChar, 36),
new SqlMetaData("STRIDASSIGNMENT", SqlDbType.VarChar, 36),
new SqlMetaData("STRIDUSER", SqlDbType.VarChar, 36),
new SqlMetaData("STRIDDOCUMENT", SqlDbType.NVarChar, 36),
new SqlMetaData("STRIDACTIVITY", SqlDbType.NVarChar, 36),
new SqlMetaData("INTVALUE", SqlDbType.Int),
new SqlMetaData("STRTEXT", SqlDbType.NText),
new SqlMetaData("DTTDATE", SqlDbType.DateTime),
new SqlMetaData("STRCREATEDBY", SqlDbType.NVarChar, 100),
}
);
foreach (var usrRow in this)
{
sdr.SetString(0, usrRow.Id);
sdr.SetString(1, usrRow.AssignmentId);
sdr.SetString(2, usrRow.UserId);
sdr.SetString(3, usrRow.DocumentId);
sdr.SetString(4, usrRow.ActivityId);
sdr.SetInt32(5, usrRow.ActivityValue);
SqlDataRecordExtensions.SetNullableString(sdr, 6, usrRow.Text);
//sdr.SetString(6, usrRow.Text);
sdr.SetDateTime(7, usrRow.Date);
sdr.SetString(8, usrRow.CreatedBy);
yield return(sdr);
}
}
/// <summary>
/// Set property value to sql record.
/// </summary>
/// <param name="dataRecord">
/// Instance of sql data record.
/// </param>
/// <param name="databaseType">
/// Type of database column.
/// </param>
/// <param name="propValue">
/// Value to be inserted.
/// </param>
/// <param name="ordinal">
/// The zero based ordinal of the data record column.
/// </param>
private void SetDataRecordValue(SqlDataRecord dataRecord, DbType databaseType, object propValue, int ordinal)
{
switch (databaseType)
{
case DbType.String:
dataRecord.SetString(ordinal, propValue.ToString());
break;
case DbType.Int32:
dataRecord.SetInt32(ordinal, Convert.ToInt32(propValue));
break;
case DbType.DateTime:
dataRecord.SetDateTime(ordinal, Convert.ToDateTime(propValue));
break;
case DbType.Boolean:
dataRecord.SetBoolean(ordinal, Convert.ToBoolean(propValue));
break;
default:
dataRecord.SetString(ordinal, propValue.ToString());
break;
}
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <LocalWeatherData> localWeatherDataList)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("StationIdentifier", SqlDbType.NVarChar, 5),
new SqlMetaData("Timestamp", SqlDbType.DateTime2),
new SqlMetaData("QualityCode", SqlDbType.TinyInt),
new SqlMetaData("StationPressure", SqlDbType.Real),
new SqlMetaData("AirTemperatureAt2m", SqlDbType.Real),
new SqlMetaData("AirTemperatureAt5cm", SqlDbType.Real),
new SqlMetaData("RelativeHumidity", SqlDbType.Real),
new SqlMetaData("DewPointTemperatureAt2m", SqlDbType.Real)
);
// Now yield the Measurements in the Data Record:
foreach (var localWeatherData in localWeatherDataList)
{
sdr.SetString(0, localWeatherData.StationIdentifier);
sdr.SetDateTime(1, localWeatherData.TimeStamp);
sdr.SetByte(2, localWeatherData.QualityCode);
sdr.SetNullableFloat(3, localWeatherData.StationPressure);
sdr.SetNullableFloat(4, localWeatherData.AirTemperatureAt2m);
sdr.SetNullableFloat(5, localWeatherData.AirTemperatureAt5cm);
sdr.SetNullableFloat(6, localWeatherData.RelativeHumidity);
sdr.SetNullableFloat(7, localWeatherData.DewPointTemperatureAt2m);
yield return(sdr);
}
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <Station> stations)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("Identifier", SqlDbType.NVarChar, 5),
new SqlMetaData("Name", SqlDbType.NVarChar, 255),
new SqlMetaData("StartDate", SqlDbType.DateTime2),
new SqlMetaData("EndDate", SqlDbType.DateTime2),
new SqlMetaData("StationHeight", SqlDbType.SmallInt),
new SqlMetaData("State", SqlDbType.NVarChar, 255),
new SqlMetaData("Latitude", SqlDbType.Real),
new SqlMetaData("Longitude", SqlDbType.Real)
);
// Now yield the Measurements in the Data Record:
foreach (var station in stations)
{
sdr.SetString(0, station.Identifier);
sdr.SetString(1, station.Name);
sdr.SetDateTime(2, station.StartDate);
sdr.SetNullableDateTime(3, station.EndDate);
sdr.SetInt16(4, station.StationHeight);
sdr.SetString(5, station.State);
sdr.SetFloat(6, station.Latitude);
sdr.SetFloat(7, station.Longitude);
yield return(sdr);
}
}
private static void ReturnSalesProcessedRecords(List <Total> _totalRevenueList)
{
SqlPipe pipe = SqlContext.Pipe;
SqlMetaData[] cols = new SqlMetaData[4];
cols[0] = new SqlMetaData("id", SqlDbType.Int);
cols[1] = new SqlMetaData("TotalRevenue", SqlDbType.Decimal, 38, 4);
cols[2] = new SqlMetaData("DatetimeStamp", SqlDbType.DateTime);
cols[3] = new SqlMetaData("TotalRevenueNew", SqlDbType.Decimal, 38, 4);
SqlDataRecord row = new SqlDataRecord(cols);
pipe.SendResultsStart(row);
//TODO: Sort the list once more time before projecting them into a table
foreach (var _totalRevenueItem in _totalRevenueList)
{
row.SetInt32(0, _totalRevenueItem.Id);
row.SetDecimal(1, _totalRevenueItem.TotalRevenue);
row.SetDateTime(2, _totalRevenueItem.DatetimeStamp);
row.SetDecimal(3, _totalRevenueItem.TotalRevenueNew);
pipe.SendResultsRow(row);
}
pipe.SendResultsEnd();
}
public static void CallQuoteService(string ticker, string quoteType, DateTime?date)
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Ticker", SqlDbType.VarChar, 20),
new SqlMetaData("Date", SqlDbType.DateTime, -1),
new SqlMetaData("Open", SqlDbType.Decimal, -1),
new SqlMetaData("Close", SqlDbType.Decimal, -1),
new SqlMetaData("High", SqlDbType.Decimal, -1),
new SqlMetaData("Low", SqlDbType.Decimal, -1),
new SqlMetaData("Wap", SqlDbType.Decimal, -1),
new SqlMetaData("Volume", SqlDbType.BigInt, -1),
new SqlMetaData("ErrorMessage", SqlDbType.VarChar, -1)
);
try
{
SqlPipe sqlPip = SqlContext.Pipe;
var result = ClientUtilities.GetQuote(ticker, quoteType, date.Value);
record.SetString(0, result.Ticker);
record.SetDateTime(1, result.Date);
record.SetDecimal(2, (decimal)result.Open);
record.SetDecimal(3, (decimal)result.Close);
record.SetDecimal(4, (decimal)result.High);
record.SetDecimal(5, (decimal)result.Low);
record.SetDecimal(6, (decimal)result.Wap);
record.SetInt64(7, result.Volume);
record.SetString(8, result.ErrorMessage);
SqlContext.Pipe.Send(record);
}
catch (Exception ex)
{
record.SetString(8, ex.Message);
SqlContext.Pipe.Send(record);
}
}
public static void CreateNewRecord()
{
// Variables.
SqlMetaData column1Info;
SqlMetaData column2Info;
SqlMetaData column3Info;
SqlDataRecord record;
// Create the column metadata.
column1Info = new SqlMetaData("Column1", SqlDbType.NVarChar, 12);
column2Info = new SqlMetaData("Column2", SqlDbType.Int);
column3Info = new SqlMetaData("Column3", SqlDbType.DateTime);
// Create a new record with the column metadata.
record = new SqlDataRecord(new SqlMetaData[] { column1Info,
column2Info,
column3Info });
// Set the record fields.
record.SetString(0, "Hello World!");
record.SetInt32(1, 42);
record.SetDateTime(2, DateTime.Now);
// Send the record to the calling program.
SqlContext.Pipe.Send(record);
}
static SqlDataRecord PopulateTaskMessageRecord(TaskMessage msg, SqlDataRecord record)
{
record.SetSqlString(ColumnOrdinals.InstanceID, msg.OrchestrationInstance.InstanceId);
record.SetSqlString(ColumnOrdinals.ExecutionID, msg.OrchestrationInstance.ExecutionId);
record.SetSqlString(ColumnOrdinals.Name, SqlUtils.GetName(msg.Event));
record.SetSqlString(ColumnOrdinals.EventType, msg.Event.EventType.ToString());
record.SetSqlInt32(ColumnOrdinals.TaskID, SqlUtils.GetTaskId(msg.Event));
record.SetDateTime(ColumnOrdinals.VisibleTime, SqlUtils.GetVisibleTime(msg.Event));
SqlString reasonText = SqlUtils.GetReason(msg.Event);
record.SetSqlString(ColumnOrdinals.Reason, reasonText);
SqlString payloadText = SqlUtils.GetPayloadText(msg.Event);
record.SetSqlString(ColumnOrdinals.PayloadText, payloadText);
// If the message contains a payload, then we generate a random payload ID for it.
record.SetSqlGuid(
ColumnOrdinals.PayloadId,
payloadText.IsNull && reasonText.IsNull ? SqlGuid.Null : new SqlGuid(Guid.NewGuid()));
// Optionally, the LockedBy and LockExpiration fields can be specified
// to pre-lock task work items for this particular node.
record.SetSqlString(ColumnOrdinals.Version, SqlUtils.GetVersion(msg.Event));
return(record);
}
static SqlDataRecord PopulateOrchestrationMessage(TaskMessage msg, SqlDataRecord record)
{
string instanceId = msg.OrchestrationInstance.InstanceId;
record.SetSqlString(ColumnOrdinals.InstanceID, instanceId);
record.SetSqlString(ColumnOrdinals.ExecutionID, msg.OrchestrationInstance.ExecutionId);
record.SetSqlInt64(ColumnOrdinals.SequenceNumber, msg.SequenceNumber);
record.SetSqlString(ColumnOrdinals.EventType, msg.Event.EventType.ToString());
record.SetSqlString(ColumnOrdinals.Name, SqlUtils.GetName(msg.Event));
record.SetSqlString(ColumnOrdinals.RuntimeStatus, SqlUtils.GetRuntimeStatus(msg.Event));
record.SetSqlInt32(ColumnOrdinals.TaskID, SqlUtils.GetTaskId(msg.Event));
record.SetDateTime(ColumnOrdinals.VisibleTime, SqlUtils.GetVisibleTime(msg.Event));
SqlString reason = SqlUtils.GetReason(msg.Event);
record.SetSqlString(ColumnOrdinals.Reason, reason);
SqlString payloadText = SqlUtils.GetPayloadText(msg.Event);
record.SetSqlString(ColumnOrdinals.PayloadText, payloadText);
// If the message contains a payload (including Reason data), then we generate a random payload ID for it.
// We could have done this in the stored procedure directly, but doing in in C# is quite a bit simpler.
record.SetSqlGuid(
ColumnOrdinals.PayloadId,
payloadText.IsNull && reason.IsNull ? SqlGuid.Null : new SqlGuid(Guid.NewGuid()));
record.SetSqlString(ColumnOrdinals.ParentInstanceID, SqlUtils.GetParentInstanceId(msg.Event));
record.SetSqlString(ColumnOrdinals.Version, SqlUtils.GetVersion(msg.Event));
return(record);
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <Flight> items)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("Year", SqlDbType.Int),
new SqlMetaData("Month", SqlDbType.Int),
new SqlMetaData("DayOfMonth", SqlDbType.Int),
new SqlMetaData("DayOfWeek", SqlDbType.Int),
new SqlMetaData("FlightDate", SqlDbType.DateTime2),
new SqlMetaData("UniqueCarrier", SqlDbType.NVarChar, 55),
new SqlMetaData("TailNumber", SqlDbType.NVarChar, 55),
new SqlMetaData("FlightNumber", SqlDbType.NVarChar, 55),
new SqlMetaData("OriginAirport", SqlDbType.NVarChar, 55),
new SqlMetaData("OriginState", SqlDbType.NVarChar, 55),
new SqlMetaData("DestinationAirport", SqlDbType.NVarChar, 55),
new SqlMetaData("DestinationState", SqlDbType.NVarChar, 55),
new SqlMetaData("DepartureDelay", SqlDbType.Int),
new SqlMetaData("TaxiOut", SqlDbType.Int),
new SqlMetaData("TaxiIn", SqlDbType.Int),
new SqlMetaData("ArrivalDelay", SqlDbType.Int),
new SqlMetaData("CancellationCode", SqlDbType.NVarChar, 55),
new SqlMetaData("CarrierDelay", SqlDbType.Int),
new SqlMetaData("WeatherDelay", SqlDbType.Int),
new SqlMetaData("NasDelay", SqlDbType.Int),
new SqlMetaData("SecurityDelay", SqlDbType.Int),
new SqlMetaData("LateAircraftDelay", SqlDbType.Int)
);
// Now yield the Measurements in the Data Record:
foreach (var item in items)
{
sdr.SetInt32(0, item.Year);
sdr.SetInt32(1, item.Month);
sdr.SetInt32(2, item.DayOfMonth);
sdr.SetInt32(3, item.DayOfWeek);
sdr.SetDateTime(4, item.FlightDate);
sdr.SetString(5, item.UniqueCarrier);
sdr.SetString(6, item.TailNumber);
sdr.SetString(7, item.FlightNumber);
sdr.SetString(8, item.OriginAirport);
sdr.SetString(9, item.OriginState);
sdr.SetString(10, item.DestinationAirport);
sdr.SetString(11, item.DestinationState);
sdr.SetNullableInt32(12, item.DepartureDelay);
sdr.SetNullableInt32(13, item.TaxiOut);
sdr.SetNullableInt32(14, item.TaxiIn);
sdr.SetNullableInt32(15, item.ArrivalDelay);
sdr.SetString(16, item.CancellationCode);
sdr.SetNullableInt32(17, item.CarrierDelay);
sdr.SetNullableInt32(18, item.WeatherDelay);
sdr.SetNullableInt32(19, item.NasDelay);
sdr.SetNullableInt32(20, item.SecurityDelay);
sdr.SetNullableInt32(21, item.LateAircraftDelay);
yield return(sdr);
}
}
private async Task StoreForAll(IEnumerable <ISerie <TKey, TEntry> > series)
{
await CreateTable().ConfigureAwait(false);
using (await _cc.WriteAsync().ConfigureAwait(false))
{
using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync().ConfigureAwait(false);
var keys = new HashSet <EntryKey <TKey> >();
List <SqlDataRecord> records = new List <SqlDataRecord>();
foreach (var serie in series)
{
var key = serie.GetKey();
SqlSerializer.Serialize <TKey, TEntry>(serie.GetEntries(), (entry, data) =>
{
var timestamp = entry.GetTimestamp();
var hashkey = new EntryKey <TKey>(key, timestamp);
if (!keys.Contains(hashkey))
{
SqlDataRecord record = new SqlDataRecord(Sql.InsertParameterMetadata);
record.SetString(0, _keyConverter.Convert(key));
record.SetDateTime(1, timestamp);
record.SetSqlBinary(2, new SqlBinary(data));
records.Add(record);
keys.Add(hashkey);
}
});
}
if (records.Count > 0)
{
using (var tx = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (var command = connection.CreateCommand())
{
command.CommandText = Sql.GetInsertProcedureName(_tableName);
command.CommandType = CommandType.StoredProcedure;
command.Transaction = tx;
var parameter = command.Parameters.AddWithValue("@Inserts", records);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = Sql.GetInsertParameterType(_tableName);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
tx.Commit();
}
}
}
}
}
public static void cp_Lease_Calc2()
{
SqlMetaData[] fields = new SqlMetaData[5];
fields[0] = new SqlMetaData("LeaseId", SqlDbType.Int);
fields[1] = new SqlMetaData("LeaseVendor", SqlDbType.NVarChar, 50);
fields[2] = new SqlMetaData("LeaseNumber", SqlDbType.NVarChar, 50);
fields[3] = new SqlMetaData("ContactDate", SqlDbType.DateTime);
fields[4] = new SqlMetaData("TotalAmount", SqlDbType.Money);
// calculate values
// set record
SqlDataRecord record = new SqlDataRecord(fields);
// start record set
SqlContext.Pipe.SendResultsStart(record);
//assemble first record
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);
// send row
SqlContext.Pipe.SendResultsRow(record);
// assemble second record
record.SetInt32(0, 1002);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7891");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 4000);
// send row
SqlContext.Pipe.SendResultsRow(record);
// send record set
SqlContext.Pipe.SendResultsEnd();
return;
}
static IEnumerable <SqlDataRecord> ToHistoryEventsParameter(
IEnumerable <HistoryEvent> historyEvents,
OrchestrationInstance instance,
int nextSequenceNumber,
EventPayloadMap eventPayloadMap)
{
var record = new SqlDataRecord(HistoryEventSchema);
foreach (HistoryEvent e in historyEvents)
{
record.SetSqlInt64(ColumnOrdinals.SequenceNumber, nextSequenceNumber++);
record.SetSqlString(ColumnOrdinals.InstanceID, instance.InstanceId);
record.SetSqlString(ColumnOrdinals.ExecutionID, instance.ExecutionId);
record.SetSqlString(ColumnOrdinals.EventType, e.EventType.ToString());
record.SetSqlString(ColumnOrdinals.Name, SqlUtils.GetName(e));
record.SetSqlString(ColumnOrdinals.RuntimeStatus, SqlUtils.GetRuntimeStatus(e));
record.SetSqlInt32(ColumnOrdinals.TaskID, SqlUtils.GetTaskId(e));
record.SetDateTime(ColumnOrdinals.Timestamp, e.Timestamp);
record.SetBoolean(ColumnOrdinals.IsPlayed, e.IsPlayed);
record.SetDateTime(ColumnOrdinals.VisibleTime, SqlUtils.GetVisibleTime(e));
if (eventPayloadMap.TryGetPayloadId(e, out Guid existingPayloadId))
{
// We already have a payload saved in the DB for this event. Send only the payload ID.
record.SetSqlString(ColumnOrdinals.Reason, SqlString.Null);
record.SetSqlString(ColumnOrdinals.PayloadText, SqlString.Null);
record.SetSqlGuid(ColumnOrdinals.PayloadID, existingPayloadId);
}
else
{
// This path is expected for ExecutionCompleted, possibly others?
SqlString reason = SqlUtils.GetReason(e);
record.SetSqlString(ColumnOrdinals.Reason, reason);
SqlString payload = SqlUtils.GetPayloadText(e);
record.SetSqlString(ColumnOrdinals.PayloadText, payload);
SqlGuid newPayloadId = reason.IsNull && payload.IsNull ? SqlGuid.Null : new SqlGuid(Guid.NewGuid());
record.SetSqlGuid(ColumnOrdinals.PayloadID, newPayloadId);
}
yield return(record);
}
}
public static void GetHotelStayIntervalCounts_Parallelizer(
SqlInt32 MinHotelId,
SqlInt32 MaxHotelId,
SqlInt32 NumThreads)
{
var qp =
new QueryParallelizer <ResultWithHotelId>(
@"SELECT
HotelId,
CheckInDate,
CheckOutDate
FROM dbo.HotelStays
WHERE
HotelId BETWEEN @minHotelId AND @maxHotelId
ORDER BY
HotelId,
CheckInDate",
"@minHotelId", "@maxHotelId",
MinHotelId.Value, MaxHotelId.Value,
NumThreads.Value,
processResults);
SqlDataRecord dr = new SqlDataRecord(
new SqlMetaData("HotelId", SqlDbType.Int),
new SqlMetaData("intervalStart", SqlDbType.Date),
new SqlMetaData("intervalEnd", SqlDbType.Date),
new SqlMetaData("numberOfRooms", SqlDbType.Int));
SqlContext.Pipe.SendResultsStart(dr);
foreach (ResultWithHotelId rh in qp.Process())
{
dr.SetInt32(0, rh.HotelId);
dr.SetDateTime(1, rh.Result.Start);
dr.SetDateTime(2, rh.Result.End);
dr.SetInt32(3, rh.Result.Count);
SqlContext.Pipe.SendResultsRow(dr);
}
SqlContext.Pipe.SendResultsEnd();
}
public SqlDataRecord ToSqlDataRecord()
{
var sdr = new SqlDataRecord(
new SqlMetaData("Name", SqlDbType.VarChar),
new SqlMetaData("Description", SqlDbType.VarChar),
new SqlMetaData("TaskStatusId", SqlDbType.Int),
new SqlMetaData("Created", SqlDbType.DateTime),
new SqlMetaData("CreatedBy", SqlDbType.VarChar),
new SqlMetaData("Updated", SqlDbType.DateTime),
new SqlMetaData("UpdatedBy", SqlDbType.VarChar)
);
sdr.SetString(0, Name);
sdr.SetString(1, Description);
if (TaskStatusId.HasValue)
{
sdr.SetInt32(2, TaskStatusId.GetValueOrDefault());
}
else
{
sdr.SetDBNull(2);
}
if (Created.HasValue)
{
sdr.SetDateTime(3, Created.GetValueOrDefault());
}
else
{
sdr.SetDBNull(3);
}
sdr.SetString(4, CreatedBy);
if (Updated.HasValue)
{
sdr.SetDateTime(5, Updated.GetValueOrDefault());
}
else
{
sdr.SetDBNull(5);
}
sdr.SetString(6, UpdatedBy);
return(sdr);
}
public override void Set(SqlDataRecord record, int ordinal, DateTime?value)
{
if (value.HasValue)
{
record.SetDateTime(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
public static void SetNullableDateTime(this SqlDataRecord rec, int index, DateTime?value)
{
if (value != null && value != DateTime.MinValue)
{
rec.SetDateTime(index, value.Value);
}
else
{
rec.SetDBNull(index);
}
}
private static void SetDateTimeToDataRecord(ref SqlDataRecord record, int index, DateTime?value)
{
if (value.HasValue)
{
record.SetDateTime(index, value.Value);
}
else
{
record.SetDBNull(index);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sqlRow = new SqlDataRecord(
new SqlMetaData("ADate", SqlDbType.DateTime),
new SqlMetaData("MachineNO", SqlDbType.Int),
new SqlMetaData("EmpNumber", SqlDbType.Int),
new SqlMetaData("VerifyMethod", SqlDbType.Int),
new SqlMetaData("PunchTime", SqlDbType.DateTime)
);
foreach (Logs log in this)
{
sqlRow.SetDateTime(0, log.ADate);
sqlRow.SetInt32(1, log.MachineNO);
sqlRow.SetInt32(2, log.EmpNumber);
sqlRow.SetInt32(3, log.VerifyMethod);
sqlRow.SetDateTime(4, log.PunchTime);
yield return(sqlRow);
}
}
public static void SetNullableDateTime(this SqlDataRecord sqlDataRecord, int ordinal, DateTime?value)
{
if (value.HasValue)
{
sqlDataRecord.SetDateTime(ordinal, value.Value);
}
else
{
sqlDataRecord.SetDBNull(ordinal);
}
}
internal override void SetDataRecordValue(SqlDataRecord record, int ordinal)
{
if (InputValue == null)
{
record.SetDBNull(ordinal);
}
else
{
record.SetDateTime(ordinal, InputValue.Value);
}
}
public static void SetDateTime(this SqlDataRecord record, int ordinal, DateTime?value)
{
if (value != null)
{
record.SetDateTime(ordinal, (DateTime)value);
}
else
{
record.SetDBNull(ordinal);
}
}