Creating and building database connection strings in .net are pretty much an inevitable part of software development and if you know the format and syntax of the required connection string then you can of course build this by creating the string manually, but a far easier method is to use one of the DbConnectionStringBuilder classes which are built into the framework. There are classes for Oracle, OLE, ODBC, entity framework and SQL server, in this post we will take a look at the SqlConnectionStringBuilder class.
To use the connection string builder class we first need to create a new instance of SqlConnectionStringBuilder
then from which we can set the relevant properties such as data source, user id, etc…
In the below example we will create a new connection sting builder which uses SQL login credentials rather then windows authentication (Integrated Security).
Dim MyConnection As New SqlClient.SqlConnectionStringBuilder MyConnection.DataSource = "MySQLServerInstanceName" MyConnection.InitialCatalog = "MyDatabaseName" MyConnection.IntegratedSecurity = False MyConnection.UserID = "MyDBUserName" MyConnection.Password = "MyDBPassword" MyConnection.LoadBalanceTimeout = 600
Now we have created our connection string builder we can use it to generate the connection string itself by calling its ToString
method.
Dim MyConnectionString As String = MyConnection.ToString
Our completed connection string from the above example will looks like the following
Data Source=MySQLServerInstanceName;Initial Catalog=MyDatabaseName;Integrated Security=False;User ID=MyDBUserName;Password=MyDBPassword;Load Balance Timeout=600
The other connection builder classes all follow the same theme but with their own specific property requirements.