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); } } }