Specify Sql Server Schema in connectionString C#

Question

I have got a console app that is connecting to a Sql Server and getting some values. I have the Schema in the Select(dbo):

        var ds = new DataTable("test");
        var connSqlRemoto = new SqlConnection("Server=myserverIP;Database=myDataBase;User Id=user;Password=pass;Integrated Security=False;Connection Timeout=60");
        connSqlRemoto.Open();
        var nombreBbdd = connSqlRemoto.Database;
        var daASqlRemoto = new SqlDataAdapter();
        var cmdSqlRemoto = new SqlCommand("SELECT * FROM " + nombreBbdd + ".dbo.myTable;", connSqlRemoto);
        cmdSqlRemoto.CommandTimeout = 1200;
        cmdSqlRemoto.Parameters.Clear();
        daASqlRemoto.SelectCommand = cmdSqlRemoto;
        daASqlRemoto.Fill(ds);

I want the Schema to be dynamic. Is it possible to pass the Schema in the connection string? Something like this is not working:

 Server=myserverIP;Database=myDataBase/dbo;User Id=user;Password=pass;Integrated Security=False;Connection Timeout=60

or

Server=myserverIP;Database=myDataBase.otherSchema;User Id=user;Password=pass;Integrated Security=False;Connection Timeout=60

Thanks.

No, this is done at the database User level, not in the connection string — Vishal Suthar
– Vishal Suthar, Commented Sep 26, 2016 at 9:28
I want teh Schema to be dynamic. So: Is it possible to pass the Schema in the connection string?..how can the schema be dynamic,when you pass in connection string — TheGameiswar
– TheGameiswar, Commented Sep 26, 2016 at 9:30
ok, thank you. @TheGameiswar Because it is an app that is receiving different connection strings. — Za7pi
– Za7pi, Commented Sep 26, 2016 at 9:31

PhillipH · Accepted Answer · 2016-09-26 09:37:43Z

2

Probably the easiest way to achieve a per-connection schema selection is to map your schema onto users, and connect to the database using the correct user. This will mean they will automatically query their default schema.

answered Sep 26, 2016 at 9:37

PhillipH

6,2221 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deepak Sharma · Accepted Answer · 2016-09-26 09:31:32Z

1

No. You can not pass schema with connection string. But you can pass schema in sqlcommand like this.

var schema=".dbo." -- you can set it globally or can change dynamically
cmdSqlRemoto = new SqlCommand("SELECT * FROM " + nombreBbdd + schema + "myTable;", connSqlRemoto);

answered Sep 26, 2016 at 9:31

Deepak Sharma

4194 silver badges14 bronze badges

2 Comments

Za7pi Over a year ago

Yes, but I have got the connectionstring in a field of the database and i would want not to create another field...

Deepak Sharma Over a year ago

In that case I suggest you to save schema along with database in same column. e.g instead of saving "dbname" save "dbname.dbo"

Collectives™ on Stack Overflow

Specify Sql Server Schema in connectionString C#

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related