471,305 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,305 software developers and data experts.

SQL statement and C# code separation

I am developing a C# application in which I have several SQL
statements. Currently I have each SQL statement residing inside a text
file. In the C# code, I will load the text files, and execute the SQL
statements. The reason I do this is because the other (more common)
solution would be:

sqlString = "select * from table " +
"where id = 1";

Which is going to be a real pain in the ass if the SQL statement is
very long, and you want to copy the statement back and forth to a SQL
tool like TOAD.

Dec 19 '05 #1
8 2102
So my question is actually, is there an elegant way to do avoid the
construction of SQL statements in the example of my previous post?

I solved this by using files, but the problem I have with this solution
is that when I compile the code, the created executable will be placed
in the bin/debug directory. The SQL text files are placed in a SQL
directory, which is located in the same directory as the sourcefiles.
So we have the following options:
- copy the SQL directory to the bin/debug directory.
- do a relative path reference like this "../../project/sql".

I don't think any of the above two options are good. If you forget to
copy the latest SQL directory, you will wonder why your code doesn't
work. Turns out you are using old SQL statements.

So does anyone know of a different and perhaps easier way to my problem?

Dec 19 '05 #2
Huy Hoang wrote:
I am developing a C# application in which I have several SQL
statements. Currently I have each SQL statement residing inside a text
file. In the C# code, I will load the text files, and execute the SQL
statements. The reason I do this is because the other (more common)
solution would be:

sqlString = "select * from table " +
"where id = 1";

Which is going to be a real pain in the ass if the SQL statement is
very long, and you want to copy the statement back and forth to a SQL
tool like TOAD.


Hi,

You could use the @ strings in C#. That way you can format your query
nicely, and it is easy to copy to SQL tools, if that is what you want.

For example:

sqlString =
@"SELECT * FROM table
WHERE id = 1
ORDER BY id DESC";

Hope this helps,

-Lenard
Dec 19 '05 #3
SPROCS?

--
Venlig hilsen
Anders Borum / SphereWorks
Microsoft Certified Professional (.NET MCP)
Dec 19 '05 #4
Hi,

Yes this is the solution I was looking for.

Dec 19 '05 #5
Hi,

Yes this is the solution I was looking for.

Thanks!

-Huy

Dec 19 '05 #6
Lenard Gunda <ar***********@freemail.hu> wrote in news:O0qH58HBGHA.4016
@TK2MSFTNGP11.phx.gbl:
You could use the @ strings in C#. That way you can format your query
nicely, and it is easy to copy to SQL tools, if that is what you want.

For example:

sqlString =
@"SELECT * FROM table
WHERE id = 1
ORDER BY id DESC";


That would work, but now you have one of two problems... either (a) it
makes it a pain to keep formatted in your source code, or (b) it makes your
source ugly because anytime you split a line the editor isn't going to
auto-indent anything for you.

One thing I have done in the past is to embed SQL text files as resources
and then read them during runtime. This way you can format your SQL
without making your code ugly, and any changes automatically get compiled
into your binaries.

Of course the problem with any of these methods is that (without tools such
as obfuscators and such) it can expose your SQL to prying eyes. If you
aren't concerned about that, then OK whatever (you should be.) Because of
this the best method is probably to put your SQL into stored procedures on
the SQL server.

-mdb
Dec 19 '05 #7
Hello!
Of course the problem with any of these methods is that (without tools
such
as obfuscators and such) it can expose your SQL to prying eyes. If you
aren't concerned about that, then OK whatever (you should be.) Because of
this the best method is probably to put your SQL into stored procedures on
the SQL server.


With encryption, if the system is to be deployed to customers. BTW. Don't
forget to backup your SPROCs before applying the encryption =o)

--
Venlig hilsen
Anders Borum / SphereWorks
Microsoft Certified Professional (.NET MCP)
Dec 19 '05 #8
Huy Hoang wrote:
So my question is actually, is there an elegant way to do avoid the
construction of SQL statements in the example of my previous post?

I solved this by using files, but the problem I have with this
solution is that when I compile the code, the created executable will
be placed in the bin/debug directory. The SQL text files are placed
in a SQL directory, which is located in the same directory as the
sourcefiles. So we have the following options:
- copy the SQL directory to the bin/debug directory.
- do a relative path reference like this "../../project/sql".

I don't think any of the above two options are good. If you forget to
copy the latest SQL directory, you will wonder why your code doesn't
work. Turns out you are using old SQL statements.

So does anyone know of a different and perhaps easier way to my
problem?


Use an O/R Mapper and write your queries using C# or VB.NET. This way
your queries are formulated at the spot where you need them, compiled
into the code and distributed with your application.

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Dec 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.