Cursor SQL Server

SQL Server > Data Types > Cursor

SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

Permissions default to any user that has SELECT permissions;

Syntax:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Example:

create table #temp
(id int)
declare @p int = 0
while @p < 10
begin
       insert into #temp
       values(@p)
       set @p = @p + 1
end

select * from #temp -- return all rows

DECLARE @id int

DECLARE c CURSOR FOR
select
   id
from
  #temp
OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
   print @id -- scroll row by row
    FETCH NEXT FROM c INTO @id
END
CLOSE c;
DEALLOCATE c;
drop table #temp

Find Last Day of Previous Month SQL Server

SQL Server > Built-in Functions > DATEADD > Find Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Google’s Romanian Domain Gets Taken Down By Algerian Hacker MCA-CRB

That’s not an empty threat, it seems. MCA-DRB, according to Zone-h’s registry of hacked sites, has been responsible for 5,530 site hacks and defacements to date, with many of them appearing to cover government and public services sites from countries across Asia, Africa, Europe, Australia and the Americas

By MCA-CRB

Algerian Hacker




S thanks = Mr-AdeL & i-Hmx & Lagripe-Dz All Members Sec

To Be Continued ....

Handle events for dynamic run-time controls - VB.NET

VB.NET > Statements > AddHandler > Add event to dynamic control

Use AddHandler and AddressOf to add event to dynamic control

Example

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Text
Imports System.Windows.Forms
Imports System.Drawing.Drawing2D
Imports System

Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  Dim btn = New Button() //add dynamic control to form
  btn.Text = "Click here"
  btn.Size = New System.Drawing.Size(100, 100)
  btn.Location = New System.Drawing.Point(50, 50)
  AddHandler btn.Click, AddressOf Button1_Click  //add event to control
  Me.Controls.Add(btn)
End Sub

Protected Sub Button1_Click(sender As System.Object, e As System.EventArgs)
  Dim btn As Button = sender
  MsgBox(btn.Text)
End Sub

End Class

readonly c#

C# > Modifiers > readonly

Readonly prevents a field to be changed.

When a field declaration includes a readonly modifier, assignments to the field introduced by the declaration can only occur as part of the declaration or in a constructor in the same class.

Readonly fields can be initialized at runtime, unlike const.

Example 1:
            public readonly int x = 10;

Example 2:

public partial class Form1 : Form
    {
        public class Stack
        {
            readonly int m_Size;
            public Stack()
                : this(20)
            { }
            public Stack(int size)
            {
                m_Size = size;
            }
        }
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            Stack st = new Stack();
        }
    }

Math.Round

C#  > System > Math class > Round

Rounds a double-precision floating-point value to a specified number of fractional digits.

Example:

using System;

public class Round
{
   public static void Main()
   {
      double value = 4.254;
      Console.WriteLine("{0} => {1}", value, Math.Round(value, 0));
   }
}
// Result
// 4.254 --> 4

sysobjects table SQL Server

SQL Server > System Tables > sysobjects




sysobjects table SQL Server

Contains one row for each database object (constraint, trigger , stored procedure, and so on).

Important columns:

name	sysname	Object name.
Id	int	Object identification number.
uid	smallint	User ID of owner object.
type	char(2)	Object type: C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint FN = Scalar function IF = Inline table-function K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored procedure R = Rule RF = Replication filter stored procedure S = System table TF = Table function TR = Trigger U = User table V = View X = Extended stored procedure

Example:

select name,id,uid, type from sysobjects where name ='trg_asset_after_update'

name                             id                  uid type
trg_asset_after_update 1719155553 1    TR

DROP TRIGGER SQL Server

SQL Server > DDL > DROP TRIGGER          

Removes one or more triggers from the current database.

• When a table is dropped, all associated triggers are also dropped. 
• When a trigger is dropped, information about the trigger is removed from the sysobjects and syscomments system tables.

Example:

drop trigger trg_asset_after_update

 

ISNULL SQL Server

SQL Server > Built-In Functions > ISNULL



Replaces NULL with the specified replacement value.

Example

SELECT
 isnull(last_name,'N/A') last_name
FROM
 person

Disable Sorting for RadGrid Column, ASP net

