What is the best practice to create SQL script files?

Question

At work, we have multiple telegrams to communicate with a customer. Those telegrams are always different with each customer, but the logic, how we work with them is always the same. The data is sent to a table on our schema with other data for example like sent time and telegram type. We split it into a view to simplify it for development and testing purpose.

Example JSON:

{"Prop1": "Test"}

Example view script (using Oracle Enterprise):

CREATE OR REPLACE VIEW test
AS
SELECT prop1
  FROM telegram
     , JSON_TABLE (
        telegram.json,
        '$' COLUMNS (
         prop1 PATH '$.Prop1'
        )
       )

My problem is now, that I do not know the correct or good way to create those view scripts with C#. Currently, I use a constant string with placeholders ({0}, {1}, ...) but they are getting really hard to maintain. I also read about T4, but it is only useable with Visual Studio. AFAIK we plan to move to .NET 5 and it looks like T4 is not available anymore and/or you cannot call the created C# classes in the code without some errors. I heard about Roslyn, too. Is it possible to create .txt/.sql instead of .cs files? I hope I got all the information needed to answer this question. If I forgot some information, tell me. Thanks in advance!

Are you using EF? Is there any reason why you need to use view? In my current project (.Net6) we will just use tables, no view or SP. So I also wondering what everyone suggested as a best practice. We won't use view/SP because we want to keep DB migration simple. So we're using LINQ even for large data tables. — Ariwibawa, May 23 '22 at 06:26
We use ADO.NET most of the time and sometimes EF. The codebase is pretty old, so the logic builds up on the views. — Christoph Thiel, May 23 '22 at 08:28
You forgot to mention the database. SQL, the language, doesn't support JSON. That's a product-specific feature. What are you trying to do? Expose all JSON properties as columns? What database product are you using? That's certainly not SQL Server syntax — Panagiotis Kanavos, May 23 '22 at 08:29
Oh sorry, you are right. We are using Oracle. I will edit the post. — Christoph Thiel, May 23 '22 at 08:46

Heinzi · Answer 1 · 2022-05-23T09:00:50.243

Is it possible to create .txt/.sql instead of .cs files?

Sure, you can add the .txt/.sql file to your project and set its build action to "Embedded Resource".

In your code, you can access the content of that resource with Assembly.GetManifestResourceStream or by linking it to a resource file. Afterwards, you can apply String.Replace or String.Format for placeholders, if required.

That having been said, you might want to consider storing your data as relational data in your database, instead of JSON text that needs to be parsed by a for Every. Single. Query. Not only will this allow you to get rid of your view, but it will improve performance and allow you to use indexes on relevant fields.

What is the best practice to create SQL script files?

1 Answers1