Create Sales Invoice Report using Crystal Report in C# windows application

Introduction:

Today in this article, I will explain How to create sales invoice/receipt using crystal report in c# windows application with the stored procedure.  

Crystal Reports is a great business objects tool available in Visual Studio to build reports. Crystal Reports is used for building simple to complex reports including daily reports as sales reports, and stock reports.

Follow these steps in order to implement “Create sales invoice using crystal report in c# windows application”

Step1: Create New Project.

 Go to File > New > Project > Windows > Windows form Application> Enter Application Name > Select your project location > then click to ok

Step2: Create a table and Stored procedure in the database.

In this example, I have used following table for creating an invoice using crystal report.

Customer:

CREATE TABLE [dbo].[tbl_Customer] (

    [Customer_id]     INT            IDENTITY (1, 1) NOT NULL,

    [First_Name]            NVARCHAR (MAX) NULL,

    [Last_Name]            NVARCHAR (MAX) NULL,

    [Billing_Address] NVARCHAR (MAX) NULL,

    [Postal_Code]     NVARCHAR (MAX) NULL,

    [Mobile_Number]   NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_Customer] PRIMARY KEY CLUSTERED ([Customer_id] ASC)

);

Invoice Product Detail:

CREATE TABLE [dbo].[tbl_Sales_Invoice_Product] (

    [Sr_no]        INT            IDENTITY (1, 1) NOT NULL,

    [InvoiceID]    NVARCHAR (50)  NULL,

    [ProductID]    INT            NULL,

    [ProductName]  NVARCHAR (MAX) NULL,

    [Quantity]     INT            NULL,

    [Amount]       FLOAT (53)     NULL,

    [Discountper]  FLOAT (53)     NULL,

    [Total_Amount] FLOAT (53)     NULL,

    PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

Invoice detail:

CREATE TABLE [dbo].[tbl_Sales_Invoice_Info] (

    [Id]           INT            IDENTITY (1, 1) NOT NULL,

    [InvoiceID]    NVARCHAR (50)  NOT NULL,

    [InvoiceDate]  DATE           NULL,

    [Customer_id]  INT            NULL,

    [Grand_Total]  FLOAT (53)     NULL,

    [Total_paid]   FLOAT (53)     NULL,

    [Balance]      FLOAT (53)     NULL,

    PRIMARY KEY CLUSTERED ([InvoiceID] ASC)

);

 Now I have to create the stored procedure for fetching record:

 Get Customer Detail

Create proc Sp_Customer_id

@Cust_id int

as

begin

Select * from tbl_Customer where Customer_id=@Cust_id

End

Get Invoice product Detail

CREATE proc Sp_InvoiceProduct_InvoiceID

@InvoiceID nvarchar(50)

as

begin

Select * from tbl_Sales_Invoice_Product where InvoiceID=@InvoiceID

end

Get Invoice Detail

CREATE proc Sp_Sales_InvoiceInfo_invid

@Invoice_id nvarchar(50)

as

begin

Select * from tbl_Sales_Invoice_info where InvoiceID=@Invoice_id

end

 Run above script in MS SQL Server and click to execute button.

Step5: Create a class for Database operation.

Here in this example, I have used Ado.net as database operation so we need to create a class for all database operations. Here I have created ‘db.cs’ class.

Now write the following code snippet given below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace InvoiceWindows

{
    class db

    {
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=BillingWdb;Integrated Security=True");

        public DataSet Invoice_product(int invid)

        {
            SqlCommand com = new SqlCommand("Sp_InvoiceProduct_InvoiceID", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@InvoiceID", invid);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

 

        public DataSet Customer_info(int cust_id)

        {

            SqlCommand com = new SqlCommand("Sp_Customer_id", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Cust_id", cust_id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

        public DataSet Inv_info(int invid)

        {

            SqlCommand com = new SqlCommand("Sp_Sales_InvoiceInfo_invid", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Invoice_id", invid);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

}

Step6: Add Class for Invoice Detail.

You can add a class for binding the Invoice details. Here I have added ‘InvoiceDetail.cs’

Now write the following code snippet given below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace InvoiceWindows

{
    class InvoiceDetail

    {
        public string ProductID { get; set; }

        public string ProductName { get; set; }

        public string Quantity { get; set; }

        public string Amount { get; set; }

        public string Discountper { get; set; }

        public int totalamount { get; set; }

    }

}

Step7: Add Crystal report

 Now I have to add Crystal report in a project with following procedure.

Go to Solution Explorer > Add New Item. Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal report gallery

Create the following format in Crystal Report

Step8: Add Windows Form for display crystal report

Go to Solution Explorer > Right Click >Add > New Item > Windows Form

Now write the following code snippet given below.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace InvoiceWindows

{
    public partial class Form1 : Form

    {
        public Form1()

        {
            InitializeComponent();
        }

        db dblayer = new db();

        private void Form1_Load(object sender, EventArgs e)

        {
            List<InvoiceDetail> _List = new List<InvoiceDetail>();

            DataSet ds = dblayer.Invoice_product(28);

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                _List.Add(new InvoiceDetail

                {

                    ProductID=dr["ProductID"].ToString(),

                    ProductName = dr["ProductName"].ToString(),

                    Quantity = dr["Quantity"].ToString(),

                    Amount = dr["Amount"].ToString(),

                    Discountper = dr["Discountper"].ToString(),

                    totalamount = Convert.ToInt32(dr["Total_Amount"])

                });

            }

            DataSet ds2 = dblayer.Customer_info(2);

            foreach (DataRow dr in ds2.Tables[0].Rows)

            {

                invoice1.SetDataSource(_List);

                invoice1.SetParameterValue("pCustomer",dr["First_Name"].ToString()+" "+dr["Last_Name"].ToString());

                invoice1.SetParameterValue("pAddress",dr["Billing_Address"].ToString());

                invoice1.SetParameterValue("pPostalCode",dr["Postal_Code"].ToString());

                invoice1.SetParameterValue("pPhoneNumber",dr["Mobile_Number"].ToString());

                invoice1.SetParameterValue("pOrder_id",28);

 

                DataSet ds3 = dblayer.Inv_info(28);

                foreach (DataRow dr1 in ds3.Tables[0].Rows)

                 {

                  invoice1.SetParameterValue("pDate",dr1["InvoiceDate"].ToString());

                invoice1.SetParameterValue("Grand_total",dr1["Grand_Total"].ToString());

                invoice1.SetParameterValue("pTotal_Paid",dr1["Total_paid"].ToString());

                invoice1.SetParameterValue("pBalance",dr1["Balance"].ToString());

                 }

                crystalReportViewer1.ReportSource=invoice1;

            }

        }

    }

}

Step 10:  Run Application

Now it’s time to run the application and got following output

f you have any problem and face any difficulty then Share your experiences with us in the comments below!

Like this post? Don’t forget to share it!