How to Select Query for Database in C#

1. What is a Database
2. Database Connection in C#

This article guides through setting up a database connection with the C# application and fetching data from different tables using the SELECT query from the database.

What is a Database

A database is not merely a collection of data organized in tabular form but much more. It represents data in an organized structure with proper naming conventions and relations with other tables.

The database management system also provides a language for interacting with the database (e.g., SQL), which is very close to the English language and thus very easily understandable by the user. It also provides us with data security and optimization schemes to increase the performance of data operations.

Database Connection in C#

Usually, the programmers use databases to store their applications’ data. The C# language provides database programming with Oracle database and Microsoft SQL server database.

C# can also work with many other databases, but those above two are the most common and widely used. Whatever your database, the logic behind every database is quite the same.

This article will use the SQL Server database to perform operations. The namespace that is required for database operations is System.data.SqlClient.

It contains all the classes that will be required during database connection and operations.

Create a Database in SQL Server

The first step is to create a database with which you need to interact within SQL Server. This database is created using the following query.

create database mydb;

use mydb;

CREATE TABLE Student(
    studentID varchar(20) NOT NULL PRIMARY KEY,
    stuentName varchar(60) NOT NULL
);

insert into Student values("BS01", 'David');
insert into Student values("BS02", 'John');

After you execute these queries, a database having table Student will be created and will have 2 rows in it.

Make a Connection With the Database in C#

For interacting with a database, you need some parameters. These parameters are combined to form a connection string.

1. Name of database - You need to provide the name of the database from which you need to read the data. It is recommended to use only one database at a time.
2. Database Credentials - There is always a username and password for interacting with a database, so you need to give that authorized username and password.
3. Some Optional Parameters - You can also provide some optional parameters. For example, you can set a timeout value that the connection should be closed if the database remains inactive for a certain period.

Let’s look at the example code where we will perform a connection with the database. Suppose the database username is usr and the password is usr123.

SqlConnection conn;
string connectString =
    @"Data Source=DESKTOP-FQ8R559;Initial Catalog=mydb;User ID=usr;Password=usr123";

conn = new SqlConnection(connectString);
conn.Open();
Console.WriteLine("Connection Established");

Read Data from the Database

We need the SqlCommand class and SqlDataReader for reading data from the database. The SqlCommand class is used for running the command on the database.

Running the Select command produces a Resultset. This Resultset can be read using the SqlDataReader class.

Let’s look at the code below.

SqlCommand cmd;
SqlDataReader dataReader;
string sql = "Select * from Student";
cmd = new SqlCommand(sql, conn);
dataReader = cmd.ExecuteReader();
Console.WriteLine("Data from the Database..");
while (dataReader.Read()) {
  Console.WriteLine(dataReader.GetValue(0) + " || " + dataReader.GetValue(1));
}
dataReader.close();
conn.close();

In this code snippet, first, we have created a SqlCommand object and created a query we need to run on the database. Then this query is sent to the cmd object and the connection object conn.

To execute the query, we use the function ExecuteReader(), which returns a Resultset containing the data acquired in the query. This ResultSet can be read using the SqlDataReader object, which reads the data in the loop row by row.

In the end, we need to close the reader and connection objects so they can be released for further use.

The output of the above code will be:

Connection Established.
Data from the Database..
BS01 || David
BS02 || John

Note that we have used ExecuteReader() function for executing the Select query. To execute more types of queries, we need different methods.

Some of the popular query results readers are: