Category: DB2Code

DB2Code – part 8–closing

From now on the only work to be done is improving step by step the generating code. For example,

1. in the REST API , the code is accessing directly the database- should be an indirection layer

2. Should generate some mock classes to return data – instead of going to database ( or maybe EF In Memory is enough?)

3. Other GUI improvements

4. Generate other project types ( e.g. .NET MAUI , Blazor …)

But those are just small improvements – the whole architecture of the project works and it is running fast to have functional code.

More , it can be customized at the needs of every programmer – if they are familiar with t4 files.

However, the fact that a programmer could make very fast an application that generates code is a sign that in 20 years + the programming is in another paradigm ( not to mention ChatGPT …)

The final result is at https://marketplace.visualstudio.com/items?itemName=ignatandrei.databasetocode

Db2Code–part 7 – Angular vs React

Once you have the REACT implementation, you will think that Angular implementation will be a breeze. Not . Some pain points and differences :

1. Angular have the power of separating components into html code and JS/TS code. While this is a feature for the developer, it becomes hard for code generators to generate 2 different files .So it will be one file and the component will have template instead of templateURL React, instead , is just one file/function to generate .

2. REACT can load dynamic function with just import the js/ts file- since all HTML in JS/TS is basically JS . To import dynamic in Angular is a hard – read https://angular.io/guide/dynamic-component-loader

3. In the same idea , the Angular components must be registered into an NgModule – or make them standalone :https://angular.io/guide/standalone-components . Anyway, this affects the DI – a hard thing to do . In REACT, just import them ( and yes, framework vs library – but I wish in Angular to be easier )

4. In REACT we have the useState – in Angular there will be Signals. I wait – the code will be prettier .

5. REACT is more ugly – with all callbacks and memos. Angular seems prettier.

6. The DI in Angular is awesome . Just decorate what you need with @Injectable

The final result is at https://marketplace.visualstudio.com/items?itemName=ignatandrei.databasetocode

Db2Code–part 6 – implementation details

Problem 1 More dbContexts

I have think that a user can have more than 1 DBContext / database implemented. If there was just only one , it were easy to add a partial to program.cs and register the database DI

services.AddDbContext<ApplicationDBContext>

in the partial . However, if each database is generated, how to have this ? The answer is https://learn.microsoft.com/en-us/dotnet/api/system.runtime.compilerservices.moduleinitializerattribute?view=net-7.0

[ModuleInitializer]
    public static void AddMe()
    {
        Generated.UtilsControllers.registerContexts.Add(new RegisterApplicationDBContext());
    }

And in the WebAPI

foreach (IRegisterContext item in UtilsControllers.registerContexts)
         {
            typesContext.Add( item.AddServices(builder.Services, builder.Configuration));
         }

Problem 2 Create database if not exists

I want to let the user to create the database if it dows not exists. But how, if I do not know the name of the database ? The idea is to obtain the DBContext from the name and inject into the DI

builder.Services.AddTransient((ctx) =>
         {
             Func<string, DbContext?> a = (string dbName) =>
             {
                 var t = typesContext.First(it => it.Name == dbName);

                 var req = ctx.GetRequiredService(t);
                 if (req == null) return null;
                 return req as DbContext;
             };
             return a;
         });

And then obtain from the controller action

[HttpPost(“{dbName}”)]
    public async Task<bool> EnsureCreated([FromServices] Func<string, DbContext?> fact, string dbName)
    {
        var exists = AllDB.Singleton.ExistsDB(dbName);
        if (!exists)
        {
            throw new ArgumentException(“DB does not exists ” + dbName);
        }

       var req = fact(dbName);
        if(req == null)
            throw new ArgumentException(“service does not exists ” + dbName);

       return await req.Database.EnsureCreatedAsync();
    }

Problem 3 ; Generate the correct pasca case names for typescript properties

This was something difficult. But – I solved by simply add this

public class LowerCaseNamingPolicy : JsonNamingPolicy
{
    public override string ConvertName(string name)
    {
        if (string.IsNullOrWhiteSpace(name))
            return name;

        return name.ToLower();
    }
}

in order to have all with lowercase

