SharpScript .ss file works to connect to database, but same code doesn't work when served to local web-browser?

Question

I have a SharpScript .ss script file with some small code that polls a database and formats things to display. The output is getting too unruly for command line output so I wanted to generate html and view things like that. Generating the html works fine with htmlDump in the .ss file, but when I create a small web project from one of their templates, the database connection stops working?

Is there any difference in how to specify the connection string for a website vs. a .ss script file?

I just have the regular args specification at the beginning of the file

<!--
db mssql
db.connection Server=ble\bla;Database=blu;user id=blo;password=#blingblong#
-->

This works fine in .ss script file, I can then do something like

```code
{{ 
    "select * from View drv
        join [Project] p on drv.ProjectId = p.ProjectId
        where DocumentId = 'GUID' " 
    | dbSelect
    | map => {it.RecordId, it.Text, it.Name}
    | to => sqlMap 
}}

sqlMap | count
sqlMap | textDump
```

and get output like the count (21) and a table from the textDump.

I then created a "bare-webapp" from a template with web new bare-webapp Name and added a new html file with the same content, but that doesn't work? If I do

{{dbTableNames | count }}

{{db}}

I can see that the db argument is rendered in the browser as "mssql" like in the argument input, but the table names are not listed, and no sql queries work. I don't see any error messages or anything, so I have no idea what is going on? I thought SharpScript would be able to render the html page similarly to how .ss script files can access the database?

Answer 1

They are very different contexts. The stand-alone .ss Sharp Scripts are executed within the context of a Sharp App that's executed by the dotnet tools which have access to all ServiceStack implementation assemblies. So when the script sees:

<!--
db mssql
db.connection Server=ble\bla;Database=blu;user id=blo;password=#blingblong#
-->

It creates a ScriptContext pre-configured with #Script Database Scripts and an OrmLiteConnectionFactory configured with the ServiceStack.OrmLite.SqlServer Provider and your connection string.

By contrast #Script Pages executing in a web page only has access to the SharpPagesFeature context that's configured in your App. So you'll need to configure OrmLite in your AppHost as normal, e.g:

container.AddSingleton<IDbConnectionFactory>(() => 
    new OrmLiteConnectionFactory(connectionString, SqlServer2012Dialect.Provider));

Then make the Database Scripts available to your SharpPagesFeature, e.g:

Plugins.Add(new SharpPagesFeature {
    ScriptMethods = {
        new DbScriptsAsync()
    }
});

Which will give your pages access to the Database Scripts which will use your registered OrmLiteConnectionFactory.

Sharp Apps

Sharp Apps are a way to rapidly develop Web Apps created entirely using #Script which enables an instant feedback live development model as your App doesn't require re-compilation or restarts. Sharp Apps, just like your Sharp Scripts are both run

$ x new bare-webapp ProjectName

Then you can start your Sharp App with x run in your App's directory, e.g:

$ cd ProjectName
$ x run

The same functionality in Sharp Scripts is also available to Sharp Apps, but instead of adding your App configuration to the top of your script you'd instead add it to your app.settings, e.g:

db mssql
db.connection Server=ble\bla;Database=blu;user id=blo;password=#blingblong#

---

Please note from v5.7 #Script is transitioning to use the JS Pipeline Operator Syntax, so it's now recommended to write:

```code
{{ 
    "select * from View drv
        join [Project] p on drv.ProjectId = p.ProjectId
        where DocumentId = 'GUID' " 
    |> dbSelect
    |> map => {it.RecordId, it.Text, it.Name}
    |> to => sqlMap 
}}

sqlMap |> count
sqlMap |> textDump
```