C数据库数据删除与新增操作指南

C# 删除并新增数据库数据

在C#中操作数据库，通常使用ADO.NET或Entity Framework等技术，下面将通过ADO.NET来演示如何删除和新增数据库数据，假设我们使用的是SQL Server数据库，并且已经有一个名为Employees的表，包含以下列：EmployeeID（主键）、FirstName、LastName和Department。

准备工作

1、引用命名空间：

确保在你的C#项目中引用了必要的命名空间：

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

2、连接字符串：

定义一个连接字符串来连接到你的数据库。

 string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

删除数据

要从Employees表中删除一条记录，可以使用SqlCommand执行DELETE语句，以下是一个示例方法，用于根据员工ID删除特定员工：

public void DeleteEmployee(int employeeId)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string deleteQuery = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
        using (SqlCommand command = new SqlCommand(deleteQuery, connection))
        {
            command.Parameters.AddWithValue("@EmployeeID", employeeId);
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            if (rowsAffected > 0)
            {
                Console.WriteLine($"Employee with ID {employeeId} has been deleted.");
            }
            else
            {
                Console.WriteLine($"No employee found with ID {employeeId}.");
            }
        }
    }
}

新增数据

要向Employees表中插入一条新记录，可以使用INSERT INTO语句，以下是一个示例方法，用于添加一个新员工：

public void AddEmployee(string firstName, string lastName, string department)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string insertQuery = "INSERT INTO Employees (FirstName, LastName, Department) VALUES (@FirstName, @LastName, @Department)";
        using (SqlCommand command = new SqlCommand(insertQuery, connection))
        {
            command.Parameters.AddWithValue("@FirstName", firstName);
            command.Parameters.AddWithValue("@LastName", lastName);
            command.Parameters.AddWithValue("@Department", department);
            connection.Open();
            command.ExecuteNonQuery();
            Console.WriteLine("New employee has been added.");
        }
    }
}

综合示例

以下是一个完整的示例程序，展示了如何删除和新增员工数据：

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        int employeeIdToDelete = 3; // 假设我们要删除的员工ID为3
        string firstName = "John";
        string lastName = "Doe";
        string department = "HR";
        // 删除员工
        DeleteEmployee(employeeIdToDelete);
        // 添加新员工
        AddEmployee(firstName, lastName, department);
    }
    public static void DeleteEmployee(int employeeId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string deleteQuery = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
            using (SqlCommand command = new SqlCommand(deleteQuery, connection))
            {
                command.Parameters.AddWithValue("@EmployeeID", employeeId);
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    Console.WriteLine($"Employee with ID {employeeId} has been deleted.");
                }
                else
                {
                    Console.WriteLine($"No employee found with ID {employeeId}.");
                }
            }
        }
    }
    public static void AddEmployee(string firstName, string lastName, string department)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string insertQuery = "INSERT INTO Employees (FirstName, LastName, Department) VALUES (@FirstName, @LastName, @Department)";
            using (SqlCommand command = new SqlCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@FirstName", firstName);
                command.Parameters.AddWithValue("@LastName", lastName);
                command.Parameters.AddWithValue("@Department", department);
                connection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("New employee has been added.");
            }
        }
    }
}

FAQs

Q1: 如果我不知道要删除的员工ID，但知道员工的姓名，我该如何删除该员工？

A1: 你可以使用SELECT语句先查询出员工的ID，然后再执行删除操作。

public void DeleteEmployeeByName(string firstName, string lastName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string selectQuery = "SELECT EmployeeID FROM Employees WHERE FirstName = @FirstName AND LastName = @LastName";
        using (SqlCommand selectCommand = new SqlCommand(selectQuery, connection))
        {
            selectCommand.Parameters.AddWithValue("@FirstName", firstName);
            selectCommand.Parameters.AddWithValue("@LastName", lastName);
            connection.Open();
            using (SqlDataReader reader = selectCommand.ExecuteReader())
            {
                if (reader.Read())
                {
                    int employeeId = reader.GetInt32(0);
                    DeleteEmployee(employeeId); // 调用前面的DeleteEmployee方法
                }
                else
                {
                    Console.WriteLine("No employee found with the given name.");
                }
            }
        }
    }
}

Q2: 如果我想批量删除多个员工，应该怎么做？

A2: 你可以使用IN子句来指定多个员工ID，然后执行一次DELETE操作。

public void DeleteMultipleEmployees(List<int> employeeIds)
{
    if (employeeIds == null || employeeIds.Count == 0) return;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string deleteQuery = "DELETE FROM Employees WHERE EmployeeID IN (" + string.Join(",", employeeIds) + ")";
        using (SqlCommand command = new SqlCommand(deleteQuery, connection))
        {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"{rowsAffected} employees have been deleted.");
        }
    }
}