mydomain
No ADS
No ADS

Connect to SQL Server Database in C#

  1. Introduction
  2. Create a Data Connection to connect from Visual Studio to SQL Server
  3. Connect to SQL Server from C#
  4. Working with SQL Server Database using C#

1. Introduction

This document is based on:
  • SQL Server 2014 (OK for others SQL Server).
  • Visual Studio 2013 (OK for other VS)

2. Create a Data Connection to connect from Visual Studio to SQL Server

No ADS
Create Data Connection on Visual Studio which allows you see data directly on Visual Studio.
Essentially,your C# program connects to SQL Server without creating Data Connections in Visual Studio. But creating the Data Connection help you know for sure that you have successfully connected to the SQL Server.
Add Server:
First, you need to add Server. It is computer which installed SQL Server, it may be your computer
In Server Explorer:
Add Data Connection
Data Connection was created.
Connection String is a string with information that allows you to connect with Database from C#. You need to retriave this string. Right click to Data Connection just newly created and select Properties

3. Connect to SQL Server from C#

No ADS
Create a Project named "ConnectSQLServer"
Project was created.
You need some utility classes which help to connect to SQL Server database.
DBSQLServerUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Tutorial.SqlConn
{
    class DBSQLServerUtils
    {
        public static SqlConnection
                 GetDBConnection(string datasource, string database, string username, string password)
        {
            //
            // Data Source=TRAN-VMWARE\SQLEXPRESS;Initial Catalog=simplehr;Persist Security Info=True;User ID=sa;Password=12345
            //
            string connString = @"Data Source="+datasource+";Initial Catalog="
                        +database+";Persist Security Info=True;User ID="+username+";Password="+password; 
            SqlConnection conn = new SqlConnection(connString);
            return conn;
        } 
    }
}
DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Tutorial.SqlConn
{
    class DBUtils
    {
        public static SqlConnection GetDBConnection()
        {
            string datasource = @"tran-vmware\SQLEXPRESS"; 
            string database = "simplehr";
            string username = "sa";
            string password = "1234";
            return DBSQLServerUtils.GetDBConnection(datasource,  database, username, password);
        }
    }
}
Code to test connection:
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data.SqlClient;

namespace ConnectSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Getting Connection ...");
            SqlConnection conn = DBUtils.GetDBConnection();
            try
            {
                Console.WriteLine("Openning Connection ...");
                conn.Open();
                Console.WriteLine("Connection successful!");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
            Console.Read();
        }
    }
}
Test connection:
Getting Connection ...
Openning Connection ...
Connection successful!

4. Working with SQL Server Database using C#

No ADS
See more:
No ADS
No ADS