Telerik > RadGrid > GridBoundColumn > AllowSorting

Gets or sets a value indicating whether the sorting feature is enabled.

Example

Disable Sorting for RadGrid Column

GridColumn col = Grid.MasterTableView.Columns[4];
GridBoundColumn colB = (GridBoundColumn)col;
colB.AllowSorting = false;

HTTP Status Codes

HTTP_STATUS_CONTINUE

100

The request can be continued.

HTTP_STATUS_SWITCH_PROTOCOLS

101

The server has switched protocols in an upgrade header.

HTTP_STATUS_OK

200

The request completed successfully.

HTTP_STATUS_CREATED

201

The request has been fulfilled and resulted in the creation of a new resource.

HTTP_STATUS_ACCEPTED

202

The request has been accepted for processing, but the processing has not been completed.

HTTP_STATUS_PARTIAL

203

The returned meta information in the entity-header is not the definitive set available from the origin server.

HTTP_STATUS_NO_CONTENT

204

The server has fulfilled the request, but there is no new information to send back.

HTTP_STATUS_RESET_CONTENT

205

The request has been completed, and the client program should reset the document view that caused the request to be sent to allow the user to easily initiate another input action.

HTTP_STATUS_PARTIAL_CONTENT

206

The server has fulfilled the partial GET request for the resource.

HTTP_STATUS_AMBIGUOUS

300

The server couldn't decide what to return.

HTTP_STATUS_MOVED

301

The requested resource has been assigned to a new permanent URI (Uniform Resource Identifier), and any future references to this resource should be done using one of the returned URIs.

HTTP_STATUS_REDIRECT

302

The requested resource resides temporarily under a different URI (Uniform Resource Identifier).

HTTP_STATUS_REDIRECT_METHOD

303

The response to the request can be found under a different URI (Uniform Resource Identifier) and should be retrieved using a GET HTTP verb on that resource.

HTTP_STATUS_NOT_MODIFIED

304

The requested resource has not been modified.

HTTP_STATUS_USE_PROXY

305

The requested resource must be accessed through the proxy given by the location field.

HTTP_STATUS_REDIRECT_KEEP_VERB

307

The redirected request keeps the same HTTP verb. HTTP/1.1 behavior.

HTTP_STATUS_BAD_REQUEST

400

The request could not be processed by the server due to invalid syntax.

HTTP_STATUS_DENIED

401

The requested resource requires user authentication.

HTTP_STATUS_PAYMENT_REQ

402

Not currently implemented in the HTTP protocol.

HTTP_STATUS_FORBIDDEN

403

The server understood the request, but is refusing to fulfill it.

HTTP_STATUS_NOT_FOUND

404

The server has not found anything matching the requested URI (Uniform Resource Identifier).

HTTP_STATUS_BAD_METHOD

405

The HTTP verb used is not allowed.

HTTP_STATUS_NONE_ACCEPTABLE

406

No responses acceptable to the client were found.

HTTP_STATUS_PROXY_AUTH_REQ

407

Proxy authentication required.

HTTP_STATUS_REQUEST_TIMEOUT

408

The server timed out waiting for the request.

HTTP_STATUS_CONFLICT

409

The request could not be completed due to a conflict with the current state of the resource. The user should resubmit with more information.

HTTP_STATUS_GONE

410

The requested resource is no longer available at the server, and no forwarding address is known.

HTTP_STATUS_LENGTH_REQUIRED

411

The server refuses to accept the request without a defined content length.

HTTP_STATUS_PRECOND_FAILED

412

The precondition given in one or more of the request header fields evaluated to false when it was tested on the server.

HTTP_STATUS_REQUEST_TOO_LARGE

413

The server is refusing to process a request because the request entity is larger than the server is willing or able to process.

HTTP_STATUS_URI_TOO_LONG

414

The server is refusing to service the request because the request URI (Uniform Resource Identifier) is longer than the server is willing to interpret.

HTTP_STATUS_UNSUPPORTED_MEDIA

415

The server is refusing to service the request because the entity of the request is in a format not supported by the requested resource for the requested method.

HTTP_STATUS_RETRY_WITH

449

