Published 4/30/2007 3:32:17 AM  in  Postgresql

Lets change this case into a functionbased (stored procedure) solution.

CREATE OR REPLACE FUNCTION savecity(p_in_cityname character varying(50),p_in_country_id int)
  RETURNS BIGINT AS
'insert into city(city,country_id) values($1,$2);
select currval(\'city_city_id_seq\') ;'
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION savecity(p_in_cityname character varying(50),p_in_country_id int) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION savecity(p_in_cityname character varying(50),p_in_country_id int) TO public;
GRANT EXECUTE ON FUNCTION savecity(p_in_cityname character varying(50),p_in_country_id int) TO postgres;

And our serverside code:

        public void InsertSP(string city, int country_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("savecity(:city, :country_id)", oConn);
            command.CommandType = CommandType.StoredProcedure;

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

            long lNewId = (long)command.ExecuteScalar();

            oConn.Close();

        }