Encapsulating SQL code in .net

  • Thread starter Thread starter Billy Cormic
  • Start date Start date
B

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
 
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.GetManifestResourceStream 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 said:
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
Click to expand...

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


}


}
 
Back
Top