c# - 如何将参数传递给 SP 并检索选择字段给中继器

Question

我使用 vs2010 和 ms sql 以及 (linq to sql) SP 来传递 p1 p2 p3 并将选定的数据检索到中继器问题是转发器忽略 p1,p2,p3 并只显示电影表中的所有数据我有 3 个表:(电影、导演、电影导演)

SP:

ALTER PROCEDURE [dbo].[SelectMovie]
    @P1 nvarchar(50),
    @P2 nvarchar(255)= null,
    @P3 nvarchar(255)= null   AS SET NOCOUNT ON 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
    SELECT  
        Movie.Movie_ID
        , Movie.title
        , Movie.image
        , Movie.actors
        , Movie.description
        ,Director.name 
    FROM 
        Movie 
            INNER JOIN Movie-director ON Movie.Movie_ID = Movie-director.Movie_ID 
            INNER JOIN Director ON Movie-director.Director_ID = Director.Director_ID 
    WHERE 
        (title LIKE '%' + (@p1) +'%') 
        OR 
        (actors LIKE '%' + (@p2) +'%') 
        OR 
        (cat_ID LIKE '%' + (@p3) +'%') 

ASPX:

<asp:Repeater ID="Repeater1" runat="server" >
<HeaderTemplate> </HeaderTemplate>
<ItemTemplate>
    <div style="width:100%;">
        <div class="excerpt">
            <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "Movie_ID")%>" class="thumb" title="An image">
                <img src="<%# DataBinder.Eval(Container.DataItem, "image")%>" alt="Post" style="opacity: 1; float:left; width:80px ; height:100px; border:3px solid #fff ; margin:5px;">
            </a> 
            <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "Movie_ID")%>" class="header">
                <h6>
                    <%# DataBinder.Eval(Container.DataItem, "title")%>
                    ( <%# DataBinder.Eval(Container.DataItem, "actors")%>)
                </h6>
            </a> 
            <div style="padding:5px;">
                <%# DataBinder.Eval(Container.DataItem, "description")%>
            </div>  
            <div style="padding:5px;">
                <%# DataBinder.Eval(Container.DataItem, "name")%>
            </div> 
        </div> 
    </div>
    <br /> 
    <hr />
</ItemTemplate> 

C#:

CDTDataContext dc = new CDTDataContext(); 
protected void Page_Load(object sender, EventArgs e)
{
        if (!IsPostBack){ }
} 
protected void Button1_Click(object sender, EventArgs e) 
{
    string Cat_ID = DropDownList1.SelectedValue;
    string keyword1 = TextBox2.Text;
    string keyword2 = TextBox3.Text;
    int? cid = int.Parse(cat_id); 
    Repeater1.DataSource = dc.SelectMovie(keyword1, keyword2, cid); 
    Repeater1.DataBind();
}

Answer 1

我认为您的SQL查询是错误的，因为OR的如果您传递空字符串，您将匹配Table中的所有记录:

ALTER PROCEDURE [dbo].[SelectMovie] @P1 NVARCHAR(50)
    ,@P2 NVARCHAR(255) = NULL
    ,@P3 NVARCHAR(255) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT Movie.Movie_ID
    ,Movie.title
    ,Movie.IMAGE
    ,Movie.actors
    ,Movie.description
    ,Director.NAME
FROM Movie
INNER JOIN Movie - director ON Movie.Movie_ID = Movie - director.Movie_ID
INNER JOIN Director ON Movie - director.Director_ID = Director.Director_ID
WHERE (title LIKE '%' + (@p1) + '%') --title has to be matched
    AND (
           (
                    actors LIKE '%' + (@p2) + '%' AND @p2 IS NOT NULL) --match only then when @p2 is not null
        OR (
                    cat_ID = @p3 OR @p3 IS NULL --if @p3 IS NULL then do not filter by @p3
           )
        )