SQL Developer Cheat Sheet

To copy result with header:
Select all - Ctrl + A
Copy with header - Ctrl + Shift + C

To set the date time on date field:
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

Default boostrap hex colors

// Colors
$blue:    #007bff !default; // primary
$indigo:  #6610f2 !default;
$purple:  #6f42c1 !default;
$pink:    #e83e8c !default;
$red:     #dc3545 !default; // danger
$orange:  #fd7e14 !default;
$yellow:  #ffc107 !default; // warning
$green:   #28a745 !default; // success
$teal:    #20c997 !default;
$cyan:    #17a2b8 !default; // info

// Grays
$white:    #fff !default;
$gray-100: #f8f9fa !default; // light
$gray-200: #e9ecef !default;
$gray-300: #dee2e6 !default;
$gray-400: #ced4da !default;
$gray-500: #adb5bd !default;
$gray-600: #868e96 !default; // secondary
$gray-700: #495057 !default;
$gray-800: #343a40 !default; // dark
$gray-900: #212529 !default;
$black:    #000 !default;

Check account details on the domain

Open windows command prompt
type the following

net user [userid] /domain

It will show you the details of the account.

Angular radio button in ng-repeat

When creating radio button in ng-repeat, only the last row gets checked.
It could be because of the name.
You can use the property ng-attr-name with your data in ng-repeat.
Reference here

Telnet is not recognized as windows command

There are 2 things that you can do.
1) Install Telnet from the Windows feature
Go to Start -> Control Panel -> Programs -> Turn Windows feature on or off
Under feature section, find Telnet client ensure that it's ticked (if it's not).
Once (1) is installed, if you still encounter telnet is not recognized error, you can proceed with (2)

2) Go to Environment variables
Right click on my computer, select Properties, on the left side select Advanced System Settings.
Under Advanced tab, click Environment Variables, under System variables section, find the Variable = Path, ensure that C:\Windows\System32 is there. If it's not there, just append it.
Then click OK.

You should be able to run telnet commands now.

clone arrays in javascript

Reference: here

jquery to obtain the ID or to use the data.

To access data/id from html

in html form let say it's a button, we can put any fields as the data (by putting the tag data-<something>)

<input class="myclass" data-myvalue="myValue123" id="myID" type="button" />

To access the element using ID from the javascript:
$('#myID').click(function(e){
   var myvalue = $(this).data('myvalue');
});

To access the element using the class from javascript:
$('.myclass').click(function(e){
   var myvalue = $(this).data('myvalue');
   var myID = $(this).attr('id');
});

Entity framework 5 can't connect to Oracle DB in Windows 2012 server

Recently I did a project using Entity Framework 5 with Oracle DB.
Only Entity framework 5 is able to connect to Oracle, I'm using Oracle 12c so must use the ODAC 12.10.1.2 for coding.
When running in local, it's working fine.
However when deploying to the server, I encountered this error:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Reinstalling ODAC doesn't work.
All TNS was updated in tnsnames.ora.
However it seems EF can't locate the TNS in tnsnames.ora.

Changing the connection string to be same like the one in tnsnames.ora doesn't work.

metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string='Data Source=MYDB.WORLD;USER ID=XXX;PASSWORD=YYY;'

After enough searching here and there.. I found a temporary solution, that is to embed the TNS names in the connection string itself.

So my entity framework connection string is now like below:

metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string='Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = MYDBSERVER)(PORT = 1111))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = MYDB)));USER ID=XXX;PASSWORD=YYY;'

Reference : http://nullablecode.com/2013/10/ef-oracle-db-connection-problem-ora-12504/

System.BadImageFormatException: Could not load file or assembly 'xxx.dll'or one of its dependencies. An attempt was made to load a program with an incorrect format.

If you encounter below error when running Visual Studio .NET but compilation is successful.
System.BadImageFormatException: Could not load file or assembly 'xxx.dll' or one of its dependencies. An attempt was made to load a program with an incorrect format.

Resolution:
1) Ensure that all projects have the same .net framework version you can check this from each project properties.
2) Ensure that the target platform is Any CPU on solution properties and each project properties (for .NET it's under Compile section, for C# it's under Build section).

Reference: here

Windows 7 upgrade error - External table is not in the expected format.

Before the upgrade it's working fine, after upgrade, my user experienced that error:
External table is not in the expected format.

