.NetBoy

JAVA SCRIPT for showing message in status bar

2008-10-16T11:59:00.000-07:00

message = "Welcome to Indira Gandhi National Open University^" + "http://www.ignou.ac.in is the official website of Indira Gandhi National Open University.^" + "^"
scrollSpeed = 25
lineDelay = 1500
txt = ""
function scrollText(pos)
{ if (message.charAt(pos) != '^')
{ txt = txt + message.charAt(pos) status = txt pauze = scrollSpeed
}
else
{ pauze = lineDelay txt = "" if (pos == message.length-1) pos = -1
} pos++ setTimeout("scrollText('"+pos+"')",pauze)
}
scrollText(0)

how to create columns dynamically in a grid view

2008-04-06T00:26:00.000-07:00

Download source files - 6 Kb

Introduction

This article describes about how to create columns dynamically in a grid view.
Many times we have the requirement where we have to create columns dynamically.
This article describes you about the dynamic loading of data using the DataTable as the datasource.

Details of the Grid

Let’s have a look at the code to understand better.

Create a gridview in the page,

Drag and drop the GridView on to the page

Manually type GridView definition in the page.

<tr>

<td>

<strong>Dynamic Gridstrong>td>

tr>

<tr>

<td>

<asp:GridView ID="GrdDynamic" runat="server" AutoGenerateColumns="False">

Columns>

asp:GridView>

td>

tr>

table>

With this we are done with creating a GridView in the page. Let’s move on to the code- beside to understand the background history of the page.

public partial class _Default : System.Web.UI.Page

