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); }