How to connect sql server database connection in c#

How to Connect SQL Server Database Using C#

This article describes the basic code and namespaces required to connect to a SQL Server database and how to execute a set of commands on a SQL Server database connection using C# in your application.

The database is one of the important aspects of accessing data for any programming language. It is necessary for a programming language to have the ability to work with databases. C# is no different.

It can work with different types of databases, including the most common ones such as Oracle and Microsoft SQL Server.

I am using Microsoft SQL Server Management Studio 2008 R2, which is a free database software provided by Microsoft.

>> SQL Server Management 2008 R2 Download Link

1. Setting Up Database and Tables

You can create the database using the SQL Server UI or by using the following commands:

CREATE DATABASE finacial;
USE finacial;

Database Table Creation:

CREATE TABLE [recharge](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [mobilenetwork] [varchar](50) NULL,
    [mobilenumber] [varchar](50) NULL,
    [amount] [varchar](40) NULL,
    [date] [varchar](60) NULL,
    [status] [varchar](50) NULL
);

---

2. Understanding ADO.NET and Namespaces

Open Microsoft Visual Studio → New Project → Console Application (or Windows Forms Application). In your code header, add the extensions:

using System.Data;
using System.Data.SqlClient;

System.Data.SqlClient:

This namespace is a .NET framework component that contains all the classes needed to connect to an SQL Server database to read, write, and update data. It provides the functionality to create database connections and execute SQL commands. In this article, we will work strictly with SQL Server databases.

Connection:

The first main step is the connection. Connecting to a database typically consists of setting up parameters, preparing a command to execute, retrieving results, and finally closing the connection.

SqlConnection con = new SqlConnection();

The SqlConnection class has three overloaded versions of the constructor:

• SqlConnection() - Initializes a new instance with no string.
• SqlConnection(string connectionString) - Initializes a new instance using the provided connection string.
• SqlConnection(string connectionString, SqlCredential credential) - Connects using a connection string alongside a secure user ID and password object.

---

3. SQL Server Authentication & Connection Strings

To connect to a SQL Server, a .NET application needs information about the server name, the target database, and authentication credentials. There are two types of authentication:

• Windows Authentication (Does not require ID/Password as it uses computer login)
• SQL Server Authentication (Requires Login ID and Password)

SQL Server Connection String (SQL Authentication):

string connetionString = "Data Source=ServerName; Initial Catalog=DatabaseName; User ID=UserName; Password=Password;";

Windows Authentication (Local Named Instance):

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS; Database=finacial; Integrated Security=True;");

Connect via an IP Address:

string connetionString = "Data Source=IP_ADDRESS,PORT; NetworkLibrary=DBMSSOCN; Initial Catalog=DatabaseName; User ID=UserName; Password=Password;";

Note on SSPI: SSPI stands for Security Support Provider Interface. Using Integrated Security=SSPI; or Integrated Security=True; ensures you are connecting via Windows Authentication instead of SQL Authentication.

---

4. Windows Forms Example: Testing Database Connection

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace sqlwindowsam
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = "Data Source=DESKTOP-19Q8E5J\\SQLEXPRESS; Initial Catalog=finacial; integrated security=true;";
            SqlConnection cnn = new SqlConnection(connetionString);
            
            try
            {
                cnn.Open();
                MessageBox.Show("Connection Open..... ! ");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Cannot open connection... ! ");
            }
        }
    }
}

---

5. Console Application: Fetching Data with SqlDataAdapter

In the console application, we can use SqlDataAdapter to execute our queries and fill a DataTable.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace sqlsampl
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS; Database=finacial; Integrated Security=True;"))
            {
                SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM recharge", con);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["mobilenumber"]);
                }
                
                Console.ReadKey();
            }
        }
    }
}

Using Statement: The purpose of the using statement in C# is to ensure that unmanaged resources (like database connections) are automatically and safely closed and disposed of when the code block completes.

When you run the application, you can expect the table outputs to appear properly in the console.

---

Related Tutorials

• How to Uninstall a SQL Server Instance in SQL Server 2008R2 and 2008
• Tips and tricks to increase PC performance on Windows 10
• Using jQuery to build table rows from AJAX response(json)