using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
|
namespace CSFramework.DB
|
{
|
/// <summary>
|
/// 自定义SqlCommand的基类，组合SqlCommand对象.
|
/// </summary>
|
public class CommandHelper
|
{
|
protected DbCommand _cmd = null;
|
private IDatabase _db = null;
|
|
private string _paramPrefix;
|
|
private Dictionary<string, DbParameter> _Params =
|
new Dictionary<string, DbParameter>();
|
|
/// <summary>
|
/// 参数前缀符号，如：@,?,p_,:
|
/// </summary>
|
public string ParamPrefix
|
{
|
get => _paramPrefix;
|
set => _paramPrefix = value;
|
}
|
|
/// <summary>
|
/// SqlCommand对象
|
/// </summary>
|
public DbCommand Command => _cmd;
|
|
/// <summary>
|
/// SQL命令的参数
|
/// </summary>
|
public DbParameterCollection Params => _cmd.Parameters;
|
|
/// <summary>
|
/// 清除参数
|
/// </summary>
|
public void ClearParam()
|
{
|
_cmd.Parameters.Clear();
|
}
|
|
/// <summary>
|
/// 返回包含参数符号的参数名称，如：@Code
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
private string ParseParamName(string paramName)
|
{
|
//调用具体数据库实例拼接产生
|
if (_db != null)
|
{
|
return _db.ParseParamName(paramName);
|
}
|
else
|
{
|
if (string.IsNullOrEmpty(_paramPrefix) ||
|
string.IsNullOrEmpty(paramName))
|
{
|
return paramName;
|
}
|
else
|
{
|
if (paramName.IndexOf(_paramPrefix) == 0) //首字母包含
|
return paramName;
|
else
|
return _paramPrefix + paramName; //添加参数符号
|
}
|
}
|
}
|
|
/// <summary>
|
/// 删除参数
|
/// </summary>
|
/// <param name="parameterName">参数名称</param>
|
public void RemoveParam(string parameterName)
|
{
|
_cmd.Parameters.RemoveAt(ParseParamName(parameterName));
|
}
|
|
/// <summary>
|
/// 增加参数
|
/// </summary>
|
/// <param name="parameterName">参数名称,不带符号</param>
|
/// <param name="dbType">DbType类型</param>
|
/// <param name="size">长度</param>
|
/// <param name="value">值</param>
|
public DbParameter AddParam(string parameterName, DbType dbType,
|
int size, object value,
|
ParameterDirection direction = ParameterDirection.Input)
|
{
|
DbParameter p;
|
|
if (_db != null)
|
p = _db.CreateParameter(parameterName, value);
|
else
|
p = _cmd.CreateParameter();
|
|
//重新赋值，自动添加参数符号
|
p.ParameterName = ParseParamName(parameterName);
|
|
p.DbType = dbType;
|
p.Size = size;
|
p.Value = value == null ? DBNull.Value : value;
|
p.Direction = direction;
|
_cmd.Parameters.Add(p);
|
return p;
|
}
|
|
public DbParameter AddParam(string parameterName, object value,
|
ParameterDirection direction = ParameterDirection.Input)
|
{
|
DbParameter p;
|
|
if (_db != null)
|
p = _db.CreateParameter(parameterName, value);
|
else
|
p = _cmd.CreateParameter();
|
|
//重新赋值，自动添加参数符号
|
p.ParameterName = ParseParamName(parameterName);
|
p.Value = value == null ? DBNull.Value : value;
|
p.Direction = direction;
|
_cmd.Parameters.Add(p);
|
return p;
|
}
|
|
public DbParameter AddParam(string parameterName,
|
ParameterDirection direction = ParameterDirection.Input)
|
{
|
return AddParam(parameterName, null, direction);
|
}
|
|
/// <summary>
|
/// 增加参数
|
/// </summary>
|
/// <param name="parameterName">参数名称,带@符号</param>
|
/// <param name="sqlDbType">SqlDbType类型</param>
|
/// <param name="value">值</param>
|
/// <param name="direction">输入/输出类型</param>
|
public DbParameter AddParam(string parameterName, DbType dbType,
|
object value,
|
ParameterDirection direction = ParameterDirection.Input)
|
{
|
DbParameter p;
|
|
if (_db != null)
|
p = _db.CreateParameter(parameterName, value);
|
else
|
p = _cmd.CreateParameter();
|
|
//重新赋值，自动添加参数符号
|
p.ParameterName = ParseParamName(parameterName);
|
p.DbType = dbType;
|
p.Value = value == null ? DBNull.Value : value;
|
p.Direction = direction;
|
_cmd.Parameters.Add(p);
|
return p;
|
}
|
|
/// <summary>
|
/// 添加Oracle游标参数，用于返回DataTable/DataSet
|
/// </summary>
|
/// <param name="parameterName"></param>
|
/// <returns></returns>
|
public DbParameter AddParamOracleCursor(string parameterName = "")
|
{
|
var ParaName = string.IsNullOrEmpty(parameterName)
|
? "PCur"
|
: parameterName;
|
var refCursorType =
|
Enum.Parse(
|
((dynamic)_cmd.CreateParameter()).OracleDbType.GetType(),
|
"RefCursor");
|
var param = _cmd.CreateParameter();
|
param.ParameterName = ParaName;
|
param.Direction = ParameterDirection.Output;
|
var piInstance = param.GetType().GetProperty("OracleDbType");
|
piInstance.SetValue(param, refCursorType, null);
|
_cmd.Parameters.Add(param);
|
return param;
|
}
|
|
/// <summary>
|
/// 给SqlCommand.Parameters赋值
|
/// </summary>
|
/// <param name="paramName">参数名</param>
|
/// <param name="value">数值</param>
|
public void SetValue(string paramName, object value)
|
{
|
paramName = ParseParamName(paramName);
|
|
var p = _cmd.Parameters[paramName];
|
if (p == null) throw new Exception(paramName + "参数不存在！");
|
p.Value = value == null ? DBNull.Value : value;
|
}
|
|
/// <summary>
|
/// 获取参数的值
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
public object GetValue(string paramName)
|
{
|
paramName = ParseParamName(paramName);
|
|
var p = _cmd.Parameters[paramName];
|
if (p == null) throw new Exception(paramName + "参数不存在！");
|
return p.Value;
|
}
|
|
public DbParameter GetParameter(string paramName)
|
{
|
paramName = ParseParamName(paramName);
|
|
if (_cmd.Parameters.IndexOf(paramName) >= 0)
|
return _cmd.Parameters[paramName];
|
else
|
return null;
|
}
|
|
#region 构造器
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">数据库接口</param>
|
/// <param name="sql">SQL命令</param>
|
/// <param name="type">SQL命令类型</param>
|
public CommandHelper(IDatabase db, string sql, CommandType type)
|
{
|
_db = db;
|
_cmd = db.CreateCommand(sql, type);
|
_paramPrefix = db.ParamSymboName;
|
}
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="cmd">Command对象</param>
|
/// <param name="paramPrefix">参数符号，如：@,?,p_,:</param>
|
public CommandHelper(DbCommand cmd, string paramPrefix)
|
{
|
_cmd = cmd;
|
_paramPrefix = paramPrefix;
|
|
if (cmd == null) throw new Exception("参数必须为DbCommand派生类的实例！");
|
}
|
|
#endregion
|
}
|
}
|
