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