Does Entity Developer support array parameters to stored fun

Does Entity Developer support array parameters to stored fun

Postby TonyV » Fri 25 May 2012 22:15

I have written a couple of stored functions in my database that take a UUID and an array of UUIDs as parameters. They compile fine in PostgreSQL.

When I try to update my database model in Entity Developer and include these functions in the model, I get the error:

Code: Select all
The Type ARRAY is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.


How do I fix the model to get rid of the error?

Here is the code for one of the functions:

Code: Select all
CREATE OR REPLACE FUNCTION security.SynchRolesForGroup(grpid uuid, roleids uuid[]) RETURNS void AS
$BODY$
DECLARE
    RoleId      UUID;
BEGIN
    DELETE FROM security.aspnetx_RolesInGroups WHERE GroupId = GrpId;

    -- Now loop over all of the UUIDs in the RoleIds parameter
    FOREACH RoleId IN ARRAY RoleIds LOOP
        INSERT INTO security.aspnetx_RolesInGroups
            ( GroupId, RoleId )
        VALUES
            ( GrpId, RoleId );
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;


Tony
TonyV
 
Posts: 74
Joined: Wed 25 May 2011 15:03

Re: Does Entity Developer support array parameters to stored

Postby Shalex » Mon 28 May 2012 14:37

Entity Framework supports only primitive types: http://msdn.microsoft.com/en-us/library/ee382832.aspx. From the Entity Framework's point of view, an array is not a primitive type.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Does Entity Developer support array parameters to stored

Postby TonyV » Tue 29 May 2012 12:41

Thank you for getting back to me so promptly.

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? Something like

Code: Select all
Guid[] ids = new Guid[] { . . . };
using ( PgSqlCommand cmd = new PgSqlCommand( "SELECT function( ids );" ) {
    cmd.Parameters.AddWithValue( "ids", ids );

    PgSqlReader reader = cmd.ExecuteReader();
    while ( reader.Read() ) {
        ...
    }
}


Thanks

Tony
TonyV
 
Posts: 74
Joined: Wed 25 May 2011 15:03

Re: Does Entity Developer support array parameters to stored

Postby Pinturiccio » Wed 30 May 2012 12:27

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
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Does Entity Developer support array parameters to stored

Postby TonyV » Wed 30 May 2012 12:34

Thank you very much! I believe we can make this work. I can probably call the stored function through the context's ExeciteStoreCommand method, so I won't have to create the PgSqlConnection and PgSqlCommand objects.

Tony
TonyV
 
Posts: 74
Joined: Wed 25 May 2011 15:03


Return to dotConnect for PostgreSQL