SQL SERVER SELECT USING PAGING DATA

begin
DECLARE @intPage INT =4
DECLARE  @intPageSize INT =20

DECLARE @intStartRow int;
DECLARE @intEndRow int;
SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;  
WITH blogs AS
    (SELECT USER_FULL_NAME,
     ROW_NUMBER() OVER(ORDER BY USER_ID DESC) as intRow,
     COUNT(USER_ID) OVER() AS intTotalHits
     FROM USERS)
SELECT USER_FULL_NAME, intTotalHits FROM blogs
WHERE intRow BETWEEN @intStartRow AND @intEndRow
end

convert dates gregorian to Umalqura and opposite sample funciton

the function take date with format yyyy-MM-dd and language ar or en
if passed date ar date so gregorian date string will returns
else umalqura date will returns.

using System.Globalization;

  public static string ServerSideMethod(string date, string lang)
    {
        string result = string.Empty;
        if (date.Length > 0)
        {
            UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
            CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
            GregorianCalendar gregCal = new GregorianCalendar();
            CultureInfo gregculture = new CultureInfo("en-US");
            if (lang == "ar")
            {
                DateTime tempDate = DateTime.ParseExact(date, "yyyy-MM-dd", umAlQuraCulture.DateTimeFormat, DateTimeStyles.AllowWhiteSpaces);
                result = tempDate.ToString("yyyy/MM/dd", gregculture.DateTimeFormat);
            }
            else
            {
                DateTime tempDate = DateTime.ParseExact(date, "yyyy-MM-dd", gregculture.DateTimeFormat, DateTimeStyles.AllowWhiteSpaces);
                result = tempDate.ToString("yyyy/MM/dd", umAlQuraCulture.DateTimeFormat);
            }
        }
        return result;
    }

Convert Gregorian Date to UmAlQura Date using .Net CLR function with SQL Server

http://code.msdn.microsoft.com/windowsdesktop/Convert-Gregorian-Date-to-89e35a4d

calling sql server stored dynamic parameters search using null values for datetime example

the following example idea is to create search form using c# used stored procedure dynamic parameters according to user input search Criteria.

the idea behind the example :
1- create Dynamic sqlParameter List to pass to stored procedure with optional Parameters.
2- Pass From and To Dates Dynamically to the Procedure so if user enter from date the search will handle begining date and no end date for query. and so on.
3- Handle automatic empty values to null parameters to pass query check for form fields.

SELECT ORACLE VARCHAR2 FILTER DIGITS NUMERIC NUMBER VALUES

if you have column in your table contains varchar2 values and need to check for digit values
or set constraint to enforce inserted data to be integer digits not special characters or alphabetic characters you can use this sample query to create your constraint.

SELECT COUNT(*) FROM yourschema.yourtable
WHERE
REGEXP_LIKE(columnName,'[[:digit:]]')


-----------------------------------------------------------------------------------------
to check Digit with specific length you can use this sample
ex : get data with 9 characters digit values

SELECT * FROM xx
WHERE
REGEXP_LIKE(columnName, '^[[:digit:]]{9}$')

-------------------------------------------------------------------------------------
to check for digits and characters with specific length you can use this template
ex: the query will select data contains 4 alpha chars and 3 digits.
SELECT * FROM xx
WHERE

REGEXP_LIKE(SEGIL_NO, '[[:alpha:]]{4}[[:digit:]]{3}')

[[:alpha:]]{4}[[:digit:]]{3}

oracle stored procedure select template return cursor with optional Parameters

Description :
i need to create Stored Procedure select from table with optional parameters to filter and return cursor.
then receive cursor using vb.net function and mapping data to data table to check data existence.

Stored Procedure Template
Stored Specification (with Package Declaration)

PACKAGE     USER_COMPANY IS
 TYPE GET_CO_INFO_CUR IS REF CURSOR ;
PROCEDURE   GET_COMPANY_DATE  -------------                            
                       (VGENERAL_ON    IN    NUMBER  DEFAULT 0       ,
                        VSEGIL_NO      IN    VARCHAR2 DEFAULT NULL    ,                                            
                        VGET_DATE      OUT   GET_DATE,
                        VEXCEPTION  OUT NUMBER);
