Datasets, searching, complex queries.

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.aspx

http://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

Database trigger to run managed C# code

Hi there,

Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.

How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?

The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.

However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....

|||

I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.

Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last

This increases performance significantly! Awesome!