3

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.

Vladislav Povorozniuc
  • 2,149
  • 25
  • 26
Za7pi
  • 1,338
  • 7
  • 22
  • 33
  • [No, this is done at the database User level, not in the connection string](http://stackoverflow.com/a/3282716/1042848) – Vishal Suthar Sep 26 '16 at 09:28
  • 1
    `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 Sep 26 '16 at 09:30
  • ok, thank you. @TheGameiswar Because it is an app that is receiving different connection strings. – Za7pi Sep 26 '16 at 09:31

2 Answers2

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.

PhillipH
  • 6,182
  • 1
  • 15
  • 25
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);
Deepak Sharma
  • 409
  • 4
  • 14
  • Yes, but I have got the connectionstring in a field of the database and i would want not to create another field... – Za7pi Sep 26 '16 at 09:32
  • In that case I suggest you to save schema along with database in same column. e.g instead of saving "dbname" save "dbname.dbo" – Deepak Sharma Sep 26 '16 at 09:36