Wednesday, August 20, 2014

Transliteration – JavaScript Example (ASP.net)

 

In this article I will illustrate how to convert text from one language to another in JavaScript.It can be achieved using Google API’s. There are many versions of API’s released.

here in this example i will use “jsapi” by referring following link in my web page.

<script type="text/javascript" src="https://www.google.com/jsapi">

else one can download java script file from the above link and reference it in the page.


Ex:  Suppose we have two textboxes , one for entering English text and the other to display translated text. Enter text in English and press tab , converted text will appear in another textbox.


ASPX Code :



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Ex.aspx.cs" Inherits="Ex" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<script type="text/javascript" src="https://www.google.com/jsapi">
</script>

<script type="text/javascript">

google.load(
"language", "1");
function myfunction() {
var filed
= 'txtEng'
var strEngName
= document.getElementById(filed).value;
var strEngArray
= new Array();
strEngArray
= strEngName.split(" ");
var str
= '';

if (document.getElementById('txtEng').value.length == 0) {

document.getElementById(
'txtTel').value = '';

}
document.getElementById(
'txtEng').value = document.getElementById('txtEng').value.replace(/^\s+|\s+$/g, '');

for (i = 0; i < strEngArray.length; i++)
{
var temp
= strEngArray[i];

google.language.transliterate([strEngArray[i]],
"en", "te", function (result)
{
if (!result.error) {
var container
= document.getElementById('txtTel');
if (result.transliterations && result.transliterations.length > 0 && result.transliterations[0].transliteratedWords.length > 0) {


container.value
= result.transliterations[0].transliteratedWords[0];
str
= str + ' ' + container.value;

}
document.getElementById(
'txtTel').value = str;

}

}

);
}




}
google.setOnLoadCallback(myfunction);
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tbody>
<tr>
<td>

Enter the Name(English)
</td>
<td>

<input id="txtEng" style="background-color:pink;width:380px; height:30px;font-size:18px;font-weight:bold" onblur="javascript:myfunction();" />
</td>

</tr>
<tr>
<td>Translated Text: </td>
<td>
<input id="txtTel" style="background-color:yellow;width:380px; height:50px;font-size:18px;font-weight:bold" />

</td>

</tr>
</tbody>

</table>
<br>
</div>
</form>
</body>
</html>


OUTPUT :


output

VIEW (SQL)

WHAT IS A VIEW

View is a virtual table whose contents are defined by a query.

A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

It is a virtual table computed or collated dynamically from data in the database when access to that view is requested.Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

CREATE VIEW SYNTAX

CREATE VIEW [ViewName]
AS
SELECT statement

Ex :

CREATE VIEW test
AS
SELECT empid,
              empname,
              empaddress
FROM employee

Get Result from view :

SELECT * FROM test

DROP VIEW

DROP VIEW ViewName

Ex: Drop View test

Tuesday, March 12, 2013

How to Enable Remote Access in sql server 2008

 

To resolve following error , follow the steps specified below:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

OR

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 28 - server does not support requested protocol)

Check the following things if remote access is failed :

1)Allow Remote Connections to this server

This must be enabled to allow remote connections to the server. To enable this , Go to start – > All Programs – > Microssft Sql Server 2008 –> Sql Server Management Studio.

mngStudio

Now connect to the instance to which you need to enable remote access. Now right click on the instance and select “Properties”. Select “Connections” from left side and see if “Allow Remote Connections to this server” is checked or not. If unchecked , check it to allow remote access.

Allowremote1

Allowremote2

2)Enable TCP/IP protocol and Sql Server Browser

Open Sql Server Configuration Manager.

Go to start – > All Programs – > Microssft Sql Server 2008 –> Configuration Tools –> Sql server configuration Manager

Configmanager

TCP/IP protocol

Expand “Sql Server Network Configuration” in the left pane , Double click on “Protocols for MSSQLSERVER” ( Protocols for YourServerInstanceName) . Enable TCP/IP .

tcp enable1

Some times TCP/IP under Sql Native Client 10.0 Configuration –>Client Protocols –>TCP/IP  might also be disabled . Check this and enable it.

tcp enable2

It gives the following message ,

warning 

so restart the sql server service . under Sql Server Configuration Manager –> Sql Server Services –> Sql Server (Your Instance Name)

Sql Server Browser

under Sql Server Configuration Manager –> Sql Server Services –>Sql Server Browser

Right click and enable it.

SqlBrowser

4)Firewall

