How to Install Node.js

Node.js is a server-side implementation of JavaScript built on top of Google V8 JavaScript runtime engine. Node's asynchronous, event-driven I/O model makes it easy for developers with JavaScript knowledge to build high-performing, scalable, and highly concurrent web applications. Node.js replacing the multithreaded approach with single-threaded, event-driven I/O. Ability to handle thousands of concurrent connections with minimal overhead on a single process.

Install nodejs in windows.

1. Download the MSI installer for nodejs. Version 0.10.33 is used at the time of this document.
2. Run the installer following all default values.
3. Verify the install by running ‘node -v’ in a terminal. The current nodejs version should be listed.

Install nodejs in Ubuntu.

Open the terminal and run the following command

1. $ sudo apt-get update
2. $ sudo apt-get install nodejs (It will install nodejs on your machine)
3. $ sudo apt-get install npm (It will install npm. Using npm we can install dependency of nodejs project.)
4. $ sudo ln -s /usr/bin/nodejs /usr/bin/node
5. Verify the install by running ‘node -v’ in a terminal. The current nodejs version should be listed.

How to Create Log file using c# 5.0 futures Caller Information Attributes

Source Code

This article provides a brief introduction to use of Caller Information as a means for creating an error logging utility class for your C# 5.0/.NET 4.5 applications. Caller Information is a new language feature introduced with the .NET 4.5 update and is made available through the System.Runtime.CompilerServices namespace.

Caller Information Attributes

There are three attributes available for use:

1. CallerMemberName
   This attribute gives you the name of the caller as a string. For methods, the respective method names are returned.
2. CallerFilePath
   This attribute gives you the path and file name of the source file that contains the caller.
3. CallerLineNumber
   This attribute gives you the line number in the source file at which the method is called. It is important to note that the caller line number attribute points to the line where the method is called and not to the line where the exception actually occurred.

Now let see how to create common class library to write log file.

Open the Visual Studio, go to file -> Add -> New project it will the “ New Project Window”. From New project window select window from left side and Window Form Application from right side and enter “Arithmetic “ in name textbox. See fig bellow.

 We will implement window form later.

Let see first how to write common class library to write log file for our window form application.

For that right click solution mouse over Add then click New Project. From New Project window select Windows from left side and select Class Library. Enter Error4net in name textbox. Then click ok button.

In Error4net project you can see class1.cs file .Just delete the file. And right click on Error4net from solution explorer select Add then click class .it will open add new item window. Select class from right side and enter ErrorLog in name textbox.The Error4net project look like bellow.

Rewrite Errorlog.cs file like bellow.

