SQL Connection String in C#

1. Basics of Database Connectivity in C#
2. Example of SQL Connection String in C#

In this tutorial, we’ll discover how to use the connection string feature of the C# programming language to perform different functionalities.

Any programming language needs to be able to get data from a database. The ability to work with databases is an absolute requirement for any programming language.

This remains true for C# as well. It’s compatible with a variety of databases.

Many databases, including Oracle and Microsoft SQL Server, can be used. Before we begin, we must have a clear understanding of the fundamentals of database connectivity.

Basics of Database Connectivity in C#

Both C# and .Net are compatible with most databases, with Oracle and Microsoft SQL Server being the most common examples. However, the methodology behind working with any database follows a mostly similar pattern across all of them.

The following are some principles applicable while working with databases that are shared by all databases.

1. Connection String - The connection is the obvious initial step to take when working with the data contained in a database. The parameters listed below are an essential part of the connection made to a database.

   For C# to comprehend the connection string, it is necessary to provide accurate information regarding the connecting string. The following elements make up the components of the connection string.

   1.1 Data Source - This is the server’s name where the database is stored, also known as the data source. In our particular instance, it is stored on a computer designated as (LocalDB)\MSSQLLocalDB.
   1.2 Initial Catalog - The name of the database is determined by using the Initial Catalog as the criteria. The name of the database to which the connection needs to be made is the first crucial aspect to consider.

   Only one database can be accessed with a single connection at any given time.

2. SQL Command - The user is given the ability to query as well as send commands to the database via SqlCommand in C#. The SQL command is provided by the object that represents the SQL connection.

   ExecuteReader is the method that is used for the results of the query, and ExecuteNonQuery is the method that is used for the insert, update, and delete commands. For example, the connection string used in the following example is:

SqlConnection con = new SqlConnection(
    @"Data Source=(LocalDB)\MSSQLLocalDB ; Initial Catalog= ConStringExample; Integrated Security=True");

Example of SQL Connection String in C#

Now, let’s look at a simple example of something that must be maintained to establish a connection to a database. In this demonstration, we will connect to a database using a connection string, and then we will use a button to insert Name and Designation to the database called ConStringExample.

1. To get started, create a new Windows Form project and give it any name you prefer; in this example, we name it chsarpConnectionStringByZeeshan.

   

2. Use two labels, and then utilize textboxes with the labels Name and Designation and named as nametextBox and desigtextBox fields.

   

3. After utilizing labels and textboxes, the next step is to include a button with the label Add Data and the name addbtn. Clicking this button will cause the data to be added to the database.

   

Now that the form has been thoroughly prepared, it is time to create the code for the Add Data button.

1. To begin, we have to import the following libraries.

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

2. To establish a connection with the database, we will create a connection between databases by utilizing the connection string given below.

   SqlConnection con = new SqlConnection(
       @"Data Source=(LocalDB)\MSSQLLocalDB ; Initial Catalog= ConStringExample; Integrated Security=True");
   

3. Now, let’s create an object of type SqlCommand and give it the name cmd.

   SqlCommand cmd;
   

4. Double-click on the Add Data button. This will create a new event where we’ll write the code.

   

5. Now, we will use the SqlCommand and pass the insert query we have prepared as a parameter. This will insert data into the table that has been provided.

   SqlCommand cmd = new SqlCommand("insert into Data1 (Name, Designation) values ('" +
                                       nametextBox.Text + "','" + desigtextBox.Text + "')",
                                   con);
   cmd.ExecuteNonQuery();
   

6. After successfully inserting the data, we will display a message stating that the data has been inserted.

   MessageBox.Show("Data inserted successfully");
   

7. In the final step, we’ll close the connection and clear the textboxes.

   con.Close();
   nametextBox.Clear();
   desigtextBox.Clear();
   

Complete Source Code:

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

namespace chsarpConnectionStringByZeeshan {
  public partial class Form1 : Form {
    SqlConnection con = new SqlConnection(
        @"Data Source=(LocalDB)\MSSQLLocalDB ; Initial Catalog= ConStringExample; Integrated Security=True");
    SqlCommand cmd;

    public Form1() {
      InitializeComponent();
    }

    private void addbtn_Click(object sender, EventArgs e) {
      con.Open();
      SqlCommand cmd = new SqlCommand("insert into Data1 (Name, Designation) values ('" +
                                          nametextBox.Text + "','" + desigtextBox.Text + "')",
                                      con);
      cmd.ExecuteNonQuery();
      MessageBox.Show("Data inserted successfully");
      con.Close();
      nametextBox.Clear();
      desigtextBox.Clear();
    }
  }
}

Output: