notes-shivam

6. ADO.NET

31 min read

  1. ADO.NET (ActiveX Data Objects) is Microsoft’s data access technology that provides a set of classes and interfaces for connecting, retrieving, manipulating, and updating data from various database systems through the .NET Framework.

Features

  • Truly disconnected data access: Connections close immediately after data retrieval, enabling superior scalability
  • Connection pooling: Automatically reuses existing connections to reduce expensive connection creation overhead
  • Multiple data source support: Works uniformly with SQL Server, Oracle, MySQL, XML files, and other sources
  • Improved performance: Only maintains connections when actively retrieving or updating data

A. Connected and Disconnected Classes

ADO.NET architecture divides into two distinct models for data access: Connected and Disconnected architectures.

1. Connected Architecture

  1. maintains an active, open database connection throughout all data operations.
  2. The connection remains established as long as the application needs to interact with the database.
  3. ideal for real-time data retrieval, scenarios requiring minimal memory usage
  4. for applications needing immediate database consistency.

Key Characteristics:

  • Sequential reader
  • Read-only access
  • Low memory footprint
  • High performance
  • Connection-dependent

2. Disconnected Architecture

  1. Disconnected architecture retrieves data from the database and stores it in-memory, operating independently of the data source.
  2. The database connection is opened only briefly to fetch or update data.
  3. best for multi-tier applications, batch processing scenarios, applications working offline

Key Characteristics:

  • Disconnected operation: Connection opens briefly, closes immediately
  • In-memory storage
  • Flexible data manipulation
  • XML integration

Core Components:

  • DataSet: In-memory representation containing multiple DataTable objects

  • DataTable: Represents a single table in memory with rows and columns

  • DataAdapter: Bridge between database and DataSet, managing data retrieval and updates

  • DataColumn, DataRow: Define table structure and hold actual data

  • Constraints, DataRelation: Enforce data integrity and relationships

B. Establishing Database Connection

Step 1: Install MySQL Connector/NET

dotnet add package MySqlConnector

Step 2: Connection String

Server=localhost;User ID=root;Password=yourpassword;Database=databasename;

components :

  • Server: Hostname or IP address (default: localhost)
  • Port: MySQL port (default: 3306)
  • User ID: MySQL username
  • Password: MySQL user password
  • Database: Initial database to use
  • Connect Timeout: Seconds to wait before timing out
  • Pooling: Enable connection pooling (recommended: true)
  • Min Pool Size: Minimum connections in pool
  • Max Pool Size: Maximum connections in pool

Step 3: Implementation

class DbConnection
{
    private string connectionString = "Server=localhost;User ID=root;Password=root;Database=studentdb;";
    public void GetAllStudents()
    {
        try
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            string query = "SELECT Id, Name, Email, Mobile FROM Student";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            connection.Open();
            MySqlReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"name: {reader["Name"]}");
                Console.WriteLine($"email: {reader["Email"]}");
            }
            reader.Close();
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
 
    public void InsertStudent(string name, string email, int mobile)
    {
        try
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            string query = "INSERT INTO STUDENT (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile)";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Email", email);
            cmd.Parameters.AddWithValue("@Mobile", mobile);
            
            connection.Open();
            
            cmd.ExecuteNonQuery();
            
            connection.Close();
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
    static void Main(string[] args)
    {
        DbConnection dbConnection = new DbConnection();
        dbConnection.InsertStudent("Deven", "d123@gmail.com", 9191991919);
        dbConnection.GetAllStudents();
    }
}

Executing Queries

1. INSERT Operation

adds new records to a database table.

public void InsertStudent(string name, string email, string mobile)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                // SQL query with parameters
                string query = "INSERT INTO Student (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile)";
                
                // Create command
                MySqlCommand cmd = new MySqlCommand(query, connection);
 
                // Add parameters (prevents SQL injection)
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters.AddWithValue("@Mobile", mobile);
                // Open connection
                connection.Open();
                // Execute - returns number of rows affected
                int rowsAffected = cmd.ExecuteNonQuery();
                Console.WriteLine($"✓ Successfully inserted {rowsAffected} record(s)");
 
            }
 
        }
 
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Insert Error: {ex.Message}");
        }
    }