using System;
using System.Runtime.CompilerServices;
using System.Configuration;
using System.IO;
namespace Error4Net
{
public static class ErrorLog
{
public static void LogInfo(string message,
[CallerMemberName] string memberName = null,
[CallerFilePath] string filePath = null,
[CallerLineNumber] int lineNumber = 0)
{
string errLogFile = CreateLogFile();
if (errLogFile != string.Empty)
{
using (FileStream fs = new FileStream(errLogFile, FileMode.Append, FileAccess.Write))
{
using (StreamWriter s1 = new StreamWriter(fs))
{
s1.Write("=======================Start Info=====================" + Environment.NewLine);
s1.Write("Info: " + DateTime.Now.ToLongDateString() + Environment.NewLine);
s1.Write("Message: " + message + Environment.NewLine);
s1.Write("Caller Member Name: " + memberName + Environment.NewLine);
s1.Write("Caller Path: " + filePath + Environment.NewLine);
s1.Write("Caller Line Number: " + lineNumber.ToString() + Environment.NewLine);
s1.Write("======================End=======================" + Environment.NewLine);
}
}
}
}


public static void LogError(string message,
[CallerMemberName] string memberName = null,
[CallerFilePath] string filePath = null,
[CallerLineNumber] int lineNumber = 0)
{
string errLogFile = CreateLogFile();
if (errLogFile != string.Empty)
{
using (FileStream fs = new FileStream(errLogFile, FileMode.Append, FileAccess.Write))
{
using (StreamWriter s1 = new StreamWriter(fs))
{
s1.Write("=======================Start Error=====================" + Environment.NewLine);
s1.Write("Error Log Entry: " + DateTime.Now.ToLongDateString() + Environment.NewLine);
s1.Write("Message: " + message + Environment.NewLine);
s1.Write("Caller Member Name: " + memberName + Environment.NewLine);
s1.Write("Caller Path: " + filePath + Environment.NewLine);
s1.Write("Caller Line Number: " + lineNumber.ToString() + Environment.NewLine);
s1.Write("======================End=======================" + Environment.NewLine);
}
}
}
}
private static string CreateLogFile()
{
string filepath = string.Empty;
bool IsEnableLog = false;
bool.TryParse(ConfigurationSettings.AppSettings["enablelog"].ToString(), out IsEnableLog);
if (IsEnableLog)
{
string logPath = ConfigurationSettings.AppSettings["logfile"].ToString();
filepath = Path.Combine(logPath, "logfile.txt");
if (!Directory.Exists(logPath))
{
Directory.CreateDirectory(logPath);
using (FileStream fs = new FileStream(filepath, FileMode.Create))
{ }
return filepath;
}
CheckFileSixe(filepath);
}
return filepath;
}
private static void CheckFileSixe(string filepath)
{
if (!File.Exists(filepath))
{
using (FileStream fs = new FileStream(filepath, FileMode.Create))
{ }
}
else
{
long maxlogfilesize;
if (!Int64.TryParse(ConfigurationSettings.AppSettings["maxlogfilesize"].ToString(), out maxlogfilesize))
{
maxlogfilesize = 10;
}
FileInfo f = new FileInfo(filepath);
long s1 = f.Length;
if ((f.Length / 1024) >= maxlogfilesize)
{
string movefilePath = CreateOldLogFile();
File.Move(filepath, movefilePath);
using (FileStream fs = new FileStream(filepath, FileMode.Create))
{ }
}
}
}

private static string CreateOldLogFile()
{
string logPath = ConfigurationSettings.AppSettings["logfile"].ToString();
string movelogpath = Path.Combine(logPath, "OldLog");
string moveFilepath = Path.Combine(movelogpath, "logfile" + string.Format("{0:yyyy_MM_dd_hh-mm-ss-tt}", DateTime.Now) + ".txt");
if (!Directory.Exists(movelogpath))
{
Directory.CreateDirectory(movelogpath);

}
return moveFilepath;
}
}
}

Now let see how to use it in our window application.

Right click “Arithmetic” from solution explorer go to Add, then go to Add reference it will open Add reference window. Select Solution from right side and check Error4Net click ok

Now delete Form1.cs file from Arithmetic Then add following line in App.config.

………………………………………………………….

Now create an one simple window form ‘SimpleArithmetic.cs” like bellow

Now right click on form and click View code it will open one class file. Rewrite that file like bellow.

using Error4Net;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Arithmetic
{
public partial class SimpleArithmetic : Form
{
public SimpleArithmetic()
{
InitializeComponent();
}

private void btnAdd_Click(object sender, EventArgs e)
{

txtResult.Text = "";
try
{
ErrorLog.LogInfo("");
txtResult.Text = (Convert.ToInt32(txtNumber1.Text) + Convert.ToInt32(txtNumber2.Text)).ToString(); }
catch(Exception ex)
{
ErrorLog.LogError(ex.Message);
}
}

private void btnSum_Click(object sender, EventArgs e)
{
txtResult.Text = "";
try
{
ErrorLog.LogInfo("");
txtResult.Text = (Convert.ToInt32(txtNumber1.Text) - Convert.ToInt32(txtNumber2.Text)).ToString();
}
catch (Exception ex)
{
ErrorLog.LogError(ex.Message);
}

}
private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}

and rewrite program.cs file like bellow

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Arithmetic
{
static class Program
{
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new SimpleArithmetic());
}
}
}

Now sample window application is ready press F5 and use this form.

CASE Expressions in MS SQL Server

A switch statement allows a variable to be tested for equality against a list of values. Each value is called a case, and the variable being switched on is checked for each switch case. We are familiar how to use switch in c#. By using CASE we can achieve this in SQL server also.

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

There are two different way we can form CASE expressions.

• Simple CASE expressions
  A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
  DECLARE @TestVal INT
SET @TestVal = 3
  SELECT
CASE @TestVal
  WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
  Result Third
• Searched CASE expressions
  A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:
  DECLARE @TestVal INT
SET @TestVal = 50
  SELECT
CASE
WHEN @TestVal <=10 THEN 'Input is less than 10'
WHEN @TestVal >10 && @TestVal <=20 THEN 'Input is greater than 10 and less than 20'
WHEN @TestVal >20 && @TestVal <=30 THEN 'Input is greater than 20 and less than 30'
WHEN @TestVal >30 && @TestVal <=40 THEN 'Input is greater than 30 and less than 40'
ELSE 'Input is greater than 40 and less than or equal to 50'
END
  Result Input is grater than 40 and less than or equal 50

