Creating SQL connection to MS SQL server

There are several ways of creating connection to SQL server from your code. Some may like using the fully automatized connecting offered by Visual Studio and using that hackable app.config file. I choose this method only if I develop or test something for myself at home.

If you created your database and your tables with SQL Management Studio, then you have to add this as a new connection in Visual Studio. Open Server Explorer tab, right click on an empty area and choose Add Connection…

Type in the address of your SQL server and the instance name optionally. If you installed SQL server locally onto your computer, then this could be localhost\. If you installed with default instance name, you do not have to add this name. If you install SQL Express, you may choose not the default instance name (mostly MSSQLSERVER), but SQLEXPRESS. In this case the server name should look like this: localhost\SQLEXPRESS

Try to use Windows authentication anytime you can! You can admin every permissions on the server using this authentication type. If you choose SQL Server Authentication, then you’ll have to admin the user too.

Select the database. If you entered the server name correctly, Visual Studio tries to download all the available database names right after you click on this dropdown list.

After selecting database, click on Advanced… button. Here you can set up many additional and important settings, such as Pooling: False, Encrypt: True (if you have centralized CA server), Integrated Security: True (if you’re using Windows Authentication).

Setting all these properties, you’ll get the generated connection string, something like this: Data Source=localhost\;Initial Catalog=…

Testing the connection should give you a ‘Test connection succeeded’ message.

After this you can create new Data Source in Visual Studio, choosing your previously created connection. This going to be stored in your App.config file, if you finish this wizard.

One of the other way is that you create your own connection string (something similar that was mentioned above) and store this string in your code. The best way to connect to SQL in your program is using some static class for new new SqlConnection:

static class SqlConnectDB
    public static SqlConnection New(string ip, string port, string db, string instance)
        return new SqlConnection("Data Source=tcp:" + ip + "\\" + instance + "," + port + ";Integrated Security=True;Encrypt=False;TrustServerCertificate=True;PersistSecurityInfo=True;database=" + db + ";connection timeout=30;Pooling=False");

Also you can create more methods here, where you open and close the connection. It’s up to you.