This is a migrated thread and some comments may be shown as answers.

client side filtering is not working in rad grid

4 Answers 149 Views
Grid
This is a migrated thread and some comments may be shown as answers.
avinash
Top achievements
Rank 1
avinash asked on 12 Jun 2014, 06:53 AM

Hi,
i want to apply client side filtering in rad grid . i am following the below link to achive it.

http://demos.telerik.com/aspnet-ajax/grid/examples/data-binding/client-side/programmatic/defaultcs.aspx

till now what i have done 
 i have written the java script  through webmethod iam calling the servr event

   var pageSize; var sortExpressions; var filterExpressions;var currentPageIndex;
            function RadGrid3_OnCommand(sender, args)
             {
              
                args.set_cancel(true);
               pageSize = sender.get_masterTableView().get_pageSize();

                sortExpressions = sender.get_masterTableView().get_sortExpressions();
                 filterExpressions = sender.get_masterTableView().get_filterExpressions();

                 currentPageIndex = sender.get_masterTableView().get_currentPageIndex();
                 if (args.get_commandName() === "Filter") {
                     currentPageIndex = 0;
                      var sortExpressionsAsSQL = sortExpressions.toString();
                  }

                  var tableView = $find("<%= RadGrid3.ClientID %>").get_masterTableView();
                  pageSize = tableView.get_pageSize();

                  sortExpressions = tableView.get_sortExpressions();
                  filterExpressions = tableView.get_filterExpressions();

                  currentPageIndex = tableView.get_currentPageIndex();
                  var startIndex = pageSize * currentPageIndex;
                  PageMethods.GetData(startIndex, pageSize, ahi_event, ahi_state, ahi_userid,filterExpressions.toList(), updateGrid);
}

my c# code is
  [WebMethod]
        public static List<sku_list> GetData(int startIndex, int pageSize, string ahi_event, string ahi_state, string ahi_userid, List<GridFilterExpression> filterExpressions)
        {
            List<sku_list> list = new List<sku_list>();
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("DECLARE @startRow int     ");
            sqlBuilder.AppendLine("SET ROWCOUNT @maximumRows");

            sqlBuilder.AppendLine("WITH OrderedEmployees As");
            sqlBuilder.AppendLine("(");
        
            string sortExpressions = "";
            
           
            //List<DbParameter> parameters = new List<DbParameter>();
            var conString = System.Configuration.ConfigurationManager.ConnectionStrings["Amscan-ProdConnectionString"];

            // string commandString = "SELECT * FROM Artwork_sku where ahi_event_name='" + startIndex + "' and state='" + maximumRows + "'";
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Amscan-ProdConnectionString"].ConnectionString))
            {
                SqlCommand command = new SqlCommand("SP_AHI_SKULIST", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = ahi_userid;
                command.Parameters.Add("@EVENAME", SqlDbType.VarChar).Value = ahi_event;
                command.Parameters.Add("@STATE", SqlDbType.VarChar).Value = ahi_state;
             
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        sku_list newObject = new sku_list();
                
                            newObject.userid = reader[0].ToString();
                            newObject.Item = reader[1].ToString();
                            Topvalue = newObject.Item;
                            newObject.event_name = reader[2].ToString();
                            newObject.Description = reader[3].ToString();
                            newObject.Is_New = reader[4].ToString();
                            newObject.State = reader[5].ToString();
                   
                        list.Add(newObject);
                    }
                    int counter = 0;
                    List<DbParameter> parameters = new List<DbParameter>();
                   
                    if (filterExpressions.Count > 0)
                        sqlBuilder.Append(" WHERE ");
                    foreach (GridFilterExpression expression in filterExpressions)
                    {
                        counter++;
                        Pair parameter = BuildParameter(expression);
                        parameters.Add((DbParameter)parameter.Second);

                        sqlBuilder.AppendFormat((string)parameter.First);
                        if (counter < filterExpressions.Count)
                        {
                            sqlBuilder.AppendFormat(" And ");
                        }
                    }
                    parameters.ForEach(

                   delegate(DbParameter parameter)
                   {
                       if (parameter != null)
                           command.Parameters.Add(parameter);
                   }
               );
                }
                finally
                {
                    reader.Close();
                }
            }
            return list;
        }

