Getting Date from Excel using openXml

When iterated through excel and read cell contains date it returned int value. you can read cell contains date and convert to simple date format using code below:

Cell cellDate;                 string newDate;                 using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("2.xlsx", false))                {                 var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants();                 foreach (Sheet sheet in sheets)                 {                     var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants();                     foreach (Sheet sheet in sheets)                    {                        List news = new List();                        uint rowIndex = 1;                        uint colIndex = 1;                        WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);                        Worksheet worksheet = worksheetPart.Worksheet;                        SheetData sheetData = worksheet.GetFirstChild();                        IEnumerator rows = sheetData                                             .Elements().GetEnumerator();                       while (rows.MoveNext())                       {                         Row row = rows.Current;                         cellDate = GetCell(sheetData, "C" + rowIndex.ToString());                         if (cellDate.DataType != null && cellDate.DataType == CellValues.SharedString)                         {                              newDate = GetSharedStringItemById(spreadsheetDocument.WorkbookPart, int.Parse(cellDate.CellValue.InnerText));                         }                         else                         {                            newDate = DateTime.FromOADate(double.Parse(((CellValue)cellDate.FirstChild).Text)).ToString();                         }               }            }         }      }    public static Cell GetCell(SheetData sheetData, string fullAddress)    {             return sheetData.Descendants()                 .Where(c => c.CellReference == fullAddress)                 .FirstOrDefault();    }     private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)     {           Row row = GetRow(worksheet, rowIndex);           if (row == null)                 return null;           return row.Elements().Where(c => string.Compare                    (c.CellReference.Value, columnName +                    rowIndex, true) == 0).FirstOrDefault();     }     // Given a worksheet and a row index, return the row.     private static Row GetRow(Worksheet worksheet, uint rowIndex)     {           return worksheet.GetFirstChild().               Elements().Where(r => r.RowIndex == rowIndex).FirstOrDefault();     }     public static string GetSharedStringItemById(WorkbookPart workbookPart, int id)     {           return workbookPart.SharedStringTablePart.SharedStringTable.Elements()                 .ElementAt(id).InnerText;     }

Database Performance Considerations

While designing your database, keep performance in mind. You can't really tune performance later when your database is in production.

1. Normalize and De normalize Tables.
2. Create Table Columns with maximum row size.(If number of columns larger than row size, split table into multiple tables with relations one to one). For column with extended size like binary, ntext, and...etc We prefer to put in separate table.
3. Create a primary key on each table. Each primary key is clustered index.
4. Add calculated columns which you need to avoid complicated queries.
5. Create an index on any column that is a foreign key.
6. Create indexes for your query, Indexes will eliminate rows scanning and locking problems:
   • Plan your indexes. Indexes enhance performance but increasing them cause drawback. Choose indexes make big filtration of your records.
   • Create simple index (indexes have only one column) for Where condition.
   • For sort create composite indexes(indexes have multiple columns).
7. Create Indexed View if Possible.
8. Use owner qualify of your objects (as much as possible) when you reference them in TSQL. Use owner.table instead of just table. 
9. Use set nocount on at the top of each stored procedure and set nocount off at the bottom. 
10. If needn't locking your database table, you take a chance on a dirty read? Ways of dirty work:
    
    • Use the NOLOCK hint.
    • Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
11. Return the columns and the rows you need.use select column1, coulmn2,.. instead of select *
12. Use transactions when appropriate, decrease transaction time.
13. Avoid temp tables as much as you can.
14. Indexes will not work with Functions for example, where LTRIM(Name) = 'Products'.
15. Avoid using OR in your query as much as possible (Cause unexpected plan), Use join instead of it.
16. Avoid negative query NOT IN (Use outer join),<> (Indexes will not work with Negative query). Same thing when you use like as like '%val'.
17. If you use dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC). 
18. Reduce the number of round trips to the server.

SQL Server Naming Conventions

1. Databases, Files, and File Paths 
   
   
   •  The database name should accurately reflect the database content and function. All database names must be prefixed with the originating component's acronym--e.g., CBC_Medicare_Compare or EDG_Database_Tracking. Use both upper and lower case letters as appropriate. Do not use spaces in the name. 
   • File names must match the database name. 
   • If the database is being developed off site from CMS's central office, please create your databases in the default Microsoft directory structure. E.g.,
     C:\Program Files\Microsoft SQL Server\MSSQL\data\OIS_Personnel_Master_Data.MDF 
2. Tables and Views 
   
   
   •  Table names should accurately reflect the table's content and function. Do not use spaces in the name. 
   • View names follow the same conventions as table names, but should be prefixed with the literal 'VW'. E.g.,
     vw1999NewRegulations 
