C#进阶:基于ADO.NET框架的DBHelper工具类实现
在C#中,使用ADO.NET可以方便地进行数据库操作。为了简化数据库操作流程,我们可以创建一个DBHelper工具类。这个工具类通常包括执行SQL查询、插入、更新、删除操作,以及事务管理等功能。下面是一个简单的基于ADO.NET的DBHelper工具类示例:
using System;
using System.Data;
using System.Data.SqlClient;
public class DBHelper
{
private readonly string _connectionString;
public DBHelper(string connectionString)
{
_connectionString = connectionString;
}
// Method to execute a query and return a DataTable
public DataTable ExecuteQuery(string query, SqlParameter[] parameters = null)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
// Method to execute a non-query command (e.g. insert, update, delete)
public int ExecuteNonQuery(string commandText, SqlParameter[] parameters = null)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteNonQuery();
}
}
}
// Method to execute a scalar command
public object ExecuteScalar(string commandText, SqlParameter[] parameters = null)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteScalar();
}
}
}
// Method for executing commands within a transaction
public void ExecuteTransaction(Action<SqlCommand> executeCommands)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction transaction = conn.BeginTransaction())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = transaction;
try
{
executeCommands(cmd);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
}
}
使用说明:
初始化DBHelper:
创建DBHelper
对象时需要提供数据库连接字符串。var dbHelper = new DBHelper("your_connection_string_here");
执行查询:
使用ExecuteQuery
方法执行查询,并返回DataTable
。string query = "SELECT * FROM Users WHERE Age > @Age";
var parameters = new[] { new SqlParameter("@Age", 18) };
DataTable usersTable = dbHelper.ExecuteQuery(query, parameters);
执行非查询命令:
使用ExecuteNonQuery
方法执行插入、更新、删除操作。string insertCommand = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
var parameters = new[]
{
new SqlParameter("@Name", "John Doe"),
new SqlParameter("@Age", 30)
};
int rowsAffected = dbHelper.ExecuteNonQuery(insertCommand, parameters);
执行标量命令:
使用ExecuteScalar
方法获取单个结果值。string scalarCommand = "SELECT COUNT(*) FROM Users";
int userCount = (int)dbHelper.ExecuteScalar(scalarCommand);
事务管理:
使用ExecuteTransaction
方法实现事务操作。dbHelper.ExecuteTransaction((cmd) =>
{
cmd.CommandText = "INSERT INTO Users (Name, Age) VALUES (@Name1, @Age1)";
cmd.Parameters.AddWithValue("@Name1", "Alice");
cmd.Parameters.AddWithValue("@Age1", 25);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Users (Name, Age) VALUES (@Name2, @Age2)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Name2", "Bob");
cmd.Parameters.AddWithValue("@Age2", 28);
cmd.ExecuteNonQuery();
});
这只是一个基本的DBHelper实现,可以根据具体需求进行扩展,比如添加日志记录、连接池管理、参数化SQL,或处理不同的数据库类型。