builder.Services.AddControllers()
             .AddJsonOptions(c =>
             {
                 c.JsonSerializerOptions.PropertyNamingPolicy = new LowerCaseNamingPolicy();
             })
               .PartManager.ApplicationParts.Add(new AssemblyPart(assControllers)); ;
         ;

Db2Code–part 5 – testing

For testing I should find some databases to test to ( and do not forget that generating SELECT from PK supports just 1 PK, not multiple).

Microsoft already show some sample database – you can download from https://github.com/microsoft/sql-server-samples/tree/master/samples/databases . However , those must exists in the moment of running tests – so I have as options:

Have an Sql Server ( by docker or on PC) and run the sql every time at the beginning of every start run test
Have an Sqlite database file with all data and copy it .

( SqlServer in memory will be a good options, but it do not work with Sql insert/update….)

A specific test looks like this

static void CreateDb()
{

//the restore operation just replace the sqlite database
     DatabaseOperations.Restore(context, “Pubs”);

}

[Theory]
[InlineData(SearchCriteria.Equal, ediscountsColumns.stor_id, “8042”, 1)]
[InlineData(SearchCriteria.Different, ediscountsColumns.stor_id, “8042”, 2)]
[InlineData(SearchCriteria.Equal, ediscountsColumns.stor_id, null, 2)]
[InlineData(SearchCriteria.Different, ediscountsColumns.stor_id, null, 1)]
//[InlineData(SearchCriteria.Equal, ediscountsColumns.discount, “5”, 1)]
[InlineData(SearchCriteria.Equal, ediscountsColumns.lowqty, “100”, 1)]
public async Task SearchAdvanced(SearchCriteria sc, ediscountsColumns col, string val, int nrRecs)
{
     CreateDb();
     var data = await context.discountsSimpleSearch(sc, col, val).ToArrayAsync();
     Assert.Equal(nrRecs, data.Length);
}

One consequence will be that the tests cannot run in parallel .And , after all, it is just a work of coding data and results. Tiresome work, however pays in the long term.

Db2Code- part 4- Architectural Changes for FrontEnd

Now comes the frontend . There are several options – Blazor, Angular, React , …. . The main problem is – how to generate JavaScript / TypeScript file where there is not .csproj available ? ( Yes, it is .esproj for javascript integration – however, you cannot add NuGet packages to it )

So the solution is to have another project that , once compiled, generates JS/ TS files . But – why this should not be available for all other projects ? So , instead each project compile itself and then generate the .cs files, let’s have a project that compiles and generates files for all. But this can be applied for each –so we have a generator for each type of classes from database ( table model, context, search, controllers)

Now actual work is done by a powershell file that replaces templates in a .csproj, then runs dotnet ef scaffold for each template. This is the diagram of how it works for you

If you want to try it, download from

https://marketplace.visualstudio.com/items?itemName=ignatandrei.databasetocode and start a new project with DB2Code project

Db2Code-part 3- deploy

Now how to let other developers to modify the connection string from a template? Easy said than done – create a template for this. This is easier to speak about than to do . So any project can be transformed into template – but what about a solution ?

Anyway – the starting point is https://learn.microsoft.com/en-us/visualstudio/extensibility/getting-started-with-the-vsix-project-template?view=vs-2022 and https://learn.microsoft.com/en-us/visualstudio/ide/template-parameters?view=vs-2022

Now it is another challenges:

How to put into the template all the files that a project has, WITHOUT putting also the bin ?
How to generate the correct usings with the name that the programmer gives to the project ? e.g. $ext_safeprojectname$
How to generate the zip template file ?