{

#region constants

const string NAME = "NAME";

const string ID = "ID";

#endregion

protected void Page_Load(object sender, EventArgs e)

{

loadDynamicGrid();

}

private void loadDynamicGrid()

{

#region Code for preparing the DataTable

//Create an instance of DataTable

DataTable dt = new DataTable();

//Create an ID column for adding to the Datatable

DataColumn dcol = new DataColumn(ID ,typeof(System.Int32));

dcol.AutoIncrement = true;

dt.Columns.Add(dcol);

//Create an ID column for adding to the Datatable

dcol = new DataColumn(NAME, typeof(System.String));

dt.Columns.Add(dcol);

//Now add data for dynamic columns

//As the first column is auto-increment, we do not have to add any thing.

//Let's add some data to the second column.

for (int nIndex = 0; nIndex <>

{

//Create a new row

DataRow drow = dt.NewRow();

//Initialize the row data.

drow[NAME] = "Row-" + Convert.ToString((nIndex + 1));

//Add the row to the datatable.

dt.Rows.Add(drow);

}

#endregion

//Iterate through the columns of the datatable to set the data bound field dynamically.

foreach (DataColumn col in dt.Columns)

{

//Declare the bound field and allocate memory for the bound field.

BoundField bfield = new BoundField();

//Initalize the DataField value.

bfield.DataField = col.ColumnName;

//Initialize the HeaderText field value.

bfield.HeaderText = col.ColumnName;

//Add the newly created bound field to the GridView.

GrdDynamic.Columns.Add(bfield);

}

//Initialize the DataSource

GrdDynamic.DataSource = dt;

//Bind the datatable with the GridView.

GrdDynamic.DataBind();

}

Let’s start dissecting right from the top,

After the page declaration, I have created 2 constants which represent the fields in the outputted grid. After that I am calling the loadDynamicGrid() method which will actually work for you. In this method,

Creating a DataTable which will hold the table definition and data for the GridView. This table is used as a DataSource for the GridView

Once the DataTable is created, I am creating 2 columns, ID & Name of type integer (ID) and string (Name) and adding them to the DataTable.

Since the table structure is ready, I am generating some sample data by iterating through a loop for displaying in the GridView.

Now I am iterating through the Columns of the DataTable to create the dynamic columns in the Gridview.

The logic behind creating dynamic column starts by creating a BoundField instance.

Once the BoundField is created, I am initializing the DataField and HeaderText with the column name of the DataTable.

After the BoundField’s basic necessary properties are defined. I am adding the newly created bound field to the GridView control

Once the creation of the dynamic columns is completed, I am assigning the DataSource of the GridView and calling the DataBind method to load the GridView with data dynamically.

That’s all your dynamic GridView is ready. I hope this information would be helpful.

In my next article, I will describe about how to create TemplateColumn dynamically. By now, you must have understand how easy is creating BoundColumn. To create any other type of columns like asp:ButtonField, asp:CheckBoxField, asp:CommandField, asp:HyperLinkField, asp:ImageField the logic & strategy is same, except the template column.

Enjoy programming.

Obtaining BLOB Values from a Database

2008-01-26T03:35:00.000-08:00

By default, the DataReader loads incoming data as a row as soon as an entire row of data is available. Binary large objects (BLOBs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload that will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received. This is ideal for loading BLOBs or other large data structures. Note that this behavior may depend on your data source. For example, returning a BLOB from Microsoft Access will load the entire BLOB being loaded into memory, rather than sequentially as it is received.

Example---------
The following example returns the publisher ID and logo from the pubs sample database in Microsoft SQL Server 2000. The publisher ID (pub_id) is a character field, and the logo is an image, which is a BLOB. Because the logo field is a bitmap, the example returns binary data using GetBytes. Notice that the publisher ID is accessed for the current row of data before the logo, because the fields must be accessed sequentially.

// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand(
"SELECT pub_id, logo FROM pub_info", connection);

// Writes the BLOB to a file (*.bmp).
FileStream stream;
// Streams the BLOB to the FileStream object.
BinaryWriter writer;

// Size of the BLOB buffer.
int bufferSize = 100;
// The BLOB byte[] buffer to be filled by GetBytes.
byte[] outByte = new byte[bufferSize];
// The bytes returned from GetBytes.
long retval;
// The starting position in the BLOB output.
long startIndex = 0;

// The publisher id to use in the file name.
string pubID = "";

// Open the connection and read data into the DataReader.
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

while (reader.Read())
{
// Get the publisher id, which must occur before getting the logo.
pubID = reader.GetString(0);

// Create a file to hold the output.
stream = new FileStream(
"logo" + pubID + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
writer = new BinaryWriter(stream);

// Reset the starting byte for the new BLOB.
startIndex = 0;

// Read bytes into outByte[] and retain the number of bytes returned.
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);

// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
writer.Write(outByte);
writer.Flush();

// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
}

// Write the remaining buffer.
writer.Write(outByte, 0, (int)retval - 1);
writer.Flush();

// Close the output file.
writer.Close();
stream.Close();
}

// Close the reader and the connection.
reader.Close();
connection.Close();

Generating Excel Report in ASP.NET 2.0

2008-01-15T10:08:00.000-08:00

Introduction

Excel is such a powerful tool that almost all companies make extensive use of it for analyzing data. Moreover, the internet is not as it was 10 years ago. Almost all pages that are out there on the internet are dynamic ones, that is, interacts with a database in backend to produce results. Sometimes, data that are displayed, if made available on in an Excel file, proper analysis of the data often helps in making more accurate decisions by using Excel features. In this tutorial, we will learn how to interact with Excel files, both reading and writing. You can download Excel Reports Example ASP.NET Project associated with this tutorial.

Excel - ASP.NET Scenarios

For the purpose of this tutorial, an Excel sheet had been prepared that holds record of students and their respective marks in subjects. Please note that these records are fictitious and had been input randomly.

StudentId	StudentName
10001	John Thomas
10002	Terry Lane
10003	Anne Marie
10004	Tom Sawyer
10005	Derek Ince
10006	Emerson Boyce
10007	Thommas Kolka
10008	Edison Hall
10009	Teddy Harewood
10010	Williams Yorkshire

Fig 1. Table holding information for students

StudentId	Marks
10001	50
10002	60
10003	75
10004	80
10005	90
10006	95
10007	100
10008	20
10009	48
10010	66

Fig 2. Table holding student marks in Mathematics subject

StudentId	Marks
10001	30
10002	25
10003	65
10004	78
10005	95
10006	64
10007	32
10008	88
10009	95
10010	99

Fig 3. Table holding student marks in Geography subject

Interacting with Excel

Fortunately, there are COM objects that are available to interact with the Excel file. Also, we can make use of the OleDB class to treat the Excel file as a simple table. In this way, we can fire SQL queries to the Excel file so that we can retrieve data from the sheets. An analogy can be made to a database. The Excel Workbook is considered as the Database while the Sheets are considered as tables. Microsoft Excel 11.0 Object Library should be added as Reference to get the features for interacting with Excel.

Fig 4. Adding Microsoft Excel 11.0 Object Library as Reference

The Excel file is then added to the DataSource folder in ASAP.NET to be interacted with. To connect to a certain database, a connection string is needed. The same thing applies for Excel. The following is a key added in the Web.Config file for future reference.

Note that our Excel file which contains the necessary data will be SchoolMgt.xls, as written in the connection string.

Fig 5. Excel file illustrating Excel Book containing data sheets with data

Reading data from Excel sheets

Let us build an interface for getting data from the Excel file.

Fig 6. Interface to search records per table selected.

It is important to import the following:

Imports System.Data.OleDb
Imports System.Data

The following code creates the connection and builds the query based on the selected table.

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String

strSQL = "SELECT * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"

dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgResults.DataSource = dsExcel
dgResults.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub

Please note that the table name in the SELECT query should be in the format [TableName$], which the square brackets and the dollar sign at the end.

Playing with SELECT statements

As you have guessed, the Excel file has turned out to be a normal database. The transparency that the OleDB connection had made is exceptional. Also, note that the first row is taken to contain field names for the columns.

Now, let us get the marks of the students in Mathematics, Geography and Total, sorted in Descending order.

Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try

Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String

strSQL = "SELECT S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
"FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
"WHERE(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
"ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgReports.DataSource = dsExcel
dgReports.DataBind()

Catch ex As Exception
Throw ex
End Try
End Sub

Fig 7. Report generating the students' marks and total, sorted in descending order.

Generating Excel Reports

Two ways for generating Excel report will be discussed in this tutorial. The first one is using the "Response" class and the second one by manipulating the Excel objects that "Microsoft Excel 11.0" Objects provide.

The following piece of code illustrates how this is done

Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
' Variables declaration
Dim dsExport As New DataSet()
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim dgGrid As New DataGrid()

dgGrid.DataSource = getData()

' Report Header
hw.WriteLine(" Student Marking Report ")

' Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(hw)

' Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
Response.Write(tw.ToString())
Response.End()
End Sub

First, we create the dataset that will hold the records. Then, a StringWriter object (tw) is also created along with an HtmlTextWriter (hw) which takes as parameter the text writer object. They are important for rendering purposes; i.e. html tags can be applied like bold, italic etc. to the resulting Excel report.
Also, a datagrid object which will store the dataset of records.
Note that function getData() in the code snippet only contains the codes in the previous example which returns a dataset of records.
hw.WriteLine(" Student Marking Report ") shows that you can also combine HTML codes with the output for proper display. In our example, the font size will be 5, underlined and bold.
dgGrid.RenderControl(hw) renders the HTML object with the data that the datagrid contains. The result is normally formatted in a tabular format.
Response.ContentType = "application/vnd.ms-excel" makes the Reponse object output to Excel.
Response.Write(tw.ToString()) outputs the formatted object to Excel.

Figure 8. Output result showing the report that is generated in Excel

The second way for generating Excel reports from ASP.NET is to use the Microsoft Excel 11.0 Objects. This method offers more fallibility in terms of manipulating the features that Excel provides and formatting the report.

The following code snippet makes use of the various Excel objects that Excel provides to generate the report.

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel

Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
Try
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorkSheet = xlWorkBook.ActiveSheet

' Gets the dataset containing the data
Dim dsData As DataSet = getData()
Dim i As Integer = 2

' Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID"
xlWorkSheet.Cells(1, 2) = "Student Name"
xlWorkSheet.Cells(1, 3) = "Mathematics"
xlWorkSheet.Cells(1, 4) = "Geography"
xlWorkSheet.Cells(1, 5) = "Total"

xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
xlWorkSheet.Range("$A1:$E1").Font.Bold = True

' Outputting the data
For Each dr As DataRow In dsData.Tables(0).Rows
xlWorkSheet.Cells(i, 1) = dr(0)
xlWorkSheet.Cells(i, 2) = dr(1)
xlWorkSheet.Cells(i, 3) = dr(2)
xlWorkSheet.Cells(i, 4) = dr(3)

' Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())

' Going to the next row
i = i + 1
Next

' Auto fit the columns
xlWorkSheet.Columns.AutoFit()

' Generating the graph
Dim chart As Excel.Chart
chart = xlWorkBook.Charts.Add()

With chart
.ChartType = Excel.XlChartType.xlColumnClustered
.SetSourceData(xlWorkSheet.Range("A1:E11"), 2)

.HasTitle = True
.ChartTitle.Characters.Text = "Students' marks"

.Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
.Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
End With
Catch ex As Exception
Throw ex
End Try
End Sub

An Excel workbook is first created. Then the active sheet is accessed by xlWorkSheet = xlWorkBook.ActiveSheet. After that, we get the data into the dataset, ready to be processed. Using the Font property, the title is displayed in Pink bold colour. Note how the Formula that Excel provides is properly used to calculate the SUM of the marks for each student xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).

