Linked Server between SQL 2005 and Oracle

SQL Server Linked Servers feature grants access to Oracle data and data from other OLEDB/ODBC compatible data sources from SQL Server.

1. Install and Configure the Oracle Client Software
Oracle client software provides the network libraries required to establish connectivity to an Oracle Database system.Download the software from http://www.oracle.com/technology/software/products/database/oracle10g/index.html. Install the software on your SQL Server system and configure it by using Oracle Net Configuration Assistant.

2. Create a Linked Server

Create a linked server by executing the following command

EXEC sp_addlinkedserver 
  'OracleLinkedServer', 'Oracle', 
  'MSDAORA', 'OracleServer'

3. Add Logins for the Linked Server
Next, provide the SQL Server system with an Oracle login to access the Oracle database by using the sp_addlinkedsrvlogin command

EXEC sp_addlinkedsrvlogin ' 
  OracleLinkedServer ', false, 
  'SQLuser', 'OracleUser', 
  'OraclePwd' 

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the domain\ to specify a Windows login. The fourth and fifth parameters supply login and password values for the Oracle system.

4. Query the Linked Server 
To test the connection, run a sample query using the linked server name. Linked servers support updates as well as queries.To access the tables on a linked server, use a four-part naming syntax:linked_server_name.catalog_ name.schema_name.table_name. For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM 
  OracleLinkedServer..SCOTT.EMP 

Random Password Generator in C#

public static string GetRandomPassword(int length)
{
 char[] chars = "$%#@!*abcdefghijklmnopqrstuvwxyz1234567890?;:ABCDEFGHIJKLMNOPQRSTUVWXYZ^&".ToCharArray();
string password = string.Empty;
Random random = new Random();
 for (int i = 0; i < length; i++)
{
int x = random.Next(1,chars.Length);
if (!password.Contains(chars.GetValue(x).ToString()))
password += chars.GetValue(x);
else i--;
 }
 return password;
}

Save Points in SQL Stored Procedures

CREATE TABLE #TestTable


(

Col1 INT,

Col2 VARCHAR(2),

Col3 VARCHAR(2)

)

GO

BEGIN TRANSACTION

INSERT INTO #TestTable

VALUES (1,'a','b');

SAVE TRANSACTION Sav1

GO

BEGIN TRANSACTION

UPDATE #TestTable SET Col2 = 'c'

WHERE Col1 = 1;

COMMIT TRANSACTION

ROLLBACK TRANSACTION Sav1;

COMMIT TRANSACTION

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction

SOAP vs REST

I have never been a part of long debates on Using SOAP vs REST but i have heard technocrats fighting over choosing the best suited services. Though I have never experimented over REST services but I could figure out the rational behind using different feelers for web services. At GrapeCity people are loud about REST services and  its like a new kid on the first floor of my office space.

SOAP

SOAP stands for Simple Object Access Protocol developed by Microsoft last century.

With WSDL and XML SOAP has become the best and most secure means of transfer of XML messages over the web. The structure of SOAP is like a postal message, It has an Envelope, Header and a Body.

For example: A normal SOAP message would look like

<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">

 <env:Header>

     <!-- Header -->

 </env:Header>

<env:Body>

    <!-- Body -->

 </env:Body>

</env:Envelope>

 

Example of a Simple SOAP Message to fetch Stock Prices:

Request:

GET /StockPrice HTTP/1.1

Host: example.org

Content-Type: application/soap+xml; charset=utf-8

Content-Length: nnn

<?xml version="1.0"?>

<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"

   xmlns:s="http://www.example.org/stock-service">

   <env:Body>

     <s:GetStockQuote>

          <s:TickerSymbol>IBM</s:TickerSymbol>

     </s:GetStockQuote>

   </env:Body>

</env:Envelope>

 Response:

HTTP/1.1 200 OK

Content-Type: application/soap+xml; charset=utf-8

Content-Length: nnn

<?xml version="1.0"?>

<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"

   xmlns:s="http://www.example.org/stock-service">

   <env:Body>

     <s:GetStockQuoteResponse>

          <s:StockPrice>45.25</s:StockPrice>

     </s:GetStockQuoteResponse>

   </env:Body>

</env:Envelope>

********************

REST

REST Services are used for Point to Point communication on the Web.

REST has four actions derived from HTTP (GET/POST/PUT/DELETE)

The web service message to fetch Stock Prices can be re written using REST

Request:

GET /StockPrice/IBM HTTP/1.1

Host: example.org

Accept: text/xml

Accept-Charset: utf-8

