The best place to put SQL in a ASP.NET app is...?

I am creating a ASP.NET application that is pretty much self contained, it will be pushed to different hosts and开发者_如何学Python as a result will include an embedded database engine - I did look at SQLite but that can not run under medium trust and that could be an issue, so it looks like it is going to have to be Microsoft SQL Server Compact Edition 4.

Microsoft SQL Server Compact Edition does not support stored procedures and because of such I'm wondering where the best place to include the SQL code would be, taking into account future edits, etc.

So far I think that my options are either directly embedded in the code as a String or Stringbuilder type (which can get messy for advanced queries), or maybe in SQL files stored in the App_GlobalResources folder - the second option is something something that I have only just considered, but think that that would be a pretty good option for containment and future editing - does anyone have any other suggestions?

Regards, Nathan.

You can store parameterized queries in your resource file.

You can use MS SQL Express. It is free, it has stored procedures, but it has 4GB database size limit (increased to 10 GB for SQL Server Express 2008 R2).

For more information read this.

I think it's better to use some ORM tool or LINQ to SQL, than store native SQL strings.

Like other have said an ORM, Entity Framework or Linq to SQL is probably better than inline SQL, however as your question wasn't asking about any of these I would suggest storing you SQL files as embedded resources. Others have mentioned resource resource files but I feel that resource files are difficult to use and you do not have an nice IDE to develop within. Having embedded .SQL files should be easier to version control, use within Visual Studio, allow you to keep your queries as closely as possible to your data access components and make it difficult to change once in a production environment.