After debugging, found out that there's 2 files being processed, even though my user only puts 1 file into the upload folder.
Turned out to be the second file is Thumbs.db, usually used for thumbnails.
But I cannot see this file in the folder itself.
So what do we need to do?
Simple, just ask your user to go to Windows Explorer, go to Tools, Folder Options

On View tab, tick the first one, Always show icons, never thumbnails then click ok.
Go to that upload folder, remove any Thumbs.db if there's is any.
It should solve the problem.

Reference: here

Cleaning COM Objects when coding using Interop.Excel

Proper coding with releasing of the COM objects must be done when coding to create excel reports using Microsoft.Office.Interop.Excel in order to avoid memory leak.

Please refer to this example to automate excel with proper releasing of COM objects in VS2010.
In the example, Solution1.AutomateExcel demonstrates automating Microsoft Excel application by using Microsoft Excel Primary Interop Assembly (PIA) and explicitly assigning each COM accessor object to a new variable that you would explicitly call Marshal.FinalReleaseComObject to release it at the end. When you use this solution, it is important to avoid calls that tunnel into the object model because they will orphan Runtime Callable Wrapper (RCW) on the heap that you will not be able to access in order to call Marshal.ReleaseComObject. You need to be very careful. For example,

  Excel.Workbook oWB = oXL.Workbooks.Add(missing);

Calling oXL.Workbooks.Add creates an RCW for the Workbooks object. If you invoke these accessors via tunneling as this code does, the RCW for Workbooks is created on the GC heap, but the reference is created under the hood on the stack and are then discarded. As such, there is no way to call MarshalFinalReleaseComObject on this RCW. To get such kind of RCWs released, you would either need to force a garbage collection as soon as the calling function is off the stack (see Solution2.AutomateExcel), or you would need to explicitly assign each accessor object to a variable and free it.

  Excel.Workbooks oWBs = oXL.Workbooks;
  Excel.Workbook oWB = oWBs.Add(missing);

In the example, Solution2.AutomateExcel demonstrates automating Microsoft Excel application by using Microsoft Excel Primary Interop Assembly (PIA) and forcing a garbage collection as soon as the automation function is off the stack (at which point the Runtime Callable Wrapper (RCW) objects are no longer rooted) to clean up RCWs and release COM objects.

public static void AutomateExcel()
        {
            AutomateExcelImpl();


            // Clean up the unmanaged Excel COM resources by forcing a garbage
            // collection as soon as the calling function is off the stack (at
            // which point these objects are no longer rooted).

            GC.Collect();
            GC.WaitForPendingFinalizers();
            // GC needs to be called twice in order to get the Finalizers called
            // - the first time in, it simply makes a list of what is to be
            // finalized, the second time in, it actually is finalizing. Only
            // then will the object do its automatic ReleaseComObject.
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

Read more on the Marshal.ReleaseCOMObjects is dangerous to be used here.

Sharepoint WS - List - Delete file

here is the code to delete a file.
Note that FileRef cannot use encoded URL.
For the GetDocID method can refer to this post.

 private static void DeleteFile(string FilePath)  
     {  
       try  
       {  
         using (ListWS.Lists listService = new ListWS.Lists())  
         {  
           listService.Url = ListURL;  
           listService.Credentials = System.Net.CredentialCache.DefaultCredentials;  
           listService.Credentials = new System.Net.NetworkCredential(username, password);  
           //Get the file id  
           string fileName = FilePath.Substring(FilePath.LastIndexOf("/") + 1);  
           string DocID = GetDocID(fileName);  
           //get guid  
           string lListName = "EDMS";  
           XmlNode lSharePointListName = listService.GetList(lListName);  
           string lListID = lSharePointListName.Attributes["ID"].Value;   
           XmlDocument doc = new XmlDocument();  
           //Perform deletion  
           string xmlCommand;  
           doc = new XmlDocument();  
           //ID is the ID field value of MyFolder element  
           xmlCommand = "<Method ID='1' Cmd='Delete'><Field Name='ID'>" + DocID + "</Field><Field Name='FileRef'>" +   
             FilePath.Replace("%20"," ") + "</Field></Method>";  
           XmlElement ele = doc.CreateElement("Batch");  
           ele.SetAttribute("OnError", "Continue");  
           ele.SetAttribute("ListVersion", "1");  
           //ele.SetAttribute("RootFolder", FilePath.Substring(0, FilePath.LastIndexOf("/")));  
           ele.InnerXml = xmlCommand;  
           XmlNode node1 = listService.UpdateListItems(lListID, ele);  
           if ((node1 != null) && (node1.InnerText == SUCCESS))  
           {  
             Console.WriteLine("SUCCESS");  
           }  
           else  
           {  
             Console.WriteLine("There is an error : " + node1.InnerText);  
           }  
         }  
       }  
       catch (Exception ex)  
       {  
         throw ex;  
       }  
     }  

here is the error that you may get:
1) 0x81020030Invalid file name.\n\nThe file name you specified could not be used. It may be the name of an existing file or directory, or you may not have permission to access the file.
This is because FileRef must be included in the query for deleting the item.

