Simple Check Maintenance Application

In this blog, we will create a simple Windows application for maintaining check details using Visual Studio.

Process Flow

  • Create table & store procedure
  • Create Application
  • Report Generation

Create table & store procedure

In this application I have used MDF file. If you need, create a database from SQL Server, following the same script.
  1. CREATE TABLE [dbo].[ChequeDetails] (  
  2.     [ChequeDetailsId]  NUMERIC (20)    IDENTITY (1, 1) NOT NULL,  
  3.     [ChequeNumber]     NVARCHAR (50)   NULL,  
  4.     [GivenTo]          NVARCHAR (50)   NULL,  
  5.     [GivenDate]        NVARCHAR (20)   NULL,  
  6.     [ValidUpto]        NVARCHAR (20)   NULL,  
  7.     [Amount]           NUMERIC (20, 2) NULL,  
  8.     [Notes]            NVARCHAR (MAX)  NULL,  
  9.     [CreatedBy]        INT             NULL,  
  10.     [CreatedDateTime]  DATETIME        NULL,  
  11.     [ModifiedBy]       INT             NULL,  
  12.     [ModifiedDateTime] DATETIME        NULL,  
  13.     [IsDeleted]        BIT             NULL,  
  14.     PRIMARY KEY CLUSTERED ([ChequeDetailsId] ASC)  
  15. );  
  16.   
  17.   CREATE TABLE [dbo].[UserMaster] (  
  18.     [UserId]        INT            IDENTITY (1, 1) NOT NULL,  
  19.     [UserName]      VARCHAR (20)   NULL,  
  20.     [Password]      NVARCHAR (MAXNULL,  
  21.     [FirstName]     VARCHAR (50)   NULL,  
  22.     [LastName]      VARCHAR (50)   NULL,  
  23.     [Gender]        TINYINT        NULL,  
  24.     [EmailId]       VARCHAR (250)  NULL,  
  25.     [PhoneNumber]   VARCHAR (10)   NULL,  
  26.     [IsActiveUser]  BIT            NULL,  
  27.     [IsUserDeleted] BIT            NULL,  
  28.     PRIMARY KEY CLUSTERED ([UserId] ASC)  
  29. );  
  30.   
  31. CREATE PROCEDURE Proc_ChequeDetails  
  32. @Mode varchar(10),  
  33. @ChequeDetailsId NUMERIC(20)=   NULL,  
  34. @ChequeNumber nvarchar(50)  =   NULL,  
  35. @GivenTo nvarchar(50)       =   NULL,  
  36. @GivenDate nvarchar(20)     =   NULL,  
  37. @ValidUpto nvarchar(20)     =   NULL,  
  38. @Notes nvarchar(max)        =   NULL,  
  39. @Amount NUMERIC(20,2)       =   NULL,  
  40. @UserId int                 =   NULL,  
  41. @IsDeleted bit              =   NULL  
  42. AS  
  43.     BEGIN  
  44.         IF(@Mode='INSERT')  
  45.             BEGIN  
  46.                 INSERT INTO dbo.ChequeDetails(ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime,IsDeleted)  
  47.                 VALUES(@ChequeNumber,@GivenTo,@GivenDate,@ValidUpto,@Amount,@Notes,@UserId,GETDATE(),@UserId,GETDATE(),0)  
  48.   
  49.                 SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage  
  50.   
  51.                 SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails  
  52.             END  
  53.         IF(@Mode='UPDATE')  
  54.             BEGIN  
  55.                 UPDATE dbo.ChequeDetails SET ChequeNumber=@ChequeNumber,GivenTo=@GivenTo,GivenDate=@GivenDate,ValidUpto=@ValidUpto,Amount=@Amount,Notes=@Notes,  
  56.                 ModifiedBy=@UserId,ModifiedDateTime=GETDATE()  
  57.                 WHERE ChequeDetailsId=@ChequeDetailsId  
  58.   
  59.                 SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage  
  60.   
  61.                 SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails  
  62.             END  
  63.         IF(@Mode='DELETE')  
  64.             BEGIN  
  65.                 UPDATE dbo.ChequeDetails SET IsDeleted=@IsDeleted,ModifiedBy=@UserId,ModifiedDateTime=GETDATE()  
  66.                 WHERE ChequeDetailsId=@ChequeDetailsId  
  67.   
  68.                 SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage  
  69.   
  70.                 SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails  
  71.             END  
  72.         IF(@Mode='GETALL')  
  73.             BEGIN  
  74.                 SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails  
  75.             END  
  76.         IF(@Mode='SEARCH')  
  77.             BEGIN  
  78.                 SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime,IsDeleted FROM dbo.ChequeDetails  
  79.             END  
  80.     END  
  81. CREATE PROCEDURE [dbo].[Proc_Report]  
  82.   
  83. AS  
  84. BEGIN  
  85.     select cd.ChequeNumber,cd.Amount,cd.GivenTo as ChequeGivenTo,  
  86.     cd.GivenDate as [ChequeGivenDate],cd.ValidUpto as [ChequeValidUpto],DATEDIFF(DAY,CAST(GETDATE() AS DATE),  
  87.     (CASE WHEN TRY_PARSE(ValidUpto AS smalldatetime) IS NULL THEN TRY_PARSE((SUBSTRING(ValidUpto,4,2)   
  88.     + '/' + SUBSTRING(ValidUpto,1,2) + '/' +SUBSTRING(ValidUpto,7,4)) AS smalldatetime) ELSE TRY_PARSE(ValidUpto AS smalldatetime) END)) AS [RemainingDays],  
  89.     cd.Notes,umc.FirstName+' '+umc.LastName as [ChequeCreatedBy],cd.CreatedDateTime as [ChequeCreatedDateTime],  
  90.     umm.FirstName+' '+umm.LastName as [ChequeModifiedBy],cd.ModifiedDateTime as [ChequeModifiedDateTime] from dbo.ChequeDetails cd   
  91.     left join dbo.UserMaster umc on cd.CreatedBy=umc.UserId  
  92.     left join dbo.UserMaster umm on cd.ModifiedBy=umm.UserId  
  93.     order by cd.ModifiedDateTime desc  
  94. END  
  95. CREATE PROCEDURE [dbo].[Proc_ValidateUser]  
  96. @Mode varchar(50),  
  97. @UserName varchar(20),  
  98. @Password varchar(max)  
  99. AS  
  100. BEGIN  
  101.     IF @Mode='VALIDATEUSER'  
  102.         BEGIN  
  103.             IF EXISTS(SELECT 1 FROM dbo.UserMaster WHERE LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@UserName)  
  104.                 BEGIN  
  105.                     IF EXISTS(SELECT 1 FROM dbo.UserMaster WHERE (LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@UserName) AND ISNULL(IsActiveUser,0)=1)  
  106.                         BEGIN  
  107.                             IF EXISTS(SELECT 1 FROM dbo.UserMaster WHERE (LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@UserName) AND Password=@Password COLLATE SQL_Latin1_General_CP1_CS_AS)  
  108.                                 BEGIN  
  109.                                     SELECT 'true' AS IsAuthenticated,UserId,'!' AS UserFullName,'Login Successfully!' AS ErrorMessage  
  110.                                     FROM dbo.UserMaster WHERE (LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@Username) AND Password=@Password  
  111.                                 END  
  112.                             ELSE  
  113.                                 BEGIN  
  114.                                     SELECT 'false' AS IsAuthenticated,0 AS UserId,'Wrong Password!' AS ErrorMessage  
  115.                                 END  
  116.                             END  
  117.                         ELSE  
  118.                             BEGIN  
  119.                                 SELECT 'false' AS IsAuthenticated,0 AS UserId,'User Name is not Active!' AS ErrorMessage  
  120.                             END  
  121.                 END  
  122.             ELSE  
  123.                 BEGIN  
  124.                     SELECT 'false' AS IsAuthenticated,0 AS UserId,'User Name not Exist!' AS ErrorMessage  
  125.                 END  
  126.         END  
  127. END  

 Create Application

Open Visual Studio New > Project > Window Desktop > Select Windows Form Application.

 

After the project name hit OK and it will go to the solution explorer window.

Right click the project and select New> New Item.

 

I have added all files like the below structure

 

If you are going to use MDF, after creating this file run the above query in Visual Studio.

I have authenticated a simple username & password.

 

Only after login is validation, it  the Parent form (MDI Form)

  1. if (TxtUserName.Text == "" || string.IsNullOrEmpty(TxtUserName.Text)) {  
  2.                 LblMessage.Text = "Please Enter User Name";  
  3.                 TxtUserName.Focus();  
  4.                 return;  
  5.             }  
  6.             if (TxtPassword.Text == "" || string.IsNullOrEmpty(TxtPassword.Text))  
  7.             {  
  8.                 LblMessage.Text = "Please Enter Password";  
  9.                 TxtPassword.Focus();  
  10.                 return;  
  11.             }  
  12.             try  
  13.             {  
  14.                 var dsResult = new DataSet();  
  15.                 var vmResult = new VmUserDetails();  
  16.                 SqlParameter[] sqlParam = new SqlParameter[3];  
  17.                 sqlParam[0] = new SqlParameter("@Mode""VALIDATEUSER");  
  18.                 sqlParam[1] = new SqlParameter("@UserName", TxtUserName.Text);  
  19.                 sqlParam[2] = new SqlParameter("@Password", TxtPassword.Text);  
  20.                 dsResult = objHelper.GetDatasetFromProcedure("dbo.Proc_ValidateUser", sqlParam);  
  21.                 if (dsResult != null && dsResult.Tables.Count > 0)  
  22.                 {  
  23.                     if (dsResult.Tables[0].Rows.Count > 0)  
  24.                     {  
  25.                         vmResult = dsResult.Tables[0].ToList<VmUserDetails>().ToList().FirstOrDefault();  
  26.                     }  
  27.                 }  
  28.                 if (vmResult.IsAuthenticated)  
  29.                 {  
  30.                     FrmChequeMaintanance frmMain = new FrmChequeMaintanance();   
  31.                     GlobalAccess.GlobalUserId = vmResult.UserId;  
  32.                     GlobalAccess.GlobalUserFullName = vmResult.UserFullName;  
  33.                     frmMain.Text = "Welcome " + vmResult.UserFullName;  
  34.                     this.Hide();  
  35.                 }  
  36.                 else  
  37.                 {  
  38.                     LblMessage.Text = vmResult.ErrorMessage;  
  39.                     return;  
  40.                 }  
Check Details
 
 

Data is saved into the database and pending to data grid control.

  1. var objAddCheque = new VmAddCheque();  
  2.             if (TxtChequeNumber.Text == "" || string.IsNullOrEmpty(TxtChequeNumber.Text))  
  3.             {  
  4.                 LblMessageFaliure.Text = "Please Enter Cheque Number";  
  5.                 TxtChequeNumber.Focus();  
  6.                 return;  
  7.             }  
  8.             if (TxtGivenTo.Text == "" || string.IsNullOrEmpty(TxtGivenTo.Text))  
  9.             {  
  10.                 LblMessageFaliure.Text = "Please Enter the Name of the Person whom you gave this Cheque";  
  11.                 TxtGivenTo.Focus();  
  12.                 return;  
  13.             }  
  14.             if (TxtGivenDate.Text == "" || string.IsNullOrEmpty(TxtGivenDate.Text))  
  15.             {  
  16.                 LblMessageFaliure.Text = "Please Select Cheque Issued date";  
  17.                 TxtGivenDate.Focus();  
  18.                 return;  
  19.             }  
  20.             if (TxtValidUpto.Text == "" || string.IsNullOrEmpty(TxtValidUpto.Text))  
  21.             {  
  22.                 LblMessageFaliure.Text = "Please Select Cheque Expiry date";  
  23.                 TxtValidUpto.Focus();  
  24.                 return;  
  25.             }  
  26.             if (TxtAmount.Text == "" || string.IsNullOrEmpty(TxtAmount.Text))  
  27.             {  
  28.                 LblMessageFaliure.Text = "Please Fill the Cheque Amount";  
  29.                 TxtAmount.Focus();  
  30.                 return;  
  31.             }  
  32.             objAddCheque.Mode = "INSERT";  
  33.             objAddCheque.ChequeNumber = TxtChequeNumber.Text;  
  34.             objAddCheque.GivenTo = TxtGivenTo.Text;  
  35.             objAddCheque.GivenDate = TxtGivenDate.Text;  
  36.             objAddCheque.ValidUpto = TxtValidUpto.Text;  
  37.             objAddCheque.Amount = Convert.ToDecimal(TxtAmount.Text);  
  38.             objAddCheque.Notes = RtfNotes.Text;  
  39.             SaveDatas(objAddCheque);  

Report Generation

For the report concept I have used default Visual Studio report of rdlc. If you need, you can add any external reporting tool like crystal report, Telerik report…etc.

 
  1. this.procReportBindingSource.DataMember = "Proc_Report";  
  2.             this.procReportBindingSource.DataSource = this.chequeMaintananceDBdsReportBindingSource;  
  3.             //   
  4.             // chequeMaintananceDBdsReportBindingSource  
  5.             //   
  6.             this.chequeMaintananceDBdsReportBindingSource.DataSource = this.chequeMaintananceDBdsReport;  
  7.             this.chequeMaintananceDBdsReportBindingSource.Position = 0;  
  8.             //   
  9.             // chequeMaintananceDBdsReport  
  10.             //   
  11.             this.chequeMaintananceDBdsReport.DataSetName = "ChequeMaintananceDBdsReport";  
  12.             this.chequeMaintananceDBdsReport.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;  
  13.             //   
  14.             // PnlReport  
  15.             //   
  16.             this.PnlReport.BackColor = System.Drawing.Color.SeaShell;  
  17.             this.PnlReport.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;  
  18.             this.PnlReport.Controls.Add(this.RptChequeDetails);  
  19.             this.PnlReport.Location = new System.Drawing.Point(13, 12);  
  20.             this.PnlReport.Name = "PnlReport";  
  21.             this.PnlReport.Size = new System.Drawing.Size(1457, 711);  
  22.             this.PnlReport.TabIndex = 1; 

 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now