473,221 Members | 1,751 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 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 2161
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: Alvin Bruney | last post by:
C# is great but it does have some short comings. Here, I examine one of them which I definitely think is a shortcoming. Coming from C++, there seems to be no equivalent in C# to separate code...
6
by: Chad Z. Hower aka Kudzu | last post by:
I want to do this. I want my programmers to do all the code. All of it - run at server and run at client. I then want a graphic artist to make the look and the layout of the pages. The...
29
by: John Rivers | last post by:
Hello, What good reason there is for not allowing methods in ASPX pages I can't imagine, but here is how to get around that limitation: (START) <body MS_POSITIONING="FlowLayout"> <form...
8
by: neilmcguigan | last post by:
I just wanted to list some reasons why I prefer inline code to code-behind. 1. you can fix some bugs more quickly. remote desktop into server, change the aspx file, and she's good to go. I'd say...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.