3. Columns
   
   The standards below are applicable to all column names:
   • Each column name must be unique within its table.
   • Each column name must be derived from the business name identified during the business/data analysis process. For more information on deriving column names from business names, see Creating Physical Names for Elements and Columns in the Data Administration standards. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name and an abbreviated physical name. 
   • Do not use reserved or key words as object names. 
   In addition, if the data is going to be brought in-house to interact with other CMS computer systems, the following standards are applicable: 
   
   • The name can have a maximum of 18 characters. 
   • The name must include acceptable class and modifying words as specified in CMS's Data Administration standards. 
4. Indexes
   
   Indexes are named to indicate the table they are attached to and the purpose of the
   index.
   
   • Primary keys have a suffix of '_PK'.
   • Foreign keys have a suffix of '_FKx' where x is a number that is incrementally assigned.
   • Clustered indexes have a suffix of '_IDX'.
   • All other indexes have a suffix of '_NDXx' where x is incrementally assigned.
   Only one suffix per index may be appended. The application of the appropriate suffix should follow the following hierarchy: primary key, clustered index, foreign key, other index. E.g., an index that is both a primary key and clustered should have a suffix of '_PK'.  It is good practice to index columns that are frequently used in a query's selection criteria. 
5. Stored Procedure
   
   
   • System level stored procedures are named using a prefix 'SP__' (two underscores) and a description of what the stored procedure does. 
   • All application level and user defined stored procedures are prefixed with the constant 'USP' with a description of what the stored procedure does. E.g., UspGetLastModifiedDate 
6. Triggers
   
   Triggers are named to indicate the table they are for and the type of trigger. The
   purpose of the trigger is identified in the prefix to the name. All triggers should be
   prefixed with the letter 'T', a letter(s) designating the type, an underscore, and the
   table name. The type should be designated as 'I' = insert, 'U' = update, 'D' = delete.
   E.g., ti_Orders (Insert trigger)
7. Variables
   
   Variable identifiers for datatypes should consist of two parts: 
   
   • The base, which describes the content of the variable; 
   • The prefix, which describes the datatype of the variable 
   Correct prefixes for each datatype are shown in the table below. 
   
   

   Datatype	Prefix	Example
   Char	chr	@chrFirstName
   Varchar	chv	@chvActivity
   Nchar	chrn	@chrnLastName
   Nvarchar	chvn	@chvnLastName
   Text	txt	@txtNote
   Ntext	txtn	@txtnComment
   Datetime	dtm	@dtmTargetDate
   Smalldatetime	dts	@dtsCompletedDate
   Tinyint	iny	@inyActivityID
   Smallint	ins	@insEquipmentTypeID
   Integer	int	@intAsset
   Bigint	inb	@inbGTIN
   Numeric or Decimal	dec	@decProfit
   Real	rea	@reaVelocity
   Float	flt	@fltLength
   Smallmoney	mns	@mnsCost
   Money	mny	@mnyPrice
   Binary	bin	@binPath
   Varbinary	biv	@bivContract
   Image	img	@imgLogo
   Bit	bit	@bitOperational
   Timestamp	tsp	@tspOrderID
   Uniqueidentifier	guid	@guidPrice
   sql_variant	var	@varInventory
   Cursor	cur	@curInventory
   Table	tbl	@tblLease

How to Access your Email box using Telnet

Checking Mail

If you installed telnet client and want read popup email do the following steps:

1. Connect to popup server using command:
   telnet popserver 110Message will appear:
   +OK sigiri POP3 Server (Version 1.004) ready.
2. Enter your username using command:
   USER username
   Message will appear:
   +OK please send PASS command
3. Enter your password:
   PASS userpassword
   Message will appear:
   +OK 2 messages ready for manages in ..
4. Now you can use commands of popup:
   list This will display the total number of messages and size.
   retr #no Displays the message-no including headers. 
   top #no lines Displays the display top lines from message-no including headers.
   dele #no Deletes the message-#no
   quit ends your session.

Sending Mail

  If you installed telnet client and want read smtp email do the following steps:

1. Connect to popup server using command:
   telnet popserver 25
2. Introduce  yourself using command:
   HELO hostname
3. Specify the sender using command:
   MAIL FROM: sender
4. Specify recipient using command:
   RCPT TO: recipient
5. Write message using command:
   DATA e-mail message.
   End message dot followed by newline.
6. You  can specify new sender using command:
   RSET Resets the system. 
7. You can end smtp session command:
   QUIT Exit sendmail (SMTP)

Convert to Base64

Base64 encoding used when there is a need to encode binary data that needs to be stored and transferred over media that are designed to deal with textual data. Base64 is commonly used in a number of applications including email via MIME, and storing complex data in XML.