2) You got success code (0x00000000) however the file just stays there in the Document Library.
This is because the FileRef path is using encoded URL
(eg. http://url/a/b/c/cc%20c.pdf -> must change to -> http://url/a/b/c/cc c.pdf)
After changing to non-encoded URL, wala.. it's working like magic!

Hope it helps!

Sharepoint WS References

Good reference websites:
http://sarangasl.blogspot.sg/2009/12/addupdate-list-item-using-sharepoint.html
http://sarangasl.blogspot.sg/2009/12/sharepoint-list-web-service.html
http://sarangasl.blogspot.sg/2009/10/caml-spquery-in-sharepoint.html

CAML Query Helper to help you create the query, can just download, it's quite easy to use.
http://spcamlqueryhelper.codeplex.com/

Sharepoint WS - Copy - Move the file from one server to another

Here's the method
faxObj is just an object if u wan to include for the field information purpose.
EDMSPath is the source
EDMSRPath is the destination

 private static bool MoveEDMSToEDMSR(ISDAFaxDBO faxObj, string EDMSPath, string EDMSRPath, string msg ="")  
     {  
       using (CopyWS.Copy copyService = new CopyWS.Copy())  
       {  
         copyService.Url = CopyURL;  
         copyService.Credentials = System.Net.CredentialCache.DefaultCredentials;  
         copyService.Credentials = new System.Net.NetworkCredential(username, password);  
         CopyWS.FieldInformation fiEDMS = new CopyWS.FieldInformation();  
         CopyWS.FieldInformation[] fi = { fiEDMS };  
         byte[] byteArr;  
         uint getFile = copyService.GetItem(EDMSPath, out fi, out byteArr);  
         string[] destination = { EDMSRPath };  
         CopyWS.CopyResult cResult1 = new CopyWS.CopyResult();  
         CopyWS.CopyResult cResult2 = new CopyWS.CopyResult();  
         CopyWS.CopyResult[] cResultArray = { cResult1, cResult2 };  
         CopyWS.FieldInformation fiRecordDate = new CopyWS.FieldInformation();  
         fiRecordDate.DisplayName = "Record Date";  
         fiRecordDate.Type = CopyWS.FieldType.DateTime;  
         fiRecordDate.Value = faxObj.MaturityDate.ToString("yyyy/MM/ddT00:00:00Z");  
         CopyWS.FieldInformation fiSector = new CopyWS.FieldInformation();  
         fiSector.DisplayName = "Sector";  
         fiSector.Type = CopyWS.FieldType.Choice;  
         fiSector.Value = "Fax";  
         CopyWS.FieldInformation[] fis = { fiRecordDate, fiSector };  
         uint copyresult = copyService.CopyIntoItems(EDMSPath, destination, fis, byteArr, out cResultArray);  
         //Array.Resize<CopyWS.FieldInformation>(ref fi, fi.Length + 2);  
         //fi[fi.Length - 2] = fiRecordDate;  
         //fi[fi.Length - 1] = fiSector;  
         //uint copyresult = copyService.CopyIntoItems(EDMSPath, destination, fi, byteArr, out cResultArray);  
         if (copyresult == 0)  
         {  
           int idx = 0;  
           foreach (CopyWS.CopyResult myCopyResult in cResultArray)  
           {  
             string opString = (idx + 1).ToString();  
             if (cResultArray[idx].ErrorMessage == null)  
             {  
               msg += "Copy operation " + opString +  
                 "completed.\r\n" + "Destination: " +  
                 cResultArray[idx].DestinationUrl;  
             }  
             else  
             {  
               msg += "Copy operation " + opString +  
                 " failed.\r\n" + "Error: " +  
                 cResultArray[idx].ErrorMessage + "\r\n" +  
                 "Code: " + cResultArray[idx].ErrorCode;  
             }  
             idx++;  
           }  
           return true;  
         }  
         else  
         {  
           return false;  
         }  
       }  
     }  

Sharepoint WS - Lists - Check if folder exists and create folders

Here's the code to check if the folder exists using Sharepoint Web Service
EDMSRPath is the full path of the file (eg. http://url/ListName/blabla/be/bo/x.pdf)
WSPath is the url of the document library (eg. http://url)

 char[] separator = ("/").ToCharArray();  
       string[] folder = EDMSRPath.Substring(EDMSRPath.IndexOf("ListName") + ("ListName").Length + 1).Split(separator);  
 //check first folder  
       // This method is separated to avoid getting the subfolders for all top folders, as it will have too much recurrence.  
       getFolders(string.Empty, ref dt);  
       string firstFolder = WSPath + "/ListName";  
       if (!checkFolderExist(dt, firstFolder + "/" + folder[0]))  
       {  
         CreateFolder(firstFolder, folder[0]);  
       }  
       firstFolder += "/" + folder[0];  
       //check the subfolders required  
       dt = new DataTable();  
       if (folder.Length > 1)  
       {  
         getFolders(folder[0], ref dt, true);  
         string subfolder = firstFolder;  
         for (int i = 1; i < folder.Length; i++)  
         {  
           if (!checkFolderExist(dt, subfolder + "/" + folder[i]))  
           {  
             CreateFolder(subfolder, folder[i]);  
           }  
           subfolder += "/" + folder[i];  
         }  
       }  

Now the actual functions
CheckFolderExist - to check if the folder exists

     private static bool checkFolderExist(DataTable dt, string folderpath)  
     {  
       if (dt.Rows.Count > 0)  
       {  
         for (int i = 0; i < dt.Rows.Count; i++)  
         {  
           if (dt.Rows[i][0].ToString() == folderpath || dt.Rows[i][0].ToString() == folderpath.Replace(" ","%20"))  
           {  
             return true;  
           }  
         }  
       }  
       return false;  
     }  

And function to create the folder

     private static string CreateFolder(string path, string foldername)  
     {  
       string res = string.Empty;  
       try  
       {  
         using (ListWS.Lists listService = new ListWS.Lists())  
         {  
           listService.Url = ListURL;  
           listService.Credentials = System.Net.CredentialCache.DefaultCredentials;  
           listService.Credentials = new System.Net.NetworkCredential(username, password);  
           XmlDocument doc = new XmlDocument();  
           string xmlCommand = "<Batch OnError='Continue' RootFolder='" + path +  
               "'><Method ID='1' Cmd='New'><Field Name='ID'>New</Field><Field Name='FSObjType'>1</Field><Field Name='BaseName'>" +  
               foldername + "</Field></Method></Batch>";  
           doc.LoadXml(xmlCommand);  
           XmlNode batchNode = doc.SelectSingleNode("//Batch");  
           XmlNode node1 = listService.UpdateListItems("ListName", batchNode);  
           if ((node1 != null) && (node1.FirstChild.FirstChild.InnerText == FOLDER_EXISTS) || (node1.FirstChild.FirstChild.InnerText == SUCCESS))  
           {  
             // success  
             res = "";  
           }  
           else  
           {  
             res = "Create new folder failed for: " + foldername + ". Error Details: " + node1.InnerText;  
           }  
             
         }  
       }  
       catch (Exception ex)  
       {  
         if (ex.Message != "Column name 'ID' is defined for different mapping types.")  
           throw ex;  
       }  
       return res;  
     }  

Sharepoint WS - Copy - Save local file to EDMS

Here is the method to save the local file in your computer and put it to the document library, my list name is EDMS.

 private static bool saveFileInEDMS(ISDAFaxDBO faxObj, string localPath, string EDMSPath)  
     {  
       using (CopyWS.Copy copyService = new CopyWS.Copy())  
       {  
         copyService.Url = CopyURL;  
         copyService.Credentials = System.Net.CredentialCache.DefaultCredentials;  
         copyService.Credentials = new System.Net.NetworkCredential(username, password);  
         string[] destination = { EDMSPath };  
         CopyWS.CopyResult cResult1 = new CopyWS.CopyResult();  
         CopyWS.CopyResult cResult2 = new CopyWS.CopyResult();  
         CopyWS.CopyResult[] cResultArray = { cResult1, cResult2 };  
         CopyWS.FieldInformation fiRecordDate = new CopyWS.FieldInformation();  
         fiRecordDate.DisplayName = "Date";  
         fiRecordDate.Type = CopyWS.FieldType.DateTime;  
         fiRecordDate.Value = faxObj.MaturityDate.ToString("yyyy/MM/ddT00:00:00Z");  
         CopyWS.FieldInformation[] fis = { fiRecordDate };  
         FileStream strm = new FileStream(localPath, FileMode.Open, FileAccess.Read);  
         Byte[] fileContents = new Byte[strm.Length];  
         int ia = strm.Read(fileContents, 0, Convert.ToInt32(strm.Length));  
         strm.Close();  
         uint copyresult = copyService.CopyIntoItems(localPath, destination, fis, fileContents, out cResultArray);  
         if (copyresult == 0)  
         {  
           return true;  
         }  
         else  
         {  
           return false;  
         }  
       }  
     }  

Sharepoint WS - Lists - Get Document ID

Here is the function to get the document data and it will return the document ID in result string.

 private static string GetDocID(string docname)  
     {  
       try  
       {  
         string result="";  
         using (ListWS.Lists listService = new ListWS.Lists())  
         {  
           listService.Url = ListURL;  
           listService.Credentials = System.Net.CredentialCache.DefaultCredentials;  
           listService.Credentials = new System.Net.NetworkCredential(username, password);  
           XmlDocument doc = new XmlDocument();  
           XmlElement query = doc.CreateElement("Query");  
           XmlElement viewFields = doc.CreateElement("ViewFields");  
           XmlElement queryOptions = doc.CreateElement("QueryOptions");  
           //query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +  
           //     "<Value Type=\"Counter\">0</Value></Gt></Where>";  
           //docname = "STAR.BE_CIS_B_My%20%20test%20123.pdf";  
           query.InnerXml = "<Where><Eq><FieldRef Name=\"LinkFilename\" />" +  
                "<Value Type=\"Text\">"+ docname.Replace("%20"," ") + "</Value></Eq></Where>";  
           //query.InnerXml = "<Where><BeginsWith><FieldRef Name=\"LinkFilename\" />" +  
           //     "<Value Type=\"Text\">S</Value></BeginsWith></Where>";  
           viewFields.InnerXml = "";  
           queryOptions.InnerXml = "<ViewAttributes Scope='Recursive'/>";  
           XmlNode nodes = listService.GetListItems("ListName", String.Empty, query, viewFields, string.Empty, queryOptions, null);  
           foreach (System.Xml.XmlNode node in nodes)  
           {  
             if (node.Name == "rs:data")  
             {  
               for (int i = 0; i < node.ChildNodes.Count; i++)  
               {  
                 if (node.ChildNodes[i].Name == "z:row")  
                 {  
                   for (int j = 0; j < node.ChildNodes[i].Attributes.Count; j++)  
                   {  
                     Console.WriteLine(j + "|" + node.ChildNodes[i].Attributes[j].Name + "|" + node.ChildNodes[i].Attributes[j].Value);  
                   }  
                   result = node.ChildNodes[i].Attributes["ows_ID"].Value;  
                 }  
               }  
             }  
           }  
         }  
         return result;  
       }  
       catch (Exception ex)  
       {  
         throw ex;  
       }  
     }  

CSS Vertical text

Try this:

 <style>.test {  
      writing-mode:tb-rl;  
      -webkit-transform:rotate(90deg);  
      -moz-transform:rotate(90deg);  
      -o-transform: rotate(90deg);  
 }  
 </style>  
 <p class="test">testing 12123123</p>  

Result:

testing 12123123

Reference here

SQL to get the tables and its row count

Below is the SQL we can use to get the tables and its row count.
CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

drop table #counts


Alternatively, can use this SQL, it will give the same result:
DECLARE @sql nvarchar(MAX)

SELECT
    @sql = COALESCE(@sql + ' UNION ALL ', '') +
        'SELECT
            ''' + s.name + '.' + t.name + ''' AS ''Table'',
            COUNT(*) AS Count
            FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM sys.schemas s
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    ORDER BY
        s.name,
        t.name

EXEC(@sql)

Reference: here

[System.Web.HttpException]: {"File does not exist."}

Did you encounter this error? Dunno where exactly the error is??
Same me too..

Resolution is check your css file.
There could be an image reference from the css file that is not valid anymore.
That's the case for me.
So after I remove the css that refer to the invalid file path, it didn't give such error anymore.

Reference: here