• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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.
0
wsfindlater
Asked:
wsfindlater
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
0
 
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.
0
 
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bob LearnedCommented:
0
 
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.
0
 
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
0
 
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.
0
 
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.

0
 
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.
0
 
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.
0
 
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,
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now