In Start –> Run , give the command “controlpanel.cpl” and click enter.

or

Start – > Control Panel –> Windows Firewall - >Allow a program or feature through firewall

Browse “Sqlserver.exe” and add.

firewall_1

firewall_2

firewall_3

Friday, March 8, 2013

FULLTEXT INDEX / FULLTEXT SEARCH

 

Use FullText Search to run full-text queries against character-based data in SQL Server tables . To run full-text queries on a table, one needs to create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more columns from the table, and each column can use a specific language.

For more information on FullText Index , see the following link

http://msdn.microsoft.com/en-IN/library/ms142571.aspx

How to Check if FullText Index is enabled in SQL Server :

Execute the following query to verify if “FullText Indexing” is installed or not in the server.

select SERVERPROPERTY('IsFullTextInstalled')

If the query returns 1 , then FullText Indexing is installed and one can create FullText Index on the required table. If it returns 0 , then its not installed , and one needs administrative privileges to install it.

CREATE FULLTEXT INDEX ON A TABLE :

1)Before creating FullText Index on a table , one needs to create

- a Unique Index on that table and

- Catalog for FullText Index.

2)Syntax for creating Full Text Index

CREATE FULLTEXT INDEX ON table_name
[ ( { column_name   TYPE COLUMN type_column_name ] 
                     [ LANGUAGE language_term ]
                     [ STATISTICAL_SEMANTICS ]
} [ ,...n]       ) ]   
KEY INDEX index_name   [ ON <catalog_filegroup_option> ]
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
[;]

Sample code for creating and dropping Full Text Index on a table :

/*CREATE TABLE*/
CREATE TABLE CandidateData
(
CandidateId INT NOT NULL,
Resume VARCHAR(MAX)
)
ON [PRIMARY]
GO

/*CREATE UNIQUE INDEX ON THE TABLE*/
CREATE UNIQUE CLUSTERED INDEX PK_CandidateData ON CandidateData(CandidateId);

/*CREATE FULLTEXT CATALOG*/
CREATE FULLTEXT CATALOG ft AS DEFAULT;

/*CREATE FULLTEXT INDEX*/
CREATE FULLTEXT INDEX ON CandidateData(Resume)
   KEY INDEX PK_CandidateData
   ON ft WITH CHANGE_TRACKING AUTO;

/*START FULLTEXT SEARCH*/
SELECT * FROM CandidateData WHERE CONTAINS(Resume, 'SQL SERVER')

/*DROP FULL TEXT INDEX*/

DROP FULLTEXT INDEX ON CandidateData

Note : Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE)

Wednesday, March 6, 2013

Example using RequiredFieldValidator ,CompareValidator , RegularExpressionValidator in Registration From

 

Aspx Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RegistrationForm_withvalidations.aspx.cs" Inherits="Forum_Topics_RegistrationForm_withvalidations" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Enter Name:<asp:TextBox id="txtName" runat="server"></asp:TextBox>
    <br />
    <asp:RequiredFieldValidator ID="RFV1" runat="server" ControlToValidate="txtName" ErrorMessage="Name is must"></asp:RequiredFieldValidator>
    <br />
    Enter Password:
    <asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>
    <br />
    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtPwd" ErrorMessage="Password is must"></asp:RequiredFieldValidator>
    <br />
    Confirm Password:
    <asp:TextBox ID="txtConfirmPwd" runat="server"></asp:TextBox>
    <br />
    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtConfirmPwd" ErrorMessage="Confirm Password is must"></asp:RequiredFieldValidator>
    <br />
    <asp:CompareValidator ID="CV1" runat="server" ControlToValidate ="txtConfirmPwd" ControlToCompare ="txtPwd" ErrorMessage="Password and Confirm Password does not match">
    </asp:CompareValidator>
    <br />
     Enter Email - ID :<asp:TextBox ID="txtEmail" runat="server" MaxLength="50" Width="140px"></asp:TextBox>
     <br />
     <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtEmail" ErrorMessage="Email is must"></asp:RequiredFieldValidator>
     <br />
       <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
           ControlToValidate="txtEmail" ErrorMessage="*Invalid Email."
           ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
    <br />
    <asp:Button ID="btnSubmit" runat="server" Text="Submit" />
    </div>
    </form>
</body>
</html>

Output :

Validator_op

validator_op1

Tuesday, March 5, 2013

Named Pipes Provider: Could not open a connection to SQL Server

 

This failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Follow the steps in the link to resolve this error.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Create Linked Server in Sql Server 2008 / Insert from one Db to another in Sql Server 2008

Linked Servers can be used to connect databases on remote server .

Suppose in the production environment if you have two servers on the network and we need to trigger insert in a table of one database to a table in different database , we can use Linked Servers.

We can create Linked Server using the Stored Procedure “sp_addlinkedserver” .To execute this , we must have the admin privileges. One has to login into sql server management studio with “Sa” user.

exec sp_addlinkedserver
    @server = 'MyLinkedServer', -- LINKED SERVER NAME (ANYTHING)
    @srvproduct = '' ,
    @provider = 'SQLNCLI', -- SQL SERVER PROVIDER .. SO SQLNCLI
    @datasrc ='SERVER=11.11.1.1', -- REMOTE DATABASE SEREVR IP ADDRESS
    @location = '' ,
    @provstr = '',
    @catalog = 'Test' -- DATABASE ON REMOTE SERVER


                  OR
exec sp_addlinkedserver 'MyLinkedServer','','SQLNCLI','SERVER=11.11.1.1','','','Test'

We can also create Linked Server from Sql Server Management Studio by following these steps :

Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect with Sa login.

In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

                                                                                                                                           CreateLinSer

In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Server you want to link to.

LinkSerPage

Under general , Choose the following things :

General

In the left pane of the New Linked Server dialog,  choose Security and provide remote user name and password .

You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

Security

In the left pane of the New Linked Server dialog, choose Server Options.   Set the Rpc and Rpc Out parameters to True and then click OK .

ServerOptions

To verify Linked Server is created or not :

using the Stored Procedure sp_linkedservers

                                                   exec sp_linkedservers

                                                                 OR

1) In the object explorer , expand Server Objects , expand Linked Servers . Now verify for the linked server created by you.

2)To test the connection , right click on the linked server created and select Test Connection .  If connection is successful , then you can perform all DML operations on the tables in the remote database.

Created

Querying Linked Server :

SELECT * FROM [LinkedServerName].[RemoteDatabaseName].[DBOwner].TableName

Similarly use Insert , Delete and Update queries. We can call functions , execute SPs etc.

Delete / Drop Linked Server :

1) In the object explorer , expand Server Objects , expand Linked Servers . Right Click on the Linked Server which you wish to delete and click Delete.

2)Drop Linked Server using Stored Procedure “sp_dropserver”

                          EXEC sp_dropserver MyLinkedServer

Monday, February 25, 2013

How to create DLL in asp.net(C#)

 

Steps to create DLL (Dynamic Linking Library)

1)Open Visual Studio , Select “New Project”

2)From the templates , choose “ClassLibrary” and give a name to it. (say “Calculatordll”)

createdll

3)It creates a namespace named “Claculatordll” and a class by default. Now write the set of code which you want make as DLL. We can have more than one class here.

namespace created

4)For now I will create two classes. One with calculator functionalities ( such as add , sub , mul etc) and the other class with display function.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CalculatorDll
{
    public class CalculatorFuncions
    {
        public int add(int a, int b)
        {
            return (a + b);
        }
        public int sub(int a, int b)
        {
            return (a - b);
        }
        public int multiply(int a, int b)
        {
            return (a * b);
        }
    }
    public class DisplayFunctions
    {
        public string Display(string str)
        {
            return str;
        }
    }
}

5)Now build the project . DLL will be created in the bin folder.

Find the dll in bin folder of the project. ( bin –> Release –> CalculatorDll.dll )

buildproject             bin folder   

 dll created

Now lets see how to use this dll in another project or web application.

It can be done in 3 ways.

1)Right click on the solution and select “add reference” . the following window appears . Browse for the calculator dll created just now and click ok.

add web reference         

browse dll

Sample code which uses the calculatordll

Aspx Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="UseDLL._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>USING DLL</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
        <tr>
            <td>
                Enter a:
            </td>
            <td>
                <asp:TextBox ID="txta" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Enter b:
            </td>
            <td>
                <asp:TextBox ID="txtb" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" />
                <asp:Button ID="btnSub" runat="server" Text="Subtract" OnClick="btnSub_Click" />
                <asp:Button ID="btnMul" runat="server" Text="Multiply" OnClick="btnMul_Click" />
            </td>
        </tr>
        <tr>
            <td>
                Result :
            </td>
            <td>
                <asp:TextBox ID="txtResult" runat="server"></asp:TextBox>
            </td>
        </tr>
        </table>
    </div>
    </form>
</body>
</html>

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace UseDLL
{
    public partial class _Default : System.Web.UI.Page
    {
        /*CREATE AN OBJECT FOR THE CLASS IN DLL AND INVOKE FUNCTIONS IN THAT CLASS*/
        CalculatorDll.CalculatorFuncions Calc = new CalculatorDll.CalculatorFuncions();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            txtResult.Text = Convert.ToString(Calc.add(Int32.Parse(txta.Text), Int32.Parse(txtb.Text)));
        }

        protected void btnSub_Click(object sender, EventArgs e)
        {
            txtResult.Text = Convert.ToString(Calc.sub(Int32.Parse(txta.Text), Int32.Parse(txtb.Text)));
        }

        protected void btnMul_Click(object sender, EventArgs e)
        {
            txtResult.Text = Convert.ToString(Calc.multiply(Int32.Parse(txta.Text), Int32.Parse(txtb.Text)));
        }
    }
}

Output :

op1            op2

2)Add this namespace to Web.config file in your application .

In System.web tag , go to pages tag and the write the following code in “controls” tag to access this dll in your application.

<system.web>
    <pages>
            <controls>
              <add namespace="CalculatorDll" assembly ="CalculatorDll" tagPrefix="CalculatorDll"/>
            </controls>
        </pages>
</system.web>

Now to access this namespace , add “using ClaculatorDll;” to your code behind code.

3) If you want to use it in a single page , then we can register this namespace using @register directive.

Reading from Text File (C#)

 

Sample code to read data from text file : use “System.IO” namespace to work with file Input Output Operations

Aspx Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SaveTextToFile.aspx.cs" Inherits="Santhakam_SaveTextToFile" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>READING TEXT FILE</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>

              <tr>
            <td colspan="2">
                <asp:Button ID="btnReadFile" runat="server" Text="ReadFile" OnClick="btnReadFile_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Label ID="lblContents" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
                <asp:Label ID="lblFileContents" runat="server" Font-Bold="True" ForeColor="Black"></asp:Label>
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class Santhakam_SaveTextToFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected String ReadTextFile()
    {
        String FileContents = string.Empty;
        StreamReader ObjReader;
        try
        {
            ObjReader = File.OpenText("C:\\Test.txt");
            FileContents = ObjReader.ReadToEnd();
            return FileContents;

        }
        catch(Exception ex)
        {
            return "Error";
        }
    }
   protected void btnReadFile_Click(object sender, EventArgs e)
    {
        lblContents.Text = "File Contents : ";
        lblFileContents.Text = ReadTextFile();
    }
}

Output:

ReadTextFile1         ReadTextFile2

Writing to a Text File (C#)

 

To perform file operations , use the namespace “using System.IO” .  StreamWriter class provides functions to write data to file and Stream Reader class provides functions to read data from file and to append data if required.

Sample code to write data to a Text File :

Aspx Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SaveTextToFile.aspx.cs" Inherits="Santhakam_SaveTextToFile" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SAVING TEXT TO FILE</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>
                Enter Text:
            </td>
            <td>
                <asp:TextBox ID="txtMsg" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <asp:Button ID="btnWriteTextToFile" runat="server" Text ="WriteTextToFile"  OnClick="btnWriteTextToFile_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <asp:Label ID="lblMsg" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

Code Behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class Santhakam_SaveTextToFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void WritetoTextFile()
    {
        //CREATE OBJECT FOR STREAMWRITER CLASS
        StreamWriter obj;
        try
        {
            /*CREATE TEXT FILE*/
            obj = File.CreateText("C:\\Sample.txt");
            /*WRITE DATA TO FILE*/
            obj.WriteLine(txtMsg.Text);
            obj.Flush();
            obj.Close();           
        }
        catch (Exception ex)
        {
            //ErrorPage.ToString();
        }

    }
   protected void btnWriteTextToFile_Click(object sender, EventArgs e)
    {
        WritetoTextFile();
        lblMsg.Text = "File has been created and text is saved to file Successfully";
    }
}

Output :

SaveTexttofile1      Savetexttofile2

Friday, November 23, 2012

PIVOT and UNPIVOT Operations in sql server

Introduction :

PIVOT turns unique values from one column in a table to multiple columns and also performs aggregation on other columns if required . UNPIVOT performs the reverse operation of PIVOT .
PIVOT does not actually change the structure of table in database , it only turns the data to view it from different perspectives.

