TonyV wrote:Would I be able to call the stored function through PgSqlCommand? Is there a way I can pass an array as a parameter to the stored function using PgSqlCommand?
Yes, it is possible. Here is an example of how it can be done.
Script for table creation:
Code: Select all
CREATE TABLE dept
(
deptno integer NOT NULL,
dname character varying(14),
loc character varying(13),
CONSTRAINT dept_pkey PRIMARY KEY (deptno)
);
And the following stored procedure will insert rows into the dept table:
Code: Select all
create or replace function dept_multi_insert(id integer[], name text[], loc text[])
returns void as'
declare
i int:=1;
length int:=array_length(id,1);
begin
while i<length+1 loop
insert into dept (deptno, dname, loc) values (id[i], name[i], loc[i]);
i:=i+1;
end loop;
end
' LANGUAGE 'plpgsql';
We can create 3 array parameters - one for each column, fill them and execute the stored procedure:
Code: Select all
PgSqlConnection conn = new PgSqlConnection("host=***;port=***;user id=***;password=***;");
conn.Open();
object[] arr_id = new object[3] { 1008, 1009, 1010 };
object[] arr_name = new object[3] { "name1", "name2", "name3" };
object[] arr_loc = new object[3] { "Vegas", "Hollywood", "NY" };
PgSqlArray id = new PgSqlArray(arr_id, PgSqlType.Int, 1, 3);
PgSqlArray name = new PgSqlArray(arr_name,PgSqlType.Text, 1, 3);
PgSqlArray loc = new PgSqlArray(arr_loc,PgSqlType.Text, 1, 3);
PgSqlCommand comm = new PgSqlCommand("dept_multi_insert", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add("id", PgSqlType.Array).Value = id;
comm.Parameters.Add("name", PgSqlType.Array).Value = name;
comm.Parameters.Add("loc", PgSqlType.Array).Value = loc;
comm.ExecuteNonQuery();
conn.Close();
After executing this code, three rows will be inserted into the dept table:
Code: Select all
1008 name1 Vegas
1009 name2 Hollywood
1010 name3 NY