Thursday, May 29, 2008

How to retrieve column names using datareader? - Part III

The following is the snapshot shhowing the output:

How to retrieve column names using datareader? - Part II

<!--

* The code snippet tells how to retrieve fields

* using datareader for, this is a part of :

* Book: C#2005 Beginners: A Step Ahead

* Written by: Gaurav Arora [gaurav.aroraose@yahoo.co.in]

-->

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Web" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

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


<script runat="server">



SqlConnection myCon = new SqlConnection("Server=localhost;database=HRnPAYROLL;trusted_Connection=true");

SqlCommand myCmd = null;

SqlDataReader myDr = null;

SqlDataAdapter myDa = null;

DataSet myDs = null;

String strOutPut = string.Empty;

protected void Page_Load(object sender, EventArgs e)

{

Getfields_DataReader();

Getfields_DataReader_SchemaOnly();

Getfields_DataSet();

}

/// <summary>

/// Retrieves all filed/column names of DataReader using GetName method without SchemaOnly

/// </summary>

protected void Getfields_DataReader()

{

myCmd = new SqlCommand("Select * from Employees", myCon);

myCon.Open();

myDr = myCmd.ExecuteReader();

//Store total number of Columns

Int32 totCol = myDr.FieldCount; //Fiedl count returns the number of total columns

Response.Write("<div class=\"upperColumn\">");

Response.Write("<h1>Retrieves All Field Names from DataReader using GetName Without SchemaOnly</h1>");


strOutPut = "<table border=\"1\"><tr> <td>Column Name</td> <td>Data Type</td></tr>";


for (Int32 intCol = 0; intCol < totCol; intCol++)

{

strOutPut += "<tr><td>" + myDr.GetName(intCol) + "</td>";

strOutPut += "<td>" + myDr.GetFieldType(intCol) + "</td></tr>";


}

strOutPut += "</table>";

//Write the output

Response.Write("<br/>" + strOutPut + "</Div> <br class=\"clear\" />");

myCon.Close();


}

/// <summary>

/// Retrieves all filed/column names of DataReader using SchemaOnly

/// </summary>

protected void Getfields_DataReader_SchemaOnly()

{

myCmd = new SqlCommand("Select * from HR_MAST_DEPT", myCon);

myCon.Open();

myDr = myCmd.ExecuteReader(CommandBehavior.SchemaOnly);

//Store total number of Columns

Int32 totCol = myDr.FieldCount; //Fiedl count returns the number of total columns

Response.Write("<div class=\"content\"> <div class=\"bottomColumn\">");

Response.Write("<h1>Retrieves All Field Names from DataReader using SchemaOnly</h1>");


strOutPut = "<table border=\"1\"><tr> <td>Column Name</td> <td>Data Type</td></tr>";


for (Int32 intCol = 0; intCol < totCol; intCol++)

{

strOutPut += "<tr><td>" + myDr.GetName(intCol) + "</td>";

strOutPut += "<td>" + myDr.GetFieldType(intCol) + "</td></tr>";


}

strOutPut += "</table>";

//Write the output

Response.Write("<br/> " + strOutPut + "</Div> </Div><br class=\"clear\" />");

myCon.Close();


}


/// <summary>

/// Retrieves all filed/column names of DataSet using ColumnName

/// </summary>

protected void Getfields_DataSet()

{


myDa = new SqlDataAdapter("Select * from Employees", myCon);

myDs = new DataSet();

myDa.Fill(myDs);

Response.Write("<div class=\"content\"> <div class=\"rightColumn\">");

Response.Write("<h1>Retrieves All Field Names from DataSet ColumnName</h1>");


strOutPut = "<table border=\"1\"><tr> <td>Column Name</td> <td>Data Type</td></tr>";


for (Int32 intCol = 0; intCol < myDs.Tables[0].Columns.Count; intCol++)

{

strOutPut += "<tr><td>" + myDs.Tables[0].Columns[intCol].ColumnName + "</td>";

strOutPut += "<td>" + myDs.Tables[0].Columns[intCol].DataType + "</td></tr>";


}

strOutPut += "</table>";

//Write the output

Response.Write("<br/> " + strOutPut + "</Div> </Div>");

}


