Get definition of system or user created objects( Stored Procedures, functions, views ) in SQL Server

In SQL Server 2005 or 2008 we can easily get the definition of user created functions, stored procedures as well as system views, procedures etc. 

There are three ways to get the definition of system views,procedures as well as user created objects. 

1. Using  OBJECT_DEFINITION function
2. Using sys.system_sql_modules view ( Only returns system views,procedures etc)
3. Using sp_helptext procedure

We will explore each one by one.

1) Using OBJECT_DEFINITION :

Using OBJECT_DEFINITION function we can get the source text of definition of the specified object.

Syntax:

OBJECT_DEFINITION function takes one parameter object_id. It is the ID of the object and it's datatype is int and assumed to represent an object in the current database context. It's return type is nvarchar(max).

Example of System Stored Procedure:

select OBJECT_DEFINITION(object_id('sp_databases')) 

Output:
  create procedure sys.sp_databases  as      set nocount on        select          DATABASE_NAME   = db_name(s_mf.database_id),          DATABASE_SIZE   = convert(int,                                      case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...                                      when convert(bigint, sum(s_mf.size)) >= 268435456                                      then null                                      else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb                                      end),          REMARKS         = convert(varchar(254),null)      from          sys.master_files s_mf      where          s_mf.state = 0 and -- ONLINE          has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access      group by s_mf.database_id      order by 1  

Example of System Views:

We can get the definition of sys.tables system view as

select OBJECT_DEFINITION(object_id('sys.tables'))

Output:
CREATE VIEW sys.tables AS   SELECT o.name, o.object_id, o.principal_id, o.schema_id, o.parent_object_id,    o.type, o.type_desc, o.create_date, o.modify_date,    o.is_ms_shipped, o.is_published, o.is_schema_published,    lob.lobds AS lob_data_space_id,    rfs.indepid AS filestream_data_space_id,    o.property AS max_column_id_used,    o.lock_on_bulk_load, o.uses_ansi_nulls, o.is_replicated, o.has_replication_filter,    o.is_merge_published, o.is_sync_tran_subscribed, o.has_unchecked_assembly_data,    lob.intprop AS text_in_row_limit,    o.large_value_types_out_of_row,    o.is_tracked_by_cdc,    o.lock_escalation_option AS lock_escalation,    ts.name AS lock_escalation_desc   FROM sys.objects$ o   LEFT JOIN sys.sysidxstats lob ON lob.id = o.object_id AND lob.indid <= 1   LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.object_id AND rfs.class = 42 AND rfs.depsubid = 0 -- SRC_OBJTOFSDS   LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option   WHERE o.type = 'U' 

In similar manner we can get the definition of sys.columns, sys.views, sys.databases etc.

Now I will show you some examples using Adventure Works database.

Example of Stored Procedure:

select OBJECT_DEFINITION(object_id('uspGetEmployeeManagers'))

Output:
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]  @EmployeeID [int]  AS  BEGIN  SET NOCOUNT ON;  WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns  AS (  SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee  FROM [HumanResources].[Employee] e   INNER JOIN [Person].[Contact] c   ON e.[ContactID] = c.[ContactID]  WHERE e.[EmployeeID] = @EmployeeID  UNION ALL  SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor  FROM [HumanResources].[Employee] e   INNER JOIN [EMP_cte]  ON e.[EmployeeID] = [EMP_cte].[ManagerID]  INNER JOIN [Person].[Contact] c   ON e.[ContactID] = c.[ContactID]  )  SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName],   [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE  FROM [EMP_cte]   INNER JOIN [HumanResources].[Employee] e   ON [EMP_cte].[ManagerID] = e.[EmployeeID]  INNER JOIN [Person].[Contact] c   ON e.[ContactID] = c.[ContactID]  ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]  OPTION (MAXRECURSION 25)   END; 

Example of View:

select OBJECT_DEFINITION(object_id('HumanResources.vEmployeeDepartment'))

Output:
CREATE VIEW [HumanResources].[vEmployeeDepartment]   AS   SELECT   e.[EmployeeID]   ,c.[Title]   ,c.[FirstName]   ,c.[MiddleName]   ,c.[LastName]   ,c.[Suffix]   ,e.[Title] AS [JobTitle]   ,d.[Name] AS [Department]   ,d.[GroupName]   ,edh.[StartDate]   FROM [HumanResources].[Employee] e  INNER JOIN [Person].[Contact] c   ON c.[ContactID] = e.[ContactID]  INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh   ON e.[EmployeeID] = edh.[EmployeeID]   INNER JOIN [HumanResources].[Department] d   ON edh.[DepartmentID] = d.[DepartmentID]   WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE()); 

Example of Table-valued function:

select OBJECT_DEFINITION(object_id('ufnGetContactInformation'))

