Alright just starting out in ASP.NET and it's making my head spin, but I think I'm getting it.
My schema in brief : Images, Categories, and a table in the middle so we can have a many-to-many relationship between images and categories. My issue is searching. I can search by keyword, and limit it to categories. So, in pseudo sql..
SELECT ... WHERE keyword LIKE '%test%' and category_id in (1,5,20,66);
I made a variable for the keyword no problem. But how can I get this dynamic list? Or, is there another way about going this problem?
Where will you put your query in? StoredProcedure or Web? I think my two posts help you:
http://forums.asp.net/t/1162993.aspxhttp://forums.asp.net/t/1158548.aspx
|||
I've been informed by the big shark guy that I recommend D-SQL a lot, but you are asking for it so here goes:
First off... beware SQL injection. Strip out ' and - from your search text. Do without.
CREATE PROCEDURE sp_search @.keyword varchar(50),
@.categorylist varchar(150) as
declare @.sql varchar(max)
set @.sql = 'SELECT ... WHERE keyword LIKE ''%' + @.keyword + '%'' and category_id in (' + @.categorylist + ')' -- BTW those are double single quotes, not double quotes
EXEC sp_executesql @.sql, N'@.keyword varchar(50), @.categorylist varchar(150)'
then call sp_search in your code, adding the keyword and category list strings as parameters. Does that help?
|||che : i'll read those links now
Charles : Interesting. Will I be able to accomplish something like this with Oracle? That looks like SQL Server to me?
|||Oh. Most folks here are SQL Server. I'm sure you can do dynamic SQL in oracle though - my boss who taught me D-SQL co-authored the Oracle Designer/2000 Handbook. The sp_executesql is a SQL serber built-in sp, and I don't know what the equivalent is in oracle:
http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a68022/dynsql.htm
|||This is a little disappointing. Dynamic SQL seems really overkill for such a trivial problem.
Perhaps I should look at not using strongly typed Datasets? Build one on the fly? Would that be different/better?
Many of the problems were have these days tend to be ones caused by the complexity of the frameworks we are working in. For example, to write a web page, I have to know C# (or VB), ASP, HTML, SQL, and .Net, and how they all interrelate. For one page. It used to be that all you needed was a front end and a database. Oh well, we get a lot in terms of scalability and functionality out of this stuff too - the complexity is market driven.
All philosophy aside, it seems to me that most searches are done with D-Sql. If you really want to make a mountain out of a molehill, you could create a search object that safely builds your query in C# and then access it from your search front end. The advantage of that would be that you could get a lot of re-use in the future. Searching is a non-trivial issue. The volume of work that has gone into searching makes it difficult to mine for simple one-off solutions.
sql