So , for all those questions , a powershell is the answer ( OK. could be also a C# – but it is easier to modify and execute)

The organization is the following:

For each project I will put a template folder in the https://github.com/ignatandrei/QueryViewer/tree/main/src/NET7/DB2GUI/DB2GUITemplate/ProjectTemplates . Also an entry will be added for https://github.com/ignatandrei/QueryViewer/blob/main/src/NET7/DB2GUI/DB2GUITemplate/GeneratorAll.vstemplate .After this the powershell https://github.com/ignatandrei/QueryViewer/blob/main/src/NET7/DB2GUI/DB2GUITemplate/createAll.ps1 will run and update the folder and create the zip with the extension.

The project can be found at https://github.com/ignatandrei/QueryViewer/tree/main/src/NET7/DB2GUI/DB2GUITemplate and the powershell I will put here:

function AddFiles{
param($folder)
push-location
cd GeneratorFromDBTemp
cd $folder
$xml = ( Get-Content “MyTemplate.vstemplate”)

#$node= $xml.VSTemplate.TemplateContent.Project
#$node.ParentNode.RemoveChild($node)
#$node= $xml.SelectSingleNode(“//Project”)

$node= $xml.VSTemplate.TemplateContent.Project
gci *.* -r -Exclude *.*sproj,*.vstemplate, __TemplateIcon.ico | % {
     $rel = Resolve-Path -Relative $_
     $rel = $rel.replace( “.\”,””)
     $newelement = $xml.CreateElement(“ProjectItem”)
     $newelement.SetAttribute(“ReplaceParameters”, “true”)
     # $newelement.SetAttribute(“TargetFileName”, $rel)
     $newelement.InnerText =$rel
     $node.AppendChild($newelement)

}
$output = $xml.OuterXml -replace ‘xmlns=””‘, ”
$output | Out-File “MyTemplate.vstemplate”
pop-location

}

cls
$dt = $date = Get-Date # Get the current date and time
$v = $date.ToString(“yyyy.MM.dd.HHmmss”)

Write-Host ‘starting’
Remove-Item -Path .\a.zip -Force -Recurse -ErrorAction SilentlyContinue
Remove-Item -Path .\GeneratorFromDBTemp -Force -Recurse -ErrorAction SilentlyContinue
$FileLocation = “source.extension.vsixmanifest”
$xmlDoc = Get-Content “source.extension.vsixmanifest”
Write-Host ‘reading vsixmanifest’
$xmlDoc | Format-List *
$node=$xmlDoc.PackageManifest.Metadata.Identity

$node=$node.SetAttribute(“Version”, $v)

$xmlDoc.OuterXml | Out-File $FileLocation
# $xmlDoc.Save(source.extension.vsixmanifest1)
New-item –ItemType “directory” GeneratorFromDBTemp
Copy-Item -Path GeneratorFromDB\* -Destination GeneratorFromDBTemp\ -Force -Recurse
push-location
cd GeneratorFromDBTemp
$FileLocation = “GeneratorAll.vstemplate”
$xml = ( Get-Content $FileLocation)
Write-Host ‘reading template’
$xml | Format-List *
$node = $xml.VSTemplate.TemplateData
Write-Host ‘this is’ $node
$node.Name = “DB2Code”
$node.Description = “GeneratorFromDB” + ” ” + $v + ” See See https://github.com/ignatandrei/queryViewer/”
$xml.OuterXml | Out-File $FileLocation

pop-location

push-location
cd ..
dotnet clean
pop-location

Write-Host “copying files”
copy-item -Path ..\ExampleModels\*             -Destination GeneratorFromDBTemp\ExampleModels\                             -Force -Recurse
copy-item -Path ..\ExampleControllers\*     -Destination GeneratorFromDBTemp\ExampleControllers\                         -Force -Recurse
copy-item -Path ..\ExampleContext\*             -Destination GeneratorFromDBTemp\ExampleContext\                             -Force -Recurse
copy-item -Path ..\ExampleWebAPI\*             -Destination GeneratorFromDBTemp\ExampleWebAPI\                             -Force -Recurse
copy-item -Path ..\GeneratorPowershell\*     -Destination GeneratorFromDBTemp\GeneratorPowershell\                         -Force -Recurse
copy-item -Path ..\GeneratorFromDB\*         -Destination GeneratorFromDBTemp\GeneratorFromDB\                             -Force -Recurse
copy-item -Path ..\examplecrats\*             -Destination GeneratorFromDBTemp\examplecrats\     -Exclude node_modules        -Force -Recurse
copy-item -Path ..\GeneratorCRA\*             -Destination GeneratorFromDBTemp\GeneratorCRA\                                 -Force -Recurse

push-location
cd GeneratorFromDBTemp

Write-Host “delete remaining folders”
Get-ChildItem -Directory -Recurse -Filter “bin” | Remove-Item -Recurse
Get-ChildItem -Directory -Recurse -Filter “obj” | Remove-Item -Recurse
Get-ChildItem -Directory -Recurse -Filter “node_modules” | Remove-Item -Recurse
Get-ChildItem -Directory -Recurse -Filter “.vscode” | Remove-Item -Recurse
Get-ChildItem -Directory -Recurse -Filter “.config” | Remove-Item -Recurse

Write-Host “modify .cs files”
gci *.cs -r | % {
     $content = Get-Content $_.FullName
     $newContent = $content -replace ‘Example’,’$safeprojectname$’
     if ($content -ne $newContent) {
         Set-Content -Path $_.FullName -Value $newContent
         # Write-Host ‘replacing ‘ $_.FullName

     }

}

Write-Host “modify connection details”
gci connectionDetails.txt -r | % {
     $content = Get-Content $_.FullName
     $newContent = $content
     $newContent = $newContent -replace ‘Example’,’$ext_safeprojectname$.’
     $newContent = $newContent -replace ‘GeneratorCRA’,’$ext_safeprojectname$.GeneratorCRA’
     if ($content -ne $newContent) {
         Set-Content -Path $_.FullName -Value $newContent
         # Write-Host ‘replacing ‘ $_.FullName

     }

}

Write-Host “modify create.ps1”
gci create.ps1 -r | % {
     $content = Get-Content $_.FullName
     $newContent = $content
     $newContent = $newContent -replace ‘examplecrats’,’$ext_safeprojectname$.examplecrats’
     if ($content -ne $newContent) {
         Set-Content -Path $_.FullName -Value $newContent
         # Write-Host ‘replacing ‘ $_.FullName

     }

}

Write-Host “modify .*sproj files”
gci *.*sproj -r | % {
     $content = Get-Content $_.FullName
     $newContent = $content
     $newContent = $newContent -replace ‘Example’,’$ext_safeprojectname$.’
     $newContent = $newContent -replace ‘example’,’$ext_safeprojectname$.’
     $newContent = $newContent -replace “..\\GeneratorFromDB\\GeneratorFromDB.csproj”,’..\$ext_specifiedsolutionname$.GeneratorFromDB\$ext_specifiedsolutionname$.GeneratorFromDB.csproj’
     if ($content -ne $newContent) {
         Set-Content -Path $_.FullName -Value $newContent
         # Write-Host ‘replacing ‘ $_.FullName

     }

}

pop-location

AddFiles “ExampleModels”
AddFiles “ExampleControllers”
AddFiles “ExampleContext”
AddFiles “ExampleWebAPI”
AddFiles “GeneratorPowershell”
AddFiles “GeneratorFromDB”
AddFiles “examplecrats”
AddFiles “GeneratorCRA”

Compress-Archive -DestinationPath .\a -Path GeneratorFromDBTemp\*
Remove-Item .\GeneratorFromDB.zip
Remove-Item .\ProjectTemplates\GeneratorFromDB.zip
Copy-Item .\a.zip .\ProjectTemplates\GeneratorFromDB.zip
Move-Item .\a.zip .\GeneratorFromDB.zip

Db2Code–part 2- architecture

What we will build you can see here :

Each class will have it is own CodeTemplates\EFCore templates from which will generate the code.

Let’s start with ExampleModels : Here will be the class definitions . From the table defintion , the DB2Code will generate

1. A definition of a interface with table columns as properties

public interface I_Department_Table 
{
 long IDDepartment { get; set; }
 string Name { get; set; }
}

2. A class with relationships

public partial class Department
{
    [Key]
    public long IDDepartment { get; set; }
 
    [StringLength(50)]
    [Unicode(false)]
    public string Name { get; set; } = null!;
 
    [InverseProperty("IDDepartmentNavigation")]
    public virtual ICollection&lt;Employee&gt; Employee { get; } = new List&lt;Employee&gt;();
}

3. class without relationship

public class Department_Table : I_Department_Table
{
 public long IDDepartment { get; set; }
 public string Name { get; set; }
 }

4. Explicit operator to convert 2 to 3

public static explicit operator Department_Table?(Department obj) { 
if(obj == null)
return null;
//System.Diagnostics.Debugger.Break();
var ret= new Department_Table();
ret.CopyFrom(obj as I_Department_Table );
return ret;
}
public static explicit operator Department?(Department_Table obj) { 
if(obj == null)
return null;
//System.Diagnostics.Debugger.Break();
var ret= new Department();
ret.CopyFrom(obj as I_Department_Table) ;
return ret;
}

5. Public method CopyFrom( interface)

public void CopyFrom(I_Department_Table other)  {
 this.IDDepartment = other.IDDepartment;
 this.Name = other.Name;
}

5. Enum with name of the columns

public enum eDepartmentColumns {
None = 0
,IDDepartment 
,Name 
}

6. Metadata with name of the tables and the columns

public static MetaTable metaData = new("Department");
static Department_Table (){
 MetaColumn mc=null;
 mc=new ("IDDepartment","long",false);
 metaData.AddColumn(mc);
 mc=new ("Name","string",false);
 metaData.AddColumn(mc);
}

Now it comes ExampleContext . Here will the database context and various search definitions that you need (e.g. for a column of type int, search will generate = , > , < , between , in array ) . More , it will generate metadata for the tables that are part of the context.

public  IAsyncEnumerable&lt;Department&gt; DepartmentSimpleSearch(GeneratorFromDB.SearchCriteria sc, eDepartmentColumns colToSearch, string value){}
public  IAsyncEnumerable&lt;Department&gt; DepartmentGetAll(){}
public async Task&lt;Department[]&gt; DepartmentFind_Array( SearchDepartment? search){}
public Task&lt;long&gt; DepartmentCount( SearchDepartment search)

Now it comes ExampleControllers . This will generate controllers for REST API for the table and also Search Controllers for any kind of search that you may want.

001

002

003

004

005

006

007

008

009

010

011

012

013

014

015

016

017

018

019

020

021

022

023

024

025

026

027

028

029

030

031

032

033

034

035

036

037

038

039

040

041

042

043

044

045

046

047

048

049

050

051

052

053

054

055

056

057

058

059

060

061

062

063

064

065

066

067

068

069

070

071

072

073

074

075

076

077

078

079

080

081

082

083

084

085

086

087

088

089

090

091

092

093

094

095

096

097

098

099

100

101

102

//this is the REST controller
[ApiController]
[Route("[controller]")]    
public partial class RESTDepartmentController : Controller
{
    private ApplicationDBContext _context;
    public RESTDepartmentController(ApplicationDBContext context)
    {
        _context=context;
    }
    [HttpGet]
    public async Task&lt;Department_Table[]&gt; Get(){
        var data= await _context.Department.ToArrayAsync();
        var ret = data.Select(it =&gt; (Department_Table)it!).ToArray();
        return ret;
 
         
    }
     
        [HttpGet("{id}")]
    public async Task&lt;ActionResult&lt;Department_Table&gt;&gt; GetDepartment(long id)
    {
        if (_context.Department == null)
        {
            return NotFound();
        }
        var item = await _context.Department.FirstOrDefaultAsync(e =&gt; e.IDDepartment==id);
 
        if (item == null)
        {
            return NotFound();
        }
 
        return (Department_Table)item!;
    }
 
 
    [HttpPatch("{id}")]
        public async Task&lt;IActionResult&gt; PutDepartment(long id, Department value)
        {
            if (id != value.IDDepartment)
            {
                return BadRequest();
            }
 
            _context.Entry(value).State = EntityState.Modified;
 
            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!DepartmentExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }
 
            return NoContent();
        }
 
        [HttpPost]
        public async Task&lt;ActionResult&lt;Department&gt;&gt; PostDepartment(Department_Table value)
        {
           
            var val = new Department();
            val.CopyFrom(value);
            _context.Department.Add(val);
            await _context.SaveChangesAsync();
 
            return CreatedAtAction("GetDepartment", new { id = val.IDDepartment }, val);
        }
        [HttpDelete("{id}")]
        public async Task&lt;IActionResult&gt; DeleteDepartment(long id)
        {
            if (_context.Department == null)
            {
                return NotFound();
            }
            var item = await _context.Department.FirstOrDefaultAsync(e =&gt; e.IDDepartment==id);
            if (item == null)
            {
                return NotFound();
            }
 
            _context.Department .Remove(item);
            await _context.SaveChangesAsync();
 
            return NoContent();
        }
 
        private bool DepartmentExists(long id)
        {
            return (_context.Department.Any(e =&gt; e.IDDepartment  == id));
        }
 
    }    

