Hi Team
I need a bit of a help, i need on my store proc options, if example. i have batchnumber 1220, matches with the existing columns correcttly, so that if this batchnumber keeps updates to 1221 it continues to matches with that column, in this instance DateCapture. I have below a store procedure that does but i do bear in mind. Somehow i need to handle this correctly.
The window application is wpf and uses store procedure, so when a user opens pop screen the batch number by business rule. it must update to the next batch number. This part works fine, but now im thinking if other columns such as DateCapture doe not match it will leave a user wonder this data is incorrect and not consistent.
// store proc
USE [Adroit_Batch_Suite] GO /****** Object: StoredProcedure [dbo].[spGetPremixSheet] Script Date: 2024/07/25 09:53:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spGetPremixSheet] @procOption NVARCHAR(50), @Batch INT = NULL, @DateCaptured DATE = NULL, @Code NVARCHAR(50) = NULL, @Phase NVARCHAR(100) = NULL, @Quantity DECIMAL(18, 2) = NULL, @Comments NVARCHAR(MAX) = NULL, @PrintedBarCodes INT, @UnusedBarcodes INT, @UsedBarCodes INT, @PrintedVar INT, @UserVar INT AS BEGIN SET NOCOUNT ON; IF @procOption = 'All' BEGIN SELECT Batch, DateCaptured, Code, Phase, Quantity, Comments, PrintedBarCodes, UnusedBarcodes, UsedBarCodes,PrintedVar, UserVar FROM [dbo].[Adr_Batch_PremixControl] END /** Primary key **/ ELSE IF @procOption = 'PK' BEGIN SELECT Batch, DateCaptured, Code, Phase, Quantity, Comments, PrintedBarCodes, UnusedBarcodes, UsedBarCodes,PrintedVar, UserVar FROM [dbo].[Adr_Batch_PremixControl] WHERE Batch = @Batch; END /*** Fetch the Code from the table **/ ELSE IF @procOption = 'Code' BEGIN SELECT TOP 1 Code FROM [dbo].[Adr_Batch_PremixControl] ORDER BY Code DESC; END /** Date matches the batch number**/ ELSE IF @procOption = 'Date' BEGIN SELECT TOP 1 DateCaptured FROM [dbo].[Adr_Batch_PremixControl] ORDER BY DateCaptured DESC; END /**Batch number from the table***/ ELSE IF @procOption = 'LatestBatchNumber' BEGIN SELECT TOP 1 Batch FROM [dbo].[Adr_Batch_PremixControl] ORDER BY Batch DESC; END /***Quantity column from the table***/ ELSE IF @procOption = 'Quantity' BEGIN SELECT TOP 1 Quantity FROM [dbo].[Adr_Batch_PremixControl] ORDER BY Quantity DESC; END /**Phase column from the table**/ ELSE IF @procOption = 'Phase' BEGIN SELECT TOP 10 Phase FROM [dbo].[Adr_Batch_PremixControl] ORDER BY Phase DESC; END END GO
// wpf code flow using batch number as an example
<Window x:Class="Feedmill_Weighbridge.Presentation.frmPremixPostData" xmlns:local="clr-namespace:Feedmill_Weighbridge.Presentation" mc:Ignorable="d" Title="frmPremixPostData" Height="550" Width="800"> <Grid> <Label x:Name="lblBatch" FontWeight="SemiBold" Visibility="Visible" Content="Batch:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,20,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="BatchTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,20,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084" IsReadOnly="True"/> <Label x:Name="lblDate" FontWeight="SemiBold" Visibility="Visible" Content="Date:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,60,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="DateTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,60,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084" IsReadOnly="True"/> <Label x:Name="lblCode" FontWeight="SemiBold" Visibility="Visible" Content="Code:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,100,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="CodeTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,100,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084" IsReadOnly="True"/> <Label x:Name="lblPhase" FontWeight="SemiBold" Visibility="Visible" Content="Phase:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,140,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="PhaseTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,140,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084" IsReadOnly="True"/> <Label x:Name="lblQuantity" FontWeight="SemiBold" Visibility="Visible" Content="Quantity:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,180,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="QuantityTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,180,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084" IsReadOnly="True"/> <Label x:Name="lblComments" FontWeight="SemiBold" Visibility="Visible" Content="Comments/Additional Info:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,220,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="CommentsTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,220,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Label x:Name="lblPrintedBarcodes" FontWeight="SemiBold" Visibility="Visible" Content="Printed Barcodes:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,260,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="PrintedBarcodesTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,260,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Label x:Name="lblUnusedBarcodes" FontWeight="SemiBold" Visibility="Visible" Content="Unused Barcodes:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,300,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="UnusedBarcodesTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,300,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Label x:Name="lblUsedBarcodes" FontWeight="SemiBold" Visibility="Visible" Content="Used Barcodes:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,340,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="UsedBarcodesTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,340,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Label x:Name="lblPrintedVar" FontWeight="SemiBold" Visibility="Visible" Content="Printed Var:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,380,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="PrintedVarTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,380,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Label x:Name="lblUsedVar" FontWeight="SemiBold" Visibility="Visible" Content="Used Var:" HorizontalAlignment="Left" VerticalAlignment="Top" Width="179" HorizontalContentAlignment="Right" Background="#FFE3E3E3" Margin="20,420,0,0" BorderThickness="1" BorderBrush="#FFBEBEBE"/> <TextBox x:Name="UsedVarTextBox" FontSize="10" Foreground="Black" HorizontalAlignment="Left" Visibility="Visible" Height="26" Margin="205,420,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="210" RenderTransformOrigin="1.19,-3.084"/> <Button x:Name="btnSave" Content="Save" Background="Green" Foreground="White" FontWeight="Bold" HorizontalAlignment="Left" VerticalAlignment="Top" Width="100" Height="30" Margin="205,460,0,0" Click="btnSave_Click"/> </Grid> </Window>
// presenter class
public class ManagePremixBatchPresenter { IPremixBatch View; private static int currentBatchNumber = -1; // constructor here // method to get batch number sequentially public int GetNextBatchNumber(int batchNo) { Managers.PremixControlManager.SetAdriotConnection(); try { if (currentBatchNumber == -1) { // Get the latest batch number from the database only if the static field is not initialized var latestBatchControl = Managers.PremixControlManager.GetLatestBatchNumber(batchNo); if (latestBatchControl == null || latestBatchControl.Batch == 0) { throw new InvalidOperationException("Failed to retrieve the latest batch number."); } currentBatchNumber = latestBatchControl.Batch; } // Increment the batch number to get the next one currentBatchNumber++; return currentBatchNumber; } catch (Exception ex) { Console.WriteLine($"Error in GetNextBatchNumber: {ex.Message}"); throw; } finally { Managers.PremixControlManager.SetDefaultConnection(); } } } }
// store procedure class handles entites
namespace Feedmill_Weighbridge.Managers { class PremixControlManager { public static PremixControl GetPremixControlByPK(int batchID) { return DataAccessProvider.GetEntity<PremixControl>( PremixControl.PROC_NAME_GET, new object[] { "PK", batchID, null, null, null, null, null, null, null, null, null, null } ); } public static PremixControl GetLatestBatchNumber(int batchNo) { return DataAccessProvider.GetEntity<PremixControl>(PremixControl.PROC_NAME_GET, new object[] { "LatestBatchNumber", batchNo, null, null, null, null, null, null, null, null, null, null }); } // connection string public static void SetAdriotConnection() { DataAccessProvider.SetConnection("ADROIT"); } public static void SetDefaultConnection() { DataAccessProvider.SetConnection("SOUL"); } }
// main window form
public partial class frmPremixPostData : Window, IPremixBatch { public ManagePremixBatchPresenter presenter; public int batchNo; public DateTime _date; public string _code; public string _phase; public double _quantity; public string Description { get ; set; } public string BatchSize { get ; set; } public string Batch { get ; set; } public ComboBox ComboProdCode { get; set ; } public DataGrid GridMicroMaterial { get ; set ; } public DateTime DateCaptured { get { return _date; } set { _date = value; DateTextBox.Text = value.ToString("yyyy/M/d"); } } public string Code { get { return _code; } set { _code = value; CodeTextBox.Text = value.ToString(); } } public string Phase { get { return _phase; } set { _phase = value; PhaseTextBox.Text = value.ToString(); } } public double Quantity { get { return _quantity; } set { _quantity = value; QuantityTextBox.Text = value.ToString(); } } public frmPremixPostData() { InitializeComponent(); presenter = new ManagePremixBatchPresenter(this); this.DataContext = this; this.Loaded += FrmPremixPostData_Loaded; } //incrementing a batch number sequentially. private void FrmPremixPostData_Loaded(object sender, RoutedEventArgs e) { int batchNumber = presenter.GetNextBatchNumber(batchNo); BatchTextBox.Text = batchNumber.ToString(); presenter.GetDateForBatch(); presenter.GetPremixCodeSheet(); presenter.RetrievePhaseControl(); presenter.RetrieveQuantity(); } } }