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);
}
}
private static IEnumerable <SqlDataRecord> BindProviderRows(IEnumerable <ProviderEntity> providersToUpdate)
{
foreach (var provider in providersToUpdate)
{
SqlDataRecord record = new SqlDataRecord(typ_ProviderTable);
record.SetInt64(0, provider.ProviderId);
record.SetString(1, provider.AddressLine1);
if (string.IsNullOrWhiteSpace(provider.AddressLine2))
{
record.SetDBNull(2);
}
else
{
record.SetString(2, provider.AddressLine2);
}
if (string.IsNullOrWhiteSpace(provider.AddressLine3))
{
record.SetDBNull(3);
}
else
{
record.SetString(3, provider.AddressLine3);
}
record.SetString(4, provider.City);
if (string.IsNullOrWhiteSpace(provider.StateOrProvince))
{
record.SetDBNull(5);
}
else
{
record.SetString(5, provider.StateOrProvince);
}
record.SetString(6, provider.Country);
record.SetString(7, provider.ZipCode);
record.SetInt32(8, (int)provider.ProviderStatus);
if (provider.GeoLatitude.HasValue)
{
record.SetDouble(9, provider.GeoLatitude.Value);
}
else
{
record.SetDBNull(9);
}
if (provider.GeoLongitude.HasValue)
{
record.SetDouble(10, provider.GeoLongitude.Value);
}
else
{
record.SetDBNull(10);
}
yield return(record);
}
}
/// <summary>
/// Sets the double.
/// </summary>
/// <param name="record">The record.</param>
/// <param name="fieldName">Name of the field.</param>
/// <param name="value">The value.</param>
public static void SetDouble(this SqlDataRecord record, string fieldName, double?value)
{
int ordinal = GetOrdinal(record, fieldName);
if (value.HasValue)
{
record.SetDouble(ordinal, value.Value);
}
}
public static void SetNullableDouble(this SqlDataRecord rec, int index, double?value)
{
if (value.HasValue)
{
rec.SetDouble(index, value.GetValueOrDefault());
}
else
{
rec.SetDBNull(index);
}
}
public static void SetNullableDouble(this SqlDataRecord sqlDataRecord, int ordinal, double?value)
{
if (value.HasValue)
{
sqlDataRecord.SetDouble(ordinal, value.Value);
}
else
{
sqlDataRecord.SetDBNull(ordinal);
}
}
internal override void SetDataRecordValue(SqlDataRecord record, int ordinal)
{
if (InputValue == null)
{
record.SetDBNull(ordinal);
}
else
{
record.SetDouble(ordinal, InputValue.Value);
}
}
public override void Set(SqlDataRecord record, int ordinal, double?value)
{
if (value.HasValue)
{
record.SetDouble(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
private static void SetDoubleToDataRecord(ref SqlDataRecord record, int index, double?value)
{
if (value.HasValue)
{
record.SetDouble(index, value.Value);
}
else
{
record.SetDBNull(index);
}
}
public override void Set(SqlDataRecord record, int ordinal, double?value)
{
EnsureArg.IsNotNull(record, nameof(record));
if (value.HasValue)
{
record.SetDouble(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
private static SqlDataRecord CreateFractalRecord(double?value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Float));
if (value.HasValue)
{
record.SetDouble(0, value.Value);
}
else
{
record.SetDBNull(0);
}
return(record);
}
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, double value)
{
EnsureArg.IsNotNull(record, nameof(record));
record.SetDouble(ordinal, value);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("DataName", SqlDbType.NVarChar, 40),
new SqlMetaData("IndexNode", SqlDbType.NVarChar, 255),
new SqlMetaData("QcLocation", SqlDbType.NVarChar, 255),
new SqlMetaData("DataType", SqlDbType.NVarChar, 40),
new SqlMetaData("DataKey", SqlDbType.NVarChar, 400),
new SqlMetaData("Latitude", SqlDbType.Float),
new SqlMetaData("Longitude", SqlDbType.Float),
new SqlMetaData("JsonDataObject", SqlDbType.NVarChar, SqlMetaData.Max)
);
foreach (IndexData data in this)
{
ret.SetString(0, data.DataName);
ret.SetString(1, data.IndexNode);
if (data.QcLocation == null)
{
ret.SetDBNull(2);
}
else
{
ret.SetString(2, data.QcLocation);
}
ret.SetString(3, data.DataType);
if (data.DataKey == null)
{
ret.SetDBNull(4);
}
else
{
ret.SetString(4, data.DataKey);
}
if (data.Latitude == null)
{
ret.SetDBNull(5);
}
else
{
ret.SetDouble(5, (double)data.Latitude);
}
if (data.Longitude == null)
{
ret.SetDBNull(6);
}
else
{
ret.SetDouble(6, (double)data.Longitude);
}
if (data.JsonDataObject == null)
{
ret.SetDBNull(7);
}
else
{
ret.SetString(7, data.JsonDataObject);
}
yield return(ret);
}
}
public List <SqlDataRecord> BuildAdultArtVisitss(List <ArtVisitModel> artVisits, int siteId)
{
var records = new List <SqlDataRecord>();
var errors = new List <ArtVisitModel>();
try
{
SqlMetaData[] sqlMetaData = new SqlMetaData[24];
sqlMetaData[0] = new SqlMetaData("PatientIdentifier", SqlDbType.NVarChar, 100);
sqlMetaData[1] = new SqlMetaData("IdNo", SqlDbType.Int);
sqlMetaData[2] = new SqlMetaData("VisitDate", SqlDbType.DateTime2);
sqlMetaData[3] = new SqlMetaData("Weight", SqlDbType.Float);
sqlMetaData[4] = new SqlMetaData("Height", SqlDbType.Float);
sqlMetaData[5] = new SqlMetaData("Temperature", SqlDbType.Float);
sqlMetaData[6] = new SqlMetaData("WhoStage", SqlDbType.NVarChar, 50);
sqlMetaData[7] = new SqlMetaData("IsPregnant", SqlDbType.Bit);
sqlMetaData[8] = new SqlMetaData("TbScreen", SqlDbType.NVarChar, 50);
sqlMetaData[9] = new SqlMetaData("TbDiagnosedTreatment", SqlDbType.NVarChar, 50);
sqlMetaData[10] = new SqlMetaData("TbTreatmentStarted", SqlDbType.NVarChar, 50);
sqlMetaData[11] = new SqlMetaData("CotrimeProphylaxis", SqlDbType.NVarChar, 50);
sqlMetaData[12] = new SqlMetaData("ArtStarted", SqlDbType.Bit);
sqlMetaData[13] = new SqlMetaData("ArvAdherenceCode", SqlDbType.NVarChar, 50);
sqlMetaData[14] = new SqlMetaData("ArvNonAdherenceCode", SqlDbType.NVarChar, 50);
sqlMetaData[15] = new SqlMetaData("DrugReactionCode", SqlDbType.NVarChar, 50);
sqlMetaData[16] = new SqlMetaData("ArvStopCode", SqlDbType.NVarChar, 50);
sqlMetaData[17] = new SqlMetaData("ArvStopDate", SqlDbType.DateTime2);
sqlMetaData[18] = new SqlMetaData("Cd4Count", SqlDbType.NVarChar, 50);
sqlMetaData[19] = new SqlMetaData("BloodPressure", SqlDbType.NVarChar, 50);
sqlMetaData[20] = new SqlMetaData("FamilyPlanningCode", SqlDbType.NVarChar, 50);
sqlMetaData[21] = new SqlMetaData("AppointmentDate", SqlDbType.DateTime2);
sqlMetaData[22] = new SqlMetaData("StatusCode", SqlDbType.NVarChar, 50);
sqlMetaData[23] = new SqlMetaData("FacilityId", SqlDbType.Int);
artVisits.ForEach(p =>
{
if (!string.IsNullOrEmpty(p.PatientIdentifier))
{
var row = new SqlDataRecord(sqlMetaData);
row.SetString(0, p.PatientIdentifier);
row.SetInt32(1, p.IdNo);
row.SetDateTime(2, (p.VisitDate == null || (p.VisitDate != null && p.VisitDate.Value < (DateTime)SqlDateTime.MinValue) || (p.VisitDate != null && p.VisitDate.Value > (DateTime)SqlDateTime.MaxValue)) ? DateTime.Today : p.VisitDate.Value);
row.SetDouble(3, p.Weight != null ? p.Weight.Value : 0.0);
row.SetDouble(4, p.Height != null ? p.Height.Value : 0.0);
row.SetDouble(5, p.Temperature != null ? p.Temperature.Value : 0.0);
row.SetString(6, !string.IsNullOrEmpty(p.WhoStage) ? p.WhoStage : string.Empty);
row.SetBoolean(7, p.IsPregnant != null ? p.IsPregnant.Value : false);
row.SetString(8, !string.IsNullOrEmpty(p.TbScreen) ? p.TbScreen : string.Empty);
row.SetString(9, !string.IsNullOrEmpty(p.TbDiagnosedTreatment) ? p.TbDiagnosedTreatment : string.Empty);
row.SetString(10, !string.IsNullOrEmpty(p.TbTreatmentStarted) ? p.TbTreatmentStarted : string.Empty);
row.SetString(11, !string.IsNullOrEmpty(p.CotrimeProphylaxis) ? p.CotrimeProphylaxis : string.Empty);
row.SetBoolean(12, p.ArtStarted != null ? p.ArtStarted.Value : false);
row.SetString(13, !string.IsNullOrEmpty(p.ArvAdherenceCode) ? p.ArvAdherenceCode : string.Empty);
row.SetString(14, !string.IsNullOrEmpty(p.ArvNonAdherenceCode) ? p.ArvNonAdherenceCode : string.Empty);
row.SetString(15, !string.IsNullOrEmpty(p.DrugReactionCode) ? p.DrugReactionCode : string.Empty);
row.SetString(16, !string.IsNullOrEmpty(p.ArvStopCode) ? p.ArvStopCode : string.Empty);
row.SetDateTime(17, (p.ArvStopDate == null || (p.ArvStopDate != null && p.ArvStopDate.Value < (DateTime)SqlDateTime.MinValue) || (p.ArvStopDate != null && p.ArvStopDate.Value > (DateTime)SqlDateTime.MaxValue)) ? DateTime.Today : p.ArvStopDate.Value);
row.SetString(18, !string.IsNullOrEmpty(p.Cd4Count) ? p.Cd4Count : string.Empty);
row.SetString(19, !string.IsNullOrEmpty(p.BloodPressure) ? p.BloodPressure : string.Empty);
row.SetString(20, !string.IsNullOrEmpty(p.FamilyPlanningCode) ? p.FamilyPlanningCode : string.Empty);
row.SetDateTime(21, (p.AppointmentDate == null || (p.AppointmentDate != null && p.AppointmentDate.Value < (DateTime)SqlDateTime.MinValue) || (p.AppointmentDate != null && p.AppointmentDate.Value > (DateTime)SqlDateTime.MaxValue)) ? DateTime.Today : p.AppointmentDate.Value);
row.SetString(22, !string.IsNullOrEmpty(p.StatusCode) ? p.StatusCode : string.Empty);
row.SetInt32(23, siteId);
records.Add(row);
}
else
{
errors.Add(p);
}
});
return(records);
}
catch (Exception e)
{
return(records);
}
}
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 override void Set(SqlDataRecord record, int ordinal, double value)
{
record.SetDouble(ordinal, value);
}
