using System.Data;
|
using Microsoft.Data.SqlClient;
|
using MES.Service.util;
|
|
namespace MES.Service.DB;
|
|
/// <summary>
|
/// 用于SQL Server数据库访问的帮助类
|
/// </summary>
|
public class SqlServerSQLHelper
|
{
|
private readonly string _connectionString;
|
|
private readonly string ConnectionString =
|
AppsettingsUtility.Settings.DataBaseConn;
|
|
public SqlServerSQLHelper()
|
{
|
_connectionString = ConnectionString;
|
}
|
|
public SqlServerSQLHelper(string connectionString)
|
{
|
_connectionString = connectionString;
|
}
|
|
public DataTable ExecuteQuery(string query)
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
using (var command = new SqlCommand(query, connection))
|
{
|
connection.Open();
|
var dataTable = new DataTable();
|
using (var reader = command.ExecuteReader())
|
{
|
dataTable.Load(reader);
|
}
|
return dataTable;
|
}
|
}
|
|
// 执行非查询操作 对数据库进行 增、删、改 操作(1）
|
public int ExecuteNonQuery(string query)
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
using (var command = new SqlCommand(query, connection))
|
{
|
connection.Open();
|
return command.ExecuteNonQuery();
|
}
|
}
|
|
public int ExecuteNonQueryWithTransaction(SqlConnection connection,
|
SqlTransaction transaction, string query)
|
{
|
using (var command = connection.CreateCommand())
|
{
|
command.Transaction = transaction;
|
command.CommandText = query;
|
return command.ExecuteNonQuery();
|
}
|
}
|
|
public int ExecuteTransaction(string query1)
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
connection.Open();
|
var transaction = connection.BeginTransaction();
|
int totalRowsAffected = 0;
|
|
try
|
{
|
totalRowsAffected = ExecuteNonQueryWithTransaction(
|
connection, transaction, query1);
|
transaction.Commit();
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
throw new Exception(ex.Message);
|
}
|
return totalRowsAffected;
|
}
|
}
|
|
public object ExecuteScalar(string query)
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
using (var command = new SqlCommand(query, connection))
|
{
|
connection.Open();
|
return command.ExecuteScalar();
|
}
|
}
|
|
public void ExecuteStoredProcedure(string procedureName,
|
SqlParameter[] parameters)
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
using (var command = new SqlCommand(procedureName, connection))
|
{
|
command.CommandType = CommandType.StoredProcedure;
|
command.Parameters.AddRange(parameters);
|
connection.Open();
|
command.ExecuteNonQuery();
|
}
|
}
|
|
public DataTable ExecuteDataTable(string sql)
|
{
|
return ExecuteDataTable(sql, CommandType.Text, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType)
|
{
|
return ExecuteDataTable(sql, commandType, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType,
|
SqlParameter[] parameters)
|
{
|
var data = new DataTable();
|
using (var connection = new SqlConnection(_connectionString))
|
using (var command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
command.Parameters.AddRange(parameters);
|
|
using (var adapter = new SqlDataAdapter(command))
|
{
|
adapter.Fill(data);
|
}
|
}
|
return data;
|
}
|
}
|
