| public ExecuteReader ( ) : Npgsql.NpgsqlDataReader | ||
| Résultat | Npgsql.NpgsqlDataReader | |
public List<Product> getList()
{
using (NpgsqlConnection connection = getConnection())
{
using (NpgsqlCommand cmd = new NpgsqlCommand(SELECT_CMD, connection))
{
connection.Open();
List<Product> result = new List<Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product temp = new Product();
temp.id = reader.GetGuid(0);
temp.description = reader.GetString(1);
temp.productGroupID = reader.GetGuid(2);
temp.Unit = Units.FromName(reader.GetString(3));
if (!reader.IsDBNull(4))
{
temp.weight = (Decimal)reader.GetFloat(4);
}
temp.money = reader.GetDecimal(5);
temp.quantity = reader.GetInt32(6);
result.Add(temp);
}
}
return result;
}
}
}
public static List<GrilleRabais> getListGrilleRistourne(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<GrilleRabais> l = new List<GrilleRabais>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
GrilleRabais a = new GrilleRabais();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Nature = lect["nature_montant"].ToString();
a.Minimal = (Double)((lect["montant_minimal"] != null) ? ((!lect["montant_minimal"].ToString().Trim().Equals("")) ? lect["montant_minimal"] : 0) : 0);
a.Maximal = (Double)((lect["montant_maximal"] != null) ? ((!lect["montant_maximal"].ToString().Trim().Equals("")) ? lect["montant_maximal"] : 0) : 0);
a.Montant = (Double)((lect["montant_ristourne"] != null) ? ((!lect["montant_ristourne"].ToString().Trim().Equals("")) ? lect["montant_ristourne"] : 0) : 0);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
builder.Append('X', Conn.BufferSize);
var expected = builder.ToString();
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
cmd.Parameters.Add(new NpgsqlParameter("p", expected));
cmd.ExecuteNonQuery();
const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
var actual = reader[0];
Assert.That(actual, Is.EqualTo(expected));
if (IsSequential(behavior))
Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
Assert.That(reader[0], Is.EqualTo(expected));
Assert.That(reader.GetString(1), Is.EqualTo("foo"));
Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
Assert.That(reader.GetValue(3), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
}
/// <summary>
/// Get all CBS neighbourhoods, specifically its name, boundary (in WKT), and center.
/// </summary>
public void Enhance(IEnumerable<LocationDescription> locationDescriptions)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var streets = new List<string>();
foreach (var locationDescription in locationDescriptions)
{
var query = string.Format(Query, locationDescription.RdBoundary);
using (var command = new NpgsqlCommand(query, conn))
{
try
{
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
streets.Add(dr["straat"].ToString());
}
}
}
catch (SystemException e)
{
Console.WriteLine(e.Message);
}
}
locationDescription.Features.Add("straten", string.Join(";", streets));
}
}
}
public void SchemaOnly([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
using (var cmd = new NpgsqlCommand(
"SELECT 1 AS some_column;" +
"UPDATE data SET name='yo' WHERE 1=0;" +
"SELECT 1 AS some_other_column",
conn))
{
if (prepare == PrepareOrNot.Prepared)
cmd.Prepare();
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
Assert.That(reader.Read(), Is.False);
var t = reader.GetSchemaTable();
Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_column"));
Assert.That(reader.NextResult(), Is.True);
Assert.That(reader.Read(), Is.False);
t = reader.GetSchemaTable();
Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_other_column"));
Assert.That(reader.NextResult(), Is.False);
}
// Close reader in the middle
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
reader.Read();
}
}
}
public void LoadValuesFor(NeuralNetwork neuralNetwork)
{
var firstLayerWeights = new ValueList<double>();
var secondLayerWeights = new ValueList<double>();
var secondLayerThresholds = new ValueList<double>();
var thirdLayerThresholds = new ValueList<double>();
try
{
var connection = new NpgsqlConnection(ConnectionString);
connection.Open();
var command = new NpgsqlCommand { Connection = connection };
command.CommandText = "SELECT weight FROM pr4_weights_layer1 ORDER BY id ASC";
var reader = command.ExecuteReader();
while (reader.Read())
firstLayerWeights.Add(reader.GetDouble(0));
reader.Close();
command.CommandText = "SELECT weight FROM pr4_weights_layer2 ORDER BY id ASC";
reader = command.ExecuteReader();
while (reader.Read())
secondLayerWeights.Add(reader.GetDouble(0));
reader.Close();
command.CommandText = "SELECT threshold FROM pr4_thresholds_layer2 ORDER BY id ASC";
reader = command.ExecuteReader();
while (reader.Read())
secondLayerThresholds.Add(reader.GetDouble(0));
reader.Close();
command.CommandText = "SELECT threshold FROM pr4_thresholds_layer3 ORDER BY id ASC";
reader = command.ExecuteReader();
while (reader.Read())
thirdLayerThresholds.Add(reader.GetDouble(0));
neuralNetwork.SetWeightsForLayer(1, firstLayerWeights);
neuralNetwork.SetWeightsForLayer(2, secondLayerWeights);
neuralNetwork.SetThresholdsForLayer(2, secondLayerThresholds);
neuralNetwork.SetThresholdsForLayer(3, thirdLayerThresholds);
}
catch (Exception)
{
Console.WriteLine("There was a problem loading values from the database.");
throw;
}
}
public List<OrganizationType> QueryAllOnOrganizationType()
{
try
{
this.OpenConn();
string SQL = "SELECT * FROM Organization_Type";
NpgsqlCommand command = new NpgsqlCommand(SQL, conn);
NpgsqlDataReader dr = command.ExecuteReader();
List<OrganizationType> orgTypes = new List<OrganizationType>();
while (dr.Read())
{
OrganizationType type = new OrganizationType();
type.Name = dr["name"].ToString();
type.Description = dr["description"].ToString();
orgTypes.Add(type);
}
this.CloseConn();
return orgTypes;
}
catch (Exception ne)
{
Console.WriteLine("error on query table connecting to server, Error details {0}", ne.ToString());
return null;
}
}
public Registration Get(Guid id)
{
var registrationEvents = new List <IRegistrationEvent>();
using (var sqlConnection = new NpgsqlConnection(ConnectionString))
{
sqlConnection.Open();
var command = "SELECT * FROM schooled.Registration WHERE id = @id ORDER BY timestamp";
using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection))
{
sqlCommand.Parameters.AddWithValue("id", NpgsqlDbType.Uuid, id);
using (var reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var eventType = Convert.ToString(reader["event_type"]);
var theEvent =
(IRegistrationEvent)JsonConvert.DeserializeObject(
Convert.ToString(reader["event"]),
RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key);
registrationEvents.Add(theEvent);
}
}
}
}
return(registrationEvents.Any() ? new Registration(id, registrationEvents) : null);
}
public TableInfo GetTableInfo(string ConnectionString, string schema, string table_name)
{
var ret = new TableInfo();
var sql = @"SELECT column_name, column_default,is_nullable,data_type,character_maximum_length
FROM information_schema.columns
WHERE table_schema = :schema
AND table_name = :table";
var cnn = new Npgsql.NpgsqlConnection(ConnectionString);
var cmd = new Npgsql.NpgsqlCommand("select * from " + Globals.Compiler.GetQName(schema, table_name), cnn);
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName="schema",
// Value=schema
//});
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName = "table",
// Value = table_name
//});
DataTable tbl = new DataTable();
//var adp = new Npgsql.NpgsqlDataAdapter(cmd);
try
{
cnn.Open();
tbl = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly).GetSchemaTable();
//adp.Fill(tbl);
}
catch (Exception ex)
{
cnn.Close();
throw (ex);
}
finally
{
cnn.Close();
}
ret.Columns = tbl.Rows.Cast <DataRow>().Select(p => new ColumInfo()
{
Name = p["ColumnName"].ToString(),
IsUnique = (p["IsUnique"] == DBNull.Value) ? false : (bool)p["IsUnique"],
IsAutoIncrement = (p["IsAutoIncrement"] == DBNull.Value) ? false : (bool)p["IsAutoIncrement"],
IsKey = (p["IsKey"] == DBNull.Value) ? false : (bool)p["IsKey"],
AllowDBNull = (p["AllowDBNull"] == DBNull.Value) ? false : (bool)p["AllowDBNull"],
IsReadOnly = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsExpression = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsIdentity = (p["IsIdentity"] == DBNull.Value) ? false : (bool)p["IsIdentity"],
DataType = p["DataType"],
ColumnSize = (int)p["ColumnSize"]
//DefaultValue = p["column_default"].ToString(),
//IsAuto = p["column_default"].ToString().Split("(")[0] == "nextval",
//AutoConstraint = ((p["column_default"].ToString().Split("(")[0] == "nextval") ? p["column_default"].ToString().Split("(")[1].Split("::")[0] : "")
}).ToList();
return(ret);
}
private static void Run()
{
var connStr = ConfigurationManager.AppSettings["ConnectionString"];
using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
{
conn.Open();
updateConn.Open();
// Define a query returning a single row result set
using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
{
using (NpgsqlDataReader dr = command.ExecuteReader())
{
// Output rows
while (dr.Read())
{
CreateThumbnail((string)dr[1], (long)dr[2]);
using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
{
update.ExecuteNonQuery();
}
}
}
}
}
}
public void Roundtrip()
{
const string expected = "Something";
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Text);
var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Varchar);
var p3 = new NpgsqlParameter("p3", DbType.String);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected };
Assert.That(p2.DbType, Is.EqualTo(DbType.String));
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
Assert.That(p3.DbType, Is.EqualTo(DbType.String));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
p1.Value = p2.Value = p3.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(string)));
Assert.That(reader.GetString(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(i), Is.EqualTo(expected.ToCharArray()));
}
reader.Close();
cmd.Dispose();
}
// GET api/values
public IEnumerable<string> Get()
{
// return new string[] { "value1", "value2" };
var result = new List<string>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"SELECT name,amenity, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'pub\' LIMIT 10;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(reader.GetString(2));
}
}
}
}
return result;
}
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);
}
//Método responsável por retornar todas as categorias fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
public List <Categoria> getAll()
{
List <Categoria> retorno = new List <Categoria>();
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_categoria";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Prepare();
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
Categoria c = new Categoria();
c.Cod_categoria = dr.GetInt32(0);
c.Desc_categoria = dr.GetString(1);
retorno.Add(c);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(retorno);
}
//Método responsável por retornar uma Categoria cujo ID seja igual o passado pelo parâmetro.
public Categoria getOne(int id)
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_categoria WHERE cod_categoria = :id";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = id;
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
Categoria c = new Categoria();
c.Cod_categoria = dr.GetInt32(0);
c.Desc_categoria = dr.GetString(1);
return(c);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(null);
}
public IEnumerable<Routine> GetRoutines()
{
var list = new List<Routine>();
using (var sqlConnection = new NpgsqlConnection(connectionString))
{
using (var cmd = new NpgsqlCommand(@"
SELECT routine_name
FROM INFORMATION_SCHEMA.routines
WHERE
routine_schema <> 'pg_catalog'
and routine_schema <>'information_schema'
", sqlConnection))
{
sqlConnection.Open();
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Routine(reader.GetString(0)));
}
}
}
}
return list;
}
public static List<PlanCommission> getListPlanCommission(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<PlanCommission> l = new List<PlanCommission>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
PlanCommission a = new PlanCommission();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Nature = lect["nature_commission"].ToString();
a.Reference = lect["reference"].ToString();
a.Commissions = BLL.CommissionBll.Liste("select * from yvs_com_commission where plan = " + a.Id);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
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 List<Remise> getListRemise(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<Remise> l = new List<Remise>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
Remise a = new Remise();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Reference = lect["reference"].ToString();
a.BaseRemise = lect["base_remise"].ToString();
a.Permanent = Convert.ToBoolean((lect["permanent"] != null) ? (!lect["permanent"].ToString().Trim().Equals("") ? lect["permanent"].ToString().Trim() : "false") : "false");
a.Grilles = BLL.GrilleRemiseBll.Liste("SELECT * FROM yvs_com_grille_remise WHERE remise =" + a.Id);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
private void UpdateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("GolfID", typeof(string));
dt.Columns.Add("Förnamn", typeof(string));
dt.Columns.Add("Efternamn", typeof(string));
String sql = "SELECT golf_id, förnamn, efternamn FROM medlem;";
NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn);
NpgsqlDataReader ndr = command.ExecuteReader();
while (ndr.Read())
{
DataRow row = dt.NewRow();
row["GolfID"] = ndr["golf_id"];
row["Förnamn"] = ndr["förnamn"];
row["Efternamn"] = ndr["efternamn"];
dt.Rows.Add(row);
}
ndr.Close();
DataView dv = new DataView(dt);
//TODO Fix this filter, only golfId working
dv.RowFilter = "GolfID LIKE '" + golfId_textBox.Text + "*' AND Förnamn LIKE '" + firstName_textBox.Text + "*' AND Efternamn LIKE '" + lastName_textBox.Text + "*'";
//Set the component data
dataGridView.DataSource = dv;
//Set column header text
dataGridView.Columns[0].HeaderText = "Golf-ID";
dataGridView.Columns[1].HeaderText = "Förnamn";
dataGridView.Columns[2].HeaderText = "Efternamn";
}
public string markeratid()
{
//Skapar int nummer som innehåller en vald rad i dataGridView1.
int nummer = dataGridView1.CurrentRow.Index;
//Skapar strängen valtid.
//Strängen innehåller information om spelare. Hämtar golfid från databasen, tabellen person.
String valtid = "select golfid from person;";
//Skapar ett nytt Npgsql kommando, command10.
NpgsqlCommand command10 = new NpgsqlCommand(valtid, Huvudfönster.conn);
//Skapar en Npgsql "DataReader", dr4. Samt utför kommando, command10.
NpgsqlDataReader dr4 = command10.ExecuteReader();
//Skapar strängen id.
//Skapar int räknare och sätter denna till 0.
string id = "";
int räknare = 0;
//Skapar en while-loop.
while (dr4.Read())
{
//Gör en uträkning för golfid.
if (räknare == nummer)
{
id = dr4["golfid"].ToString();
}
räknare = räknare + 1;
}
//Stänger DataReader.
dr4.Close();
return id;
}
public void LogarUsuario(string email, string pass)
{
string strCom = "Select id_usuario, email_usuario , nm_usuario , senha_usuario from usuario where email_usuario = :email_usuario and senha_usuario = :senha_usuario";
try
{
using (NpgsqlConnection conexao = ConnectionFactory.createConnection())
{
Model.Aluno objAluno = new Model.Aluno();
NpgsqlCommand comm = new NpgsqlCommand(strCom, conexao);
comm.Parameters.AddWithValue("email_usuario", email);
comm.Parameters.AddWithValue("senha_usuario", pass);
NpgsqlDataReader dataReader = comm.ExecuteReader();
CarregarAluno(dataReader);
//carregarCursos
CursoDAL dalCurso = new CursoDAL();
List<Model.Curso> vListCurso = new List<Model.Curso>();
vListCurso = dalCurso.ObterCursoXAluno(Model.Session.Session.Aluno.Id);
Model.Session.Session.Aluno.CursoCadastrado = new List<Model.Curso>();
Model.Session.Session.Aluno.CursoCadastrado = vListCurso;
}
}
catch (Exception ex)
{
//log
throw new ExceptionDAL(ex.Message, ex);
}
}
/// <summary>
/// Обновить данные
/// </summary>
protected override void UpdateDefaultButton()
{
NpgsqlConnection npgSqlConnection = new NpgsqlConnection(WaveEnvirument.ConnectionString);
try
{
npgSqlConnection.Open();
//формируем запрос
using (NpgsqlCommand sqlQuery = new NpgsqlCommand("SELECT * FROM public.object_structure;", npgSqlConnection))
{
NpgsqlDataReader reader = sqlQuery.ExecuteReader();
lastUpdate = DateTime.Now;
while (reader.Read())
{
}
}
}
catch (Exception ex)
{
}
finally
{
}
}
public static TestReferralSurvey BuildAdditionalFromDataBaseData(string idReferral)
{
using (NpgsqlConnection connection = Global.GetSqlConnection())
{
string findPatient = "SELECT id_survey_organ, survey_comment, id_survey_type FROM public.referral WHERE id_referral = '" + idReferral + "' ORDER BY id_referral DESC LIMIT 1";
NpgsqlCommand person = new NpgsqlCommand(findPatient, connection);
using (NpgsqlDataReader personFromDataBase = person.ExecuteReader())
{
Survey p = new Survey();
while (personFromDataBase.Read())
{
//что делать с DateSpecified и Мисами?
if (personFromDataBase["survey_comment"] != DBNull.Value)
p.Comment = Convert.ToString(personFromDataBase["survey_comment"]);
TestReferralSurvey pers = new TestReferralSurvey(p);
pers.additional = TestAdditional.BuildAdditionalFromDataBaseData(idReferral);
if (personFromDataBase["id_survey_organ"] != DBNull.Value)
pers.surveyOrgan = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_organ"]));
if (personFromDataBase["id_survey_type"] != DBNull.Value)
pers.surveyType = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_type"]));
return pers;
}
}
}
return null;
}
public IEnumerable <EventStoreItem> GetAll()
{
var entities = new List <EventStoreItem>();
using (var sqlConnection = new NpgsqlConnection(ConnectionString))
{
sqlConnection.Open();
var command = "SELECT db_id, id, event_type, event, timestamp FROM schooled.Registration";
using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection))
{
using (var reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var eventType = Convert.ToString(reader["event_type"]);
var entity =
new EventStoreItem
{
Id = Guid.Parse(Convert.ToString(reader["id"])),
EventType = eventType,
TimeStamp = DateTimeOffset.Parse(Convert.ToString(reader["timestamp"])),
Event =
(IRegistrationEvent)JsonConvert.DeserializeObject(
Convert.ToString(reader["event"]),
RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key)
};
entities.Add(entity);
}
}
}
}
return(entities);
}
/// <summary>
/// This method reads users from database, looks for username match and checks
/// if pass is correct for given username.
/// </summary>
/// <returns> Bool representing success or failure of user login</returns>
public static bool Verify(string name, string pass)
{
using(NpgsqlConnection conn = Connect())
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("select name, pass from users order by id ASC", conn);
NpgsqlDataReader table = command.ExecuteReader();
List<Users> output = new List<Users>();
foreach (IDataRecord row in table)
{
if (name == (string)row["name"])
{
if(pass == (string)row["pass"])
{
// do something here to give authorization cookie?
// or if not here, then on client side?
return true;
}
else{
throw new System.ApplicationException("Name and pass do not match.");
// return false;
}
}
}
}
return false;
}
protected void Page_Load(object sender, EventArgs e)
{
if (SiteMaster.Rank == "2" && SiteMaster.LoggedIn)
{
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
"Password=root;Database=project56;");
conn.Open();
// Define query
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM users", conn);
// Execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
//Get rows and place in ArrayList
while (dr.Read())
{
maillist.Add(dr[0]);
ranklist.Add(dr[2]);
}
// Close connection
conn.Close();
for(int i=0;i<maillist.Count;i++)
{
maildrop.Items.Add(maillist[i].ToString()); //Add all emails to dropdown
}
}
}
public Main()
{
InitializeComponent();
string str = "Uid=postgres; Password=123; server=localhost; port=5432; Database=classmanagement;";
NpgsqlConnection conn = new NpgsqlConnection(str);
conn.Open();
String select = "SELECT name FROM subject";
NpgsqlCommand select_command = new NpgsqlCommand(select, conn);
NpgsqlDataReader reader = select_command.ExecuteReader();
while (reader.Read())
{
comboBox1.Items.Add(reader[0]);
comboBox3.Items.Add(reader[0]);
comboBox4.Items.Add(reader[0]);
comboBox6.Items.Add(reader[0]);
comboBox7.Items.Add(reader[0]);
}
dataGridView2.Refresh();
dataGridView2.ColumnCount = 2;
dataGridView2.Columns[0].Name = "Name";
dataGridView2.Columns[1].Name = "Surname";
reader.Close();
conn.Close();
}
//Mostra todos os registro do banco diretamente, sem fazer cast para objeto.
public void ShowAll()
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_diretor";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Prepare();
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
//Listar todos os campos automatizado
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write("{0} ", dr[i].ToString());
}
Console.Write("\n");
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
}
private void initDataListView()
{
dataListView.Items.Clear();
var date = dateTimePicker.Value.Date;
var dateQuery = "'" + date.Year + "-" + date.Month + "-" + date.Day + "'";
var query = "SELECT Data.start_t, Data.end_t, Category.name" +
" FROM Data" +
" JOIN Mem_cat ON (Data.mem_cat_id=Mem_cat.id)" +
" JOIN Category ON (Mem_cat.category_id=Category.id)" +
" WHERE Mem_cat.member_id=" + id +
" AND Data.day=" + dateQuery;
var cmd = new NpgsqlCommand(query, npgsqlConnection);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var startTime = dr.GetTimeSpan(0);
var endTime = dr.GetTimeSpan(1);
var category = dr.GetString(2);
var item = new ListViewItem(startTime.ToString());
item.SubItems.Add(endTime.ToString());
item.SubItems.Add(category);
dataListView.Items.Add(item);
}
dr.Close();
}
public string getXml(string testID)
{
string result = "";
try
{
NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, xml_answer as qXml FROM completed_test where id= @testID", conn);
cmd.Parameters.AddWithValue("testID", int.Parse(testID));
//cmd.Parameters.Add("testID", testID);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
result = dr["qXml"].ToString();
}
dr.Close();
conn.Close();
return result.TrimStart();
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
return "";
}
public static List<ModeloUsuario> ObtenerTodosUsuarios()
{
try
{
List<ModeloUsuario> usuarios = null;
NpgsqlCommand cmd = new NpgsqlCommand("select nombre,apellido,direccion,nombreusuario,email from usuario", Conexion.conexion);
Conexion.abrirConexion();
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
usuarios = new List<ModeloUsuario>();
while (reader.Read())
{
ModeloUsuario usuario = new ModeloUsuario();
usuario.nombre = reader["nombre"].ToString();
usuario.apellido = reader["apellido"].ToString();
usuario.direccion = reader["direccion"].ToString();
usuario.nombreUsuario = reader["nombreusuario"].ToString();
//usuario.contrasena = reader["contrasena"].ToString();
// usuario.nivelAcceso = reader["nivelacceso"].ToString();
usuario.email = reader["email"].ToString();
usuarios.Add(usuario);
}
}
Conexion.cerrarConexion();
return usuarios;
}
catch (Exception ex)
{
throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde");
}
}
static int limitForPointView = 10000; //количество точек для отображения на Zedgraph
#endregion Fields
#region Methods
public static List<List<string>> GetAllExperimentGroups(ADatabase db)
{
try
{
List<List<string>> result = new List<List<string>>();
string sql = "SELECT experimentgroups.experimentgroupsid, experimentgroups.datestart, experimentgroups.datestop - experimentgroups.datestart,(select Count(*) from experiments where experimentgroups.experimentgroupsid = experiments.experimentgroupsid) as cn ,(select Count(*) as pointcount from parametersvalues where parametersvalues.experimentid = (select experimentid from experiments where experiments.experimentgroupsID = experimentgroups.experimentgroupsID FETCH FIRST ROW ONLY) and parametersvalues.parametersid = 1), experimentgroups.powerdelay, experimentgroups.commdelay, experimentgroups.description FROM ExperimentGroups order by experimentgroups.experimentgroupsid";
NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
NpgsqlDataReader myreader = readCommand.ExecuteReader();
List<string> line;
while (myreader.Read())
{
line = new List<string>();
// load the combobox with the names of the people inside.
// myreader[0] reads from the 1st Column
for (int i = 0; i < myreader.FieldCount; i++)
{
line.Add(myreader[i].ToString());
}
result.Add(line);
}
myreader.Close(); // we are done with the reader
return result;
}
catch (Exception ex)
{
FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetAllExperimentGroups", ex.Message);
return null;
}
}
public string canHandIn(string testID)
{
string result = "";
NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, start_time, end_time FROM completed_test where id= @testID ", conn);
cmd.Parameters.AddWithValue("testID", int.Parse(testID));
//cmd.Parameters.Add("testID", testID);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
result = dr["start_time"].ToString();
if (dr["end_time"] != null)
{
}
}
else
{
dr.Close();
conn.Close();
return "FINNS INGET TEST";
}
dr.Close();
conn.Close();
TimeSpan diffTime = DateTime.Parse(DateTime.Now.ToString()) - DateTime.Parse(result);
if (diffTime.TotalMinutes > 29)
{
return "TIDEN DROG ÖVER";
}
return "OK";
}
public void MultipleCommands(bool[] queries)
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var sb = new StringBuilder();
foreach (var query in queries)
sb.Append(query ? "SELECT 1;" : "UPDATE data SET name='yo' WHERE 1=0;");
var sql = sb.ToString();
foreach (var prepare in new[] {false, true})
{
using (var cmd = new NpgsqlCommand(sql, conn))
{
if (prepare)
cmd.Prepare();
using (var reader = cmd.ExecuteReader())
{
var numResultSets = queries.Count(q => q);
for (var i = 0; i < numResultSets; i++)
{
Assert.That(reader.Read(), Is.True);
Assert.That(reader[0], Is.EqualTo(1));
Assert.That(reader.NextResult(), Is.EqualTo(i != numResultSets - 1));
}
}
}
}
}
}
public void Int32()
{
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer);
var p2 = new NpgsqlParameter("p2", DbType.Int32);
var p3 = new NpgsqlParameter { ParameterName = "p3", Value = 8 };
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(p3.DbType, Is.EqualTo(DbType.Int32));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
p1.Value = p2.Value = (long)8;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetInt32(i), Is.EqualTo(8));
Assert.That(reader.GetInt64(i), Is.EqualTo(8));
Assert.That(reader.GetInt16(i), Is.EqualTo(8));
Assert.That(reader.GetByte(i), Is.EqualTo(8));
Assert.That(reader.GetFloat(i), Is.EqualTo(8.0f));
Assert.That(reader.GetDouble(i), Is.EqualTo(8.0d));
Assert.That(reader.GetDecimal(i), Is.EqualTo(8.0m));
Assert.That(reader.GetValue(i), Is.EqualTo(8));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(8));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(int)));
Assert.That(reader.GetDataTypeName(i), Is.EqualTo("int4"));
}
reader.Dispose();
cmd.Dispose();
}
public static TestPerson BuildPersonFromDataBaseData(string idPerson, string MIS)
{
using (NpgsqlConnection connection = Global.GetSqlConnection())
{
string findPatient = "SELECT * FROM public.person WHERE id_person = '" + idPerson + "'";
NpgsqlCommand person = new NpgsqlCommand(findPatient, connection);
using (NpgsqlDataReader personFromDataBase = person.ExecuteReader())
{
Person p = new Person();
while (personFromDataBase.Read())
{
//что делать с DateSpecified и Мисами?
if (personFromDataBase["birthday"]!= DBNull.Value)
p.BirthDate = Convert.ToDateTime(personFromDataBase["birthday"]);
if (MIS == "")
p.IdPatientMis = null;
if ((personFromDataBase["family_name"] != DBNull.Value) || (personFromDataBase["given_name"] != DBNull.Value) || (personFromDataBase["middle_name"] != DBNull.Value))
{
p.HumanName = new HumanName();
if (personFromDataBase["family_name"] != DBNull.Value)
p.HumanName.FamilyName = Convert.ToString(personFromDataBase["family_name"]);
if (personFromDataBase["given_name"] != DBNull.Value)
p.HumanName.GivenName = Convert.ToString(personFromDataBase["given_name"]);
if (personFromDataBase["middle_name"] != DBNull.Value)
p.HumanName.MiddleName = Convert.ToString(personFromDataBase["middle_name"]);
}
TestPerson pers = new TestPerson(p);
if (personFromDataBase["id_sex"] != DBNull.Value)
pers.sex = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_sex"]));
return pers;
}
}
}
return null;
}
public void RecordsAffected()
{
var command = new NpgsqlCommand("insert into data (field_int4) values (7); insert into data (field_int4) values (8)", Conn);
using (var dr = command.ExecuteReader()) {
Assert.AreEqual(2, dr.RecordsAffected);
}
}
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);
}
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);
}
//Método responsável por retornar um Filme com todos os seus diretores cujo ID seja igual o passado pelo parâmetro.
public Filme getOne(int id)
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_filme WHERE cod_filme = :id";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = id;
NpgsqlDataReader dr = sql.ExecuteReader();
CategoriaDAO cdao = new CategoriaDAO();
while (dr.Read())
{
Filme f = new Filme();
f.Cod_filme = dr.GetInt32(0);
f.Nome_filme = dr.GetString(1);
f.Data = dr.GetDateTime(2);
//Como a classe filme tem um atributo do tipo Objeto Categoria,
//utilizo a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco me retorna, com esse ID passo pro
//método getOne que retorna um Objeto do tipo Categoria.
f.Categoria = cdao.getOne(3);
//É utilizado o método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
f.Diretores = this.getDiretores(f.Cod_filme);
return(f);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(null);
}
public static Npgsql.NpgsqlDataReader GetDataReader(Npgsql.NpgsqlCommand command)
{
if (command != null)
{
Npgsql.NpgsqlDataReader reader = default(Npgsql.NpgsqlDataReader);
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
command.Connection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
}
return(null);
}
//Método responsável por retornar todos os filmes e seus diretores fazendo um cast para objeto, retornando uma lista com todos os filmes do banco.
public List <Filme> getAll()
{
List <Filme> retorno = new List <Filme>();
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_filme";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Prepare();
NpgsqlDataReader dr = sql.ExecuteReader();
CategoriaDAO cdao = new CategoriaDAO();
//Esse trecho é executado pra cada filme.
while (dr.Read())
{
Filme f = new Filme();
f.Cod_filme = dr.GetInt32(0);
f.Nome_filme = dr.GetString(1);
f.Data = dr.GetDateTime(2);
//Como a classe filme tem um atributo do tipo Objeto Categoria,
//é utilizada a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco retorna, esse ID é passado para o
//método getOne que retorna um Objeto do tipo Categoria.
f.Categoria = cdao.getOne(3);
//É utilizado método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
f.Diretores = this.getDiretores(f.Cod_filme);
retorno.Add(f);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(retorno);
}
public void quartier_traitement(int code)
{
cnx.Close();
cnx.Open();
n.NpgsqlCommand cm = new n.NpgsqlCommand("create or replace view buffer_all as select num,ST_buffer(emp,puissance)as bf from antenne;", cnx);
cm.ExecuteNonQuery();
n.NpgsqlCommand cma = new n.NpgsqlCommand("create or replace view quart as select st_intersection(quartier.zone,bf)as inter from quartier,buffer_all where st_intersects(quartier.zone,bf) and quartier.code=" + code + "", cnx);
cma.ExecuteNonQuery();
n.NpgsqlCommand sauvegarde = new n.NpgsqlCommand("CREATE OR REPLACE VIEW cell as select ST_UNION(inter) as geom from quart", cnx);
sauvegarde.ExecuteNonQuery();
n.NpgsqlCommand c = new n.NpgsqlCommand(" select ST_AREA(geom) as valeur from cell", cnx);
double i = 0;
var rea = c.ExecuteReader();
while (rea.Read())
{
i = Double.Parse(rea["valeur"].ToString());
}
n.NpgsqlCommand ca = new n.NpgsqlCommand(" select ST_AREA(zone) as valeur from quartier where code=" + code + "", cnx);
double ia = 0;
var re = ca.ExecuteReader();
while (re.Read())
{
ia = Double.Parse(re["valeur"].ToString());
}
tc.Text = ((i / ia) * 100) + "%";
supc.Text = i + "";
supnc.Text = (ia - i) + "";
n.NpgsqlCommand de = new n.NpgsqlCommand(" select densite as valeur from quartier where code=" + code + "", cnx);
double iaa = 0;
var e = de.ExecuteReader();
while (e.Read())
{
iaa = Double.Parse(e["valeur"].ToString());
}
pc.Text = (iaa * i) + "";
pnc.Text = ((ia - i) * iaa) + "";
}
public string Comment(string json)
{
string status = String.Empty;
try
{
using (NpgsqlConnection con = new NpgsqlConnection(_connectionString))
{
con.Open();
var command = new Npgsql.NpgsqlCommand("_bt_comments_create", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "input";
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
parameter.Value = json;
command.Parameters.Add(parameter);
NpgsqlDataReader reader = command.ExecuteReader();
string resultset = string.Empty;
while (reader.Read())
{
if (reader[0] != null)
{
resultset = reader[0].ToString();
}
}
status = "ok";
status = resultset;
}
}
catch (Exception ex)
{
status = ex.Message;
}
return(status);
}
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);
}
//Método responsável por retornar um Diretor cujo ID seja igual o passado pelo parâmetro.
public Diretor getOne(int id)
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_diretor WHERE cod_diretor = :id";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = id;
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
Diretor d = new Diretor();
d.Cod_diretor = dr.GetInt32(0);
d.Nome_diretor = dr.GetString(1);
//É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
d.filmes = this.getFilmes(d.Cod_diretor);
return(d);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(null);
}
//Método responsável por retornar todos os diretores fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
public List <Diretor> getAll()
{
List <Diretor> retorno = new List <Diretor>();
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_diretor";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Prepare();
NpgsqlDataReader dr = sql.ExecuteReader();
//Esse trecho é executado para cada diretor
while (dr.Read())
{
Diretor d = new Diretor();
d.Cod_diretor = dr.GetInt32(0);
d.Nome_diretor = dr.GetString(1);
//É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
d.filmes = this.getFilmes(d.Cod_diretor);
retorno.Add(d);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(retorno);
}
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 T RunStoredFunction <T>(string procedure, Func <NpgsqlDataReader, T> mapColumns, List <Npgsql.NpgsqlParameter> paramsNpgsqlParameters = null)
{
if (paramsNpgsqlParameters == null)
{
paramsNpgsqlParameters = new List <NpgsqlParameter>();
}
using (var connection = new Npgsql.NpgsqlConnection(_databaseConfig.ConnectionString))
{
connection.Open();
using (var command = new Npgsql.NpgsqlCommand(procedure, connection))
{
command.CommandType = CommandType.StoredProcedure;
foreach (var parameter in paramsNpgsqlParameters)
{
command.Parameters.Add(parameter);
}
command.Prepare();
return(mapColumns(command.ExecuteReader()));
}
}
}
//Método responsavel por retornar todos os filmes do diretor.
public List <Filme> getFilmes(int cod_diretor)
{
bd.OpenConnection();
List <Filme> filmes = new List <Filme>();
String query = "SELECT * FROM tab_filme_diretor WHERE cod_diretor = :cod_diretor";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = cod_diretor;
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
FilmeDAO fdao = new FilmeDAO();
//Adiciona na lista de diretores que vai ser retornada, o diretor pelo metodo
//getOne que recebe como parâmetro o ID retornado do banco e faz o retorno do Objeto.
filmes.Add(fdao.getOne(dr.GetInt32(1)));
}
return(filmes);
}
private void FillOptimizedChangeLog(ref Npgsql.NpgsqlCommand command, ref List <OptimizedChangeLogElement> optimizedChangeLog, int startChangeId)
{
Logger.Info("FillOptimizedChangeLog START");
try
{
OrderedDictionary tempOptimizedChangeLog = new OrderedDictionary();
//Fill optimizedChangeLog
using (NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
string gmlId = dr.GetString(0);
// TODO: Fix dirty implementation later - 20121006-Leg: Uppercase First Letter
//gmlId = char.ToUpper(gmlId[0]) + gmlId.Substring(1);
string transType = dr.GetString(1);
long changelogId = dr.GetInt64(2);
OptimizedChangeLogElement optimizedChangeLogElement;
if (transType.Equals("D"))
{
//Remove if inserted or updated earlier in this sequence of transactions
if (tempOptimizedChangeLog.Contains(gmlId))
{
optimizedChangeLogElement = (OptimizedChangeLogElement)tempOptimizedChangeLog[gmlId];
string tempTransType = optimizedChangeLogElement.TransType;
tempOptimizedChangeLog.Remove(gmlId);
if (tempTransType.Equals("U"))
{
//Add delete if last operation was update.
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
else
{
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
else
{
if (!tempOptimizedChangeLog.Contains(gmlId))
{
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
}
}
//Fill optimizedChangeLog
foreach (var item in tempOptimizedChangeLog.Values)
{
optimizedChangeLog.Add((OptimizedChangeLogElement)item);
}
}
catch (System.Exception exp)
{
Logger.ErrorException("FillOptimizedChangeLog function failed:", exp);
throw new System.Exception("FillOptimizedChangeLog function failed", exp);
}
Logger.Info("FillOptimizedChangeLog END");
}
/// <summary>
/// 执行存储过程或Sql语句返回DataTable
/// </summary>
/// <param name="sql">存储过程名称或Sql语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="paras">参数</param>
/// <returns></returns>
public DataTable Run(string sql, CommandType commandType, params object[] paras)
{
DataTable table = new DataTable();
switch (this.DBType)
{
case DBTypeEnum.SqlServer:
SqlConnection con = this.Database.GetDbConnection() as SqlConnection;
SqlDataAdapter adapter = new SqlDataAdapter();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
adapter.SelectCommand = cmd;
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
adapter.Fill(table);
adapter.SelectCommand.Parameters.Clear();
}
break;
case DBTypeEnum.MySql:
MySqlConnection mySqlCon = this.Database.GetDbConnection() as MySqlConnection;
using (MySqlCommand cmd = new MySqlCommand(sql, mySqlCon))
{
if (mySqlCon.State == ConnectionState.Closed)
{
mySqlCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
MySqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
mySqlCon.Close();
}
break;
case DBTypeEnum.PgSql:
Npgsql.NpgsqlConnection npgCon = this.Database.GetDbConnection() as Npgsql.NpgsqlConnection;
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, npgCon))
{
if (npgCon.State == ConnectionState.Closed)
{
npgCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
npgCon.Close();
}
break;
case DBTypeEnum.SQLite:
case DBTypeEnum.Oracle:
var connection = this.Database.GetDbConnection();
var isClosed = connection.State == ConnectionState.Closed;
if (isClosed)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandTimeout = 2400;
command.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
command.Parameters.Add(param);
}
}
using (var reader = command.ExecuteReader())
{
table.Load(reader);
}
}
if (isClosed)
{
connection.Close();
}
break;
}
return(table);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particilar table.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType,
string connectionString, bool getSchemaTable, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
PostgreSqlClient.NpgsqlConnection pgConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
{
// Open the connection.
pgConnection.Open();
// Create the command and assign any parameters.
dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.PostgreSqlDataType, queryText), pgConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
// Get the schema from the data because the
// table has not predefined schema
if (getSchemaTable)
{
// Load the table after the schema is
// returned.
dataTable = dataReader.GetSchemaTable();
dataTable = new DataTable();
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
else
{
// Load the data into a table schema.
// Load the data into a table schema.
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
dataReader.Close();
}
// Close the database connection.
pgConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (pgConnection != null)
{
pgConnection.Close();
}
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The datatable schema to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
PostgreSqlClient.NpgsqlConnection pgConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
{
// Open the connection.
pgConnection.Open();
// Create the command and assign any parameters.
dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.PostgreSqlDataType, queryText), pgConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.Tables.AddRange(tables);
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
pgConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (pgConnection != null)
{
pgConnection.Close();
}
}
}
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();
}
}
}
///<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));
}
}
}
}
}
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);
}
}
}