Call Us: +1 917 512 2849

C# Database connection – how to set it up correctly

Before explaining the connection let’s explain what C# and a database are.

C_databese_connection_sql

What is C#?

C# (pronounced “See Sharp”) is a modern, object-oriented, and type-safe programming language. C# enables developers to build numerous types of safe and robust apps that run in .NET. C# has its roots in the C family of languages and will be immediately familiar to C, C++, Java, and JavaScript programmers. This tour provides an overview of the major components of the language in C# 8 and earlier. If you wish to explore the language through interactive examples, consider the introduction to C# tutorials.

Programming language C# is component- and object-oriented. C# has language structures that explicitly support these principles, making C# a natural language in which to develop and use software components. Since its origin, C# has added features to support new workloads and emerging software design practices. At its core, C# is an object-oriented language. You define types and their behavior.

What is SQL Server?

SQL Server is a relational database management system, or RDBMS, created and marketed by Microsoft.

Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interfacing with relational databases. SQL Server is tied to Transact-SQL, or T-SQL, Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.

SQL Server works exclusively on the Windows environment for more than 20 years. Microsoft made it available on Linux in 2016. In October 2016, the Windows and Linux-compatible SQL Server 2017 versions became generally available.

Database connectivity basics

C# and. Most databases, including Oracle and Microsoft SQL Server, can be used with Net. However, the reasoning behind using each database is largely the same.

We’ll examine using Microsoft SQL Server as our database in our examples. Download and use Microsoft SQL Server Express Edition, a free database program offered by Microsoft, for educational purposes.

 

The concepts that apply to all databases while working with them are listed below.

  1. Connection: The connection is the first and most evident step in working with database data. The following parameters are typically included in a database connection.
  • The first crucial argument is the name of the database to which a connection needs to be made, also known as the data source. Only one database can be used by each connection at once.
  • Credentials – The username and password that must be used to login into the database are the following crucial factor. It makes sure the credentials for the login and password are valid for connecting to the database.
  • Extra parameters – You can enter optional parameters for each type of database to give.net more details about how to manage the connection to the database. For instance, one can give a parameter for the duration of the connection’s active state. If no action is taken after a predetermined amount of time, the parameter would
  1. Choosing data from the database – After the connection has been made, fetching the data from the database is a crucial next step. The ‘SQL’ select command can be used to access the database in C#. You can retrieve information from a particular database table by using the “SQL” query.
  2. Database record insertion – C# can also be used to add records to the database. For each record that needs to be inserted into the database, values can be given in C#.
  3. Database data updating – C# can be used to update already-existing records in the database. Each row that needs to be modified in the database can have new values supplied in C#.
  4. Database record deletion – C# can also be used to remove records from databases. In C#, select instructions can be used to define which rows should be erased.

 

Let’s move on to the following sections to examine how to carry out database operations in C# now that we have examined the logic behind each action.

SQL Command in C#

The user can query and send commands to the database using sqlCommand in C#. The SQL connection object specifies the SQL command. ExecuteReader method is used for query results, and ExecuteNonQuery is used for insert, update, and delete instructions. It is the approach that works the best for the various commands.

How to connect C# to Database

Let’s now take a look at the code that must be maintained in order to establish a connection to a database. In our example, we’ll establish a connection to Demodb, a database. Below are the credentials used to connect to the database.

Username: sa

Password: demo123

We’ll see a straightforward Windows forms database application. The database connection will be made using a straightforward “Connect” button.

So let’s take the following actions to accomplish this.

Step 1) Creating a new project in Visual Studio is the first step. After starting Visual Studio, you must select New->Project from the menu.

c_math

Step 2) The next step is to select a Windows Forms application as the project type. Additionally, we must state the project’s name and location here.

sql_database_connection
  • There are numerous options for creating various types of projects in the project dialog box in Visual Studio. Click the Windows option on the left-hand side.
  • We will be able to see an option for when we click the Windows choices in the previous stage.
  • Then we give the application a name, in this example “DemoApplication.” Additionally, we must give a place where our application can be kept.
  • We then press the “OK” button to instruct Visual Studio to start building our project.
connect_c_sql_server
  • Step 4) Double-click the form now to add an event handler to the button and click the event’s code. Add the following code to the event handler.
sql_connection_string 

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 Explication

  1. Making variables is the first step in setting up the connection string and the connection to the SQL Server database.
  2. The connection string must be created next. In order for C# to understand the connection string, it must be properly defined. The following components make up the connection string:
  • The database’s server is identified by its name as the data source. In our situation, it is found on a computer known as WIN- 50GP30FGO75.
  • The database’s name is specified in the Initial Catalog.
  • The UserID and Password are required to connect to the database.
  1.  3. The connecting string is then assigned to the cnn variable. The connection to the database is made using the SqlConnection-type variable cnn.

    4. Then, we open a connection to the database by using the Open method of the cnn variable. The user will then only see a notification indicating that the connection has been made.

    5. We then shut down the connection to the database once the process has been properly completed. If no more action has to be taken on the database, it is always a good idea to disconnect from it.

connect_database_c