Query custom database (separate to Umbraco) with raw SQL Umbraco #help-with-umbraco

Query custom database (separate to Umbraco) with r...

leekelleher

10/13/2023, 3:43 PM

> **Back story**: Since Umbraco 11, (when it stopped shipping with the "Microsoft.Data.SqlClient" library), I haven't been able to get the SQL data-source in my Contentment package to work. Prior to this, e.g. v9/v10, I could detect whether if a connection string was SQL Server or SQLite (or even earlier SQL CE). But without adding a dependency on "Microsoft.Data.SqlClient", I can't figure out how to make a raw SQL query on a custom database connection. If I have a custom database connection string, totally separate to the Umbraco database. This could be either SQL Server or SQLite. Should it be possible to reuse any Umbraco code to run a raw SQL query against that database? As far as my google-fu goes, there are many examples for EFCore, e.g. developing your own models/context (for inside the Umbraco database) ... but nothing for a raw SQL query. > Note, if you aren't familiar with the SQL data-source in Contentment, it lets a developer enter a raw SQL query to populate a list editor, e.g. dropdown list, radiobuttons, etc. Any code snippet examples would be hugely appreciated. 🙏

huwred

10/13/2023, 3:59 PM

could you use a provider name string and use that to instantiate the correct connection?

huwred

10/13/2023, 4:06 PM

something like

Copy code

cs
            if (appconfig.GetConnectionString("myConnection_ProviderName") == "Microsoft.Data.Sqlite")
            {
                var database = new Database(appconfig.GetConnectionString("myconnection"), DatabaseType.SQLite, SqlClientFactory.Instance);
            }
            else
            {
                var database = new Database(appconfig.GetConnectionString("myconnection"), DatabaseType.SqlServer2012, SqlClientFactory.Instance);
            }

leekelleher

10/14/2023, 11:11 AM

Thank you @huwred. I shall give it a go! 🙏

leekelleher

10/14/2023, 11:35 AM

Hmmm... seems that

SqlClientFactory

isn't available in Umbraco 11/12 (.NET 7) ... I assume it's because the "Microsoft.Data.SqlClient" library no longer ships with it. 🤔

leekelleher

10/14/2023, 2:06 PM

2 hours later... I found that I can inject

IDbProviderFactoryCreator

to make a call to

_dbProviderFactoryCreator.CreateFactory(providerName)

which will give me the

DbProviderFactory

, which I can pass into the

new Database()

instance.

huwred

10/15/2023, 5:51 AM

Cool, will change my code to do that, save any issues when it gets updated 😀

kdx-perbol

10/16/2023, 8:21 AM

You can raw-SQL with EF Core. https://github.com/dotnet/EntityFramework.Docs/blob/main/samples/core/Querying/SqlQueries/Program.cs Maybe you will want to use EF Core at some point and it would be worth introducing now. We use EF Core in some U12 solutions and it works fine. 🙂

leekelleher

10/16/2023, 2:30 PM

@huwred This is what I ended up with for my Contentment package: https://github.com/leekelleher/umbraco-contentment/blob/develop/src/Umbraco.Community.Contentment/DataEditors/DataList/DataSources/SqlDataListSource.NET6_0.cs#L112-L128 (I'm not too keen on the magic strings, but I'm sure there's some constant hidden away within Umbraco or .NET itself. 🤷‍♂️

leekelleher

10/16/2023, 2:37 PM

Thanks @kdx-perbol, good to know. I haven't really used EFCore (nor the old EF), so there'll be a slight learning curve on my side. With the example you linked to, I'm wondering what the generic equivalent of the

var context = new BloggingContext()

would be? e.g. is there somewhere to set what the connection string at runtime? Reason being is the with Contentment Data List's SQL data-source, it lets the user pick a connection string, then enter a raw query. From what my little understanding about EFCore, feels that it's designed to be more of an ORM, less cowboy raw SQL. 🤠

kdx-perbol

10/16/2023, 4:05 PM

If the database is already provided for you, you'd have to generate the equivalent of

BloggingContext

yourself from the Contentment database. Absolutely an ORM, so maybe not super useful as just a database query tool.

Sebastiaan

10/17/2023, 7:56 AM

I've sent you a PR, but totally understand if you don't want to take it 🙂

John Sharp

04/09/2024, 10:41 PM

Hi. I found this thread and had another solution. Not sure if anyone will find it useful but sharing in case. Using EF you can access the DbContext, Connection and Command individually. var conn = DbContext.Database.CurrentTransaction!.GetDbTransaction(); Using var cmd = conn.CreateCommand(); cmd.Transaction = Database.CurrentTransaction?.GetDbConnection(); You'll need to reference Microsoft.EntityFrameworkCore.Storage to. To access the extension method.

John Sharp

04/09/2024, 10:42 PM

Once you've done that you can access cmd as a normal cmd object and do cmd.CommandText =sql; cmd.ExecuteReaderAsync();

leekelleher

04/10/2024, 12:29 PM

Thanks for the info @John Sharp, much appreciated! (bookmarked for when I need it next)

John Sharp

04/10/2024, 1:21 PM

No problem. I hope it helps 🙂

224 Views

Previous Next