</script>


<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<style type="text/css">

.content

{

margin:auto;

width:600px;

background-color:white;

border:Solid 2px orange;

}


html

{

background-color:gray;

font:14px Georgia,Serif,Garamond;

}


h1

{

color:Green;

font-size:18px;

border-bottom:Solid 1px orange;

}

.lbl

{

color:green;

font-weight:bold;

}


.upperColumn

{



margin:auto;

width:500px;

border:Solid 2px orange;

background-color:white;

padding:10px;

}

.bottomColumn

{



margin:auto;

width:700px;

border:Solid 2px orange;

background-color:white;

padding:10px;

}

.leftColumn

{ float:left;

width:300px;

height:150px;

border-right:Solid 1px gray;

background-color:white;

padding:5px;

}

.rightColumn

{ float:left;

height:150px;

border-left:Solid 1px gray;

padding:5px;

}

.clear

{

clear:both;

}

</style>

<title>How to retrieve all field names of datareader</title>

</head>

<body>

<form id="form1" runat="server">

</form>

</body>

</html>

How to retrieve column names using datareader? - Part I

Sometimes, we people when doing a higher things had lost some basics from memories, the sam happened with me.

Yesterday [May 28, 2008] my supervisor asked me a bit question - "How to retrieve column names using datareader?" I was junked that time and noticed that I have lost something from my memories of basics.
Today, I have sit back with my PC and workout the problem and write following Code snippet for VS2005:

We need a table to attain the above task, following query will solve the problem:

/* This query is created a HRnPAYROLL DATABASE
* for use of different examples shown in
* Book: C#2005 Beginners: A Step Ahead
* Written by: Gaurav Arora */

--First Create HRnPAYROLL DATABASE
Create Database HRnPAYROLL
go

Use HRnPAYROLL

--Create Employees Table and fill data

Create Table Employees
(
ID Varchar(4) Primary Key,
FirstName Varchar(25) Default 'Gaurav',
LastName Varchar(25) Default 'Arora',
Age Varchar(4) Default '19'
)
go