Figure 9. Excel Report having output the list of students and their respective marks.

A chart is also generated using the objects. The x-axis consists of the names and id of each student. The y-axis, the marks for the different students. The chart is always handy for analysis of data.

Figure 10. Chart that is generated using Microsoft Excel 11.0 Objects

Summary

In this tutorial, you learned how to read data from Excel, generate report using the Response objects and to play with the Microsoft Excel 11.0 objects provided. Indeed, it is impossible to cover all features in this tutorial but many important techniques were covered to help you down the line. It is up to you to be creative and adapt the techniques to your scenarios.

2008-01-15T10:04:00.001-08:00

ASP.Net 2.0: Export GridView to Excel

2008-01-15T09:45:00.000-08:00

Introduction:

In this article, we will see how to Export an ASP.Net 2.0 GridView to Excel.

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.

The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Step 1: Setup your web page with the Gridview

In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario.

ContactPhone Table Structure:

Column Name	Type
ContactID	Int (Identity)
FName	Varchar(50)
LName	Varchar(50)
ContactPhone	Varchar(20)

Step: The Actual Export

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

To avoid this error, add the following code:

public override void VerifyRenderingInServerForm(Control control)

{

}

Step : Convert the contents

If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added.

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i <>

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

Code Listing:

Image: Page Design