Output:
CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int)  RETURNS @retContactInformation TABLE   (  [ContactID] int PRIMARY KEY NOT NULL,   [FirstName] [nvarchar](50) NULL,   [LastName] [nvarchar](50) NULL,   [JobTitle] [nvarchar](50) NULL,   [ContactType] [nvarchar](50) NULL  )  AS   BEGIN  DECLARE   @FirstName [nvarchar](50),   @LastName [nvarchar](50),   @JobTitle [nvarchar](50),   @ContactType [nvarchar](50);  SELECT   @ContactID = ContactID,   @FirstName = FirstName,   @LastName = LastName  FROM [Person].[Contact]   WHERE [ContactID] = @ContactID;  SET @JobTitle =   CASE   WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e   WHERE e.[ContactID] = @ContactID)   THEN (SELECT [Title]   FROM [HumanResources].[Employee]   WHERE [ContactID] = @ContactID)  WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc   INNER JOIN [Person].[ContactType] ct   ON vc.[ContactTypeID] = ct.[ContactTypeID]   WHERE vc.[ContactID] = @ContactID)   THEN (SELECT ct.[Name]   FROM [Purchasing].[VendorContact] vc   INNER JOIN [Person].[ContactType] ct   ON vc.[ContactTypeID] = ct.[ContactTypeID]   WHERE vc.[ContactID] = @ContactID)  WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc   INNER JOIN [Person].[ContactType] ct   ON sc.[ContactTypeID] = ct.[ContactTypeID]   WHERE sc.[ContactID] = @ContactID)   THEN (SELECT ct.[Name]   FROM [Sales].[StoreContact] sc   INNER JOIN [Person].[ContactType] ct   ON sc.[ContactTypeID] = ct.[ContactTypeID]   WHERE [ContactID] = @ContactID)  ELSE NULL   END;  SET @ContactType =   CASE   WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e   WHERE e.[ContactID] = @ContactID)   THEN 'Employee'  WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc   INNER JOIN [Person].[ContactType] ct   ON vc.[ContactTypeID] = ct.[ContactTypeID]   WHERE vc.[ContactID] = @ContactID)   THEN 'Vendor Contact'  WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc   INNER JOIN [Person].[ContactType] ct   ON sc.[ContactTypeID] = ct.[ContactTypeID]   WHERE sc.[ContactID] = @ContactID)   THEN 'Store Contact'  WHEN EXISTS(SELECT * FROM [Sales].[Individual] i   WHERE i.[ContactID] = @ContactID)   THEN 'Consumer'  END;  IF @ContactID IS NOT NULL   BEGIN  INSERT @retContactInformation  SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;  END;  RETURN;  END; 

2) Using sys.system_sql_modules view :
We can get the definition of all the system views, procedures by running the below query

select * from sys.system_sql_modules

-- This query will return 1880 rows having 9 Columns. I am showing only one row with two important columns.
Output:

object_id
-1073624922
definition
create procedure sys.sp_MSalreadyhavegeneration      (@genguid uniqueidentifier,        @subscribernick binary(6),       @compatlevel int = 10) -- backward compatibility level, default=Sphinx    as      declare @nicknames varbinary(1000)      declare @offset int      declare @retcode int      declare @dbname sysname            -- Security Checking       -- PAL users have access      exec @retcode = sys.sp_MSrepl_PAL_rolecheck      if (@retcode <> 0) or (@@error <> 0)          return 1                if (@genguid is null)      begin          RAISERROR(14043, 16, -1, '@genguid', 'sp_MSalreadyhavegeneration')          return (1)      end        select @nicknames = nicknames from dbo.MSmerge_genhistory where guidsrc = @genguid      if @nicknames is null      begin          set @dbname= db_name()          -- what is the appropriate error to return?          RAISERROR(21333, 16, -1, @dbname)          return (1)      end        if @compatlevel < 90          set @subscribernick= substring(@subscribernick,1,4) + 0x0000        declare @replnicklength int      set @replnicklength= col_length('sysmergesubscriptions', 'replnickname')      set @offset= 1      while @offset < DATALENGTH(@nicknames)      begin          -- If the subscriber nickname is already in the list just return          if substring(@nicknames, @offset, @replnicklength) = @subscribernick              return (0)          set @offset = @offset + @replnicklength      end            -- Subscriber nickname is not in the array.  Add it so that we won't send this gen      -- back down in the return message.  (Would waste bandwidth...)        -- Append guard byte          set @offset = DATALENGTH(@nicknames)      if substring(@nicknames, @offset, 1) <> 0xFF          begin                  set @nicknames = @nicknames + 0xFF          end        if @nicknames = 0x0          set @nicknames = @subscribernick + 0xFF      else          set @nicknames = @subscribernick + @nicknames        update dbo.MSmerge_genhistory set nicknames = @nicknames where guidsrc = @genguid            return (0)  

We can also get the definition of only one view or procedure as

select definition from sys.system_sql_modules where [object_id] = ( select OBJECT_ID('sys.columns') )

Output:
CREATE VIEW sys.columns AS   SELECT id AS object_id,    name, colid AS column_id,    xtype AS system_type_id,    utype AS user_type_id,    length AS max_length,    prec AS precision, scale,    convert(sysname, CollationPropertyFromId(collationid, 'name')) AS collation_name,    sysconv(bit, 1 - (status & 1)) AS is_nullable,  -- CPM_NOTNULL    sysconv(bit, status & 2) AS is_ansi_padded,  -- CPM_NOTRIM    sysconv(bit, status & 8) AS is_rowguidcol,   -- CPM_ROWGUIDCOL    sysconv(bit, status & 4) AS is_identity,   -- CPM_IDENTCOL    sysconv(bit, status & 16) AS is_computed,   -- CPM_COMPUTED    sysconv(bit, status & 32) AS is_filestream,   -- CPM_FILESTREAM    sysconv(bit, status & 0x020000) AS is_replicated,   -- CPM_REPLICAT    sysconv(bit, status & 0x040000) AS is_non_sql_subscribed, -- CPM_NONSQSSUB    sysconv(bit, status & 0x080000) AS is_merge_published, -- CPM_MERGEREPL    sysconv(bit, status & 0x100000) AS is_dts_replicated, -- CPM_REPLDTS    sysconv(bit, status & 2048) AS is_xml_document,  -- CPM_XML_DOC     xmlns AS xml_collection_id,    dflt AS default_object_id,    chk AS rule_object_id,    sysconv(bit, status & 0x1000000) AS is_sparse, -- CPM_SPARSE    sysconv(bit, status & 0x2000000) AS is_column_set -- CPM_SPARSECOLUMNSET   FROM sys.syscolpars   WHERE number = 0 AND has_access('CO', id) = 1  

3) Using sp_helptext procedure :
It displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure. It also returns the definition in multiple rows.
Syntax:
It accepts two parameters; first one is object_id and the second one is computed column. 