Example using PIVOT and UNPIVOT :

Here I will take a simple example to illustrate PIVOT and UNPIVOT operations.
Let us suppose we have a table with the following information employee id  ,day in a week and working hours of employee. (Just to illustrate ,I have considered data of 5 days in a week , scenarios could be different )

Pivot_Op1
Now if we want to generate a report of an employee with his average or sum of working hours for all the days in a week  i.e if you want to turn the row information into columns as below , we can use PIVOT and to revert it use UNPIVOT.

Pivot_op2

Code – PIVOT Operation

BEGIN
    DECLARE @Emp_Attendance AS TABLE
    (
        EmpId INT ,
        [DAY] VARCHAR(10),
        WorkingHrs INT
    )
    INSERT INTO @Emp_Attendance VALUES(1,'MON',8)
    INSERT INTO @Emp_Attendance VALUES(1,'TUE',7)
    INSERT INTO @Emp_Attendance VALUES(1,'WED',4)
    INSERT INTO @Emp_Attendance VALUES(1,'THU',5)
    INSERT INTO @Emp_Attendance VALUES(1,'FRI',6)
    INSERT INTO @Emp_Attendance VALUES(2,'MON',6)
    INSERT INTO @Emp_Attendance VALUES(2,'TUE',9)
    INSERT INTO @Emp_Attendance VALUES(2,'WED',5)
    INSERT INTO @Emp_Attendance VALUES(2,'THU',8)
    INSERT INTO @Emp_Attendance VALUES(2,'FRI',4)
    /*PIVOT*/
    SELECT EmpId ,[Mon] ,[Tue],[Wed],[Thu],[Fri] FROM
    (SELECT EmpId ,DAY,WorkingHrs FROM @Emp_Attendance) AS A
    PIVOT
    (
    AVG(WorkingHrs)
    FOR DAY IN ([Mon] ,[Tue],[Wed],[Thu],[Fri])
    )AS B
END
GO

Code – UNPIVOT Operation
 

BEGIN
    DECLARE @Emp_Attendance AS TABLE
    (
        EmpId INT ,
        [DAY] VARCHAR(10),
        WorkingHrs INT
    )
    INSERT INTO @Emp_Attendance VALUES(1,'MON',8)
    INSERT INTO @Emp_Attendance VALUES(1,'TUE',7)
    INSERT INTO @Emp_Attendance VALUES(1,'WED',4)
    INSERT INTO @Emp_Attendance VALUES(1,'THU',5)
    INSERT INTO @Emp_Attendance VALUES(1,'FRI',6)
    INSERT INTO @Emp_Attendance VALUES(2,'MON',6)
    INSERT INTO @Emp_Attendance VALUES(2,'TUE',9)
    INSERT INTO @Emp_Attendance VALUES(2,'WED',5)
    INSERT INTO @Emp_Attendance VALUES(2,'THU',8)
    INSERT INTO @Emp_Attendance VALUES(2,'FRI',4)
    /*PIVOT*/
    SELECT EmpId ,[Mon] ,[Tue],[Wed],[Thu],[Fri] INTO #temp_Pivot FROM
    (SELECT EmpId ,DAY,WorkingHrs FROM @Emp_Attendance) AS A
    PIVOT
    (
    AVG(WorkingHrs)
    FOR DAY IN ([Mon] ,[Tue],[Wed],[Thu],[Fri])
    )AS B
    SELECT 'AFTER PIVOT'
    SELECT * FROM #temp_Pivot
    SELECT 'AFTER UNPIVOT'
    /*UNPIVOT*/
    SELECT EmpId , DAY,WorkingHrs FROM
    (SELECT EmpId,Mon,Tue,Wed,Thu,Fri FROM #temp_Pivot ) AS A
    UNPIVOT
    (
        WorkingHrs FOR DAY IN (Mon,Tue,Wed,Thu,Fri)
    ) AS B
END
GO

Output :

Pivot_op3

Tuesday, November 20, 2012

Common Table Expression

In Sql Server suppose you want to write a query to display numbers from 1 to 10 in sequence , then we either use while loop or cursor. If our requirement is for a simple task , then we can make use of cursors and loop. If it is little complex, then we have to write lot of code using a cursor . But by using CTE we can reduce number of lines of our code and make it simpler. To retrieve hierarchical data efficiently CTE can be used.

Sample Example – Common Table Expression

Example : Write a query to Output numbers from 1 to 10 in sequence.

/*DISPLAY LIST OF NUMBERS FROM 1 to 10*/

BEGIN

DECLARE @Start INT , @End INT

SET @Start = 1

SET @End = 10

;WITH Series AS

(

SELECT @Start AS List --INITIALISATION PART

UNION ALL

SELECT List + 1 FROM Series WHERE List < @End --RECURSION PART

)

SELECT * FROM Series

/*changing recursion limit

SELECT * FROM Series OPTION (MAXRECURSION 150)

*/

END

GO

Points to be remembered:

1) The previous statement must be terminated with a semicolon if we are using CTE , so place a semicolon before Common Table Expression.

2) By default Maximum No of recursions is limited to 100 . If the query undergoes more than 100 recursions, it throws an error.

3) If one want to change the maximum limit of transactions , then use OPTION (MAXRECURSION 200) in Select statement . Maximum recursion limit one can specify for integers is 32767.

4) To see intermediate output , then specify OPTION (MAXRECURSION @RecursionNo) . Suppose the query takes 10 recursions and we wish to see output after 2nd recursion , then include option(MAXRECURSION 2) in select statement.

Retrieve hierarchical data using CTE :

Example :We have employee table with 3 columns ( empid , empname , reportsto) employee id , name and his manager information. Now to generate report of an employee in hierarchy -- “to whom he reports and employees who report to him ( subordinates) and their subordinates also” .

 

BEGIN

DECLARE @TblEmployees AS TABLE

(

EmployeeID INT PRIMARY KEY ,

EmpName VARCHAR(100),

ReportsTo INT NULL

)

INSERT INTO @TblEmployees VALUES( 1 , 'Ram' , NULL)

INSERT INTO @TblEmployees VALUES( 2 , 'Raj' , NULL)

INSERT INTO @TblEmployees VALUES( 3 , 'Ravi' , NULL)

INSERT INTO @TblEmployees VALUES( 4 , 'Rakhi' , 1)

INSERT INTO @TblEmployees VALUES( 5 , 'Rajesh' , 2)

INSERT INTO @TblEmployees VALUES( 6 , 'Ravinder' , 3)

INSERT INTO @TblEmployees VALUES( 7 , 'Sailender' , 3)

INSERT INTO @TblEmployees VALUES( 8 , 'Vikram', 6)

INSERT INTO @TblEmployees VALUES( 9 , 'Tarun' , 7)

;WITH ReportingOfficers AS

(

SELECT EmployeeID,EmpName , ReportsTo FROM @TblEmployees WHERE EmployeeID = 3 --ReportsTo IS NULL

UNION ALL

SELECT E.EmployeeID , E.EmpName,E.ReportsTo FROM @TblEmployees E , ReportingOfficers M

WHERE M.EmployeeID = E.ReportsTo

)

SELECT * FROM ReportingOfficers

END

GO

Here i queried for employee id 3.so the result contains details of employee id 3 , his manager details and his subordinates , and sub- subordinates data.

Output :

Common Table Expression

Friday, November 16, 2012

Write text to Notepad using C#

 

In this article i will illustrate how to create text file , write and append text to it using C#.

Aspx Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SaveTextToaFile.aspx.cs" Inherits="Forum_Topics_SaveTextToaFile" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SAVING TEXT TO A FILE</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Write few words about you:
                </td>
                <td>
                    <asp:TextBox ID="txtAboutYou" runat="server" TextMode="MultiLine"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Button ID="btnSaveText" runat="server" Text="SaveText" OnClick="btnSaveText_Click" />           
                </td>
            </tr>
             <tr>
                <td colspan="2" align="left">
                <asp:Label ID="lblMsg" runat="server" ForeColor="Red" ></asp:Label>
                </td>           
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class Forum_Topics_SaveTextToaFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSaveText_Click(object sender, EventArgs e)
    {
        /*FILE NAME OR PATH TO FILE*/
        string Filename = "C:\\Users\\Administrator\\Desktop\\Profile.txt";
        //CHECK FOR FILE EXISTENCE
        if (!File.Exists(Filename))
        {
            //CREATE FILE
            StreamWriter txtFile = File.CreateText(Filename);
            txtFile.Close();
            //APPEND TEXT
            txtFile = File.AppendText(Filename);
            txtFile.WriteLine(txtAboutYou.Text);
            txtFile.Close();
        }
        else
            //APPEND TEXT
            File.AppendAllText(Filename, txtAboutYou.Text);

        lblMsg.Text = "Text is saved";
    }
}