SQL Connection

The SQLConnection class allows you to connect to a database and run query against it, it is the first step to interacting with databases, For our examples we are going to use a database table called Person and it's only going to have 3 columns:

1. Id
2. FirstName
3. LastName

USE [Test]

GO

/****** Object:  Table [dbo].[Person]    Script Date: 03/20/2014 11:35:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Person](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [FirstName] [varchar](50) NULL,

      [LastName] [varchar](50) NULL

 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

      [Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

with our table built let's get started, we are just going to use a simple console app to demonstrate making a connection and running a query.

To open a connection, the first thing we need is a connection string, think of this as connection information about your database for your SqlConnection class. A great place to get connection string is in visual studio if you have your database selected in the server explorer, the connection string will be in the properties window.

Now that you have your connection string, simply create a variable in your code; for demo purposes only, never do this in real development; in production you should always keep your Connection string in the web/app config, or if your using SharePoint in a property bag, basically some place where you could change it without requiring a re-deploy.

A SqlConnection class has to be disposed of, so this can be done either by instantiating it inside a using clause, or calling the dispose method on the object.

• Using(var conn = new SqlConnection(connectionString));
• conn.dispose();

Not only do you have to dispose it, but you also have to open and close it before and after you use it. I do this in a try, catch and finally block.

using System;

using System.Data.SqlClient;

namespace pc.databaseAccessExample {

    class Program {

        static string cs =

          @"Data Source=BEAST\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args) {

            using (var conn = new SqlConnection(cs)) {

                try { conn.Open(); }

                catch (SqlException sqlEx) { Console.WriteLine(sqlEx.Message); }

                catch (Exception Ex) { Console.WriteLine(Ex.Message); }

                finally { conn.Close(); }

            }

        }

    }

}

if you run this, nothing will happen, because well we don't run any sort of instructions against the database, but this will handle the opening, closing and disposing of your database connection.

One caveat if you are using .net core you have to add the "System.Data.SqlClient" nuget package.
in the terminal type "dotnet add pacakge System.Data.SqlClient"