SQL 数据库引擎
1.83/5 (3投票s)
用于 .Sql Server 数据库引擎的辅助类
引言
这次我将介绍一个关于 SqlHelper 的文章，名为 Sql 数据库引擎。它提供了针对 Sql Server 的各种数据访问操作的方法。
使用代码
使用代码非常简单。下载本文附带的 dll。在您的项目中引用它并开始使用它。本文的目标读者是初级/中级开发人员。
通常，当我们访问数据库结果到页面时，我们会每次在同一页面上编写大量数据访问代码，每当需要时。这会使您的代码变得混乱且难以调试或未来修改。我早期也是这样做的。
这个类可以帮助您消除这种混乱的代码，使您的代码更轻量级且易于理解。
让我们来看下面的代码
命名空间：我想每个 .Net 开发人员都应该了解这个命名空间。它们与 C/C++ 的头文件类似，包含定义和声明类、方法、属性等。可以创建自己的命名空间。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
这里 AmitRanjan.DataAccessBlock.DBEngine 是我的命名空间，我可以通过它在任何应用程序中调用我的类及其方法。我的类是 SqlDataEngine，因为它仅包含 Sql Server 特定的数据访问方法。 这是一个密封类，因为我不希望它被继承。并且将其构造函数设为私有，以防止使用 new 关键字创建其对象。
namespace AmitRanjan.DataAccessBlock.DBEngine
{
public sealed class SqlDataEngine
{
private SqlDataEngine() { }
全局变量，用于从配置文件中保存连接字符串和 SqlConnection 类的实例。设为私有，以防止从 SqlDataEngine 类外部访问它。
private static string connectionString = Common.ConnectionString;
private static SqlConnection connection;
EstablishConnection()：此方法检查 SqlConnection 实例的当前状态，如果发现已打开，则关闭现有连接并重新打开一个新连接以与数据库交互。您必须在数据访问块的开头调用此方法。例如这样。假设我正在尝试获取有关学生的信息，我的方法名称是 GetStudentDetails。那么我的方法将是
private void GetStudentDetails(int StudentID)
{
try
{
EstablishConnection();
//Your Statements for Data Access
}
catch(Exception ex){// catch block statements}
}
我希望这能清晰地说明 EstablishConnection 方法的作用。
/// <summary>
/// Establishes Connection with SqlServer Instance
/// </summary>
public static void EstablishConnection()
{
try
{
connection = new SqlConnection(connectionString);
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
}
catch
{
throw new DatabaseServerNotFoundException("Unable to connect with database server. Please contact your network administrator.");
}
}
TerminateConnection：这是 EstablishConnection 方法的相反操作。您可以在应用程序的 finally 块中使用它作为清理代码。这样，无论是否有任何异常，连接都会被关闭。
/// <summary>
/// Teminates existing connection with SqlServer Instance
/// </summary>
public static void TerminateConnection()
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
}
BeginTransaction：这会将 SqlTransaction 与当前的 SqlConnection 关联起来，如果用户在操作多个表时需要事务。
用法：在代码块中声明 SqlTransaction 的实例。
C#：SqlTransaction transaction;
VB：Dim transaction as new SqlTransaction
在包含以下数据访问层方法的您的方法中，例如，使用如下代码：ExecuteNonQuery(yourSqlStatement, transaction)
private void TransactionTest( string cmdText)
{
SqlTransaction transaction = new SqlTransaction();
try
{
SqlDataEngine.EstablishConnection();
SqlDataEngine.BeginTransaction(transaction); //Start a new transaction taking condition your query is inserting/updating / deleting on multiple tables.
SqlDataEngine.ExecuteNonQuery(cmdText,transaction); // Execute your query
SqlDataEngine.CommitTransaction(); // Commit on successful completion
}
catch
{
SqlDataEngine.RollbackTransaction();
}
finally
{
SqlDataEngine.TerminateConnection();
}
}
我认为上面的代码块将消除关于方法实现的大多数疑问。如果还有疑问，请在下面发布您的查询。
/// <summary>
/// Starts a transaction with existing connection
/// </summary>
/// <param name="transaction"></param>
public static void BeginTransaction(SqlTransaction transaction)
{
if (transaction != null)
{
transaction = connection.BeginTransaction();
}
else
{
throw new NullReferenceException("Transaction object is null.");
}
}
/// <summary>
/// Commits a successfully executed statement
/// </summary>
/// <param name="transaction">sql transaction</param>
public static void CommitTransaction(SqlTransaction transaction)
{
if (transaction != null)
{
transaction.Commit();
}
else
{
throw new NullReferenceException("Transaction object is null.");
}
}
/// <summary>
/// Rollbacks a transaction if error occurred while processing.
/// </summary>
/// <param name="transaction">sql transaction</param>
public static void RollbackTransaction(SqlTransaction transaction)
{
if (transaction != null)
{
transaction.Rollback();
}
else
{
throw new NullReferenceException("Transaction object is null.");
}
}
/// <summary>
/// Executes Non queries like Insert,Update,Delete
/// </summary>
/// <param name="commandText">Sql Statement as string</param>
/// <returns>no. of rows affected by the query as integer</returns>
public static int ExecuteNonQuery(string commandText)
{
int returnValue = 0;
using (SqlCommand command = new SqlCommand(commandText, connection))
{
returnValue = command.ExecuteNonQuery();
}
return returnValue;
}
/// <summary>
/// Executes Non queries like Insert,Update,Delete, in case Sql parameters are required
/// </summary>
/// <param name="commandText">Sql Statement as String</param>
/// <param name="parameters">Array of SqlParameters</param>
/// <returns>no. of rows affected as integer</returns>
public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
{
int returnValue = 0;
using (SqlCommand command = new SqlCommand(commandText, connection))
{
if (parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
returnValue = command.ExecuteNonQuery();
}
else
{
throw new SqlParametersException("If using Sql Parameters, count could not be Zero. ");
}
}
return returnValue;
}
/// <summary>
/// Executes Non queries like Insert,Update,Delete, in case Sql parameters are required
/// </summary>
/// <param name="commandText">Sql Statement as String</param>
/// <param name="parameters">Array of SqlParameters</param>
/// <param name="transaction">Sql Transaction</param>
/// <returns>no. of rows affected as integer</returns>
public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
{
int returnValue = 0;
if (transaction != null)
{
using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
{
if (parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
returnValue = command.ExecuteNonQuery();
}
else
{
throw new SqlParametersException("If using Sql Parameters, count could not be Zero. ");
}
}
}
else
{
throw new NullReferenceException( "Transaction object is null.");
}
return returnValue;
}
/// <summary>
/// Executes select statement and loads result to datatable
/// </summary>
/// <param name="commandText">Sql Statement as String</param>
/// <returns>Result as Datatable</returns>
public static DataTable ExecuteReader(string commandText)
{
DataTable dtResult = new DataTable();
using (SqlCommand command = new SqlCommand(commandText,connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
dtResult.Load(reader);
}
}
return dtResult;
}
/// <summary>
/// Executes select statement and loads result to datatable with parameters supplied
/// </summary>
/// <param name="commandText">Sql Statement as String</param>
/// <param name="parameters">Array of sql parameters</param>
/// <returns>Result as Datatable</returns>
public static DataTable ExecuteReader(string commandText, SqlParameter[] parameters)
{
DataTable dtResult = new DataTable();
using (SqlCommand command = new SqlCommand(commandText, connection))
{
if (parameters.Length > 0)
{
using (SqlDataReader reader = command.ExecuteReader())
{
dtResult.Load(reader);
}
}
else
{
throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
}
}
return dtResult;
}
/// <summary>
/// Executes select statement and loads result to datatable with parameters supplied
/// </summary>
/// <param name="commandText">Sql Statement as String</param>
/// <param name="parameters">Array of sql parameters</param>
/// <param name="transaction">Sql Transaction</param>
/// <returns>Result as Datatable</returns>
public static DataTable ExecuteReader(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
{
DataTable dtResult = new DataTable();
if (transaction != null)
{
using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
{
if (parameters.Length > 0)
{
using (SqlDataReader reader = command.ExecuteReader())
{
dtResult.Load(reader);
}
}
else
{
throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
}
}
}
else
{
throw new NullReferenceException("Transaction object is null.");
}
return dtResult;
}
/// <summary>
/// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
/// </summary>
/// <param name="commandText">Sql statement as string</param>
/// <returns>value of first row and first column as object</returns>
public static object ExecuteScalar(string commandText)
{
object oResult = null;
using (SqlCommand command = new SqlCommand(commandText, connection))
{
oResult = command.ExecuteScalar();
}
return oResult;
}
/// <summary>
/// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
/// </summary>
/// <param name="commandText">Sql statement as string</param>
/// /// <param name="parameters">Array of SqlParameters</param>
/// <returns>value of first row and first column as object</returns>
public static object ExecuteScalar(string commandText, SqlParameter[] parameters)
{
object oResult = null;
using (SqlCommand command = new SqlCommand(commandText, connection))
{
if (parameters.Length > 0)
{
oResult = command.ExecuteScalar();
}
else
{
throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
}
}
return oResult;
}
/// <summary>
/// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
/// </summary>
/// <param name="commandText">Sql statement as string</param>
/// <param name="parameters">Array of SqlParameters</param>
/// <param name="transaction">Sql transaction </param>
/// <returns>value of first row and first column as object</returns>
public static object ExecuteScalar(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
{
object oResult = null;
if (transaction != null)
{
if (parameters.Length > 0)
{
using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
{
oResult = command.ExecuteScalar();
}
}
else
{
throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
}
}
else
{
throw new NullReferenceException("Transaction object is null.");
}
return oResult;
}
}
}