Column store indexes

          Column store index is awesome feature in SQL server 2012. You can get this feature expand the any one table you can see Index. Then right click on the indexes folder you can see “Non-Clustered Column store Index” , as shown in the below figure.

             Normally when we create an Index on table it store the records on page as row wise and each page have 8KB Size. For example I create an index on CountryMaster table, after that if I query country column from the CountryMaster table it fetch the record from all the pages and give it into us. See the image bellow

Now let see how Non-Clustered Column store Index work. Non-Clustered Column store Index stored the records based on column wise it store same column data on same page. If I stored data as column wise then we avoid fetch data from multiple page. See image bellow

Timeout when running stored procedure using Entity Framework (EF) Model first

When we execute the stored procedure using entity framework sometimes we got error The wait operation timed out. In this post I want to explain how to solve this issue. Most probably of this issue is the stored procedure take more time (more than 30 sec). For demonstration purpose I was created a stored procedure like bellow.

CREATE PROCEDURE LongRunTimeProcedure
AS
BEGIN
--Your implementation
--Your demo purpose I use WAITFOR DELAY Class
WAITFOR DELAY '00:01'
Select * from CountryMaster
END
GO

When we execute the above stored procedure from SSMS it take 1 mints. See the bellow image. WAITFOR DELAY '00:01' query stop the execution for I mints after I 1 mints it will execute the next Select * from CountryMaster

If we run the above the stored procedure using Entity framework model first concept it will throw the Timeout error like bellow.

Because the default CommandTimeout of entity framework is 30 seconds. But stored procedure estimate time is 1 min. now see how to solve the problem.

To solve this problem just change the CommandTimeout to 0 before to call the stored procedure like bellow.