2. Insert Multiple records

public void InsertMultipleRecords()
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
 
                string query = "INSERT INTO Student (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile)";
 
                MySqlCommand cmd = new MySqlCommand(query, connection);
 
                // Data to insert
                string[][] studentData = new string[][]
                {
                    new string[] { "Alice Johnson", "[email protected]", "9876543210" },
                    new string[] { "Bob Smith", "[email protected]", "9876543211" },
                    new string[] { "Carol White", "[email protected]", "9876543212" }
                };
                // Insert each record
                foreach (var student in studentData)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@Name", student[0]);
                    cmd.Parameters.AddWithValue("@Email", student[1]);
                    cmd.Parameters.AddWithValue("@Mobile", student[2]);
                    int result = cmd.ExecuteNonQuery();
                    Console.WriteLine($"✓ Inserted: {student[^0]}");
                }
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }

3. Update record

public void UpdateStudent(int studentId, string newEmail, string newMobile)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string query = "UPDATE Student SET Email = @Email, Mobile = @Mobile WHERE Id = @Id";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Email", newEmail);
                cmd.Parameters.AddWithValue("@Mobile", newMobile);
                cmd.Parameters.AddWithValue("@Id", studentId);
                
                connection.Open();
 
                int rowsAffected = cmd.ExecuteNonQuery();
                if (rowsAffected > 0)
                    Console.WriteLine($"✓ Successfully updated {rowsAffected} record(s)");
                else
                    Console.WriteLine("✗ No records found to update");
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Update Error: {ex.Message}");
        }
    }

4. Delete a record

public void DeleteStudent(int studentId)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string query = "DELETE FROM Student WHERE Id = @Id";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Id", studentId);
                connection.Open();
                int rowsDeleted = cmd.ExecuteNonQuery();
                if (rowsDeleted > 0)
                    Console.WriteLine($"✓ Successfully deleted {rowsDeleted} record(s)");
                else
                    Console.WriteLine("✗ No records found to delete");
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Delete Error: {ex.Message}");
        }
    }

Complete CRUD operations program

 
using MySql.Data.MySqlClient;
using System;
 
class DbOperations
{
 
    private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
 
    public void InsertStudent(string name, string email, string mobile)
    {
	    // INSERT CODE
    }
    public void InsertMultipleRecords()
    {
	    // INSERT MULTIPLE RECORDS CODE
    }
    public void UpdateStudent(int studentId, string newEmail, string newMobile)
    {
	    // UPDATE CODE
    }
    public void DeleteStudent(int studentId)
    {
	    // DELETE CODE
    }
    static void Main()
    {
        CRUDOperations crud = new CRUDOperations();
        Console.WriteLine("=== INSERT Operations ===");
        crud.InsertStudent("John Doe", "[email protected]", "1234567890");
 
        Console.WriteLine("\n=== INSERT Multiple Records ===");
        crud.InsertMultipleRecords();
        Console.WriteLine("\n=== UPDATE Operation ===");
 
        crud.UpdateStudent(1, "[email protected]", "9999999999");
        Console.WriteLine("\n=== DELETE Operation ===");
        crud.DeleteStudent(3);
        Console.ReadKey();
 
    }
 
}

C. Data Streams : DataReader and DataAdapter

1. DataReader - Connected Data Access

  1. MySqlDataReader is a lightweight, forward-only stream of data from the database.

  2. It reads data row-by-row without storing it in memory.

  3. Key Methods:

    • Read() - Advances to next row, returns true if row exists
    • GetString(index), GetInt32(index) - Get typed values by column index
    • GetValue(index) - Get value in native format
    • HasRows - Check if result set contains rows
    • NextResult() - Move to next result set
    • Close() - Close the reader

  4. Example :

