需要查询和C#代码作为搜索条件 [英] Need query and c# code for search criteria
问题描述
如果用户要搜索产品，则可以输入任何一个选项示例，他可以输入产品ID，或者用户要输入produtid和prodcutname和供应商名称，这将是要搜索的查询.
信息:-我的桌子是这样的
桌子名称:-产品
(ProductID int主键不为null，ProductName varchar(50)不为null，ProductDescription varchar(100)，ProductPrice int不为null，
SupplierName varchar(50))
我的代码:-
if user want to search for product he can enter any one option example he can enter product id or user wants to enter both produtid and prodcutname and suppliername what will be the query to search.
Information:- My table is like this
table Name:- Product
(ProductID int primary key not null, ProductName varchar(50) not null, ProductDescription varchar(100), ProductPrice int not null,
SupplierName varchar(50))
my code :-
private DataSet ProductSearch()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
DataSet ds = new DataSet();
sb.Append("select * from Products");
if (!(string.IsNullOrEmpty(Convert.ToString(ProductID))))
{
sb.Append(" where ");
sb.Append("ProductID like''").Append(ProductID).Append("%''");
}
if (!(string.IsNullOrEmpty(ProductName)))
{
sb.Append(" where ");
sb.Append("ProductName like''").Append(ProductName).Append("%''");
}
if (!(string.IsNullOrEmpty(SupplierName)))
{
sb.Append(" where ");
sb.Append("SupplierName like''").Append(SupplierName).Append("%''");
}
try
{
OpenConnection();
SqlCommand command = new SqlCommand(sb.ToString(), connection);
command.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(command);
da.SelectCommand = command;
da.Fill(ds);
}
catch { }
finally
{
CloseConnection();
}
return ds;
}
如果以上代码无法正常工作，则任何人都可以提供此功能的代码.
谢谢
if above code is not working correc can any one provide code for this functionality.
Thanks
推荐答案
这是解决方案(整个项目)
请复制所有.net代码，并创建单独的项目，并调整数据库连接字符串.
请在您的数据库中运行存储过程
ASPX代码
Here is the solution (Whole project )
Please copy all .net code and make separate project and adjust database connection string as well
Please run stored procedure in to your database
ASPX code
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:label id="Label1" runat="server" text="ProductId" xmlns:asp="#unknown"></asp:label>
<asp:textbox id="txtProductId" runat="server" width="243px" xmlns:asp="#unknown"></asp:textbox>
<asp:label id="Label2" runat="server" text="ProductName" xmlns:asp="#unknown"></asp:label>
<asp:textbox id="txtProductName" runat="server" width="243px" xmlns:asp="#unknown"></asp:textbox>
<asp:label id="Label3" runat="server" text="Supplier Name" xmlns:asp="#unknown"></asp:label>
<asp:textbox id="txtSupplierName" runat="server" width="243px" xmlns:asp="#unknown"></asp:textbox>
<asp:button id="btnProductSearch" runat="server" onclick="btnProductSearch_Click" text="Product Search" xmlns:asp="#unknown">
Width="153px" />
<asp:gridview id="GridView1" runat="server">
</asp:gridview>
</asp:button></div>
</form>
</body>
</html>
C#代码
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace _346216_Need_query_and_csharp_code_for_search_criteria
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnProductSearch_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
DataTable dt = new DataTable();
try
{
conn = new
SqlConnection("Server=MDT765;DataBase=TST;User Id=TestUser;Password=TestUser@123;Integrated Security=SSPI");
conn.Open();
SqlCommand cmd = new SqlCommand(
"ProductSearch", conn);
cmd.CommandType = CommandType.StoredProcedure;
if( txtProductId.Text!= string.Empty)
cmd.Parameters.Add(
new SqlParameter("@ProductID", txtProductId.Text.ToString()));
if (txtProductName.Text != string.Empty)
cmd.Parameters.Add(
new SqlParameter("@ProductName", txtProductName.Text.ToString()));
if (txtSupplierName.Text != string.Empty)
cmd.Parameters.Add(
new SqlParameter("@SupplierName", txtSupplierName.Text.ToString()));
dt.Load(cmd.ExecuteReader());
GridView1.DataSource = dt;
GridView1.DataBind();
}
finally
{
}
}
}
}
SQL代码:存储过程
SQL Code : stored procedure
CREATE PROCEDURE ProductSearch
(
@ProductID int =NULL
,@ProductName varchar(50) = NULL
,@SupplierName varchar(50) = NULL
)
AS
IF (@ProductId is null AND @ProductName IS null AND @SupplierName is null )
BEGIN
RETURN -1
END
SELECT DISTINCT * FROM Product
WHERE ProductId=COALESCE(@ProductId,-1)
OR ProductName like COALESCE('%' + @ProductName + '%' ,'')
OR SupplierName =COALESCE('%' + @SupplierName + '%' ,'')
希望这会有所帮助，然后接受并投票回答
--Rahul D.
Hope this helps if yes then accept and vote the answer
--Rahul D.
我想给个主意:确保sb.ToString()是正确的语句return.
使用它:response.write(sb.ToString());这为您提供了选择查询，可以在Query-Analyzer中执行它.获得您需要的结果.我认为这对您有帮助！
I want give an idea : make sure the sb.ToString() is correct statement return.
Use it: response.write(sb.ToString()); this provide you select query execute it in Query-Analyzer. Get your needy results . I think it help u!!
这篇关于需要查询和C#代码作为搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