Examples:
Displaying the definition of a View:

USE AdventureWorks;
GO
EXEC sp_helptext 'HumanResources.vEmployee';

Output:
CREATE VIEW [HumanResources].[vEmployee]   
AS   
SELECT   
e.[EmployeeID]  
,c.[Title]  
,c.[FirstName]  
,c.[MiddleName]  
,c.[LastName]  
,c.[Suffix]  
,e.[Title] AS [JobTitle]   
,c.[Phone]  
,c.[EmailAddress]  
,c.[EmailPromotion]  
,a.[AddressLine1]  
,a.[AddressLine2]  
,a.[City]  
,sp.[Name] AS [StateProvinceName]   
,a.[PostalCode]  
,cr.[Name] AS [CountryRegionName]   
,c.[AdditionalContactInfo]  
FROM [HumanResources].[Employee] e  
INNER JOIN [Person].[Contact] c   
ON c.[ContactID] = e.[ContactID]  
INNER JOIN [HumanResources].[EmployeeAddress] ea   
ON e.[EmployeeID] = ea.[EmployeeID]   
INNER JOIN [Person].[Address] a   
ON ea.[AddressID] = a.[AddressID]  
INNER JOIN [Person].[StateProvince] sp   
ON sp.[StateProvinceID] = a.[StateProvinceID]  
INNER JOIN [Person].[CountryRegion] cr   
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];  

Displaying the definition of a computed column :
The following example displays the definition of the computed column TotalDue on the SalesOrderHeader table in the AdventureWorks database.

USE AdventureWorks;
GO
sp_helptext @objname = N'AdventureWorks.Sales.SalesOrderHeader', @columnname = TotalDue ;
GO

Output:
(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

Similarly, we can get the definition of system databases.

Thanks for reading, If you have any query feel free to ask.

Add Numbers from string(varchar) in tsql

Once I was asked by my colleague to write a function which will add the numbers from a string, so I write a function for it. I used the TSQL substring,  Len, ASCII and char functions to achieve this task.

Code Snippet:

CREATE FUNCTION SelectNumbersFromString(@str varchar(50)) RETURNS varchar(MAX) AS BEGIN DECLARE @cchk char(5); DECLARE @len int ; DECLARE @aschr int;
SET @len =
  (SELECT len(@str)); DECLARE @count int
SET @count = 1 DECLARE @ans varchar(MAX)
SET @ans = '' While @count <= @len BEGIN
SET @cchk =
  (SELECT Substring(@str,@count,1));
SET @aschr =
  (SELECT ASCII(@cchk)); IF @aschr IN (49,
                                       50,
                                       51,
                                       52,
                                       53,
                                       54,
                                       55,
                                       56,
                                       57,
                                       58) BEGIN
SET @ans = @ans + CHAR(@aschr) END
SET @count = @count + 1; END RETURN @ans; END

Creating and Consuming WCF Data Service

WCF Data Services provides the ability to create Windows Communication Foundation (WCF) services that use the Atom Publishing protocol (AtomPub) and Open Data protocol (OData) to expose and consume data over the web. OData is an open protocol for sharing data that builds on Atom Publishing protocol

(AtomPub).

In this post I will explain you how to use a WCF Data Service. Create a new web application and add a WCF Data Service template from the Add New Item Dialogue box. We named it gamesDataService, this service will give us the detail of games. The gamesDataService contains the following code:

namespace wcfDataService  
 {  
   public class gamesDataService : DataService< /* TODO: put your data source class name here */ >  
   {  
     // This method is called only once to initialize service-wide policies.  
     public static void InitializeService(DataServiceConfiguration config)  
     {  
       // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.  
       // Examples:  
       // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);  
       // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);  
       config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;  
     }  
   }  
 }  

The code-behind class inherits from the generic DataService class. A comment appears where the parameter type is expected, gamesDataService in this case. The code contains a method InitializeService which accepts one parameter of type DataServiceConfiguration. This method is used to set up service configuration. The DataServiceConfiguration class has a SetEntitySetAccessRule method that accepts two parameters. The first parameter represents the name of the entity set on which you want to set permission. This parameter also accepts the asterisk (*) wildcard to set permissions on all entity sets. The second parameter is an EntitySetRights enumeration value that represents the permissions you want to assign to the entity set.

Following are the EntitySetRights values:

1. All -> Provides authorization to create, read, update, and delete data.
2. AllRead -> Provides authorization to read data.
3. AllWrite -> Provides authorization to write data.
4. None -> Denies all rights to access data.
5. ReadMultiple -> Provides authorization to perform multiple item queries to read from the entity set.
6. ReadSingle -> Provides authorization to perform single item queries to read from the entity set.
7. WriteAppend -> Provides authorization to write new items to the entity set.
8. WriteDelete -> Provides authorization to delete items from the entity set.
9. WriteMerge -> Provides authorization to perform merge-based updates, in which the payload must be an entity and needs only to contains the properties being modified.
10. WriteReplace Provides authorization to perform replace-based updates, in which the payload must be an entity and should contains all the properties of the entity.

The config object sets permissions on the operations by providing a ServiceOperationRights enumeration value to the SetServiceOperationAccessRule method. The config.DataServiceBehavior.MaxProtocolVersion is used to gets or sets the maximum protocol version that is supported by the response sent by the data service.

Now we will add a class called Games to our project. In the Games class we create four properties which are shown below:

namespace wcfDataService  
 {  
   [DataServiceKey("gameID")]  
   public class Games  
   {  
     public long gameID { get; set; }  
     public string name { get; set; }  
     public string size { get; set; }  
     public string price { get; set; }  
   }  
 }  

After creating this class we create properties in our gamesDataService class to expose data. This property gets data from a hard-coded list of games but could certainly get its data from a back-end SQL Server or other source. The property’s data type must be of generic IQueryable. The following code shows completed gamesDataService class with Game property

namespace wcfDataService  
 {  
   public class gamesDataService : DataService<gamesDataService>  
   {  
     public IQueryable<Games> Game  
     {  
       get  
       {  
         return (new List<Games>  
         {  
           new Games{ gameID=001, name="Street Fighter", price="10$", size="10MB" },  
           new Games{ gameID=002,name="Need For Speed 2", price="25$", size="250MB" },  
           new Games{ gameID=003, name="Delta Force 1", price="30$", size="300MB" }  
         }).AsQueryable();  
       }  
     }  
     // This method is called only once to initialize service-wide policies.  
     public static void InitializeService(DataServiceConfiguration config)  
     {        
       config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);        
       config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;  
     }  
   }  
 }  