using MySql.Data.MySqlClient;
using System;
 
class DataReaderDemo
{
    private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
    public void ReadAllStudents()
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string query = "SELECT Id, Name, Email, Mobile FROM Student";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                connection.Open();
                // ExecuteReader returns MySqlDataReader
                MySqlDataReader reader = cmd.ExecuteReader();
                Console.WriteLine("Student Records:");
 
                // Read data row by row
                while (reader.Read())
                {
                    // Method 1: Access by column name
                    int id = Convert.ToInt32(reader["Id"]);
                    string name = reader["Name"].ToString();
                    string email = reader["Email"].ToString();
                    string mobile = reader["Mobile"].ToString();
                    Console.WriteLine($"ID: {id}, Name: {name}, Email: {email}, Mobile: {mobile}");
 
                    // Method 2: Access by column index
 
                    // Console.WriteLine($"{reader[^0]}, {reader[^1]}, {reader}, {reader[^3]}");
 
                }
                reader.Close();
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
    public void SearchStudent(int studentId)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string query = "SELECT * FROM Student WHERE Id = @Id";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Id", studentId);
                
                connection.Open();
                
                MySqlDataReader reader = cmd.ExecuteReader();
 
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("Student Found:");
                        Console.WriteLine($"  ID: {reader["Id"]}");
                        Console.WriteLine($"  Name: {reader["Name"]}");
                        Console.WriteLine($"  Email: {reader["Email"]}");
                        Console.WriteLine($"  Mobile: {reader["Mobile"]}");
                    }
                }
                else
                {
                    Console.WriteLine("✗ Student not found");
                }
                reader.Close();
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
 
        }
    }
    static void Main()
    {
        DataReaderDemo demo = new DataReaderDemo();
        Console.WriteLine("Read all students: ");
        demo.ReadAllStudents();
 
        Console.WriteLine("Search Specific Student");
        demo.SearchStudent(1);
 
    }
 
}

2. DataAdapter - Disconnected Data Access

Definition: MySqlDataAdapter is a bridge class connecting the database to in-memory DataSet or DataTable objects. It uses Connection and Command objects internally.[^23][^24]

Key Commands:[^24]

  • SelectCommand - Retrieves data from database

  • InsertCommand - Inserts new rows

  • UpdateCommand - Updates existing rows

  • DeleteCommand - Deletes rows

  • Fill() - Fills DataSet/DataTable with data

  • Update() - Synchronizes changes back to database

DataAdapter Implementation:

 
using MySql.Data.MySqlClient;
 
using System;
 
using System.Data;
 
  
 
class DataAdapterDemo
 
{
 
    private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
 
  
 
    public void DisplayStudentsWithDataAdapter()
 
    {
 
        try
 
        {
 
            // Create DataAdapter
 
            string query = "SELECT Id, Name, Email, Mobile FROM Student";
 
            MySqlDataAdapter adapter = new MySqlDataAdapter(query, connectionString);
 
            // Create DataTable
 
            DataTable studentTable = new DataTable("Students");
 
            // Fill DataTable (connection opens and closes automatically)
 
            adapter.Fill(studentTable);
 
            Console.WriteLine($"Retrieved {studentTable.Rows.Count} records\n");
 
            Console.WriteLine("Student Records:");
 
            Console.WriteLine("================================================");
 
            // Iterate through rows
 
            foreach (DataRow row in studentTable.Rows)
 
            {
 
                Console.WriteLine($"ID: {row["Id"]}, Name: {row["Name"]}, Email: {row["Email"]}, Mobile: {row["Mobile"]}");
 
            }
 
        }
 
        catch (MySqlException ex)
 
        {
 
            Console.WriteLine($"✗ Error: {ex.Message}");
 
        }
 
    }
 
  
 
