Simple Check Maintenance Application

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

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.

CREATE TABLE [dbo].[ChequeDetails] (
    [ChequeDetailsId]  NUMERIC (20)    IDENTITY (1, 1) NOT NULL,
    [ChequeNumber]     NVARCHAR (50)   NULL,
    [GivenTo]          NVARCHAR (50)   NULL,
    [GivenDate]        NVARCHAR (20)   NULL,
    [ValidUpto]        NVARCHAR (20)   NULL,
    [Amount]           NUMERIC (20, 2) NULL,
    [Notes]            NVARCHAR (MAX)  NULL,
    [CreatedBy]        INT             NULL,
    [CreatedDateTime]  DATETIME        NULL,
    [ModifiedBy]       INT             NULL,
    [ModifiedDateTime] DATETIME        NULL,
    [IsDeleted]        BIT             NULL,
    PRIMARY KEY CLUSTERED ([ChequeDetailsId] ASC)
);

  CREATE TABLE [dbo].[UserMaster] (
    [UserId]        INT            IDENTITY (1, 1) NOT NULL,
    [UserName]      VARCHAR (20)   NULL,
    [Password]      NVARCHAR (MAX) NULL,
    [FirstName]     VARCHAR (50)   NULL,
    [LastName]      VARCHAR (50)   NULL,
    [Gender]        TINYINT        NULL,
    [EmailId]       VARCHAR (250)  NULL,
    [PhoneNumber]   VARCHAR (10)   NULL,
    [IsActiveUser]  BIT            NULL,
    [IsUserDeleted] BIT            NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
);

CREATE PROCEDURE Proc_ChequeDetails
@Mode varchar(10),
@ChequeDetailsId NUMERIC(20)=   NULL,
@ChequeNumber nvarchar(50)  =   NULL,
@GivenTo nvarchar(50)       =   NULL,
@GivenDate nvarchar(20)     =   NULL,
@ValidUpto nvarchar(20)     =   NULL,
@Notes nvarchar(max)        =   NULL,
@Amount NUMERIC(20,2)       =   NULL,
@UserId int                 =   NULL,
@IsDeleted bit              =   NULL
AS
    BEGIN
        IF(@Mode='INSERT')
            BEGIN
                INSERT INTO dbo.ChequeDetails(ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime,IsDeleted)
                VALUES(@ChequeNumber,@GivenTo,@GivenDate,@ValidUpto,@Amount,@Notes,@UserId,GETDATE(),@UserId,GETDATE(),0)

                SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage

                SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails
            END
        IF(@Mode='UPDATE')
            BEGIN
                UPDATE dbo.ChequeDetails SET ChequeNumber=@ChequeNumber,GivenTo=@GivenTo,GivenDate=@GivenDate,ValidUpto=@ValidUpto,Amount=@Amount,Notes=@Notes,
                ModifiedBy=@UserId,ModifiedDateTime=GETDATE()
                WHERE ChequeDetailsId=@ChequeDetailsId

                SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage

                SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails
            END
        IF(@Mode='DELETE')
            BEGIN
                UPDATE dbo.ChequeDetails SET IsDeleted=@IsDeleted,ModifiedBy=@UserId,ModifiedDateTime=GETDATE()
                WHERE ChequeDetailsId=@ChequeDetailsId

                SELECT 1 IsSuccess,'Cheque added Successfully!' as OutputMessage

                SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails
            END
        IF(@Mode='GETALL')
            BEGIN
                SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime FROM dbo.ChequeDetails
            END
        IF(@Mode='SEARCH')
            BEGIN
                SELECT ChequeDetailsId,ChequeNumber,GivenTo,GivenDate,ValidUpto,Amount,Notes,CreatedBy,CreatedDateTime,ModifiedBy,ModifiedDateTime,IsDeleted FROM dbo.ChequeDetails
            END
    END
CREATE PROCEDURE [dbo].[Proc_Report]

AS
BEGIN
    select cd.ChequeNumber,cd.Amount,cd.GivenTo as ChequeGivenTo,
    cd.GivenDate as [ChequeGivenDate],cd.ValidUpto as [ChequeValidUpto],DATEDIFF(DAY,CAST(GETDATE() AS DATE),
    (CASE WHEN TRY_PARSE(ValidUpto AS smalldatetime) IS NULL THEN TRY_PARSE((SUBSTRING(ValidUpto,4,2)
    + '/' + SUBSTRING(ValidUpto,1,2) + '/' +SUBSTRING(ValidUpto,7,4)) AS smalldatetime) ELSE TRY_PARSE(ValidUpto AS smalldatetime) END)) AS [RemainingDays],
    cd.Notes,umc.FirstName+' '+umc.LastName as [ChequeCreatedBy],cd.CreatedDateTime as [ChequeCreatedDateTime],
    umm.FirstName+' '+umm.LastName as [ChequeModifiedBy],cd.ModifiedDateTime as [ChequeModifiedDateTime] from dbo.ChequeDetails cd
    left join dbo.UserMaster umc on cd.CreatedBy=umc.UserId
    left join dbo.UserMaster umm on cd.ModifiedBy=umm.UserId
    order by cd.ModifiedDateTime desc