The request should be retried after doing the appropriate action.

HTTP_STATUS_SERVER_ERROR

500

The server encountered an unexpected condition that prevented it from fulfilling the request.

HTTP_STATUS_NOT_SUPPORTED

501

The server does not support the functionality required to fulfill the request.

HTTP_STATUS_BAD_GATEWAY

502

The server, while acting as a gateway or proxy, received an invalid response from the upstream server it accessed in attempting to fulfill the request.

HTTP_STATUS_SERVICE_UNAVAIL

503

The service is temporarily overloaded.

HTTP_STATUS_GATEWAY_TIMEOUT

504

The request was timed out waiting for a gateway.

HTTP_STATUS_VERSION_NOT_SUP

505

The server does not support, or refuses to support, the HTTP protocol version that was used in the request message.

Session Timeout asp.net web.config

ASP.NET > Session > Timeout

The Timeout property specifies the time-out period assigned to the Session object for the application, in minutes. If the user does not refresh or request a page within the time-out period, the session ends.
The default is 10 minutes.
Should not be lower than 4 minutes an higher than 20 minutes.

<sessionstate timeout="30">

Disable Back Button Interrnet Browser Asp .net

ASP.NET > Disable Back Button IE

HttpCacheability.NoCache
Sets the Cache-Control: no-cache header

SetNoStore
Sets the Cache-Control: no-store HTTP header.

Example:

Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Cache.SetNoStore();

NEWID SQL Server

SQL Server > Built-In Functions > NEWID

Creates a unique value of type uniqueidentifier.

Example

DECLARE @myid uniqueidentifier
SET @myid = NEWID()

update
 user
set
 app_token = @myid
where
 id = 1

SET ANSI_NULLS SQL Server

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

SET ANSI_NULLS { ON | OFF }

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name.

How to add HyperLink Column to Telerik RadGrid Code Behind

Telerik > RadGrid > GridHyperLinkColumn

Each row in a Hyperlink column will contain a predefined hyperlink. This link is not the same for the whole column and can be defined for each row individually.

DataNavigateUrlFields: Gets or sets a string, representing a comma-separated enumeration of DataFields from the data source, which will form the url of the windwow/frame that the hyperlink will target.

DataNavigateUrlFormatString: Gets or sets a string, representing a comma-separated enumeration of DataFields from the data source, which will form the url of the windwow/frame that the hyperlink will target.

Example:
Add hyperlink column to RadGrid programmatically

RadGrid grd = new RadGrid();

GridHyperLinkColumn linkColumn = new GridHyperLinkColumn();
string[] fld = { "id" };
linkColumn.DataNavigateUrlFields = fld;
linkColumn.DataNavigateUrlFormatString = "Default.aspx?ID={0}";
linkColumn.HeaderText = "Id";
grd.Columns.Add(linkColumn);

DataTable.Select Method (String) c#

C# > Data   > DataTable > Select

DataTable Select method gets an array of all DataRow objects that match the filter criteria.
If the column on the filter contains a null value, it will not be part of the result.

Example:

private void FilterDataTable(string filter)
{
    DataTable table = DataSet1.Tables["Person"];
    DataRow[] foundRows;

    // Use the Select method to find all rows matching the filter. Sample: filter = "name = 'dan' "
    foundRows = table.Select(expression);
    for(int i = 0; i < foundRows.Length; i ++)
    {
        Console.WriteLine(foundRows[i][0] );
    }
}

How to exit for C#

C# > Statements > break

How to exit for C#? The answer is break.
The break statement terminates the closest enclosing loop or switch statement in which it appears. Control is passed to the statement that follows the terminated statement.

Example:

for (int j = 0; j < list.Count; j++)
{
    if (j==5)
       break;
}

Transaction SQL Server

SQL Server

BEGIN TRANSACTION

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

ROLLBACK TRANSACTION

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

COMMIT TRANSACTION

Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.






Example

begin transaction

update table1
set field1='a'

if @@ERROR <> 0
begin
 rollback transaction
 RAISERROR ('Error table1',16,1)
 return -1
end


update table2
set field3='a'

if @@ERROR <> 0
begin
rollback transaction
RAISERROR ('Error table2',16,1)
return -1
end

