数据库中的高级搜索 [英] Advance Search in Database

问题描述

hi
我想在数据库中使用Sql Command搜索（SELECT *来自tabale1 Like ....）

现在，我需要用+搜索。

例如，当我需要罚款[Javad Emsakpour]时，我在文本框搜索中写[ja + emsak]。

解决方案

我个人会避免构建SELECT语句在客户端代码中。它打开了几个安全问题，主要是SQL注入攻击向量，但是如果你将单个路由作为一个存储过程的集合进入你的数据，你可以锁定SQL帐户，除了执行存储过程之外不允许任何东西，这样如果有人设法从您的应用程序外部连接到您的数据库，他们仍然只能运行您的应用程序将运行的SP。



所以，我会建立一个搜索存储过程，包含所有选项。



但这不能回答你的问题。在SQL中，您可以将LIKE与通配符一起使用，并且您的示例中的加号必须被替换掉并替换为百分号（％），在SQL的LIKE中，这意味着匹配任何字符。

< blockquote>

 选择 * 来自 table1 其中 column1 喜欢％something％

做你想做的事你可以做这样的事情（警告这还没有经过测试！）

  string  ValueEnteredByTextBox =   [ja + emsak]; 
  string  SQL =   SELECT * FROM sometable某些像@Param; 
  string  LikeClause = ValueEnteredByTextBox.replace（  +， ％）; 
 
 使用（SqlConnection cn =  new  SqlConnection（DatabaseConnectionString））
 {
 cn.open（）; 
 
 SqlCommand cmd =  new  SqlCommand（sql，cn）; 
 cmd.Parameters.AddWithValue（  @ Param，LikeClause）; 
 
  //  在此填充数据表或数据集 
} 
 

hi I want to search in Database with Sql Command(SELECT * From tabale1 Like ....)
now, i need search with +.
For example ,when i to need fine [Javad Emsakpour], I write [ja+emsak] in textbox search.

解决方案

Personally I would avoid building SELECT statements in client code. It opens up several security concerns, primarily SQL injection attack vector, but also if you make the single route into your data a collection of stored procedures, you can lock down the SQL account to not allow anything except to execute the stored procs, so that IF someone manages to connect to your DB from outside of your app, they can still only run the SPs that your app would run.

So, I would build a search stored proc that carries all the options.

But that doesn't answer your question. In SQL you can use LIKE with wildcards, and your plus sign in your example would have to be substituted out and replaced with a percent sign (%) which in SQL's LIKE, means match any character.

select * from table1 where column1 like %something% 

To do what you are after you could do something like this (warning this hasn't been tested!)

string ValueEnteredByTextBox = "[ja+emsak]";
string SQL = "SELECT * FROM sometable where somecolumn like @Param";
string LikeClause = ValueEnteredByTextBox.replace("+","%");

using(SqlConnection cn = new SqlConnection(DatabaseConnectionString))
{
  cn.open();

  SqlCommand cmd = new SqlCommand(sql,cn);
  cmd.Parameters.AddWithValue("@Param", LikeClause);

  //fill data table or dataset here
}

这篇关于数据库中的高级搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！