static void Main(string[] args)
{
Console.WriteLine(ReadDBConnectionString());
var DBConnection = new NpgsqlConnection(ReadDBConnectionString());
DBConnection.Open();
var StringSQLCommand = "select version()";
var SQLCommand = new NpgsqlCommand(StringSQLCommand, DBConnection);
var PostgreSQLVersion = SQLCommand.ExecuteScalar().ToString();
Console.WriteLine($"PostgreSQL version: {PostgreSQLVersion}");
StringSQLCommand = @"SELECT * FROM public.""Item"";";
SQLCommand.CommandText = StringSQLCommand;
SQLCommand.Connection = DBConnection;
Npgsql.NpgsqlDataReader ItemList = SQLCommand.ExecuteReader();
while (ItemList.Read())
{
Console.WriteLine("{0} , {1} ", ItemList.GetString(0), ItemList.GetString(1));
}
Console.ReadKey();
DBConnection.Close();
string ReadDBConnectionString()
{
string appSettings = ConfigurationManager.ConnectionStrings["Database1"].ToString();
return(appSettings);
}
}
/// <summary>
/// Converts a NpgsqlDataReader to a DataSet
/// <param name='reader'>
/// NpgsqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(NpgsqlDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
public static DBclasses.AsksCategory getAsksCategoriesById(int id)
{
DBclasses.AsksCategory result = new DBclasses.AsksCategory();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from askscategories where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.description = reader["description"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
public static DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
{
DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from kindofanimals where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.infoURL = reader["infoURL"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
// List All Candidates
public IEnumerable <CandidateInfo> GetAllCandidates()
{
List <CandidateInfo> canList = new List <CandidateInfo>();
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * from public.us_election_states order by name";
cmd.CommandType = CommandType.Text;
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CandidateInfo can = new CandidateInfo();
can.ID = Convert.ToInt32(dr["ID"].ToString());
can.State = dr["name"].ToString();
can.Republicans = Convert.ToInt32(dr["percent_rep"].ToString());
can.Democrats = Convert.ToInt32(dr["percent_dem"].ToString());
canList.Add(can);
}
conn.Close();
}
return(canList);
}
/// <summary>
/// Preenche um User com os seus dados
/// </summary>
public override void fillUser( User user )
{
NpgsqlConnection conn = new NpgsqlConnection(OrionGlobals.getConnectionString("connectionStringPG"));
NpgsqlCommand cmd = new NpgsqlCommand("OrionsBelt_UsersGetUser", conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.Add("@user_mail",user.Mail);
try {
conn.Open();
reader = cmd.ExecuteReader();
if( reader.HasRows ) {
reader.Read();
user.Nick = (string) getField("user_nick");
user.UserId = (int) getField("user_id");
if ( getField("user_ruler_id") != DBNull.Value )
user.RulerId = (int) getField("user_ruler_id");
user.EloRanking = (int) getField("user_rank");
user.RegistDate = (DateTime) getField("user_registDate");
user.LastLogin = (DateTime) getField("user_lastLogin");
user.Skin = (int) getField("user_skin");
user.Lang = (string) getField("user_lang");
user.ImagesDir = (string) getField("user_imagesDir");
user.Website = (string) getField("user_website");
user.Avatar = (string) getField("user_avatar");
user.Msn = (string) getField("user_msn");
user.Icq = (string) getField("user_icq");
user.Jabber = (string) getField("user_jabber");
user.Aim = (string) getField("user_aim");
user.Yahoo = (string) getField("user_yahoo");
user.AllianceId = (int) reader["user_alliance_id"];
user.AllianceRank = AllianceInfo.ToAllianceRank( reader["user_alliance_rank"].ToString() );
user.Wins = (int) reader["user_wins"];
user.Losses = (int) reader["user_losses"];
}else {
ExceptionLog.log("User não encontrado", "Mail: " + user.Mail==null?"null":user.Mail );
}
reader = null;
} catch( NpgsqlException e ) {
throw new AlnitakException("Erro ao executar o procedimento 'OrionsBelt_UsersGetUser' @ NpgsqlServerUserutility::fillUser ",e);
} finally {
conn.Close();
}
}
public static List <DBclasses.Pet> getAllPets()
{
List <DBclasses.Pet> result = new List <DBclasses.Pet>();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from pets order by id";
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
DBclasses.Pet row = new DBclasses.Pet();
row.id = (long)reader["id"];
row.kindOfAnimals = (int)reader["kindOfAnimals"];
row.addedByUser = (long)reader["addedByUser"];
row.addedDateTime = reader["addedDateTime"].ToString();
row.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
row.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
row.name = reader["name"].ToString();
row.photos = reader["photos"] as string[];
row.code = reader["code"].ToString();
row.sterilised = (bool)reader["sterilised"];
row.pasported = (bool)reader["pasported"];
row.isLosted = (bool)reader["isLosted"];
row.birthDate = reader["birthDate"].ToString();
row.roughlyBirth = (bool)reader["roughlyBirth"];
row.roughlyDay = (bool)reader["roughlyDay"];
row.roughlyMonth = (bool)reader["roughlyMonth"];
row.organizationID = (int)reader["organizationID"];
row.address = reader["address"].ToString();
row.isDisabled = (bool)reader["isDisabled"];
row.disableDescription = reader["disableDescription"].ToString();
row.price = (long)reader["price"];
row.callTimeFrom = reader["_callTimeFrom"].ToString();
row.callTimeTo = reader["_callTimeTo"].ToString();
row.region = (int)reader["region"];
row.phones = reader["phones"] as string[];
row.vaccinated = (bool)reader["vaccinated"];
row.sex = (bool)reader["sex"];
result.Add(row);
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
}//O(N)
public bool supplierExists(int id) //checks if supplier exists and return true if doesn't exists
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Suppliers\" WHERE \"ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id == 0);
}//O(1)
}//O(1)
public bool isProductExists(int id) //check if product exists, if doesn't returns true
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Products\" WHERE \"Product number\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id == 0);
}//O(1)
}//O(1)
public bool isCustomerOrdered(int id) //checks if customer has an open order, if does reuturn true
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Orderd\" WHERE \"Customer ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id != 0);
}//O(1)
}//O(1)
public bool isSupplierGotItems(int id) //check is supplier got items, return true if does
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Products\" WHERE \"Supplier\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id != 0);
}//O(1)
public static DBclasses.Organization getOrganizationById(int id)
{
DBclasses.Organization result = new DBclasses.Organization();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from organizations where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.name = reader["name"].ToString();
result.contactPerson = reader["contactPerson"].ToString();
result.emails = reader["emails"] as string[];
result.phones = reader["phones"] as string[];
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.addresses = reader["addresses"] as string[];
result.photo = reader["photo"].ToString();
result.aboutOrg = reader["aboutOrg"].ToString();
result.needHelp = reader["needHelp"].ToString();
result.donation = reader["donation"].ToString();
result.otherOrg = reader["otherOrg"].ToString();
result.linkToVK = reader["linkToVK"].ToString();
result.linkToFB = reader["linkToFB"].ToString();
result.linkToYT = reader["linkToYT"].ToString();
result.linkToTG = reader["linkToTG"].ToString();
result.linkToIG = reader["linkToIG"].ToString();
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public void CarregarAluno(NpgsqlDataReader dr)
{
Model.Aluno objAluno = new Model.Aluno();
if (dr.Read())
{
objAluno.Nome = dr.IsDBNull(dr.GetOrdinal("nm_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("nm_usuario"));
objAluno.Email = dr.IsDBNull(dr.GetOrdinal("email_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("email_usuario"));
objAluno.Senha = dr.IsDBNull(dr.GetOrdinal("senha_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("senha_usuario"));
objAluno.Id = dr.IsDBNull(dr.GetOrdinal("id_usuario")) ? 0 : dr.GetInt32(dr.GetOrdinal("id_usuario"));
Model.Session.Session.Aluno = objAluno;
}
else { throw new ExceptionDAL("Não foi possível logar usuário"); }
}
}//O(1)
public void checkCustomers(ref LinkedList <Customer> customers)
{
while (customers.Count != 0)
{
customers.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Customers\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
customers.AddLast(new Customer(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4)));
}
this.sort.sortCustomers(ref customers);
}//O(N)
}//O(1)
public void checkProducts(ref LinkedList <Product> products)
{
while (products.Count != 0)
{
products.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT \"Product number\", \"Name\", \"First name\" || ' ' || \"Last name\" as \"supplier\", \"ID\", \"Mail\" FROM \"Products\" JOIN \"Suppliers\" ON \"Products\".\"Supplier\" = \"Suppliers\".\"ID\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
products.AddLast(new Product(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetString(4)));
}
this.sort.sortProducts(ref products);
}//O(N)
}//O(1)
public void checkOrders(ref LinkedList <Order> orders)
{
while (orders.Count != 0)
{
orders.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT DISTINCT \"Order ID\", \"Product Number\", \"Products\".\"Name\", \"Customer ID\", \"First name\" || ' ' || \"Last name\" as \"Customer name\" , \"Quantity\" FROM \"Orderd\" JOIN \"Products\" ON \"Orderd\".\"Product Number\" = \"Products\".\"Product number\" JOIN \"Customers\" ON \"Orderd\".\"Customer ID\" = \"Customers\".\"ID\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
orders.AddLast(new Order(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetInt32(3), reader.GetString(4), reader.GetInt32(5)));
}
this.sort.sortOrders(ref orders);
}//O(N)
}//O(1)
public void checkSuppliers(ref LinkedList <Supply> suppliers) //get all suppliers
{
while (suppliers.Count != 0)
{
suppliers.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Suppliers\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
suppliers.AddLast(new Supply(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
}
this.sort.sortSuppliers(ref suppliers);
}//O(N)
}//O(1)
public void allSupplierItems(ref LinkedList <AllSupplierItems> allSupplierItems, int id) //shows all products from that seller
{
while (allSupplierItems.Count != 0)
{
allSupplierItems.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Products\" WHERE \"Supplier\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
allSupplierItems.AddLast(new AllSupplierItems(reader.GetInt32(0), reader.GetString(1)));
}
}
this.sort.sortSupplierItems(ref allSupplierItems);
}//O(N)
}//O(1)
public void customerOrder(ref LinkedList <CustomerOrder> allCustomerOrder, int id) //get all customer orders
{
while (allCustomerOrder.Count != 0)
{
allCustomerOrder.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT \"Order ID\", \"Product Number\", \"Name\", \"Quantity\" FROM \"Orderd\" JOIN \"Products\" ON \"Orderd\".\"Product Number\" = \"Products\".\"Product number\" WHERE \"Customer ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
allCustomerOrder.AddLast(new CustomerOrder(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetInt32(3)));
}
}
this.sort.sortCustomerOrder(ref allCustomerOrder);
}//O(N)
}//O(1)
//orders:
public void addOrder(int product, int customer, int amount)
{
int count = 0; //auto order pk
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Orderd\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read()) //count query result always 1 row, so it's one iteration
{
count = reader.GetInt32(0) + 1;
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("INSERT INTO \"Orderd\"(\"Order ID\", \"Product Number\", \"Customer ID\", \"Quantity\") VALUES (@PK, @ProID, @CusID, @quantity)", this.con)) {
execute.Parameters.AddWithValue("PK", count);
execute.Parameters.AddWithValue("ProID", product);
execute.Parameters.AddWithValue("CusID", customer);
execute.Parameters.AddWithValue("quantity", amount);
execute.ExecuteNonQuery();
}
}//O(1)
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);
}
}
public static DBclasses.User getUserById(long id)
{
DBclasses.User result = new DBclasses.User();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from users where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.rank = (int)reader["rank"];
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.email = reader["email"].ToString();
result.phone1 = reader["phone1"].ToString();
result.phone2 = reader["phone2"].ToString();
result.getAsksInfo = reader["getAsksInfo"] as int[];
result.photo = reader["photo"].ToString();
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
/// <summary>
/// Arma la lista de contactos con la información obtenida de la DB de PostgreSQL
/// </summary>
/// <param name="data">DataReader obtenido de la consulta select</param>
/// <returns>Lista de los contactos con su información respectiva</returns>
public List<Contacto> ArmarListaContactosPgSQL(NpgsqlDataReader data)
{
List<Contacto> listaContactos = new List<Contacto>();
// Lee la información y la almacena en cada campo del modelo de vista
while (data.Read())
{
Contacto contacto = new Contacto();
contacto.dpi = (String)data["dpi"] ?? String.Empty;
contacto.nombre = (String)data["nombre"] ?? String.Empty;
contacto.apellido = (String)data["apellido"] ?? String.Empty;
contacto.direccion = (String)data["direccion"] ?? String.Empty;
contacto.telefonoCasa = (String)data["telefono_casa"] ?? String.Empty;
contacto.telefonoMovil = (String)data["telefono_movil"] ?? String.Empty;
contacto.nombreContacto = (String)data["nombre_contacto"] ?? String.Empty;
contacto.numeroContacto = (String)data["numero_telefono_contacto"] ?? String.Empty;
listaContactos.Add(contacto);
}
return listaContactos;
}
public static DBclasses.ArchivePet getArchivePetById(int id)
{
DBclasses.ArchivePet result = new DBclasses.ArchivePet();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from archivepets where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.kindOfAnimals = (int)reader["kindOfAnimals"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.photos = reader["photos"] as string[];
result.homeFound = (bool)reader["homeFound"];
result.newOwnerName = reader["newOwnerName"].ToString();
result.newOwnerPhone = reader["newOwnerPhone"].ToString();
result.lastContactWithNewOwner = reader["lastContactWithNewOwner"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public static DBclasses.Ask getAskById(int id)
{
DBclasses.Ask result = new DBclasses.Ask();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from asks where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
result.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
result.categories = reader["categories"] as int[];
result.name = reader["name"].ToString();
result.desciption = reader["desciption"].ToString();
result.organizationID = (int)reader["organizationID"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
private void Construct(NpgsqlDataReader reader)
{
_id = Convert.ToInt32(reader[Table + "_" + Columns.Id]);
_name = Convert.ToString(reader[Table + "_" + Columns.Name]);
_shapefile = new Shapefile(Convert.ToInt32(reader[Table + "_" + Columns.ShapefileId]));
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT " +
"st_xmin(" + ShapefileGeometry.Columns.Geometry + ") as left," +
"st_xmax(" + ShapefileGeometry.Columns.Geometry + ") as right," +
"st_ymin(" + ShapefileGeometry.Columns.Geometry + ") as bottom," +
"st_ymax(" + ShapefileGeometry.Columns.Geometry + ") as top " +
"FROM " + _shapefile.GeometryTable);
reader = cmd.ExecuteReader();
double left = double.MaxValue;
double right = double.MinValue;
double bottom = double.MaxValue;
double top = double.MinValue;
while (reader.Read())
{
double l = Convert.ToDouble(reader["left"]);
if (l < left)
left = l;
double r = Convert.ToDouble(reader["right"]);
if (r > right)
right = r;
double b = Convert.ToDouble(reader["bottom"]);
if (b < bottom)
bottom = b;
double t = Convert.ToDouble(reader["top"]);
if (t > top)
top = t;
}
reader.Close();
DB.Connection.Return(cmd.Connection);
_boundingBox = new PostGIS.Polygon(new PostGIS.LineString(new List<PostGIS.Point>(new PostGIS.Point[]{
new PostGIS.Point(left, top, _shapefile.SRID),
new PostGIS.Point(right, top, _shapefile.SRID),
new PostGIS.Point(right, bottom, _shapefile.SRID),
new PostGIS.Point(left, bottom, _shapefile.SRID),
new PostGIS.Point(left, top, _shapefile.SRID)}), _shapefile.SRID), _shapefile.SRID);
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2browse(string browse_type_in, string browse_category_in, string browse_actor_in,
string browse_title_in, int batch_size_in, int customerid_out, ref int rows_returned,
ref int[] prod_id_out, ref string[] title_out, ref string[] actor_out, ref decimal[] price_out,
ref int[] special_out, ref int[] common_prod_id_out, ref double rt)
{
// Products table: PROD_ID INT, CATEGORY TINYINT, TITLE VARCHAR(50), ACTOR VARCHAR(50),
// PRICE DECIMAL(12,2), SPECIAL TINYINT, COMMON_PROD_ID INT
int i_row;
string data_in = null;
int[] category_out = new int[GlobalConstants.MAX_ROWS];
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
switch(browse_type_in)
{
case "category":
Browse_By_Category.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Category.Parameters["category_in"].Value = Convert.ToInt32(browse_category_in);
data_in = browse_category_in;
break;
case "actor":
Browse_By_Actor.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Actor.Parameters["actor_in"].Value = "\"" + browse_actor_in + "\"";
data_in = "\"" + browse_actor_in + "\"";
break;
case "title":
Browse_By_Title.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Title.Parameters["title_in"].Value = "\"" + browse_title_in + "\"";
data_in = "\"" + browse_title_in + "\"";
break;
}
// Console.WriteLine("Thread {0}: Calling Browse w/ browse_type= {1} batch_size_in= {2} data_in= {3}",
// Thread.CurrentThread.Name, browse_type_in, batch_size_in, data_in);
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
try
{
switch(browse_type_in)
{
case "category":
Rdr = Browse_By_Category.ExecuteReader();
break;
case "actor":
Rdr = Browse_By_Actor.ExecuteReader();
break;
case "title":
Rdr = Browse_By_Title.ExecuteReader();
break;
}
i_row = 0;
while (Rdr.Read())
{
prod_id_out[i_row] = Rdr.GetInt32(0);
category_out[i_row] = Rdr.GetInt16(1);
title_out[i_row] = Rdr.GetString(2);
actor_out[i_row] = Rdr.GetString(3);
price_out[i_row] = Rdr.GetDecimal(4);
special_out[i_row] = Rdr.GetInt16(5);
common_prod_id_out[i_row] = Rdr.GetInt32(6);
++i_row;
}
Rdr.Close();
rows_returned = i_row;
}
catch (NpgsqlException e)
{
Console.WriteLine("Thread {0}: Error in Browse: {1}", Thread.CurrentThread.Name, e.Message);
return(false);
}
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
return(true);
}
/// <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;
}
}
}
}
internal NpgsqlDataReader GetReader(CommandBehavior cb)
{
CheckConnectionState();
// Block the notification thread before writing anything to the wire.
using (_connector.BlockNotificationThread())
{
IEnumerable<IServerResponseObject> responseEnum;
NpgsqlDataReader reader;
_connector.SetBackendCommandTimeout(CommandTimeout);
if (_prepared == PrepareStatus.NeedsPrepare)
{
PrepareInternal();
}
if (_prepared == PrepareStatus.NotPrepared)
{
byte[] commandText = GetCommandText();
var query = new NpgsqlQuery(commandText);
// Write the Query message to the wire.
_connector.Query(query);
// Tell to mediator what command is being sent.
if (_prepared == PrepareStatus.NotPrepared)
{
_connector.Mediator.SetSqlSent(commandText, NpgsqlMediator.SQLSentType.Simple);
}
else
{
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
}
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader.
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread()
);
if (
CommandType == CommandType.StoredProcedure
&& reader.FieldCount == 1
&& reader.GetDataTypeName(0) == "refcursor"
)
{
// When a function returns a sole column of refcursor, transparently
// FETCH ALL from every such cursor and return those results.
var sw = new StringWriter();
while (reader.Read())
{
sw.WriteLine("FETCH ALL FROM \"{0}\";", reader.GetString(0));
}
reader.Dispose();
var queryText = sw.ToString();
if (queryText == "")
{
queryText = ";";
}
// Passthrough the commandtimeout to the inner command, so user can also control its timeout.
// TODO: Check if there is a better way to handle that.
query = new NpgsqlQuery(queryText);
// Write the Query message to the wire.
_connector.Query(query);
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader.
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread()
);
}
}
else
{
// Update the Bind object with current parameter data as needed.
BindParameters();
// Write the Bind, Execute, and Sync message to the wire.
_connector.Bind(_bind);
_connector.Execute(_execute);
_connector.Sync();
// Tell to mediator what command is being sent.
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader, possibly with a saved row description from Prepare().
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread(),
true,
_currentRowDescription
);
}
return reader;
}
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2purchase(int cart_items, int[] prod_id_in, int[] qty_in, int customerid_out,
ref int neworderid_out, ref bool IsRollback, ref double rt)
{
int i, j;
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
//Cap cart_items at 10 for this implementation of stored procedure
cart_items = System.Math.Min(10, cart_items);
// Extra, non-stored procedure query to find total cost of purchase
Decimal netamount_in = 0;
//Modified by GSK for parameterization of query below - Affects performance in case of Query Caching
//string cost_query = "select PROD_ID, PRICE from PRODUCTS where PROD_ID in (" + prod_id_in[0];
//for (i=1; i<cart_items; i++) cost_query = cost_query + "," + prod_id_in[i];
//cost_query = cost_query + ")";
////Console.WriteLine(cost_query);
//NpgsqlCommand cost_command = new NpgsqlCommand(cost_query, objConn);
//Parameterized query by GSK
string cost_query = "select PROD_ID, PRICE from PRODUCTS where PROD_ID in ( @ARG0";
for ( i = 1 ; i < cart_items ; i++ ) cost_query = cost_query + ", @ARG" + i;
cost_query = cost_query + ")";
NpgsqlCommand cost_command = new NpgsqlCommand ( cost_query , objConn );
string ArgHolder;
for ( i = 0 ; i < cart_items ; i++ )
{
ArgHolder = "@ARG" + i;
cost_command.Parameters.Add ( ArgHolder , NpgsqlDbType.Integer );
cost_command.Parameters[ArgHolder].Value = prod_id_in[i];
//Console.WriteLine (cost_command.Parameters[ArgHolder].Value);
}
Rdr = cost_command.ExecuteReader();
while (Rdr.Read())
{
j = 0;
int prod_id = Rdr.GetInt32(0);
while (prod_id_in[j] != prod_id) ++j; // Find which product was returned
netamount_in = netamount_in + qty_in[j] * Rdr.GetDecimal(1);
//Console.WriteLine(j + " " + prod_id + " " + Rdr.GetDecimal(1));
}
Rdr.Close();
// Can use following code instead if you don't want extra roundtrip to database:
// Random rr = new Random(DateTime.Now.Millisecond);
// Decimal netamount_in = (Decimal) (0.01 * (1 + rr.Next(40000)));
Decimal taxamount_in = (Decimal) 0.0825 * netamount_in;
Decimal totalamount_in = netamount_in + taxamount_in;
//Console.WriteLine(netamount_in);
Purchase.Parameters["customerid_in"].Value = customerid_out;
Purchase.Parameters["number_items"].Value = cart_items;
Purchase.Parameters["netamount_in"].Value = netamount_in;
Purchase.Parameters["taxamount_in"].Value = taxamount_in;
Purchase.Parameters["totalamount_in"].Value = totalamount_in;
Purchase.Parameters["prod_id_in0"].Value = prod_id_in[0]; Purchase.Parameters["qty_in0"].Value = qty_in[0];
Purchase.Parameters["prod_id_in1"].Value = prod_id_in[1]; Purchase.Parameters["qty_in1"].Value = qty_in[1];
Purchase.Parameters["prod_id_in2"].Value = prod_id_in[2]; Purchase.Parameters["qty_in2"].Value = qty_in[2];
Purchase.Parameters["prod_id_in3"].Value = prod_id_in[3]; Purchase.Parameters["qty_in3"].Value = qty_in[3];
Purchase.Parameters["prod_id_in4"].Value = prod_id_in[4]; Purchase.Parameters["qty_in4"].Value = qty_in[4];
Purchase.Parameters["prod_id_in5"].Value = prod_id_in[5]; Purchase.Parameters["qty_in5"].Value = qty_in[5];
Purchase.Parameters["prod_id_in6"].Value = prod_id_in[6]; Purchase.Parameters["qty_in6"].Value = qty_in[6];
Purchase.Parameters["prod_id_in7"].Value = prod_id_in[7]; Purchase.Parameters["qty_in7"].Value = qty_in[7];
Purchase.Parameters["prod_id_in8"].Value = prod_id_in[8]; Purchase.Parameters["qty_in8"].Value = qty_in[8];
Purchase.Parameters["prod_id_in9"].Value = prod_id_in[9]; Purchase.Parameters["qty_in9"].Value = qty_in[9];
// Console.WriteLine("Thread {0}: Calling Purchase w/ customerid = {1} number_items= {2}",
// Thread.CurrentThread.Name, customerid_out, cart_items);
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
bool deadlocked;
do
{
try
{
deadlocked = false;
neworderid_out = (int) Purchase.ExecuteScalar();
}
catch (NpgsqlException e)
{
if (e.Code == "40P01")
{
deadlocked = true;
Random r = new Random(DateTime.Now.Millisecond);
int wait = r.Next(1000);
Console.WriteLine("Thread {0}: Purchase deadlocked...waiting {1} msec, then will retry",
Thread.CurrentThread.Name, wait);
Thread.Sleep(wait); // Wait up to 1 sec, then try again
}
else if (e.Code == "P0001")
{
deadlocked=false;
neworderid_out = 0;
}
else
{
Console.WriteLine("Thread {0}: SQL Error {1} in Purchase: {2}",
Thread.CurrentThread.Name, e.Code, e.Message);
return(false);
}
}
} while (deadlocked);
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
if (neworderid_out == 0) IsRollback = true;
return(true);
}
internal NpgsqlDataReader GetReader(CommandBehavior cb)
{
CheckConnectionState();
// Block the notification thread before writing anything to the wire.
using (_connector.BlockNotificationThread())
{
State = CommandState.InProgress;
NpgsqlDataReader reader;
_connector.SetBackendCommandTimeout(CommandTimeout);
if (_prepared == PrepareStatus.NeedsPrepare)
{
PrepareInternal();
}
if (_prepared == PrepareStatus.NotPrepared)
{
var commandText = GetCommandText();
// Write the Query message to the wire.
_connector.SendQuery(commandText);
// Tell to mediator what command is being sent.
if (_prepared == PrepareStatus.NotPrepared)
{
_connector.Mediator.SetSqlSent(commandText, NpgsqlMediator.SQLSentType.Simple);
}
else
{
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
}
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread());
// For un-prepared statements, the first response is always a row description.
// For prepared statements, we may be recycling a row description from a previous Execute.
// TODO: This is the source of the inconsistency described in #357
reader.NextResult();
reader.UpdateOutputParameters();
if (
CommandType == CommandType.StoredProcedure
&& reader.FieldCount == 1
&& reader.GetDataTypeName(0) == "refcursor"
)
{
// When a function returns a sole column of refcursor, transparently
// FETCH ALL from every such cursor and return those results.
var sw = new StringWriter();
while (reader.Read())
{
sw.WriteLine(String.Format("FETCH ALL FROM \"{0}\";", reader.GetString(0)));
}
reader.Dispose();
var queryText = sw.ToString();
if (queryText == "")
{
queryText = ";";
}
// Passthrough the commandtimeout to the inner command, so user can also control its timeout.
// TODO: Check if there is a better way to handle that.
_connector.SendQuery(queryText);
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread());
// For un-prepared statements, the first response is always a row description.
// For prepared statements, we may be recycling a row description from a previous Execute.
// TODO: This is the source of the inconsistency described in #357
reader.NextResultInternal();
reader.UpdateOutputParameters();
}
}
else
{
// Bind the parameters, execute and sync
for (var i = 0; i < _parameters.Count; i++)
_parameters[i].Bind(_connector.NativeToBackendTypeConverterOptions);
_connector.SendBind(AnonymousPortal, _planName, _parameters, _resultFormatCodes);
_connector.SendExecute();
_connector.SendSync();
// Tell to mediator what command is being sent.
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
// Construct the return reader, possibly with a saved row description from Prepare().
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread(), true, _currentRowDescription);
if (_currentRowDescription == null) {
reader.NextResultInternal();
}
reader.UpdateOutputParameters();
}
return reader;
}
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2login(string username_in, string password_in, ref int customerid_out, ref int rows_returned,
ref string[] title_out, ref string[] actor_out, ref string[] related_title_out, ref double rt)
{
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
Login.Parameters["username_in"].Value = username_in;
Login.Parameters["password_in"].Value = password_in;
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
NpgsqlTransaction t ;
try
{
t = objConn.BeginTransaction();
Rdr = Login.ExecuteReader();
Rdr.Read();
customerid_out = Rdr.GetInt32(0);
}
catch (NpgsqlException e)
{
Console.WriteLine("Thread {0}: Error in Login: {1}", Thread.CurrentThread.Name, e.Message);
return (false);
}
int i_row = 0;
if ((customerid_out > 0) && Rdr.NextResult())
{
while (Rdr.Read())
{
title_out[i_row] = Rdr.GetString(0);
actor_out[i_row] = Rdr.GetString(1);
related_title_out[i_row] = Rdr.GetString(2);
++i_row;
}
}
Rdr.Close();
t.Commit();
rows_returned = i_row;
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
return(true);
}
public bool GetByPrimaryKey(string pKey)
{
string sQuery = "select * from tbm_carrepair WHERE repairid='" + pKey + "'";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
try
{
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("repairid")))
{
m_repairid = rdr.GetString(rdr.GetOrdinal("repairid"));
}
else
{
m_repairid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("carid")))
{
m_carid = rdr.GetString(rdr.GetOrdinal("carid"));
}
else
{
m_carid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opadd")))
{
m_opadd = rdr.GetString(rdr.GetOrdinal("opadd"));
}
else
{
m_opadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd")))
{
m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd"));
}
else
{
m_pcadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luadd")))
{
m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd"));
}
else
{
m_luadd = System.DateTime.MinValue;
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opedit")))
{
m_opedit = rdr.GetString(rdr.GetOrdinal("opedit"));
}
else
{
m_opedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit")))
{
m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit"));
}
else
{
m_pcedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luedit")))
{
m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit"));
}
else
{
m_luedit = System.DateTime.MinValue;
};
m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt"));
}
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
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);
}
public bool db_select_1(ref DataTable tab_1, int rows, int cols)
{
int var1 = 0;
object[] array = new object[cols];
if (db_query != "")
{
db_conn.Open();
db_command = new NpgsqlCommand(db_query, db_conn);
try
{
db_datareader = db_command.ExecuteReader();
//db_array_int_1 = new int[rows, db_datareader.FieldCount];
while (db_datareader.Read()) // reads rows
{
for (int i = 0; i < db_datareader.FieldCount; i++) // reads fields
{
array[i] = db_datareader[i];
}
tab_1.Rows.Add(array);
var1++;
}
}
catch (Exception e)
{
//this.eventer(e.Message);
MessageBox.Show(e.Message);
db_conn.Close();
return false;
}
db_conn.Close();
return true;
}
else
{
return false;
}
}
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));
}
}
}
public static void readDictionary(double top, double left, double bottom, double right)
{
//PythonInterface.useModel();
if (!dictProcessed)
conn.Open();
geo_dictionary.Clear();
HashSet<string> id = new HashSet<string>();
string sql = "SELECT id FROM location WHERE location.geom && ST_MakeEnvelope(" + left + ", " + top + ", " + right + ", " + bottom + ", 4326)";
command = new NpgsqlCommand(sql, conn);
dr = command.ExecuteReader();
while (dr.Read())
{
id.Add(dr[0].ToString());
}
foreach (var item in id)
{
sql = "SELECT name FROM entity WHERE id = " + item;
command = new NpgsqlCommand(sql, conn);
dr = command.ExecuteReader();
while (dr.Read())
{
foreach (var item2 in dr[0].ToString().Trim().Split(new Char[] { ',', ' ', '&', '-', ':', '[', ']', '/', '(', ')', '.' }))
{
geo_dictionary.Add(item2.ToLower());
}
}
}
foreach (var item in geo_dictionary)
Log.WriteLine(item);
if (dictProcessed) return;
dictProcessed = true;
//StreamReader file = new StreamReader(DictionaryPath); /// relative path
//List<string> Dictionary = new List<string>();
//string line;
//// read dictionary
//while ((line = file.ReadLine()) != null)
//{
// if (_IndexDictionary[line.Length] == null)
// _IndexDictionary[line.Length] = new List<string>();
// _IndexDictionary[line.Length].Add(line);
//}
}
/// <summary>Obtém um User dado o seu id</summary>
public override User getUser( int id )
{
NpgsqlConnection conn = new NpgsqlConnection(OrionGlobals.getConnectionString("connectionStringPG"));
NpgsqlCommand cmd = new NpgsqlCommand("OrionsBelt_UsersGetUserById", conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.Add("@id",id);
try {
conn.Open();
reader = cmd.ExecuteReader();
if( reader.Read() ) {
User user = new User();
user.Nick = (string) reader["user_nick"];
user.UserId = (int) reader["user_id"];
user.RulerId = (int) reader["user_ruler_id"];
user.EloRanking = (int) reader["user_rank"];
user.RegistDate = (DateTime) getField("user_registDate");
user.LastLogin = (DateTime) getField("user_lastLogin");
user.Skin = (int) reader["user_skin"];
user.Lang = (string) reader["user_lang"];
user.ImagesDir = (string) reader["user_imagesDir"];
user.Website = (string) reader["user_website"];
user.Avatar = (string) reader["user_avatar"];
user.Msn = (string) reader["user_msn"];
user.Icq = (string) reader["user_icq"];
user.Jabber = (string) reader["user_jabber"];
user.Aim = (string) reader["user_aim"];
user.Yahoo = (string) reader["user_yahoo"];
user.AllianceId = (int) reader["user_alliance_id"];
user.AllianceRank = AllianceInfo.ToAllianceRank( reader["user_alliance_rank"].ToString() );
user.Wins = (int) reader["user_wins"];
user.Losses = (int) reader["user_losses"];
return user;
}else
return null;
} catch( Exception e ) {
throw new AlnitakException("Erro ao executar o procedimento 'OrionsBelt_UsersGetUserById' @ NpgsqlServerUserutility::getUser : " + e.Message,e);
} finally {
conn.Close();
}
}
private static void DoDeriveParameters(NpgsqlCommand command)
{
// See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html
command.Parameters.Clear();
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
// This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
var serverVersion = command.Connector.ServerVersion;
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
else
{
query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
}
else
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname";
}
else
{
query = "select proargnames, proargtypes from pg_proc where proname = :proname";
}
procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
}
using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
{
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
{
NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
prm.Value = schemaName.Replace("\"", "").Trim();
}
string[] names = null;
int[] types = null;
string[] modes = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (serverVersion >= new Version("8.1.0"))
{
if (!rdr.IsDBNull(2))
{
types = rdr.GetValue(2) as int[];
}
if (!rdr.IsDBNull(3))
{
modes = rdr.GetValue(3) as String[];
}
}
if (types == null)
{
if (rdr.IsDBNull(1) || rdr.GetString(1) == "")
{
return; // Parameterless function
}
types = rdr.GetString(1).Split().Select(int.Parse).ToArray();
}
}
else
{
throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
}
command.Parameters.Clear();
for (var i = 0; i < types.Length; i++)
{
var param = new NpgsqlParameter();
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo))
{
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
param.NpgsqlDbType = typeInfo.NpgsqlDbType;
if (names != null && i < names.Length)
{
param.ParameterName = ":" + names[i];
}
else
{
param.ParameterName = "parameter" + (i + 1);
}
if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported)
{
param.Direction = ParameterDirection.Input;
}
else
{
switch (modes[i])
{
case "i":
param.Direction = ParameterDirection.Input;
break;
case "o":
param.Direction = ParameterDirection.Output;
break;
case "b":
param.Direction = ParameterDirection.InputOutput;
break;
case "v":
throw new NotImplementedException("Cannot derive function parameter of type VARIADIC");
case "t":
throw new NotImplementedException("Cannot derive function parameter of type TABLE");
default:
throw new ArgumentOutOfRangeException("proargmode", modes[i],
"Unknown code in proargmodes while deriving: " + modes[i]);
}
}
command.Parameters.Add(param);
}
}
}
public bool db_select(int array1, int rows, int cols)
{
int var1 = 0;
try
{
Array.Clear(db_array_int_1, 0, db_array_int_1.Length);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
if (db_query != "")
{
db_conn.Open();
db_command = new NpgsqlCommand(db_query, db_conn);
try
{
db_datareader = db_command.ExecuteReader();
db_array_int_1 = new int[rows, db_datareader.FieldCount];
while (db_datareader.Read()) // reads rows
{
for (int i = 0; i < db_datareader.FieldCount; i++) // reads fields
{
db_array_int_1[var1,i] = Convert.ToInt32(db_datareader[i]);
}
var1++;
}
}
catch (Exception e)
{
//this.eventer(e.Message);
MessageBox.Show(e.Message);
db_conn.Close();
return false;
}
db_conn.Close();
return true;
}
else
{
return false;
}
}
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>");
}
public void testDataReader(NpgsqlDataReader reader)
{
if (reader != null)
{
if (reader.HasRows)
{
while (reader.Read())
{// oid geomText
//string oid = reader["oid"].ToString();
//string geomText = reader["geomText"].ToString();
for (int i = 0; i < reader.FieldCount; i++)
{
string value = reader[i].ToString();
}
}
reader.Close();
reader.Dispose();
}
}
}
private static void DoDeriveParameters(NpgsqlCommand command)
{
// See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html
command.Parameters.Clear();
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
// This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
var serverVersion = command.Connector.ServerVersion;
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
else
{
query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
}
else
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname";
}
else
{
query = "select proargnames, proargtypes from pg_proc where proname = :proname";
}
procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
}
using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
{
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
{
NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
prm.Value = schemaName.Replace("\"", "").Trim();
}
string[] names = null;
uint[] types = null;
char[] modes = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (serverVersion >= new Version("8.1.0"))
{
if (!rdr.IsDBNull(2))
{
types = rdr.GetValue(2) as uint[];
}
if (!rdr.IsDBNull(3))
{
modes = rdr.GetValue(3) as char[];
}
}
if (types == null)
{
if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0)
{
return; // Parameterless function
}
types = rdr.GetFieldValue <uint[]>(1);
}
}
else
{
throw new InvalidOperationException(String.Format("{0} does not exist in pg_proc", command.CommandText));
}
}
command.Parameters.Clear();
for (var i = 0; i < types.Length; i++)
{
var param = new NpgsqlParameter();
throw new NotImplementedException();
/*
* // TODO: Fix enums, composite types
* var npgsqlDbType = c.Connector.TypeHandlerRegistry.GetNpgsqlDbTypeFromOid(types[i]);
* if (npgsqlDbType == NpgsqlDbType.Unknown)
* throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
* param.NpgsqlDbType = npgsqlDbType;
*
* if (names != null && i < names.Length)
* param.ParameterName = ":" + names[i];
* else
* param.ParameterName = "parameter" + (i + 1);
*
* if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported)
* param.Direction = ParameterDirection.Input;
* else
* {
* switch (modes[i])
* {
* case 'i':
* param.Direction = ParameterDirection.Input;
* break;
* case 'o':
* param.Direction = ParameterDirection.Output;
* break;
* case 'b':
* param.Direction = ParameterDirection.InputOutput;
* break;
* case 'v':
* throw new NotImplementedException("Cannot derive function parameter of type VARIADIC");
* case 't':
* throw new NotImplementedException("Cannot derive function parameter of type TABLE");
* default:
* throw new ArgumentOutOfRangeException("proargmode", modes[i],
* "Unknown code in proargmodes while deriving: " + modes[i]);
* }
* }
*
* command.Parameters.Add(param);
*/
}
}
}
/// <summary>
///
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public object npgsqldatareader2arraylist(NpgsqlDataReader dr)
{
ArrayList a = new ArrayList();
if (dr.HasRows || dr != null)
{
while (dr.Read())
{
for (int n = 0; n < dr.FieldCount; n++)
{
var x = dr.GetValue(n).ToString();
a.Add(x);
}
}
}
return a;
}
///<summary>
///
/// This method is reponsible to derive the command parameter list with values obtained from function definition.
/// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown.
/// Parameters name will be parameter1, parameter2, ...
/// For while, only parameter name and NpgsqlDbType are obtained.
///</summary>
/// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
public static void DeriveParameters(NpgsqlCommand command)
{
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
// This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
query =
"select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
}
else
{
query = "select proargnames, proargtypes from pg_proc where proname = :proname";
procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
}
using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
{
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
{
NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
prm.Value = schemaName.Replace("\"", "").Trim();
}
String[] names = null;
String[] types = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (!rdr.IsDBNull(1))
{
types = rdr.GetString(1).Split();
}
}
}
if (types == null)
{
throw new InvalidOperationException(
String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
command.Parameters.Clear();
for (Int32 i = 0; i < types.Length; i++)
{
// skip parameter if type string is empty
// empty parameter lists can cause this
if (!string.IsNullOrEmpty(types[i]))
{
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo))
{
command.Parameters.Clear();
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
if (names != null && i < names.Length)
{
command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType));
}
else
{
command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType));
}
}
}
}
}
/// <summary>
/// 若使用【读写分离】，查询【从库】条件cmdText.StartsWith("SELECT ")，否则查询【主库】
/// </summary>
/// <param name="readerHander"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public void ExecuteReader(Action <NpgsqlDataReader> readerHander, CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms)
{
DateTime dt = DateTime.Now;
NpgsqlCommand cmd = new NpgsqlCommand();
string logtxt = "";
DateTime logtxt_dt = DateTime.Now;
var pool = this.MasterPool;
bool isSlave = false;
//读写分离规则
if (this.SlavePools.Any() && cmdText.StartsWith("SELECT ", StringComparison.CurrentCultureIgnoreCase))
{
var availables = slaveUnavailables == 0 ?
//查从库
this.SlavePools : (
//查主库
slaveUnavailables == this.SlavePools.Count ? new List <NpgsqlConnectionPool>() :
//查从库可用
this.SlavePools.Where(sp => sp.IsAvailable).ToList());
if (availables.Any())
{
isSlave = true;
pool = availables.Count == 1 ? availables[0] : availables[slaveRandom.Next(availables.Count)];
}
}
Object <NpgsqlConnection> conn = null;
var pc = PrepareCommand(cmdType, cmdText, cmdParms, ref logtxt);
if (IsTracePerformance)
{
logtxt += $"PrepareCommand: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
Exception ex = null;
try {
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
if (isSlave)
{
//从库查询切换，恢复
bool isSlaveFail = false;
try {
if (pc.cmd.Connection == null)
{
pc.cmd.Connection = (conn = pool.Get()).Value;
}
//if (slaveRandom.Next(100) % 2 == 0) throw new Exception("测试从库抛出异常");
} catch {
isSlaveFail = true;
}
if (isSlaveFail)
{
if (conn != null)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
pool.Return(conn, ex);
if (IsTracePerformance)
{
logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
}
}
LoggerException(pool, cmd, new Exception($"连接失败，准备切换其他可用服务器"), dt, logtxt, false);
pc.cmd.Parameters.Clear();
ExecuteReader(readerHander, cmdType, cmdText, cmdParms);
return;
}
}
else
{
//主库查询
if (pc.cmd.Connection == null)
{
pc.cmd.Connection = (conn = pool.Get()).Value;
}
}
if (IsTracePerformance)
{
logtxt += $"Open: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
logtxt_dt = DateTime.Now;
}
using (NpgsqlDataReader dr = pc.cmd.ExecuteReader()) {
if (IsTracePerformance)
{
logtxt += $"ExecuteReader: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
while (true)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
bool isread = dr.Read();
if (IsTracePerformance)
{
logtxt += $" dr.Read: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
if (isread == false)
{
break;
}
if (readerHander != null)
{
object[] values = null;
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
values = new object[dr.FieldCount];
dr.GetValues(values);
logtxt += $" dr.GetValues: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
logtxt_dt = DateTime.Now;
}
readerHander(dr);
if (IsTracePerformance)
{
logtxt += $" readerHander: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms ({string.Join(",", values)})\r\n";
}
}
}
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
dr.Close();
}
if (IsTracePerformance)
{
logtxt += $"ExecuteReader_dispose: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
}
} catch (Exception ex2) {
ex = ex2;
}
if (conn != null)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
pool.Return(conn, ex);
if (IsTracePerformance)
{
logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
}
}
LoggerException(pool, pc.cmd, ex, dt, logtxt);
pc.cmd.Parameters.Clear();
}
// Get Candidate by ID
public CandidateInfo GetCandidateById(int?canId)
{
CandidateInfo can = new CandidateInfo();
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * from public.us_election_states where id=@ID";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new NpgsqlParameter("ID", canId));
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
can.ID = Convert.ToInt32(dr["ID"].ToString());
can.State = dr["name"].ToString();
can.Republicans = Convert.ToInt32(dr["percent_rep"].ToString());
can.Democrats = Convert.ToInt32(dr["percent_dem"].ToString());
}
conn.Close();
}
return(can);
}
