1. dotConnect for PostgreSQL
7.3.146.0
2. PostgreSQL server version
"PostgreSQL 9.3.2, compiled by Visual C++ build 1600, 64-bit"
3.The DDL script
Code: Select all
CREATE TYPE public.test_resulttype AS
(thely_id integer,
nev character varying,
irsz smallint,
var_nev character varying,
kozter_nev character varying,
kozjell_nev character varying,
cim_hazsz character varying,
cim_kieg character varying,
nyilvszam character varying,
stat_nev character varying,
tipus_nev character varying,
uzem_id integer);
ALTER TYPE public.test_resulttype
OWNER TO postgres;
CREATE OR REPLACE FUNCTION public.test(p_2 character varying, p_0 character varying, p_1 character varying, p_4 integer, p_5 character varying, p_3 integer, p_6 integer DEFAULT 0, p_7 integer DEFAULT 10)
RETURNS SETOF test_resulttype AS
$BODY$
BEGIN
RETURN QUERY SELECT
1::integer,
'nev'::varchar(200),
2::smallint,
'var_nev'::varchar(200),
'kozter_nev'::varchar(200),
'kozjell_nev'::varchar(200),
'com_hazsz'::varchar(200),
'cim_kieg'::varchar(200),
'nyilvszam'::varchar(200),
'stat_nev'::varchar(200),
'tipus_nev'::varchar(200),
3::integer;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION public.test(character varying, character varying, character varying, integer, character varying, integer, integer, integer)
OWNER TO postgres;
I've added a DataSet at solution explorer.
I've used visual designer:
1. Add-->TableAdapter
2. Select Connection String
3. Use existing stored procedure
4. Select--> test(public)
5. Unselect Fill a DataTable
6. Select Retrun a DataTable
7. Finish
The return types are all ok (correct order and types)
But the GetData methode is generated incorrectly
DataSet1.Designer.cs
Code: Select all
public virtual DataSet1.testDataTable GetData(string p_0, string p_1, string p_2, global::System.Nullable<int> p_3, global::System.Nullable<int> p_4, string p_5, global::System.Nullable<int> p_6, global::System.Nullable<int> p_7) {
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((p_0 == null)) {
this.Adapter.SelectCommand.Parameters[0].Value = global::System.DBNull.Value;
...
As you can see, the GetData methode has the parameters in alphabetical order.
If I leave everything as is and try to call GetData:
Code: Select all
using PostGresParameterOrder.DataSet1TableAdapters;
namespace PostGresParameterOrder
{
class Program
{
static void Main(string[] args)
{
DataSet1TableAdapters.testTableAdapter adapter = new testTableAdapter();
var ret = adapter.GetData("", "", "", 3, 4, "", 5, 6);
}
}
}
I get the following exception:
Code: Select all
System.FormatException was unhandled
HResult=-2146233033
Message=Input string was not in a correct format.
Source=mscorlib
StackTrace:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ToInt32(Object value)
at Devart.Data.PostgreSql.PgSqlUtils.ParamValueToBytes(String version, Encoding encoding, PgSqlType pgSqlType, Object val, Int32 typeOid, Boolean integerDateTime)
at Devart.Data.PostgreSql.PgSqlCommand.a(String A_0, Encoding A_1, ArrayList A_2, Boolean A_3)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PostGresParameterOrder.DataSet1TableAdapters.testTableAdapter.GetData(String p_0, String p_1, String p_2, Nullable`1 p_3, Nullable`1 p_4, String p_5, Nullable`1 p_6, Nullable`1 p_7) in c:\users\gimp\documents\visual studio 2010\Projects\PostGresParameterOrder\PostGresParameterOrder\DataSet1.Designer.cs:line 1345
at PostGresParameterOrder.Program.Main(String[] args) in c:\users\gimp\documents\visual studio 2010\Projects\PostGresParameterOrder\PostGresParameterOrder\Program.cs:line 14
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
If I use "Parameters Collection Editor" and arrange the parameters everthing works as expected.