Is there a way to use SQL from "*.sql" files directly in my C# code?

Wondering if I am missing something. Using SQL Server 2000 with C# 2003. Notice that files with ".SQL" have SQL script.
My question is:
Is there a way to use SQL from "*.sql" files directly in my C# code?
I usually make s string out of the SQL statement and put that string in my command or adaptor.
wsfindlaterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
using System.IO;


StreamReader reader = new StreamReader(fileName);
string sql = reader.ReadToEnd();
SqlDataAdapter adapter = new SqlDataAdapter(sql, "connection string goes here");
DataTable table = new DataTable();
adapter.Fill(table);

foreach (DataRow row in table.Rows)
{
}

Bob

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
devsolnsCommented:
I could think of a more risky thing to do.  I would avoid this idea like the plague!.  Keep your sql where it belongs in the database and access it via stored procs.
wsfindlaterAuthor Commented:
Have tried stored procedures, and I don't really like them. Think I am not alone. eg:

http://home.epix.net/~eichler/acerant/AntiSP.htm

My alternative is to dynamically build the SQL strings. Problem I have found with this is it is not easy to read the string again once it is coded.

Don't like the idea of reading files from the disk, especially in ASP programs.

Maybe there is a way to store the .SQL files as a resource?
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Bob LearnedCommented:
devsolnsCommented:
Ok im a bit confused as whats not to like about stored procedures??????????  If this is for anything other than your own personal project I think its bizarre to embedd sql into the application or anywhere else.  you'd allow sql injection to be a trivial process.
Arthur_WoodCommented:
another argument in favor of using Stored Procedures is this:  should you EVER need to modify the SQL, with a stored Procedure in the Database, such a modification requires NO CHANGE whatsoever to your application  - as long as the returned values from the stored procedure are the same types, in the same order.  However, if your SQL is entirely in your Source code, the changes to the SQL mean that you must edit the source code of the project, then recompile the code, and then , in a REAL development environment, you would need to RE-TEST the application - the cost in time could be enormous.

AW
wsfindlaterAuthor Commented:
Thank-you TheLearnedOne for just answering my question.
If you want to know why I don't like stored procedures, you might check out the link above, which has links to other sources.
If the returned values from the stored procedure are the same types why would the SQL be changed?
The only reason I can think of is there must have been some error in the SQL in the first place.
If the stored procedure is giving back somthing different than it did originally for the same input parameters doen't this present a risk to the already tested application? Seems to me that it would have to be retested anyway.
wsfindlaterAuthor Commented:
In the .NET (or Mono) programming language "C#", the equivalent are the ADO.NET SqlCommand (for Microsoft SQL Server) or OracleCommand (for Oracle's database server) objects. The example below shows how to prevent injection attacks using the SqlCommand object. The code for other ADO.NET providers is very similar, but may vary slightly depending on the specific implementation by that provider vendor.

devsolnsCommented:
wsfindlater from what it sounds like your working on a super small project for a small company so your approach is probably a ok.  you think that way in a commercial software shop or large company that changes frequently (ie bank) your approach would be disastrous to say the least.
wsfindlaterAuthor Commented:
OK, devsolns I can see what your saying.
"embedd sql into the application or anywhere else"
So all the SQL is in stored procedures. Yes, this would make sense where you just have one database source and a lot of developers.
I work in a large aerospace company. The data is not always centralized. My applications are Web based and task oriented.
devsolnsCommented:
I have yet to see a company with centralized data, ever!!.  Most of the applications I work with interact with many hetergenous systems at once.  many rdbms,  websphere mq server, mainframe or some esb. most are also web based with tiered logic.  the model i described holds up just fine in this environment.

i am only saying this to help you.  look into this area of development (architecture) and it sounds like you could become a lead for a lot of insight with your group.

good luck,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.