    public void ModifyAndUpdateData()
 
    {
 
        try
 
        {
 
            // Fill DataTable
 
            string query = "SELECT Id, Name, Email, Mobile FROM Student";
 
            MySqlDataAdapter adapter = new MySqlDataAdapter(query, connectionString);
 
            DataTable studentTable = new DataTable();
 
            adapter.Fill(studentTable);
 
            Console.WriteLine($"Original records: {studentTable.Rows.Count}");
 
            // Modify existing row
 
            if (studentTable.Rows.Count > 0)
 
            {
 
                studentTable.Rows[^0]["Email"] = "[email protected]";
 
                studentTable.Rows[^0]["Mobile"] = "9999999999";
 
                Console.WriteLine("✓ Modified first record");
 
            }
 
            // Add new row
 
            DataRow newRow = studentTable.NewRow();
 
            newRow["Name"] = "Emma Davis";
 
            newRow["Email"] = "[email protected]";
 
            newRow["Mobile"] = "8888888888";
 
            studentTable.Rows.Add(newRow);
 
            Console.WriteLine("✓ Added new record");
 
            // Configure commands for update
 
            MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
 
            // Update database
 
            int rowsAffected = adapter.Update(studentTable);
 
            Console.WriteLine($"✓ {rowsAffected} row(s) updated in database");
 
        }
 
        catch (Exception ex)
 
        {
 
            Console.WriteLine($"✗ Error: {ex.Message}");
 
        }
 
    }
 
  
 
    static void Main()
 
    {
 
        DataAdapterDemo demo = new DataAdapterDemo();
 
        Console.WriteLine("=== Display with DataAdapter ===");
 
        demo.DisplayStudentsWithDataAdapter();
 
        Console.WriteLine("\n=== Modify and Update Data ===");
 
        demo.ModifyAndUpdateData();
 
        Console.ReadKey();
 
    }
 
}

D. DataSet?

  1. DataSet is an in-memory, disconnected cache of multiple related tables retrieved from a data source.
  2. It provides complete independence from the database after initial data retrieval.

Components:

  • DataTableCollection - Contains one or more DataTable objects
  • DataRelationCollection - Defines parent-child relationships between tables
  • ConstraintCollection - Enforces data integrity rules

Implementation:

i) load dataset from db

private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
 
