public List <T> Query(string query)
{
List <T> resultset = new List <T>();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, Connection.Get()))
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dynamic entry = new ExpandoObject();
for (int i = 0; i < reader.FieldCount; i++)
{
Type type = reader.GetFieldType(i);
entry[reader.GetName(i)] = Convert.ChangeType(reader.GetFieldValue <Object>(i), type);
}
resultset.Add(entry);
}
return(resultset);
}
}
// Инициализирует запись данными из DataReader
protected Record(NpgsqlDataReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
if( !reader.IsDBNull(i) )
this.GetType().GetProperty(reader.GetName(i)).SetValue(this, reader.GetValue(i), null);
}
public DataSet get_data_mmyy(string str, string tu, string den)
{
DataSet tmp = null;
DateTime dt1 = StringToDate(tu);
DateTime dt2 = StringToDate(den);
int y1 = dt1.Year, m1 = dt1.Month;
int y2 = dt2.Year, m2 = dt2.Month;
int itu, iden;
string mmyy = "";
bool be = true;
Npgsql.NpgsqlConnection connct = new NpgsqlConnection(ConStr);
connct.Open();
for (int i = y1; i <= y2; i++)
{
itu = (i == y1) ? m1 : 1;
iden = (i == y2) ? m2 : 12;
for (int j = itu; j <= iden; j++)
{
mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
if (bMmyy(mmyy))
{
sql = str.Replace("xxx", "medibv" + mmyy);
sql = str.Replace("medibvmmyy", "medibv" + mmyy);
using (Npgsql.NpgsqlCommand cmm = new NpgsqlCommand(sql, connct))
{
// cmm.Connection.Open();
Npgsql.NpgsqlDataReader drd = null;
try
{
drd = cmm.ExecuteReader();
if (tmp == null)
{
tmp = new DataSet();
}
if (tmp.Tables.Count == 0 && drd.FieldCount > 0)
{
tmp.Tables.Add("Table");
}
if (tmp.Tables.Count > 0)
{
for (int ia = 0; ia < drd.FieldCount; ia++)
{
if (!tmp.Tables[0].Columns.Contains(drd.GetName(ia)))
{
tmp.Tables[0].Columns.Add(drd.GetName(ia), drd.GetFieldType(ia));
}
}
while (drd.Read())
{
DataRow ndtr = tmp.Tables[0].NewRow();
for (int ie = 0; ie < drd.FieldCount; ie++)
{
ndtr[drd.GetName(ie)] = drd[ie];
}
tmp.Tables[0].Rows.Add(ndtr);
}
}
}
catch
{
}
finally
{
if (drd != null)
{
drd.Close();
drd.Dispose();
}
}
}
}
}
}
connct.Close();
return(tmp);
}
private void FigureOutFunctionReturn(IEnumerable<Parameter> parameters, NpgsqlDataReader rdr, string actualName)
{
if (parameters.Where(param => param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output).Count() == 0)
{
if (rdr.FieldCount == 1 && rdr.GetName(0) == actualName)
{
// Simple return
rdr.Read();
Console.WriteLine("Simple Return: {0}", rdr.GetValue(0));
}
}
}
/// <summary>
/// Transfere dados do banco de dados atual para um banco de dados de destino.
/// Conexão com o banco de destino precisa estar aberta.
/// </summary>
/// <returns>Número de linhas transferidas.</returns>
/// <param name="p_query">Consulta SQL para buscar os dados no banco atual.</param>
/// <param name="p_insert">Comando de inserção para inserir cada linha no banco de destino.</param>
/// <param name="p_destdatabase">Conexão com o banco de destino.</param>
public override uint Transfer(string p_query, Spartacus.Database.Command p_insert, Spartacus.Database.Generic p_destdatabase)
{
uint v_transfered = 0;
if (this.v_con == null)
{
try
{
this.v_con = new Npgsql.NpgsqlConnection(this.v_connectionstring);
this.v_con.Open();
this.v_cmd = new Npgsql.NpgsqlCommand(p_query, this.v_con);
this.v_reader = this.v_cmd.ExecuteReader();
while (v_reader.Read())
{
for (int i = 0; i < v_reader.FieldCount; i++)
p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString());
p_destdatabase.Execute(p_insert.GetUpdatedText());
v_transfered++;
}
return v_transfered;
}
catch (Npgsql.NpgsqlException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
if (this.v_cmd != null)
{
this.v_cmd.Dispose();
this.v_cmd = null;
}
if (this.v_con != null)
{
this.v_con.Close();
this.v_con = null;
}
}
}
else
{
try
{
this.v_cmd.CommandText = p_query;
this.v_reader = this.v_cmd.ExecuteReader();
while (v_reader.Read())
{
for (int i = 0; i < v_reader.FieldCount; i++)
p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString());
p_destdatabase.Execute(p_insert.GetUpdatedText());
v_transfered++;
}
return v_transfered;
}
catch (Npgsql.NpgsqlException e)
{
throw new Spartacus.Database.Exception(e);
}
finally
{
if (this.v_reader != null)
{
this.v_reader.Close();
this.v_reader = null;
}
}
}
}
void ShowResultSet(NpgsqlDataReader reader)
{
bool headerPrinted = false;
int count = 1;
while (reader.Read())
{
if (!headerPrinted)
{
Console.WriteLine(@"<table class=""sqloutput""><tbody><tr><th> </th>");
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
Console.WriteLine(@"<th>{0}</th>", string.IsNullOrEmpty(name) ? "(No column name)" : name);
}
Console.WriteLine("</tr>");
headerPrinted = true;
}
Console.WriteLine(@"<tr><td>{0}</td>", count++);
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader[i] == DBNull.Value)
{
Console.WriteLine(@"<td><i>NULL</i></td>");
}
else
{
if (reader[i] as string == null && reader[i] as IEnumerable != null)
{
string res = "";
foreach (var a in (reader[i] as IEnumerable))
res += Convert.ToString(a);
Console.WriteLine(@"<td>{0}</td>", res);
}
else
{
Console.WriteLine(@"<td>{0}</td>", HttpUtility.HtmlEncode(reader[i]));
}
}
}
Console.WriteLine("</tr>");
}
if (headerPrinted)
Console.WriteLine("</tbody></table>");
}