How to connect SQL Server database with C #

In modern software development, database connection and operation are an indispensable part.

C

Provides a powerful class library to interact with SQL Server databases.

This article will describe in detail how to use the SqlConnectionSumSqlCommandClass to connect to the SQL Server database and show how to perform SQL queries, inserts, updates, and deletes.

。

Ready to work.

Before you start, make sure you have the following software installed: 1. Visual Studio (or other support for C

Integrated development environment).

2. SQL Server database 3. Necessary NuGet packages (e.g System.Data.SqlClient）

Connect to SQL Server database.

First, we need to create a SqlConnectionObject to establish a connection to the SQL Server database.

Here is a simple example code:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // 定义连接字符串
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

        // 创建 SqlConnection 对象
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // 打开连接
                connection.Open();
                Console.WriteLine("连接成功！");
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
        }
    }
}

Execute SQL queries.

Next, we will show how to use SqlCommandObject to execute SQL queries.

Suppose we have a EmployeesTable containing Id,Name, and PositionList.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("连接成功！");

                // 定义 SQL 查询语句
                string query = "SELECT Id, Name, Position FROM Employees";

                // 创建 SqlCommand 对象
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // 执行查询并获取结果集
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int id = reader.GetInt32(0);
                            string name = reader.GetString(1);
                            string position = reader.GetString(2);
                            Console.WriteLine($"ID: {id}, Name: {name}, Position: {position}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("操作失败：" + ex.Message);
            }
        }
    }
}

Insert data.

Use SqlCommandObjects can easily perform insertion operations.

Below is an example of inserting a new employee record:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("连接成功！");

                // 定义插入语句
                string insertQuery = "INSERT INTO Employees (Name, Position) VALUES (@Name, @Position)";

                // 创建 SqlCommand 对象
                using (SqlCommand command = new SqlCommand(insertQuery, connection))
                {
                    // 添加参数并赋值
                    command.Parameters.AddWithValue("@Name", "John Doe");
                    command.Parameters.AddWithValue("@Position", "Developer");

                    // 执行插入操作
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"插入了 {rowsAffected} 行数据。
");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("操作失败：" + ex.Message);
            }
        }
    }
}

Update data.

The update operation is similar to the insertion, except that the SQL statement is different.

Below is an example of updating an employee's position:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("连接成功！");

                // 定义更新语句
                string updateQuery = "UPDATE Employees SET Position = @Position WHERE Name = @Name";

                // 创建 SqlCommand 对象
                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    // 添加参数并赋值
                    command.Parameters.AddWithValue("@Name", "John Doe");
                    command.Parameters.AddWithValue("@Position", "Senior Developer");

                    // 执行更新操作
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"更新了 {rowsAffected} 行数据。
");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("操作失败：" + ex.Message);
            }
        }
    }
}

Delete data.

Finally, let's take a look at how to delete data.

Here's an example of deleting a specific employee:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("连接成功！");

                // 定义删除语句
                string deleteQuery = "DELETE FROM Employees WHERE Name = @Name";

                // 创建 SqlCommand 对象
                using (SqlCommand command = new SqlCommand(deleteQuery, connection))
                {
                    // 添加参数并赋值
                    command.Parameters.AddWithValue("@Name", "John Doe");

                    // 执行删除操作
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"删除了 {rowsAffected} 行数据。
");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("操作失败：" + ex.Message);
            }
        }
    }
}

Summarize.

From the above example, we can see that using C # in SqlConnectionSumSqlCommandIt is very intuitive and efficient for classes to interact with SQL Server databases.

Whether it is query, insert, update or delete operations, it can be achieved through simple code.

I hope this article can help you better understand and apply these technologies and use them flexibly in practical projects.