Sunday, November 28, 2010

C# - Get child properties

On my previous post, we talked about parsing child properties. We will use that piece of code to create an extension method for type Object and easily retrieve the child properties from any class.

For instance you have this parent class:
class Parent
{
      public String Lastname  { get; set; }
}
And you have this child class:

class Child : Parent
{
      public String Petname{ get; set; }
}


You can retrieve the child properties thru this:
class Parent
{
      public String Lastname  { get; set; }
    
     public void DoSomething()
     {
           PropertyInfo[] childProperties = this.GetChildProperties();
Object propertyValue = this.GetChildProperty("Petname");
     }
}
Here's how we define our extension method.

namespace CtrlAltDelete.Library.Helpers.Dynamic
{
    public static class ObjectExtensions
    {
         public static PropertyInfo[] GetChildProperties(this Object source)
        {
            return source.GetType().UnderlyingSystemType.GetProperties();
         }
         public static Object GetChildProperty(this Object source, String propertyName)
        {
            Type childType = source.GetType().UnderlyingSystemType;
            PropertyInfo property = childType .GetProperty(propertyName);
            if (property.CanRead)
                return property.GetValue(source, null);
            return null;
            }
    }
}

C# - How to parse child properties through the parent class

Most of the times, we wanted to have a parent class that will contain all usable functions of the inheriting child classes, and thus a possibility that we might need to access custom properties of the inheriting classes. Since different class types can inherit the parent class, we need to use Reflection in order to gain access to the custom properties.

For instance this is your parent class:
class Parent
{
      public String Lastname { get; set; }
}
And this is your inheriting class:
class Child : Parent
{
     public String Name { get;  set; }
}
You wanted to have a common function on the parent class that will retrieve all custom properties of the inheriting classes. Then you will have this code:
class Parent
{
     public String Lastname { get; set; }

    public void GetChildProperties()
   {
         List<string> childProperties = new List<string>();
         Type childType = this.GetType().UnderlyingSystemType;
         PropertyInfo[] customProperties =  childType.GetProperties();
         foreach(PropertyInfo property in customProperties )
         {
            childProperties.Add(property.Name + " => " + property.Value);
         }
   }
}

Saturday, November 27, 2010

MSSQL - How to execute UPDATE with JOIN

UPDATE Table1
SET Table1field1 = 'somevalue', Table1field2 = 'somevalue'
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t1.id = t2.ref_id
WHERE t1.Table1field1 = 'test' AND t2.Table2field1 = 'test'
    

Friday, November 19, 2010

C# - Retrieve Class name of child instance/executing child

Let's assume we have this parent class:

namespace MyClasses.Test
{
  class Parent
  {
    public Parent()
    {
    }
  }
}

Then we have this child class:

namespace MyClasses.Test
{
  class Child : Parent
  {
    public Child()
    {
    }
  }
}

From what I've tried, there are two ways on how to retrieve the class name, namespace and type of the executing child.

First let's try the long way. :)

using System.Diagnostics;

public Parent()
{
  StackTrace st = new StackTrace(true);
  MethodBase mb = st.GetFrame(1).GetMethod();
  Type type = mb.ReflectedType;
  String className = type.Name;
  String nameSpace = type.Namespace;
}

Or we could try the easy way. :)
public Parent()
{
    Type type = this.GetType().UnderlyingSystemType;
    String className = type.Name;
    String nameSpace = type.Namespace;
}

These will return:

className = "Child"
nameSpace =  "MyClasses.Test"

Thursday, November 4, 2010

C# - MVC.net Partial View thru Ajax

There are different ways to load Partial View thru Ajax. What I am presenting here are just those that I've known and tested.

To make a partial view available thru ajax calls, it must inherit the "ViewUserControl":

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<CAD.Models.SetupModel>" %>

On your controller, you can check if request is an ajax call and render the partial view:


public virtual ActionResult DetailsTable(SetupModel setup)
        {
            if (Request.IsAjaxRequest())
            {
                return PartialView("DetailsTable", setup);
            }
            else
            {
                return View(setup);
            }
        }

Suppose you have this div to display the ajax response:

<div id="otherForms">
</div>

Here are some samples how to render the partial view thru ajax:

A.

<%= Ajax.ActionLink("Show All", MVC.Setup.DetailsTable(), new AjaxOptions { UpdateTargetId = "otherForms" }) %>


B.

