Posts

SQL Views - Variables

CREATE VIEW VW_X AS WITH MyVars (AnoInit, AnoEnd)   AS ( SELECT   2010 AS 'AnoInit',   (SELECT TOP 1 AnoLetivo FROM TB_Anos) AS 'AnoEnd'   ) SELECT               AnoX,               (SELECT AnoInit  FROM MyVars) as AnoInit,              (SELECT AnoEnd  FROM MyVars) as AnoEnd  FROM TABLE GO
Post a Comment
Read more

Download file from input type file javascript

        <script>             function downloadFile() {                 if (!window.File || !window.FileReader || !window.FileList || !window.Blob) {                     alert('The File APIs are not fully supported in this browser.');                     return;                 }                 input = document.getElementById('fileinput');                 if (!input) {                     alert("Um, couldn't find the fileinput element.");                 }                 else if (!input.files) {           ...
Post a Comment
Read more

POST an array of objects with $.ajax to C# WebMethod

//Create JS method invoking your C# WebMethod (example: getNames)     function getFstNames() {         var myObj = [             { 'fstName': 'name 1', 'lastName':'last name 1', 'age': 32 }           , { 'fstName': 'name 2', 'lastName':'last name 1', 'age': 33 }         ];         var postData = JSON.stringify({ lst: myObj });         console.log(postData);         $.ajax({             type: "POST",             url: "http://yourUrl.aspx/getNames",             data: postData,             contentType: "application/json; charset=utf-8",             dataType: "json",             success: function (response) {   ...
Post a Comment
Read more

Work with array of objects in browser local storage

Add methods to work with objects in local storage     $(document).ready(function () {         Storage.prototype.setObj = function (key, obj) {             return this.setItem(key, JSON.stringify(obj))         }         Storage.prototype.getObj = function (key) {             return JSON.parse(this.getItem(key))         }         var myObj = [];         localStorage.setObj('myObj', myObj);     }); Save item in local storage     function SaveItem() {         var myObj = localStorage.getObj('myObj');         var objNome = $('#txtFstName').val();         var objApelido = $('#txtLastName').val();                 var obj = { 'fstName': objNome, 'lastName': objApelid...
Post a Comment
Read more

sql - date queries

--Get month names with month numbers ; WITH months ( MonthNumber ) AS (     SELECT 0     UNION ALL     SELECT MonthNumber + 1     FROM months     WHERE MonthNumber < 12 ) SELECT DATENAME ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) AS [MonthName] , Datepart ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) AS MonthNumber FROM months ORDER BY Datepart ( MONTH , DATEADD ( MONTH ,- MonthNumber , GETDATE ())) ; --Get name of day select DATENAME ( WEEKDAY , GETDATE ()) AS TodayIs --Get first date of current month SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( DAY ,-( DAY ( GETDATE ()))+ 1 , GETDATE ()), 105 ) FirstDate ; --Get last date of current month SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( DAY ,-( DAY ( GETDATE ())), DATEADD ( MONTH , 1 , GETDATE ())), 105 ) LastDate ; --Get first date of previous month select DATEADD (...
Post a Comment
Read more

sql - simple queries

--Create table CREATE TABLE TableName (    Id INT   IDENTITY ( 1 , 1 ) PRIMARY KEY ,    Name Nvarchar ( 500 ),    Age INT ) --Create View CREATE VIEW view_name AS SELECT Id , Name , Age FROM TableName --Create Stored procedure CREATE PROCEDURE getInfoFromTable AS SELECT * FROM TableName --Add columns to existing table ALTER TABLE { TABLENAME } ADD { COLUMNNAME } { TYPE } --Remove column from a table ALTER TABLE TableName DROP COLUMN Email --Swap values of 2 columns UPDATE TableName SET Column1 = Column2 , Column2 = Column1 --Get Id of latest inserted record INSERT INTO TableName ( NAME , Email , Age ) VALUES ( 'Test User' , 'test@gmail.com' , 30 ) SELECT SCOPE_IDENTITY () --Delete duplicate records DELETE FROM TableName WHERE ID NOT IN ( SELECT MAX ( ID ) FROM TableName GROUP BY DuplicateColumn ) --Replace a string in a SQL Server ...
Post a Comment
Read more

sql - advanced queries

--Get all column names from table select COLUMN_NAME from INFORMATION_SCHEMA . COLUMNS where TABLE_NAME = 'TableName' ; --Get column names with data type select COLUMN_NAME , DATA_TYPE from INFORMATION_SCHEMA . COLUMNS where TABLE_NAME = 'TableName' ; --Search column name from database SELECT c . name AS ColName , t . name AS TableName FROM sys . columns c JOIN sys . tables t ON c . object_id = t . object_id WHERE c . name LIKE '%columnNameToSearch%'     --Get all user created tables SELECT NAME FROM sys . objects WHERE TYPE = 'U' --Get all view names SELECT * FROM sys . views --Get all stored procedure names SELECT * FROM sys . procedures --Counting rows for all tables at once SELECT [TableName] = so . name ,     [RowCount] = MAX ( si . rows ) FROM     sysobjects so ,     sysindexes si WHERE     so . xtype = '...
Post a Comment
Read more