the method for filttering  in c#

 #region filter
        private static Pair BuildParameter(GridFilterExpression expression)
        {
            string fieldName = expression.FieldName.Trim().Split(' ')[0];
            GridKnownFunction filterFunction =
                (GridKnownFunction)Enum.Parse(typeof(GridKnownFunction), expression.FilterFunction);

            SqlParameter sqlParameter = null;

            string filterExpression = string.Empty;
            switch (filterFunction)
            {
                case GridKnownFunction.NoFilter:
                    filterExpression = "1 = 1";
                    break;
                case GridKnownFunction.Contains:
                    filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         string.Format("%{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                    break;
                case GridKnownFunction.DoesNotContain:
                    filterExpression = string.Format("[{0}] NOT LIKE @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         string.Format("%{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                    break;
                case GridKnownFunction.StartsWith:
                    filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         string.Format("{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                    break;
                case GridKnownFunction.EndsWith:
                    filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         string.Format("%{0}", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                    break;
                case GridKnownFunction.EqualTo:
                    filterExpression = string.Format("[{0}] = @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.NotEqualTo:
                    filterExpression = string.Format("[{0}] <> @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.GreaterThan:
                    filterExpression = string.Format("[{0}] > @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                        Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.LessThan:
                    filterExpression = string.Format("[{0}] < @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.GreaterThanOrEqualTo:
                    filterExpression = string.Format("[{0}] >= @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                        Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.LessThanOrEqualTo:
                    filterExpression = string.Format("[{0}] <= @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.Between:
                    filterExpression = string.Format(" ([{0}] >= @{0}) AND ([{0}] <= @{0})", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                        Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.NotBetween:
                    filterExpression = string.Format(" ([{0}] < @{0}) OR ([{0}] > @{0})", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                        Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                    break;
                case GridKnownFunction.IsEmpty:
                    filterExpression = string.Format(" [{0}] = @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         string.Empty);
                    break;
                case GridKnownFunction.NotIsEmpty:
                    filterExpression = string.Format(" [{0}] <> @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                          string.Empty);
                    break;
                case GridKnownFunction.IsNull:
                    filterExpression = string.Format(" [{0}] = @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         DBNull.Value);
                    break;
                case GridKnownFunction.NotIsNull:
                    filterExpression = string.Format(" [{0}] <> @{0}", fieldName);
                    sqlParameter =
                        new SqlParameter(string.Format("@{0}", fieldName),
                                         DBNull.Value);
                    break;
            }

            return new Pair(filterExpression,
                            sqlParameter);
        }
        #endregion


now i dont know what to do to make filter working , the example link which i am following is doing lot of thing i am using a view in my sql command so iam confused also .

if any simple client side filtering example is there then plz let me know

4 Answers, 1 is accepted

Sort by
0
avinash
Top achievements
Rank 1
answered on 16 Jun 2014, 05:20 AM
i am trying the above way and till now what i have achived i want to let you know

my filter method [ private static Pair BuildParameter(GridFilterExpression expression)] is hitting when i am clicking the filter in rad grid and while debugging i am able to see the value which i enter to filter and the filter funtion which i click but how to update the grid according to the filter funtion .

how to update this  below method according to the filter funtion so that the radgrid show yhe fiter data

public static List<sku_list> GetData(int startIndex, int
pageSize, string ahi_event, string ahi_state, string ahi_userid,
List<GridFilterExpression> filterExpressions)


any help will be appreciated
0
Daniel
Telerik team
answered on 17 Jun 2014, 07:51 AM
Hello Avinash,

If there is no data coming to RadGrid I would recommend that you put a breakpoint on the line where the ExecuteReader method is called to ensure that the SqlDataReader is populated properly.
Also I would recommend that you open your browser console and check for errors which may lead you to the root of the problem.

Regards,
Daniel
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
avinash
Top achievements
Rank 1
answered on 17 Jun 2014, 10:07 AM
Hi Daniel,
  I appreciate your comment on this , that's the the thing i want to know how to intimate sql reader regarding the filter function, how does the sqlreader will act on the this method return
private static Pair BuildParameter(GridFilterExpression expression)

        {

    return new Pair(filterExpression,

                            sqlParameter);

}

how to use sqlparameter

if you have any link to share regarding client side filtering or any application then plz share
0
Daniel
Telerik team
answered on 20 Jun 2014, 11:54 AM
Hello Avinash,

The BuildParameter method will convert the GridFilterExpression object to Pair of string filter expression and an SqlParameter. This helper method is used when GetData/GetCount page methods are invoked.
If you have any doubts about the way this logic works, I would strongly recommend that you debug the code from the programmatic binding demo step-by-step.

Regards,
Daniel
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
Tags
Grid
Asked by
avinash
Top achievements
Rank 1
Answers by
avinash
Top achievements
Rank 1
Daniel
Telerik team
Share this question
or