Devart Forums

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"

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();
            }