Most businesses prefer SQL Server for their organization as it offers higher productivity, enhanced business agility, better risk mitigation, and efficient working. Like applications, if the SQL Server is not properly maintained, it will affect your business and its performance. This blog highlights the fundamentals of Database Connectivity and helps you establish a C# SQL Server connection.

Introduction to Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS) that helps store and retrieve information as per the demand. Developed by Microsoft, it is one of the best designs to compete with Oracle Database and MySQL. Also, the Software Product supports different Business Intelligence Operations, Transaction Processing, Data Management, and Analytical Operations. The server has numerous versions managed and administered using the Structured Query Language (SQL) language.

Nowadays, various businesses carry sensitive customer information and share files through a network. In this case, using the Microsoft SQL Server will add increased security, speed, and reliability. Furthermore, it helps in maintaining a backup system. There are numerous benefits of using SQL Server in the business world. It also supports different database types.

Fundamentals of Database Connectivity

Various Programming Languages like C# and .Net are compatible with Oracle and Microsoft SQL Server. Also, they follow the same logic with each database in most cases. Here are a few concepts common for all Databases.

• Creating a Connection: The first and foremost step is to establish a connection. In order to work with Database information, establishing a connection is a must. Follow the below-listed parameters to create a connection.
  • Database Name or Data Source: Database Name refers to the name of the Database to which the Developer needs to create a connection. These are case-sensitive with a maximum length of 64 bytes. It comprises letters, numbers, underscores, and symbols. Also, every connection is allowed to work with one Database at a time.
  • Credentials: Secondly, to establish a secure connection to the Database, make sure to generate a username and strong password. This parameter allows only the privileged users to connect and use the database.
  • Optional Parameters: There are a few optional parameters to establish a better connection, such as details on how .net must handle connections or how long the connections will stay active. These parameters can inform users if no action takes place. Also, it helps determine if the connection to the Database needs closure.

• Fetch data from the Database: As you create a connection to the Database, the next step is to select information from the source. C# executes ‘SQL’ statements that can help fetch information from the Database tables. Hence, to select specific information from the Database, it is recommended to execute the ‘SQL’ select command.

• Insert Information into the Database: C# Programming Language is a great option to insert data into the Databases. All you require is to add or specify values in C# for each row that you want to insert.

• Update the Existing Data: C# Programming Language is also used for updating the previous data records in the Database. Again, you need to add or specify values in C# for each row that you want to update.

• Remove Data: Use C# for deleting any previous or new data records from the Database. Select or specify commands for each row in C# that you want to delete.

Now that you’re familiar with the fundamentals of Database Connectivity, let’s dive straight into the C# SQL Server connection.

Steps to Establish C# SQL Server Database Connection

There is a single and simple method to connect to a Database. A code that will help summarize how to establish a connection to a Database is shared in this section. Let’s say you want to create a connection to a Database named Demodb. Username (sa) and Password (demo123) are credentials that will make all the privileged users connect to a Database.

Follow the below-listed steps to easily establish a C# SQL Server Database connection.

Using a simple connect button, users can establish a connection to the Database. Look at the Windows forms application to learn how to work with Databases.

Step 1: Create a New Project

• To get started with C# SQL Server, open Visual Studio to create a new project. Once the video studio is active, go to the menu option at the top. Select “New” and click on “Project“.

• Mention your project name and choose a location from the browser. Now, press “OK“. Also, make sure to select your project type as a “Windows Forms” application.

• Before you move on to the next step, make a note of these:
  • You can create various types of projects from the Project dialog box in Visual Studio. Go to the left-hand side and click on the “Windows” option.
  • Now, select “Windows Forms Application” from the list. Name the application and provide a location where you want to save the project. In this example, we have saved the application as “DemoApplication”.
  • Lastly, press the “OK” button to create a project in the Visual Studio.

Step 2: Connect

• Go to the toolbox and add a button to the Windows form. Mention the button’s text property as “Connect“. Here is how the button will appear on your screen.

Step 3: Add Event Handler

• To add the Event Handler to the code, make sure to double-click on the form. The Event Handler is used for the Button Click event. Copy the below-listed code and paste it into the Event Handler.

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 DemoApplication1
{
 public partial class Form1 : Form
 {
  public Form1()
  {
   InitializeComponent();
  }

  private void button1_Click(object sender, EventArgs e)
  {
   string connetionString;
   SqlConnection cnn;
   connetionString = @"Data Source=WIN-50GP30FGO75;Initial Catalog=Demodb;User ID=sa;Password=demol23";
   cnn = new SqlConnection(connetionString);
   cnn.Open();
   MessageBox.Show("Connection Open  !");
   cnn.Close();
  }
 }
}

Code Explanation

• In the first stage, you create variables. These variables help generate the connection string. Further, it aids in establishing a C# SQL Server Database connectivity.

• Now, in the next step, you will create a connection string. Make sure to place and specify it in the right manner for C#. It will help in a better understanding of the connection string. A connection string comprises:
  • Data Source: It refers to the server name where a Database is maintained. In this example, the machine on which the Database resides is WIN- 50GP30FGO75.
  • To specify the Database name, you require the initial catalog.
  • To establish a connection with the Database, create credentials – userID and password.

• In the third step, the connection string is assigned to the variable cnn. It is a SqlConnection type mostly used to help create a connection with the Database.

• Now, the Open method of the cnn variable is exercised to establish a connection. As the connection is established, a message will display on your screen confirming the status.

• As the operation is successful, add a closure to the connection. It is best to close the connection if no other changes are necessary.

• Execute the project using Visual Studio and place the above code correctly in C#. We assure if done properly, you will get the right output. Also, click the “Connect” button as the form displays on your screen.

Output

You will notice that the connection to the Database is created as you press the connection button on the screen from the output. Here is how the output will appear on the screen.

That’s it, you’ve successfully established the C# SQL Server Database connection.

Conclusion

Microsoft SQL Server is a Relational Database Management System (RDBMS) that helps businesses work efficiently at all times. The Relational Database is generally used to store, retrieve information, Business Intelligence operations, manage data, and perform analysis.

Using Microsoft SQL Server adds more security, speed, and reliability. In this post, we have shown how to establish a C# SQL Server connection. Follow the above-listed fundamentals of Database connectivity and steps to easily build a C# SQL Server Database connectivity.