public static List<Customer> GetCustomerListWithID()
{
string selectStatement = "SELECT Customer.PK, Customer.Name, Customer.Details, Customer.VersionPK, Version.ID, Customer.plcAddress, Customer.Contacts, Customer.LogonDetail FROM Customer " +
"LEFT JOIN Version ON Customer.VersionPK=Version.PK WHERE Customer.isDeleted=0 ORDER BY Customer.Name ASC";
SqlConnection connection = Database.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
List<Customer> customerList = new List<Customer>();
try
{
connection.Open();
var reader = selectCommand.ExecuteReader();
while (reader.Read())
{
int pk = reader.GetInt32(0);
String name = reader.GetString(1);
String details = SafeGetString(reader, 2);
int? versionPK;
string versionID;
if (reader.GetValue(3) == DBNull.Value || reader.GetValue(3) == null)
{
versionPK = null;
versionID = "";
}
else
{
versionPK = reader.GetInt32(3);
versionID = reader.GetString(4);
}
String plc = SafeGetString(reader, 5);
String contacts = SafeGetString(reader, 6);
String logon = SafeGetString(reader, 7);
Customer cust = new Customer(pk, name, details, plc, contacts, logon, versionPK, versionID, false);
customerList.Add(cust);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return customerList;
}
public static List<Customer> GetCustomersWithVersion(int vpk)
{
string selectStatement = "SELECT * FROM Customer WHERE VersionPK = @pk AND isDeleted =0";
SqlConnection connection = Database.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@pk", vpk);
List<Customer> customerList = new List<Customer>();
try
{
connection.Open();
var reader = selectCommand.ExecuteReader();
while (reader.Read())
{
int pk = reader.GetInt32(0);
String name = reader.GetString(1);
String details = SafeGetString(reader, 2);
int versionPK = SafeGetInt(reader, 3);
String plc = SafeGetString(reader, 5);
String contacts = SafeGetString(reader, 6);
String logon = SafeGetString(reader, 7);
Customer cust = new Customer(pk, name, details, plc, contacts, logon, versionPK, "", false);
customerList.Add(cust);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return customerList;
}
public static List<Customer> GetCustomerList()
{
string selectStatement = "SELECT * FROM Customer " +
"WHERE isDeleted=0 ORDER BY Name ASC";
SqlConnection connection = Database.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
List<Customer> customerList = new List<Customer>();
try
{
connection.Open();
var reader = selectCommand.ExecuteReader();
while (reader.Read())
{
int pk = reader.GetInt32(0);
String name = reader.GetString(1);
String details = SafeGetString(reader, 2);
int? versionPK;
if (reader.GetValue(3) == DBNull.Value)
{
versionPK = null;
}
else
{
versionPK = reader.GetInt32(3);
}
String plc = SafeGetString(reader, 5);
String contacts = SafeGetString(reader, 6);
String logon = SafeGetString(reader, 7);
Customer cust = new Customer(pk, name, details, plc, contacts,logon, versionPK, false);
customerList.Add(cust);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return customerList;
}