Now view the gamesDataService in browser it will show the following output:

The browser shows an XML representation of the service. In the defined namespaces, the atom namespace is defined. This is the namespace that is used for RSS feeds. The Game collection is defined as a relative URI location, so adding Game to the end of the URI will display an XML feed containing the list of games.

In order to retrieve schema information add /$metadata at the end of the URI.

Now we will create a web form and we will get the data from the gamesDataService and will display it in a gridview. Add a new webform wfGames to the project, then add a gridview named gvGames to it. The aspx code is:

<html xmlns="http://www.w3.org/1999/xhtml">  
 <head runat="server">  
   <title></title>  
 </head>  
 <body>  
   <form id="form1" runat="server">  
   <div>  
     <asp:GridView ID="gvGames" runat="server" ></asp:GridView>  
   </div>  
   </form>  
 </body>  
 </html>  

Before we write code for data binding first add a reference to the WCF service. Right click on the solution and select Add Service Reference, a dailogue window is opened click the Discover button, it will show the gamesDataService, then click the OK button.

Now in the Page Load event add the following code to retrieve data and display it in a grid view:

namespace wcfDataService  
 {  
   public partial class wfGames : System.Web.UI.Page  
   {  
     protected void Page_Load(object sender, EventArgs e)  
     {  
       gamesDataService gds = new gamesDataService();  
       var query = (from f in gds.Game  
              select f).ToList();  
       gvGames.DataSource = query;  
       gvGames.DataBind();  
     }  
   }  
 }  

We create an object of gamesDataService class and then write a simple LINQ query to get all the games and then bind the list with the grid view. The following output is shown in the browser:

Thanks for reading this post, if you like it then share it with others, you can also the download complete source code from below sky drive link
Download WCFDataService Project


References:
Accessing Data with Microsoft.NET Framework 4 Ebook

Calling a WCF Service from Client Side( JavaScript ) ( Ajax Enabled WCF Service )

In this post I will show you how to call the WCF service from the client side. First of all create a new Asp.net empty website. From the add new item dialog box add a new web form. In this web form add two text boxes and a label control. In text boxes we will take the numbers from the user and in label we will show the sum of the numbers, also add a button control. The aspx source code looks like:

<asp:TextBox ID="txtNum1" runat="server" ClientIDMode="Static" />  
 <br />  
 <asp:TextBox ID="txtNum2" runat="server" ClientIDMode="Static" />  
 <br />  
 <input name="ButtonCalculate" type="button" value="Sum"  
 onclick="ButtonCalculate_onclick()" />  
 <br />  
 <asp:Label ID="lblResult" runat="server" ClientIDMode="Static" ></asp:Label>  

Now after  this from the add new item dialogue box add a new Ajax enabled WCF service to your solution. It will add two files one with .svc and one with .cs extension. Now open the .cs file and write a function for calculating the sum of two numbers. The code is below:

[ServiceContract(Namespace = "")]  
 [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]  
 public class ServiceAddNumbers  
 {       
      [OperationContract]  
      public int AddNumbes(int a,int b)  
      {           
           return a+b;  
      }        
 }  

In the above code namespace attribute is not necessary. We created a function AddNumbers which takes two integers and return its sum.
Now we will look at how to call this service from the client side. First add a script manager to the page. In source of Script Manager add a services tag and inside it add a reference to the wcf service. The code is below:

<asp:ScriptManager ID="ScriptManager1" runat="server">  
 <Services>  
     <asp:ServiceReference Path="~/ServiceAddNumbers.svc" />  
 </Services>  
 </asp:ScriptManager>  

After this we will write a JavaScript function to call the service. The function code is below :

<script language="javascript" type="text/javascript">  
     function ButtonCalculate_onclick() {  
       var svc = new ServiceAddNumbers;  
       svc.AddNumbes(txtNum1.value, txtNum2.value, onSuccess, onFail, null);  
       function onSuccess(result) {         
         lblResult.innerText = result;  
       }  
       function onFail(result) {  
         alert(result);  
       }            
 }  
   </script>  

The svc.AddNumbers function took five parameters first and second are the numbers, onSuccess function is called on successfully calling the service and onFail if an error occured and last parameter is usercontext set it to null.
Now the code is completed. Just run it add two numbers and it will show the sum of two numbers in the label control.

Download the complete Visual Studio Solution from below link:
Calling WCF Service from Client Side

Thanks.

Send DataSet/DataTable From Sql Clr Stored Procedure

In this post I will show you how to send a datatable or dataset from a sql clr procedure. Firstly create a new sql clr project. If you have no idea how to create a sql clr project then see the following post.

Creating a sql clr project

After creating sql clr project go to Add New Item and add a new stored procedure template. This will create the following code: 

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {      
      
   }  
 };  

