The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Database Connectivity

Next ►
►

In this section, we will explore how to connect a C# application to a database. This is a crucial skill for any developer, as most applications need to interact with a database to store and retrieve data. We will cover the following topics:

  1. Introduction to ADO.NET
  2. Connecting to a Database
  3. Executing SQL Commands
  4. Reading Data with DataReader
  5. Using DataAdapter and DataSet
  6. Practical Exercises

  1. Introduction to ADO.NET

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. It provides a rich set of components for creating distributed, data-sharing applications. ADO.NET is designed to be efficient and scalable, making it suitable for both small and large applications.

Key Components of ADO.NET

  • Connection: Establishes a connection to a specific data source.
  • Command: Executes a command against a data source.
  • DataReader: Reads data from a data source in a forward-only, read-only manner.
  • DataAdapter: Fills a DataSet and resolves updates with the data source.
  • DataSet: An in-memory representation of data that can work with multiple tables.

  1. Connecting to a Database

To connect to a database, you need a connection string, which contains information about the data source, such as the server name, database name, and authentication details.

Example: Connecting to a SQL Server Database

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occurred: " + ex.Message);
            }
        }
    }
}

Explanation

  • SqlConnection: Represents a connection to a SQL Server database.
  • connectionString: Contains the details required to connect to the database.
  • connection.Open(): Opens the connection to the database.
  • using: Ensures that the connection is closed and disposed of properly.

  1. Executing SQL Commands

Once connected, you can execute SQL commands to interact with the database.

Example: Executing a SQL Command

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string sql = "INSERT INTO Students (Name, Age) VALUES ('John Doe', 25)";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("Rows affected: " + rowsAffected);
            }
        }
    }
}

Explanation

  • SqlCommand: Represents a SQL statement to execute against a SQL Server database.
  • ExecuteNonQuery(): Executes a command that does not return any data (e.g., INSERT, UPDATE, DELETE).

  1. Reading Data with DataReader

To read data from a database, you can use the SqlDataReader class.

Example: Reading Data

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string sql = "SELECT Name, Age FROM Students";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string name = reader["Name"].ToString();
                        int age = Convert.ToInt32(reader["Age"]);
                        Console.WriteLine($"Name: {name}, Age: {age}");
                    }
                }
            }
        }
    }
}

Explanation

  • SqlDataReader: Provides a way to read a forward-only stream of rows from a SQL Server database.
  • ExecuteReader(): Executes the command and returns a SqlDataReader object.
  • reader.Read(): Advances the SqlDataReader to the next record.

  1. Using DataAdapter and DataSet

The SqlDataAdapter and DataSet classes provide a way to work with data in a disconnected manner.

Example: Using DataAdapter and DataSet

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string sql = "SELECT Name, Age FROM Students";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "Students");
            
            foreach (DataRow row in dataSet.Tables["Students"].Rows)
            {
                string name = row["Name"].ToString();
                int age = Convert.ToInt32(row["Age"]);
                Console.WriteLine($"Name: {name}, Age: {age}");
            }
        }
    }
}

Explanation

  • SqlDataAdapter: Represents a set of data commands and a database connection that are used to fill a DataSet and update a SQL Server database.
  • DataSet: An in-memory representation of data that can contain multiple tables.
  • adapter.Fill(dataSet, "Students"): Fills the DataSet with data from the database.

  1. Practical Exercises

Exercise 1: Connecting to a Database

Task: Write a C# program that connects to a SQL Server database and prints a success message.

Solution:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occurred: " + ex.Message);
            }
        }
    }
}

Exercise 2: Executing a SQL Command

Task: Write a C# program that inserts a new record into a table called Students.

Solution:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string sql = "INSERT INTO Students (Name, Age) VALUES ('Jane Doe', 22)";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("Rows affected: " + rowsAffected);
            }
        }
    }
}

Exercise 3: Reading Data

Task: Write a C# program that reads and prints all records from the Students table.

Solution:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string sql = "SELECT Name, Age FROM Students";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string name = reader["Name"].ToString();
                        int age = Convert.ToInt32(reader["Age"]);
                        Console.WriteLine($"Name: {name}, Age: {age}");
                    }
                }
            }
        }
    }
}

Conclusion

In this section, we covered the basics of database connectivity in C#. We learned how to:

  • Connect to a database using SqlConnection.
  • Execute SQL commands using SqlCommand.
  • Read data using SqlDataReader.
  • Work with data in a disconnected manner using SqlDataAdapter and DataSet.

These skills are fundamental for any C# developer working with databases. In the next section, we will delve into more advanced topics such as Entity Framework, which provides a higher-level abstraction for database operations.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved