techmore.in

C# - Parameterization

In C#, parameterization is a technique used to safely pass parameters to SQL queries, preventing SQL injection and ensuring that queries are executed efficiently. It involves using parameters in SQL commands rather than directly embedding values in the query string. This practice also enhances readability and maintainability of your code.

Why Use Parameterization?

  1. Prevents SQL Injection: By separating query logic from data, parameterized queries mitigate the risk of SQL injection attacks.
  2. Improves Query Performance: SQL Server can optimize parameterized queries better because the query plan can be reused.
  3. Enhances Code Readability: Parameters make it clear what data is being used and makes queries easier to manage and debug.

Parameterization is a key practice in SQL operations to avoid SQL injection and ensure safe and efficient query execution. Here’s how to use parameterization in C# with SQL Server, MySQL, and Oracle databases:

1. SQL Server

Insert Data

csharp
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string query = "INSERT INTO Employees (Name, Age) VALUES (@Name, @Age)";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");
                command.Parameters.AddWithValue("@Age", 30);

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read Data

csharp
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string query = "SELECT * FROM Employees WHERE Name = @Name";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");

                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}");
                    }
                }
            }
        }
    }
}

Update Data

csharp
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string query = "UPDATE Employees SET Age = @Age WHERE Name = @Name";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Age", 31);
                command.Parameters.AddWithValue("@Name", "John Doe");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete Data

csharp
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string query = "DELETE FROM Employees WHERE Name = @Name";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

2. MySQL

Insert Data

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string query = "INSERT INTO Employees (Name, Age) VALUES (@Name, @Age)";
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");
                command.Parameters.AddWithValue("@Age", 30);

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read Data

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string query = "SELECT * FROM Employees WHERE Name = @Name";
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string name = reader["Name"].ToString();
                        int age = Convert.ToInt32(reader["Age"]);
                        Console.WriteLine($"Name: {name}, Age: {age}");
                    }
                }
            }
        }
    }
}

Update Data

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string query = "UPDATE Employees SET Age = @Age WHERE Name = @Name";
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Age", 31);
                command.Parameters.AddWithValue("@Name", "John Doe");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete Data

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string query = "DELETE FROM Employees WHERE Name = @Name";
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

3. Oracle

Insert Data

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string query = "INSERT INTO Employees (Name, Age) VALUES (:Name, :Age)";
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                command.Parameters.Add(new OracleParameter("Name", "John Doe"));
                command.Parameters.Add(new OracleParameter("Age", 30));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read Data

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string query = "SELECT * FROM Employees WHERE Name = :Name";
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                command.Parameters.Add(new OracleParameter("Name", "John Doe"));

                using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string name = reader["Name"].ToString();
                        int age = Convert.ToInt32(reader["Age"]);
                        Console.WriteLine($"Name: {name}, Age: {age}");
                    }
                }
            }
        }
    }
}

Update Data

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string query = "UPDATE Employees SET Age = :Age WHERE Name = :Name";
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                command.Parameters.Add(new OracleParameter("Age", 31));
                command.Parameters.Add(new OracleParameter("Name", "John Doe"));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete Data

csharp
using Oracle.ManagedDataAccess.Client;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=your_username;Password=your_password;Data Source=your_data_source;";

        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();

            string query = "DELETE FROM Employees WHERE Name = :Name";
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                command.Parameters.Add(new OracleParameter("Name", "John Doe"));

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

Key Points

  • SQL Server: Use SqlParameter with AddWithValue or Add method.
  • MySQL: Use MySqlParameter with AddWithValue or Add method.
  • Oracle: Use OracleParameter with named parameters.

Replace placeholder values in connection strings and queries with actual database details. Always ensure parameters are used correctly to maintain the security and integrity of your SQL operations.