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

Process Flow

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. );
  17. CREATE TABLE [dbo].[UserMaster] (
  18. [UserId] INT IDENTITY (1, 1) NOT NULL,
  19. [UserName] VARCHAR (20) NULL,
  20. [Password] NVARCHAR (MAX) NULL,
  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. );
  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)
  49. SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage
  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
  59. SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage
  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
  68. SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage
  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]
  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;