Basic Database Operations Using C#

This tutorial will tell you how to use C# using System.data.SqlClient namespace to carry out fundamental database operations. Because all relational database systems normally support standard SQL, the core operations are INSERT, UPDATE, SELECT, and DELETE.

Prerequisites: Microsoft SQL Server Management Studio

To create a database and table, open Microsoft SQL Server Management Studio and input the code below.

create database Demodb;

use Demodb;

CREATE TABLE demo(

    articleID varchar(30) NOT NULL PRIMARY KEY,

    articleName varchar(30) NOT NULL,

);

insert into demo values(1, 'C#');

insert into demo values(2, 'C++');

Connecting C# with Database: A connection was the first thing you needed for database work.

NOTE: Here, we will run these programs using the command prompt. Use Microsoft SQL Server Management Studio to see the Output.

Example 1:

Program:

The CODE is as follows:

using System;

using System.Data.SqlClient;

namespace Database_Operation {

class DBConn {

            // Main Method

            static void Main()

            {

                        Connect();

                        Console.ReadKey();

            }

            static void Connect()

            {

                        string constr;

                        SqlConnection conn;

                        constr = @"Data Source=DESKTOP-GP8F496;Initial Catalog=Demodb;User ID=sa;Password=24518300";

                        conn = new SqlConnection(constr);

                        // to open the connection

                        conn.Open();

                        Console.WriteLine("Connection Open!");

                        // to close the connection

                        conn.Close();

            }

}

}

OUTPUT:

The Output for the following program will be:

Connection Open !

Example 2:

Program:

The CODE is as follows:

string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=MyTestDb;Integrated Security=True;";

using (SqlConnection connection = new SqlConnection(connectionString))

{

    connection.Open();

    // Insert one more record into the database

    string sql = "INSERT INTO [dbo].[Authors] VALUES (4, 'Smith')";

    SqlCommand cmd = new SqlCommand(sql, connection);

    SqlDataAdapter adap = new SqlDataAdapter();

    adap.InsertCommand = new SqlCommand(sql, connection);

    adap.InsertCommand.ExecuteNonQuery();

    //Read all records from the database

    sql = "SELECT * FROM Authors";

    cmd = new SqlCommand(sql, connection);

    SqlDataReader dreader = cmd.ExecuteReader();

    // for one by one reading row

    while (reader.Read())

    {

        Console.WriteLine(dreader.GetValue(0) + ", " + dreader.GetValue(1));

    }

}

OUTPUT:

The Output for the following program will be:

1, Mark

2, John

3, Stella

4, Smith

Working with data-driven applications requires knowledge of database operations. To interface with databases in C#, you can use various libraries and frameworks, including ADO.NET, Entity Framework, or Dapper. This tutorial concentrates on Microsoft's ADO.NET, a portable and adaptable data access technique.

Fundamentals of Database Connectivity:

C# and .Net are the most popular databases; Oracle, and Microsoft SQL Server, can work with most databases.

We'll analyze how the Microsoft SQL Server database functions in our examples. Microsoft offers a free database program called Microsoft SQL Server Express Edition that can be downloaded and used for educational purposes.

1. Connection:

• Database name or Data Source –The database name to which a connection must be made is the first crucial parameter.
• Credentials – The next essential element is the username and password required to connect to the database. It makes that the login information and password are current in order to connect to the database.

2. Updating data into the database: The database can be updated using C# on existing records.

3. Deleting data from a database: Records can be deleted using C#. In C#, select commands can define which rows should be deleted.

After looking at the reasoning behind each action, let's move on to the sections that follow to look at how to do database operations in C#.

How to connect C# to the Database:

Now let's examine the code that needs to be kept up to date in order to create a connection to a database. We'll create a database connection to Demodb for our example. The login information needed to access the database is listed below.

• Username – sa
• Password – demo123

We'll see a simple Windows forms application that uses databases. We'll have a straightforward "Connect" button to connect to the database.

So, let's follow the methods listed below to do this.

Step 1: Select the menu item New->Project after starting Visual Studio.

Step 2:  The name and location of the project must also be included here.

• The project dialogue box in Visual Studio offers a wide range of choices for creating different projects. Go to the left and choose Windows.
• Click to choose this alternative.In this example, We give the application the name "DemoApplication." A place for our application to be stored is also something we need to give.

To allow Visual Studio to create our project, we finally clicked the "OK" Button.

Step 3: Now include a toolbox button in the Windows form. Put Connect in the Button's text property. This is what it will resemble.

Step 4: To add an event handler to the button and click the event's code, double-click the form.

Code Explanation:

It must be defined appropriately for C# to understand the connection string. The following components make up the connection string:

• Data Source-The database server with this name is where the database is located.

You will receive the Output listed below when the project is run through Visual Studio with the code mentioned earlier configured. Click the Connect button when the form appears.

The Output shows that the database connection was made when you clicked the "connect" Button. Therefore, the message box was displayed.

Access data with the SqlDataReader:

The ADO.NET library, which offers classes for connecting to and communicating with databases, is used to carry out database operations in C#. This guide will examine the procedures for carrying out fundamental database operations in C#.

1. Importing the necessary namespaces: To get started, you must import the namespaces needed for C# database work. System namespaces are the most often used namespaces are System. Data and System.Data.SqlClient. These offer classes for establishing database connections, running queries, and getting data.

2. Establishing a connection to the database: The SqlConnection class is used to build and manage the connection.

Conclusion:

In Conclusion, using the ADO.NET framework, which offers classes for connecting to and communicating with databases, is vital to perform database operations using C#. You might also efficiently work with databases on your C# packages by following the commands provided in this tutorial.

The classes are used because System provides data and the System before using Data.SqlClient, that's critical for operating with databases; you must first provide the required namespaces.

The closing and disposal of the connection are both made by the using statement. Use the SqlCommand class' AddWithValue() function to define and assign parameter values.

You can carry out a variety of database operations in your C# programs by following these instructions. Remember to handle exceptions properly and dispose of resources to ensure code integrity and performance. Many applications depend on database operations because they let you effectively store, retrieve, and alter data. You have robust tools to work with databases and create reliable and scalable apps with the ADO.NET library and C#.