SQL statement and C# code separation

[Huy Hoang]

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.

[Huy Hoang]

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?

[Lenard Gunda]

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

[Anders Borum]

SPROCS?

--
Venlig hilsen
Anders Borum / SphereWorks
Microsoft Certified Professional (.NET MCP)

[Huy Hoang]

Hi,

Yes this is the solution I was looking for.

[Huy Hoang]

Hi,

Yes this is the solution I was looking for.

Thanks!

-Huy

[Michael Bray]

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

[Anders Borum]

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)

[Frans Bouma [C# MVP]]

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#)
------------------------------------------------------------------------