commit transaction

Max upload file size in ASP.NET

ASP.NET > MaxRequestLength

The MaxRequestLength property specifies the limit for the buffering threshold of the input stream. For example, this limit can be used to prevent denial of service attacks that are caused by users who post large files to the server.

The default value is 4096 KB (4 MB).

Example: Set max size up to 64 MB in web.config

<httpRuntime
maxRequestLength="65536"
/>

IndexOf c#

C# > String > IndexOf

Reports the zero-based index of the first occurrence of the specified string in another string.

Index numbering starts from zero.

The zero-based index position of value if that string is found, or -1 if it is not.

This method performs a word (case-sensitive and culture-sensitive) search using the current culture. The search begins at the first character position of this instance and continues until the last character position.

Example:

string ast_name = valuePath;
int poz = ast_name.IndexOf("http");
if (poz >-1) // found "http"
   ast_name = ast_name.Substring(poz,ast_name.Length-poz);

Changing the size of Telerik RadComboBox DropDown

Telerik > RadComboBox > ComboBoxElement > DropDownWidth 


this.radComboBox1.ComboBoxElement.DropDownWidth = 200;
this.radComboBox1.ComboBoxElement.DropDownHeight = 600;

Browse all rows and values RadGridView - Win Forms

Telerik > RadGridView > Browse all rows and values

Example:

C# Code
            for (i = 0; i <= RadGridView1.Rows.Count; i++)
            {
                for (j = 0; j <= RadGridView1.Columns.Count; j++)
                {
                    dynamic di = RadGridView1.Rows(i).Cells(j).Value;
                }
            }
            //or
             foreach (GridViewRowInfo _row in RadGridView1.Rows)
            {
                foreach (GridViewColumn _column in RadGridView1.Columns)
                {
                    dynamic di = _row.Cells(_column.Name).Value;
                }
            }

VB.NET Code

For i = 0 To RadGridView1.Rows.Count
  For j = 0 To RadGridView1.Columns.Count
   Dim di = RadGridView1.Rows(i).Cells(j).Value
  Next
Next
'or
For Each _row As GridViewRowInfo In RadGridView1.Rows
  For Each _column As GridViewColumn In RadGridView1.Columns
    Dim di = _row.Cells(_column.Name).Value
  Next
Next

How to set time to RadTimePicker Programmatically (ASP.NET)

Telerik > RadTimePicker > Set time

StartTime represents the starting time of the clock
EndTime represents  the time of the last clock item

Example : Set time  to RadTimePicker  code behind

RadTimePicker1.SelectedDate = DateTime.ParseExact("12:00", RadTimePicker1.TimeView.TimeFormat, null); // set default time RadTimePicker1.TimeView.StartTime  = new TimeSpan(8, 60, 0); //start time RadTimePicker1.TimeView.EndTime =  new TimeSpan(17,60, 0); // end time

OLAP

Database > OLAP

Short for Online Analytical Processing, a category of software tools that provides analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides time series and trend analysis views. OLAP often is used in data mining.

The chief component of OLAP is the OLAP server, which sits between a client and a database management systems (DBMS). The OLAP server understands how data is organized in the database and has special functions for analyzing the data. There are OLAP servers available for nearly all the major database systems.

Microsoft SQL Server Analysis Services is part of Microsoft SQL Server, a database management system. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities.

Oracle Essbase is the industry-leading multi-dimensional online analytical processing (OLAP) server, providing a rich environment for effectively developing custom analytic and enterprise performance management applications. By leveraging its self-managed, rapid application development capabilities, business users can quickly model complex business scenarios. For example, line-of-business personnel can simply and rapidly develop and manage analytic applications that can forecast likely business performance levels and deliver "what-if" analyses for varying conditions. Oracle Essbase supports extremely fast query response times for vast numbers of users, large data sets, and complex business models.

Analysis Services tutorial. Creating OLAP cube. Introduction to data warehouse

WITH (SQL Server)

SQL Server > DML > WITH

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.






Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select
 PersonID, PersonName, PersonSalary
FROM
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 *
FROM
 Person_CTE
ORDER BY
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;

3. Get all children of parent