Image : Sample in action

Image: Export to Excel button is clicked

Image: GridView contents exported to Excel

ExcelExport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %>

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

<title>Contacts Listingtitle>

head>

<body>

<div>

Contacts Listing

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" />span>strong><br />

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"

DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />

<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />

<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />

<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />

<asp:TemplateField HeaderText="Favorites">

<asp:CheckBox ID="CheckBox1" runat="server" />

ItemTemplate>asp:TemplateField>

Columns>

asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"

DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"

ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"

SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"

UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

InsertParameters>

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

<asp:Parameter Name="ContactID" Type="Int32" />

UpdateParameters>

<asp:Parameter Name="ContactID" Type="Int32" />

DeleteParameters>

asp:SqlDataSource>

<br />

div>

form>

body>

html>

ExcelExport.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

public partial class DeleteConfirm : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//Export the GridView to Excel

PrepareGridViewForExport(GridView1);

ExportGridView();

}

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i <>

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

Implementation Options:

In quite a few cases, developers face an error in the Export functionality - typically the error message is "RegisterForEventValidation can only be called during Render();".

Our website readers have contributed some good suggestions in the article comments below. I would particularly like to highlight the suggestion by Marianna, who provides an alternative implementation to the VerifyRenderingInServerForm override. This approach is described below:

Step 1: Implement the Export functionality as described above.

Step 2: Remove the code to override the VerifyRenderingInServerForm method.

Step 3: Modify the code for the ExportGridView function as below. The code highlighted in green creates and HtmlForm on the fly, before exporting the gridview, adds the gridview to this new form and renders the form (instead of rendering the gridview in our original implementation)

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

// Create a form to contain the grid

HtmlForm frm = new HtmlForm();

GridView1.Parent.Controls.Add(frm);

frm.Attributes["runat"] = "server";

frm.Controls.Add(GridView1);

frm.RenderControl(htw);

//GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

This implementation has the advantage that it can be setup as re-usable code in a separate library, without having to override the base class method each time.

Gridview, ObjectDataSource Making life easy

2008-01-14T11:07:00.000-08:00

Gridview, ObjectDataSource Making life easy

Introduction

In this Article I will give some brief on sorting, searching, paging in gridview with ObjectDataSource. There are many articles on the internet which had demoed the same but had used SqlDataSources which can’t be done in live projects, ObjectDataSource will preserve the three tier architecture of an Application.

Prerequisite

· Sql Server 2000(Northwind database is used for sample app)

· Visual Studio 2005

· EntLib 2.0 (DataAccess)

The Sample Application

The application used for this article is having

· Default.aspx

· BizLayer.cs

· DataAccess.cs

In this Application the Gridview will use object data source for auto paging, sorting etc. Object data source will be using bizlayer methods for selection and searching.

Sample Application Architecture

Getting Started

Drag and drop a grid view, an object data source form the toolbox data tab on default.aspx, once done you will get something like

Now configure the data source by clicking the Configure data source as show above and the wizard will take us forward.

Populating the gridview (The select Method)

In This method we will be selecting all the records available in Customers table of Northwind database.

Step 1: Choose your business object

Step 2: Select BizLayer from the drop down.

Step 3: Choose a method of the business object that will return data associated with the select operation. We will select GetCustomers method from the drop down list for our application.

Step 4: selecting a Data source for our grid. Click the smart tag of our grid

Step 5: Select odsNorthWind(object data source) form the Choose data source drop down list.

Enabling auto paging/Sorting

After selecting the data source for the grid we will get a menu where check the enable paging and sorting option for auto paging/sorting, and that all now our grid will have paging and sorting capability with out writing a single line of code, isn’t that great.

Search

Its very common scenario where we are supposed to filter the data in grid, Object data source has given a property to do the same, what we are only suppose to do is just supply the search string to filter expression property of object data source and that’s all J.

FilterExpression Property: Gets or sets a filtering expression that is applied when the method that is specified by the SelectMethod property is called (as per MSDN).

In the Sample app we have provide a search on company name, so the filterexpression property for the same can be coded as

protected void btnSearch_Click(object sender, EventArgs e)

