Executing SQL query that outputs (print) through Entity Framework

Question

I was provided with an SQL query that outputs (PRINT) data neatly, the sticky part is that I need to execute it (and use the output later obviously) through my application which already makes use of EF4.1.

Here's what I have so far.

        string sqlQuery = string.Empty;
        _ctx.Connection.Open();
        DbCommand com = _ctx.Connection.CreateCommand();

        sqlQuery = @"declare @note varchar(1000)
                    set @note = (select
                    'Client: '+cast([NO_CLIENT] as varchar)+' - '+TABLE1.[nom]+
                    CHAR(13)+CHAR(10)+
                    'Adr_1 '+[ADR_1]+
                    CHAR(13)+CHAR(10)+
                    'Adr_2 '+[ADR_2]+
                    CHAR(13)+CHAR(10)+
                    'Adr_3 '+[ADR_3]+
                    CHAR(13)+CHAR(10)+
                    'Adr_4 '+[ADR_4]+
                    CHAR(13)+CHAR(10)+
                    'Contact: '+[CONTACT]+
                    CHAR(13)+CHAR(10)+
                    'Postal Code|Code Postal: '+substring([CODE_POSTAL],1,3)+' '+substring([CODE_POSTAL],4,3)+
                    CHAR(13)+CHAR(10)+
                    'Tel No: '+[TELEPHONE]+' Ext.: '+[POSTE_EXTENSION]+
                    CHAR(13)+CHAR(10)+
                    'Fax No: '+[NO_FAX]+
                    CHAR(13)+CHAR(10)+
                    'Clinic: '+[CLINIQUE_QUI_FACTURE]+
                    CHAR(13)+CHAR(10)+
                    'Langu.: '+ case [LANGUE] when 1 then 'Fr' when 2 then 'En' Else 'N/A'  end+
                    CHAR(13)+CHAR(10)+
                    case [ACTIF] when 0 then 'Non-Active|Inactif' when 1 then 'Active|Actif' Else 'N/A' end+
                    CHAR(13)+CHAR(10)+
                    'Account Man.|Chargé Cpte: '+CAST(TABLE1.[NO_VENDEUR_1] AS VARCHAR)+' - '+clve.[NOM]+
                    CHAR(13)+CHAR(10)+
                    'DATE REGISTERED|DATE ENREGISTREMENT: '+[DATE_ENREGISTREMENT]+
                    CHAR(13)+CHAR(10)+
                    '---------- MARKETING INFO ----------'+
                    CHAR(13)+CHAR(10)+
                    'Company|Cie: '+[TABLE2_NOM]+
                    CHAR(13)+CHAR(10)+
                    'Contact: '+[TABLE2_CONTACT]+
                    CHAR(13)+CHAR(10)+
                    'Tel.: '+[TABLE2_TELEPHONE]+
                    CHAR(13)+CHAR(10)+
                    'Adr_1: '+[TABLE2_ADR1]+
                   CHAR(13)+CHAR(10)+
                   'Adr_2: '+[TABLE2_ADR2]+
                   CHAR(13)+CHAR(10)+
                   'Adr_3: '+[TABLE2_ADR3]+
                   CHAR(13)+CHAR(10)+
                   'Adr-4: '+[TABLE2_ADR4]+
                   CHAR(13)+CHAR(10)+
                   'City|Ville: '+[TABLE2_VILLE_PROV]+
                   CHAR(13)+CHAR(10)+
                   'Postal Code|Code Postal: '+substring([TABLE2_CODE_POSTAL],1,3)+' '+substring([TABLE2_CODE_POSTAL],4,3)+
                   CHAR(13)+CHAR(10)+
                   'Fax No: '+[TABLE2_FAX_1]+
                   CHAR(13)+CHAR(10)+
                   'Ext.: '+[TABLE2_FAX_1_POSTE]+
                   CHAR(13)+CHAR(10)+
                   'Fax 2 :'+[TABLE2_FAX_2]+
                   CHAR(13)+CHAR(10)+
                   'Email1: '+[TABLE2_EMAIL1]+
                   CHAR(13)+CHAR(10)+
                   'Email2: '+[TABLE2_EMAIL2]

                    FROM TABLE1 
                        left join TABLE3 on TABLE1.no_vendeur_1 = TABLE3.no_vendeur
                        where [NO_CLIENT] = 2003)


                    print @note

                ";

        com.CommandText = @sqlQuery;
        DbDataReader rd = com.ExecuteReader();

The query executes fine in SQL Management Studio, but I get this exception when the application runs it: (looks like it is parsing it somehow)

The query syntax is not valid. Near term '@note', line 1, column 14.

What do you think am I doing wrong ? (Probably everything lol)

Answer 1

Here's what I ended up doing:

var r = _ctx.ExecuteStoreQuery<string>(@sqlQuery).FirstOrDefault();

The sqlQuery declares and sets value for @note, then select's it in the end.

r will contain flat text.

This has pointed me in the right direction: ExecuteStoreCommand returns -1 , EntityFramework , C#?

Answer 2

First, I pretty sure you cannot Execute a Print statement as a SqlReader. I believe if you did the following it would work:

select @note =

Additionally, I would change your SQL Statement to look like (my preference):

select @note =

Instead of

set @note =

Update 1

So I did the following:

using (SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;
                     Integrated Security=SSPI;MultipleActiveResultSets=true"))
{
  con.Open();
  using (SqlCommand com = new SqlCommand(string.Empty, con))
  {
    string blah = @"declare @note varchar(1000) 
      set @note = 'asdf' 
      select @note";
    com.CommandText = blah;
    com.ExecuteReader();
  }
}

And it executes without issue. So most likely there is either a problem on line 1 (as the error states), the DbCommand is parsing something incorrectly, or there is code you haven't provided that is changing the DbCommand.

Additionally, if you are using Entity Framework, why completely by-pass it instead of using it to create the same thing?