/// <summary>
/// Gets the characteristics for the client. if it is a new client all are unchecked and and for old the same checks are in place
/// </summary>
private void GetChar()
{
clbChar.Items.Clear();
string GetCharSQL = "SELECT * FROM CHARACTERISTIC_TABLE;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetCharSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())// this will read through all the info in the char table and add them to the checked list box
{
AChar tempchar = new AChar();
if (reader["CHAR_NAME"] != DBNull.Value)
{
clbChar.Items.Add(reader["CHAR_NAME"] as string);
tempchar.CharName = reader["CHAR_NAME"] as string;
tempchar.CharID = (reader["CHAR_ID"] as int?) ?? 0;
charList.Add(tempchar);
}
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve characteristics from Database.! \n Error reads: " + ex.Message);
}
//changes the string to go into the char table and CTC table. this will allow us to read in and check the items in the checked list box
GetCharSQL = "SELECT CHARACTERISTIC_TABLE.CHAR_NAME, CTC_TABLE.CTC_ID, CTC_TABLE.CLIENT_ID " +
"FROM CHARACTERISTIC_TABLE LEFT OUTER JOIN CTC_TABLE ON CHARACTERISTIC_TABLE.CHAR_ID = CTC_TABLE.CHAR_ID";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetCharSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
int i = -1; //this is used to keep track of what index in the clb we are working on
string storedName = "nothing";
while (reader.Read()) // this will read through all the info in the char table and then it will check the box if it is
{
if (reader["CHAR_NAME"] != DBNull.Value && reader["CHAR_NAME"] as string != storedName) //if not a repeat
{
i++;
storedName = reader["CHAR_NAME"] as string;
}
foreach (AChar aChar in charList)
{
if (clbChar.Items[i].ToString() == aChar.CharName) //these are the ones selected that came in with the inport
{
if (reader["CLIENT_ID"].ToString() == clientID.ToString()) //marked as being incoming to save
{
charList[i].CharClient = clientID; //puts in the client id for the sorting and checking process
charList[i].CharMini = (reader["CTC_ID"] as int?) ?? 0; //arbitrary but it marks it for recycling first
clbChar.SetItemChecked(i, true); //this will check the ones that have the relation correct.
}
}
}
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve characteristics from Database.! \n Error reads: " + ex.Message);
}
}
private void GetServChar() //this makes a list of the char and serv at the begining of the
{
clbLabels.Items.Clear();
string GetCharSQL = "select * from SERVICE_TABLE st inner join CHARACTERISTIC_TABLE ct on " +
"st.SERV_ID = ct.SERV_ID order by st.SERV_NAME, ct.CHAR_NAME";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetCharSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
int i = 0;
while (reader.Read())//reads in all the data assotiated with the
{
AChar tempChar = new AChar();
if (reader["CHAR_ID"] != DBNull.Value)
{
tempChar.CharID = (reader["CHAR_ID"] as int?) ?? 0;
}
if (reader["CHAR_NAME"] != DBNull.Value)
{
tempChar.CharName = (reader["CHAR_NAME"] as string);
}
if (reader["CHAR_MIN"] != DBNull.Value)
{
tempChar.CharMini = (reader["CHAR_MIN"] as int?) ?? 0;
}
if (reader["CHAR_COST"] != DBNull.Value)
{
tempChar.CharCost = (reader["CHAR_COST"] as decimal?) ?? 0.00m;
}
if (reader["SERV_ID"] != DBNull.Value)
{
tempChar.CharAsso = (reader["SERV_ID"] as int?) ?? 0;
}
if (reader["CHAR_ACTIVE"] != DBNull.Value)
{
if (!reader.GetBoolean(reader.GetOrdinal("CHAR_ACTIVE")))
{
tempChar = null; //if not active then it will delete the temp so it is not added
}
}
if (tempChar != null)
{
characteristicList.Add(tempChar);
}
i++;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve characteristics from Database.! \n Error reads: " + ex.Message);
}
//fill the checked list box
foreach (AChar charactor in characteristicList)
{
clbLabels.Items.Add(charactor.CharName);
}
}