And also a SEARCH controller

001

002

003

004

005

006

007

008

009

010

011

012

013

014

015

016

017

018

019

020

021

022

023

024

025

026

027

028

029

030

031

032

033

034

035

036

037

038

039

040

041

042

043

044

045

046

047

048

049

050

051

052

053

054

055

056

057

058

059

060

061

062

063

064

065

066

067

068

069

070

071

072

073

074

075

076

077

078

079

080

081

082

083

084

085

086

087

088

089

090

091

092

093

094

095

096

097

098

099

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

[ApiController]
[Route("[controller]/[action]")]    
public partial class AdvancedSearchDepartmentController : Controller
{
    private ISearchDataDepartment _search;
    public AdvancedSearchDepartmentController(ISearchDataDepartment search)
    {
        _search=search;
    }
 
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; GetAll()
    {
        await foreach(var item in _search.DepartmentFind_AsyncEnumerable(null))
        {
            yield return (Department_Table)item!;
        }
         
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; GetWithSearch(SearchDepartment s)
    {
        await foreach(var item in _search.DepartmentFind_AsyncEnumerable(s))
        {
            yield return (Department_Table)item!;
        }
         
    }
 
//has one key
    [HttpGet]
    public async Task&lt;Department_Table?&gt; GetSingle(long id){
        var data=await _search.DepartmentGetSingle(id);
       if(data == null)
        return null;
       return (Department_Table)data;
    }
 
                  [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; EqualValues_IDDepartment( long[]  values)
    {
        string? value=null;
        if(values.Length&gt;0)
            value=string.Join( ",",values);
        var sc=SearchDepartment.FromSearch(GeneratorFromDB.SearchCriteria.InArray,eDepartmentColumns.IDDepartment,value);
        await foreach (var item in _search.DepartmentFind_AsyncEnumerable(sc))
        {
         
            yield return (Department_Table)item!;
        }
    }
     [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; DifferentValues_IDDepartment( long[]  values)
    {
        string? value=null;
        if(values.Length&gt;0)
            value=string.Join( ",",values);
        var sc=SearchDepartment.FromSearch(GeneratorFromDB.SearchCriteria.NotInArray,eDepartmentColumns.IDDepartment,value);
        await foreach (var item in _search.DepartmentFind_AsyncEnumerable(sc))
        {
         
            yield return (Department_Table)item!;
        }
    }
         [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; EqualValue_IDDepartment( long  value)
    {
        var sc = GeneratorFromDB.SearchCriteria.Equal;
        await foreach (var item in _search.DepartmentSimpleSearch_IDDepartment(sc, value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; DifferentValue_IDDepartment( long  value)
    {
        var sc = GeneratorFromDB.SearchCriteria.Different;
        await foreach (var item in _search.DepartmentSimpleSearch_IDDepartment(sc, value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public  async IAsyncEnumerable&lt;Department_Table&gt; SimpleSearch_IDDepartment(GeneratorFromDB.SearchCriteria sc,  long value){
        await foreach(var item in _search.DepartmentSimpleSearch_IDDepartment(sc,value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; FindNull_IDDepartment(){
        var sc = GeneratorFromDB.SearchCriteria.Equal;
        await foreach(var item in _search.DepartmentSimpleSearchNull_IDDepartment(sc))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; FindNotNull_IDDepartment(){
        var sc = GeneratorFromDB.SearchCriteria.Different;
        await foreach(var item in _search.DepartmentSimpleSearchNull_IDDepartment(sc))
        {
            yield return (Department_Table)item!;
        }
    }
              [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; EqualValues_Name( string[]  values)
    {
        string? value=null;
        if(values.Length&gt;0)
            value=string.Join( ",",values);
        var sc=SearchDepartment.FromSearch(GeneratorFromDB.SearchCriteria.InArray,eDepartmentColumns.Name,value);
        await foreach (var item in _search.DepartmentFind_AsyncEnumerable(sc))
        {
         
            yield return (Department_Table)item!;
        }
    }
     [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; DifferentValues_Name( string[]  values)
    {
        string? value=null;
        if(values.Length&gt;0)
            value=string.Join( ",",values);
        var sc=SearchDepartment.FromSearch(GeneratorFromDB.SearchCriteria.NotInArray,eDepartmentColumns.Name,value);
        await foreach (var item in _search.DepartmentFind_AsyncEnumerable(sc))
        {
         
            yield return (Department_Table)item!;
        }
    }
         [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; EqualValue_Name( string  value)
    {
        var sc = GeneratorFromDB.SearchCriteria.Equal;
        await foreach (var item in _search.DepartmentSimpleSearch_Name(sc, value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; DifferentValue_Name( string  value)
    {
        var sc = GeneratorFromDB.SearchCriteria.Different;
        await foreach (var item in _search.DepartmentSimpleSearch_Name(sc, value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public  async IAsyncEnumerable&lt;Department_Table&gt; SimpleSearch_Name(GeneratorFromDB.SearchCriteria sc,  string value){
        await foreach(var item in _search.DepartmentSimpleSearch_Name(sc,value))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; FindNull_Name(){
        var sc = GeneratorFromDB.SearchCriteria.Equal;
        await foreach(var item in _search.DepartmentSimpleSearchNull_Name(sc))
        {
            yield return (Department_Table)item!;
        }
    }
    [HttpGet]
    public async IAsyncEnumerable&lt;Department_Table&gt; FindNotNull_Name(){
        var sc = GeneratorFromDB.SearchCriteria.Different;
        await foreach(var item in _search.DepartmentSimpleSearchNull_Name(sc))
        {
            yield return (Department_Table)item!;
        }
    }
         
 
 
     
 
 
}//end class

Finally , ExampleWebAPI . This will add the controllers from the ExampleControllers to show the use .

var assControllers = typeof(UtilsControllers).Assembly;
builder.Services.AddControllers()
              .PartManager.ApplicationParts.Add(new AssemblyPart(assControllers)); ;
        

Of course , the name Example will be replaced , from the template, with the name of your project

Db2Code–part 1–idea

From the second year that I have started programming, I find tedious to replicate the tables structure to code and to generate classes and SQL . I started asking questions – and no one has ever envisaged near me an ORM ( it was not a definition then ). I have made my own ORM – and it was paying . However, I did not know how to market – so oblivion was his fate.

Moving to this year , there are many ORM . One of those is EFCore – that has also a revers engineer / scaffolding ready : https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/ . But how generate all classes , controllers and others ? I was into https://learn.microsoft.com/en-us/dotnet/csharp/roslyn-sdk/source-generators-overview – but this does not allow programmers to modify what it generates. But , fortunately, this year EFCore7 has templates, based on t4 templates : https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/templates?tabs=dotnet-core-cli . That means that now we could generate whatever we need!

So this is the project : A DB2Code Visual Studio Template. You can download from https://marketplace.visualstudio.com/items?itemName=ignatandrei.databasetocode . Also, the source code is at https://github.com/ignatandrei/queryViewer/ . Download, modify the connectionDetails.txt and it will generate from you everything to WebAPI from SqlServer or Sqlite ( more providers soon). Also, it generates different projects ( models, context, controllers) – and you can also modify the templates. It works ( for the moment) just with tables with 0 or 1 PK.

The next parts will be only about the challenges to develop such a solution.

TILT–specifications –part 2

This is an application to store what I have learned today / each day .

It will be one note string per day , note will be no more than 140 characters.

It has tags – programming, life, and so on. Can add one link to the note.

Can be saved on local ( desktop, mobile )or on cloud ( site). If wanted will be sync by having url part and password

Will have a calendar to see every day what have I learned – missing days will have an red X

Reports:

Can be exported by date end -date start into Excel Word PDF

Will show what you have learned one year before

Default are all public – if you want to be private , should pay or copy the application.

Tools used:

notepad

Category: DB2Code

Tools used:

Andrei Ignat weekly software news(mostly .NET)