Hello
I have a class with an sql statement to return a list of customers from a postcode search from a database.
Then on my form I am attempting to return a sorted list of these customers into a list box for selection.
Could someone please tell me how to use sorted lists:-/
Thank you ... John.
This is my class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
namespace TheatreTicketBooking
{
class Customer
{
private string m_CustomerNumber;
private string m_CustomerName;
private string m_Address1;
private string m_Address2;
private string m_Town;
private string m_County;
private string m_PostCode;
private DateTime m_CreationDate;
private string m_FriendMarker;
// Declare an ScheduledEventID property of type string:
public string CustomerNumber
{
get
{
return m_CustomerNumber;
}
set
{
m_CustomerNumber = value;
}
}
// Declare a CustomerName property of type string:
public string CustomerName
{
get
{
return m_CustomerName;
}
set
{
m_CustomerName = value;
}
}
// Declare an Address1 property of type string:
public string Address1
{
get
{
return m_Address1;
}
set
{
m_Address1 = value;
}
}
// Declare an Address2 property of type string:
public string Address2
{
get
{
return m_Address2;
}
set
{
m_Address2 = value;
}
}
// Declare a Town property of type string:
public string Town
{
get
{
return m_Town;
}
set
{
m_Town = value;
}
}
// Declare a County property of type string:
public string County
{
get
{
return m_County;
}
set
{
m_County = value;
}
}
// Declare a PostCode property of type string:
public string PostCode
{
get
{
return m_PostCode;
}
set
{
m_PostCode = value;
}
}
// Declare a CreationEvent property of type DateTime:
public DateTime CreationEvent
{
get
{
return m_CreationDate;
}
set
{
m_CreationDate = value;
}
}
// Declare a FriendMarker property of type string:
public string FriendMarker
{
get
{
return m_FriendMarker;
}
set
{
m_FriendMarker = value;
}
}
//For a given Postcode, retrieve a Customer object
public void GetCustomersByCustomerNumber(string customerNumber)//being passed this ID (intCutomer No oncustomers
{
string TBConnectionString = ConfigurationManager.ConnectionStrings["TicketBookingConnectionString"].ConnectionString;
SqlConnection cnTB = new SqlConnection(TBConnectionString);
cnTB.Open();
SqlCommand cmCust = new SqlCommand();
cmCust.Connection = cnTB;
cmCust.CommandType = CommandType.Text;
cmCust.CommandText = "Select * from Customers where CustomerNumber " + customerNumber;//star - read it all from customers table Post Code field (variablePCode)
SqlDataReader drCustomer = cmCust.ExecuteReader();
drCustomer.Read();// go and read the data
//the variables
m_CustomerNumber = drCustomer[0].ToString();
m_CustomerName = drCustomer[1].ToString();
m_Address1 = drCustomer[2].ToString();
m_Address2 = drCustomer[3].ToString();
m_Town = drCustomer[4].ToString();
m_County = drCustomer[5].ToString();
m_PostCode = drCustomer[6].ToString();
m_CreationDate = (DateTime)drCustomer[7];
m_FriendMarker = drCustomer[8].ToString();
drCustomer.Close();
cnTB.Close();//txtCustomerNumber
}
//For a given CustomerNumber, retrieve a Customer object
public SortedList ListCustomerByPostcode(string PostCode)
{
string TBConnectionString = ConfigurationManager.ConnectionStrings["TicketBookingConnectionString"].ConnectionString;
SqlConnection cnTB = new SqlConnection(TBConnectionString);
cnTB.Open();
SqlCommand cmCustList = new SqlCommand();
cmCustList.Connection = cnTB;
cmCustList.CommandType = CommandType.Text;
cmCustList.CommandText = "Select CustomerNumber, CustomerName, Address1, Address2, Town, County, PostCode, CreationDate, FriendMarker from Cutomers where PostCode Like '" + PostCode + "%'";
//cmCustList.CommandText = "Select CustomerName from Cutomers WHERE PostCode = '" + PostCode + "%'";
SqlDataReader drCustList = cmCustList.ExecuteReader();
SortedList CustList = new SortedList();//creating a new sorted list
while (drCustList.Read())// while it reads the data rows one at a time
{
CustList.Add(drCustList[0], drCustList[1] + " , " + drCustList[2] + " , " + drCustList[3] + " , " + drCustList[4] + " , " + drCustList[5] + " , " + drCustList[6] + " , " + drCustList[7].ToString() + " , " + drCustList[8]);// list the fields
}
drCustList.Close();
cnTB.Close();
return CustList;
}
}
}And this is my form code with my useless attempt at trying to use a sorted list!
private void btnListCustomerByPostcode_Click(object sender, EventArgs e)
{
try
{
Customer cust = new Customer();
SortedList custList = cust.ListCustomerByPostcode(ECenterPostcodeTextBox.Text);
lstCustomerSelect.Items.Add(custList);
}
catch
{
MessageBox.Show("Please Enter a Postcode");
}
}
