following is only program fragment, original program structure is from sample
named Duwamish in ms vs.net 2002.
/////////////////////////////////
private const String ID_PARM = "@id";
private const String NAME_PARM = "@name";
public UserData GetUserById(int id)
{if ( dataAdapter == null ){ throw new System.ObjectDisposedException( GetType().FullName );}
UserData userData = new UserData();
dataAdapter.SelectCommand = GetUserByIdCommand();dataAdapter.SelectCommand.Parameters[ID_PARM].Value = id;
dataAdapter.Fill(data);
return userData;
}
private SelectCommand GetUserByIdCommand()
{if ( getUserCommand == null) { selectUserByIdCommand = new SelectCommand("user_select_by_id",
Configuration.ConnectDB()); selectUserByIdCommand.CommandType = CommandType.StoredProcedure;
ParameterCollection params = selectUserByIdCommand.Parameters; params.Add(new Parameter(ID_PARM,
DbType.Int32));}return selectUserByIdCommand;
}
/////////////////////////////////
---------------------------------
CREATE TABLE users ( id serial NOT NULL, name character varying(32) NOT NULL
);
---------------------------------
CREATE TYPE user_set AS (id integer,name character varying(32)
);
---------------------------------
CREATE FUNCTION user_select_by_id("@id" int4)
RETURNS SETOF user_set
AS '
declare rec record;
begin
for rec in select * from users where id = "@id"loop return next rec;end loop;return;
end; ' LANGUAGE plpgsql;
---------------------------------
Thanks & Regards!
Arnold.Zhu
2004-11-26