Below way of conversion to Base64 using csharp or javascript:
Conversion from/to base64 using csharp:

static public string EncodeTo64(string toEncode)     {       byte[] toEncodeAsBytes             = System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode);       string returnValue             = System.Convert.ToBase64String(toEncodeAsBytes);       return returnValue;     }     static public string DecodeFrom64(string encodedData)     {       byte[] encodedDataAsBytes           = System.Convert.FromBase64String(encodedData);       string returnValue =          System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes);       return returnValue;     }

Conversion from/to base64 using javascript:

var Base64 = { // private property _keyStr : "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=", // public method for encoding encode : function (input) {     var output = "";     var chr1, chr2, chr3, enc1, enc2, enc3, enc4;     var i = 0;     input = Base64._utf8_encode(input);     while (i < input.length) {         chr1 = input.charCodeAt(i++);         chr2 = input.charCodeAt(i++);         chr3 = input.charCodeAt(i++);         enc1 = chr1 >> 2;         enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);         enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);         enc4 = chr3 & 63;         if (isNaN(chr2)) {             enc3 = enc4 = 64;         } else if (isNaN(chr3)) {             enc4 = 64;         }         output = output +         Base64._keyStr.charAt(enc1) + Base64._keyStr.charAt(enc2) +         Base64._keyStr.charAt(enc3) + Base64._keyStr.charAt(enc4);     }     return output; }, // public method for decoding decode : function (input) {     var output = "";     var chr1, chr2, chr3;     var enc1, enc2, enc3, enc4;     var i = 0;     input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");     while (i < input.length) {         enc1 = Base64._keyStr.indexOf(input.charAt(i++));         enc2 = Base64._keyStr.indexOf(input.charAt(i++));         enc3 = Base64._keyStr.indexOf(input.charAt(i++));         enc4 = Base64._keyStr.indexOf(input.charAt(i++));         chr1 = (enc1 << 2) | (enc2 >> 4);         chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);         chr3 = ((enc3 & 3) << 6) | enc4;         output = output + String.fromCharCode(chr1);         if (enc3 != 64) {             output = output + String.fromCharCode(chr2);         }         if (enc4 != 64) {             output = output + String.fromCharCode(chr3);         }     }     output = Base64._utf8_decode(output);     return output; }, // private method for UTF-8 encoding _utf8_encode : function (string) {     string = string.replace(/\r\n/g,"\n");     var utftext = "";     for (var n = 0; n < string.length; n++) {         var c = string.charCodeAt(n);         if (c < 128) {             utftext += String.fromCharCode(c);         }         else if((c > 127) && (c < 2048)) {             utftext += String.fromCharCode((c >> 6) | 192);             utftext += String.fromCharCode((c & 63) | 128);         }         else {             utftext += String.fromCharCode((c >> 12) | 224);             utftext += String.fromCharCode(((c >> 6) & 63) | 128);             utftext += String.fromCharCode((c & 63) | 128);         }     }     return utftext; }, // private method for UTF-8 decoding _utf8_decode : function (utftext) {     var string = "";     var i = 0;     var c = c1 = c2 = 0;     while ( i < utftext.length ) {         c = utftext.charCodeAt(i);         if (c < 128) {             string += String.fromCharCode(c);             i++;         }         else if((c > 191) && (c < 224)) {             c2 = utftext.charCodeAt(i+1);             string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));             i += 2;         }         else {             c2 = utftext.charCodeAt(i+1);             c3 = utftext.charCodeAt(i+2);             string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));             i += 3;         }     }     return string; } }

Also there is jquery library for base64:
https://github.com/carlo/jquery-base64

Serialization in Silverlight

When I want to serialize/deserialize class in silverlight 4/5, I found XmlSerializeris not supported.

We can use class DataContractSerializer which is supported by silverlight. Below methods using DataContractSerializer to serialize/deserialize classes:

Serialize method:

public string Serialize<T>(T data)         {             using (var memoryStream = new MemoryStream())             {                 var serializer = new DataContractSerializer(typeof (T));                  serializer.WriteObject(memoryStream, data);                 memoryStream.Seek(0, SeekOrigin.Begin);                 var reader = new StreamReader(memoryStream);                 string content = reader.ReadToEnd();                 return content;             }         }

Deserialize method: 

public T Deserialize<T>(string xml)         {             using( var stream = new MemoryStream(Encoding.Unicode.GetBytes(xml)) )             {                 var serializer = new DataContractSerializer(typeof (T));                 T theObject = (T)serializer.ReadObject(stream);                 return theObject;             }         }