public void LoadDataSetFromDatabase()
    {
        try
        {
	        // create an empty dataset
            DataSet schoolData = new DataSet("SchoolData");
            
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                // Load Students table
                MySqlDataAdapter studentAdapter = new MySqlDataAdapter(
                    "SELECT * FROM Student", connection);
                studentAdapter.Fill(schoolData, "Students");
                
                // Load Courses table (if exists)
                MySqlDataAdapter courseAdapter = new MySqlDataAdapter(
                    "SELECT * FROM Course", connection);
                courseAdapter.Fill(schoolData, "Courses");
            }
            
            Console.WriteLine($"DataSet loaded with {schoolData.Tables.Count} table(s)");
 
            foreach (DataTable table in schoolData.Tables)
            {
                Console.WriteLine($"  Table: {table.TableName} - {table.Rows.Count} rows, {table.Columns.Count} columns");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }

ii) Creating a dataset manually

 public void CreateDataSetManually()
    {
        try
        {
            // Create DataSet
            DataSet schoolData = new DataSet("SchoolManagement");
            // Create Student table
            DataTable studentTable = new DataTable("Students");
            // Add columns with auto-increment ID
            DataColumn studentId = new DataColumn("Id", typeof(int));
            studentId.AutoIncrement = true;
            studentId.AutoIncrementSeed = 1;
            
            studentTable.Columns.Add(studentId);
            studentTable.Columns.Add("Name", typeof(string));
            studentTable.Columns.Add("Email", typeof(string));
            studentTable.Columns.Add("Mobile", typeof(string));
 
            // Set primary key
            studentTable.PrimaryKey = new DataColumn[] { studentId };
 
            // Add rows
            DataRow row1 = studentTable.NewRow();
            
            row1["Name"] = "Alice Johnson";
            row1["Email"] = "[email protected]";
            row1["Mobile"] = "9876543210";
            
            studentTable.Rows.Add(row1);
            
            // Add table to DataSet
 
            schoolData.Tables.Add(studentTable);
 
            // Display data
 
            foreach (DataRow row in 
                     schoolData.Tables["Students"].Rows)
            {
                Console.WriteLine($"  ID: {row["Id"]}, Name: 
	            {row["Name"]}, Email: {row["Email"]}");
                
            }
        }
        
        catch (Exception ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }

Advantages of DataSet

  1. Scalability and Performance
    1. DataSet eliminates continuous connection requirements, allowing more clients to request information without database connection bottlenecks.
    2. Connection pooling further improves performance by reusing existing connections.
  2. Data Source Independence
    1. DataSet works independently of the data source and can combine data from multiple heterogeneous sources (SQL Server, MySQL, Oracle, XML files) using a single programming model.
  3. Disconnected Data Manipulation
    1. All data operations occur in-memory without requiring an active connection.
    2. Multiple changes can be accumulated and synchronized in a single batch update.
  4. XML Integration and Interoperability
    1. DataSet provides native XML support, enabling XML serialization/deserialization
    2. This enables seamless data transmission across firewalls in distributed systems
  5. Rich Relational Model

    1. DataSet supports a complete relational model with tables, columns, rows, constraints, and relationships,

E. Prepared Statements (Parameterized Queries)

Prepared statements (also called parameterized queries) are pre-compiled SQL queries where user input is treated as data values, not SQL code.

Security & Performance Benefits:

  • SQL Injection Prevention: Input cannot alter SQL structure
  • Query Plan Reuse: Execution plans cached for repeated queries
  • Type Safety: Explicit parameter types prevent conversion errors
  • Performance: Reduces compilation overhead

Prepared Statement Implementation:

 
using MySql.Data.MySqlClient;
using System;
class PreparedStatementsDemo
{
    private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
 
    public void SearchWithPreparedStatement(int age, string department)
    {
        try
        {
 
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                // Parameterized query (prevents SQL injection)
                string query = "SELECT * FROM Student WHERE Age = @Age AND Department = @Department";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                // Add parameters
                cmd.Parameters.AddWithValue("@Age", age);
                cmd.Parameters.AddWithValue("@Department", department);
                // Prepare command (optional but recommended for repeated execution)
                cmd.Prepare();
                connection.Open();
                MySqlDataReader reader = cmd.ExecuteReader();
 
                Console.WriteLine($"Students: Age={age}, Department= 
                {department}\n");
 
                while (reader.Read())
                {
                    Console.WriteLine($"  {reader["Name"]}, Age: 
                    {reader["Age"]}, Dept: {reader["Department"]}");
                }
                reader.Close();
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
 
    public void InsertWithPreparedStatement()
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                string insertQuery = "INSERT INTO Student (Name, Email, Age, Department) VALUES (@Name, @Email, @Age, @Department)";
                MySqlCommand cmd = new MySqlCommand(insertQuery, connection);
                // Define parameters
                cmd.Parameters.Add("@Name", MySqlDbType.VarChar, 100);
                cmd.Parameters.Add("@Email", MySqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Age", MySqlDbType.Int32);
                cmd.Parameters.Add("@Department", MySqlDbType.VarChar, 50);
                // Prepare
                cmd.Prepare();
                connection.Open();
                // Sample data
                string[][] students = new string[][]
                {
                    new string[] { "David Brown", "[email protected]", "20", "CS" },
                    new string[] { "Emma Wilson", "[email protected]", "19", "IT" },
                    new string[] { "Frank Miller", "[email protected]", "21", "EC" }
 
                };
 
                // Execute multiple times with different values
                foreach (var student in students)
                {
                    cmd.Parameters["@Name"].Value = student[^0];
                    cmd.Parameters["@Email"].Value = student[^1];
                    cmd.Parameters["@Age"].Value = int.Parse(student);
                    cmd.Parameters["@Department"].Value = student[^3];
                    cmd.ExecuteNonQuery();
                    Console.WriteLine($"✓ Inserted: {student[^0]}");
                }
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
}

F. Stored Procedures

Stored procedures are pre-compiled database objects containing SQL code that can accept parameters and be invoked from applications.

Features:

  • Performance: Pre-compiled, execution plans cached
  • Security: Database logic encapsulation
  • Reusability: Multiple applications can call same procedure
  • Maintainability: Centralized business logic

Creating MySQL Stored Procedure:

CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT Id, Name, Email, Mobile FROM Student;
END;
CREATE PROCEDURE GetStudentById(IN p_Id INT)
BEGIN
    SELECT Id, Name, Email, Mobile FROM Student WHERE Id = p_Id;
END;
CREATE PROCEDURE CreateStudent(
    IN p_Name VARCHAR(100),
    IN p_Email VARCHAR(50),
    IN p_Mobile VARCHAR(50),
    OUT p_Id INT
)
BEGIN
    INSERT INTO Student (Name, Email, Mobile) VALUES (p_Name, p_Email, p_Mobile);
    SELECT LAST_INSERT_ID() INTO p_Id;
END;

Calling Stored Procedures:

using MySql.Data.MySqlClient;
using System;
using System.Data;
 
class StoredProcedureDemo
{
    private string connectionString = "Server=localhost;User ID=root;Password=root123;Database=studentdb;";
    
    public void CallSimpleStoredProcedure()
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand("GetAllStudents", 
                connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
 
                connection.Open();
 
                MySqlDataReader reader = cmd.ExecuteReader();
 
                Console.WriteLine("All Students:\n");
 
                while (reader.Read())
                {
                    Console.WriteLine($"  ID: {reader["Id"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Mobile: {reader["Mobile"]}");
                }
                reader.Close();
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
 
 ```
 
 ```cs
    public void CreateStudent(string name, string email, string mobile)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand("CreateStudent", 
                connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                
                // Input parameters
                cmd.Parameters.AddWithValue("@p_Name", name);
                cmd.Parameters.AddWithValue("@p_Email", email);
                cmd.Parameters.AddWithValue("@p_Mobile", mobile);
                // Output parameter
                MySqlParameter outputParam = new MySqlParameter
                {
                    ParameterName = "@p_Id",
                    MySqlDbType = MySqlDbType.Int32,
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(outputParam);
                connection.Open();
                cmd.ExecuteNonQuery();
                
                // Get output parameter value
 
                int newId = Convert.ToInt32(outputParam.Value);
                Console.WriteLine($"✓ New Student Created with ID: 
                {newId}");
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }
   public void CallStoredProcedureWithInputParam(int studentId)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand("GetStudentById", 
              connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                
                // Add input parameter
                MySqlParameter inputParam = new MySqlParameter
                {
                    ParameterName = "@p_Id",
                    MySqlDbType = MySqlDbType.Int32,
                    Value = studentId,
                    Direction = ParameterDirection.Input
                };
 
                cmd.Parameters.Add(inputParam);
                connection.Open();
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    Console.WriteLine("Student Found:");
                    Console.WriteLine($"  ID: {reader["Id"]}");
                    Console.WriteLine($"  Name: {reader["Name"]}");
                    Console.WriteLine($"  Email: {reader["Email"]}");
                    Console.WriteLine($"  Mobile: {reader["Mobile"]}");
                }
                else
                {
                    Console.WriteLine("✗ Student not found");
                }
                reader.Close();
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"✗ Error: {ex.Message}");
        }
    }

Graph View

Backlinks