<%= Ajax.ActionLink("Show All", MVC.Setup.DetailsTable(), new AjaxOptions { UpdateTargetId = "otherForms", OnSuccess="refreshOtherForms" }) %>
function refreshOtherForms(response) {
            var html = response.get_data();
            var container = response.get_updateTarget();
            $(container).html(html);
            return false;
}

C.

<a href="#" onclick="refreshOtherForms();return false;">

function refreshOtherForms(response) {
            $('#otherForms').load('<%= Url.Action("DetailsTable", "Setup") %>');
}

D.

<a href="#" onclick="refreshOtherForms();return false;">
function refreshOtherForms(response) {
            $.ajax({
                url: '/Setup/DetailsTable',
                data: { variable1: value1 },
                dataType: 'html',
                success: function (data) {
                    $('#otherForms').html(data);
                }
            });
}

C# MVCContrib - Configuration/Setup

To use MVCContrib classes on your views, you have to edit your web.config:


<configuration>
  <system.web>
      <compilation debug="true" targetFramework="4.0">
          <assemblies>
             <add assembly="MvcContrib" />

          </assemblies>
      </compilation>



      <pages>
          <namespaces>
             <add namespace="MvcContrib.UI"/>
             <add namespace="MvcContrib.UI.Grid"/>
             <add namespace="MvcContrib.UI.Pager"/>
             <add namespace="MvcContrib.Pagination"/>
             <add namespace="MvcContrib.UI.Html"/>
             <add namespace="MvcContrib"/>
          </namespaces>
      </pages>
 </system.web>
</configuration>

Monday, October 18, 2010

Media Wiki - Upload file configuration

To enable uploading of files on media wiki, first enable the settings on PHP.INI

file_uploads = On

Then on your LocalSettings.php (under wiki site root folder), apply these changes:

Turn on the file upload:

$wgEnableUploads   = true

Set your upload directory. This is where your uploaded files will be saved:

$wgUploadDirectory = "c:/Sites/PHP/mediawiki-1.16.0/uploads";

Set your upload url. This is the url to use for downloading your files.

$wgUploadPath  = $wgScriptPath."/uploads";
For example: http://wiki.mysite.com/uploads/myfile.txt.

To modify the file types that can be uploaded:
$wgFileExtensions = array_merge($wgFileExtensions, array('doc', 'xls'));



To access the upload page, add this string on the url:

index.php?title=Special:Upload

For example:

http://wiki.mysite.com/index.php?title=Special:Upload


If you want some advanced configurations on file uploads such as setting upload size limit, file types, etc, you can find it on their media wiki's website.

Media Wiki - Edit navigation/menu

Add this string on your url: index.php?title=MediaWiki:Sidebar

For example:

http://wiki.mysite.com/index.php?title=MediaWiki:Sidebar

Apache - Add authentication on site

To enable login on your site, you need two files:

1. .htpasswd - can be saved anywhere as long as it's secure
2. .htaccess - must be on the root directory of the site

On your .htpasswd, define the username and password on this format:

username:password

On your .htaccess, have these lines:

AuthUserFile "C:/wamp/pwds/.htpasswd"
AuthName "Valid Users"
AuthType Basic
require valid-user

Sunday, October 3, 2010

C# get credit card type

Usage:

String cardType = CreditCardUtility.GetTypeName("4111 1111 1111 1111");

This will return "Visa"

Here is the full code (or you can just copy the lines of code that you need):


