在C#项目中管理SQL Server连接,通常遵循以下步骤:
using System.Data;
using System.Data.SqlClient;
public static SqlConnection CreateConnection(string connectionString)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
return connection;
}
using
语句确保连接正确关闭:string connectionString = "your_connection_string";
using (SqlConnection connection = CreateConnection(connectionString))
{
// 在这里执行你的数据库操作
}
public static DataTable ExecuteQuery(SqlConnection connection, string query)
{
DataTable result = new DataTable();
using (SqlCommand command = new SqlCommand(query, connection))
{
SqlDataReader reader = command.ExecuteReader();
result.Load(reader);
}
return result;
}
string connectionString = "your_connection_string";
using (SqlConnection connection = CreateConnection(connectionString))
{
string query = "SELECT * FROM your_table";
DataTable result = ExecuteQuery(connection, query);
// 处理查询结果
}
public static int ExecuteNonQuery(SqlConnection connection, string query, params SqlParameter[] parameters)
{
int rowsAffected;
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddRange(parameters);
rowsAffected = command.ExecuteNonQuery();
}
return rowsAffected;
}
string connectionString = "your_connection_string";
using (SqlConnection connection = CreateConnection(connectionString))
{
string query = "INSERT INTO your_table (column1, column2) VALUES (@value1, @value2)";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@value1", "your_value1"),
new SqlParameter("@value2", "your_value2")
};
int rowsAffected = ExecuteNonQuery(connection, query, parameters);
// 处理影响的行数
}
通过遵循这些步骤,你可以在C#项目中有效地管理SQL Server连接。记住始终使用参数化查询以防止SQL注入攻击,并确保在不再需要时正确关闭连接。