public static T Convert <T>(System.Data.Common.DbDataReader reader, ref T obj)
{
try
{
var fields = typeof(T).GetFields(BindingFlags.Instance | BindingFlags.Public);
foreach (var f in fields)
{
var col = reader.GetOrdinal(f.Name);
if (col >= 0)
{
f.SetValue(obj, ChangeType(reader.GetValue(col), f.FieldType));
}
}
var props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (var p in props)
{
var col = reader.GetOrdinal(p.Name);
if (col >= 0)
{
p.SetValue(obj, ChangeType(reader.GetValue(col), p.PropertyType), null);
}
}
}
catch (Exception ex) { TraceLogger.Instance.WriteException(ex); throw; }
return(obj);
}
public List <Usuarios> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Usuarios> list = new List <Usuarios>();
while (Datos.Read())
{
Usuarios item = new Usuarios();
item.id = Convert.ToInt32(Datos.GetValue(0));
item.nombre = Convert.ToString(Datos.GetValue(1));
item.cedula = Convert.ToString(Datos.GetValue(2));
item.clave = Convert.ToString(Datos.GetValue(3));
list.Add(item);
}
return(list);
}
private static void QueryEmployee(Oracle.DataAccess.Client.OracleConnection conn)
{
string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee";
// Tạo một đối tượng Command.
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
// Liên hợp Command với Connection.
cmd.Connection = conn;
cmd.CommandText = sql;
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// Chỉ số (index) của cột Emp_ID trong câu lệnh SQL.
int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
long empId = Convert.ToInt64(reader.GetValue(0));
// Chỉ số của cột Emp_No là 1.
string empNo = reader.GetString(1);
int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
string empName = reader.GetString(empNameIndex);
int mngIdIndex = reader.GetOrdinal("Mng_Id");
long?mngId = null;
if (!reader.IsDBNull(mngIdIndex))
{
mngId = Convert.ToInt64(reader.GetValue(mngIdIndex));
}
Console.WriteLine("--------------------");
Console.WriteLine("empIdIndex:" + empIdIndex);
Console.WriteLine("EmpId:" + empId);
Console.WriteLine("EmpNo:" + empNo);
Console.WriteLine("EmpName:" + empName);
Console.WriteLine("MngId:" + mngId);
}
}
}
}
public List <Archivos> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Archivos> list = new List <Archivos>();
while (Datos.Read())
{
Archivos item = new Archivos();
item.id = Convert.ToInt32(Datos.GetValue(0));
item.usuarios.id = Convert.ToInt32(Datos.GetValue(1));
item.nombre = Convert.ToString(Datos.GetValue(2));
item.fecha = Convert.ToString(Datos.GetValue(3));
item.ubicacion = Convert.ToString(Datos.GetValue(4));
list.Add(item);
}
return(list);
}
public static dynamic consulta(string query, bool tipoSelect = false, bool eliminando = false)
{
var consulta = new List <Dictionary <string, object> >();
try
{
conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conexion);
if (!tipoSelect)
{
System.Data.Common.DbDataReader datos = cmd.ExecuteReader();
while (datos.Read())
{
Dictionary <string, object> objeto = new Dictionary <string, object>();
for (int x = 0; x < datos.FieldCount; x++)
{
objeto.Add(datos.GetName(x), datos.GetValue(x));
}
consulta.Add(objeto);
}
// consulta.Clear();
// consulta = new List<Dictionary<string, object>>();
}
else
{
try
{
cmd.ExecuteNonQuery();
conexion.Close();
cmd.Dispose();
return(true);
}
catch (Exception e)
{
if (eliminando)
{
return(false);
}
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion.Close();
return(false);
}
}
conexion.Close();
}
catch (Exception e)
{
if (eliminando)
{
return(false);
}
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion.Close();
}
return(consulta);
}
public void AddSheet(System.Data.Common.DbDataReader reader, string?name = null)
{
NewSheet(name);
// Write headers
for (int i = 0; i < reader.FieldCount; i++)
{
WriteData(reader.GetName(i));
}
NewLine();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var value = reader.GetValue(i);
if (value == null || value == DBNull.Value)
{
value = "";
}
WriteData(value);
}
NewLine();
}
}
public List <AfiliadoPaciente> Mapear(System.Data.Common.DbDataReader Datos)
{
List <AfiliadoPaciente> list = new List <AfiliadoPaciente>();
while (Datos.Read())
{
AfiliadoPaciente item = new AfiliadoPaciente();
item.id_AfiliadoPaciente = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <Especialidad> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Especialidad> list = new List <Especialidad>();
while (Datos.Read())
{
Especialidad item = new Especialidad();
item.id_especialidad = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
private List <InstitucionSistema> Mapear(System.Data.Common.DbDataReader Datos)
{
List <InstitucionSistema> list = new List <InstitucionSistema>();
while (Datos.Read())
{
InstitucionSistema item = new InstitucionSistema();
item.id_institucionSistema = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
private static DBSerie ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBSerie dbSerie = new DBSerie();
dbSerie.ID = result.GetInt32(0);
dbSerie.Name = result.GetString(1);
if (!string.IsNullOrEmpty(result.GetValue(2).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(2).ToString()))
{
dbSerie.Image = ReadToEnd(result.GetStream(2));
}
dbSerie.Extension = result.GetString(3);
if (!string.IsNullOrEmpty(result.GetValue(4).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(4).ToString()))
{
dbSerie.ParentID = result.GetInt32(4);
}
return(dbSerie);
}
public static T Gr <T>(this System.Data.Common.DbDataReader Dr, int I)
{
if (Dr.IsDBNull(I))
{
return(default(T));
}
return((T)Dr.GetValue(I));
}
public List <LugarAtencion> Mapear(System.Data.Common.DbDataReader Datos)
{
List <LugarAtencion> list = new List <LugarAtencion>();
while (Datos.Read())
{
LugarAtencion item = new LugarAtencion();
item.id_lugarAtencion = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <CodigoMPS> Mapear(System.Data.Common.DbDataReader Datos)
{
List <CodigoMPS> list = new List <CodigoMPS>();
while (Datos.Read())
{
CodigoMPS item = new CodigoMPS();
item.id_codigoMPS = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <GrupoPrioritario> Mapear(System.Data.Common.DbDataReader Datos)
{
List <GrupoPrioritario> list = new List <GrupoPrioritario>();
while (Datos.Read())
{
GrupoPrioritario item = new GrupoPrioritario();
item.id_GrupoPrioritario = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
private List <TipoEstable> Mapear(System.Data.Common.DbDataReader Datos)
{
List <TipoEstable> list = new List <TipoEstable>();
while (Datos.Read())
{
TipoEstable item = new TipoEstable();
item.id_tipoEstable = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <FormacionProfecional> Mapear(System.Data.Common.DbDataReader Datos)
{
List <FormacionProfecional> list = new List <FormacionProfecional>();
while (Datos.Read())
{
FormacionProfecional item = new FormacionProfecional();
item.id_formacionProfecional = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <Usuario> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Usuario> list = new List <Usuario>();
while (Datos.Read())
{
Usuario item = new Usuario();
item.id = Convert.ToInt32(Datos.GetValue(0));
item.usuario = Convert.ToString(Datos.GetValue(1));
item.contrasena = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public List <Provincia> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Provincia> list = new List <Provincia>();
while (Datos.Read())
{
Provincia item = new Provincia();
item.id_Provincia = Convert.ToInt32(Datos.GetValue(0));
item.codigo = Convert.ToInt32(Datos.GetValue(1));
item.descrpcion = Convert.ToString(Datos.GetValue(2));
list.Add(item);
}
return(list);
}
public static dynamic consulta2(string query, bool tipoSelect = false)
{
string host = SISPE_MIGRACION.Properties.Resources.servidor;
string usuario = SISPE_MIGRACION.Properties.Resources.usuario;
string password = SISPE_MIGRACION.Properties.Resources.password;
string database = "archivos";
string port = SISPE_MIGRACION.Properties.Resources.puerto;
string queryConexion = string.Format("Host={0};Username={1};Password={2};Database={3};port={4}Timeout=300;CommandTimeout = 300", host, usuario, password, database, port);
realizarConexion2(queryConexion);
var consulta = new List <Dictionary <string, object> >();
try
{
conexion2.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conexion2);
if (!tipoSelect)
{
System.Data.Common.DbDataReader datos = cmd.ExecuteReader();
while (datos.Read())
{
Dictionary <string, object> objeto = new Dictionary <string, object>();
for (int x = 0; x < datos.FieldCount; x++)
{
objeto.Add(datos.GetName(x), datos.GetValue(x));
}
consulta.Add(objeto);
}
}
else
{
try
{
cmd.ExecuteNonQuery();
conexion2.Close();
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion2.Close();
return(false);
}
}
conexion2.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion2.Close();
}
return(consulta);
}
public static Row FullFromReader(string name, System.Data.Common.DbDataReader reader)
{
Row m = new Row(name);
for (int i = 0; i < reader.FieldCount; i++)
{
m[reader.GetName(i)] = reader.GetValue(i);
}
return(m);
}
} // End Function Sql2DataTableTest
public static DataTable Sql2DataTableTest(System.Data.Common.DbCommand cmd, DataTable dt)
{
if (dt == null)
{
dt = new DataTable();
}
//else dt.Clear();
using (System.Data.Common.DbDataReader rdr = CoinBaseSharp.SQL.ExecuteReader(cmd, System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection))
{
int fieldCount = rdr.FieldCount;
string[] fieldNames = new string[fieldCount];
System.Type[] fieldTypes = new System.Type[fieldCount];
object[] objs = new object[fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
fieldNames[i] = rdr.GetName(i);
fieldTypes[i] = rdr.GetFieldType(i);
dt.Columns.Add(fieldNames[i], fieldTypes[i]);
} // Next i
if (rdr.HasRows)
{
while (rdr.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < fieldCount; ++i)
{
object obj = rdr.GetValue(i);
if (obj == null)
{
dr[fieldNames[i]] = System.DBNull.Value;
}
else
{
dr[fieldNames[i]] = obj;
}
} // Next i
dt.Rows.Add(dr);
} // Whend
} // End if (rdr.HasRows)
} // End Using rdr
// string str = dt.ToHtml();
// System.Console.WriteLine(str);
// System.Console.WriteLine(dt.Rows.Count);
return(dt);
} // End Function Sql2DataTableTest
public static void MultipleDataSets(string strSQL)
{
DataSetSerialization ser = new DataSetSerialization();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
)
)
{
Table tbl = null;
do
{
tbl = new Table();
for (int i = 0; i < dr.FieldCount; ++i)
{
tbl.Columns.Add(
new ColumnInfo()
{
ColumnName = dr.GetName(i),
FieldType = dr.GetFieldType(i)
}
);
} // Next i
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
thisRow[i] = dr.GetValue(i);
} // Next i
tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
string str = EasyJSON.JsonHelper.SerializePretty(ser);
System.Console.WriteLine(str);
DataSetSerialization ser2 = EasyJSON.JsonHelper.Deserialize <DataSetSerialization>(str);
System.Console.WriteLine(ser2);
} // End Sub MultipleDataSets
public List <Resultado> Mapear(System.Data.Common.DbDataReader Datos)
{
List <Resultado> list = new List <Resultado>();
while (Datos.Read())
{
Resultado item = new Resultado(Convert.ToString(Datos.GetValue(0).ToString()), Convert.ToInt32(Datos.GetValue(1)));
list.Add(item);
}
return(list);
}
private static DBPage ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBPage dbPage = new DBPage();
dbPage.ID = result.GetInt32(0);
dbPage.IDFichier = result.GetInt32(1);
dbPage.Ordre = result.GetInt32(2);
if (!string.IsNullOrEmpty(result.GetValue(3).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(3).ToString()))
{
dbPage.Element = ReadToEnd(result.GetStream(3));
}
return(dbPage);
}
static string AsCSVDif(System.Data.Common.DbDataReader srcVals, System.Data.Common.DbDataReader destVals, int idOrdinal)
{
var sb = new System.Text.StringBuilder("Field\tSource\tDestination\r\n");
for (int i = 0; i < srcVals.FieldCount; i++)
{
object oldRes = srcVals.GetValue(i);
object newRes = destVals.GetValue(i);
if (i == idOrdinal || oldRes != newRes)
{
sb.AppendFormat("{0}\t{1}\t{2}\r\n",srcVals.GetName(i), oldRes,newRes);
}
}
return sb.ToString();
}
private static DBFichier ExtractDataFromDataReader(System.Data.Common.DbDataReader result)
{
DBFichier dBFichier = new DBFichier();
dBFichier.ID = result.GetInt32(0);
dBFichier.Name = result.GetString(1);
if (!string.IsNullOrEmpty(result.GetValue(2).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(2).ToString()))
{
dBFichier.Order = result.GetInt32(2);
}
if (!string.IsNullOrEmpty(result.GetValue(3).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(3).ToString()))
{
dBFichier.Image = ReadToEnd(result.GetStream(3));
}
if (!string.IsNullOrEmpty(result.GetValue(4).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(4).ToString()))
{
dBFichier.ParentID = result.GetInt32(4);
}
if (!string.IsNullOrEmpty(result.GetValue(5).ToString()) && !string.IsNullOrWhiteSpace(result.GetValue(5).ToString()))
{
dBFichier.Collection = result.GetString(5);
}
return(dBFichier);
}
public T ReadValue <T>(System.Data.Common.DbDataReader reader, int index)
{
object obj = reader.GetValue(index);
if (obj is T)
{
return((T)obj);
}
try
{
return((T)obj);
}
catch { }
return(default(T));
}
public static object GetReaderValue(System.Data.Common.DbDataReader reader, string name)
{
var ordinal = reader.GetOrdinal(name);
if (reader.IsDBNull(ordinal))
{
return(null);
}
try
{
return(reader.GetValue(ordinal));
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public dynamic consulta(string query, bool tipoSelect = false)
{
dynamic consulta = null;
try
{
conexion.Open();
cmd = new NpgsqlCommand(query, conexion, transaccion);
if (!tipoSelect)
{
consulta = new List <Dictionary <string, object> >();
System.Data.Common.DbDataReader datos = cmd.ExecuteReader();
while (datos.Read())
{
Dictionary <string, object> objeto = new Dictionary <string, object>();
for (int x = 0; x < datos.FieldCount; x++)
{
objeto.Add(datos.GetName(x), datos.GetValue(x));
}
consulta.Add(objeto);
}
}
else
{
cmd.ExecuteNonQuery();
transaccion.Rollback();
consulta = true;
}
}
catch {
if (transactionActivada)
{
transactionActivada = false;
transaccion.Rollback();
}
conexion.Close();
consulta = false;
}
conexion.Close();
return(consulta);
}
private DataTable ObterTabela(System.Data.Common.DbDataReader reader)
{
//while (reader.NextResult())
//{
//}
DataTable tbEsquema = reader.GetSchemaTable();
DataTable tbRetorno = new DataTable();
foreach (DataRow r in tbEsquema.Rows)
{
if (!tbRetorno.Columns.Contains(r["ColumnName"].ToString()))
{
DataColumn col = new DataColumn()
{
ColumnName = r["ColumnName"].ToString(),
Unique = Convert.ToBoolean(r["IsUnique"]),
AllowDBNull = Convert.ToBoolean(r["AllowDBNull"])
//ReadOnly = false //Convert.ToBoolean(r["IsReadOnly"])
};
tbRetorno.Columns.Add(col);
}
else
{
/* break point aqui quando der louca na montagem da estrutura da tabela dinâmica */
}
}
while (reader.Read())
{
DataRow novaLinha = tbRetorno.NewRow();
for (int i = 0; i < tbRetorno.Columns.Count; i++)
{
novaLinha[i] = reader.GetValue(i);
}
tbRetorno.Rows.Add(novaLinha);
}
return(tbRetorno);
}