/** CreditCardUtility.cs ***/ 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using System.Collections.Specialized; namespace CreditCard
{
    public class CreditCardUtility
    {
        protected static CreditCardUtility _instance = new CreditCardUtility();
        protected CreditCard _card;
        public CreditCard Card
        {
            get
            {
                if (this._card == null)
                    this._card = new CreditCard();
                return this._card;
            }
            set
            {
                this._card = value;
            }
        }
        public static CreditCardUtility Instance
        {
            get
            {
                if(_instance == null)
                    _instance = new CreditCardUtility();
                return _instance;
            }
            set
            {
                _instance = value;
            }
        }

        private CreditCardUtility()
        {
        }

        static CreditCardUtility()
        {
        }

        public static String GetTypeName(String cardNumber)
        {
            return Instance.Card.GetCardType(cardNumber);   
        }
    }
} 
/** CreditCard.cs ***/  
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace CreditCard
{
    public class CreditCard
    {
        //http://www.regular-expressions.info/creditcard.html
        //# Visa: ^4[0-9]{12}(?:[0-9]{3})?$ All Visa card numbers start with a 4. New cards have 16 digits. Old cards have 13.
        //# MasterCard:  All MasterCard numbers start with the numbers 51 through 55. All have 16 digits.
        //# American Express: ^3[47][0-9]{13}$ American Express card numbers start with 34 or 37 and have 15 digits.
        //# Diners Club: ^3(?:0[0-5]|[68][0-9])[0-9]{11}$ Diners Club card numbers begin with 300 through 305, 36 or 38. All have 14 digits. There are Diners Club cards that begin with 5 and have 16 digits. These are a joint venture between Diners Club and MasterCard, and should be processed like a MasterCard.
        //# Discover: ^6(?:011|5[0-9]{2})[0-9]{12}$ Discover card numbers begin with 6011 or 65. All have 16 digits.
        //# JCB: ^(?:2131|1800|35\d{3})\d{11}$ JCB 

        public const String AMEXPattern = @"^3[47][0-9]{13}$";
        public const String MasterCardPattern = @"^5[1-5][0-9]{14}$";
        public const String VisaCardPattern = @"^4[0-9]{12}(?:[0-9]{3})?$";
        public const String DinersClubCardPattern = @"^3(?:0[0-5]|[68][0-9])[0-9]{11}$";
        public const String enRouteCardPattern = @"^(2014|2149)";
        public const String DiscoverCardPattern = @"^6(?:011|5[0-9]{2})[0-9]{12}$";
        public const String JCBCardPattern = @"^(?:2131|1800|35\d{3})\d{11}$";

        protected NameValueCollection _patterns;
        public NameValueCollection CardPatterns
        {
            get
            {
                if(this._patterns == null)
                {
                    this._patterns = new NameValueCollection();
                    this._patterns.Add("AMEX",AMEXPattern);
                    this._patterns.Add("MasterCard",MasterCardPattern);
                    this._patterns.Add("Visa",VisaCardPattern);
                    this._patterns.Add("DinersClub",DinersClubCardPattern);
                    this._patterns.Add("enRoute",enRouteCardPattern);
                    this._patterns.Add("Discover",DiscoverCardPattern);
                    this._patterns.Add("JCB",JCBCardPattern);

                }
                return this._patterns;
            }
            set
            {
                this._patterns = value;
            }
        }

        public CreditCard()
        {
        }

        public String GetCardType(String cardNumber)
        {
            
            String cardType = "Unknown";
            
            try
            {
                String cardNum = cardNumber.Replace(" ", "").Replace("-", "");
                Regex regex;
                foreach (String cardTypeName in this.CardPatterns.Keys)
                {
                    regex = new Regex(this.CardPatterns[cardTypeName]);
                    if (regex.IsMatch(cardNum))
                    {
                        cardType = cardTypeName;
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return cardType;

        }
    }
}

Friday, October 1, 2010

Root directory as subdirectory

One way to point your root directory to a subdirectory, you need these lines on your .htaccess file on the root directory:


Options +FollowSymLinks
RewriteEngine on
RewriteBase /
RewriteCond $1 !^httpdocs/
RewriteRule ^(.*)$ /httpdocs/$1 [L]

Thursday, September 30, 2010

List IIS application pool

Start Menu -> Run -> type cmd -> Press Enter key

type: iisapp pool

Press Enter

Tuesday, September 21, 2010

LinQ2SQL GUID

This example shows how to compare/filter GUID or UniqueIdentifier types  on Linq2SQL:

String guid = "5236DFEC-A4DB-40B5-B873-F2468697B282";
Guid filterGuid = new Guid(guid);

var matchDB = (from c in db.FormsSubmissions
                         where
                              c.GuidColumn.Equals(filterGuid)
                          select c).First();

Monday, September 20, 2010

MSSQL alternative to MySQL's GROUP_CONCAT

Here's a MSSQL equivalent of the group_concat()  from MySQL:
DECLARE @itemFields varchar(MAX)
SELECT  @itemFields = COALESCE( @itemFields + ',', '') + QUOTENAME([fi].[Name])     
FROM [forms_items] fi     
GROUP BY [fi].[Name]

MSSQL GROUP+

MSSQL GROUP+

MSSQL alternative to Distinct clause

Here's an alternative way of using DISTINCT clause. I've tested this several times versus the DISTINCT clause and yields to faster performance.

using DISTINCT clause:

SELECT DISTINCT([name]) FROM Table1

alternative:

SELECT [name] FROM Table1
GROUP BY [name]

See screenshot:

MSSQL alternative to Distinct clause

Here's an alternative way of using DISTINCT clause. I've tested this several times versus the DISTINCT clause and yields to faster performance.

using DISTINCT clause:

SELECT DISTINCT([name]) FROM Table1

alternative:

SELECT [name] FROM Table1
GROUP BY [name]

See screenshot:

Wednesday, September 15, 2010

C# Dynamically execute a function from string

/**
* from other post (http://dotnet-anythingabout.blogspot.com/2010/09/dynamically-create-instance-of-object.html)
*/
String typeName = "Yournamespace.yourclassname";
Object objectInstance = Activator.CreateInstance(Type.GetType(typeName));

object[] parameters = new object[2]; /** or new object[] {"test","markg"} **/
parameters [0] ="test";
parameters [1] ="markg";
string methodName = "Add";

objectInstance .GetType().InvokeMember(methodName, BindingFlags.InvokeMethod, null, objectInstance , parameters);

OR

Type[] paramTypes = new Type[2];
paramTypes[0] = Type.GetType("System.String");
paramTypes[1] = Type.GetType("System.String");

MethodInfo methodInfo = objectInstance.GetType().GetMethod(methodName, paramTypes);
methodInfo.Invoke(objectInstance ,parameters);

C# Dynamically create instance of object from string

String typeName = "Yournamespace.yourclassname";
Object obj = Activator.CreateInstance(Type.GetType(typeName));

Saturday, September 11, 2010

chicken adobo

Ingredients:

oil
garlic
onions
salt
black pepper powder
coriander powder (optional)
black pepper seeds
soy sauce (i suggest kikkoman)
vinegar
oyster sauce (optional)
worcestershire sauce (optional)
chicken meat

Steps:

1. heat some oil
2. add the garlic and after 3 seconds add the onions and stir
3. add the meat and stir from time to time until it's stale (or pale)
4. add some salt to taste
5. add pepper powder, coriander powder and stir
6. add some soy sauce and vinegar (the suggested ratio would be 3:1 depending how sour the vinegar is)
7. add some water and stir
8. repeat steps 4-5 if necessary. (note that it's better to make it a less salty or slightly lower your taste.. see step 11)
9. let it boil
10. add the black pepper seeds and stir
11. simmer for a few minutes until sauce becomes thick. Stir from time to time.
12. serve

cheesy spaghetti

Ingredients:

2 tbsp olive oil
450g cheese
1kg pasta
1kg ground beef
450g sliced hotdog (optional. adds flavor and color)
1kg spaghetti sauce (or your own choosing of sauce - sweet, sour, etc)
pepper
salt
tomato ketchup (optional)

Step A:

1. Boil water with oil and add enough salt to taste
2. Add the pasta and let it cook for approx. 10-12 minutes, depending on the packaging's suggestion
3. Drain the pasta

Step B:

1. Heat the oil
2. Add the ground beef and hotdogs and stir
3. Add some salt to taste
4. Add some pepper and stir
3. Let it heat for a few minutes and stir from time to time until the beef is cooked (repeat steps 3 and 4 if necessary)
4. Add the cheese and stir well until it's fully melted and mixed with the meat
5. Add the spaghetti sauce and stir well.
6. You can add some tomato ketchup depending on your taste.
7. Set the heat to the minimum and stir the mixture from time to time until it's cooked (do not overboil)
8. Slowly mix the sauce and pasta and let it heat for a few minutes. This will let the sauce sip into the pasta (or you can serve the pasta separately with the sauce)
9. Serve.

Thursday, September 9, 2010

chicken biryani

Ingredients

1. garlic
2. onions
3. tomatoes
4. coriander leaves
5. biryani masala
6. coconut milk
7. basmati rice
8. salt
9. olive oil
10. black pepper powder

Step A:

1. soak rice on water for 30 minutes
2. boil water and add some salt and oil (make sure the salt is enough to taste and there is enough water to cover the rice)
3. add the rice in the boiling water
4. watch carefully until the rice is 50%-75% cooked (don't let it dry)
5. drain the rice

Step B:

1. heat oil in a pan
2. add the garlic and after 3 seconds add the onions and stir
3. add the chicken and stir
3. add plenty of tomatoes
4. stir from time to time until tomatoes creates a paste and chicken is half cooked
5. add biryani powder
6. add pepper powder and stir
7. let it heat until you smell the biryani
8. add tiny amount of coconut milk and coriander leaves and stir
9. let it heat until meat is cooked

Step C:

1. heat a separate cooking pot
2. place small amount of chicken biryani (thin layer)
3. layer it with some rice. (just enough to cover the first layer)
4. repeat steps#2-3 until done.
5. cover the potand let it heat for a while until rice is cooked
6. serve

chicken curry

Ingredients:

1. sliced garlic
2. onions
3. salt
4. coconut milk
5. curry powder
6. black pepper powder
7. chili powder (optional)
8. chicken
9. basil leaves
10. coriander leaves

Steps:

1. heat oil in a pan
2. add garlic and after 3 seconds add the onions and stir
3. add the chicken
4. stir until the chicken until it becomes stale (or pale)
5. add the curry powder and coriander powder and stir
6. add the black pepper and chili powder (optional) and stir well
7. add some salt to taste
8. let it heat for a few moments until you smell the curry
9. add the coconut milk
10. add the coriander leaves and basil leaves then stir
11. let it boil until chicken is cooked or until sauce becomes thick
12. serve

stir-fry noodles

Ingredients:

1. minced garlic
2. minced ginger
3. onions
4. onion spring
5. mushroom
6. carrots
7. snow peas
8. cabbage
9. celery
10. green bell pepper
11. chicken
12. soy sauce
13. oyster sauce
14. worcestershire sauce
15. black pepper powder
16. salt

Steps:

1. Boil water and add some salt
2. Cook the noodles for three minutes or until it is "al dente"
3. Drain the water
4. On a separate pan/wok, heat oil
5. Add the garlic and after 3 seconds add the ginger
6. Add the onions
7. Add the chicken
8. Let the chicken tender and stir from time to time
9. Add the carrots, bell pepper, mushroom, snow peas and celery
10. Stir
11. Add the pepper powder, soy sauce, oyster sauce and the worcestershire sauce
12. Stir well
13. Heat for a few minutes until the vegetables are done
14. Mix the noodles and the cabbage
15. Stir well until noodles are full mixed with the sauce and cabbage is cooked
16. Add the onion spring and serve

Wednesday, September 1, 2010

C# Dynamically get and set values of a property from string


This sample code will let you retrieve the list of properties of an object and will also let you assign and get values dynamically given a property name as string.

Don't forget to include the System.Reflection namespace:

using System.Reflection;

MyClass myObject = new MyClass();
String objectValue;
String objectName;

//get properties
foreach(PropertyInfo info in myObject .GetType().GetProperties())
{
      objectName = info.Name;
      // get value
     if(info.CanRead)
         objectValue = info.GetValue(myObject,null);

     //set value
     objectValue = "Hello Pilipinas!";
     if(info.CanWrite)
       info.SetValue(myObject,objectValue,null);
}


Or you can access the property directly given it's name as string

PropertyInfo info = myObject.GetType().GetProperty("myPropertyName");

// get value
if(info.CanRead)
     objectValue = info.GetValue(myObject,null);


//set value
objectValue = "Hello Pilipinas!";
if(info.CanWrite)
    info.SetValue(myObject,objectValue,null);


Monday, August 30, 2010

Regular Expression Minimum Digits / Phone Number

This regular expression will check string that contains at least 7 number of digits:

(\D?\d\D?){7,}

For example:

123456 = false
12ad34g5a6 = false
1234567 = true
1m2m3m4m5m6m7 = true

This can also be used to validate or check phone numbers (at least 7 digits)

(([\(\-\+\s]?\d[\)\-\+\s]?){7,})\d?$

(+971) 372 - 6202
(+971) 3726202
(971) 3726202
971-372-6202
9713726202

Monday, June 28, 2010

PHP foreach loop

PHP Class Constructor

//parent class

class BaseModel
{
    public $table;
    public $db_host;
    public $db_usr;
    public $pw;
    public $db;
   
    public function __construct()
    {
        $this->db_host = "some value";
        $this->db_usr = "some value";
        $this->pw = "some value";
        $this->db = "some value";
    }
}

//child class
class ChildModel extends BaseModel
{
    public function __construct()
    {
        parent::__construct();          
       $this->table = "some value";
    }
}

Saturday, June 26, 2010

Wednesday, June 23, 2010

MSSQL SPLIT Function


CREATE FUNCTION [dbo].[SPLIT]
(
        @inputString NVARCHAR(max),
        @delimiter CHAR
)
RETURNS @result TABLE (token NVARCHAR(max))
AS
BEGIN

DECLARE @startposition INT
DECLARE @endposition INT
DECLARE @inputLength INT
DECLARE @lengthToParse INT

SET @startposition = 1
SET @endposition = 1
SET @inputLength = LEN(@inputString)

WHILE @startposition <= @inputLength 
    BEGIN 


        SELECT @endpositioncharindex(@delimiter,@inputString,@startposition)  


        IF @endposition <> 0
        BEGIN
              SET @endposition = @endposition - 1
        END
        ELSE
        BEGIN
              SET @endposition = @inputLength
       END

       SET @lengthToParse = @endposition - @startposition + 1
       INSERT INTO @result(tokenVALUES(substring(@inputString,@startposition,@lengthToParse))
       SET @startposition = @endposition + 2

END

RETURN

END

Sample usage:

SELECT * FROM SPLIT('abc.def.ghi','.')

Will return:
abc
def
ghi

 

MSSQL SPLIT Function


CREATE FUNCTION [dbo].[SPLIT]
(
        @inputString NVARCHAR(max),
        @delimiter CHAR
)
RETURNS @result TABLE (token NVARCHAR(max))
AS
BEGIN

DECLARE @startposition INT
DECLARE @endposition INT
DECLARE @inputLength INT
DECLARE @lengthToParse INT

SET @startposition = 1
SET @endposition = 1
SET @inputLength = LEN(@inputString)

WHILE @startposition <= @inputLength 
    BEGIN 


        SELECT @endpositioncharindex(@delimiter,@inputString,@startposition)  


        IF @endposition <> 0
        BEGIN
              SET @endposition = @endposition - 1
        END
        ELSE
        BEGIN
              SET @endposition = @inputLength
       END

       SET @lengthToParse = @endposition - @startposition + 1
       INSERT INTO @result(tokenVALUES(substring(@inputString,@startposition,@lengthToParse))
       SET @startposition = @endposition + 2

END

RETURN

END

Sample usage:

SELECT * FROM SPLIT('abc.def.ghi','.')

Will return:
abc
def
ghi

 

MSSQL LIKE Clause


SELECT * FROM myTable WHERE name LIKE '%abc%'
- This will return rows that contains the substring "abc"

SELECT * FROM myTable WHERE name LIKE '_abc'
- This will return rows that starts with any single character followed by the word "abc"

SELECT * FROM myTable WHERE name LIKE '[A-J]abc'
- This will return rows that starts with letter A to J followed by the word "abc"


SELECT * FROM myTable WHERE name LIKE '[^z]abc'
- This will return rows that starts with any single character except letter "z" followed by the word "abc"

SELECT * FROM myTable WHERE name LIKE '[abc]efg'
- This will return rows that starts with a or b or c followed by efg


SELECT * FROM myTable WHERE name LIKE '[ja-ju]'
 - This will return rows that starts from ja- to ju-


SELECT * FROM myTable WHERE name NOT LIKE '%[_]%'
SELECT * FROM myTable WHERE name LIKE '%[^_]%'
- This will return rows that does not contain the character "_" (underscore)

MSSQL LIKE Clause


SELECT * FROM myTable WHERE name LIKE '%abc%'
- This will return rows that contains the substring "abc"

SELECT * FROM myTable WHERE name LIKE '_abc'
- This will return rows that starts with any single character followed by the word "abc"

SELECT * FROM myTable WHERE name LIKE '[A-J]abc'
- This will return rows that starts with letter A to J followed by the word "abc"


SELECT * FROM myTable WHERE name LIKE '[^z]abc'
- This will return rows that starts with any single character except letter "z" followed by the word "abc"

SELECT * FROM myTable WHERE name LIKE '[abc]efg'
- This will return rows that starts with a or b or c followed by efg


SELECT * FROM myTable WHERE name LIKE '[ja-ju]'
 - This will return rows that starts from ja- to ju-


SELECT * FROM myTable WHERE name NOT LIKE '%[_]%'
SELECT * FROM myTable WHERE name LIKE '%[^_]%'
- This will return rows that does not contain the character "_" (underscore)

MSSQL Paging Query

Assuming:
@page = 1 (INT)
@itemsPerPage = 1000 (INT)


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY myColumn) as RowNumber,*
    FROM myTable
) as tempTable
WHERE
tempTable.RowNumber BETWEEN (((@page - 1) * @itemsPerPage) + 1) AND (@page * @itemsPerPage)


This query will return the first 1000 rows sorted by myColumn

MSSQL Paging Query

Assuming:
@page = 1 (INT)
@itemsPerPage = 1000 (INT)


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY myColumn) as RowNumber,*
    FROM myTable
) as tempTable
WHERE
tempTable.RowNumber BETWEEN (((@page - 1) * @itemsPerPage) + 1) AND (@page * @itemsPerPage)


This query will return the first 1000 rows sorted by myColumn