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>

Leave A Reply