Now I will write two functions SendDataSet and SendDataTable. In SendDataSet We will check whether DataSet is null or not, if dataset is not null then we will call the SendDataTable function. In SendDataTable function we will first determine the datatypes of datacolumns, for this we have a function ExtractDataTableColumnMetaData. This function will return the metadata of the given datatable. Now we will assign the metadata to the SqlDataRecord. After assigning it we will use the SqlContext.Pipe methods to Send the row. Below is the complete code.

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {  
     string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";  
     string cmd = "select * from categories; select * from customers";  
     using (SqlConnection conn = new SqlConnection(connectionString))  
     {  
       SqlCommand sqlCmd = new SqlCommand(cmd, conn);  
       SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);  
   
       DataSet ds = new DataSet("Test");  
       adapter.Fill(ds);  
       SendDataSet(ds);  
     }  
   }  
   
   public static void SendDataSet(DataSet ds)  
   {  
     if (ds == null)  
     {  
       throw new ArgumentException("SendDataSet requires a non-null data set.");  
     }  
     else  
     {  
       foreach (DataTable dt in ds.Tables)  
       {  
         SendDataTable(dt);  
       }  
     }  
   }  
   
   
   public static void SendDataTable(DataTable dt)  
   {  
     bool[] coerceToString; // Do we need to coerce this column to string?  
     SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);  
   
     SqlDataRecord record = new SqlDataRecord(metaData);  
     SqlPipe pipe = SqlContext.Pipe;  
     pipe.SendResultsStart(record);  
     try  
     {  
       foreach (DataRow row in dt.Rows)  
       {  
         for (int index = 0; index < record.FieldCount; index++)  
         {  
           object value = row[index];  
           if (null != value && coerceToString[index])  
             value = value.ToString();  
           record.SetValue(index, value);  
         }  
          
         pipe.SendResultsRow(record);  
       }  
     }  
     finally  
     {  
       pipe.SendResultsEnd();  
     }  
   }  
   
   private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] coerceToString)  
   {  
     SqlMetaData[] metaDataResult = new SqlMetaData[dt.Columns.Count];  
     coerceToString = new bool[dt.Columns.Count];  
     for (int index = 0; index < dt.Columns.Count; index++)  
     {  
       DataColumn column = dt.Columns[index];  
       metaDataResult[index] = SqlMetaDataFromColumn(column, out coerceToString[index]);  
     }  
   
     return metaDataResult;  
   }  
   
   private static Exception InvalidDataTypeCode(TypeCode code)  
   {  
     return new ArgumentException("Invalid type: " + code);  
   }  
   
   private static Exception UnknownDataType(Type clrType)  
   {  
     return new ArgumentException("Unknown type: " + clrType);  
   }  
   
   private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool coerceToString)  
   {  
     coerceToString = false;  
     SqlMetaData smd = null;  
     Type clrType = column.DataType;  
     string name = column.ColumnName;  
     switch (Type.GetTypeCode(clrType))  
     {  
       case TypeCode.Boolean: smd = new SqlMetaData(name, SqlDbType.Bit); break;  
       case TypeCode.Byte: smd = new SqlMetaData(name, SqlDbType.TinyInt); break;  
       case TypeCode.Char: smd = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;  
       case TypeCode.DateTime: smd = new SqlMetaData(name, SqlDbType.DateTime); break;  
       case TypeCode.DBNull: throw InvalidDataTypeCode(TypeCode.DBNull);  
       case TypeCode.Decimal: smd = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;  
       case TypeCode.Double: smd = new SqlMetaData(name, SqlDbType.Float); break;  
       case TypeCode.Empty: throw InvalidDataTypeCode(TypeCode.Empty);  
       case TypeCode.Int16: smd = new SqlMetaData(name, SqlDbType.SmallInt); break;  
       case TypeCode.Int32: smd = new SqlMetaData(name, SqlDbType.Int); break;  
       case TypeCode.Int64: smd = new SqlMetaData(name, SqlDbType.BigInt); break;  
       case TypeCode.SByte: throw InvalidDataTypeCode(TypeCode.SByte);  
       case TypeCode.Single: smd = new SqlMetaData(name, SqlDbType.Real); break;  
       case TypeCode.String: smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
         break;  
       case TypeCode.UInt16: throw InvalidDataTypeCode(TypeCode.UInt16);  
       case TypeCode.UInt32: throw InvalidDataTypeCode(TypeCode.UInt32);  
       case TypeCode.UInt64: throw InvalidDataTypeCode(TypeCode.UInt64);  
       case TypeCode.Object:  
         smd = SqlMetaDataFromObjectColumn(name, column, clrType);  
         if (smd == null)  
         {  
           // Unknown type, try to treat it as string;  
           smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
           coerceToString = true;  
         }  
         break;  
   
       default: throw UnknownDataType(clrType);  
     }  
   
     return smd;  
   }  
   
   private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)  
   {  
     SqlMetaData smd = null;  
     if (clrType == typeof(System.Byte[]) || clrType == typeof(SqlBinary) || clrType == typeof(SqlBytes) ||  
   clrType == typeof(System.Char[]) || clrType == typeof(SqlString) || clrType == typeof(SqlChars))  
       smd = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);  
     else if (clrType == typeof(System.Guid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(System.Object))  
       smd = new SqlMetaData(name, SqlDbType.Variant);  
     else if (clrType == typeof(SqlBoolean))  
       smd = new SqlMetaData(name, SqlDbType.Bit);  
     else if (clrType == typeof(SqlByte))  
       smd = new SqlMetaData(name, SqlDbType.TinyInt);  
     else if (clrType == typeof(SqlDateTime))  
       smd = new SqlMetaData(name, SqlDbType.DateTime);  
     else if (clrType == typeof(SqlDouble))  
       smd = new SqlMetaData(name, SqlDbType.Float);  
     else if (clrType == typeof(SqlGuid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(SqlInt16))  
       smd = new SqlMetaData(name, SqlDbType.SmallInt);  
     else if (clrType == typeof(SqlInt32))  
       smd = new SqlMetaData(name, SqlDbType.Int);  
     else if (clrType == typeof(SqlInt64))  
       smd = new SqlMetaData(name, SqlDbType.BigInt);  
     else if (clrType == typeof(SqlMoney))  
       smd = new SqlMetaData(name, SqlDbType.Money);  
     else if (clrType == typeof(SqlDecimal))  
       smd = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);  
     else if (clrType == typeof(SqlSingle))  
       smd = new SqlMetaData(name, SqlDbType.Real);  
     else if (clrType == typeof(SqlXml))  
       smd = new SqlMetaData(name, SqlDbType.Xml);  
     else  
       smd = null;  
   
     return smd;  
   }  
   
 };  

