GenerateCrudServices with large db

ServiceStack
1

Hi, i was just trying the new GenerateCrudServices.
My problem currently is that i have big sql server, and by big i also mean with a lot of tables.
On my case i would need to include just a few tables, on a need basis.
I’m also working with a VPN and this make things very slow to load, and often i get a time out.
Tried to add IncludeService, and IncludeType, but it seem it still scan all tables before hitting those functions?
It is currently possibile to make it scan only what i need?
Thanks

2

I’ve added support for IncludeTables and ExcludeTables in this commit which will allow you to filter RDBMS tables from the CreateCrudServices connection which you would use instead of AutoRegister = true, e.g:

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    GenerateCrudServices = new GenerateCrudServices {
        // AutoRegister = true,
        CreateServices = new List<CreateCrudServices> {
            new CreateCrudServices {
                IncludeTables = new List<string> { "Employee", "Customer" }
            }
        },
    }
});

I’ll let you know when this change is on MyGet (takes about 30-45 mins for a successful builds).

1 Like
3

ok this change is now available from the latest v5.9.3 that’s on MyGet.

4

good i can see now is not loading all tables.

And i can call the new services :slight_smile:

Just out of curiosity i tried generating the code and got the following exception, running the x tool produce and empty file only with comments and using, the exception is shown if open on the browser.

x csharp https://localhost:5001 -path /crud/all/csharp 

System.ArgumentNullException: Value cannot be null. (Parameter 'source')
   at System.Linq.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument)
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source, Func`2 predicate)
   at ServiceStack.GenerateCrudServices.ResolveMetadataTypes(CrudCodeGenTypes request, IRequest req, Action`1 generateOperationsFilter) in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack.Server\GenerateCrudServices.cs:line 921
   at ServiceStack.GenerateCrudServices.GenerateSource(IRequest req, CrudCodeGenTypes request, Action`1 generateOperationsFilter) in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack.Server\GenerateCrudServices.cs:line 822
   at ServiceStack.CrudCodeGenTypesService.Any(CrudCodeGenTypes request) in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack.Server\GenerateCrudServices.cs:line 1324
5

This NRE should be resolved from this commit but it’s suggesting there was an error retrieving columns for a specific table for which I’ve included Error Info in this commit. So if you enable logging in the next version it should yield more info about the error.

1 Like
6

Good thanks i will check it later

7

This change is now available on the latest MyGet.

8

Tried and now is not crashing, but it’s generating a lot of code for services that i have not defined, it looks like if the new table filter is not taken in account.

For example i have set to auto generate service for only one table witch produce the QueryCDCENTITIES from that table and i can see it on the metadata and invoke it fine.
But i don’t see it on the generated code, but i see many other tables that i don’t have enabled.

This is how i have set it up.

   Plugins.Add(new AutoQueryFeature {
                MaxLimit = 100,                
                GenerateCrudServices = new GenerateCrudServices {
               
                    CreateServices = new List<CreateCrudServices> {
                        
                        new CreateCrudServices {
                            Schema = "dbo",
                            IncludeTables = new List<string> { "CDC_ENTITIES" },
                            IncludeCrudOperations = new List<string> {"Query"}
                        }
                    },
    }
            });
9

What generated code do you not see it on? /types/csharp?

Please be more specific, i.e. what routes / generated code are you referring to, where are the “many other tables” appearing that shouldn’t be?

10

Ok on this endpoint i see only my table that i have specified on IncludeTables, and other services that i have coded my self.

I was trying generating with

x csharp https://localhost:5001 -path /crud/all/csharp

I think i found why i was confused so the /crud/all/csharp try to generate all possible endpoints for all tables without taking in consideration the IncludeTables Filter right?

From the log i found multiple error generated when calling the /crud/all/csharp

ERROR: GetTableSchemas(): Failed to GetTableColumns() for '"TABLE_XXX"', Exception: Internal connection fatal error.
...
ERROR: GetTableSchemas(): Failed to GetTableColumns() for '"TABLE_XXX"', Exception: Internal connection fatal error.

So for what i see it tries to generate services for all tables, but it generate only a few because of those multiple errors.

Maybe it’s the VPN? it is very slow my connection to the database.

EDIT:

there is also a lot of these:

ERROR: GetTableSchemas(): Failed to GetTableColumns() for '"TABLE_XX"', Exception: ExecuteReader requires an open and available Connection. The connection's current state is closed.

11

Right, it’s not looking at any of your CreateCrudServices generation rules, you’d need to include the filters with the call, e.g:

/crud/all/csharp?Schema=dbo&IncludeTables=CDC_ENTITIES,IncludeCrudOperations=Query

See the CrudCodeGenTypes for the full options:

Seems to be a fairly common db connection failure:

https://www.google.com/search?q=Internal+connection+fatal+error

It looks like the first error is killing the connection that’s causing the other db accesses in that method to fail. I’ve added a commit that if Exceptions cause db connections to fail, to reopen a new one.

1 Like