You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
90 lines
2.6 KiB
90 lines
2.6 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using FireBirdUtil.DataTypes;
|
|
using FirebirdSql.Data.FirebirdClient;
|
|
using System.Data;
|
|
|
|
namespace FireBirdUtil.SqlUtils
|
|
{
|
|
public class SqlExecuteBaseUtil
|
|
{
|
|
/// <summary>
|
|
/// 执行非查询命令(如果事务参数不为null，则在事务中执行)
|
|
/// </summary>
|
|
public static void ExecuteNonQuery(FbConnection connection, FbTransaction transaction, string commandText)
|
|
{
|
|
using (FbCommand fbCmd = connection.CreateCommand()) {
|
|
if (transaction != null)
|
|
fbCmd.Transaction = transaction;
|
|
fbCmd.CommandText = GetReplaceNullSql(commandText);
|
|
fbCmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行查询命令【根据返回的单个值处理后的值】(如果事务参数不为null，则在事务中执行)
|
|
/// </summary>
|
|
/// <typeparam name="TResult">处理后的结果类型</typeparam>
|
|
public static TResult ExecuteScalar<TResult>(FbConnection connection, FbTransaction transaction, string commandText, Func<object, TResult> DealResult)
|
|
{
|
|
#if DEBUG
|
|
if (DealResult == null)
|
|
throw new ArgumentException("DealResult参数不允许为null");
|
|
#endif
|
|
TResult result;
|
|
using (FbCommand fbCmd = connection.CreateCommand()) {
|
|
if (transaction != null)
|
|
fbCmd.Transaction = transaction;
|
|
fbCmd.CommandText = GetReplaceNullSql(commandText);
|
|
var obj = fbCmd.ExecuteScalar();
|
|
result = DealResult(obj);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行查询命令(如果事务参数不为null，则在事务中执行)
|
|
/// </summary>
|
|
public static DataTable ExecuteReader(FbConnection connection, FbTransaction transaction, string commandText)
|
|
{
|
|
DataTable table;
|
|
using (FbCommand fbCmd = connection.CreateCommand()) {
|
|
if (transaction != null)
|
|
fbCmd.Transaction = transaction;
|
|
fbCmd.CommandText = GetReplaceNullSql(commandText);
|
|
var reader = fbCmd.ExecuteReader();
|
|
table = CreateDataTable(reader);
|
|
}
|
|
return table;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 如果null由引号包括，则去掉null前后的引号。即：用null代替'null'
|
|
/// </summary>
|
|
private static string GetReplaceNullSql(string sql)
|
|
{
|
|
var tmp = sql.ToLower();
|
|
var index = tmp.IndexOf("'null'");
|
|
if (index < 0)
|
|
return sql;
|
|
string old = sql.Substring(index, 6);
|
|
sql = sql.Replace(old, "null");
|
|
sql = GetReplaceNullSql(sql);
|
|
return sql;
|
|
}
|
|
|
|
private static DataTable CreateDataTable(FbDataReader reader)
|
|
{
|
|
if (reader.IsClosed)
|
|
throw new ApplicationException("查询命令已结束");
|
|
|
|
var dataTable = new DataTable();
|
|
dataTable.Load(reader);
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
}
|
|
}
|