END
CREATE PROCEDURE [dbo].[Proc_ValidateUser]
@Mode varchar(50),
@UserName varchar(20),
@Password varchar(max)
AS
BEGIN
    IF @Mode='VALIDATEUSER'
        BEGIN
            IF EXISTS(SELECT 1 FROM dbo.UserMaster WHERE LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@UserName)
                BEGIN
                    IF EXISTS(SELECT 1 FROM dbo.UserMaster WHERE (LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@UserName) AND ISNULL(IsActiveUser,0)=1)
                        BEGIN
                            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)
                                BEGIN
                                    SELECT 'true' AS IsAuthenticated,UserId,'!' AS UserFullName,'Login Successfully!' AS ErrorMessage
                                    FROM dbo.UserMaster WHERE (LOWER(UserName)=LOWER(@UserName) OR PhoneNumber=@Username) AND Password=@Password
                                END
                            ELSE
                                BEGIN
                                    SELECT 'false' AS IsAuthenticated,0 AS UserId,'Wrong Password!' AS ErrorMessage
                                END
                            END
                        ELSE
                            BEGIN
                                SELECT 'false' AS IsAuthenticated,0 AS UserId,'User Name is not Active!' AS ErrorMessage
                            END
                END
            ELSE
                BEGIN
                    SELECT 'false' AS IsAuthenticated,0 AS UserId,'User Name not Exist!' AS ErrorMessage
                END
        END
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)

if (TxtUserName.Text == "" || string.IsNullOrEmpty(TxtUserName.Text)) {
                LblMessage.Text = "Please Enter User Name";
                TxtUserName.Focus();
                return;
            }
            if (TxtPassword.Text == "" || string.IsNullOrEmpty(TxtPassword.Text))
            {
                LblMessage.Text = "Please Enter Password";
                TxtPassword.Focus();
                return;
            }
            try
            {
                var dsResult = new DataSet();
                var vmResult = new VmUserDetails();
                SqlParameter[] sqlParam = new SqlParameter[3];
                sqlParam[0] = new SqlParameter("@Mode", "VALIDATEUSER");
                sqlParam[1] = new SqlParameter("@UserName", TxtUserName.Text);
                sqlParam[2] = new SqlParameter("@Password", TxtPassword.Text);
                dsResult = objHelper.GetDatasetFromProcedure("dbo.Proc_ValidateUser", sqlParam);
                if (dsResult != null && dsResult.Tables.Count > 0)
                {
                    if (dsResult.Tables[0].Rows.Count > 0)
                    {
                        vmResult = dsResult.Tables[0].ToList<VmUserDetails>().ToList().FirstOrDefault();
                    }
                }
                if (vmResult.IsAuthenticated)
                {
                    FrmChequeMaintanance frmMain = new FrmChequeMaintanance();
                    GlobalAccess.GlobalUserId = vmResult.UserId;
                    GlobalAccess.GlobalUserFullName = vmResult.UserFullName;
                    frmMain.Text = "Welcome " + vmResult.UserFullName;
                    this.Hide();
                }
                else
                {
                    LblMessage.Text = vmResult.ErrorMessage;
                    return;
                }

Check Details

 

 

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

var objAddCheque = new VmAddCheque();
            if (TxtChequeNumber.Text == "" || string.IsNullOrEmpty(TxtChequeNumber.Text))
            {
                LblMessageFaliure.Text = "Please Enter Cheque Number";
                TxtChequeNumber.Focus();
                return;
            }
            if (TxtGivenTo.Text == "" || string.IsNullOrEmpty(TxtGivenTo.Text))
            {
                LblMessageFaliure.Text = "Please Enter the Name of the Person whom you gave this Cheque";
                TxtGivenTo.Focus();
                return;
            }
            if (TxtGivenDate.Text == "" || string.IsNullOrEmpty(TxtGivenDate.Text))
            {
                LblMessageFaliure.Text = "Please Select Cheque Issued date";
                TxtGivenDate.Focus();
                return;
            }
            if (TxtValidUpto.Text == "" || string.IsNullOrEmpty(TxtValidUpto.Text))
            {
                LblMessageFaliure.Text = "Please Select Cheque Expiry date";
                TxtValidUpto.Focus();
                return;
            }
            if (TxtAmount.Text == "" || string.IsNullOrEmpty(TxtAmount.Text))
            {
                LblMessageFaliure.Text = "Please Fill the Cheque Amount";
                TxtAmount.Focus();
                return;
            }
            objAddCheque.Mode = "INSERT";
            objAddCheque.ChequeNumber = TxtChequeNumber.Text;
            objAddCheque.GivenTo = TxtGivenTo.Text;
            objAddCheque.GivenDate = TxtGivenDate.Text;
            objAddCheque.ValidUpto = TxtValidUpto.Text;
            objAddCheque.Amount = Convert.ToDecimal(TxtAmount.Text);
            objAddCheque.Notes = RtfNotes.Text;
            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.

 

this.procReportBindingSource.DataMember = "Proc_Report";
            this.procReportBindingSource.DataSource = this.chequeMaintananceDBdsReportBindingSource;
            //
            // chequeMaintananceDBdsReportBindingSource
            //
            this.chequeMaintananceDBdsReportBindingSource.DataSource = this.chequeMaintananceDBdsReport;
            this.chequeMaintananceDBdsReportBindingSource.Position = 0;
            //
            // chequeMaintananceDBdsReport
            //
            this.chequeMaintananceDBdsReport.DataSetName = "ChequeMaintananceDBdsReport";
            this.chequeMaintananceDBdsReport.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
            //
            // PnlReport
            //
            this.PnlReport.BackColor = System.Drawing.Color.SeaShell;
            this.PnlReport.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.PnlReport.Controls.Add(this.RptChequeDetails);
            this.PnlReport.Location = new System.Drawing.Point(13, 12);
            this.PnlReport.Name = "PnlReport";
            this.PnlReport.Size = new System.Drawing.Size(1457, 711);
            this.PnlReport.TabIndex = 1;

 

G

M

T

Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

Text-to-speech function is limited to 200 characters

Options : History : Feedback : Donate

Close