Invoice,Billing Application(Software)

Introduction

This is a simple billing project which was developed using C# & SQL Server 2005. You can use it for all type of invoice/Bill preparation after small modification.
Download Project
Download SQL Script

Video in Youtube

Create Database
There are two ways to create a database.
         1. SQL Management Studio Query Analyser.
         2. SQL Management Studio Visual Tool.
The following sql script create a database with default option using SQL Management Studio Query Analyser.

CREATE DATABASE [MyBill]

(where MyBill is a name of database)
Open SQL Server Management Studio Express and execute the above mentioned query.

Create a database using SQL Server Management Studio Express
Right Click on Databases.
Select New Database... from the popup

Type your database name(Eg. "MyBill")
Select "OK"

Table Creation

Table Name	Purpose
CompanyInfo	Store the Company name & Address, it will be used in Invoice
PrMaster	Store the Product details such as Product code, description, Rate, Unit etc.
InvoiceMain	Store the invoice details such as Invoice No., Date, Customer Name & Address,
InvoiceSub	Store the product details for corresponding invoice.

Script for Create the Table.
Copy the following script and paste in SQL Server Management studio Query Analyser. Press "F5" or Click "Run" button in the Tool bar.

        Use MyBill
go
CREATE TABLE [dbo].[InvoiceMain](
 [InvId] [int] IDENTITY(1,1) NOT NULL,
 [InvoiceNo] [int] NULL,
 [InvoiceDate] [datetime] NULL,
 [InvoiceCustomer] [varchar](200) NULL,
 [InvoiceAddress] [varchar](500) NULL,
 [InvoiceCustomerId] [varchar](50) NULL,
 [InvoiceChequeNo] [varchar](25) NULL,
 [InvoiceBank] [varchar](100) NULL,
 [InvChequeDate] [datetime] NULL,
 [InvNetAmount] [numeric](18, 2) NULL,
 [InvTaxNetAmount] [numeric](18, 2) NULL,
 CONSTRAINT [PK_InvoiceMain] PRIMARY KEY CLUSTERED 
(
 [InvId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvoiceSub](
 [RowId] [int] IDENTITY(1,1) NOT NULL,
 [InvId] [int] NULL,
 [PrId] [int] NULL,
 [PrQty] [numeric](18, 2) NULL,
 [PrRate] [numeric](18, 2) NULL,
 [PrTaxPercent] [numeric](18, 2) NULL,
 CONSTRAINT [PK_InvoiceSub] PRIMARY KEY CLUSTERED 
(
 [RowId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyInfo](
 [CompanyId] [int] IDENTITY(1,1) NOT NULL,
 [CompanyName] [varchar](100) NULL,
 [CompanyAddress1] [varchar](100) NULL,
 [CompanyAddress2] [varchar](100) NULL,
 [CompanyAddress3] [varchar](100) NULL,
 [CompanyPINCode] [varchar](50) NULL,
 [CompanyPhone1] [varchar](100) NULL,
 [CompanyTNGST] [varchar](50) NULL,
 [CompanyCST] [varchar](50) NULL,
 [CompanyTIN] [varchar](50) NULL,
 CONSTRAINT [PK_CompanyInfo] PRIMARY KEY CLUSTERED 
(
 [CompanyId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrMaster](
 [PrId] [int] IDENTITY(1,1) NOT NULL,
 [PrCode] [varchar](50) NULL,
 [PrDesc] [varchar](500) NULL,
 [PrUnit] [varchar](50) NULL,
 CONSTRAINT [PK_PrMaster] PRIMARY KEY CLUSTERED 
(
 [PrId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

Database Diagram(E-R Diagram)

Insert data into Companyinfo Table

       INSERT INTO [CompanyInfo]
           ([CompanyName]
           ,[CompanyAddress1]
           ,[CompanyAddress2]
           ,[CompanyAddress3]
           ,[CompanyPINCode]
           ,[CompanyPhone1]
           ,[CompanyTNGST]
           ,[CompanyCST]
           ,[CompanyTIN])
     VALUES
           ('My Company',
   'Address1',
   'Address2',
   'Address3',
   'PIN Code',
   'Phone No',
   'TNGST No',
   'CST No',
   'TIN'
   )
go
Select * from CompanyInfo
         

Insert Data in Product Master

Please refer the screen structure. I have designed this application in VS 2010 with C# Code. Below mentioned screen used to create Product.

Saved data in SQL Server.

Invoice/Bill Preparation

Please refer screen structure for Invoice/Bill.

Invoice List

Add - Create New Invoice.

Delete - Delete the selected Invoice.
Close - Close Invoice List.
Print - Print the selected Invoice.

Report Designing - Crystal Report.

Now I am going to design invoice/Billing application in  below mentioned format using Crystal Report.
Download Crystal report for Visual studio 2010

Add Crystal Report

Select Project Menu->Add New Item->Visual C# Items->Crystal Reports
Type the Report Name(MyInvoice.rpt)

Select "As a Blank Report"

Report Sections

Add fields to Report

Connect with Data source

Select View Menu->Server Explorer(Refer below screen)

Follow the images for data source connections.

Add table schema to Dataset

Drag & drop the "PrintInvoice" stored Procedure with "Invoicedataset.xsd"(Refer below screen)
PrintInvoice is a Stored Procedure which returns the necessary records for print the Invoice.

Place the field in Crystal Report.

Swith to MyInvoice.rpt Design mode.
Open Field Explorer.(If Field Explorer not visible Go to View Menu View->Other Windows->Document Outline)

Paper size & Margin Settings

Refer the below screen for Paper size & Margin settings.

Report Design

Place the fields in Report Area

Place the fields in Report(Refer the Image)

Preview the Report

Add new form and Place Crystal report viewer and name it CRV.

Change the DOCK Property of Crystal report viewer to Fill(Refer screen)

Switch to design view of frmInvoiceList.cs. Double click and write the following code in click Event

Refer the screen for Print the invoice.

  
private void btnPrint_Click(object sender, EventArgs e) {
            if (lstMain.SelectedItems.Count == 0)
            {
                MessageBox.Show("Please select the invoice...");
                return;
            }
            DataTable dt = new DataTable();
            string InvId;
            InvId = this.lstMain.SelectedItems[0].SubItems[6].Text;
            dt = Invoice.InvoicePrint(InvId);
            frmReportShow f = new frmReportShow();
            rptinvoice rpt = new rptinvoice();
            rpt.SetDataSource(dt);
            f.CRV.ReportSource = rpt;           
            f.ShowDialog();
        }

Print the Invoice(Refer Screen)

Invoice Output

Points to Remember
Just look at below screen.

Script of PrintInvoice stored Procedure

ALTER Proc [dbo].[PrintInvoice](@InvId int)
as
SELECT  dbo.InvoiceMain.InvId,
dbo.InvoiceMain.InvoiceNo,dbo.InvoiceMain.InvoiceDate, dbo.InvoiceMain.InvoiceCustomer,
dbo.InvoiceMain.InvoiceAddress, dbo.InvoiceMain.InvoiceCustomerId,
dbo.InvoiceMain.InvoiceChequeNo,dbo.InvoiceMain.InvoiceBank,
dbo.InvoiceMain.InvChequeDate, dbo.InvoiceMain.InvNetAmount,
dbo.InvoiceMain.InvTaxNetAmount,dbo.InvoiceSub.RowId,
dbo.InvoiceSub.PrId,dbo.InvoiceSub.PrQty,dbo.InvoiceSub.PrRate, 
dbo.InvoiceSub.PrTaxPercent,dbo.PrMaster.PrCode,
dbo.PrMaster.PrDesc,dbo.PrMaster.PrUnit,
dbo.CompanyInfo.CompanyName, dbo.CompanyInfo.CompanyAddress1,
dbo.CompanyInfo.CompanyAddress2,dbo.CompanyInfo.CompanyAddress3,
dbo.CompanyInfo.CompanyPINCode, dbo.CompanyInfo.CompanyPhone1,
dbo.CompanyInfo.CompanyTNGST, dbo.CompanyInfo.CompanyCST,
dbo.CompanyInfo.CompanyTIN,
dbo.AmountToWords(InvNetAmount)+' only.' as Amountinwords
FROM dbo.InvoiceMain 
INNER JOIN dbo.InvoiceSub ON dbo.InvoiceMain.InvId =
 dbo.InvoiceSub.InvId INNER JOIN
dbo.PrMaster ON dbo.InvoiceSub.PrId = 
dbo.PrMaster.PrId CROSS JOIN
dbo.CompanyInfo
where dbo.InvoiceMain.InvId=@InvId
order by rowId

The field InvId(Identity Column) is a primary key of InvoiceMain Table.
The field InvId is a Foreign key of InvoiceSub Table which used to create ONE TO MANY relationship between the tables.
The PrintInvoice stored Procedure used to print the invoice. When you execute this procedure it should return some records. If it is return Zero record, the blank report will open.
In invoice no 1, there are two items are used.
In invoice no 2, there are three items are used.

ER Diagram for Invoice Print The above ER Diagram show the relationship between the table. The CompanyInfo table cross join with other table.
The CompanyInfo table must have only one record. If it have more than one record, the duplicate records will be return byPrintInvoice stored procedure.
The CompanyInfo table is optional. I have stored the Company details in that table. You can directly type(static text) company details in Crystal Report using Text box control. After deployment of the application, you may need to change the company details. If you use the CompanyInfo table, just update the details in Corresponding field in CompanyInfo. It will reflect immediately in the report.
if you use the static text method, you have to again deploy the entire project.

If you want the source code, Please send the mail to mailtobabum@gmail.com

Troubleshoot

So many user post their comments. But they have written, the application returned blank reports. Please run the following query in your Sql Server management studio(Refer Image). All the query should return at least one row. Otherwise you will get the blank report. If any of the query return no records, kindly check whether the table has record or not.

select * from [dbo].[CompanyInfo]

select * from [dbo].[PrMaster]

select * from [dbo].[InvoiceMain] order by invid 

select * from [dbo].[InvoiceSub] order by invid 


SELECT  dbo.AmountToWords(InvNetAmount)+' only.' as Amountinwords,   dbo.InvoiceMain.InvId,
 dbo.InvoiceMain.InvoiceNo,
 dbo.InvoiceMain.InvoiceDate, dbo.InvoiceMain.InvoiceCustomer, dbo.InvoiceMain.InvoiceAddress, 
dbo.InvoiceMain.InvoiceCustomerId, dbo.InvoiceMain.InvoiceChequeNo, dbo.InvoiceMain.InvoiceBank,
 dbo.InvoiceMain.InvChequeDate, 
dbo.InvoiceMain.InvNetAmount, dbo.InvoiceMain.InvTaxNetAmount, dbo.InvoiceSub.RowId,
 dbo.InvoiceSub.PrId, dbo.InvoiceSub.PrQty, dbo.InvoiceSub.PrRate, 
dbo.InvoiceSub.PrTaxPercent, dbo.PrMaster.PrCode, dbo.PrMaster.PrDesc, dbo.PrMaster.PrUnit,
 dbo.CompanyInfo.CompanyName, 
dbo.CompanyInfo.CompanyAddress1, dbo.CompanyInfo.CompanyAddress2, dbo.CompanyInfo.CompanyAddress3,
 dbo.CompanyInfo.CompanyPINCode, 
dbo.CompanyInfo.CompanyPhone1, dbo.CompanyInfo.CompanyTNGST, dbo.CompanyInfo.CompanyCST,
 dbo.CompanyInfo.CompanyTIN
FROM dbo.InvoiceMain INNER JOIN
dbo.InvoiceSub ON dbo.InvoiceMain.InvId = dbo.InvoiceSub.InvId INNER JOIN
dbo.PrMaster ON dbo.InvoiceSub.PrId = dbo.PrMaster.PrId CROSS JOIN
dbo.CompanyInfo