--*****************************************************************************************************************
Insert Into Employees Values ('0001','Anil','Jain','37');
Insert Into Employees Values ('0002','Aman','Jain','35');
Insert Into Employees Values ('0003','Amanpreet','Singh','28');
Insert Into Employees Values ('0004','Anuradha','Bhatia','24');
Insert Into Employees Values ('0005','Archana','Garg','25');
Insert Into Employees Values ('0111','Babita','Gupta','33');
Insert Into Employees Values ('0112','Babban','Das','24');
Insert Into Employees Values ('0113','Babu','Ram','24');
Insert Into Employees Values ('0114','Babbu','Man','38');
Insert Into Employees Values ('0115','Balbir','Singh','24');
Insert Into Employees Values ('0201','Chaman','Lal','38');
Insert Into Employees Values ('0202','Charan','Das','37');
Insert Into Employees Values ('0203','Chander Bhan','Singh','49');
Insert Into Employees Values ('0204','Changej','Khan','24');
Insert Into Employees Values ('0205','Champak','Lal','18');
Insert Into Employees (ID,FirstName,Age) Values ( '0160',' AMIT KUMAR','21')
Insert Into Employees (ID,FirstName,Age) Values ( '0180',' ABHISHEK MISHRA','24')
Insert Into Employees (ID,FirstName,Age) Values ( '0181',' ARUN KUMAR','23')
Insert Into Employees (ID,FirstName,Age) Values ( '0184',' ASHUTOSH BAJPAYEE','21')
Insert Into Employees (ID,FirstName,Age) Values ( '0185',' ANIL KUMAR YADAV','21')
Insert Into Employees (ID,FirstName,Age) Values ( '0188',' ASHOK KUMAR PATHAK','25')
Insert Into Employees (ID,FirstName,Age) Values ( '0190',' ABHISHEK KUMAR SINGH','23')
Insert Into Employees (ID,FirstName,Age) Values ( '0194',' AMIT KUMAR THAKUR','22')
Insert Into Employees (ID,FirstName,Age) Values ( '0549',' BRIJESH CHANDRA','21')
Insert Into Employees (ID,FirstName,Age) Values ( '0558',' BRAJESH KUMAR','27')
Insert Into Employees (ID,FirstName,Age) Values ( '0560',' B.K. SINGH','31')
Insert Into Employees (ID,FirstName,Age) Values ( '0562',' BALBIR SINGH [MANGAT]','35')
Insert Into Employees (ID,FirstName,Age) Values ( '1244',' DHARMESH PANWAR','27')
Insert Into Employees (ID,FirstName,Age) Values ( '1296',' DEEPAK PRASAD','26')
Insert Into Employees (ID,FirstName,Age) Values ( '1320',' DEEPAK JAIN','30')
Insert Into Employees (ID,FirstName,Age) Values ( '2004',' FAKHRE MUBEEN','21')
Insert Into Employees (ID,FirstName,Age) Values ( '2466',' GAURAV ARORA','19')
Insert Into Employees (ID,FirstName,Age) Values ( '2480',' GURDEEP SINGH','34')
Insert Into Employees (ID,FirstName,Age) Values ( '2481',' GOPAL DATT SATI','27')
Insert Into Employees (ID,FirstName,Age) Values ( '3210',' KAHLON I.J. SINGH','26')
Insert Into Employees (ID,FirstName,Age) Values ( '3211',' INDRESH KUMAR PANDEY','35')
Insert Into Employees (ID,FirstName,Age) Values ( '3641',' JAI PRAKASH','27')
Insert Into Employees (ID,FirstName,Age) Values ( '3683',' JAYCHANDRA','27')
Insert Into Employees (ID,FirstName,Age) Values ( '3698',' J.K. SINGH','23')
Insert Into Employees (ID,FirstName,Age) Values ( '3700',' JEET NARAYAN SINGH','29')
Insert Into Employees (ID,FirstName,Age) Values ( '3701',' JIVENDRA KUMAR','29')
Insert Into Employees (ID,FirstName,Age) Values ( '4009',' KAPOOR SINGH','30')
Insert Into Employees (ID,FirstName,Age) Values ( '4057',' KISHAN LAMA','21')
Insert Into Employees (ID,FirstName,Age) Values ( '4110',' KRISHAN KUMAR MISHRA','33')
Insert Into Employees (ID,FirstName,Age) Values ( '4112',' K.B. CHOUBEY','33')
Insert Into Employees (ID,FirstName,Age) Values ( '4113',' KAISH UDDIN','35')
Insert Into Employees (ID,FirstName,Age) Values ( '4455',' LOKNATH BALBANTARAY','34')
Insert Into Employees (ID,FirstName,Age) Values ( '4801',' M.K. DEBROY','30')
Insert Into Employees (ID,FirstName,Age) Values ( '4884',' MADAN GIRI','21')
Insert Into Employees (ID,FirstName,Age) Values ( '4936',' MANJISH KUMAR','34')
Insert Into Employees (ID,FirstName,Age) Values ( '4946',' MADAN SINGH','24')
Insert Into Employees (ID,FirstName,Age) Values ( '4949',' MANOJ KUMAR TIWARI','21')
Insert Into Employees (ID,FirstName,Age) Values ( '4978',' MUKHTAR AHMED','18')
Insert Into Employees (ID,FirstName,Age) Values ( '4985',' MANOJ SAXENA','31')
Insert Into Employees (ID,FirstName,Age) Values ( '4987',' MANISH MISHRA','31')
Insert Into Employees (ID,FirstName,Age) Values ( '5263',' NEERAJ MAHAWAR','33')
Insert Into Employees (ID,FirstName,Age) Values ( '5271',' NIRAJ KUMAR SINHA','19')
Insert Into Employees (ID,FirstName,Age) Values ( '5272',' N.K. SINGH','21')
Insert Into Employees (ID,FirstName,Age) Values ( '5274',' NITIN GUPTA','32')
Insert Into Employees (ID,FirstName,Age) Values ( '5275',' NARESH KUMAR GUPTA','23')
Insert Into Employees (ID,FirstName,Age) Values ( '5612',' O.P. YADAV','32')
Insert Into Employees (ID,FirstName,Age) Values ( '6135',' PRAMOD SINGH RAWAT','30')
Insert Into Employees (ID,FirstName,Age) Values ( '6136',' PRAMOD KUMAR GAUTAM','22')
Insert Into Employees (ID,FirstName,Age) Values ( '6137',' PRAMOD KUMAR SINHA','31')
Insert Into Employees (ID,FirstName,Age) Values ( '6138',' PRASAD SASNUR','20')
Insert Into Employees (ID,FirstName,Age) Values ( '6700',' R. BHATTACHARJEE','21')
Insert Into Employees (ID,FirstName,Age) Values ( '6753',' RAMA SHANKAR','20')
Insert Into Employees (ID,FirstName,Age) Values ( '6765',' RAMESH CHANDRA','27')
Insert Into Employees (ID,FirstName,Age) Values ( '6776',' RAMU YADAV','20')
Insert Into Employees (ID,FirstName,Age) Values ( '6899',' RAVINDER LAMBA','29')
Insert Into Employees (ID,FirstName,Age) Values ( '6925',' RAVINDRA KUMAR RANA','24')
Insert Into Employees (ID,FirstName,Age) Values ( '6996',' R.P. YADAV','28')
Insert Into Employees (ID,FirstName,Age) Values ( '7010',' RAJESH PUNDIR','33')
Insert Into Employees (ID,FirstName,Age) Values ( '7011',' RAJIV CHAUDHARY','32')
Insert Into Employees (ID,FirstName,Age) Values ( '7012',' RAVINDRA YADAV','23')
Insert Into Employees (ID,FirstName,Age) Values ( '7013',' RAJESH SHARMA','32')
Insert Into Employees (ID,FirstName,Age) Values ( '7016',' RAVI SHANKAR','19')
Insert Into Employees (ID,FirstName,Age) Values ( '7017',' RAM BILAS CHOUDHARY','23')
Insert Into Employees (ID,FirstName,Age) Values ( '7022',' RAJ KISHORE RAI','24')
Insert Into Employees (ID,FirstName,Age) Values ( '7023',' R. RAMESH','35')
Insert Into Employees (ID,FirstName,Age) Values ( '7123',' SATISH KUMAR','32')
Insert Into Employees (ID,FirstName,Age) Values ( '7296',' SHANKAR LAL','29')
Insert Into Employees (ID,FirstName,Age) Values ( '7471',' SANJEEVAN M.K.','30')
Insert Into Employees (ID,FirstName,Age) Values ( '7480',' SANDEEP KR. SHRIVASTA','34')
Insert Into Employees (ID,FirstName,Age) Values ( '7484',' SUBHARAM BANERJEE','25')
Insert Into Employees (ID,FirstName,Age) Values ( '7490',' S.M. TRIPATHI','31')
Insert Into Employees (ID,FirstName,Age) Values ( '7501',' TEK BAHADUR CHATTRI','23')
Insert Into Employees (ID,FirstName,Age) Values ( '7513',' TULSI PRASAD','30')
Insert Into Employees (ID,FirstName,Age) Values ( '7919',' UMESH CHANDRA MISHRA','19')
Insert Into Employees (ID,FirstName,Age) Values ( '8480',' VIVEK TRIPATHI','31')
Insert Into Employees (ID,FirstName,Age) Values ( '8481',' VIJAY KUMAR','22')
Insert Into Employees (ID,FirstName,Age) Values ( '8485',' VIJAY KUMAR MISHRA','20')
Insert Into Employees (ID,FirstName,Age) Values ( '8486',' VINEET KUMAR SAXENA','35')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J001',' ANURAG', 'MISHRA','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J002',' ANURAG', ' DWIVEDI','24')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J006',' A.B.', ' KATIYAR','23')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J007',' AJAY', ' NAGAR','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J009',' AMIT KUMAR', ' SINGH','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J011',' AKHILESH KUMAR', ' VERMA','25')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J012',' ASHUTOSH', ' SRIVASTVA','23')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J013',' AKHILESH', ' BABU','22')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J018',' ASHOK KUMAR', ' PAL','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J019',' ANIL KUMAR', ' SINGH','27')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J023',' AMRISH', ' SHARMA','31')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J151',' CHANDRA PRATAP', ' SINGH','35')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J152',' C.P.', ' MISHRA','27')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J201',' DILIP', ' NAYAK','26')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J204',' DHEERAJ KUMAR', ' SACHAN','30')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J205',' DEVBRAT', ' SINGH','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J209',' DINESH', ' MISHRA','19')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J210',' DHARMENDRA KR.', ' PANDEY','34')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J316',' HEMANT', ' KUMAR','27')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J350',' JAGDISH', ' AWASTHI','26')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J366',' K.K.', ' KHULAR','35')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J371',' KAVIRAJ',' ','27')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J372',' KRISHNA KANT', ' SINGH','27')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J383',' LIBREN STENLY', ' LUGEN','23')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J384',' LING RAJ', ' SAHU','29')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J396',' MAHENDRA', ' PRAJAPATI','29')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J400',' MANJEET SINGH', ' REHSHI','30')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J403',' MAHENDRA', ' KHARAD','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J405',' MANPREET SINGH', ' JABBAL','33')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J411',' NEERAJ', ' TIWARI','33')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J416',' NARENDRA PRATAP', ' SINGH','35')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J417',' NARAYAN', ' SINGH','34')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J443',' PRAMOD KUMAR', ' SHARMA','30')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J444',' PRAFUL CHANDRA', ' RAI','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J445',' PRAKASH', ' SHARMA','34')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J447',' PRAVEEN KUMAR', ' DUBEY','24')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J448',' PRADEEP', ' KUMAR','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J450',' PRADEEP KR.', ' DWIVEDI','18')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J451',' PARTHA', ' CHOUDHARY','31')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J501',' RAM BABU', '','31')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J502',' RAJEEV', ' SHAHANI','33')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J508',' RAJEEV', ' LOCHAN','19')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J510',' RAJIV', ' KUMAR','21')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J551',' SANJAY', ' SINGH','32')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J554',' SUBODH', ' TIWARI','23')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J556',' SHIV SHANKAR', ' GOSWAMI','32')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J559',' SANJAY', ' TOMAR','30')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J562',' SURENDER', ' PAL SINGH','22')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J563',' SANDEEP', ' KUMAR','31')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J651',' T.', 'RAMESH','20')
Insert Into Employees (ID,FirstName,LastName,Age) Values ( 'J701',' UDAY RAJ', ' SINGH','20')