{

if (txtCustName.Text != "")

{

odsNorthWind.FilterExpression = "CompanyName LIKE '%" + txtCustName.Text + "%'";

}

Update

Again updating is also a very common scenario in data manipulation applications, In the sample application we will update the contact name of a selected customer.

Editing gridview data

For enabling editing in a gridview row first add an edit column to the gridview. To do that first click the grid view smart tag then click edit column link and then add an Edit, update, delete command from Command field as shown below.

Click Ok.

In the sample application the grid is having only three columns form the customer table and a command column.

Code Manipulation for gridview edit

1. Make the readonly property of BoundField to false for all the fiield which are supposed to me edited in gridview.

2. Set the DataKeyNames property of gridview to the ID of the table, in sample app DataKeyNames="CustomerID"

<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"

ReadOnly="True" SortExpression="CustomerID" />

<asp:BoundField DataField="CompanyName" HeaderText="Company Name"

ReadOnly="True" SortExpression="CompanyName" />

<asp:BoundField DataField="ContactName" HeaderText="ContactName"

ReadOnly="false" SortExpression="ContactName" />

<asp:CommandField ShowEditButton="True" />

Columns>

<asp:GridView ID="gvCustomers" runat="server" DataKeyNames="CustomerID" AllowPaging="True" AllowSorting="True" CellPadding="4" DataSourceID="odsNorthWind" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" EmptyDataText="No Record Available">

Adding update method to Object Data Source

The last part left is to add an update method to the data source control,to do so click the smart tag of ObjectDataSource,then click Configure data source and click next then select update tab. Select an update method form Chosse a method drop down as shown below, in sample app we had selected UpdateCustomer method.

The Sample App Code

BizLayer.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public class BizLayer

{

public BizLayer()

{

// TODO: Add constructor logic here

}

public DataSet GetCustomers()

{

DataAccess dataAcess = null;

try

{

dataAcess = new DataAccess();

return dataAcess.GetCustomers();

}

finally

{

dataAcess = null;

}

public void UpdateCustomers(string CustomerID,string contactName)

{

DataAccess dataAcess = null;

try

{

dataAcess = new DataAccess();

dataAcess.UpdateCustomers(CustomerID, contactName);

}

finally

{

dataAcess = null;

}

DataAccess.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

public class DataAccess

{

public DataAccess()

{

// TODO: Add constructor logic here

}

public DataSet GetCustomers()

{

DataSet dsGetCustomer = null;

DbCommand dbCommand = null;

Database db = DatabaseFactory.CreateDatabase("Northwind");

dbCommand = db.GetSqlStringCommand("select * from Customers");

dsGetCustomer = db.ExecuteDataSet(dbCommand);

return dsGetCustomer;

}

public void UpdateCustomers(string CustomerID,string contactName)

{

DbCommand dbCommand = null;

Database db = DatabaseFactory.CreateDatabase("Northwind");

dbCommand = db.GetSqlStringCommand("update Customers SET ContactName = '"+contactName+"' where CustomerID = '" + CustomerID+"'");

db.ExecuteNonQuery(dbCommand);

}

Default.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSearch_Click(object sender, EventArgs e)

{

if (txtCompanyName.Text != "")

{

odsNorthWind.FilterExpression = "CompanyName LIKE '%" + txtCompanyName.Text + "%'";

}

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

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

<title>GridView and ObjectDataSource making Life easytitle>

head>

<body>

<tr>

<td>

<asp:Label ID="Label1" runat="server" Text="Enter Company Name :">asp:Label>

<asp:TextBox ID="txtCompanyName" runat="server">asp:TextBox>

<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />

td>

tr>

<tr>

<td>

td>

tr>

<tr>

<asp:GridView ID="gvCustomers" runat="server" DataKeyNames="CustomerID" AllowPaging="True" AllowSorting="True"

CellPadding="4" DataSourceID="odsNorthWind" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" EmptyDataText="No Record Available">

<asp:BoundField DataField="CustomerID" HeaderText="Customer ID" ReadOnly="True" SortExpression="CustomerID" />

<asp:BoundField DataField="CompanyName" HeaderText="Company Name" ReadOnly="True" SortExpression="CompanyName" />

<asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />

<asp:CommandField ShowEditButton="True" />

Columns>

asp:GridView>

<asp:ObjectDataSource ID="odsNorthWind" runat="server" SelectMethod="GetCustomers"

TypeName="BizLayer" UpdateMethod="UpdateCustomers">

<asp:Parameter Name="CustomerID" Type="String" />

<asp:Parameter Name="contactName" Type="String" />

UpdateParameters>

asp:ObjectDataSource>

td>

tr>

<tr>

<td>

td>

tr>

<tr>

<td>

td>

tr>

table>

form>

body>

html>

Convert a Number into Words

2008-01-14T10:57:00.000-08:00

Convert a Number into Words

When developing commercial applications, particularly when those applications perform activities such as cheque printing, it can be necessary to display numeric values using the equivalent text. This article describes an algorithm to achieve this.

Algorithm Goals

There are many occasions when it is necessary to output a numeric value using text rather than numeric digits. A common example is the production of cheques. These usually require both the numeric and the spoken-word equivalent text to be printed. Unfortunately, the .NET framework does not provide a simple method by which this conversion can be made so a custom algorithm must be developed.

This article describes an algorithm that converts a numeric value into the equivalent text. It goes on to provide the C# code for this task. For simplicity, the article concentrates on the generation of English words for 32-bit integer values. However, you can extend the method easily to add other languages, decimal number conversion and currency information. Alternatively, you can support the BlackWasp web site by purchasing and downloading our Number to Words Component that supports additional functionality, larger numbers and greater customisation from only £2.00.

Large Number Names

The English names of the smaller numbers are generally accepted. However, once a number to be converted reaches a value of one thousand million or greater, the names of numbers vary according to usage. The algorithm described in this article uses the modern short scale numbering. In this scale, one thousand million is equivalent to one billion. This is different to the less-used traditional British long scale numbering where a billion is one million million.

The following table lists the short scale number names included in the algorithm and their numeric values. The range of numbers has been selected to provide for the full range of values that may be held in a 32-bit integer. If you wish to extend the functionality of the algorithm to larger numbers, a Wikipedia article exists that lists the names of large numbers.

Value	Name
1	one
10	ten
100	hundred
1,000	thousand
1,000,000	million
1,000,000,000	billion

Number Rules

The set of rules for converting an integer number to text initially appear to be reasonably complex. However, once analysed, they can be separated into a small number of distinct rule groups that are simpler to implement individually. To provide a single algorithm for all integer values there are six such rule groups:

Zero Rule. If the value is zero then the number in words is 'zero' and no other rules apply.
Three Digit Rule. The integer value is split into groups of three digits starting from the right-hand side. Each set of three digits is then processed individually as a number of hundreds, tens and units. Once converted to text, the three-digit groups are recombined with the addition of the relevant scale number (thousand, million, billion).
Hundreds Rules. If the hundreds portion of a three-digit group is not zero, the number of hundreds is added as a word. If the three-digit group is exactly divisible by one hundred, the text 'hundred' is appended. If not, the text "hundred and" is appended. eg. 'two hundred' or 'one hundred and twelve'
Tens Rules. If the tens section of a three-digit group is two or higher, the appropriate '-ty' word (twenty, thirty, etc.) is added to the text and followed by the name of the third digit (unless the third digit is a zero, which is ignored). If the tens and the units are both zero, no text is added. For any other value, the name of the one or two-digit number is added as a special case.
Recombination Rules. When recombining the translated three-digit groups, each group except the last is followed by a large number name and a comma, unless the group is blank and therefore not included at all. One exception is when the final group does not include any hundreds and there is more than one non-blank group. In this case, the final comma is replaced with 'and'. eg. 'one billion, one million and twelve'.
Negative Rule. Negative numbers are always preceded by the text 'negative'.

The Algorithm and the Code

Preparation

Depending upon how you wish to use this algorithm, you may want to create it in a new class, incorporate it in an existing class or simply hide it behind a form event. To keep the code simple and short, this article assumes you have a class of some kind prepared and are adding a new method to that class. The code simply declares a new public method that accepts the integer to be converted as its only parameter.

To prepare, create or identify the class that you will add the method to, then add the following declaration code:

// Converts an integer value into English words
public string NumberToWords(int number)
{

}

NB: If you are creating or adding to a utilities class, you may wish to adjust the declaration to make the method static. If you do, all of the other declarations detailed below must also be marked as static.

Declaring the Names for Numbers

There are three key groups of names for numbers that must be declared. Firstly there are the small numbers between zero and nineteen that are used by the tens rule. The second group of numbers is used to represent the tens values from twenty to ninety. Finally we have the scale numbers. These are the large numbers that are used during recombination.

The number names must be stored within the program code in a manner that permits easy identification. In this example code, we will declare three arrays. An array of twenty items holds the small numbers, an array of ten items is used for the tens values (with two initial unused entries) and an array of four values contains the scale number names with the first entry containing an empty string.

As the code will eventually include private methods in addition to the public method already declared, the arrays are created as private class-level variables. This allows the arrays to be accessible by the entire class without being passed between functions. The numbers are defined in Title Case as this is aesthetically pleasing and can be easily converted to upper or lower case after the process is complete.

Add the following declarations to the class to declare and initialise the arrays:

// Single-digit and small number names
private string[] _smallNumbers = new string[]
   {"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight",
    "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen",
     "Sixteen", "Seventeen", "Eighteen", "Nineteen"};
   
// Tens number names from twenty upwards
private string[] _tens = new string[]
   {"", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty",
    "Ninety"};
   
// Scale number names for use during recombination
private string[] _scaleNumbers = new string[]
   {"", "Thousand", "Million", "Billion"};

Applying the Zero Rule

The zero rule is the easiest to implement. All that is required is a simple if statement to test for a value of zero in the integer parameter. If the value is zero, the text returned is 'Zero' (from item 0 in the small numbers array). In all other cases, the remaining rules must all be considered.

To implement the zero rule, the following code is added within the NumberToWords method:

// Zero rule
if (number == 0)
   return _smallNumbers[0];

Separating the Number into Three-Digit Groups

Once it has been determined that the value is non-zero, it is necessary to split the number into its three-digit groups. This is relatively easy using a mixture of the modulus (%) and division (/) operators. When any integer is divided by one thousand, the remainder supplied by the modulus operator is equivalent to the last three digits of the original value. This can be repeated to extract the three-digit groups working from right to left.

One problem with this method is that if the original value is negative the modulus values will also be negative. This can be rectified by using the Abs method from the standard Math class. This method converts negative values to their positive equivalents but leaves positive values unchanged.

The range provided by the integer data type ensures that there will never be more than four three-digit groups. We can therefore create an array with four elements to hold the extracted values. Theis array is populated using a simple for loop that executes once for each group.

The separation is achieved by adding the following code after the zero rule:

// Array to hold four three-digit groups
int[] digitGroups = new int[4];

// Ensure a positive number to extract from
int positive = Math.Abs(number);

// Extract the three-digit groups
for (int i = 0; i < 4; i++)
{
   digitGroups[i] = positive % 1000;
   positive /= 1000;
}

NB: The three-digit group array index numbers can be matched against the large number array indices. This will be useful for recombination later.

Converting a Three-Digit Group

The next rules that need to be applied are those concerning the individual three-digit groups. Each group in turn needs to be converted to text. To achieve this with efficient code, a private method is to be created. This method, named ThreeDigitGroupToWords, accepts an integer value of no more than three digits and returns the equivalent English text.

The new subroutine is called once for each of the three-digit groups using another for loop. For each pass, the returned text is stored in a new array of strings. The index numbers of the three-digit group values and their new text versions are kept synchronised.

Add the following code after the digit extraction loop to call the new private method:

// Convert each three-digit group to words
string[] groupText = new string[4];

for (int i = 0; i < 4; i++)
   groupText[i] = ThreeDigitGroupToWords(digitGroups[i]);

To declare the new method, add the following declaration to the class:

// Converts a three-digit group into English words
private string ThreeDigitGroupToWords(int threeDigits)
{

}

Applying The Hundreds Rules

In order to apply the hundreds rules, there are two key values that must be known. The first value is the number of hundreds that exist in the three-digit group. This can be determines by simply dividing the three-digit value by one hundred. The second number that is required is the remainder of this division. This value, extracted using the modulus operator, represents the other two digits in the group.

The first of the hundreds rules determines if the number of hundreds is included in the returned text. A simple if statement checks if this value is non-zero and adds text accordingly. Otherwise, the digit is converted to text, using the appropriate string from the small numbers array, and the word 'Hundred' is appended.

A second conditional statement determines if there are any tens or units within the three-digit group. If there are, these will be applied by the tens rules later. However, the word 'and' will be required between the hundreds and the tens and units and this is added immediately. Where the three-digit group represents an exact number of hundreds only, the remainder is zero and the 'and' is note required.

This code is added within the ThreeDigitGroupToWords method to apply the hundreds rules:

// Initialise the return text
string groupText = "";

// Determine the hundreds and the remainder
int hundreds = threeDigits / 100;
int tensUnits = threeDigits % 100;

// Hundreds rules
if (hundreds != 0)
{
   groupText += _smallNumbers[hundreds] + " Hundred";

   if (tensUnits != 0)
       groupText += " and ";
}

Applying The Tens Rules

To apply the tens rules we must first determine the number of tens and units. This is achieved in a similar manner to determining the hundreds in the previous rules; the remainder from the previous rule is split using division and modulus operators. Once the number of tens and units are known, several nested if statements are used to add the correct text.

This code should be added after the hundreds rules to apply the tens rules and return the result:

// Determine the tens and units
int tens = tensUnits / 10;
int units = tensUnits % 10;

// Tens rules
if (tens >= 2)
{
   groupText += _tens[tens];
   if (units != 0)
       groupText += " " + _smallNumbers[units];
}
else if (tensUnits != 0)
   groupText += _smallNumbers[tensUnits];

return groupText;

Recombining the Three-Digit Groups

Now that the three-digit groups have all individually been converted into words, they must be recombined. This is achieved by starting with the lower value groups and adding each larger group to the combined string in turn. Where a group is not blank, the correct large number text from the large numbers array is appended. If a non-blank group has already been added, a comma or the word 'and' is also appended.

To control the decision of whether to add 'and' to the combined string, a Boolean variable is initialised after adding the first (smallest) three-digit group. If this group represents a number between one and ninety nine, the Boolean value is set to true, otherwise is it set to false. Whenever another group of digits is added that is non-zero the Boolean is checked. If true, the word 'and' is added. If the Boolean is false, a comma is appended instead.

To implement the recombination rules add the following code after the for loop in the public NumberToWords method:

// Recombine the three-digit groups
string combined = groupText[0];
bool appendAnd;

// Determine whether an 'and' is needed
appendAnd = (digitGroups[0] > 0) && (digitGroups[0] < 100);

// Process the remaining groups in turn, smallest to largest
for (int i = 1; i < 4; i++)
{
   // Only add non-zero items
   if (digitGroups[i] != 0)
   {
       // Build the string to add as a prefix
       string prefix = groupText[i] + " " + _scaleNumbers[i];
      
       if (combined.Length != 0)
           prefix += appendAnd ? " and " : ", ";
      
       // Opportunity to add 'and' is ended
       appendAnd = false;

       // Add the three-digit group to the combined string
       combined = prefix + combined;
   }
}

Applying the Negative Rule

The method is now almost complete. The remaining process is to apply the negative rule. To achieve this, the original value is rechecked to determine if it is positive or negative. If negative, the text is prefixed accordingly. The text is then returned from the method using a standard return statement.

Add the final lines of code to the NumberToWords method:

// Negative rule
if (number < 0)
   combined = "Negative " + combined;

return combined;

2008-01-14T10:31:00.001-08:00

This is a basic idea, how to create data utility class in .Net. Generally it is a good practice to use a data utility class in any application.I hope it will provide some idea to you all beginners....

Happy programming ...............
----------------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Mail;

namespace DataUtility
{
///

/// Summary description for DataUtility.
///
public class DataUtility
{

#region Class Level Variable
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
public SqlDataReader dr;
#endregion

#region Openconnection Function
private void OpenConnection()
{
if(con==null)
{
//con=new SqlConnection("workstation id='PC-13';packet size=4096;user id=sa;integrated security=SSPI;data source=ABC;persist security info=False;initial catalog=yellowpages");
con=new SqlConnection("server=.;uid=sa;database=yellowpages");
}

if(con.State==ConnectionState.Closed)
{
con.Open();
}
cmd=new SqlCommand();
cmd.Connection=con;

}
#endregion

#region CloseConnection Function
private void CloseConnection()
{
if(con.State==ConnectionState.Open)
{
con.Close();
}
}
#endregion

#region DisposeConnection Function

private void DisposeConnection()
{
if(con!=null)
{
con.Dispose();
con=null;
}
}
#endregion

#region ExecuteSql Method

public int ExecuteSql(string strsql)
{
OpenConnection();
cmd.CommandType=CommandType.Text;
cmd.CommandText=strsql;
int intresult;
intresult=cmd.ExecuteNonQuery();
CloseConnection();
DisposeConnection();
return intresult;
}
#endregion

#region GetDataSet Method

public DataSet GetDataSet(string strsql)
{
OpenConnection();
da=new SqlDataAdapter(strsql,con);
DataSet ds=new DataSet();
da.Fill(ds);
da.Dispose();
CloseConnection();
DisposeConnection();
return ds;
}
#endregion

#region GetDataTable Method

public DataTable GetDataTable(string strsql)
{
OpenConnection();
da=new SqlDataAdapter(strsql,con);
DataTable dt=new DataTable();
da.Fill(dt);
da.Dispose();
CloseConnection();
DisposeConnection();
return dt;
}
#endregion

#region GetDataReader Method
public SqlDataReader GetDataReader(string strsql)
{
OpenConnection();
cmd.CommandType=CommandType.Text;
cmd.CommandText=strsql;

dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
#endregion

#region IsExist Method

public Boolean IsExist(string strsql)
{
OpenConnection();
cmd.CommandType=CommandType.Text;
cmd.CommandText=strsql;
int intresult;
intresult=(int)cmd.ExecuteScalar();
CloseConnection();
DisposeConnection();

if(intresult>0)
{
return true;
}
else
{
return false;

}

}
#endregion

#region createmember
public void createmember(string mg)
{

MailMessage msg=new MailMessage();
msg.To="admin@testme.in";
msg.Subject="Login Created";
msg.Body=mg;
msg.BodyFormat=MailFormat.Html;
msg.From="127.0.0.1";
SmtpMail.SmtpServer="127.0.0.1";
SmtpMail.Send(msg);

}

#endregion
# region Execute Procedure

public void ExecuteProcedure(String procname,SqlParameter [] sqlparam)

{
OpenConnection();
cmd.CommandType =CommandType.StoredProcedure ;
cmd.CommandText =procname;
cmd.Parameters.Clear();
foreach(SqlParameter thisparam in sqlparam )
{
cmd.Parameters.Add ((SqlParameter ) thisparam);
}
cmd.ExecuteNonQuery();
CloseConnection();

}

#endregion

#region Default Constructor
public DataUtility()
{
//
// TODO: Add constructor logic here
//
}
#endregion

}
}