Encapsulating SQL code in .net

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Billy Cormic

    #1

    Encapsulating SQL code in .net

    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

    #2
    Re: Encapsulating SQL code in .net

    Billy,

    Consider loading script from file

    HTH
    Alex

    "Billy Cormic" <billy_cormic@h otmail.com> wrote in message
    news:dd2f7565.0 407061808.58d07 24b@posting.goo gle.com...[color=blue]
    > 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[/color]


    Comment

    • Jay B. Harlow [MVP - Outlook]

      #3
      Re: Encapsulating SQL code in .net

      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.Reflecti on.Assembly.Get ManifestResourc eStream 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.Resource s.ResourceManag er (or other classes in
      System.Resource s) to read the SQL script resource strings...

      Hope this helps
      Jay


      "Billy Cormic" <billy_cormic@h otmail.com> wrote in message
      news:dd2f7565.0 407061808.58d07 24b@posting.goo gle.com...[color=blue]
      > 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[/color]


      Comment

      • William Ryan  eMVP

        #4
        Re: Encapsulating SQL code in .net

        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!

        "Billy Cormic" <billy_cormic@h otmail.com> wrote in message
        news:dd2f7565.0 407061808.58d07 24b@posting.goo gle.com...[color=blue]
        > 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[/color]


        Comment

        • IBM Salesman

          #5
          Re: Encapsulating SQL code in .net

          Billy Cormic wrote:
          [color=blue]
          > 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[/color]

          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("SELE CT");

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


          }

          sb.Append("FROM ");

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


          }


          }

          Comment

          Working...