C# - How to connect to a MSSQL database using a SqlConnection

on C#, MSSQL

You can use the SqlConnection class to connect to a MSSQL server. If we have a database called MyDb with a simple table called MyEntities:

CREATE TABLE [dbo].[MyEntities](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NOT NULL,
   CONSTRAINT [PK_MyEntities] PRIMARY KEY CLUSTERED 
   (
	  [Id] ASC
   )
)

In order to connect to a database we need a connection string, there are different variations of these. A basic one would be:

  • "Data Source=<ServerAddress>;Initial Catalog=<Database>;Integrated Security=<True/False>;"

Here are two examples with values:

  • "Data Source=localhost;Initial Catalog=MyDb;Integrated Security=True;"
  • "Data Source=localhost;Initial Catalog=MyDb;User Id=MyDbUser;Password=MyDbPassword1234;Trusted_Connection=True"

You can find many more examples of connection strings here.

When we have our connection string we can connect to the database using a new SqlConnection:

using (var sqlConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=MyDb;Integrated Security=True;"))
{
    using (SqlCommand command = new SqlCommand("SELECT * FROM [dbo].[MyEntities];", sqlConnection))
    {
        sqlConnection.Open();
        SqlDataReader reader = command.ExecuteReader();
    }
}

In the above we create a new SqlConnection and provide it with our connection string. We wrap it in a using statement so that it is disposed of when we are done using it. One way to use the connection is to create a new SqlCommand and execute a reader from it. This way you can read returned data from a query row by row. I have left out the mapping of this to simplify the example.

I hope this simple example will be helpful to you, let me know what you think in the comments down below!