Click here to Skip to main content
15,170,343 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted

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.
   
SQL
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!)

C#
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
}
   
v2
Comments
Freak30 9-Dec-13 7:30am
   
With this you would force the search strings to be in a particular order and nothing befor or after them. To match the behavior of a search engine the the resulting query should be more like:
SELECT * FROM sometable where somecolumn like %param1%
AND somecolumn like %param2%;
with an additional and for every additional string to search for.
Simon_Whale 9-Dec-13 10:15am
   
curious to see how you would of done this as the solution would still execute in the order of the statement in the management studio. you could of changed the likes for contains / patindex but these still cause a full table scan regardless of indexing.
Manoj Kumar Choubey 9-Dec-13 8:57am
   
I am agree with Freak.
C#
string query = "SELECT * FROM sometable where somecolumn like @param";
TextBox1.Text.replace("+","%");

SqlConnection cn = new SqlConnection("YourConnectionString"))
cn.open();
SqlCommand cmd = new SqlCommand(query,cn);
cmd.Parameters.AddWithValue("@param", TextBox1.Text);

//Call SqlAdapter here and fill it by Data Table in it.



Hope this helps as answered by Simon_Whale already above.

Thanks.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900