Int, bigint, smallint, and tinyint SQL Server

SQL Server > Data Types > Int, bigint, smallint, and tinyint

These type are exact number data types and use integer data.
The bigint data type is used when integer values exceed the range.

bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
int	-2,147,483,648 to 2,147,483,647
smallint	-32,768 to 32,767
tinyint	0 to 255

Example:

Use int type for primary key in a table

CREATE TABLE [dbo].[Table] (        [Id] INT NOT NULL PRIMARY KEY )

Decimal and numeric types SQL Server example

SQL Server > Data Types > decimal and numeric

Numeric is functionally equivalent to decimal.
They have fixed precision and scale.

precision: maximum total number of decimal digits (for both left and right)

scale: number of decimal digits that will be stored to the right of the decimal point.
Example:

declare @num numeric(20,5)
set @num = 1333.2
print @num

declare @num1 numeric(20,0)
set @num1 = 1333.2
print @num1

Result:
1333.20000
1333 

RadGrid for ASP.NET AJAX

Telerik > RadGrid

RadGrid for ASP.NET AJAX is a control for data, paging, sorting, filtering and data editing to grouping and displaying hierarchical data.

• GridHyperlinkColumn
• GridBoundColumn

RadPivotGrid ASP.NET example

Telerik > ASP.NET > RadPivotGrid

RadPivotGrid is a control used to aggregate records in a concise tabular format.

Example:

<telerik:RadPivotGrid ID="RadPivotGrid1" runat="server" DataSourceID="LinqDataSource1" AllowPaging="true" AllowFiltering="false" ShowFilterHeaderZone="false">

  <ClientSettings Scrolling-AllowVerticalScroll="true">
  </ClientSettings>
  <Fields>
         <telerik:PivotGridColumnField DataField="City">
     </telerik:PivotGridColumnField>
      <telerik:PivotGridColumnField DataField="Month">
      </telerik:PivotGridColumnField>
       <telerik:PivotGridRowField DataField="ProductName">
       </telerik:PivotGridRowField>
      <telerik:PivotGridAggregateField DataField="Quantity" Aggregate="Sum">
    </telerik:PivotGridAggregateField>
    </Fields>
</telerik:RadPivotGrid>


Creates directory C# example

C# > Files > Directory > CreateDirectory

Creates directory  in the specified path.

Example:

 

string dir = @"c:\dir1";

try
{
            if (!Directory.Exists(dir))
      {
              DirectoryInfo di = Directory.CreateDirectory(dir);
      }
}
catch (Exception ex)
{
       MessageBox.Show(ex.ToString());
}
finally {}

 

Directory class C#

C# > Files > Directory






Directory class offers methods for operations like move, copy, rename, create, and delete directories.

• CreateDirectory
• GetAccessControl 
• GetFiles
• GetDirectories
• Delete

Check User Has Write Permission on Folder C#

C# > Files > Directory > GetAccessControl

GetAccessControl: returns the Windows access control list (ACL) for a directory
GetAccessRules: gets a collection of the access rules associated with the specified security identifier

Example: check write permission on folder

public static bool HasWritePermission(string dir)

        {
            bool Allow = false;
            bool Deny = false;
            DirectorySecurity  acl = null;
            try
            {
                acl = Directory.GetAccessControl(dir);
            }
            catch (System.IO.DirectoryNotFoundException)
            {
                throw new Exception("DirectoryNotFoundException");
            }
            if (acl == null)
                return false;
            AuthorizationRuleCollection  arc = acl.GetAccessRules(true, true, typeof(System.Security.Principal.SecurityIdentifier));
            if (arc == null)
                return false;
            foreach (FileSystemAccessRule rule in arc)
            {
                if ((FileSystemRights.Write & rule.FileSystemRights) != FileSystemRights.Write)
                    continue;
                if (rule.AccessControlType == AccessControlType.Allow)
                    Allow = true;
                else if (rule.AccessControlType == AccessControlType.Deny)
                    Deny = true;
            }
            return Allow && !Deny;
        }

SQL Server Data Types

SQL Server > Data Types

A data type is an attribute that specifies the type of data that the object can hold.

Exact Numerics

• numeric, decimal
• int, bigint, smallint, tinyint
• bit
• money, smallmoney
  Approximate Numerics
  float

  real

  Date and Time

  date	datetimeoffset
  datetime2	smalldatetime
  datetime	time

  Character Strings

  char	varchar
  text

  Unicode Character Strings

  nchar	nvarchar
  ntext

  Binary Strings

  binary	varbinary
  image

  Other Data Types

  cursor	timestamp
  hierarchyid	uniqueidentifier
  sql_variant	xml
  table	Spatial Types

  rowversion

XML exist XQuery SQL Server example

SQL Server > XML > Methods > exist()

Syntax

          exist (XQuery)

Returns

• 1 : XQuery expression in a query returns a nonempty result
• 0 : XQuery expression in a query returns an empty result.
• NULL : if target NULL.

Example: Find specific node in XML

DECLARE @data  XML

.... you xml 

SELECT @data.exist('(//item1)') 'Item1 exists', @data.exist('(//item3)') 'Item3 exists'

Results:

Add computed column SQL Server

SQL Server > DDL > Alter > Add computed column

You can add computed column with AS keyword followed by your computed formula.

Note: the computed column cannot be updated!

CREATE TABLE #tmp

(

     [hours] float,

     [rate]  float

)

ALTER TABLE #tmp ADD salary AS ([hours] * [rate])

INSERT INTO #tmp([hours], [rate])

SELECT 160, 10

UNION

SELECT 180, 12

SELECT * FROM #tmp

--The column "salary" cannot be modified because it is either a computed column or is the result of a UNION operator.

--update  #tmp

--set salary = salary *1.14

drop table #tmp

SQL Server Catalog Views

SQL Server > Catalog Views

Catalog views return information that is used by the SQL Server Database Engine.

• It is recommend that you query the catalog views instead of system tables
• It is recommend using SELECT * FROM sys.catalog_view_name because the number of columns returned might change

List of catalog views:

• Database mail
• sys.dataspaces
• sys.schemas
• sys.columns

Check the Status of E-Mail Messages SQL Server

SQL Server > Catalog Views > Database mail > sysmail_faileditems

In this SQL example will find information about any emails could not send successfully.


SELECT fi.subject, fi.last_mod_date,lg.description
FROM
   dbo.sysmail_faileditems as fi
INNER JOIN dbo.sysmail_event_log AS lg
ON fi.mailitem_id = lg.mailitem_id

Duration between two dates in SQL Server example

SQL Server > Built-In Functions > DATEDIFF

Returns the number of date and time boundaries crossed between two specified dates.

Example:

Duration between two dates in SQL Server example

DECLARE 

  @date1 DATETIME
,@date2   DATETIME

SELECT
  @date1 = getdate(),
   @date2  = dateadd(day,1,getdate()),
  @date2  = dateadd(minute,5,@date2)

SELECT
  @date1,
  @date2,
  DATEDIFF(hh, @date1, @date2) as Hours,  
  DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @date1, @date2),@date1),@date2) as Minutes

Result: