Alter table in Oracle

Alter table to add column

CREATE Table Employee
(
Id int PRIMARY KEY ,
Name VARCHAR2(100)
);

ALTER TABLE Employee
ADD
(
Email VARCHAR2(50)
);

Alter table to drop constraint

CREATE TABLE Employee
(
Id INT NOT NULL,
Name VARCHAR2(100),
CONSTRAINT pk_Emploee_Id PRIMARY KEY (Id)
);

ALTER TABLE Employee
DROP CONSTRAINT pk_Emploee_Id
;

Alter table to add multiple columns

ALTER TABLE Employee
CREATE Table Employee
(
Id int PRIMARY KEY ,
Name VARCHAR2(100)
);

ADD
(
Email VARCHAR2(50),
Mobile VARCHAR2(20)
);

Create Table in Oracle

1. Simple Create table

CREATE TABLE Employee
(
Id INT ,
Name VARCHAR2(100)
);

2. Create table with primary key

CREATE TABLE Employee
(
Id INT primary key ,
Name VARCHAR2(100)
);

3. Create table with primary key constraint

CREATE TABLE Employee
(
Id INT NOT NULL,
Name VARCHAR2(100),
CONSTRAINT pk_Emploee_Id PRIMARY KEY (Id)
);

WCF Service Endpoint Basics

Service Endpoint Basics

All communication with a Windows Communication Foundation (WCF) service happens through the endpoints of the service. Endpoints allow clients to access the functionality offered by a WCF service. Every endpoint must be associated with an address, a binding, and a contract

Eg:

<endpoint address="" binding="wsHttpBinding" contract="IService">

ABCs of Endpoints


Address: The address for an endpoint is a unique Uniform Resource Locator (URL) that identifies the location of the service. The address should follow the Web Service Addressing (WS-Addressing) standard.
 
Binding: The binding determines how the service can be accessed. This means that the binding can specify not only the protocol used to access the service but an encoding method used to format the message contents.
 
Contract: The final element in the service endpoint is the contract. This identifies the operations exposed by the service, and it typically refers to the interface name, preceded by the project namespace. By including the namespace, you are using the fully qualified type name for the contract.

Message Contracts in WCF

Message Contracts


When developing your WCF services, Data contracts enable you to define the structure of the data that will be sent in the body of your SOAP messages, either in the inbound (request) messages or in the outbound (response) messages.

Message Contract Attributes are used to

A. control how the SOAP message body is structured and, ultimately, how it is serialized

B. Supply and access custom headers.



To define Message contracts, use the following attributes: MessageContract Attribute, MessageHeader Attribute, and MessageBodyMember Attribute.

The MessageContract Attribute: The MessageContract Attribute can be applied to classes and structures to define your own message structure.

The MessageHeader Attribute: The MessageHeader Attribute can be applied to members of a Message contract to declare which elements belong among the message headers.

The MessageBodyMemberAttribute: The MessageBodyMemberAttribute can be applied to members of your Message contracts to declare which elements belong within the message body.

