Published 4/30/2007 1:45:04 AM  in  Postgresql

First the function:

CREATE OR REPLACE FUNCTION updatecity(p_in_cityname character varying(50),p_in_city_id int)
  RETURNS void AS
'update city set city=$1 where city_id=$2;
'
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION updatecity(p_in_cityname character varying(50),p_in_city_id int) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION updatecity(p_in_cityname character varying(50),p_in_city_id int) TO public;
GRANT EXECUTE ON FUNCTION updatecity(p_in_cityname character varying(50),p_in_city_id int) TO postgres;

And the C# code:

        public void Update(string newcity, int city_id)
        {
            Npgsql.NpgsqlConnection oConn = new Npgsql.NpgsqlConnection("Server=192.168.10.4;Port=5432;Userid=postgres;Password=stefan;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable;Database=pagila");
            oConn.Open();


            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand("update city set city=:city where city_id=:city_id", oConn);

            command.Parameters.Add(new Npgsql.NpgsqlParameter("city", DbType.String, 50));
            command.Parameters.Add(new Npgsql.NpgsqlParameter("city_id", DbType.Int16));
            command.Parameters[0].Value = newcity;
            command.Parameters[1].Value = city_id;

            command.ExecuteNonQuery();

            oConn.Close();

        }