After writing the whole code, Deploy the solution.Before deploying Must remember to change the Permission Level to external or unsafe, and also remember to give trustworthy right to the database by running the following command on the sql server management studio.

alter database DataBaseName set trustworthy on  

and one more thing CLR integration must be enabled, if it is not then run this command

sp_configure 'clr enabled', 1     
 GO     
 RECONFIGURE     
 GO  

Download the whole solution from skydrive, just change the database reference. Thanks.
SendDataSet

References:
The below msdn post helps me alot
http://msdn.microsoft.com/en-us/library/ff878201.aspx

SQL CLR Table Valued Function

SQL CLR project allows the developers to write code in .Net. In this post I will create a SQL CLR function that will return a table as a result.
For those people who did not knew how to create a Sql Clr project first take a look at the following post
Creating Sql Clr Stored Procedure
After creating a SQL CLR project. From the Project Menu or Add New Item dialog box add a new class file. In the class create a function having return type IEnumerable and add the SqlFunction attribute to header of your function as shown below:

public partial class Functions  
 {  
   [Microsoft.SqlServer.Server.SqlFunction]  
   public static IEnumerable fn_getData()  
   {  
      ArrayList rowsArray = new ArrayList();  
      return rowsArray;  
   }  
 }  

Now add the FillRowMethodName and TableDefinition parameters to the SqlFunction attribute. Set FillRowMethodName = "FillRow". The FillRow method is implicity called by the SQL CLR framework each time the MoveNext() on the returned IEnumerable ( type that implements IEnumerable interface ) is called. In TableDefinition specify the columns of the table. See the below code

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]  
   public static IEnumerable fn_getData()  
   {  
     ArrayList rowsArray = new ArrayList();  
     return rowsArray;  
   }  

Now we will create a function SetData in which we will fill the arraylist with some data.

 public static void SetData(ArrayList rowsArray)
     {
        object[] column = new object[3];
        column[0] = "John";
        column[1] = 18;
        column[2] = "Newyork";
        rowsArray.Add(column);
     }

After this we will finally write the FillRow method. This method will write the row by row data of ArrayList to the table. It has an object parameter which will give the row of an arrayList. The signature of FillRow method is
private static void FillRow(Object obj, out [col1_type] [column Name], ... )

In FillRow method we will store the obj in an object Array and cast the columns to their respective types as shown below:

public static void FillRow(object obj, out string Name, out int Age, out string City)  
    {  
     object[] row = (object[])obj;  
     Name = (string)row[0];  
     Age = (int)row[1];  
     City = (string)row[2];  
   }  

It gets called once each time the framework calls .MoveNext() on the underlying ArrayList method (which implements the inherited IEnumerable interface)

The complete code is below:

public partial class Functions  
 {  
   [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]  
   public static IEnumerable fn_getData()  
   {  
     ArrayList rowsArray = new ArrayList();  
     SetData(rowsArray);  
     return rowsArray;  
   }  
    public static void FillRow(object obj, out string Name, out int Age, out string City)  
    {  
     object[] row = (object[])obj;  
     Name = (string)row[0];  
     Age = (int)row[1];  
     City = (string)row[2];  
   }  
    public static void SetData(ArrayList rowsArray)  
    {  
     object[] column = new object[3];  
     column[0] = "John";  
     column[1] = 18;  
     column[2] = "Newyork";  
     rowsArray.Add(column);  
     object[] column1 = new object[3];  
     column1[0] = "Scott";  
     column1[1] = 25;  
     column1[2] = "Washington";  
     rowsArray.Add(column1);  
   }  
 }  

Now deploy the project and move to Sql Server. Open the database in which you have deployed the function. I have used NorthWind database. I ran this query and got the desired output

References:
http://msdn.microsoft.com/en-us/library/ms131103(v=sql.90).aspx
http://blogs.msdn.com/b/stuartpa/archive/2005/07/21/441468.aspx

Creating a SQL CLR Stored Procedure using VS 2010 and Sql Server 2008

SQL CLR give us the ability to write code in .Net Framework. In Visual Studio 2005,8 and 10 there is a database project namely SQL CLR Database project as shown in the below image.

Before creating the project please change the .net framework version to .net Framework 3.5 from the drop down menu.
After choosing the template you have to choose a database in which you will deploy the solution. The following window will open when you press OK button.

We will use Northwind database. Press the OK button. The following window will open

To enable the debugging press  the Yes button otherwise No. Now the project is created. The project consists of following folders and files.

Now from the project menu select stored procedure to add a code file.

Add the stored procedure and give a name to it. We gave a name sp_WriteFile to it. We use the File Class method WriteAllText to write a file to the Sytem. The complete code consists of only one line as shown in the below image.

The sp has two parameters FileName and FileContent. Now go to project properties select Database tab and set the Permission Level to external or Unsafe. By default permission level is set to Safe.

We changed the permission level because we have to access the File System. In Safe permission level we can 't access the file system.
Read more about the permission levels at MSDN

Now deploy the solution.

When you deploy the project the following error may appears.

