Encapsulating SQL code in .net

[Billy Cormic]

Hello,
I would like to completely encapsulate approximately 1000 lines of
SQL code in my vb.net web application. Setting the 1000 lines of SQL
code equal to a string seems inefficient. Setting the SQL code equal
to a string would be difficult because I would have to use numerous
underscores ( _ ) and plus signs ( + ). I also do not want to use
stored procedures for intellectual property / security reasons (I have
to give the client the SQL Server SA password). Can anyone please
tell me a good way of accomplishing this goal?

Thanks,
Billy

[AlexS]

Billy,

Consider loading script from file

HTH
Alex

"Billy Cormic" <bi**********@hotmail.com> wrote in message
news:dd**************************@posting.google.c om...

Hello,
I would like to completely encapsulate approximately 1000 lines of
SQL code in my vb.net web application. Setting the 1000 lines of SQL
code equal to a string seems inefficient. Setting the SQL code equal
to a string would be difficult because I would have to use numerous
underscores ( _ ) and plus signs ( + ). I also do not want to use
stored procedures for intellectual property / security reasons (I have
to give the client the SQL Server SA password). Can anyone please
tell me a good way of accomplishing this goal?

Thanks,
Billy

[Jay B. Harlow [MVP - Outlook]]

Billy,
In addition to the other comments.

Is this a single SQL Script or multiple SQL Scripts?

I would consider embedding a single SQL script as an embedded resource file
in my assembly. Or I would consider embedding multiple SQL scripts as
embedded resource strings in my assembly.

You can add an .sql file to your project, and set the "Build Action" on the
file properties (from Solution Explorer) as "Embedded Resource. Then you can
use System.Reflection.Assembly.GetManifestResourceStre am to read the SQL
Script from the assembly.

Alternatively you can add a .resx file to your project to hold multiple SQL
scripts. And use System.Resources.ResourceManager (or other classes in
System.Resources) to read the SQL script resource strings...

Hope this helps
Jay
"Billy Cormic" <bi**********@hotmail.com> wrote in message
news:dd**************************@posting.google.c om...

Hello,
I would like to completely encapsulate approximately 1000 lines of
SQL code in my vb.net web application. Setting the 1000 lines of SQL
code equal to a string seems inefficient. Setting the SQL code equal
to a string would be difficult because I would have to use numerous
underscores ( _ ) and plus signs ( + ). I also do not want to use
stored procedures for intellectual property / security reasons (I have
to give the client the SQL Server SA password). Can anyone please
tell me a good way of accomplishing this goal?

Thanks,
Billy

[William Ryan eMVP]

You may find this helpful http://www.knowdotnet.com/articles/ddl.html . I've
done it a few different ways in this article.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/...ity/newsgroups
"Billy Cormic" <bi**********@hotmail.com> wrote in message
news:dd**************************@posting.google.c om...

Hello,
I would like to completely encapsulate approximately 1000 lines of
SQL code in my vb.net web application. Setting the 1000 lines of SQL
code equal to a string seems inefficient. Setting the SQL code equal
to a string would be difficult because I would have to use numerous
underscores ( _ ) and plus signs ( + ). I also do not want to use
stored procedures for intellectual property / security reasons (I have
to give the client the SQL Server SA password). Can anyone please
tell me a good way of accomplishing this goal?

Thanks,
Billy

[IBM Salesman]

Billy Cormic wrote:

Hello,
I would like to completely encapsulate approximately 1000 lines of
SQL code in my vb.net web application. Setting the 1000 lines of SQL
code equal to a string seems inefficient. Setting the SQL code equal
to a string would be difficult because I would have to use numerous
underscores ( _ ) and plus signs ( + ). I also do not want to use
stored procedures for intellectual property / security reasons (I have
to give the client the SQL Server SA password). Can anyone please
tell me a good way of accomplishing this goal?

Thanks,
Billy

You might want to consider breaking down your problem.

Why don't you create a class that has some of the properties and methods
appropriate to a SQL statement.

For example, (this is psuedo code-- you can flesh it out )

class SqlScript
{

public SqlString( string table,
string[] fields,
string where,
string order )

{

// set to local variables
}
public string buildStatement()
{

StringBuilder sb;
sb.Append("SELECT");

for each ( string str in fields)
{
sb.Append(str);
sb.Append(",");
}

sb.Append("FROM ");

// and so on... you get the idea
}
}