How to specify schema name in connectionstring in asp.net

Question

How can I specify a schema name in SQL ConnectionString using ASP.net dynamically. I have a single database with multiple schema. I need to fetch data from table belonging to particular schema.

Answer 1

You can only specify the database and user details in the connection string.

To retrieve data from a specific schema, you need to reference that schema in your query. For example;

SELECT field1, field2 FROM [Schema].[Table]

Answer 2

I would like to reference here an older thread, which is definitely useful in this question:

Possible to set default schema from connection string?

You can only set the default schema to the user itself. You can try an ALTER USER statement, where you can define a default schema to the user. It means you can create a user to each schema, if that is what you need.

Answer 3

Each user has their own schema and that is the default schema. Once logged in you can execute:

ALTER SESSION SET CURRENT_SCHEMA = myschema

So you need to execute an extra statement after connecting.

Answer 4

Here is how to do it. I just tried it.

"ConnectionStrings": {
"<Connection Name>": "Host=<Database Server>;Database=<Database Name>;Port=5432;Username=<WindowUserName>;Password=<WindowsPassword>;IntegratedSecurity=true;SearchPath=MySchema"