Introduction

Basic

Intermediate

Database

C# Reading From Database

In this section, we still using the same database as the previous section which is created by the following queries:

#Create database
CREATE DATABASE IF NOT EXISTS campus;

#Activate database
USE campus;

#Create table
CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fullname VARCHAR(100),
    gender VARCHAR(10),
    faculty VARCHAR(50),
    enrolled DATETIME,
    updated DATETIME )
engine=innodb;

To read data from a database, there's at least two ways.

Using DataReader

To use a DataReader, we will need at least a Connection, a Command and the DataReader itself. Note that DataReader has no constructor. We should instead initialize it with the Command's ExecuteReader method. For example:

var conn = new MySqlConnection("Host=localhost;Uid=root;Pwd=123;Database=campus");
var cmd = new MySqlCommand("SELECT id, fullname, gender, faculty, enrolled, updated FROM students", conn);

//The connection must be open to ExecuteReader
conn.Open();
var r = cmd.ExecuteReader();
//For clarity, you can write:
//MySqlDataReader r = cmd.ExecuteReader();

//Check if the DataReader has rows
if (r.HasRows) {
    //Try to read one row from the database
    //Returns true when there is a row, false when there is no row
    r.Read();

    Console.WriteLine("Students data:");
    Console.WriteLine(r[0]); //The first returned column:id (0 index)
    Console.WriteLine(r[1]); //The second returned column:fullname (1 index)
    Console.WriteLine(r[2]); //The third returned column:gender (2 index)
    Console.WriteLine(r["faculty"]); //can also get by column name
    Console.WriteLine(((DateTime)r["enrolled"]).ToString("dd MMMM yyyy"));
    r.Close(); //Should better close the reader after reading.
}
//Close the connection only after the reader has finished reading.
conn.Close();

The r.HasRows inside the if condition can also be replaced with r.Read() if we want to check the data existence and get one row at the same time:

if (r.Read()) {
    Console.WriteLine("Students data:");
    Console.WriteLine(r[0]); //The first returned column:id (0 index)
    Console.WriteLine(r[1]); //The second returned column:fullname (1 index)
    Console.WriteLine(r[2]); //The third returned column:gender (2 index)
    Console.WriteLine(r["faculty"]); //can also get by column name
    Console.WriteLine(((DateTime)r["enrolled"]).ToString("dd MMMM yyyy"));
    r.Close(); //Should better close the reader after reading.
}

And also, if we plan to read all rows available, we just need to replace the if with while. Don't forget to move the r.Close() method outside the while loop:

while (r.Read()) {
    Console.WriteLine("Students data:");
    Console.WriteLine(r[0]); //The first returned column:id (0 index)
    Console.WriteLine(r[1]); //The second returned column:fullname (1 index)
    Console.WriteLine(r[2]); //The third returned column:gender (2 index)
    Console.WriteLine(r["faculty"]); //can also get by column name
    Console.WriteLine(((DateTime)r["enrolled"]).ToString("dd MMMM yyyy"));
}
r.Close(); //Should better close the reader after reading.

We can also populate all rows available at the DataReader into a DataTable:

var conn = new MySqlConnection("Host=localhost;Uid=root;Pwd=123;Database=campus");
var cmd = new MySqlCommand("SELECT id, fullname, gender, faculty, enrolled, updated FROM students", conn);
//A new DataTable named StudentTable.
var dt = new DataTable("StudentTable");

//The connection must be open to ExecuteReader
conn.Open();
var r = cmd.ExecuteReader();
//Load all rows that can be returned from r into the DataTable
dt.Load(r);
conn.Close();

//We can use the DataTable after the Connection already closed
//As much as we want
foreach (DataRow oRow in dt.Rows) {
    Console.WriteLine("Students data:");
    Console.WriteLine(oRow[0]); //The first column:id (0 index)
    Console.WriteLine(oRow["fullname"]); //The second column:fullname (1 index)
    Console.WriteLine(oRow.Field<string>(2)); //get the third column: gender as a string
    Console.WriteLine(oRow.Field<string>("faculty")); //can also get by column name
    Console.WriteLine(oRow.Field<DateTime>("enrolled").ToString("dd MMMM yyyy"));
}

Using DataAdapter

Using a data adapter actually does not really different from the last DataReader example above. One little good thing about DataAdapter is that it can automatically open and close the Connection for us:

var conn = new MySqlConnection("Host=localhost;Uid=root;Pwd=123;Database=campus");
var cmd = new MySqlCommand("SELECT id, fullname, gender, faculty, enrolled, updated FROM students", conn);
var dt = new DataTable("StudentTable");
//Create a DataAdapter, and directly set its SelectCommand to our cmd object above
var adap = new MySqlDataAdapter(cmd);
//Get all rows returned by the command into the DataTable
adap.Fill(dt);

foreach (DataRow oRow in dt.Rows) {
    Console.WriteLine("Students data:");
    Console.WriteLine(oRow[0]); //The first column:id (0 index)
    Console.WriteLine(oRow["fullname"]); //The second column:fullname (1 index)
    Console.WriteLine(oRow.Field<string>(2)); //get the third column: gender as a string
    Console.WriteLine(oRow.Field<string>("faculty")); //can also get by column name
    Console.WriteLine(oRow.Field<DateTime>("enrolled").ToString("dd MMMM yyyy"));
}

Note that other than Retrieving data with the Fill method, DataAdapter also can automatically update changed rows, insert new rows, and delete removed rows. Or in other words, all changes on the DataTable will also be sent to the database server. All of those are done with only one Update method. But that method will not be covered here.


Fandi Susanto

Create Your Badge