--*****************************************************************************************************************
-- Create HR_MAST_DEPT Table

CREATE TABLE HR_MAST_DEPT
(
[code] [char] (2) Primary Key ,
[Dpname] [varchar] (25) NOT NULL ,
[city] [varchar] (25),
[country] [varchar] (50),
[inhouse] [bit],

)
gO

-- Fill HR_MAST_DEPT Table

Insert Into HR_MAST_DEPT Values ( '10' , 'Information Technology' , 'Delhi' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '20' , 'Human Resources Dept' , 'Mohali' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '30' , 'Marketing' , 'Toronto' , 'Canada' , '0' )
Insert Into HR_MAST_DEPT Values ( '40' , 'Civil' , 'Calgiri' , 'Canada' , '0' )
Insert Into HR_MAST_DEPT Values ( '50' , 'Sales' , 'Gurgaon' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '60' , 'Commercial' , 'Delhi' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '70' , 'Admin' , 'Noida' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '80' , 'Testing' , 'Gurgaon' , 'India' , '1' )
Insert Into HR_MAST_DEPT Values ( '90' , 'Implementing' , 'Delhi' , 'India' , '1' )

--*****************************************************************************************************************

Tuesday, May 20, 2008

Small but unforgettable Questions of SQL Server

1. How to run a query on a remote SQL Server?
Ans: To do the same use OPENROWSET:
Syntax: SELECT * FROM OPENROWSET('SQLOLEDB',REMOTE_SERVER_NAME';'sa';'password','SQL STATEMENT')
Example: Select * from OPENROWSET('SQLOLEDB','local';'sa';'','Select * from employees')

2.How can execute operating system comman from within SQL Server?
Ans: The xp_cmdshell stored procedure helps to do the same.
Example:EXEC MASTER..xp_cmdshell 'Dir C:\'

3. If we have two triggers of same type on a table, then which one will fire first?
Ans: They will fire as they are created. However we can set the trigger order by using stored procedure sp_settriggerorder, but the first and last triggers have of different types. Also define first and last triggers rest will fire as they have created.

SQL Server Script to Create Windows Directories

The script is originally written by : Tim Ford
One can attain the same with the use of two inbuild stored procedure master.sys.xp_dirtree and master.sys.xp_create_subdir

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

Brief Code Overview
The core functionality in this script is based on two extended system stored procedures: master.sys.xp_dirtree and master.sys.xp_create_subdir. Let's take a look at each, individually:

  • master.sys.xp_dirtree - This extended stored procedure returns all the folders within the folder that is passed into it as a parameter. It also returns the nested level of each folder found. By inserting the values returned from xp_dirtree into the temp table you can then query against it to test the existence of the folder you are attempting to create.
  • master.sys.xp_create_subdir - Use this stored procedure to create a folder on either a local server or network share.

How to export data from SQL Server to Excel

Exporting data from SQL Server to Excel can be achieved in a variety of ways:
  • Data Transformation Services [DTS]
  • SQL Server Integration Services [SSIS]
  • Bulk Copy [BCP]

Rest of above GUI operation there is another option available via the T-SQL language is the OPENROWSET command.This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:


OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
'provider_string' }
, { [ catalog. ] [ schema. ] object
'query'
}
BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
SINGLE_BLOB SINGLE_CLOB SINGLE_NCLOB }
} )
::=
[ , CODEPAGE = { 'ACP' 'OEM' 'RAW' 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]

With the following example you can write a simple job for the operation:-
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

How to Read the SQL Server log files using T-SQL

The system stored procedure sp_readerrorlog allows to read the contents of the SQL Server error log files directly from a query window and also allows to search for certain keywords when reading directly from the error file.
Lets check following sample: -

CREATE PROC [sys].[sp_readerrorlog](
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN

IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
BEGIN
RAISERROR(15003,-1,-1, N'securityadmin')
RETURN (1)
END

IF (@p2 IS NULL)
EXEC sys.xp_readerrorlog @p1
ELSE
EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END


This procedure takes four parameters:
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
If you do not pass any parameters this will return the contents of the current error log.

Some examples:
  • EXEC sp_readerrorlog 6 -returns all of the rows from the 6th archived error log
  • EXEC sp_readerrorlog 6, 1, '2005' - returns just 8 rows wherever the value 2005 appears
  • EXEC sp_readerrorlog 6, 1, '2005', 'exec' - returns only rows where the value '2005' and 'exec' exist

A mindful brain

Some days back I have been come to contact with a very nice person who has really mind clowing brain. I readt all it out from her descent writtings her style of writting an expressing the matter among someones.
I must appreciate to visit her blog : http://www.shwetagupta.com/blog/. The only and the only name in my mind is Shweta Gupta, Director of http://www.criticat.com/

My wishes are with you Shweta, go ahead and acheieve more hikes.

Best of Luck!

Saturday, May 10, 2008

Just-in-Time [JIT]

JIT compiler is a crucial component of the .NET framework. The JIT compiler converts IL to machine code, which is then executed. The JIT compiler does not compile the entire code at once because it could hamper the performance of the program. It compiles the code at runtime, at the time it is called. The code that is compiled gets stored until the execution comes to an end. This avoids recompilation of code.

Types of JIT
There following three types of JIT:
Pre-JIT
It compiles whole source code into native code in a single compilation cycle. This is done at the time of deployment of the application
Econo-JIT
It compiles only those methods that are called at runtime. However, these methods are removed when they are not required.
Normal-JIT
It also compiles only those methods which are called at runtime. These methods are compiled the first time they are called, and then they are stored in cache. When the same methods are called again, the compiled code from cache is used for execution

Common Language Runtime [CLR]

Let's rewind our mind to ancient era, when there is no existence of CLR concept, what happened when we invoke the language program or code. Basically, languages consist both compiler and runtime environment. Compiler converts or compiles code to executable files [known as PE (Portable file) files], which can be run by the users, in the other hand runtime environment provides O.S. [Operating System] services to executable code. Now at that time each language has its own runtime environment. Like Visual Basic contained MSVBVM60.DLL, Visual C++ contained MSVCRT40.DLL and for Java we need JRE [Java Runtime Environment] or JVM [Java Virtual machine].
But, with the invention of Common Language Runtime there is no need to gather individual runtime environment for individual languages, that's why .Net runtime is known as Common Language Runtime [CLR] Environment.

.NET framework

.NET framework is a Tool, which provides an environment for building, deploying and running web services and other application.

It also predicts from above figure that .NET framework [see fig11] consists of CLR and a single of unit of complete set of Class Libraries, provides the scarcity to develop Windows applications and Web Application with the help of .NET programming languages.

According to .NET documentation of Microsoft:
The .NET Framework is a new computing platform that simplifies application development in the highly distributed environment of the Internet. The .NET Framework is designed to fulfill the following objectives:

  1. To provide a consistent object-oriented programming environment whether object code is stored and executed locally, executed locally but Internet-distributed, or executed remotely.

  2. To provide a code-execution environment that minimizes software deployment and versioning conflicts.

  3. To provide a code-execution environment that guarantees safe execution of code, including code created by an unknown or semi-trusted third party.
    To provide a code-execution environment that eliminates the performance problems of scripted or interpreted environments.

  4. To make the developer experience consistent across widely varying types of applications, such as Windows-based applications and Web-based applications.

  5. To build all communication on industry standards to ensure that code based on the .NET Framework can integrate with any other code.

The Object Oriented approach

The fundamental idea behind object-oriented languages is to combine into a single unit both data and the functions that operate on that data. Such unit is called an Object.

An object functions, called member function in OOP, typically provide the only way to access its data. If you want to read data item in a object, you call a member function in the object. It will read the item and return the value to you. You can't access the data directly. The data is hidden, so it is safe from accidental alteration. Data and its functions are said to be encapsulated into a single entity.

If you want to modify the data in an Object, you know exactly what functions interact with it : the member functions in the object. No other functions can access the data. fig1 shows conceptual view of OOP approach.

Keep in mind that Object Oriented Programming is not primarily concerned with the details of program operation. It deals with the overall organization of the program.

Characteristics of Object Oriented Language
It will be divided into objects. Thinking in terms of objects, rather than functions


  1. Physical objects

  2. Elements of the Computer user environment

  3. Programming constructs

  4. Collection of data


Stored procedure with a parameter and output parameter

The following example is for Microsoft SqlServer. One can try and run this is for learners.
Step-1 Create a Stored Procedure
CREATE PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName

Step-2 After creating a stored procedure, run the below block of code. This will execute the above stored procedure and if the ContactID has a value it will also return the person and address info
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName='Smith', @ContactID=@ContactID OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END