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.SqlClient;
public class Customer : ICloneable
{
#region Variables
string strSql = string.Empty;
string strConn = string.Empty;
DataTable dt = null;
SqlConnection customerConnection = null;
SqlDataAdapter customerdata = null;
SqlCommand customercommand = null;
private string _CustomerID;
private string _CompnayName;
private string _ContactName;
private string _ContactTitle;
private string _Address;
private string _City;
private string _Region;
private string _PostalCode;
private string _Country;
private string _Phone;
private string _Fax;
#endregion
#region Constructor
public Customer()
{
}
public Customer( string customerid, string companyname, string contactname, string contacttilte, string address, string city, string region,
string country, string postalcode, string phone, string fax )
{
this.CustomerID = customerid;
this.CompnayName = companyname;
this.ContactTitle = contacttilte;
this.ContactName = contactname;
this.Address = address;
this.City = city;
this.Region = region;
this.PostalCode = postalcode;
this.Country = country;
this.Phone = phone;
this.Fax = fax;
}
#endregion
#region Property
public string CustomerID
{
get
{
return _CustomerID;
}
set
{
_CustomerID = value;
}
}
public string CompnayName
{
get
{
return _CompnayName;
}
set
{
_CompnayName = value;
}
}
public string ContactName
{
get
{
return _ContactName;
}
set
{
_ContactName = value;
}
}
public string ContactTitle
{
get
{
return _ContactTitle;
}
set
{
_ContactTitle = value;
}
}
public string Address
{
get
{
return _Address;
}
set
{
_Address = value;
}
}
public string City
{
get
{
return _City;
}
set
{
_City = value;
}
}
public string Region
{
get
{
return _Region;
}
set
{
_Region = value;
}
}
public string PostalCode
{
get
{
return _PostalCode;
}
set
{
_PostalCode = value;
}
}
public string Country
{
get
{
return _Country;
}
set
{
_Country = value;
}
}
public string Phone
{
get
{
return _Phone;
}
set
{
_Phone = value;
}
}
public string Fax
{
get
{
return _Fax;
}
set
{
_Fax = value;
}
}
#endregion
public Object Clone()
{
Customer customer = new Customer();
customer.CustomerID = this.CustomerID;
customer.CompnayName = this.CompnayName;
customer.ContactName = this.ContactName;
customer.ContactTitle = this.ContactTitle;
customer.Country = this.Country;
customer.City = this.City;
customer.Address = this.Address;
customer.Fax = this.Fax;
customer.Phone = this.Phone;
customer.PostalCode = this.PostalCode;
customer.Region = this.Region;
return customer;
}
}
//////////////Class\\\\\\\\\\\\\
this is my comparere class
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.Collections;
using System.Collections.Generic;
using System.Reflection;
/// <summary>
/// Customer is used to sort the generic collection of the People class
/// </summary>
public class CustomerComparer: IComparer<Customer>
{
#region Constructor
public CustomerComparer(string p_propertyName)
{
//We must have a property name for this comparer to work
this.PropertyName = p_propertyName;
}
#endregion
#region Property
private string _propertyName;
public string PropertyName
{
get { return _propertyName; }
set { _propertyName = value; }
}
#endregion
#region IComparer<Customer> Members
/// <summary>
/// This comparer is used to sort the generic comparer
/// The constructor sets the PropertyName that is used
/// by reflection to access that property in the object to 
/// object compare.
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
public int Compare(Customer x, Customer y)
{
Type t = x.GetType();
PropertyInfo val = t.GetProperty(this.PropertyName);
if (val != null)
{
return Comparer.DefaultInvariant.Compare(val.GetValue(x,null), val.GetValue(y,null));
}
else
{
throw new Exception(this.PropertyName + " is not a valid property to sort on.  It doesn't exist in the Class.");
}
}
#endregion
}
THis is My DataAccess Class
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;
[DataObject]
public class CustomerDAL
{
#region Variables
SqlConnection customerConnection = null;
SqlCommand customerCommand = null;
SqlDataReader customerReader = null;
string commandString = string.Empty;
#endregion
#region Select and Count
protected int _count = -1;
[DataObjectMethod(DataObjectMethodType.Select)]
public List<Customer> LoadAll(string p_sortExpression, string p_sortDirection)
{
List<Customer> customer = new List<Customer>();
customerConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
commandString = "Select_Customer";
customerCommand = new SqlCommand(commandString, customerConnection);
try
{
customerConnection.Open();
customerReader = customerCommand.ExecuteReader();
while (customerReader.Read())
{
Customer cus = new Customer();
cus.CustomerID = (string)customerReader["CustomerID"];
cus.CompnayName = (string)customerReader["CompanyName"];
cus.ContactName = (string) customerReader["ContactName"];
cus.ContactTitle  = (string)customerReader["ContactTitle"];
cus.Country = (string)customerReader["Country"];
cus.Fax = (string)customerReader["Fax"];
cus.Phone = (string)customerReader["Phone"];
cus.PostalCode = (string)customerReader["PostalCode"];
cus.Region = (string)customerReader["Region"];
cus.Address = (string)customerReader["Address"];
cus.City = (string)customerReader["City"];
customer.Add(cus);
}
_count = customer.Count;
customerReader.Close();
//We sort the generic list if requested too
if (p_sortExpression != null && p_sortExpression != string.Empty)
{
customer.Sort(new CustomerComparer(p_sortExpression));
}
//Now that we have sorted check to see if the sort direction is desc
if (p_sortDirection != null && p_sortDirection == "Desc")
{
customer.Reverse();
}
return customer;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
customerConnection.Close();
}
}
#endregion
#region Select Customer By ID
public Customer LoadByID(Customer  customer)
{
customerConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
commandString = "Select_Customer_ByID";
customerCommand = new SqlCommand(commandString, customerConnection);
customerCommand.CommandType = CommandType.StoredProcedure;
customerCommand.Parameters.Add( "@CustomerID", SqlDbType.VarChar ).Value = customer.CustomerID;
//customerCommand.Parameters.Add( new SqlParameter( "@CustomerID", SqlDbType.VarChar ) ).Value = customer.CustomerID;
//sc.Parameters.Add(new SqlParameter("@empId",SqlDbType.Int)).Value = Int32.Parse(anID.ToString());
try
{
customerConnection.Open();
customerReader = customerCommand.ExecuteReader();
Customer cus = new Customer();
while (customerReader.Read())
{
cus.CustomerID = (string)customerReader["CustomerID"];
cus.CompnayName = (string)customerReader["CompanyName"];
cus.ContactTitle  = (string)customerReader["ContactTitle"];
cus.ContactName = (string) customerReader["ContactName"];
cus.Country = (string)customerReader["Country"];
cus.Fax = (string)customerReader["Fax"];
cus.Phone = (string)customerReader["Phone"];
cus.PostalCode = (string)customerReader["PostalCode"];
cus.Region = (string)customerReader["Region"];
cus.Address = (string)customerReader["Address"];
cus.City = (string)customerReader["City"];
}
customerReader.Close();
return cus;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
customerConnection.Close();
}
}
#endregion
}
///////////Stored Procedure\\\\\\\\\\\\
--Select_Customer
CREATE proc dbo.Select_Customer
AS
Select
isnull(CustomerID,'N/A') as CustomerID,
isnull(CompanyName,'N/A') as CompanyName,
isnull(ContactName,'N/A') as ContactName,
isnull(ContactTitle,'N/A') as ContactTitle,
isnull(Address,'N/A') as Address,
isnull(City,'N/A') as City,
isnull(Region,'N/A') as Region,
isnull(PostalCode,'N/A') as PostalCode,
isnull(Country,'N/A') as Country,
isnull(Phone,'N/A') as Phone,
isnull(Fax,'N/A') As Fax
From
Customers
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Select_Customer_ByID ANTON
CREATE proc dbo.Select_Customer_ByID
@CustomerID varchar(200)
AS
Select
isnull(CustomerID,  'N/A') as CustomerID,
isnull(CompanyName,  'N/A') as CompanyName,
isnull(ContactName,  'N/A') as ContactName,
isnull(ContactTitle,  'N/A') as ContactTitle,
isnull(Address,  'N/A') as Address,
isnull(City,  'N/A') as City,
isnull(Region,  'N/A') as Region,
isnull(PostalCode,  'N/A') as PostalCode,
isnull(Country,'N/A') as   Country,
isnull(Phone,'N/A') as  Phone,
isnull(Fax,'N/A') as  Fax
From
Customers
Where
CustomerID=@CustomerID