Daniel Alvarez a3daniel@live.com

Conexión ASP con PostgreSQL

PostgreSQL

CREATE TABLE personas (

nombre VARCHAR(100),

ci INT,

fecha_nacimiento DATE,

telefono_celular INT,

correo_electronico VARCHAR(100),

PRIMARY KEY (ci)

);

INSERT INTO personas VALUES ('Daniel Alvarez', 6120049, '1989-02-17', 70505575,

'a3daniel@live.com');

INSERT INTO personas VALUES ('Katherine Vasquez', 6159753, '1990-12-23', 74567891,

'katy123@hotmail.com');

INSERT INTO personas VALUES ('Raquel Quiroga', 7224654, '1988-01-12', 76546545,

'raquel8954@gmail.com');

SELECT * FROM personas;

CREATE OR REPLACE FUNCTION insertar (a VARCHAR, b INT, c DATE, d INT, e VARCHAR)

RETURNS BOOLEAN

AS

$$

BEGIN

INSERT INTO personas VALUES (a, b, c, d, e);

RETURN TRUE;

END;

$$

LANGUAGE plpgsql;

SELECT insertar('Nataly Sanchez', 7539515, '1978-03-03', 70508956,

'natynaty@hotmail.com');

DROP FUNCTION buscar

CREATE OR REPLACE FUNCTION buscar_persona (VARCHAR(100), OUT n VARCHAR(100), OUT ci

INT, OUT c VARCHAR(100))

RETURNS SETOF RECORD

AS

$$

SELECT nombre, ci, correo_electronico FROM personas WHERE nombre = $1;

$$

LANGUAGE SQL;

SELECT buscar('Daniel Alvarez');

Daniel Alvarez a3daniel@live.com

Visual C#

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Npgsql; using System.Data; public partial class Interfaz : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void BotonGuardar_Click(object sender, EventArgs e) { NpgsqlConnection conexion = new NpgsqlConnection(); conexion.ConnectionString = "server=localhost; port=5432; database=a3d; user id=postgres; password=contrasenia"; NpgsqlCommand comando = new NpgsqlCommand(); comando.Connection = conexion; comando.CommandText = "INSERT INTO personas VALUES (@a, @b, @c, @d, @e)"; comando.Parameters.AddWithValue("@a", TextBox1.Text); comando.Parameters.AddWithValue("@b", int.Parse(TextBox2.Text)); comando.Parameters.AddWithValue("@c", TextBox3.Text); comando.Parameters.AddWithValue("@d", int.Parse(TextBox4.Text)); comando.Parameters.AddWithValue("@e", TextBox5.Text); conexion.Open(); comando.ExecuteNonQuery(); conexion.Close(); } protected void BotonListar_Click(object sender, EventArgs e) { NpgsqlConnection conexion = new NpgsqlConnection(); conexion.ConnectionString = "server=localhost; port=5432; database=a3d; user id=postgres; password=contrasenia"; NpgsqlCommand comando = new NpgsqlCommand(); comando.Connection = conexion; comando.CommandText = "SELECT * FROM personas"; NpgsqlDataAdapter adaptador = new NpgsqlDataAdapter(); adaptador.SelectCommand = comando; DataSet ds = new DataSet(); adaptador.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); } protected void BotonGuardarSP_Click(object sender, EventArgs e) { NpgsqlConnection conexion = new NpgsqlConnection(); conexion.ConnectionString = "server=localhost; port=5432; database=a3d; user id=postgres; password=contrasenia"; NpgsqlCommand comando = new NpgsqlCommand(); comando.Connection = conexion;

Daniel Alvarez a3daniel@live.com

comando.CommandText = "insertar (@a, @b, @c, @d, @e)"; comando.CommandType = CommandType.StoredProcedure; comando.Parameters.AddWithValue("@a", TextBox1.Text); comando.Parameters.AddWithValue("@b", int.Parse(TextBox2.Text)); comando.Parameters.AddWithValue("@c", TextBox3.Text); comando.Parameters.AddWithValue("@d", int.Parse(TextBox4.Text)); comando.Parameters.AddWithValue("@e", TextBox5.Text); conexion.Open(); comando.ExecuteNonQuery(); conexion.Close(); } protected void BotonBuscarSP_Click(object sender, EventArgs e) { // NO FUNCIONA !!! NpgsqlConnection conexion = new NpgsqlConnection(); conexion.ConnectionString = "server=localhost; port=5432; database=a3d; user id=postgres; password=contrasenia"; NpgsqlCommand comando = new NpgsqlCommand(); comando.Connection = conexion; comando.CommandText = "buscar (@a)"; comando.CommandType = CommandType.StoredProcedure; comando.Parameters.AddWithValue("@a", TextBox6.Text); NpgsqlDataAdapter adaptador = new NpgsqlDataAdapter(); adaptador.SelectCommand = comando; DataSet ds = new DataSet(); adaptador.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); } }