Extract Filter part in Odata QueryOptions Web API

If you are creating Web API using Odata controller  and want to extract filters query from Odata request. Refer below sample query(bold part is odata filter)

You can download the code from (https://github.com/mesan21ster/ExtractOdataFilterOptions)
You can also follow my YouTube channel (https://www.youtube.com/playlist?list=PLbAvaAiacB_FwIYjEU8hDXnn6r56XeHrU)

Follow below simple steps to get that.

Sample Odata URL

https://localhost:44335/odata/testData/testDataType?$format=json&$filter=(FirstName+eq+'Santosh'+AND+LastName+eq+'Kumar'+AND+Age+eq+ 30+AND+BirthYear+eq+2019+AND+BirthCity+eq+'Delhi')&$count=true&$orderby=Age+desc

Namespaces used in code
using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using System.Linq;
using Microsoft.AspNet.OData;
using Microsoft.AspNet.OData.Query;

using Microsoft.AspNet.OData.Routing;

Sample OdataController
namespace test.Controllers

{
[ODataRoutePrefix("testData")]

public class TestDataController : ODataController
{
[ODataRoute("testDataType")]
[HttpGet]
public IQueryable testoData(ODataQueryOptions oDataQueryOptions)
        {
//here you have to follow below steps to extract odata filters from odata query options parameter
}
}
}
Step 1

Declare filter expression

 string oDataFilterExpression = @"(?" +
                            "\n" + @"     (?.+?)\s+" +
                            "\n" + @"     (?eq|ne|gt|ge|lt|le|add|sub|mul|div|mod)\s+" +
                            "\n" + @"     '?(?.+?)'?" +
                            "\n" + @")" +
                            "\n" + @"(?:" +
                            "\n" + @"    \s*$" +
                            "\n" + @"   |\s+(?:or|and|not)\s+" +
                            "\n" + @")" +
                            "\n";  

Step 2
Declare regex and target filter string ( you can get your target filter string from "oDataQueryOptions.Filter.RawValue"
Regex oDataFilterRegex = new Regex(oDataFilterExpression, RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);


string strTargetString = @"FirstName eq 'Santosh' AND LastName eq 'Kumar' AND Age eq  30 AND BirthYear eq 2019 AND BirthCity eq 'Delhi'".TrimEnd(')').TrimStart('(');

Step 3
Create a replace string in json format and make json array
string strReplace = @"{ 'Resource' : '${Resource}'," + @"   'Operator' : '${Operator}'," + @"   'Value'    : '${Value}'},";
 var val = oDataFilterRegex.Replace(strTargetString, strReplace);

 var filterJson = "[" + val.TrimEnd(',') + "]"; //Make json array

Step 4
Create a class to Deserialize json string to object
    public class FilterValue
    {
        public string Resource { get; set; }
        public string Operator { get; set; }
        public string Value { get; set; }

    }

Step 5 get result back
Deserialize json string to object and find your expected value

var oDataFilterObject = JsonConvert.DeserializeObject<List<FilterValue>>(filterJson);

var firstName= oDataFilterObject.Find(x => x.Resource == "FirstName").Value;

Dynamic sql query

Use dynamic sql query like below

declare @TableName varchar(max)

declare @type varchar(max)

  set @TableName='test'

  set @type ='''abc'''

  declare @SQLQuery varchar(max)

   SET @SQLQuery = 'SELECT LETTER_ID FROM ' + @TableName  + ' WHERE LETTER_TYPE =  ' + @type

  print @SQLQuery

 execute(@SQLQuery)

Delete duplicate Row in sql server

Delete Duplicate Row in sql server

create table test(name varchar(20),rollNo int)

insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)

SET ROWCOUNT 0 --execute rowcount 0 after executing the cursor
select * from test order by name


--Create cursor for duplicate row delete

Declare @Count int
DECLARE @rollNo int
DECLARE @name nvarchar(20)


DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, rollNo, Count(*) - 1
FROM test
GROUP BY name, rollNo
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name,@rollNo,@Count

WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @count
--DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo
DELETE FROM test WHERE name = @name AND rollNo = @rollNo

FETCH NEXT FROM dublicate_cursor INTO @name, @rollNo, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor


Note:- SET ROWCOUNT @count is used when you are using sql server 2000
In sql server 2005 you can use DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo


Thanks and enjoy

Formatting specifiers

Numbers
Basic number formatting specifiers:

Specifier Type Format Output (Passed Double 1.42) Output (Passed Int -12400)
c Currency {0:c} $1.42 -$12,400
d Decimal (Whole number) {0:d} System.FormatException -12400
e Scientific {0:e} 1.420000e+000 -1.240000e+004
f Fixed point {0:f} 1.42 -12400.00
g General {0:g} 1.42 -12400
n Number with commas for thousands {0:n} 1.42 -12,400
r Round trippable {0:r} 1.42 System.FormatException
x Hexadecimal {0:x4} System.FormatException cf90
Custom number formatting:

Specifier Type Example Output (Passed Double 1500.42) Note
0 Zero placeholder {0:00.0000} 1500.4200 Pads with zeroes.
# Digit placeholder {0:(#).##} (1500).42
. Decimal point {0:0.0} 1500.4
, Thousand separator {0:0,0} 1,500 Must be between two zeroes.
,. Number scaling {0:0,.} 2 Comma adjacent to Period scales by 1000.
% Percent {0:0%} 150042% Multiplies by 100, adds % sign.
e Exponent placeholder {0:00e+0} 15e+2 Many exponent formats available.
; Group separator see below
The group separator is especially useful for formatting currency values which require that negative values be enclosed in parentheses. This currency formatting example at the bottom of this document makes it obvious:

Dates
Note that date formatting is especially dependant on the system’s regional settings; the example strings here are from my local locale.

Specifier Type Example (Passed System.DateTime.Now)
d Short date 10/12/2002
D Long date December 10, 2002
t Short time 10:11 PM
T Long time 10:11:29 PM
f Full date & time December 10, 2002 10:11 PM
F Full date & time (long) December 10, 2002 10:11:29 PM
g Default date & time 10/12/2002 10:11 PM
G Default date & time (long) 10/12/2002 10:11:29 PM
M Month day pattern December 10
r RFC1123 date string Tue, 10 Dec 2002 22:11:29 GMT
s Sortable date string 2002-12-10T22:11:29
u Universal sortable, local time 2002-12-10 22:13:50Z
U Universal sortable, GMT December 11, 2002 3:13:50 AM
Y Year month pattern December, 2002
The ‘U’ specifier seems broken; that string certainly isn’t sortable.

Custom date formatting:

Specifier Type Example Example Output
dd Day {0:dd} 10
ddd Day name {0:ddd} Tue
dddd Full day name {0:dddd} Tuesday
f, ff, … Second fractions {0:fff} 932
gg, … Era {0:gg} A.D.
hh 2 digit hour {0:hh} 10
HH 2 digit hour, 24hr format {0:HH} 22
mm Minute 00-59 {0:mm} 38
MM Month 01-12 {0:MM} 12
MMM Month abbreviation {0:MMM} Dec
MMMM Full month name {0:MMMM} December
ss Seconds 00-59 {0:ss} 46
tt AM or PM {0:tt} PM
yy Year, 2 digits {0:yy} 02
yyyy Year {0:yyyy} 2002
zz Timezone offset, 2 digits {0:zz} -05
zzz Full timezone offset {0:zzz} -05:00
: Separator {0:hh:mm:ss} 10:43:20
/ Separator {0:dd/MM/yyyy} 10/12/2002
Enumerations
Specifier Type
g Default (Flag names if available, otherwise decimal)
f Flags always
d Integer always
x Eight digit hex.
Some Useful Examples
String.Format(”{0:$#,##0.00;($#,##0.00);Zero}”, value);

This will output “$1,240.00″ if passed 1243.50. It will output the same format but in parentheses if the number is negative, and will output the string “Zero” if the number is zero.

String.Format(”{0:(###) ###-####}”, 8005551212);

This will output “(800) 555-1212″.

Convert Numbers with 2 decimal places in GridView Field

If you are using template field in GridView
use
Text='<%# Bind("Project_cost", "{0:N}") %>'
in itemTemplate of that field
it will automatically convert your number field with 2 decimal places

If you are using boundField and wants to doing same
use
DataFormatString="{0:N}"

in that boundField it will automatically convert your number field with 2 decimal places

Keep visiting for more Information
sites.google.com/site/santo01org/

Best of luck

Convert Numbers with 2 decimal places in gridview Template field

Note:

if you are using template field in gridview and wants to convert number field in decimal places(2 and more places) just use Text='<%# Bind("Project_cost", "{0:N}") %>' in itemTemplate field.
It will convert your field values with 2 decimal places
like : your number is -44
after converting - 44.00


Same thing if you want to do with bound field use DataFormatString="{0:N}" in the required field

Keep visiting for more Information
sites.google.com/site/santo01org/
Best of luck

Usage of doPostBack in a real environment

input type="text" name="t1" id="Tx1" onserverchange="Text1_ServerChange" runat="server" onblur="enablePostBack();"

input type="text" name="t2" id="Tx2" runat="server"

//script for postbacking
script language="javascript"
function enablePostBack()
{
//T1 is the first argument(name of our control) I mentioned earlier and give the
// value of second argument as "" that's all
__doPostBack("Tx1","");
}
/script

//c# functon

protected void Text1_ServerChange(object sender, EventArgs e)
{
Tx2.Value = (Int16.Parse(Tx1.Value) * 45).ToString();
}