/// <summary>
/// 生成@xxxx参数
/// </summary>
/// <param name="tableFilter"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public string ParseTableDBParam(TableFilter tableFilter, DBParamCollection parameters)
{
string result = string.Empty;
foreach (var child in tableFilter.ChildFilters)
{
foreach (var value in child.FilterValue.ConvertTostring().Split(','))
{
var param = new DBParam(child.FilterName, value);
parameters.Add(param);
result += param.ParamName + ",";
}
}
return(result.TrimEnd(','));
}
public object ExecuteScalar(string commandText, DBParamCollection parmsValue = null, System.Data.CommandType cmdType = CommandType.Text)
{
if (_dbContextModel == null || _dbContextModel.ConnectionString == null || _dbContextModel.ConnectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
using (var connection = new TDbConnection())
{
connection.ConnectionString = this._dbContextModel.ConnectionString;
connection.Open();
var cmd = new TDbCommand();
PrepareCommand(cmd, connection, cmdType, commandText, parmsValue, out bool mustCloseConnection);
object retval;
// Execute the command & return the results
retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
foreach (TDbParameter dp in cmd.Parameters)
{
if (dp.Direction != ParameterDirection.Input)
{
foreach (DBParam dbParam in parmsValue)
{
if (dbParam.ParamName == dp.ParameterName)
{
dbParam.ParamValue = dp.Value;
break;
}
}
}
}
cmd.Parameters.Clear();
if (mustCloseConnection)
{
connection.Close();
}
return(retval);
}
}
/// <summary>
/// insert 生成values()参数,与列名
/// </summary>
/// <param name="tableFilter"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public string ParseTableColumns(TableFilter tableFilter, DBParamCollection parameters, out string columns)
{
string result = string.Empty;
columns = string.Empty;
foreach (var child in tableFilter.ChildFilters)
{
if (child.FilterValue.IsNullOrEmpty())
{
continue;
}
foreach (var value in child.FilterValue.ConvertTostring().Split(','))
{
var param = new DBParam(child.FilterName, value);
columns += child.FilterName + ",";
parameters.Add(param);
result += param.ParamName + ",";
}
}
columns = columns.TrimEnd(',');
return(result.TrimEnd(','));
}
public System.Data.IDataReader ExecuteReader(string commandText, DBParamCollection parmsValue = null, System.Data.CommandType cmdType = CommandType.Text)
{
if (_dbContextModel == null || _dbContextModel.ConnectionString == null || _dbContextModel.ConnectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
TDbConnection connection = null;
try
{
connection = new TDbConnection
{
ConnectionString = this._dbContextModel.ConnectionString
};
connection.Open();
// Call the overload that takes a connection in place of the connection string
var cmd = new TDbCommand();
PrepareCommand(cmd, connection, cmdType, commandText, parmsValue, out bool mustCloseConnection);
var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (mustCloseConnection)
{
connection.Close();
}
return(dataReader);
}
catch (Exception ex)
{
if (connection != null)
{
connection.Close();
}
throw ex;
}
}
public int ExecuteNonQuery(string commandText, DBParamCollection parmsValue = null, System.Data.CommandType cmdType = CommandType.Text)
{
if (_dbContextModel == null || _dbContextModel.ConnectionString == null || _dbContextModel.ConnectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
// Create & open a SqlConnection, and dispose of it after we are done
using (TDbConnection connection = new TDbConnection())
{
connection.ConnectionString = this._dbContextModel.ConnectionString;
connection.Open();
// Call the overload that takes a connection in place of the connection string
var cmd = new TDbCommand();
PrepareCommand(cmd, connection, cmdType, commandText, parmsValue, out bool mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
foreach (TDbParameter dp in cmd.Parameters)
{
if (dp.Direction != ParameterDirection.Input)
{
foreach (DBParam dbParam in parmsValue)
{
if (dbParam.ParamName == dp.ParameterName)
{
dbParam.ParamValue = dp.Value;
break;
}
}
}
}
cmd.Parameters.Clear();
if (mustCloseConnection)
{
connection.Close();
}
return(retval);
}
}
public virtual System.Data.DataSet ExecuteDataset(string commandText, DBParamCollection parmsValue = null, System.Data.CommandType cmdType = CommandType.Text)
{
if (_dbContextModel == null || _dbContextModel.ConnectionString == null || _dbContextModel.ConnectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
using (var connection = new TDbConnection())
{
connection.ConnectionString = this._dbContextModel.ConnectionString;
connection.Open();
var cmd = new TDbCommand();
PrepareCommand(cmd, connection, cmdType, commandText, parmsValue, out bool mustCloseConnection);
// System.Data.SqlClient.SqlDataAdapter
// Create the DataAdapter & DataSet
using (var da = new TDbDataAdapter())
{
da.SelectCommand = cmd;
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
if (mustCloseConnection)
{
connection.Close();
}
// Return the dataset
return(ds);
}
}
}
public static string Build(this TableFilter tableFilter, DBParamCollection parameters)
{
TableFilterBuilder b = new TableFilterBuilder();
return(b.ParseTableFilter(tableFilter, parameters));
}
public string ParseTableFilter(TableFilter tableFilter, DBParamCollection parameters)
{
if (null == tableFilter)
{
return("1=1");
}
string result = string.Empty;
if (tableFilter.IsContainer)
{
#region 遍历加载子条件
foreach (var child in tableFilter.ChildFilters)
{
child.MainTable = tableFilter.MainTable;
var filterString = ParseTableFilter(child, parameters);
if (string.IsNullOrWhiteSpace(filterString))
{
continue;
}
if (!string.IsNullOrEmpty(result))
{
result += tableFilter.And ? " AND " : " OR ";
}
result += filterString;
}
if (!string.IsNullOrEmpty(result))
{
result = string.Format("{0}({1})", tableFilter.Not ? "NOT" : string.Empty, result);
}
else
{
result = "1=1";
}
#endregion 遍历加载子条件
}
else
{
if (tableFilter.FilterValue.IsNullOrEmpty() && tableFilter.OperateMode != EOperateMode.IsNull & tableFilter.OperateMode != EOperateMode.IsNotNull)
{
return(result);
}
if (tableFilter.OperateMode == EOperateMode.In)
{
string parmsString = string.Empty;
foreach (var value in tableFilter.FilterValue.ConvertTostring().Split(','))
{
var param = new DBParam(tableFilter.FilterName, value);
parameters.Add(param);
parmsString += param.ParamName + ",";
}
result = string.Format("{0}{1} IN ({2})", tableFilter.FilterName, (tableFilter.Not ? " NOT" : string.Empty), parmsString.TrimEnd(','));
}
else
{
var paramValue = string.Empty;
if (tableFilter.OperateMode == EOperateMode.LikeLeft)
{
paramValue = $"{tableFilter.FilterValue}%";
}
else if (tableFilter.OperateMode == EOperateMode.LikeRight)
{
paramValue = $"%{tableFilter.FilterValue}";
}
else if (tableFilter.OperateMode == EOperateMode.LikeRight)
{
paramValue = $"%{tableFilter.FilterValue}%";
}
var param = new DBParam(tableFilter.FilterName, paramValue.IsNullOrEmpty() ? tableFilter.FilterValue : paramValue);
if (tableFilter.OperateMode != EOperateMode.IsNull && tableFilter.OperateMode != EOperateMode.IsNotNull)
{
parameters.Add(param);
}
result = tableFilter.FilterName + GetOperateFilter(tableFilter.OperateMode, param.ParamName, parameters.GetStringPlus);
if (tableFilter.Not)
{
result = "NOT(" + result + ")";
}
}
}
return(result);
}
private void PrepareCommand(TDbCommand command, TDbConnection connection, CommandType commandType, string commandText, DBParamCollection commandParameters, out bool mustCloseConnection)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandText == null || commandText.Length == 0)
{
throw new ArgumentNullException("commandText");
}
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
command.CommandTimeout = _dbContextModel.CommandTimeout;
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary> /// 根据 DBParamCollection 转换成参数化查询 /// </summary> /// <param name="command"></param> /// <param name="commandParameters"></param> public abstract void AttachParameters(TDbCommand command, DBParamCollection commandParameters);