Need advice ...

[shapper]

Hello,

I have a SQL table to hold files information.
One of the table columns is Path which holds values as:

C:\Assets\MyPic.jpg
C:\Assets\MyCV.doc
....

I need to get all record which are images, i.e., which extension
is .jpg, .gif, .png.

Should I use a SQL Stored Procedure or LINQ to filter the paths which
end with the desired extensions?

OR

Should I create, instead, a column named Type and before I insert a
record I would detect in my C# code the file type and fill the Type
column with it?

How is this usually done?

Thank You,
Miguel

 

[Misbah Arefin]

There are a number of ways you can do this it all depends on the QOS
requirements of your application there are tradeoffs with each approach

using stored proc to query you would have to use something like
WHERE Path LIKE '%.jpg' OR Path LIKE '%.gif' etc
this would use an "Index Scan" and depending on the number of rows in your
table might take a lot of time for the query to execute

another approach would be to create a IsImage (BIT) column (also create
index on this column) which you could populate during your insert - your
where clause would be something like
WHERE IsImage = 1
this would use an "Index Seek" which would be faster than the WHERE Path
LIKE approach

another solution instead of explicitly setting the value of the IsImage
column via code let SQLServer do that for you - you would have to make the
IsImage column a computed column and make it persist so that it is not
calculated each time rather only during insert and updates to the row

using LINQ vs SP I would say use SP if the number of rows in the table are
expected to be huge

 

[Zeeshan Haider]

I absoulatlely agree with Misbah.

There are a number of ways you can do this it all depends on the QOS
requirements of your application there are tradeoffs with each approach

using stored proc to query you would have to use something like
WHERE Path LIKE '%.jpg' OR Path LIKE '%.gif' etc
this would use an "Index Scan" and depending on the number of rows in your
table might take a lot of time for the query to execute

another approach would be to create a IsImage (BIT) column (also create
index on this column) which you could populate during your insert - your
where clause would be something like
WHERE IsImage = 1
this would use an "Index Seek" which would be faster than the WHERE Path
LIKE approach

another solution instead of explicitly setting the value of the IsImage
column via code let SQLServer do that for you - you would have to make the
IsImage column a computed column and make it persist so that it is not
calculated each time rather only during insert and updates to the row

using LINQ vs SP I would say use SP if the number of rows in the table are
expected to be huge