Create connection file and fill data table with data:-----------
file name :
DataAccess.cs
======================
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace DataAccessNamespace
{
/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
//Variables & public properties ***********************************
private string connectionString = "";
private int recordCount = -1;
/// <summary>
/// Property: gets count of records retrieved or changed
/// </summary>
public int Count
{
get
{
return recordCount;
}
}
//Class constructor is executed when object is initialized ***********
/// <summary>
/// Connection string name in web.config file is required to initialize DataAccess
/// </summary>
/// <param name="ConnectionName">Name of web.config connection string</param>
public DataAccess()
{
//if (WebConfigurationManager.ConnectionStrings[ConnectionName] == null)
//{
// throw new Exception("Cannot find connection string named '" +
// ConnectionName + "' in web.config");
//}
//Get connection string from web.config.
//connectionString = WebConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;
connectionString = @"Data Source=USF142\SQLEXPRESS;Initial Catalog=demo;Integrated Security=True;";
}
/// <summary>
/// Executes SELECT statement and returns results in dataTable
/// </summary>
/// <param name="SQL">Select SQL statement</param>
/// <returns></returns>
public DataTable FillDataTable(string SQL)
{
SqlConnection _objConn = new SqlConnection(connectionString);
SqlDataAdapter objAdapter = new SqlDataAdapter(SQL, _objConn);
DataTable dt = new DataTable();
try
{
objAdapter.Fill(dt);
}
catch (SqlException ex)
{
throw new Exception("Error in SQL:" + SQL, ex);
}
catch (Exception ex)
{
throw ex; //Bubbling exception up to parent class
}
finally
{
_objConn.Close();
}
recordCount = dt.Rows.Count;
return dt;
}
/// <summary>
/// Executes "non-query" SQL statements (insert, update, delete)
/// </summary>
/// <param name="SQL">insert, update or delete</param>
/// <returns>Number of records affected</returns>
public int ExecuteNonQuery(string SQL)
{
SqlConnection _objConn = new SqlConnection(connectionString);
try
{
_objConn.Open();
SqlCommand objCmd = new SqlCommand(SQL, _objConn);
recordCount = objCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Error in SQL:" + SQL, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message); //Rethrowing exception up to parent class
}
finally { _objConn.Close(); }
return recordCount;
}
public int ExecuteScalar(String SQL)
{
SqlConnection _objConn = new SqlConnection(connectionString);
int intID;
try
{
_objConn.Open();
SqlCommand objCmd = new SqlCommand(SQL, _objConn);
intID = Convert.ToInt32(objCmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Error in SQL:" + SQL, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message); //Rethrowing exception up to parent class
}
finally { _objConn.Close(); }
return intID;
}
}
}
==============================
create Default2.aspx+cs file
aspx.cs file
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using DataAccessNamespace;
public partial class demo_Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataAccess myDA = new DataAccess();
//Populate dataTable and bind to GridView Control
string strSQL = "Select * from employee";
GridView1.DataSource = myDA.FillDataTable(strSQL);
GridView1.DataBind();
}
}
.aspx file:-----------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="demo_Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" >
</asp:GridView>
</div>
</form>
</body>
</html>
