Beispiel #1
0
	protected BXSqlCommand MakeListCommand(BXFormFilter f, BXPagingOptions paging, string sortExp)
	{
		string pagingQuery = "select top {5} * from (select	(row_number() over ({3})) as __RowNumber__, {0} from {1} {2} ) _x_ where __RowNumber__ > {4} order by __RowNumber__";
		string nonPagingQuery = "select	{0} from {1} {2} {3}";

		string select = "*";
		string from = "b_Log";
		string where = string.Empty;
		string orderby = string.Empty;

		BXSqlCommand qcmd = new BXSqlCommand();

		if (f != null)
		{
			SqlDbType t;
			BXSqlFilterCollection fc = new BXSqlFilterCollection();
			foreach (BXFormFilterItem item in f)
			{
				string fn1 = item.filterName.ToLowerInvariant();

				switch (fn1)
				{
					case "type":
						t = SqlDbType.TinyInt;
						break;
					case "id":
						t = SqlDbType.Int;
						break;
					case "code":
						t = SqlDbType.Int;
						break;
					case "source":
						t = SqlDbType.NVarChar;
						break;
					case "occured":
						t = SqlDbType.DateTime;
						break;
					case "title":
						t = SqlDbType.NVarChar;
						break;
					case "message":
						t = SqlDbType.NVarChar;
						break;
					default:
						continue;
				}

				fc.Add(new BXSqlFilter(item.filterName, item.filterOperator, item.filterValue, t));
			}

			BXSqlFilterAnd commandTextWhereAnd = new BXSqlFilterAnd(fc);
			foreach (SqlParameter p in commandTextWhereAnd.FilterParameter)
				qcmd.Parameters.Add(p);

			string commandTextWhere = commandTextWhereAnd.FilterString;

			if (!String.IsNullOrEmpty(commandTextWhere))
				where = string.Format("WHERE {0}", commandTextWhere);
		}

		if (!String.IsNullOrEmpty(sortExp))
			orderby = string.Format("ORDER BY {0}", sortExp);


		if (paging != null && paging.startRowIndex >= 0 && paging.maximumRows > 0)
			qcmd.CommandText = string.Format(pagingQuery, select, from, where, orderby, paging.startRowIndex, paging.maximumRows);
		else
			qcmd.CommandText = string.Format(nonPagingQuery, select, from, where, orderby);

		return qcmd;
	}
Beispiel #2
0
	protected void Grid_SelectCount(object sender, Bitrix.UI.BXSelectCountEventArgs e)
	{
		string query = "select {0} from {1} {2} {3}";

		string select = "count(*)";
		string from = "b_Log";
		string where = string.Empty;
		string orderby = string.Empty;

		BXFormFilter f = Filter.CurrentFilter;

		using (BXSqlCommand qcmd = new BXSqlCommand())
		{
			if (f != null)
			{
				SqlDbType t;
				BXSqlFilterCollection fc = new BXSqlFilterCollection();
				foreach (BXFormFilterItem item in f)
				{
					string fn1 = item.filterName.ToLowerInvariant();

					switch (fn1)
					{
						case "type":
							t = SqlDbType.TinyInt;
							break;
						case "id":
							t = SqlDbType.Int;
							break;
						case "code":
							t = SqlDbType.Int;
							break;
						case "source":
							t = SqlDbType.NVarChar;
							break;
						case "occured":
							t = SqlDbType.DateTime;
							break;
						case "title":
							t = SqlDbType.NVarChar;
							break;
						case "message":
							t = SqlDbType.NVarChar;
							break;
						default:
							continue;
					}

					fc.Add(new BXSqlFilter(item.filterName, item.filterOperator, item.filterValue, t));
				}

				BXSqlFilterAnd commandTextWhereAnd = new BXSqlFilterAnd(fc);
				foreach (SqlParameter p in commandTextWhereAnd.FilterParameter)
					qcmd.Parameters.Add(p);

				string commandTextWhere = commandTextWhereAnd.FilterString;

				if (!String.IsNullOrEmpty(commandTextWhere))
					where = string.Format("WHERE {0}", commandTextWhere);
			}

			qcmd.CommandText = string.Format(query, select, from, where, orderby);
			e.Count = (int)qcmd.ExecuteScalar();
		}
	}