I have a quite large SQL query that works as expected when i´m exceuting it in MS SQL. However when I run it from C# it does not fetch any rows (dataTable does not return any rows). If I instead use a simple query, for e.g. "SELECT * Accounts FROM TableX" from C#, dataTable returns all the rows it should do. I also tried to remove all the spaces from the SQL query so everything was on the same line, but without any change. Here is the code:
internal void GetData()
{
try
{
using (SqlConnection connection = new SqlConnection(connectionBuilder.ToString())) //1. Open connection
{
connection.Open();
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(GetSqlString(), connection))
{
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
foreach (DataRow dataRow in dataTable.Rows)
{
var field1 = dataRow[0];
var field2 = dataRow[1];
Logger.Log(field1 + " " + field2);
}
}
}
}
catch (Exception ex)
{
utilityProvider.Log("Error" + ex.ToString());
}
}
private string GetSqlString()
{
return @"SELECT
Field1 = subscr.cind_recipient,
Field2 = COALESCE(
a.name, s.cind_name,
CASE WHEN c.cind_is_protected_identity = 0 THEN c.fullname
ELSE ISNULL(c.cind_protected_firstname, '') + ' ' + ISNULL(c.cind_protected_lastname, '') END ),
Field3 = COALESCE(CASE WHEN c.cind_is_protected_identity <> 1 THEN c.address1_line3
ELSE c.cind_protected_address1_line3 END,
s.cind_postal_line3),
Field4 = COALESCE(CASE WHEN c.cind_is_protected_identity = 0 THEN c.address1_line1
ELSE c.cind_protected_address1_line1 END,
a.address2_line1, s.cind_postal_line1),
Field5 = COALESCE(CASE WHEN c.cind_is_protected_identity = 0 THEN c.address1_line2
ELSE c.cind_protected_address1_line2 END,
a.address2_line2, s.cind_postal_line2),
Field6 = COALESCE(CASE WHEN c.cind_is_protected_identity = 0 THEN mpc1.cind_postalcode
ELSE mpc.cind_postalcode END,
a.address1_postalcode, s.cind_postal_postalcode),
Field7 = COALESCE(CASE WHEN c.cind_is_protected_identity = 0 THEN mpc1.cind_city
ELSE mpc.cind_city END,
a.address1_city, s.cind_postal_city),
Field8 = c.cind_member_number,
Field9 = ISNULL(COALESCE(c.cind_union_section_idname, a.cind_mml_mub_union_section_idname), a1.cind_mml_mub_union_section_idname),
Field10 = CASE WHEN c.cind_is_protected_identity <> 1 THEN c.cind_soc_sec_no
ELSE c.cind_protected_cind_soc_sec_no END,
Field11 = COALESCE(a.cind_organization_no, s.cind_organization_no),
Field12 = c.gendercodename,
Field13 = cind_count,
Field14 = k1.cind_name,
Field15 = k1.cind_number,
Field16 = k2.cind_name,
Field17 = k2.cind_number,
Field18 = 'sam',
Field19 = subscr.villa_free_exname
FROM dbo.Filteredcind_mml_mub_subscription subscr
INNER JOIN Filteredcind_mml_mub_service svc
ON subscr.cind_mml_mub_service_id = svc.cind_mml_mub_serviceid
AND svc.cind_code = 'PE002'
LEFT JOIN Filteredcind_mml_mub_site s
ON subscr.cind_mml_mub_site_id = s.cind_mml_mub_siteid
LEFT JOIN FilteredAccount a
ON subscr.cind_account_id = a.accountid
LEFT JOIN FilteredAccount a1
ON a1.accountid = s.cind_account_id
INNER JOIN FilteredContact c
ON subscr.cind_contact_id = c.contactid
AND c.statecode = 0
LEFT JOIN Filteredcind_mml_mub_postalcity mpc
ON c.cind_protected_cind_postalcity_id = mpc.cind_mml_mub_postalcityid
LEFT JOIN Filteredcind_mml_mub_postalcity mpc1
ON c.cind_postalcity_id = mpc1.cind_mml_mub_postalcityid
LEFT JOIN Filteredcind_mml_mub_county lan
ON c.cind_county_id = lan.cind_mml_mub_countyid
LEFT JOIN Filteredcind_mml_mub_country land
ON lan.cind_country_id = land.cind_mml_mub_countryid
LEFT JOIN (Filteredcind_mml_mub_membership m1
INNER JOIN (SELECT mt1.cind_mml_mub_membership_typeid
FROM Filteredcind_mml_mub_membership_type mt1
WHERE mt1.cind_code = 'PRDI-45') mtt1
ON mtt1.cind_mml_mub_membership_typeid = m1.cind_mml_mub_membership_type_id)
ON c.contactid = m1.cind_contact_id
AND (m1.statuscode = 1
OR m1.statuscode = 434578)
AND m1.statecode = 0
LEFT JOIN Filteredcind_mml_mub_local_union k1
ON m1.cind_mml_mub_local_union_id = k1.cind_mml_mub_local_unionid
LEFT JOIN (Filteredcind_mml_mub_membership m2
INNER JOIN (SELECT mt2.cind_mml_mub_membership_typeid
FROM Filteredcind_mml_mub_membership_type mt2
WHERE mt2.cind_code = 'EXTR-01') mtt2
ON mtt2.cind_mml_mub_membership_typeid = m2.cind_mml_mub_membership_type_id)
ON c.contactid = m2.cind_contact_id
AND (m2.statuscode = 1
OR m2.statuscode = 126670001)
AND m2.statecode = 0
LEFT JOIN Filteredcind_mml_mub_local_union k2
ON m2.cind_mml_mub_local_union_id = k2.cind_mml_mub_local_unionid
WHERE subscr.statuscode = 1
AND subscr.statecode = 0";
}
