[ACCEPTED]-Specifying Port With SqlConnectionStringBuilder?-connection-string

TL;DR

remove the space before the port number 25 within your data source string:

{Data Source=".\TESTSERVER, 1433";Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE}

and let it 24 look like this

{Data Source=".\TESTSERVER,1433";Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE}

Long Answer

After a little playing around 23 you can omit the extra quotes by removing 22 the whitespace between the comma and the 21 port number:

var stringBuilder = new SqlConnectionStringBuilder();
var sqlCommand = "select TOP 100 * from MyTable;";

stringBuilder.IntegratedSecurity = true;
stringBuilder.InitialCatalog = "MyCatalog";
stringBuilder.DataSource = @"myServer\InstanceName,1433";

// This will give the connection string:
// "Data Source=myServer\\InstanceName,1433;Initial Catalog=MyCatalog;Integrated Security=True"
using (var connection = new SqlConnection(stringBuilder.ToString()))
using (var command = new SqlCommand(sqlCommand, connection))
using (var adapter = new SqlDataAdapter(command))
{
    var table = new DataTable();
    connection.Open();
    adapter.Fill(table);
}

Unfortunately this still leads 20 to the same error message as the one you 19 provided. So i took a deeper look into the 18 network communication and found out, if 17 you don't pass in a port number it first 16 tries a tcp connection to port 1433 (as 15 usual) but it will stay unconnected. Then 14 it tries a udp connection to port 1434 and 13 receives from their a dynamic port number 12 that will be used for a second tcp connection 11 where the data will flow.

By using Process 10 Monitor from Sysinternals you can watch 9 this process:

// The first try by using TCP port 1433
WindowsFormsApplication.vshost.exe  5480    TCP Reconnect   MyMachine:53202 -> SqlServerInstance:1433   SUCCESS
WindowsFormsApplication.vshost.exe  5480    TCP Reconnect   MyMachine:53202 -> SqlServerInstance:1433   SUCCESS
// The second try by using UDP port 1434
WindowsFormsApplication.vshost.exe  7664    UDP Send    MyMachine:50245 -> SqlServerInstance:1434   SUCCESS
WindowsFormsApplication.vshost.exe  7664    UDP Receive MyMachine:50245 -> SqlServerInstance:1434   SUCCESS
// Taking informations out of UDP connection to connect to dynamic assigned port
WindowsFormsApplication.vshost.exe  7664    TCP Connect MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
// Closing of dynamic assigned port
WindowsFormsApplication.vshost.exe  7664    TCP Disconnect  MyMachine:53209 -> SqlServerInstance:58904  SUCCESS

By using the explict port number 8 I'll only see the first given lines and 7 afterwards the exception is thrown. So defining 6 the default port number leads to another 5 behavior than not defining any port number!

Nevertheless 4 if you need to define a explicit port number 3 for your server, simply avoid the space 2 after the comma and your connection string 1 looks pretty.

Just take a look at the http://www.connectionstrings.com/sql-server-2008

SQL Server 2008 3 connection string does not contain extra 2 quotes around the server indtance name. Your 1 connection string should be

Data Source=.\TESTSERVER, 1433;Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE

MSDN SqlConnection.ConnectionString Property

The name or network address of the instance 3 of SQL Server to which to connect. The port 2 number can be specified after the server 1 name:

Example : MyDataSource,1433

For me its work like this:

//read instance
builder.DataSource.Split(new Char[] { ',' })[0]
//read port number
builder.DataSource.Split(new Char[] { ',' })[1]

//write
builder.DataSource = builder.DataSource.Split(new Char[] { ',' })[0] + ",1234";

0