Response:

HTTP/1.1 200 OK

Content-Type: text/xml; charset=utf-8

Content-Length: nnn

<?xml version="1.0"?>

<s:Quote xmlns:s="http://example.org/stock-service">

     <s:TickerSymbol>IBM</s:TickerSymbol>

     <s:StockPrice>45.25</s:StockPrice>

</s:Quote>

********************

SOAP 

Pros:

• Langauge, platform, and transport independent
• Designed to handle distributed computing environments
• Is the prevailing standard for web services, and hence has better support from other standards (WSDL, WS-*) and tooling from vendors
• Built-in error handling (faults)
• Extensibility

Cons:

• Conceptually more difficult, more "heavy-weight" than REST
• More verbose
• Harder to develop, requires tools

REST 

Pros:

• Language and platform independent
• Much simpler to develop than SOAP
• Small learning curve, less reliance on tools
• Concise, no need for additional messaging layer
• Closer in design and philosophy to the Web

Cons:

• Assumes a point-to-point communication model--not usable for distributed computing environment where message may go through one or more intermediaries
• Lack of standards support for security, policy, reliable messaging, etc., so services that have more sophisticated requirements are harder to develop ("roll your own")
• Tied to the HTTP transport model

 

I found an interesting blog article on REST Services.

For more information http://www.xfront.com/REST-Web-Services.html

Understanding .NET RIA Services (WCF RIA Services) Architecture

I am very new to this interesting technology and I thought to have a bite too. I have seen many Silverlight geeks talking about RIA services and I felt why are people so crazy and all the time discussing the same topic from Cabs to Washrooms.  Only when I tried my hands on this technology only then I realized the potential of  RIA Services.
".NET RIA Services now popular as WCF RIA Services is a service which bridges the gaps between multiple tiers in any Rich Internet Application such as Silverlight. A common problem when developing an n-tier RIA solution is coordinating application logic between the middle tier and the presentation tier. RIA Services solves this problem by providing framework components, tools, and services that make the application logic on the server available to the RIA client."
The RIA Services provides a pattern to write application logic that runs on the mid-tier and controls access to data for queries, changes and custom operations. It also provides end-to-end support for common tasks such as data validation, authentication and roles by integrating with Silverlight components on the client and ASP.NET on the mid-tier.

RIA Services follows a simple end to end pattern for the flow of business logic across the layers. It models a N tier application into a 2 tier application side by side keeping the legacy of N tier applications alive by maintaining trusted boundaries.

The first part is to write a Domain Service class. The application logic resides in this class.

Domain Service Class  surfaces the set of data a client can see, also specifies rules around the data and the operations such as authorization and validation. 

Domain Service Class is stateless i.e. it responds to query requests and change set requests.

The second part of the pattern is to write a client side Data Model which is known as Domain Context class.This class represents the view of what the client can see. It contains lists of objects, one for each type exposed to the client, and it contains a set of load methods that roughly correspond to the queries exposed on the service. These load methods can be used to load objects into the lists. Domain Context class extracts all the changes made to the data at the client side, creates a list of changes and submits it to the Domain Service class for committing. DomainContext is optimized for taking advantage of the stateful environment and helps in binding client side services like Silverlight and WPF.

Domain Services resides on the server and communicates through REST(Representational state transfer) or SOAP(Simple Object Access Protocol) using XML or JSON.

Domain Context on the client is like a proxy which knows the rules and semantics of Domain Service class.

The Domain Service can work with different data sources from relational databases to CLR types to Azure Cloud Data sources.The Domain Service is a reusable and tangible component which can jell with any Presentation Technology to provide data.

RIA Services can be used in developing Rich Internet Applications under multiple scenarios. One of the scenario can be when a single RIA needs to access few domain services in the middle tier. Another scenario might be when multiple RIA need to access a single domain service in the middle tier.

Irrespective of the scenario there always rests a link known as (RIA Service Link) between middle tier and the presentation tier. This is a special kind of P2P(Project to Project) link which helps to generate Presentation tier logic in the middle tier.

In my next post I will talk more about RIA Services Solution Structures and how to create RIA Services through Visual Studio Interface.

Javascript to show a division dynamically

//
//function createDiv()
//{
//var divTag = document.createElement("div");
//divTag.id = "div1";
//divTag.setAttribute("align","center");
//divTag.style.margin = "0px auto";
//divTag.innerHTML = "Piyush James Bond has created a Division";
//document.body.appendChild(divTag);
//}
//

Steps to apply Code Access Security Policy in Sharepoint Solution