//C:\Code\CLRProject\CLRProject\bin\Debug\CLRProject.sql(106-106): Deploy error SQL01268: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'CLRProject' failed because assembly 'CLRProject' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.  
// An error occurred while the batch was being executed.  
// Build FAILED.  

As the message explains, SQLCLR code with the EXTERNAL_ACCESS permission set must either be signed or the database must be given the TRUSTWORTHY attribute. Let's make the database TRUSTWORTHY with this ALTER DATABASE statement:

ALTER DATABASE Northwind SET TRUSTWORTHY ON  

Another problem is of database ownership as mentioned in the message "database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission" so change the owner of the database to sa. Run the following command

EXEC sp_changedbowner sa  

CLR Integration must be enabled in order to use SQL CLR objects. By Default CLR Intergration is disabled so we first have to enable it.
Run the following script to enable Sql CLR integation.

sp_configure 'show advanced options', 1;  
 GO  
 RECONFIGURE;  
 GO  
 sp_configure 'clr enabled', 1;  
 GO  
 RECONFIGURE;  
 GO  

Now again deploy the solution. After deployment open the Sql Server Management Studio. Select Northwind database and open the stored procedures folder and the assemblies folder. See the below image.

As shown in the image sp_WriteFile is added to stored procedures and an assembly of CLRProject is added to the Assemblies folder.

References:
Enabling CLR Integration
CLR Integration Code Access Security
http://www.mssqltips.com/sqlservertip/1662/writing-to-an-operating-system-file-using-the-sql-server-sqlclr/

Steps to enable CLR Integration, OLE Automation and xp_cmdshell procedures in sql server 2005 and 2008

For SQL Server 2005:
Follow these steps:
1) From start menu open Sql Server 2005 surface are configuration

2) Select Surface Area Configuration For Features

Select OLE Automation from Database Engine node and then checked the Enable OLE Automation checked box to enable the feature. Follow the same step for CLR Integration and xp_cmdshell. Then click the apply button and close the window. The CLR Integration, OLE Automation features and xp_cmdshell features are now enabled.


For SQL Server 2008:
1) Open the SQL Server 2008 and follow the below steps

After performing the above steps click he OK button.  The OLE Automation features and xp_cmdshell features are now enabled.
To enable CLR Integration select the server configuration from the drop down menu.

Change the ClrIntegrationEnabled value from false to true to enable it. Now CLR Integration is enabled.
or run the following script to enable CLR Integration

sp_configure 'show advanced options', 1;   
  GO   
  RECONFIGURE;   
  GO   
  sp_configure 'clr enabled', 1;   
  GO   
  RECONFIGURE;   
  GO   

Reference:
http://msdn.microsoft.com/en-us/library/ms161956.aspx

Reading and Writing Files using T-SQL ( Sql Server )

Reading a File:

OPENROWSET is a T-SQL function that allows reading data from many sources including using the SQL Server’s BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table.

The BULK option was added to T-SQL in SQL Server 2005 and it persists in SQL Server 2008.
When used with the BULK provider keyword you can name a data file to read as one of three types of objects:

1. SINGLE_BLOB, which reads a file as varbinary(max)
2. SINGLE_CLOB, which reads a file as varchar(max) 
3. SINGLE_NCLOB, which reads a file as nvarchar(max) 

OPENROWSET returns a single column, named BulkColumn, as its result. Here’s an example that reads a text file:
Query:

SELECT BulkColumn 
FROM OPENROWSET (BULK 'C:\Test\test.txt', SINGLE_CLOB) MyFile

Output:

Writing a File:

1) Write a File using  xp_cmdshell:

Before using xp_cmdshell, we first have to enable xp_cmdshell feature, due to security reasons it is disabled by default.
See the following post
Steps to enable OLE Automation and xp_cmdshell procedures in sql server 2005 and 2008
                                                                     OR

EXEC sp_configure 'show advanced options', 1

GO

--- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

--- To update the currently configured value for this feature.

RECONFIGURE

GO  

Query:

Declare @text varchar(max)
Declare @data cursor
Declare @a varchar(20)
Declare @b varchar(30)
DECLARE @cmd sysname, @var sysname

SET @data = Cursor for
Select FirstName,LastName from dbo.Employees // Northwind database


Open @data
WHILE (1 <> 0)
BEGIN

    FETCH NEXT FROM @data INTO @a , @b

    SET @text ='First Name :'+@a+' and Last Name :'+@b

    IF ( @@FETCH_STATUS <>  0 )
    BEGIN
        BREAK;
    END

SET @cmd = 'echo ' + @text + ' >> c:\var_out.txt'
exec master..xp_cmdshell @cmd

END

In the above code, we declare a cursor  @data in which we stored first name and last name of employees and then open the @data cursor and place an infinite while loop. The IF condition is placed inside the while loop to check @@Fetch_Status. The loop will continue works until the @@Fetch_Status does not return 0.
In the end the records are written to the var_out.txt file. There are multiple records in the employees table, all of them are appended in the same file.

2) Using OLE Automation Stored Procedures:

CREATE PROCEDURE sp_OAToFile(@FileName varchar(255), @Text1 text) AS
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

GO

SET QUOTED_IDENTIFIER OFF
GO 
SET ANSI_NULLS ON
GO

In the above code, we created a stored procedure which takes two parameters file name and text. File name is the name of the file which will be created and text is the content which is written to the file.
sp_OACreate creates an instance of OLE object.
sp_OAMethod calls the method of OLE object.
and last one sp_OADestroy procedure destroys the OLE object.

References:
OLE Automation Stored Procedures.
http://msdn.microsoft.com/en-us/library/ms190501(v=sql.110).aspx
Read file reference
http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/

Important MS SQL server system Views

Microsoft SQL server has a lot of built in system views which provides information about almost all of the objects in the database. Let's talk about the most important system views:

1)sys.databases
This view will return all the databases in the instance of a Sql Server.

