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

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

Postby ithunter » Wed 28 Jul 2010 16:15

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
ithunter
 
Posts: 2
Joined: Mon 05 Jul 2010 10:29

Postby StanislavK » Fri 30 Jul 2010 17:17

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL