execute a sql, devart only return 100 rows(expect 1800)

[ithunter]

the sql:

Code: Select all

select c."CustomerId",max("CustomerName") as "CustomerName",max("Sex") as "Sex",max("NickName") as "NickName",'' as "CustomerGroup",max("Birthday") as "Birthday",max("Company") as "Company",max("Mobile1") as "Mobile1",max("Mobile2") as "Mobile2",max("Phone") as "Phone",max("PhoneExt") as "PhoneExt",max("Fax") as "Fax",max("FaxExt") as "FaxExt",max("InternalExt") as "InternalExt",max("Email") as "Email",max("QQorMsn") as "QQorMsn",max("LastContactTime") as "LastContactTime",max("Address") as "Address",max("Remark") as "Remark",max("InsertUser") as "InsertUser",max("ModifyUser") as "ModifyUser" from (
      select
          distinct c."CustomerId",c."CustomerName",c."LetterAbbr",(case when c."Sex" = 'Y' then '男' when c."Sex" = 'N' then '女' END) as "Sex",c."NickName",(case when "Birthday" = '9999-12-31 23:59:59' then '' else cast("Birthday" as varchar) end) as "Birthday",c."Company",c."Photo",c."Mobile1",c."Mobile2",c."Phone",c."PhoneExt",c."Fax",c."FaxExt",c."InternalExt",c."DefaultNumberType",c."DefaultNumber",c."Email",c."QQorMsn",(case when "LastContactTime" = '9999-12-31 23:59:59' then '' else cast("LastContactTime" as varchar) end) as "LastContactTime",c."Address",c."Remark",c."FlagBlacklist",c."FlagDeleted",c."InsertUser",c."InsertTime",c."ModifyUser",c."ModifyTime",cast(c.XMIN as varchar(50))
        
      from "Cus_Customer" c inner join 
      (
      select cg."CustomerId",cg."GroupId",cg."FlagTrashed",cg."FlagBlacklist" from "Cus_CustomerGroup" cg inner join "Cus_UserGroupRight" ugr on cg."GroupId" = ugr."GroupId" and ugr."UserId" = 'admin'
      union
      select cg."CustomerId",cg."GroupId",cg."FlagTrashed",cg."FlagBlacklist" from "Cus_CustomerGroup" cg inner join "Cus_UserCustomerRight" ucr on cg."CustomerGroupId" = ucr."CustomerGroupId" and ucr."UserId" = 'admin'
      ) as tb on c."CustomerId" = tb."CustomerId" and c."FlagDeleted" = false and tb."GroupId" in (1,2,3,4,5))  c left join "Cus_CustomerField" cf on cf."CustomerId" = c."CustomerId" left join "Cus_CustomizedField" f on cf."FieldId" = f."FieldId" group by c."CustomerId" order by c."CustomerId"

the c# code like this:

Code: Select all

            StreamReader sr = File.OpenText("sql.txt");
            string sql = sr.ReadToEnd();
            sr.Close();

            PgSqlConnection conn = new PgSqlConnection();
            conn.ConnectionString = "User ID=postgres;Password=bangercss;Host=127.0.0.1;Port=5432;Database=Css;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=15;Unicode=true;";

            conn.Open();

            PgSqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = sql;

            int count = 0;

            try
            {
                IDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    count++;
                }
            }
            finally
            {
                conn.Close();
            }

the count is 100, but should be 1800

if i use "cmd.GetRecordCount();" instead of "cmd.ExecuteReader();"
the count is 1800

[StanislavK]

Please try setting the FetchAll property of the PgSqlCommand to true.

By default, this property is false to decrease the initial response time and network traffic.