public virtual ObjectResult< longruntimeprocedure_result> LongRunTimeProcedure()
{
((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 0;
return ((IObjectContextAdapter)this<longruntimeprocedure_result>("LongRunTimeProcedure"
} 

This is immediate solution only not an permanent solution, for permanent solution you should increase the performance of stored procedure. Please see the link how to improve how to improve the performance of stored procedure.

Improve performance of Stored Procedures

Frequently we use the Stored Procedure in our application to perform data manipulation and data query or both. In that case Performance is most importance because best performance is the main concern to develop a successful application. In this post I want to point out what are the things we need to follow to write best stored procedure with performance.

• SET NOCOUNT
  When we executing the select or DML (Insert, Update, and Delete) query in SQL server it return the message that inform to how many row that query affect in database. This information is useful for debugging purpose but definitely it take some time to write that message. It will affect the performance.
  To avoid it we have to Include SET NOCOUNT ON In above the query. For example
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Procedure code here
  SELECT column1 FROM dbo.TblTable1
  -- Reset SET NOCOUNT to OFF
  SET NOCOUNT OFF;
GO
END
• Create Clustered and Non-Clustered Indexes
  Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.
• Use schema name with object name
  Schema name should be used with all the object we used in stored procedure. Because if we use the object without schema name, in executing time it search all the schema one by one to find the object. So better use Schema with object. For example.
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Preferred way querying table
  SELECT column1 FROM dbo.TblTable1
  – Should avoid
  SELECT column1 FROM TblTable1
  --Preferred way to call sp
  EXEC dbo.MyProce
  – Should avoid
  EXEC MyProce
  -- Reset SET NOCOUNT to OFF
  SET NOCOUNT OFF;
GO
END
• Do not use the prefix “sp_” in the stored procedure name
  If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
• Use IF EXISTS (SELECT 1) instead of (SELECT *)
  Sometimes we need to check the records is already present in table or not for that we use IF EXISTS. For example if the records already exist in table then update otherwise insert. In that case we use IF EXISTS class it return “true” if any records is return by internal query either single value “1” are all columns.
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  --Preferred way querying table
IF EXISTS(select 1 from Batch WHERE BatchName = 'batch1')
  BEGIN
  Update Batch SetBoolParam =0 WHERE BatchName = 'batch1'
END
ELSE
BEGIN
Insert Batch(BatchName) values ('batch1')
END
---- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
END

• Use TRY-Catch for error handling
  The stored procedure have more than one T-SQL statement .In that case weed to check after every t-SQl statement is any error have. To avoid that we use TRY-Catch
  CREATE PROC dbo.ProcName
  AS
BEGIN
SET NOCOUNT ON;
  BEGIN TRY
Select 1/1
  Select 1/0
  END TRY
BEGIN CATCH
RAISERROR ('Error raised in TRY block.', 16, 1);
  END CATCH
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
  GO
  END
• Use TRY-Catch for error handling
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch
  DECLARE @Query VARCHAR (100)
  DECLARE @Age INT
SET @Age = 25
  SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + convert( VARCHAR (3),@Age)
  EXEC (@Query)
  If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
  DECLARE @Query VARCHAR (100)
  DECLARE @Age INT
SET @Age = 25
  SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + convert( VARCHAR (3),@Age)
  execute sp_executesql @Query N'@Age int' , @Age = 25
  the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance
• Choose Appropriate Data Type
  Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.
• Avoid nchar and nvarchar
  Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.
• Avoid * in SELECT statement
  Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required
  -- Avoid
SELECT * FROM tblName
  --Best practice
SELECT col1,col2,col3 FROM tblName
• CREATE PROCEDURE with WITH RECOMP
  The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Convert Excel to XMl using LINQ OR Read Data From Excel using c#

        

      Before that I request to you all please give your valuable feed back about the blog and this post. It will help me to create upcoming blog post better than now.

In this example I want to explain how to read data from Excel file and save it as XML. for that I will use LinqToExcel

C# Linq to Excel

            LinqToExcel invited by Paul Yoder is a .NET library which facilitates querying Excel spreadsheets and .CSV files using the familiar LINQ syntax. So if you have idea in Linq then you can easily use it. It is common for both xls, xlsx, csv file.

How to Install

            We can easily install this using Nuget Package by using following comets.

PM> Install-Package LinqToExcel

Or you can use “Manage Nuget Package “and search “LinqToExcel” and install ref following windows.

Now our application is ready to use the feature of Linq to Excel. Before go to c# code we need to prepare excel file using bellow employee details

EmployeeName	Address	City	State	Country
Name1	Address1	Chennai	Tamil Nadu	India
Name2	Address2	Bangalore	Karnataka	India
Name3	Address3	Delhi	Delhi	India
Name4	Address4	Thiruvananthapuram	Kerala	India
Name5	Address5	Hyderabad	Telangana	India

                          using this above  data and converted it into excel book like and name it as EmployeeWorkBook.xlsx.It look like bellow.

      

Now open the Visual studio and create a console application for read data from excel file, name it as ExceltoXml. Let us see how read data from employee details excel file using Linq to Excel and convert it into XML.

 Rewrite Program.cs class file like bellow.

using LinqToExcel; using System; using System.Collections.Generic; using System.Xml.Linq; using System.Linq; namespace ExcelToXml {     class Program     {        static void Main(string[] args)         {            try             {                var excel = new ExcelQueryFactory(@"E:\employeeworkbook.xlsx"); foreach (string sheetName in excel.GetWorksheetNames()) { //Get Excel column var columnList = excel.GetColumnNames(sheetName); XDocument srcTree = new XDocument(new XComment("This is a xml contain Employee Details"), new XElement("EmployeeRoot", excel.Worksheet(sheetName).AsEnumerable().Select(a=> new XElement("Employees",from cn in columnList select new XElement(cn,a[cn])))));  srcTree.Save(@"E:\employee.xml"); }             }             catch (Exception ex)             {                 throw ex;             }         }              } }

Save it and Run the application .for run the application pres F5 button from your key board.
It will create employee.xml file and save it in E Directory.

- <!-- This is a xml contain Employee Details   --> -<EmployeeRoot> - <Employees> - <Employee>   <EmployeeName>Name1</EmployeeName>   <Address>Address1</Address>   <City>Chennai</City>   <State>Tamil Nadu</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name2</EmployeeName>   <Address>Address2</Address>   <City>Bangalore</City>   <State>Karnataka</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name3</EmployeeName>   <Address>Address3</Address>   <City>Delhi</City>   <State>Delhi</State>   <Country>India</Country> </Employee> - <Employee>   <EmployeeName>Name4</EmployeeName>   <Address>Address4</Address>   <City>Thiruvananthapuram</City>   <State>Kerala</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name5</EmployeeName>   <Address>Address5</Address>   <City>Hyderabad</City>   <State>Telangana</State>   <Country>India</Country>   </Employee>   </Employees>   </EmployeeRoot>