如何为以下场景编写sqlite查询？ [英] How to write an sqlite query for the following scenario?

问题描述

这是我对wpf表单的查询



查询=从Customer_New中选择Cust_Id，Card_Number，Clients_Title，Address_Current，Phone_Number，Mobile_Number，其中1 = 1;

尝试

{

if（txt_title.Text！=）

查询+ =和Clients_Title赞'％'+ txt_title.Text +％';

if（txt_address.Text！=）

查询+ =和Address_Current赞'％'+ txt_address.Text +％';

if（txt_phone.Text！=）

查询+ =和Phone_Number喜欢'％'+ txt_phone.Text + ％';

if（txt_mobile.Text！=）

查询+ =和Mobile_Number赞'％'+ txt_mobile.Text +％' ;

if（cbo_location.Text！=）

查询+ =和AreaLocation赞'％'+ cbo_location.Text +％';

}



catch {}



我想报告查看器查询数据，比如我的wpf表格。这是我在报告查看器中尝试的查询



SELECT Cust_Id，Clients_Title，Card_Number，Key_Person，Address_Current，Phone_Number，Mobile_Number，AreaLocation

FROM Customer_New

WHERE（Clients_Title = @Clients_Title）或

（Address_Current = @Address_Current）或

（Phone_Number = @Phone_Number）或

（Mobile_Number = @Mobile_Number）或

（AreaLocation = @AreaLocation）



任何人都可以告诉我查询报告查看器，如wpf表单。注意： -



我无法在报表查看器中使用C＃控件。在报表查看器中我只能使用sql



报表查看器查询需要的是：



当where子句的所有字符串都为null时，我的报表查看器

应该选择查询是：



查询=从Customer_New中选择Cust_Id，Card_Number，Clients_Title，Address_Current，Phone_Number，Mobile_Number;



当任何两个where子句的字符串与相应的行db不匹配时，则不显示



将显示

最后如果只提供了where子句的一个条件，将进行选择

Here is my query for wpf form

Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New Where 1=1";
try
{
if (txt_title.Text != "")
Query += " and Clients_Title Like '%" + txt_title.Text + "%'";
if (txt_address.Text != "")
Query += " and Address_Current Like '%" + txt_address.Text + "%'";
if (txt_phone.Text != "")
Query += " and Phone_Number Like '%" + txt_phone.Text + "%'";
if (txt_mobile.Text != "")
Query += " and Mobile_Number Like '%" + txt_mobile.Text + "%'";
if (cbo_location.Text != "")
Query += " and AreaLocation Like '%" + cbo_location.Text + "%'";
}

catch { }

I want to report viewer query data like my wpf form . Here is the query that i am trying in report viewer

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (Clients_Title = @Clients_Title) OR
(Address_Current = @Address_Current) OR
(Phone_Number = @Phone_Number) OR
(Mobile_Number = @Mobile_Number) OR
(AreaLocation = @AreaLocation)

Can anyone tell me query for report viewer like wpf form . Note :-

I cant use cant use C# controls in report viewer.Here in report viewer i can only use sql

What is needed in report viewers query is:

When are all string of where clause are null then my report viewer
should select query is:

Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New ";

When any two string of where clause are not matching corresponding row db then nothing

will be displayed
Lastly, selection will be made if just one condition of where clause is provided

推荐答案

这可以吗？

Can this work?

<pre>SELECT 
	Cust_Id, Clients_Title, Card_Number, Key_Person, 
	Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM 
	Customer_New
WHERE 
	(@Clients_Title IS NULL OR Clients_Title = @Clients_Title) AND
	(@Address_Current IS NULL OR Address_Current = @Address_Current) AND
	(@Phone_Number IS NULL OR Phone_Number = @Phone_Number) AND
	(@Mobile_Number IS NULL OR Mobile_Number = @Mobile_Number) AND
	(@AreaLocation IS NULL OR AreaLocation = @AreaLocation)

我想你可以在sql查询中编写case语句，如下所示链接。试试这个



谢谢

Ganesh

I think u can write case statements in the sql query like in this Link. Try this

Thanks
Ganesh

我的建议是你创建一个存储过程。在该存储过程中，您将根据存储过程参数和执行该sp的报表查看器返回数据。例如

My suggestion is you create a stored procedure. Inside that stored procedure you return data based on stored procedure parameter and your report viewer you execute that sp. For example

create proc myproc
(
    @id int
    ,@title
)
as
begin
  if isnull(@id,'') == '' and isnull(@number,'') begin
        select all * from MyTable;
        return
  end
  else begin
       -- create user dynamic sql based on your stored procedure parameters and execute it exec sp_executeSql(@sql) just like you create sql inside wpf form.   
  end
end

如果您理解我的解决方案，请告诉我。

Let me know if you understand my solution.

这篇关于如何为以下场景编写sqlite查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！