External Sql Statements in C#

We have a C# application, for some reasons i wont go into we are unable to use Entity Framework.

That being said I still prefer to have parameterized sql statements external from my application code, especially the large ones.  For most small CRUD based options I have just written a simple class to map the objects etc.  

What would be the best way to externalize the sql queries but still allow some dynamic parameters, Ill provide a simple example here

StringBuilder x = new StringBuilder();
x.AppendFormat("select * from Customers where CustomerId = {0}", this.CustomerId);
// the problem with the above is the sql is written into the application code, i want to store the sql portion somewhere external in a .txt or property file

So is there a way where the query would be in a different file like

select * from Customers where CustomerId = {0} // maybe file is called sql1.txt

and then you would load the sql from a seperate file

like
using (StreamReader sr = new StreamReader("sql1.txt"))
            {
                StringBuilder x = new StringBuilder();
                x.appendFormat(sr.ReadToEnd(), this.CustomerId);
               
            }

Basically i would like to follow the approach of keeping all sql seperate like i read here

Externalizing SQL has key benefits for the administrators (DBAs) whose primary language is SQL, not Java. Having SQL in a form close to SQL allows them to understand what each query is doing and suggest changes more easily if necessary. Storing the SQL in strings, or fluent libraries, locks the SQL away from the DBAs making it harder to have discussions and more difficult to change.

However i would not have completely static sql files so I would still need somehow to dynamically populate the elements
LVL 4
Brant SnowAsked:
Who is Participating?
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.

Russ SuterCommented:
Have you considered using parameterized stored procedures?
0
Brant SnowAuthor Commented:
I have not Russ, could you offer some more information on parameterized stored procedures on sql server databases?
0
Russ SuterCommented:
SQL Server has built-in support for parameterized stored procedures that can easily be called directly from ASP.NET.

https://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/

The stored procedures live within the database along with the tables. They have numerous advantages over direct queries. Two of the most notable advantages are performance optimization and protection against SQL injection attack (if implemented properly).
0

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.