END;  -- end Package Declaration

---------------------------------------------------------
Stored Body(with Package Body)

PACKAGE BODY   USER_COMPANY IS
PROCEDURE   GET_COMPANY_DATE  --------------                                            
                       (VGENERAL_ON    IN    NUMBER  DEFAULT 0       ,
                        VSEGIL_NO      IN    VARCHAR2 DEFAULT NULL    ,                     
                        VGET_DATE      OUT   GET_DATE,
                        VEXCEPTION  OUT NUMBER)  IS
BEGIN
     OPEN VGET_DATE  FOR          
      SELECT  GENERAL_ON, SEGIL_NO, EMAIL, PASS_WORD, MOBIL_NO, PHONE_NO, MNG_MOBIL_NO,USER_TYPE
      FROM    TND.TND_COMPANY_REGISTER 
      WHERE  (VGENERAL_ON = 0 OR GENERAL_ON = VGENERAL_ON)
      AND    (VSEGIL_NO   IS NULL OR  SEGIL_NO   = VSEGIL_NO );
EXCEPTION WHEN OTHERS  THEN
          VEXCEPTION := SQLCODE;      
    
END  GET_COMPANY_DATE;
END; --end Package Body
--------------------------------------------------------------------------------
Notes : if you passed two optional parameters so the stored procedure query will filter using two values
else if the number value is 0 or varchar2 value is null or empty string the query execution will ignore it in filter so you can use this template for search optionally for your input parameters
the other benefit is if you pass one parameter only for the procedure the execution will succeed because of default value binding option.

-----------------------------------------------------------------------
vb.net function to get cursor data

note : the template code using Oracle.DataAccess.Client library.

 Public Function Get_Company_Data(ByVal general_No As String, ByVal sejelno As String) As DataTable
        Dim mydt As New DataTable()
        Try
            Dim OConn As New OracleConnection
            OConn.ConnectionString = connect.Tender
            Dim cmd As New OracleCommand
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "SchemaName.PackageName.ProcedureName"
            cmd.Connection = OConn
            cmd.BindByName = True
            Dim O_par_1, O_par_2, O_par_3, O_par_4 As OracleParameter
            O_par_1 = cmd.Parameters.Add("VGENERAL_ON", OracleDbType.Double, 22)
            O_par_2 = cmd.Parameters.Add("VSEGIL_NO", OracleDbType.Varchar2, 20)
            O_par_3 = cmd.Parameters.Add("VGET_DATE", OracleDbType.RefCursor)
            O_par_4 = cmd.Parameters.Add("VEXCEPTION", OracleDbType.Double, 20)
            O_par_1.Direction = Data.ParameterDirection.Input
            O_par_2.Direction = Data.ParameterDirection.Input
            O_par_3.Direction = Data.ParameterDirection.Output
            O_par_4.Direction = Data.ParameterDirection.Output

            O_par_1.Value = general_No
            O_par_2.Value = sejelno


            If OConn.State = ConnectionState.Closed Then
                OConn.Open()
            End If
            Dim adp As New OracleDataAdapter(cmd)

            adp.Fill(mydt)

            cmd.Connection.Close()
            OConn.Close()
            If mydt.Rows.Count > 0 Then
                Return mydt
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Return mydt
        End Try
    End Function

jquery update panel ajax problem solve , updatepanel may need to register scripts outside body also solve the update panel script not working problem

if you fire jquery event in document.ready function and you use update panel you will face problem
after postback inside update panel the jquery function not loaded

to Solve this Problem follow this scenario :

<script type="text/javascript">

        $(document).ready(function () {
         //put you code here to fire on the first.
        });
        function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {

            //put your code here to fire after postback event .
            }
        }
 
    </script>


-------------------------------------------------------------------------------------------------------------------
i face problem with bootstrap scirpts with update panel to be run.
first time page visited the script exists on head tag working but after any asynchronous request inside update panel stop running scripts.
so i solve this problem by set master content outside form tag and register the script inside , this solve the problem>