public bool Equals(Command other)
{
if (CommandType.Equals(other.CommandType) == false)
{
return(false);
}
return(StateID.Equals(other.StateID));
}
//OoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOo
public void ExecuteNonQuery(CommandType commandType, string commandText, params object[] parameters)
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = commandText;
// Use base class' connection object
cmdToExecute.Connection = _mainConnection;
if (commandType.Equals(CommandType.StoredProcedure) && parameters != null && parameters.Length > 0)
{ //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = GetSpParameterSet(cmdToExecute);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameters);
}
try
{
if (_mainConnectionIsCreatedLocal)
{
// Open connection.
_mainConnection.Open();
}
else
{
if (_mainConnectionProvider.IsTransactionPending)
{
cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
}
}
cmdToExecute.ExecuteNonQuery();
// _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;
//
// if(_errorCode != (int)LLBLError.AllOk)
// {
// // Throw error.
// throw new Exception("Stored Procedure 'sp_tblIPP_UpdateOne' reported the ErrorCode: " + _errorCode);
// }
return;
}
catch (Exception e)
{
throw new Exception(e.Message + " " + cmdToExecute.CommandText);
}
finally
{
if (_mainConnectionIsCreatedLocal)
{
// Close connection.
_mainConnection.Close();
}
cmdToExecute.Dispose();
}
}
//OoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOo
public void ExecuteNonQuery(CommandType commandType, string commandText, params object[] parameters)
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = commandText;
// Use base class' connection object
cmdToExecute.Connection = _mainConnection;
if (commandType.Equals(CommandType.StoredProcedure) && parameters != null && parameters.Length > 0)
{ //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = GetSpParameterSet(cmdToExecute);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameters);
}
try
{
if(_mainConnectionIsCreatedLocal)
{
// Open connection.
_mainConnection.Open();
}
else
{
if(_mainConnectionProvider.IsTransactionPending)
{
cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
}
}
cmdToExecute.ExecuteNonQuery();
// _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;
//
// if(_errorCode != (int)LLBLError.AllOk)
// {
// // Throw error.
// throw new Exception("Stored Procedure 'sp_tblIPP_UpdateOne' reported the ErrorCode: " + _errorCode);
// }
return;
}
catch (Exception e)
{
throw new Exception(e.Message + " " + cmdToExecute.CommandText);
}
finally
{
if(_mainConnectionIsCreatedLocal)
{
// Close connection.
_mainConnection.Close();
}
cmdToExecute.Dispose();
}
}
public bool Equals(CommandModel other)
{
if (other is null)
{
return(false);
}
if (ReferenceEquals(this, other))
{
return(true);
}
return(Id.Equals(other.Id, StringComparison.OrdinalIgnoreCase) &&
Data.Equals(other.Data, StringComparison.OrdinalIgnoreCase) &&
CommandType.Equals(other.CommandType, StringComparison.OrdinalIgnoreCase));
}
public object GetDynamicParameters()
{
RuntimeDefinedParameterDictionary dynamicParams = null;
if (CommandType.Equals(CommandTypeEnum.Complete.ToString()))
{
commandCmdlet = new CompleteCommandCmdlet(this.MyInvocation);
}
else
{
throw new PSArgumentException();
}
dynamicParams = commandCmdlet.RuntimeDefinedParams;
return(dynamicParams);
}
/// <summary>
/// <para>Implementation of <see cref="IEquatable{T}"/> for <see cref="SQLQuery"/>.</para>
/// <para>Returns true if <see cref="SQLQuery"/> references the same <see cref="object"/> or its members are equal.</para>
/// </summary>
/// <param name="other"></param>
/// <returns></returns>
public bool Equals(SQLQuery <T> other)
{
if (other is null)
{
return(false);
}
if (ReferenceEquals(this, other))
{
return(true);
}
return(SQL.Equals(other.SQL) &&
DbTableName.Equals(other.DbTableName) &&
TObjects.Equals(other.TObjects) &&
CommandType.Equals(other.CommandType) &&
IsBulk.Equals(other.IsBulk) &&
BatchSize.Equals(other.BatchSize));
}
/// <summary>
///
/// </summary>
/// <param name="strSqlSPName">存储过程的名字</param>
/// <param name="strParaKeys">参数列表，,隔开</param>
/// <param name="strParaVals">参数值数组</param>
/// <param name="strRetType">Table返回DataTable；String时返回string；Int时返回int</param>
/// <returns></returns>
public object ExecDbCommandAp(string _command, string[] _parakeys, string[] _paravals,
string _rettype, CommandType _commandtype)
{
using (DbCommand cmd = _commandtype.Equals(CommandType.Text) ?
_dbComObj.GetSqlStringCommond(_command) :
_dbComObj.GetStoredProcCommond(_command))
{
if (_parakeys != null && _paravals != null & _parakeys.Length == _paravals.Length)
{
for (int i = 0; i < _parakeys.Length; i++)
{
_dbComObj.AddInParameter(cmd, "@" + _parakeys[i], DbType.String, _paravals[i]);
}
}
if (_rettype == "Table")
{
return(_dbComObj.ExecuteDataTable(cmd));
}
else if (_rettype == "String")
{
return(_dbComObj.ExecuteScalar(cmd).ToString());
}
else if (_rettype == "Int")
{
return(Convert.ToInt32(_dbComObj.ExecuteScalar(cmd)));
}
else if (_rettype == "DataSet")
{
return(_dbComObj.ExecuteDataSet(cmd));
}
else if (_rettype == "ListObj")
{
return(_dbComObj.ExecuteListObject(cmd));
}
else if (_rettype == "ListHash")
{
return(_dbComObj.ExecuteListHashtable(cmd));
}
return(null);
}
}
public bool IsNone()
{
return(type.Equals(CommandType.None));
}
//OoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOo
public DataTable ExecuteQuery(CommandType commandType, string commandText, string tableName, params object[] parameters)
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText =commandText;
// Use base class' connection object
cmdToExecute.Connection = _mainConnection;
if (commandType.Equals(CommandType.TableDirect))
{
cmdToExecute.CommandText = "Select * From " + commandText;
cmdToExecute.CommandType = CommandType.Text;
}
else
{
cmdToExecute.CommandType = commandType;
}
if
(commandType.Equals(CommandType.StoredProcedure) && parameters != null && parameters.Length > 0)
{ //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = GetSpParameterSet(cmdToExecute);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameters);
}
if(_mainConnectionIsCreatedLocal)
{
// Open connection.
_mainConnection.Open();
}
else
{
if(_mainConnectionProvider.IsTransactionPending)
{
cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
}
}
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
DataTable dt = new DataTable();
if (tableName != "")
dt.TableName = tableName;
try
{
adapter.Fill(dt);
return dt;
// _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;
// if(_errorCode != (int)LLBLError.AllOk)
// {
// // Throw error.
// throw new Exception("Stored Procedure 'sp_tblIPP_SelectAll' reported the ErrorCode: " + _errorCode);
// }
}
catch (Exception e)
{
throw new Exception(e.Message + " " + cmdToExecute.CommandText);
}
finally
{
if(_mainConnectionIsCreatedLocal)
{
// Close connection.
_mainConnection.Close();
}
cmdToExecute.Dispose();
adapter.Dispose();
}
}
//OoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOoOo
public DataTable ExecuteQuery(CommandType commandType, string commandText, string tableName, params object[] parameters)
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = commandText;
// Use base class' connection object
cmdToExecute.Connection = _mainConnection;
if (commandType.Equals(CommandType.TableDirect))
{
cmdToExecute.CommandText = "Select * From " + commandText;
cmdToExecute.CommandType = CommandType.Text;
}
else
{
cmdToExecute.CommandType = commandType;
}
if
(commandType.Equals(CommandType.StoredProcedure) && parameters != null && parameters.Length > 0)
{ //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = GetSpParameterSet(cmdToExecute);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameters);
}
if (_mainConnectionIsCreatedLocal)
{
// Open connection.
_mainConnection.Open();
}
else
{
if (_mainConnectionProvider.IsTransactionPending)
{
cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
}
}
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
DataTable dt = new DataTable();
if (tableName != "")
{
dt.TableName = tableName;
}
try
{
adapter.Fill(dt);
return(dt);
// _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;
// if(_errorCode != (int)LLBLError.AllOk)
// {
// // Throw error.
// throw new Exception("Stored Procedure 'sp_tblIPP_SelectAll' reported the ErrorCode: " + _errorCode);
// }
}
catch (Exception e)
{
throw new Exception(e.Message + " " + cmdToExecute.CommandText);
}
finally
{
if (_mainConnectionIsCreatedLocal)
{
// Close connection.
_mainConnection.Close();
}
cmdToExecute.Dispose();
adapter.Dispose();
}
}
/// <summary>
/// Выполнение переданного sql
/// </summary>
/// <param name="sql">Текст запроса, который необходимо выполнить</param>
/// <param name="cn">Строка подключения</param>
/// <param name="ctype">Тип sql-команды</param>
/// <param name="args">Словарь с параметрами: Название параметра с @; значение параметра</param>
/// <param name="localParams">Дополнительные параметры фильтрации, ограничения которых накладываются без учета args</param>
/// <param name="_sort">Порядок сортировки заданный пользователем</param>
/// <param name="_defaultSort">Порядок сортировки по-умолчанию</param>
/// <param name="columnList">Словарь с названием и типом колонок, которые возвращает sql</param>
/// <param name="groupByList">
/// Словарь c колонками, по которым возможна группировка. Значением записи в словаре является
/// флаг о необходимости группировки по данному полю
/// </param>
/// <param name="sumList">Словарь c колонками, по которым необходимо суммирование значений</param>
/// <param name="_pageNum">Номер текущей страницы на форме списка</param>
/// <param name="_itemsPerPage">Количество выводимых записей на страницу</param>
/// <param name="_pageCount">Общее количество страниц</param>
/// <param name="_sRez">Количество найденных записей</param>
/// <returns>Возвращает DataTable с результами запроса</returns>
public static DataTable GetData(string sql, string cn, CommandType ctype,
Dictionary <string, object> args, StringCollection localParams,
string _sort, string _defaultSort,
Dictionary <string, Type> columnList, Dictionary <string, bool> groupByList,
Dictionary <string, decimal> sumList,
#pragma warning disable CS1573 // Параметр "bigdata" не имеет совпадающего тега param в комментарии XML для "DBManager.GetData(string, string, CommandType, Dictionary<string, object>, StringCollection, string, string, Dictionary<string, Type>, Dictionary<string, bool>, Dictionary<string, decimal>, ref int, ref int, ref int, out int, bool, string)" (в отличие от остальных параметров)
#pragma warning disable CS1573 // Параметр "sortBigData" не имеет совпадающего тега param в комментарии XML для "DBManager.GetData(string, string, CommandType, Dictionary<string, object>, StringCollection, string, string, Dictionary<string, Type>, Dictionary<string, bool>, Dictionary<string, decimal>, ref int, ref int, ref int, out int, bool, string)" (в отличие от остальных параметров)
ref int _pageNum, ref int _itemsPerPage, ref int _pageCount, out int _sRez, bool bigdata = false, string sortBigData = "")
#pragma warning restore CS1573 // Параметр "sortBigData" не имеет совпадающего тега param в комментарии XML для "DBManager.GetData(string, string, CommandType, Dictionary<string, object>, StringCollection, string, string, Dictionary<string, Type>, Dictionary<string, bool>, Dictionary<string, decimal>, ref int, ref int, ref int, out int, bool, string)" (в отличие от остальных параметров)
#pragma warning restore CS1573 // Параметр "bigdata" не имеет совпадающего тега param в комментарии XML для "DBManager.GetData(string, string, CommandType, Dictionary<string, object>, StringCollection, string, string, Dictionary<string, Type>, Dictionary<string, bool>, Dictionary<string, decimal>, ref int, ref int, ref int, out int, bool, string)" (в отличие от остальных параметров)
{
var dt = new DataTable("Data");
#region Обработка переданных локальных параметров фильтрации
if (localParams != null && localParams.Count > 0)
{
var filter = "";
for (var i = 0; i < localParams.Count; i++)
{
if (i > 0)
{
filter += " AND ";
}
filter += localParams[i];
}
filter = @"
WHERE (" + filter + ")";
sql = string.Format(sql, filter);
}
if (localParams == null || localParams.Count == 0)
{
sql = string.Format(sql, "");
}
#endregion
#region Оборачиваем запрос во внешний SELECT для добавления группировки
if (ctype.Equals(CommandType.Text) && columnList != null)
{
if (groupByList != null && groupByList.FirstOrDefault(x => x.Value).Value)
{
var groupField = from p in groupByList
where p.Value
select new { Field = p.Key };
var sGroup = @"
SELECT ";
foreach (var f in columnList)
{
if (f.Key.Equals(groupByList.FirstOrDefault(x => x.Key == f.Key && x.Value).Key))
{
sGroup += " " + f.Key + ",";
}
else
{
if (f.Value.Equals(typeof(string)))
{
sGroup += string.Format(" '' {0},", f.Key);
}
else if (f.Value.Equals(typeof(decimal)))
{
sGroup += string.Format(" SUM({0}) {0},", f.Key);
}
else
{
sGroup += string.Format(" NULL {0},", f.Key);
}
}
}
sql = sGroup.Substring(0, sGroup.Length - 1) + string.Format(@"
FROM
({0}) TGroup
GROUP BY ", sql);
foreach (var f in groupField)
{
sql += f.Field + ",";
}
sql = sql.Substring(0, sql.Length - 1);
}
}
#endregion
#region Добавляем сортировку
if (ctype.Equals(CommandType.Text) && (_sort.Length > 0 || _defaultSort.Length > 0))
{
sql += @"
ORDER BY " + (_sort.Length == 0 ? _defaultSort : _sort);
}
#endregion
SqlConnection conn = null;
SqlDataReader dr = null;
DataTable dtSchema = null;
if (bigdata && !ctype.Equals(CommandType.Text))
{
throw new Exception("Для работы в режиме BigData можно использовать только CommandType.Text");
}
if (_itemsPerPage == 0)
{
_itemsPerPage = 2;
}
var pageNum = _pageNum == 0 ? 1 : _pageNum;
var itemsPerPage = _itemsPerPage;
var index = (pageNum - 1) * itemsPerPage;
if (bigdata && _pageNum > -1)
{
sql = string.Format(@"
WITH BigData AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber
FROM ({1}) X_DB
)
SELECT *
FROM BigData
WHERE RowNumber BETWEEN {2} AND {3}
", string.IsNullOrEmpty(sortBigData)? "(SELECT NULL)": sortBigData, sql, index + 1, index + itemsPerPage + 1);
}
conn = new SqlConnection(cn);
var cm = new SqlCommand(sql, conn);
cm.CommandType = ctype;
#region Заполняем коллекцию переданными параметрами, обязательными для запроса
if (args != null)
{
FillQueryArgsCollection(args, cm);
}
#endregion
_sRez = 0;
try
{
if (pageNum > 0)
{
#region Если требуется постраничная разбивка данных используем DataReader
conn.Open();
dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
dtSchema = dr.GetSchemaTable();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
var columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)drow["DataType"]);
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
dt.Columns.Add(column);
}
}
var i = 0;
var n = 0;
List <string> sumListCloneKeys = null;
if (sumList != null)
{
sumListCloneKeys = new List <string>(sumList.Keys);
for (var j = 0; j < sumListCloneKeys.Count; j++)
{
sumList[sumListCloneKeys[j]] = 0;
}
}
while (dr.Read())
{
if (i >= index && n < itemsPerPage || bigdata && i < itemsPerPage)
{
var dataRow = dt.NewRow();
for (var j = 0; j < dt.Columns.Count; j++)
{
dataRow[dt.Columns[j]] = dr[j];
}
dt.Rows.Add(dataRow);
n++;
}
#region Суммируем значения необходимых полей
if (sumList != null)
{
foreach (var t in sumListCloneKeys)
{
sumList[t] +=
Convert.ToDecimal(dr.GetValue(dr.GetOrdinal(t)));
}
}
#endregion
i++;
}
if (!bigdata)
{
_pageCount = ((int)Math.Ceiling(i / (double)itemsPerPage));
_sRez = i;
}
else
{
_pageCount = i > itemsPerPage ? pageNum + 1 : pageNum;
_sRez = (pageNum - 1) * itemsPerPage + i;
}
_pageNum = pageNum == 0 ? 1 : pageNum;
_itemsPerPage = itemsPerPage;
#endregion
}
else
{
cm.CommandTimeout = 60;
conn.Open();
dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
}
}
catch (Exception ex)
{
var dex = new DetailedException(ex.Message, ex, cm);
Logger.WriteEx(dex);
throw dex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return(dt);
}
/// <summary>
/// 参数准备
/// </summary>
/// <param name="cmd"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
private void PrepareCommand(SqlCommand cmd, CommandType commandType, string commandText,
QueryParameterCollection commandParameters)
{
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.Connection = (SqlConnection)m_DbConnection;
cmd.Transaction = (SqlTransaction)trans;
if (commandText.IndexOf("''") >= 0)
{
var blankname = "@____blankchar_______";
commandText = commandText.Replace("''", blankname);
if (commandParameters == null) commandParameters = new QueryParameterCollection();
commandParameters.Add(blankname, "");
}
if ((commandParameters != null) && (commandParameters.Count > 0))
{
for (int i = 0; i < commandParameters.Count; i++)
{
commandParameters[i].InitRealParameter(DatabaseType.MSSQLServer);
cmd.Parameters.Add(commandParameters[i].RealParameter as SqlParameter);
}
}
Open();
//if (this.checkSQL)
//{
string notallow = string.Empty;
//所有的执行方式都检查参数，包括存储过程和SQL语句
//if (CheckSQL.CheckSQLText(commandParameters, out notallow) <= 0) //语句中有不允许的内容，如果是开发人员写的，请改写sql语句或写存储过程实现
//{
// Dev.Log.Loger.Error("sql语句中不允许出现：" + notallow);
// throw new Exception("数据格式不正确！！！");
//}
//}
//if (this.checkParms)
//{
//string notallow;
if (!commandType.Equals(CommandType.StoredProcedure) && CheckSQL.CheckSQLText(commandText, out notallow) <= 0)
//语句中有不允许的内容，如果是开发人员写的，请改写sql语句或写存储过程实现
{
Loger.Error("sql语句中不允许出现：" + notallow);
throw new Exception("数据格式不正确！！！注入字符" + notallow);
}
//}
//DBLog.AddLog("", commandType, commandText, commandParameters);
}
