private void SetButchersLabels() { var sqlConn = new SqlConn(DbConnectionString()); try { if (sqlConn.TestConnection()) { var sqlDatatable = new SqlDataTable(); var dt = new DataTable(); var sqlQuery = string.Empty; sqlQuery = "SELECT * FROM tblCustomer"; dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); SetControlsLookUpEdit(lookUpEdit_Customer, dt, "CustomerName", "IdCustomer", "Customer"); sqlQuery = "SELECT * FROM tblShift"; dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); SetControlsLookUpEdit(lookUpEdit_Shift, dt, "Shift", "IdShift", "Shift"); lookUpEdit_Product.Enabled = false; } else { throw new Exception("Unexpected error when tried to connect to server and download data."); } } catch (Exception Ex) { var message = string.Format(Ex.Message); var title = "Database connection"; XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void SetControlsLookUpEditProductList() { var sqlConn = new SqlConn(DbConnectionString()); var sett = Properties.Settings.Default; int factoryId = sett.Factory; string customerId = lookUpEdit_Customer.EditValue.ToString(); string sqlQuery = "SELECT * FROM v_ButcherLabelsProductList WHERE IdCustomer=" + customerId + " AND IdFactory=" + factoryId + ""; var dt = new DataTable(); dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); lookUpEdit_Product.Enabled = true; lblColorLabel.Text = string.Empty; var control = lookUpEdit_Product.Properties; control.DataSource = null; control.DataSource = dt; control.DisplayMember = "Description"; control.ValueMember = "ProdCode"; control.AppearanceDropDown.FontSizeDelta = 10; control.AppearanceDropDownHeader.FontSizeDelta = 10; LookUpColumnInfoCollection columns = control.Columns; columns.Clear(); columns.Add(new LookUpColumnInfo("ProdCode", 150, "Code")); columns.Add(new LookUpColumnInfo("Description", 400, "Description")); columns.Add(new LookUpColumnInfo("CustomerName", 120, "Customer")); columns.Add(new LookUpColumnInfo("LabelType", 100, "Label")); }
private void GetDataFromSI(string barCode) { var sqlConn = new SqlConn(SIConnectionString()); DataTable dt = new DataTable(); try { if (sqlConn.TestConnection()) { var sqlQuery = string.Empty; string batchPallet = string.Empty; switch (barCode.Length) { case 14: batchPallet = barCode.Substring(2); _batchOrPallet = SqlQueryBatchPallet.PalletBatchField.palletid; sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet); break; case 22: batchPallet = barCode.Substring(0, 12); _batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno; sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet); break; default: batchPallet = barCode; _batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno; sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet); break; } if (_butcherLabelsTable != null) { dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); _butcherLabelsTable.Merge(dt); } else { dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); _butcherLabelsTable = dt; } AddDataToGridView(dt); } else { throw new Exception("Unexpected error when tried to connect to SI database and download data."); } } catch (Exception ex) { var message = string.Format(ex.Message); var title = "SI database connection"; XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void XmlDataForReport() { string sqlQuery = "SELECT ProductionDate, ProdDescription, RawMaterialDescription, Customer, Shift, LabelDescription as Color, Lot, PalletId, Udf2, Udf3, Udf4, KillDate, LabelBatchNumber AS Batch FROM tblButcherLabelsData WHERE ProductionDate = '09/05/2017' AND Customer='Lidl' AND Shift='Dayshift' AND ProdCode='LD5204354' AND FactoryId='1'"; DataTable dt = new DataTable(); var sqlConn = new SqlConn(DbConnectionString()); dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); dt.TableName = "tblPalletBatch"; dt.WriteXmlSchema(@"C:\Users\krzysztof.matyja\Dropbox\Employee365 Projects\Traceability\PalletBatch.xsd"); }
private void GetDataForGridViewBatch() { try { string sqlQuery = string.Empty; DataTable dt = new DataTable("Batch"); var sqlConn = new SqlConn(DbConnectionString()); sqlQuery = "SELECT RawMaterialCode AS Code, RawMaterialDescription AS Description, KillDate, Weight, LabelBatchNumber AS Batch " + "FROM tblButcherLabelsData " + "WHERE ProductionDate = '" + string.Format("{0:MM/dd/yyyy}", dateEdit_ProdDate.EditValue) + "' AND " + "Customer='" + lookUpEdit_Customer.Text + "' AND Shift='" + lookUpEdit_Shift.Text + "' AND " + "ProdCode='" + lookUpEdit_Product.GetColumnValue("ProdCode").ToString() + "' AND " + "FactoryId='" + Properties.Settings.Default.Factory + "'"; dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery); gridControl_Batch.DataSource = null; gridControl_Batch.DataSource = dt; } catch (Exception ex) { XtraMessageBox.Show(ex.Message, "Download data from database", MessageBoxButtons.OK, MessageBoxIcon.Error); } }