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);
}
}
/// <summary>
/// Sets the byte.
/// </summary>
/// <param name="record">The record.</param>
/// <param name="fieldName">Name of the field.</param>
/// <param name="value">The value.</param>
public static void SetByte(this SqlDataRecord record, string fieldName, byte?value)
{
int ordinal = GetOrdinal(record, fieldName);
if (value.HasValue)
{
record.SetByte(ordinal, value.Value);
}
}
public async Task AddTransactions(IEnumerable <ByteString> transactions)
{
using (SqlTransaction context = Connection.BeginTransaction(IsolationLevel.Snapshot))
{
foreach (ByteString rawTransaction in transactions)
{
byte[] rawTransactionBuffer = rawTransaction.ToByteArray();
Transaction transaction = MessageSerializer.DeserializeTransaction(rawTransaction);
byte[] transactionHash = MessageSerializer.ComputeHash(rawTransactionBuffer);
byte[] mutationHash = MessageSerializer.ComputeHash(transaction.Mutation.ToByteArray());
Mutation mutation = MessageSerializer.DeserializeMutation(transaction.Mutation);
IReadOnlyList <Record> conflicts = await ExecuteQuery <Record>(
"EXEC [Openchain].[AddTransaction] @instance, @transactionHash, @mutationHash, @rawData, @records;",
reader => mutation.Records.First(record => record.Key.Equals(new ByteString((byte[])reader[0]))),
new Dictionary <string, object>()
{
["instance"] = this.instanceId,
["transactionHash"] = transactionHash,
["mutationHash"] = mutationHash,
["rawData"] = rawTransactionBuffer,
["type:records"] = "Openchain.RecordMutationTable",
["records"] = mutation.Records.Select(record =>
{
SqlDataRecord result = new SqlDataRecord(recordMutationMetadata);
RecordKey key = ParseRecordKey(record.Key);
result.SetBytes(0, 0, record.Key.ToByteArray(), 0, record.Key.Value.Count);
if (record.Value == null)
{
result.SetDBNull(1);
}
else
{
result.SetBytes(1, 0, record.Value.ToByteArray(), 0, record.Value.Value.Count);
}
result.SetBytes(2, 0, record.Version.ToByteArray(), 0, record.Version.Value.Count);
result.SetString(3, key.Name);
result.SetByte(4, (byte)key.RecordType);
return(result);
}).ToList()
},
context);
if (conflicts.Count > 0)
{
throw new ConcurrentMutationException(conflicts[0]);
}
}
context.Commit();
}
}
public override void Set(SqlDataRecord record, int ordinal, byte?value)
{
if (value.HasValue)
{
record.SetByte(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
public static void SetByte(this SqlDataRecord record, int ordinal, byte?value)
{
if (value != null)
{
record.SetByte(ordinal, (byte)value);
}
else
{
record.SetDBNull(ordinal);
}
}
internal override void SetDataRecordValue(SqlDataRecord record, int ordinal)
{
if (InputValue == null)
{
record.SetDBNull(ordinal);
}
else
{
record.SetByte(ordinal, InputValue.Value);
}
}
public override void Set(SqlDataRecord record, int ordinal, byte?value)
{
EnsureArg.IsNotNull(record, nameof(record));
if (value.HasValue)
{
record.SetByte(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
private List <SqlDataRecord> MakeRecordKeyUDTs <TModel>(IList <TModel> records) where TModel : IKeyedModel <byte, int> //RecordKey is a SQL UDT.
{
var metaData = new SqlMetaData[] { new SqlMetaData("ShardId", System.Data.SqlDbType.TinyInt), new SqlMetaData("RecordId", System.Data.SqlDbType.Int) };
var result = new List <SqlDataRecord>();
foreach (var obj in records)
{
var cnt = new SqlDataRecord(metaData);
cnt.SetByte(0, obj.Key.ShardId);
cnt.SetInt32(1, obj.Key.RecordId);
result.Add(cnt);
}
return(result);
}
private List <SqlDataRecord> MakeRecordKeyUDTs(IList <ShardKey> records) //RecordKey is a SQL UDT.
{
var metaData = new SqlMetaData[] { new SqlMetaData("ShardId", System.Data.SqlDbType.TinyInt), new SqlMetaData("RecordId", System.Data.SqlDbType.Int) };
var result = new List <SqlDataRecord>();
foreach (var key in records)
{
var cnt = new SqlDataRecord(metaData);
cnt.SetByte(0, key.ShardId);
cnt.SetInt32(1, key.RecordId);
result.Add(cnt);
}
return(result);
}
public override void Set(SqlDataRecord record, int ordinal, byte value)
{
record.SetByte(ordinal, value);
}
public void SqlRecordFillTest()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.Bit),
new SqlMetaData("col2", SqlDbType.TinyInt),
new SqlMetaData("col3", SqlDbType.VarBinary, 1000),
new SqlMetaData("col4", SqlDbType.NVarChar, 1000),
new SqlMetaData("col5", SqlDbType.DateTime),
new SqlMetaData("col6", SqlDbType.Float),
new SqlMetaData("col7", SqlDbType.UniqueIdentifier),
new SqlMetaData("col8", SqlDbType.SmallInt),
new SqlMetaData("col9", SqlDbType.Int),
new SqlMetaData("col10", SqlDbType.BigInt),
new SqlMetaData("col11", SqlDbType.Real),
new SqlMetaData("col12", SqlDbType.Decimal),
new SqlMetaData("col13", SqlDbType.Money),
new SqlMetaData("col14", SqlDbType.Variant)
};
SqlDataRecord record = new SqlDataRecord(metaData);
for (int i = 0; i < record.FieldCount; i++)
{
Assert.Equal($"col{i + 1}", record.GetName(i));
}
record.SetBoolean(0, true);
Assert.Equal(true, record.GetBoolean(0));
record.SetByte(1, 1);
Assert.Equal(1, record.GetByte(1));
byte[] bb1 = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
byte[] bb2 = new byte[5];
record.SetSqlBinary(2, new SqlBinary(new byte[0]));
record.SetBytes(2, 0, bb1, 0, 3);
record.SetBytes(2, 2, bb1, 6, 3);
// Verify the length of the byte array
Assert.Equal(5, record.GetBytes(2, 0, bb2, 0, 5));
Assert.Equal(5, record.GetBytes(2, 0, null, 0, 0));
byte[] expected = new byte[] { 1, 2, 7, 8, 9 };
Assert.Equal <byte>(expected, bb2);
char[] cb1 = new char[] { 'a', 'b', 'c', 'd', 'e', 'f', 'g' };
char[] cb2 = new char[5];
record.SetChars(3, 0, cb1, 0, 3);
record.SetChars(3, 2, cb1, 4, 3);
char[] expectedValue = new char[] { 'a', 'b', 'e', 'f', 'g' };
Assert.Equal(expectedValue.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal <char>(expectedValue, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetString(3, "");
string xyz = "xyz";
record.SetString(3, "xyz");
Assert.Equal(xyz, record.GetString(3));
Assert.Equal(xyz.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal(xyz, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetChars(3, 2, cb1, 4, 3);
Assert.Equal(5, record.GetChars(3, 0, cb2, 0, 5));
string interleavedResult = "xyefg";
Assert.Equal(interleavedResult, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
Assert.Equal(interleavedResult, record.GetString(3));
record.SetSqlDateTime(4, SqlDateTime.MaxValue);
Assert.Equal(SqlDateTime.MaxValue, record.GetSqlDateTime(4));
record.SetSqlDouble(5, SqlDouble.MaxValue);
Assert.Equal(SqlDouble.MaxValue, record.GetSqlDouble(5));
SqlGuid guid = new SqlGuid("F9168C5E-CEB2-4faa-B6BF-329BF39FA1E4");
record.SetSqlGuid(6, guid);
Assert.Equal(guid, record.GetSqlGuid(6));
record.SetSqlInt16(7, SqlInt16.MaxValue);
Assert.Equal(SqlInt16.MaxValue, record.GetSqlInt16(7));
record.SetSqlInt32(8, SqlInt32.MaxValue);
Assert.Equal(SqlInt32.MaxValue, record.GetSqlInt32(8));
record.SetSqlInt64(9, SqlInt64.MaxValue);
Assert.Equal(SqlInt64.MaxValue, record.GetSqlInt64(9));
record.SetSqlSingle(10, SqlSingle.MinValue);
Assert.Equal(SqlSingle.MinValue, record.GetSqlSingle(10));
record.SetSqlDecimal(11, SqlDecimal.Null);
record.SetSqlDecimal(11, SqlDecimal.MaxValue);
Assert.Equal(SqlDecimal.MaxValue, record.GetSqlDecimal(11));
record.SetSqlMoney(12, SqlMoney.MaxValue);
Assert.Equal(SqlMoney.MaxValue, record.GetSqlMoney(12));
// Try adding different values to SqlVariant type
for (int i = 0; i < record.FieldCount - 1; ++i)
{
object valueToSet = record.GetSqlValue(i);
record.SetValue(record.FieldCount - 1, valueToSet);
object o = record.GetSqlValue(record.FieldCount - 1);
if (o is SqlBinary)
{
Assert.Equal <byte>(((SqlBinary)valueToSet).Value, ((SqlBinary)o).Value);
}
else
{
Assert.Equal(valueToSet, o);
}
record.SetDBNull(record.FieldCount - 1);
Assert.Equal(DBNull.Value, record.GetSqlValue(record.FieldCount - 1));
record.SetDBNull(i);
Assert.Equal(DBNull.Value, record.GetValue(i));
}
}
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, byte value)
{
EnsureArg.IsNotNull(record, nameof(record));
record.SetByte(ordinal, value);
}
/// <summary> Provides an enumerator for the list of PduPropertyDetails </summary>
/// <returns> IEnumerator SqlDataRecord </returns>
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord sqlDataRecord = new SqlDataRecord
(
new SqlMetaData("StartingBytePosition", SqlDbType.Int),
new SqlMetaData("PduDataTypeId", SqlDbType.TinyInt),
new SqlMetaData("DataBlock", SqlDbType.NVarChar, SqlMetaData.Max),
new SqlMetaData("PropertyName", SqlDbType.NVarChar, 50),
new SqlMetaData("Value", SqlDbType.NVarChar, 255)
);
foreach (PduPropertyDetail pduPropertyDetail in this)
{
sqlDataRecord.SetInt32(0, pduPropertyDetail.StartingBytePosition);
sqlDataRecord.SetByte(1, (byte)pduPropertyDetail.PduDataType);
if (pduPropertyDetail.DataBlock != null)
{
sqlDataRecord.SetValue(2, BitConverter.ToString(pduPropertyDetail.DataBlock).Replace("-", " "));
}
else
{
sqlDataRecord.SetValue(2, null);
}
sqlDataRecord.SetString(3, pduPropertyDetail.Name);
switch (pduPropertyDetail.PduDataType)
{
case PduDataTypes.Byte:
sqlDataRecord.SetString(4, pduPropertyDetail.ValueByte.ToString());
break;
case PduDataTypes.CString:
case PduDataTypes.EncodedString:
if (pduPropertyDetail.ValueString != null)
{
sqlDataRecord.SetString(4, pduPropertyDetail.ValueString);
}
else
{
sqlDataRecord.SetValue(4, null);
}
break;
case PduDataTypes.UInt:
sqlDataRecord.SetString(4, pduPropertyDetail.ValueUInt.ToString());
break;
case PduDataTypes.UShort:
sqlDataRecord.SetString(4, pduPropertyDetail.ValueUShort.ToString());
break;
case PduDataTypes.ByteArray:
sqlDataRecord.SetValue(4, BitConverter.ToString(pduPropertyDetail.DataBlock).Replace("-", " "));
break;
default:
sqlDataRecord.SetValue(4, null);
break;
}
yield return(sqlDataRecord);
}
}
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;
}
}
