/// <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); } }