Columns:
name ( datatype sysname ) of database is unique with in the instance of a SQL Server
database_id ( datatype int ) of database is unique with in the instance of a SQL Server
Database state ( type tinyint ) :

0 = ONLINE

1 = RESTORING

2 = RECOVERING

3 = RECOVERY_PENDING

4 = SUSPECT

5 = EMERGENCY

6 = OFFLINE

2) sys.objects

This view provides information about all of the objects in the selected database. 

The name ( datatype sysname ) is the name of the object.

The object_id ( datatype int ) is unique with in the database.

The type column ( datatype char(2) ) specifies whether it is a function, procedure or a table etc.

Important types are given below.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

Example:

select * from sys.objects     
where type='V'
            OR
select * from sys.views
--Both queries will bring the same result.

3) sys.procedures

This procedure will return all the procedures from the selected database

3) sys.tables

This view will returns all the tables from the selected database

4) sys.views 

This view will return all the views from the selected database.

5) sys.triggers;

This view will return all the triggers from the selected database.

6) sys.columns
This will retrun all the columns of the objects ( Table,Views etc ) in the selected database

Source:
MSDN
Read more at

http://msdn.microsoft.com/en-us/library/ms178534.aspx

http://msdn.microsoft.com/en-us/library/ms189783.aspx

When to use DataReader, DataSet [ DataReader v/s DataSet ]

When to use DataReader and DataSet
ADO.NET provides two objects for retrieving relational data and storing it in memory: the DataSet and the DataReader. The DataSet provides an in-memory relational representation of data, a complete set of data that includes the tables that contain, order, and constrain the data, as well as the relationships between the tables. 
The DataReader provides a fast, forward-only, read-only stream of data from a database.
When using a DataSet, you will often make use of a DataAdapter (and possibly a CommandBuilder) to interact with your data source. Also, when using a DataSet, you may employ a DataView to apply sorting and filtering to the data in the DataSet. The DataSet can also be inherited to create a strongly typed DataSet in order to expose tables, rows, and columns as strongly typed object properties.


DataSet vs. DataReader

To determine whether to use the DataSet or the DataReader when you design your application, consider the level of functionality that is needed in the application.

Use the DataSet in order to do the following with your application:

1. Navigate between multiple discrete tables of results.
2. Manipulate data from multiple sources (for example, a mixture of data from more than one database, from an XML file, and from a spreadsheet).
3. Exchange data between tiers or using an XML Web service. Unlike the DataReader, the DataSet can be passed to a remote client.
4. Reuse the same set of rows to achieve a performance gain by caching them (such as for sorting, searching, or filtering the data).
5. Perform a large amount of processing per row. Extended processing on each row returned using a DataReader ties up the connection serving the DataReader longer than necessary, impacting performance.
6. Manipulate data using XML operations such as Extensible Stylesheet Language Transformations (XSLT transformations) or XPath queries.

Use the DataReader in your application if you:

1. Do not need to cache the data.
2. Are processing a set of results too large to fit into memory.
3. Need to quickly access data once, in a forward-only and read-only manner.

How DataReader Works in Connected Mode
The DataReader has a defined set of operations that revolve around its connected, forward-only, read-only nature (the read-only DataReader is also known as the firehose cursor of ADO.NET). A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time. As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.
 The following code demonstrates how to retrieve a list of products from the Northwind database using a SqlDataReader object:

   1:  string sSQL = "SELECT * FROM Products";

   2:  string sConnString =

   3:      "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4:  using (SqlConnection oCn = new SqlConnection(sConnString))

   5:  {

   6:      SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   7:      oSelCmd.CommandType = CommandType.Text;

   8:      oCn.Open();

   9:      SqlDataReader oDr = oSelCmd.ExecuteReader();

  10:      DataGrid1.DataSource = oDr;

  11:      DataGrid1.DataBind();

  12:  }

Both a SqlConnection and a SqlCommand object are created. The SqlConnection is opened and the SqlCommand object executes the SQL query, returning the first row to the SqlDataReader. At this point the connection to the database is still open and associated with the SqlDataReader. This code shows how a SqlDataReader can be bound to a bindable object such as an ASP.NET DataGrid.

Alternatively, a DataReader could be used to retrieve the rows and then loop through them manually, one by one. It can support several resultsets as well. For example, a list of products and categories could be retrieved from a database. The following code retrieves a SqlDataReader and loops through its rows, writing the first column's value for each row to the console:

   1:  SqlDataReader oDr = oCmd.ExecuteReader();

   2:  while(oDr.Read()) {

   3:      Console.WriteLine(oDr[0]);

   4:  }

How Dataset Works in Disconnected Mode

The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it. Instead, to fill a DataSet from a database you first create a DataAdapter object (such as a SqlDataAdapter) for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.

You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects. One of its purposes is to serve as the route for a rowset to get from the database to the DataSet. For example, when the SqlDataAdapter's Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet. Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.
The following code shows how a DataSet can be filled from the Products table of the Northwind database. Notice that there is no explicit SqlDataReader object in this code sample:

   1:  string sSQL = "SELECT * FROM Products";

   2:  string sConnString = 

   3:      "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4:  SqlDataAdapter oDa = new SqlDataAdapter();

   5:  DataSet oDs = new DataSet();

   6:  using(SqlConnection oCn = new SqlConnection(sConnString))

   7:  {

   8:      SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   9:      oSelCmd.CommandType = CommandType.Text;

  10:      oDa.SelectCommand = oSelCmd;

  11:      oDa.Fill(oDs, "Products");

  12:  }

 Unlike the DataReader, the DataSet is not read-only. A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider's objects. Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only. In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched. The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.

Source: MSDN
http://msdn.microsoft.com/en-us/library/ms971481.aspx
http://msdn.microsoft.com/en-us/magazine/cc188717.aspx