Live Stock Market Quotes (NSE) in .NET(C#)

This program extracts live stock price information from yahoo website. It can be included in our application to get usage of NSE stock quotes.

I have tried a lot to find a webservice which give NSE stock quotes, unfortunately I didn't find any. So I came into this decision to make a program which uses other websites like yahoo, rediff and msn. Finally I got a solution by writing the following application.

This program will give you live NSE stock quotes.

  [DataContract]
    public class StockQuote
    {
        [DataMember]
        public decimal LastPrice { set; get; }

        [DataMember]
        public decimal PreviousClose { set; get; }

        [DataMember]
        public decimal Open { set; get; }

        [DataMember]
        public DateTime LastTraded { set; get; }

        [DataMember]
        public string LastTradedString { set; get; }

        [DataMember]
        public decimal DayLow { set; get; }

        [DataMember]
        public decimal DayHigh { set; get; }

        [DataMember]
        public decimal FiftyTwoWeekLow { set; get; }

        [DataMember]
        public decimal FiftyTwoWeekHigh { set; get; }


    }

    [DataContract]
    public enum Exchange
    {
        NSE, BSE
    }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.IO;

namespace StocksToBuy
{
    public class YahooStockQuote
    {
       

        public StockQuote GetQuote(string symbol, Exchange exchange)
        {
            try
            {
                string url = "http://in.finance.yahoo.com/q?s=";
                if (exchange == Exchange.NSE)
                    url += symbol.ToUpper() +
                    ".NS";
                else
                    url += symbol.ToUpper() +
                    ".BO";
                string webPage = null;
                HttpWebRequest webRequest;
                HttpWebResponse webResponse;
                webRequest = (HttpWebRequest)WebRequest.Create(url);
                webResponse = (HttpWebResponse)webRequest.GetResponse();
                Stream pageStream = webResponse.GetResponseStream();
                StreamReader sr = new StreamReader(pageStream);
                webPage = sr.ReadToEnd();
                pageStream.Close();
                sr.Close();
                webResponse.Close();
                int startIndex;
                int endIndex;
                int tempStartIndex;
                int tempEndIndex;
                string temp;
                // Last Traded Value 
                startIndex = webPage.IndexOf("Last Trade:</th><td class=\"yfnc_tabledata1\"><big><b>") + 52;
                endIndex = webPage.IndexOf("</b>", startIndex);
                temp = webPage.Substring(startIndex, endIndex - startIndex);
                tempStartIndex = temp.IndexOf('>') + 1;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string lastPrice = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);

                // Last Trade Time 
                startIndex = webPage.IndexOf("Trade Time:</th><td class=\"yfnc_tabledata1\">", endIndex) + 44;
                endIndex = webPage.IndexOf("</td>", startIndex);
                temp = webPage.Substring(startIndex, endIndex - startIndex);
                tempStartIndex = temp.IndexOf('>') + 1;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string lastTradeTime = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);

                // Previous close 
                startIndex = webPage.IndexOf("Prev Close:</th><td class=\"yfnc_tabledata1\">", endIndex) + 44;
                endIndex = webPage.IndexOf('<', startIndex);
                string previousClose = webPage.Substring(startIndex, endIndex - startIndex);

                //Open price 
                startIndex = webPage.IndexOf("Open:</th><td class=\"yfnc_tabledata1\">", endIndex) + 38;
                endIndex = webPage.IndexOf('<', startIndex);
                string open = webPage.Substring(startIndex, endIndex - startIndex);

                //Day Low and Day High 
                startIndex = webPage.IndexOf("Day's Range:</th><td class=\"yfnc_tabledata1\">", endIndex) + 45;
                endIndex = webPage.IndexOf("</td>", startIndex);
                temp = webPage.Substring(startIndex, endIndex - startIndex);
                tempStartIndex = temp.IndexOf("\">") + 2;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string dayLow = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);
                tempStartIndex = temp.IndexOf("\">", tempEndIndex) + 2;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string dayHigh = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);

                //52 Week Low and 52 Week High 
                startIndex = webPage.IndexOf("Range:</th><td class=\"yfnc_tabledata1\"><", endIndex) + 39;
                endIndex = webPage.IndexOf("</td>", startIndex);
                temp = webPage.Substring(startIndex, endIndex - startIndex);
                tempStartIndex = temp.IndexOf('>') + 1;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string Fifty2WeekLow = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);
                tempStartIndex = temp.IndexOf('>', temp.IndexOf('-')) + 1;
                tempEndIndex = temp.IndexOf('<', tempStartIndex);
                string Fifty2WeekHigh = temp.Substring(tempStartIndex, tempEndIndex - tempStartIndex);




                return new StockQuote
                {
                    LastPrice = Convert.ToDecimal(lastPrice),
                    DayHigh = Convert.ToDecimal(dayHigh),
                    DayLow = Convert.ToDecimal(dayLow),
                    FiftyTwoWeekHigh = Convert.ToDecimal(Fifty2WeekHigh),
                    FiftyTwoWeekLow = Convert.ToDecimal(Fifty2WeekLow),
                    LastTraded = Convert.ToDateTime(lastTradeTime),
                    LastTradedString=lastTradeTime,
                    Open = Convert.ToDecimal(open),
                    PreviousClose = Convert.ToDecimal(previousClose)
                };
            }
            catch
            {
                return new StockQuote();
            }
        }
    }
}

SQL Index - Clustered Index and Non-Clustered Index

SQL INDEX


An index is a database object that, when created on a table, it can provide faster access paths to data and can facilitate faster query execution. Indexes are used to provide SQL Server with amore efficient method of accessing the data. Instead of always searching every data page in a table, an index facilitates retrieving specific rows without having to read a table’s entire content.

By default, rows in a regular un-indexed table aren’t stored in any particular order. A table in an order less state is called a heap. In order to retrieve rows from a heap based on a matching set of search conditions, SQL Server would have to read through all the rows in the table.

When an index is created, its index key data is stored in a B-tree structure. A B-tree structure starts with a root node, which is the beginning of the index. This root node has index data that contains a range of index key values that point to the next level of index nodes, called the intermediate leaf level. The bottom level of the node is called the leaf level. The leaf level differs based on whether the actual index type is clustered or non-clustered. If it is a clustered index, the leaf level is the actual data pages itself. If a non-clustered index, the leaf level contains pointers to the heap or clustered index data pages. A clustered index determines how the actual table data is physically stored. You can only designate one clustered index.

There are two types of indexes:

1. Clustered Indexes

2. Non clustered indexes

Clustered Index

A clustered index determines the physical order of the data in a table. Database Engine allows the creation of a single clustered index per table, because the rows of the table cannot be physically ordered more than one way. A clustered index is built by default for each table, for which you define the primary key using the primary key constraint.

When an index is created, its index key data is stored in a B-tree structure. The top level of the clustered index B-tree is known as the root node, the bottom level nodes are known as leaf nodes, and all nodes in between the root node and leaf nodes are collectively referred to as intermediate nodes. In a clustered index, the leaf nodes contain the actual data rows for a table, and all leaf nodes point to the next and previous leaf nodes, forming a doubly linked list. The clustered index holds a special position in SQL Server indexing: because its leaf nodes contain the actual table data, there can only be one clustered index defined per table.

                                            Figure: Clustered index B-tree structure


Eg:

Let’s take a look at a visualization of what a B-Tree looks like for a clustered index.

Non Clustered Index

A non-clustered index has the same index structure as a clustered index, with two important differences:

• A non-clustered index does not change the physical order of the rows in the table.

• Unlike clustered indexes, however, each leaf node in a non-clustered index contains the non-clustered key value and a row locator.



The physical order of rows in a table will not be changed if one or more non-clustered indices are defined for that table. For each non-clustered index, Database Engine creates an additional index structure that is stored in index pages. Non-clustered indexes are based on order of the data, but do not physically sort the data.

If a table has a clustered index, all non-clustered indexes defined on the table automatically include the clustered index columns as the row locator. If the table is a heap, SQL Server creates row locators to the rows from the combination of the file identifier, page number, and slot on the page.

                                             Figure: Non-clustered index B-tree structure

Let’s take a look at a visualization of what a B-Tree looks like for a non-clustered index