IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("ExportId", SqlDbType.Int),
new SqlMetaData("OrderColumn", SqlDbType.Int),
new SqlMetaData("ColumnName", SqlDbType.VarChar, 500),
new SqlMetaData("ColumnType", SqlDbType.VarChar, 500),
new SqlMetaData("ColumnValue", SqlDbType.VarChar, 500),
new SqlMetaData("TableName", SqlDbType.VarChar, 500),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
foreach (tBaseExportColumns data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.ExportId);
ret.SetInt32(2, data.OrderColumn);
ret.SetString(3, String.IsNullOrEmpty(data.ColumnName) ? "" : data.ColumnName);
ret.SetString(4, String.IsNullOrEmpty(data.ColumnType) ? "" : data.ColumnType);
ret.SetString(5, String.IsNullOrEmpty(data.ColumnValue) ? "" : data.ColumnValue);
ret.SetString(6, String.IsNullOrEmpty(data.TableName) ? "" : data.TableName);
ret.SetInt16(7, data.Status);
ret.SetInt16(8, data.Action);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
//new SqlMetaData("DISTRIBUTORID", SqlDbType.Int),
new SqlMetaData("ACCOUNTTYPE", SqlDbType.Int),
new SqlMetaData("BANKNAME", SqlDbType.VarChar, 50),
new SqlMetaData("ROUTINGNUMBER", SqlDbType.VarChar, 50),
new SqlMetaData("ACCOUNTNUMBER", SqlDbType.VarChar, 50),
new SqlMetaData("SETASDEFAULT", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt),
new SqlMetaData("POSITION", SqlDbType.Int)
);
foreach (tBaseBank data in this)
{
ret.SetInt32(0, data.ID);
ret.SetInt32(1, data.AccountType);
ret.SetString(2, data.BankName);
ret.SetString(3, data.RoutingNumber);
ret.SetString(4, data.AccountNumber);
ret.SetInt16(5, data.Setasdefault);
ret.SetInt16(6, data.Action);
ret.SetInt32(7, data.Position);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("QUERYID", SqlDbType.Int),
new SqlMetaData("TABLEQUERYID", SqlDbType.Int),
new SqlMetaData("TABLEQUERYCOLUMNID", SqlDbType.Int),
new SqlMetaData("QUERYCOMPARISONID", SqlDbType.Int),
new SqlMetaData("VALUE", SqlDbType.NVarChar, 500),
new SqlMetaData("OPERATOR", SqlDbType.SmallInt),
new SqlMetaData("STATUS", SqlDbType.SmallInt)
);
foreach (tQueryFilters data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.QueryId);
ret.SetInt32(2, data.TableQueryId);
ret.SetInt32(3, data.TableQueryColumnId);
ret.SetInt32(4, data.QueryComparisonId);
ret.SetString(5, data.Value);
ret.SetInt16(6, data.Operator);
ret.SetInt16(7, data.status);
yield return(ret);
}
}
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);
}
}
/// <summary>
/// Sets the int16.
/// </summary>
/// <param name="record">The record.</param>
/// <param name="fieldName">Name of the field.</param>
/// <param name="value">The value.</param>
public static void SetInt16(this SqlDataRecord record, string fieldName, Int16?value)
{
int ordinal = GetOrdinal(record, fieldName);
if (value.HasValue)
{
record.SetInt16(ordinal, value.Value);
}
}
/*al jectura el procedure madna a llamar al metodo tipo SqlDataRecord*/
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()/*devuelve un tipo Ienumretaor, permite solo lectura a una colección de objetos*/
{
/*el cual pirmero configura la tabla sql data record envia una fila unica de datos */
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
/*recoore la lista de obejtos asignaleSqlDataRecord el valor de la lista */
foreach (tBaseId data in this)/**recorre una lista enmurada y envia uno por uno son promresas*/
{
ret.SetInt32(0, data.Id);
ret.SetInt16(1, data.Status);
ret.SetInt16(2, data.Action);
yield return(ret);/*promesas yield*/
}
}
internal override void SetDataRecordValue(SqlDataRecord record, int ordinal)
{
if (InputValue == null)
{
record.SetDBNull(ordinal);
}
else
{
record.SetInt16(ordinal, InputValue.Value);
}
}
public override void Set(SqlDataRecord record, int ordinal, short?value)
{
if (value.HasValue)
{
record.SetInt16(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("CODE ", SqlDbType.Int),
new SqlMetaData("TABLE", SqlDbType.Int),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
foreach (tBaseUserSettings data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.Code);
ret.SetInt32(2, data.Table);
ret.SetInt16(3, data.Status);
ret.SetInt16(4, data.Action);
yield return(ret);
}
}
public static SqlDataRecord SetInt16(this SqlDataRecord r, int pos, short?v)
{
if (!v.HasValue)
{
r.SetDBNull(pos);
}
else
{
r.SetInt16(pos, v.Value);
}
return(r);
}
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, short?value)
{
EnsureArg.IsNotNull(record, nameof(record));
if (value.HasValue)
{
record.SetInt16(ordinal, value.Value);
}
else
{
record.SetDBNull(ordinal);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var row = new SqlDataRecord(
new SqlMetaData("CheckId", SqlDbType.Int),
new SqlMetaData("Status", SqlDbType.SmallInt)
);
foreach (OnHoldChecksModel check in this)
{
row.SetInt32(0, check.CheckID);
row.SetInt16(1, Int16.Parse(check.CheckStatus.ToString()));
yield return(row);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("ExportId", SqlDbType.Int),
new SqlMetaData("FilterField", SqlDbType.Int),
new SqlMetaData("FilterName", SqlDbType.VarChar, 500),
new SqlMetaData("FilterType", SqlDbType.Int),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
foreach (tBaseExportDetails data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.OrderExportId);
ret.SetInt32(2, data.FilterField);
ret.SetString(3, String.IsNullOrEmpty(data.FilterName) ? "" : data.FilterName);
ret.SetInt32(4, data.FilterType);
ret.SetInt16(5, data.Status);
ret.SetInt16(6, data.Action);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("ADDRESSTYPE", SqlDbType.SmallInt),
new SqlMetaData("ADDRESS", SqlDbType.VarChar, 50),
new SqlMetaData("ADDRESS1", SqlDbType.VarChar, 50),
new SqlMetaData("CITY", SqlDbType.VarChar, 50),
new SqlMetaData("STATE", SqlDbType.VarChar, 50),
new SqlMetaData("ZIP", SqlDbType.VarChar, 50),
new SqlMetaData("COUNTY", SqlDbType.VarChar, 50),
new SqlMetaData("COUNTRY", SqlDbType.VarChar, 50),
new SqlMetaData("SETASDEFAULT", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt),
new SqlMetaData("POSITION", SqlDbType.Int),
new SqlMetaData("STATUS", SqlDbType.Int)
);
foreach (tBaseAddress data in this)
{
ret.SetInt32(0, data.ID);
ret.SetInt16(1, data.Addresstype);
ret.SetString(2, data.Address);
ret.SetString(3, data.Address1);
ret.SetString(4, data.City);
ret.SetString(5, data.State);
ret.SetString(6, data.Zip);
ret.SetString(7, !String.IsNullOrEmpty(data.County) ? data.County : "");
ret.SetString(8, data.Country);
ret.SetInt16(9, data.Setasdefault);
ret.SetInt16(10, data.Action);
ret.SetInt32(11, data.Position);
ret.SetInt32(12, data.Status);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ALERTID", SqlDbType.Int),
new SqlMetaData("MARKETID", SqlDbType.Int),
new SqlMetaData("STATUS", SqlDbType.SmallInt)
);
foreach (tBaseAlertSystem_Market data in this)
{
ret.SetInt32(0, data.AlertId);
ret.SetInt32(1, data.MarketId);
ret.SetInt16(2, data.Status);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("QUERYID", SqlDbType.Int),
new SqlMetaData("TABLEQUERYID", SqlDbType.Int),
new SqlMetaData("TABLEQUERYCOLUMNID", SqlDbType.Int),
new SqlMetaData("ALIAS", SqlDbType.NVarChar, 500),
new SqlMetaData("STATUS", SqlDbType.SmallInt)
);
foreach (tQueryFields data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.QueryId);
ret.SetInt32(2, data.TableQueryId);
ret.SetInt32(3, data.TableQueryColumnId);
ret.SetString(4, data.Alias);
ret.SetInt16(5, data.status);
yield return(ret);
}
}
public override void Set(SqlDataRecord record, int ordinal, short value)
{
record.SetInt16(ordinal, value);
}
public override void Set(SqlDataRecord record, int ordinal, short value)
{
EnsureArg.IsNotNull(record, nameof(record));
record.SetInt16(ordinal, value);
}
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();
}
}
