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);
}
}
//https://stackoverflow.com/questions/36725999/c-sharp-fastest-way-to-insert-data-into-sql-database
//for xml based deserialization into objects to pass in ienumerable
//https://stackoverflow.com/questions/4143421/fastest-way-to-serialize-and-deserialize-net-objects
//https://stackoverflow.com/questions/25770180/how-can-i-insert-10-million-records-in-the-shortest-time-possible
public IEnumerable <SqlDataRecord> GetSqlDataRecords()
{
SqlMetaData MetaDataCol1;
SqlMetaData MetaDataCol2;
SqlMetaData MetaDataCol3;
MetaDataCol1 = new SqlMetaData("stringTest", SqlDbType.NVarChar);
MetaDataCol2 = new SqlMetaData("intTest", SqlDbType.Int);
MetaDataCol3 = new SqlMetaData("doubleTest", SqlDbType.Decimal, 18, 8); // precision 18, 8 scale
SqlDataRecord DataRecord = new SqlDataRecord(new SqlMetaData[] { MetaDataCol1, MetaDataCol2, MetaDataCol3 });
var lines = File.ReadLines(_location + _filename);
foreach (var line in lines)
{
var data = line.Split('|');
DataRecord.SetString(0, data[2]);
DataRecord.SetInt32(1, Convert.ToInt32(data[0]));
DataRecord.SetDecimal(2, Convert.ToDecimal(data[1]));
yield return(DataRecord);
}
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
var items = new List <SqlDataRecord>();
foreach (var param in _transfers)
{
var rec = new SqlDataRecord(_transferIdMetaData, _senderAccountIdMetaData, _receiverAccountIdMetaData, _requiredPaymentId, _commitmentId, _amount, _type, _collectionPeriodName);
rec.SetInt64(0, param.TransferId);
rec.SetInt64(1, param.SenderAccountId);
rec.SetInt64(2, param.ReceiverAccountId);
rec.SetGuid(3, param.RequiredPaymentId);
rec.SetInt64(4, param.CommitmentId);
rec.SetDecimal(5, param.Amount);
rec.SetString(6, param.Type.ToString());
rec.SetString(7, param.CollectionPeriodName);
items.Add(rec);
}
var p = sqlCommand.Parameters.Add("@transfers", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "[Data_Load].[TransferEntity]";
p.Value = items;
}
private static IEnumerable <SqlDataRecord> BindDishRows(IEnumerable <DishEntity> dishesToUpdate)
{
foreach (var dish in dishesToUpdate)
{
SqlDataRecord record = new SqlDataRecord(typ_DishTable);
record.SetInt64(0, dish.DishId);
record.SetInt64(1, dish.ProviderId);
record.SetInt32(2, (int)dish.DishType);
record.SetString(3, dish.DishName);
record.SetString(4, dish.Description);
record.SetString(5, dish.Ingredients);
record.SetDecimal(6, dish.Price);
record.SetInt32(7, dish.WaitingTimeInMins);
if (!dish.ThumbNailPictureKey.HasValue)
{
record.SetDBNull(8);
}
else
{
record.SetGuid(8, dish.ThumbNailPictureKey.Value);
}
record.SetBoolean(9, dish.Available);
yield return(record);
}
}
/// <summary>
/// Sets the decimal.
/// </summary>
/// <param name="record">The record.</param>
/// <param name="fieldName">Name of the field.</param>
/// <param name="value">The value.</param>
public static void SetDecimal(this SqlDataRecord record, string fieldName, decimal?value)
{
int ordinal = GetOrdinal(record, fieldName);
if (value.HasValue)
{
record.SetDecimal(ordinal, value.Value);
}
}
public new IEnumerator <SqlDataRecord> GetEnumerator()
{
SqlDataRecord returnValue = new SqlDataRecord(
new SqlMetaData("ItemId", SqlDbType.Int),
new SqlMetaData("Quantity", SqlDbType.Int),
new SqlMetaData("PricePerItem", SqlDbType.Decimal),
new SqlMetaData("TotalPrice", SqlDbType.Decimal)
);
foreach (var item in _orderItemList)
{
returnValue.SetInt32(0, item.ItemId);
returnValue.SetInt32(1, item.Quantity);
returnValue.SetDecimal(2, item.PricePerItem);
returnValue.SetDecimal(3, item.TotalPrice);
yield return(returnValue);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sqlRow = new SqlDataRecord(new SqlMetaData("Habitacion", SqlDbType.Decimal));
foreach (Habitacionalities cust in this)
{
sqlRow.SetDecimal(0, cust.Habitacion);
yield return(sqlRow);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sqlRow = new SqlDataRecord(new SqlMetaData("Funcionalidades", SqlDbType.Decimal));
foreach (Funcionality cust in this)
{
sqlRow.SetDecimal(0, cust.Funcionalidades);
yield return(sqlRow);
}
}
public static void SetDecimal(this SqlDataRecord record, int index, decimal?value)
{
if (value == null)
{
record.SetDBNull(index);
}
else
{
record.SetDecimal(index, value ?? 0);
}
}
private static SqlDataRecord CreateBookRecipeIngredientRecord(EditIngredientModel ingredient)
{
var dataRecord = new SqlDataRecord(BookRecipeIngredientMetaData);
dataRecord.SetInt32(0, ingredient.IngredientNo.Value);
dataRecord.SetString(1, ingredient.Name);
dataRecord.SetDecimal(2, ingredient.Quantity.Value);
var quantityUnit = UnitKey.Parse(ingredient.QuantityUnit);
dataRecord.SetString(3, quantityUnit.UnitTypeCode);
dataRecord.SetString(4, quantityUnit.UnitCode);
dataRecord.SetDecimal(5, ingredient.Serving.Value);
var servingUnit = UnitKey.Parse(ingredient.ServingUnit);
dataRecord.SetString(6, servingUnit.UnitTypeCode);
dataRecord.SetString(7, servingUnit.UnitCode);
dataRecord.SetDecimal(8, ingredient.CaloriesPerServing.Value);
dataRecord.SetDecimal(9, ingredient.ProteinPerServing.Value);
return(dataRecord);
}
internal override void SetDataRecordValue(SqlDataRecord record, int ordinal)
{
if (InputValue == null)
{
record.SetDBNull(ordinal);
}
else
{
record.SetDecimal(ordinal, InputValue.Value);
}
}
public override void Set(SqlDataRecord record, int ordinal, decimal?value)
{
if (value.HasValue)
{
record.SetDecimal(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
public static void SetDecimal(this SqlDataRecord record, int ordinal, decimal?value)
{
if (value != null)
{
record.SetDecimal(ordinal, (decimal)value);
}
else
{
record.SetDBNull(ordinal);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var row = new SqlDataRecord(new SqlMetaData("value1", SqlDbType.Int), new SqlMetaData("value2", SqlDbType.Money));
foreach (Pair <int, decimal> kv in this)
{
row.SetInt32(0, kv.Item1);
row.SetDecimal(1, kv.Item2);
yield return(row);
}
}
private static IEnumerable <SqlDataRecord> BindOrderRows(IEnumerable <OrderEntity> ordersToUpdate)
{
foreach (var order in ordersToUpdate)
{
SqlDataRecord record = new SqlDataRecord(typ_OrderTable);
record.SetInt64(0, order.OrderId);
record.SetInt64(1, order.UserId);
record.SetDateTime(2, order.OrderDate);
record.SetDecimal(3, order.SubTotal);
if (order.Tax.HasValue)
{
record.SetDecimal(4, order.Tax.Value);
}
else
{
record.SetDBNull(4);
}
if (order.OtherCharges.HasValue)
{
record.SetDecimal(5, order.OtherCharges.Value);
}
else
{
record.SetDBNull(5);
}
if (!string.IsNullOrWhiteSpace(order.Notes))
{
record.SetString(6, order.Notes);
}
else
{
record.SetDBNull(6);
}
record.SetInt32(7, (int)order.Status);
yield return(record);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("ID2", SqlDbType.Int),
new SqlMetaData("QUANTITY", SqlDbType.Decimal),
new SqlMetaData("QUANTITY2", SqlDbType.Decimal),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
foreach (tBaseIdv3 data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.Id2);
ret.SetDecimal(2, data.Quantity);
ret.SetDecimal(3, data.Quantity2);
ret.SetInt16(4, data.Status);
ret.SetInt16(5, data.Action);
yield return(ret);
}
}
public override void Set(SqlDataRecord record, int ordinal, decimal?value)
{
EnsureArg.IsNotNull(record, nameof(record));
if (value.HasValue)
{
record.SetDecimal(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("DetalleVenta", SqlDbType.Int),
new SqlMetaData("IdProducto", SqlDbType.Int),
new SqlMetaData("IdVenta", SqlDbType.Int),
new SqlMetaData("Cantidad", SqlDbType.Int),
new SqlMetaData("PrecioUnitario", SqlDbType.Decimal),
new SqlMetaData("Igv", SqlDbType.Money),
new SqlMetaData("SubTotal", SqlDbType.Money)
);
foreach (DetalleVenta data in this)
{
ret.SetInt32(0, data.IdDetalleVenta);
ret.SetInt32(1, data.IdP);
ret.SetInt32(2, data.IdVenta);
ret.SetInt32(3, data.Cantidad);
ret.SetDecimal(4, data.PUnitario);
ret.SetDecimal(5, data.Igv);
ret.SetDecimal(6, data.SubTotal);
yield return(ret);
}
}
private static SqlDataRecord CreateFractalRecord(decimal?value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Decimal));
if (value.HasValue)
{
record.SetDecimal(0, value.Value);
}
else
{
record.SetDBNull(0);
}
return(record);
}
public static IEnumerable <SqlDataRecord> ToSqlDataRecords(List <int> filters)
{
var metaData = new[]
{
new SqlMetaData("product_id", SqlDbType.Int),
new SqlMetaData("price", SqlDbType.Decimal, 18, 9)
};
foreach (var filter in filters)
{
var record = new SqlDataRecord(metaData);
record.SetInt32(0, filter);
record.SetDecimal(1, 0);
yield return(record);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sqlRow = new SqlDataRecord(
new SqlMetaData("ItemId", SqlDbType.Int),
new SqlMetaData("Quantity", SqlDbType.Decimal),
new SqlMetaData("TranId", SqlDbType.VarChar, 50),
new SqlMetaData("TranDate", SqlDbType.Date));
foreach (ItemOpeningStockType cust in this)
{
sqlRow.SetSqlInt32(0, cust.ItemId);
sqlRow.SetDecimal(1, cust.Quantity);
sqlRow.SetString(2, cust.TranId);
sqlRow.SetDateTime(3, cust.TranDate);
yield return(sqlRow);
}
}
// Custom iterator method to support TVPs in SQL Server 2008
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("OrderId", SqlDbType.Int),
new SqlMetaData("LineNumber", SqlDbType.Int),
new SqlMetaData("ProductId", SqlDbType.Int),
new SqlMetaData("Quantity", SqlDbType.Int),
new SqlMetaData("Price", SqlDbType.Money));
foreach (OrderDetail od in this)
{
sdr.SetInt32(0, od.OrderId);
sdr.SetInt32(1, od.LineNumber);
sdr.SetInt32(2, od.ProductId);
sdr.SetInt32(3, od.Quantity);
sdr.SetDecimal(4, od.Price);
yield return(sdr);
}
}
public override void Set(SqlDataRecord record, int ordinal, decimal value)
{
record.SetDecimal(ordinal, value);
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
var items = new List <SqlDataRecord>();
foreach (var param in _parameters)
{
var rec = new SqlDataRecord(
_programmeTypeMetaData,
_standardCodeMetaData,
_frameworkCodeMetaData,
_pathwayCodeMetaData,
_ukprnMetaData,
_learnRefNumberMetaData,
_transferSenderAccountIdMetaData,
_employerAccountIdMetaData,
_paymentStatusMetaData,
_negotiatedPriceMetaData,
_startDateMetaData,
_endDateMetaData,
_effectiveFromMetaData,
_effectiveToMetaData,
_ulnMetaData);
if (param.ProgrammeType.HasValue)
{
rec.SetInt32(0, param.ProgrammeType.Value);
}
if (param.StandardCode.HasValue)
{
rec.SetInt64(1, param.StandardCode.Value);
}
if (param.FrameworkCode.HasValue)
{
rec.SetInt32(2, param.FrameworkCode.Value);
}
if (param.PathwayCode.HasValue)
{
rec.SetInt32(3, param.PathwayCode.Value);
}
rec.SetInt64(4, param.Ukprn);
rec.SetString(5, param.LearnerReferenceNumber);
if (param.TransferSenderAccountId.HasValue)
{
rec.SetInt64(6, param.TransferSenderAccountId.Value);
}
rec.SetInt64(7, param.EmployerAccountId);
rec.SetInt32(8, param.PaymentStatus);
rec.SetDecimal(9, param.NegotiatedPrice);
rec.SetDateTime(10, param.StartDate);
rec.SetDateTime(11, param.EndDate);
rec.SetDateTime(12, param.EffectiveFrom);
if (param.EffectiveTo != null)
{
rec.SetDateTime(13, param.EffectiveTo.Value);
}
rec.SetInt64(14, param.Uln);
items.Add(rec);
}
var p = sqlCommand.Parameters.Add("@commitments", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "[dbo].[CommitmentType]";
p.Value = items;
}
void SetValue(SqlDataRecord record, int position, Type type, object value)
{
switch (type.Name)
{
case "Int16":
record.SetInt16(position, (short)value);
break;
case "Int32":
record.SetInt32(position, (int)value);
break;
case "Int64":
record.SetInt64(position, (long)value);
break;
case "Boolean":
record.SetBoolean(position, (bool)value);
break;
case "Byte":
record.SetByte(position, (byte)value);
break;
case "Bytes[]":
record.SetBytes(position, 0, (byte[])value, 0, ((byte[])value).Length);
break;
case "Char":
record.SetChar(position, (char)value);
break;
case "Char[]":
record.SetChars(position, 0, (char[])value, 0, ((char[])value).Length);
break;
case "DateTime":
record.SetDateTime(position, (DateTime)value);
break;
case "Decimal":
record.SetDecimal(position, (decimal)value);
break;
case "Double":
record.SetDouble(position, (double)value);
break;
case "Guid":
record.SetGuid(position, (Guid)value);
break;
case "String":
record.SetSqlString(position, (string)value);
break;
default:
record.SetValue(position, value);
break;
}
}
public static void GetTable(string connectionString, string tableName)
{
var metaCount = 0;
var fieldNames = new List <string>();
//--use: "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;OLE DB Services=-4;" for SQL Compact 3.1
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;OLE DB Services=-4;" for SQL Compact 3.5 SP2
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;OLE DB Services=-4;" for SQL Compact 4.0
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
// determine the number of SqlMetadata parameters needed
using (var cmd = new OleDbCommand())
{
cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @p1 ORDER BY ORDINAL_POSITION";
cmd.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarWChar, 128));
cmd.Parameters[0].Value = tableName;
cmd.Connection = conn;
using (var rdr = cmd.ExecuteReader())
{
while (rdr != null && rdr.Read())
{
//if (SqlContext.Pipe != null) SqlContext.Pipe.Send(rdr[1].ToString());
if (rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
{
continue;
}
metaCount++;
fieldNames.Add("[" + rdr[0] + "]");
}
}
}
if (metaCount == 0)
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send("No data found, or table does not exist");
}
return;
}
//Get the meta data for the fields
var metadata = GetMetaData(metaCount, tableName, conn);
var record = new SqlDataRecord(metadata);
var fields = new System.Text.StringBuilder();
foreach (var field in fieldNames)
{
fields.Append(field);
fields.Append(", ");
}
fields.Remove(fields.Length - 2, 2);
using (var cmd = new OleDbCommand("SELECT " + fields + " FROM [" + tableName + "]", conn))
{
using (var rdr = cmd.ExecuteReader())
{
if (SqlContext.Pipe != null)
{
//SqlContext.Pipe.Send(cmd.CommandText);
SqlContext.Pipe.SendResultsStart(record);
while (rdr != null && rdr.Read())
{
for (var i = 0; i < rdr.FieldCount; i++)
{
if (rdr.IsDBNull(i))
{
record.SetDBNull(i);
}
else
{
if (metadata[i].SqlDbType == SqlDbType.Bit)
{
record.SetBoolean(i, rdr.GetBoolean(i));
}
if (metadata[i].SqlDbType == SqlDbType.TinyInt)
{
record.SetByte(i, rdr.GetByte(i));
}
if (metadata[i].SqlDbType == SqlDbType.SmallInt)
{
record.SetInt16(i, rdr.GetInt16(i));
}
if (metadata[i].SqlDbType == SqlDbType.Int)
{
record.SetInt32(i, rdr.GetInt32(i));
}
if (metadata[i].SqlDbType == SqlDbType.BigInt)
{
record.SetInt64(i, rdr.GetInt64(i));
}
if (metadata[i].SqlDbType == SqlDbType.NVarChar || metadata[i].SqlDbType == SqlDbType.NChar)
{
record.SetString(i, rdr.GetString(i));
}
if (metadata[i].SqlDbType == SqlDbType.UniqueIdentifier)
{
record.SetGuid(i, rdr.GetGuid(i));
}
if (metadata[i].SqlDbType == SqlDbType.Timestamp || metadata[i].SqlDbType == SqlDbType.Binary || metadata[i].SqlDbType == SqlDbType.VarBinary)
{
var tsbuffer = (byte[])rdr[i];
record.SetBytes(i, 0, tsbuffer, 0, tsbuffer.Length);
}
if (metadata[i].SqlDbType == SqlDbType.DateTime)
{
record.SetDateTime(i, rdr.GetDateTime(i));
}
if (metadata[i].SqlDbType == SqlDbType.Money || metadata[i].SqlDbType == SqlDbType.Decimal)
{
record.SetDecimal(i, rdr.GetDecimal(i));
}
if (metadata[i].SqlDbType == SqlDbType.Float)
{
record.SetDouble(i, rdr.GetDouble(i));
}
if (metadata[i].SqlDbType == SqlDbType.Real)
{
record.SetSqlSingle(i, Convert.ToSingle(rdr.GetValue(i)));
}
}
}
//Send the completed record..
SqlContext.Pipe.SendResultsRow(record);
}
if (rdr != null)
{
rdr.Close();
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
conn.Close();
}
}
public override void Set(SqlDataRecord record, int ordinal, decimal value)
{
EnsureArg.IsNotNull(record, nameof(record));
record.SetDecimal(ordinal, value);
}
