c#数据集SQL查询

Question

我使用cloture.xsd。我把它连接到我的报告Cloture_Tete。

在SQL中起作用:

SELECT     NO_ORDRE, CODE_CLIENT, CODE_DEST, STATUT_ORDRE, RS_NOM, ADRESSE, CP, VILLE, PAYS, INSEE, SIRET, TEL, FAX, EMAIL, ENLEV_UNITE, ENLEV_CREMB, 
                      ENLEV_DECL, MODAL_MODE, MODAL_PORT, MODAL_SPEC, LIVRS_EXPRS, LIVRS_SIGN, LIVRS_SAMD, NBR_COLIS, POID, POID_COR, CODE_MAG, OBS, 
                      DATE_CREE, DATE_MODIF, CREE_PAR, MODIF_PAR, REF_EXPED, AUTRE_REF, DATE_CLOTUR, AGENCE, TRANSPORTEUR, CHAUFFEUR, FACTURATION
FROM         ORDRE
WHERE     (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (1, 2))

，但当我通过c#:

protected void Page_Load(object sender, EventArgs e)
    {
        string CHAUFFEUR = Request.QueryString["CHAUFFEUR"];
        string CODE_CLIENT = Request.QueryString["ClientID"];
        string ClotList = Request.QueryString["ClotList"];
        try
        {
            using (var connectionWrapper = new Connexion())
            {
                ReportViewer_Cloture.Report = new Cloture_Tete(Convert.ToInt32(CHAUFFEUR), CODE_CLIENT, ClotList);

            }
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
    }

与我的预定义Select在cloture.xsd:

SELECT ADRESSE, AGENCE, AUTRE_REF, CHAUFFEUR, CODE_CLIENT, CODE_DEST, CODE_MAG, CP, CREE_PAR, DATE_CLOTUR, DATE_CREE, DATE_MODIF, EMAIL, ENLEV_CREMB, ENLEV_DECL, ENLEV_UNITE, FACTURATION, FAX, INSEE, LIVRS_EXPRS, LIVRS_SAMD, LIVRS_SIGN, MODAL_MODE, MODAL_PORT, MODAL_SPEC, MODIF_PAR, NBR_COLIS, NO_ORDRE, OBS, PAYS, POID, POID_COR, REF_EXPED, RS_NOM, SIRET, STATUT_ORDRE, TEL, TRANSPORTEUR, VILLE FROM ORDRE WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (@Trans))

和我看我的SQL Profiler它产生:

exec sp_executesql N'SELECT ADRESSE, AGENCE, AUTRE_REF, CHAUFFEUR, CODE_CLIENT, CODE_DEST, CODE_MAG, CP, CREE_PAR, DATE_CLOTUR, DATE_CREE, DATE_MODIF, EMAIL, ENLEV_CREMB, ENLEV_DECL, ENLEV_UNITE, FACTURATION, FAX, INSEE, LIVRS_EXPRS, LIVRS_SAMD, LIVRS_SIGN, MODAL_MODE, MODAL_PORT, MODAL_SPEC, MODIF_PAR, NBR_COLIS, NO_ORDRE, OBS, PAYS, POID, POID_COR, REF_EXPED, RS_NOM, SIRET, STATUT_ORDRE, TEL, TRANSPORTEUR, VILLE FROM ORDRE WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (@Trans))',N'@Trans varchar(50)',@Trans='1,2'

不工作，为什么不同的只是(TRANSPORTEUR IN (1, 2))在SQL和(TRANSPORTEUR IN (@Trans))',N'@Trans varchar(50)',@Trans='1,2'

thanks you in advance

Answer 1

当你使用IN (@Trans)时，你在说(本质上)IN ('1,2')，这与说IN (1,2)非常不同。具体来说，它比较的是单个字符串，而不是多个值。您可能会发现有各种"拆分"UDF实现(即调用UDF并对数据进行内部连接)。